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.
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 | Project | Midterm | Final |
| 10% | 10% | 40% | 40% |
| Week No | Topics | Material |
|---|---|---|
| Week 1 | Advanced Concepts in ERM | Material covered in class: Inheritance (adding type attribute), dealing with time variant data, fan trap |
| Week 2-4 | Database Architectures and the Web (Note: chapter 2 and 3 ppts have overlapping material) | ch02, ch03 |
| Week 4-6 | Database Development LifeCycle | ch10 |
| Week 7-8 |
Conceptual Database Design Methodology | ch16 |
| Week 9 | Logical Database Design Methodology |
logicaldbdesign.ppt removing inheritance ch17 |
| Week 10 | Physical Database Design Methodology |
ch18 |
| Week 11 | Monitoring and Tuning the Operational System |
ch19 |
| Week 12-13 | Security and Administration | ch20 |
| Supplemental Material | Designing a conceptual ERD. Case study: Nutrition list, supermarket receipt |
nutrition list, supermarket receipt datarequirements listed in class first part second part |
| Supplemental Material | Case Study: Dream House | ch11 |
| Week No | Topics | Material |
|
|---|---|---|---|
| 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 | |
| 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 | |
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.
