Aim of the Course
This course is the second core-course in the databases field in the department. It assumes knowledge of SQL and database design (ITEC212). Conceptual and logical database design procedure for an enterprise level database and implementation from the programming perspective are covered in the lectures. Common problems and their solutions, security and access considerations in database design are discussed in detail. The labs cover efficient use of SQL for complicated tasks and teach a 3GL database language.
Teaching/Learning Strategy
The topics will be introduced in lecture hours. Student participation in discussion is important in promoting successful learning. Students will work in pairs on a project where they have to perform analysis, design and programming as discussed in class.
Lab Material
Sprin2018-2019 Spool Files
Lab 1 : If Statements (26/02/2018)
gr01 gr02Lab 4 : Implicit Cursor: DML, Select Into and Related Named Exceptions
gr01gr02
Lab 4 : Implicit Cursor: DML, Select Into and Related Named Exceptions (Cont) gr01gr02 Lab 6 : Explicit Cursors
gr01 gr02Lab 8 ? Functions
Fall 2018-2019 Lab Material
Your classmate Mahmoud created a google drive folder to share the spool files with you.
Click here
| Week 1 | Lab requirements,SQL review | | |
| Week 2 | Introduction to PL/SQL, Block structure of PL/SQL and overview of the language If , Elsif & Else Statements in PL/SQL |
Intro to PL/SQL
|
|
|
|
Week 3 | Loops in PL/SQL
|
loops, Exercises loops and if
|
|
|
Apex Account Information |
Spring 20182019 Apex Account Usernames/Password
Apex Student Guide
Script to create the tables used in lab
| |
|
Week 4-5 | Using DML and select into |
plsql_dml.ppt, Spool files:
Fall2017-18gr01,gr02
qsfromstudsgr01 quizansgr02,
Spring2016-7:lab3gr01.txt,lab3gr02.txt,lab4gr01.txt,lab4gr02.txt | |
| Exercises
| Download and install College.sql
| |
|
Week 6 | Explicit Cursors
|
Explicit_Cursors.pptx, Spool files:Fall2017-18Lab5gr01,lab5gr02 ;
lab6gr01,
lab6gr02
Spring2016-17:lab5gr01.txt, lab5gr02.txt
| |
|
Week 7-8 | Exceptions |
Exception Handling.pptx, Spool files:
Fall2017-18gr01,gr02
Spring2016-17:lab6gr01.txt, lab6gr02.txt | |
|
Week 9-10 | Users defined Exceptions, raising exceptions, cursor for loops |
Spool files: gr01,gr02
Spring2016-17:lab7gr01.txt, lab7gr02.txt | |
|
Week 11-12 | Procedures-Functions | *(new)procedures and functions *(new)
procedures_and_functions.pptx
Spool files:Fall2017-18gr01,gr02
Spring2016-17:lab8gr01.txt, lab8gr02.txt
| |
|
Week 13 | Parameter Types |
Spool files: gr01,gr02
Spring2016-17:lab9gr01.txt, lab9gr02.txt | |
|
Week 14-15 | Triggers |
Triggers.pptx,
overview of triggers
Spool files:Fall2017-18gr01_1stuser - gr01_2nduser,gr02_1stuser - gr02_2nduser
Spring2016-17:lab10gr01.txt, lab10gr02.txt
| |
| | | |
| | | |
Project
All submissions will be both hard copy and soft copy via email. Handwritten projects will NOT be graded!
Spring 20182019 Project: Design DB for wayfair.com. Description
Submission 1 (for feedback Due 21/05/2019):
Conceptual Database Design: Submission will include : composition diagram or; Conceptual ERD; and the relevant documentation (entity type document, relationship type document, attribute document, attribute domain document) Use peter chen notation. ( example DB Documentation. )
Final Submission : Due 10/06/2019 on paper and as soft copy
Fall 20182019 Project: Design DB for an orgaization that provides Online Courses
Submission 1 (for feedback): ITEC224 project fall20182019.docx
Conceptual Database Design for two views. Submission will include for each user view : composition diagram or numbered list of requirements; Conceptual ERD; and the relevant documentation (entity type document, relationship type document, attribute document, attribute domain document) Use peter chen notation. ( example DB Documentation. )
Submission 2 (Final Report Due 28/12/2018 before 14:30 as hard copy and as soft copy via email): Complete the project based on the feedback. Write a
short report that has introduction section/chapter (Describe the project you are working on using your own words), Conceptual design for each user view as described above (You can create a separate chapter/sect'on for each userview). You are encouraged to include screen shots and explanations to clarify your design. Finally a conclusion section/chapter where you discuss the problems youhave faced, how you overcame the difficulties. Also explain how you would complete the project if you had more time.
