Design and implement at least 4 queries. (2 marks) ➢ Design two stored procedures

Project Objective: The main goal of this project is to give students an opportunity to put the theory of database modelling, implementation, and application design into practice. The result will be a fully functioning database application. It includes three main phases: • The first phase: Proposal and Analysis •

The second phase: Design of the relational DB, • The third phase: Implementation of the relational DB Instructions of the project: • You can work on this project as a group (maximum 4 students) or individually.

Develop a proposal for the database management system project. – This project will focus on designing DBMS for any topic related to any domain. Below, some examples of database project domain. – Healthcare Domain. o Medical Billing and Claims System o Pharmacy Inventory Management System o Clinical Trials Database o Medical Equipment Maintenance System o Disease Registry Database o Drug Interaction Database – Education Domain o Library o School/university system – …. – Students are not limited to the list above • One submission for each group is mandatory. •

Groups, where each includes (4 Students), should submit for approval their project ideas. This proposal should include: – A separate cover page indicating the title of your project, the full names of the group members (with student ID), the course number and course section. – A narrative description of the problem or opportunity being addressed. – Identification of the information needs – what information would help solve the problem or allow one to take advantage of the opportunity. – Initial list of entities (tables) that have been identified. This should come naturally from the above discussions. – Distribution of duties for the project. List the names of each group member and what their primary role. • Groups should not continue working on the project unless it has been approved by the instructor. • Project worth = 20 marks, will be distributed as following: •

Design the database following an ER model. (5 marks) – The Conceptual ER should contain all necessary entities and relationship titles (many to many is allowed at this stage) and cardinalities. – The ERD should contain more details compared to Conceptual ER such as entities, attributes, relationships (not many to many), cardinalities etc. • Convert your ER diagram to a relational schema. (2.5 marks) – Underline all key attributes in your relations. • Normalize the tables (each table should be in 3NF at least) (2.5 marks) – Specify all functional dependencies for each relation. (Mapping) • Use Oracle to create the normalized tables. (2 marks) • Populate your tables with 5 rows at least. (2 marks) • Design and implement at least 4 queries. (2 marks) As specified below – Data Retrieval Queries with Select (total of 4 Queries) ➢ Where ➢ Order By ➢ Group By ➢ Aggregate functions ➢ Subquery ➢ Join query Note: – You are free to mix between all those clauses in one or more query but you have to use them all. So the total number of queries for Data Retrieval Queries with Select in the end will be 4. – Simple queries such as select all (*) is NOT accepted. • Design two stored procedures as the following (4 marks) ➢

Create a PARAMETER based SELECT QUERY stored procedure which return records based on parameters? ➢ Create an UPDATE query based stored procedure. • Each student should submit one report for each group about his/her project via the Blackboard contains the following: – Project title. – Group members information (name, id, email) – ER Diagram. – All schemas before and after normalization. – All SQL statements of: ➢ Creating tables ➢ Inserting data in tables ➢ Queries. ➢ Stored Procedures – Screenshots from oracle of all the tables after population and queries results. Project Milestones Instructors should meet with each group to discuss their progress made for phase 1, 2 and 3. The instructors must give students comments on the submitted part of the project and discuss their design issues. There is no grades for phase1,2, and 3. • Feb10: Group Project Proposal submitted. (accepted/not accepted) • Mar25: Systems Analysis and E-R Model completed. (Pass/Fail) • Mar25: Logical modelling and normalization. (Pass/Fail) After students submit final project marks are distributed as follows: • Apr29: Full project should be submitted by this date. • Marks distribution is as follows: • Conceptual ER Model (2.5 marks) • ERD Model completed. (2.5 marks) • Relational Model (2.5) • Logical modelling and normalization. (2.5 marks) • Physical

Database implementation (10 marks), distributed as follows: ➢ Create the normalized tables. (2 marks) ➢ Populate your tables with 5 rows at least. (2 marks) ➢

Design and implement at least 4 queries. (2 marks) ➢ Design two stored procedures (4 marks) • Apr29: Complete final report Note: You may not work on a project that has already been done. This includes projects that you find on the web and projects completed in prior semesters. (Instructors will use a plagiarism checking tool in Blackboard)

Last Completed Projects