ITEC224 - Database Programming (No longer updated)

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.


Grading Scheme

Project MidtermFinal

Students Marks

Lecture Material


Week NoTopicsMaterial
Week 1Advanced Concepts in ERMMaterial covered in class: Inheritance (adding type attribute), dealing with time variant data, fan trap
Week 2-4Database Architectures and the Web
(Note: chapter 2 and 3 ppts have overlapping material)
Week 4-6Database Development LifeCycle ch10
Week 7-8 Conceptual Database Design Methodology
Week 9 Logical Database Design Methodology logicaldbdesign.ppt   removing inheritance ch17
Week 10Physical Database Design Methodology ch18
Week 11
Monitoring and Tuning
the Operational System
Week 12-13Security and Administrationch20
Supplemental Material 
Designing a conceptual ERD. Case study: Nutrition list, supermarket receipt nutrition listsupermarket receipt

datarequirements listed in class first part second part
Supplemental Material 
Case Study: Dream Housech11


Lab Material

Sprin2018-2019 Spool Files
Lab 1 : If Statements (26/02/2018) gr01 gr02
Lab 2 : Loops (05/03/2018) gr01gr02
Lab 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 gr02
Lab 7 :  Exceptions gr01gr02
Lab 8 ? Functions
Lab 9 : Procedures gr01
Lab11: Triggers gr01 gr02

Fall 2018-2019 Lab Material 
Your classmate Mahmoud created a google drive folder to share the spool files with you. Click here

Week NoTopicsMaterial
Week 1Lab requirements,SQL review
Week 2Introduction 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 3Loops in PL/SQL
loopsExercises 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-5Using DML and select into plsql_dml.ppt, Spool files: Fall2017-18gr01,gr02 qsfromstudsgr01 quizansgr02,

Download and install College.sql
Week 6Explicit Cursors 
Explicit_Cursors.pptxSpool files:Fall2017-18Lab5gr01,lab5gr02 ; lab6gr01, lab6gr02
Week 7-8Exceptions  Exception Handling.pptxSpool files: Fall2017-18gr01,gr02
Week 9-10Users defined Exceptions, raising exceptions, cursor for loops Spool files: gr01,gr02
Week 11-12Procedures-Functions *(new)procedures and functions *(new)
Spool files:Fall2017-18gr01,gr02
Week 13Parameter Types Spool files: gr01,gr02
Week 14-15Triggers Triggers.pptx, overview of triggers
Spool files:Fall2017-18gr01_1stuser - gr01_2nduser,gr02_1stuser -  gr02_2nduser




All submissions will be both hard copy and soft copy via email. Handwritten projects will NOT be graded!

Spring 20182019 Project: Design DB for 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.