In this project, you need to design a database system for a given domain. For this, first you need to conduct research about the domain itself and collect data requirements (such as information about the main components of the system, how the system works, how different entities interact with each other etc.) Then you will try to design a system as practical as possible. This means, assumptions you make should comply with real-world situations/scenarios.
Tasks:
Step 1: Write the data requirements for the system.
Step 2: Draw the initial ER diagram for your system. ER diagrams should be drawn electronically. You can use any chart-drawing tool of your choice. (i.e. draw.io, lucidchart.com, etc.)
A minimal system design should include at least:
a. two one-to-one binary relationships.
b. two one-to-many binary relationships.
c. two many-to-many binary relationships.
Indicate cardinality and participation constraints on ER diagram. You can use different notations to show cardinality ratios. (i.e. min-max notation, Crow’s Foot notation)
Step 3: Map the ER diagram into relational schema. Show the resulting relational schema: show your tables, primary keys and foreign keys.
Step 4: Discuss database normalization rules on your tables. Show the functional dependencies that violate 1st, 2nd and 3rd normal forms. Normalize your table(s) into 3NF.
Step 5: Show the final relational schema after normalization.
Step 6: Create tables using appropriate SQL command. Make sure to include primary key and foreign key definitions and triggered actions on foreign keys. Decide also about NOT NULL constraints and DEFAULT values for the attributes.
Step 7: PL/SQL: Define two relevant stored procedures and two triggers (they should have a meaningful application in real-world cases)
Project Presentations:
Project presentations will be on May 2nd and May 4th. In the presentation you will explain your ER model and stored procedures & triggers.
Project Report:
You will submit your final project report until May 4, 2022, 11:59pm.
Only one member from each group is required to upload the project report through eLearning system.
At the beginning of the project report, make sure to include:
o Project Title
o Course number and section number
o Team number
o Team members (names and netids)
Naming convention for the report file: CourseNumber-TeamNumber-ProjectTitle.pdf.
Example: CS6360.001-Team4-Amazon-5.pdf
Evaluation:
o Requirements collection: comprehensive, detail-oriented system (20 points)
o ER/EER (20 points)
o Mapping to relational model and normalization (20 points)
o SQL (20 points)
o PL/SQL (20 points)