The purpose of this assignment is to develop a working relational database for a real-world
application. In this project, you will be able to learn database design methodology for relational
database and create a database which can store data efficiency and retrieve information
effectively.
Skills
• Analysis and list clear assumptions to work with unclear requirements
• Experience with analysis and design of (DB) software
• Knowledge of DBMS, both in terms of use and implementation/design
• Experience with SQL
• Experience working as part of team
Introduction
You have been asked to design a database system solution for the team chosen project.
• Study the project’s problem description very well before moving on to the next step
Tasks:
For this Assignment final submission, please complete the following tasks:
For the team chosen project idea
1. Draw an EE-R data model for your project design. Use MySQL Workbench E-R model
to present your EE-R diagrams. Justify the decisions you make regarding minimum and
maximum cardinalities. Document any assumptions you made, and the reasons for your
design choices. As part of this assignment, you should identify any missing or incomplete
requirements (if there are any), and explicitly state them in your documentation, and you
should also explicitly state any assumptions you made that were not part of the
requirements listed above, in order to develop a complete diagram.
2. Convert this data model to a Relations database. Specify tables, primary keys, and
foreign keys.
a. Map out the EE-R model to the equivalent logical Relational Model and define
the final schema
CS331 - Fall 2021 Project Assignment 2
b. Write SQL Create Table statements for each of these tables. Consider avoiding
the mistake of creating a table that already exists in the schema
c. Write foreign key constraints for the relationships in each of these tables. Make
your own assumptions regarding Cascading deletions or updates and justify those
assumptions (Hint you can combine the SQL for your answers to part B and C)
d. Write drop statements for all of your tables and schema at the end of the script
