In this course, you will design, develop and implement a relational database for the case study assigned to you (go to the course web site to download). The required database should be developed according to the following stages: 1. Develop a conceptual data model reflecting the following requirements: • Identification of the relations (entity types). • Identification of relationship types and their participation and cardinality constraints. • Identification of attributes and association of attributes with entity or relationship types. • Determination of candidate and primary key attributes of entity types. • Determination of specialization/generalization and categorization relationships, whenever it is appropriate. • Enhanced Entity-Relationship (EER) diagram to reflect the requirements. 2. Develop a logical data model based on the following requirements: • Refinement of the conceptual model - including a refined Enhanced Entity-Relationship (EER) diagram. • Derivation of relations from the refined conceptual model. • Validation of logical model using normalization to BCNF. • Validation of logical model against corresponding user transactions.
• Definition of integrity constraints including: — Primary key constraints. — Referential Integrity (foreign key) constraints. — Entity integrity (NULL and default value) constraints. — Alternate key constraints. — General constraints if any. 3. Translate the logical data model for the ORACLE Enterprise DBMS. • Development of SQL code to create the entire database schema and reflect its constraints. • Development of the case study application to interact with the database and enable the transaction requirements using embedded SQL. 4. Interact with the database and enable the transaction requirements. You need to create several sample tuples for each relation in your database. Report: The report should include a detailed typed documentation of the project's stages, results at each stage, test data, sample output, and conclusion. For example. you should include the EER diagrams for the conceptual data model and the logical data model.
Sample Solution