Reference no: EM132541685

Part A Entity Relationship Modelling and Logical Database Design

Question 1 Identify Entities & Develop Business Rules
Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Each relationship must be described by two rules, one in each direction.
Each/A/An ENTITY_1 May/MustRelationship_Verb_Phrasenumber ENTITY_2

Question 2 Construct a logical Entity Relationship Diagram (ERD)
Based on the business rules developed in Question 1, construct a logical ERD for the case study using the
Visual Paradigm tool. (10 marks)
• Identify all attributes in each entity including all primary and foreign keys (any composite and multivalued attributes must be transformed according to the rules of the relational model).
• Identify ostrong and weak entities ostrong (identifying) and weak (non-identifying) relationships
• Verb phrases for relationships should be shown in both directions, except where they are adjacent to an associative entity, in which case only the verb phrases related to the bounding entities need to be shown.
• If super/subtypes are used, indicate whether the super-subtype relationships are disjoint or overlapping, and fully or partially specialised (complete or incomplete).
• Identify the connectivity for each relationship.
• Identify the mandatory/optional dependencies for the relationships.
• Resolve allM:N relationships.
• Describe any assumptions you have made in a text note in the diagram.

Question 3 Use Normalisation to validate your ERD and derive the relational schema
Use the technique of normalisation to validate the logical ERD data model that you developed in Question 2 is in third normal form.
• Select two entities from your ERD. For each of these entities, discuss how and why they are in 3NF:
oprovide justification using the concepts of functional dependencies and the steps involved with the first three normal formals.

Part B Database Implementation

In this part you should develop a database based on your logical ERD developed and validated in Part A. You must populate the tables with sample realistic data.

Question 4 Create tables
Create the tables in Oracle. Write SQL scripts defining each table.
• The table definitions should include
(1) All attributes with appropriate data types
(2) all appropriate constraints such as primary key, foreign keys, and check statements. All constraints must be given names. Naming standards must be used. Question 5 Populate data
• Insert a small sample of realistic test data (5 -10 rows min.) into each table. Refer to Appendix A of case study specification for examples of sample data. All test data must be meaningful in order to demonstrate your understanding of the data.

For Part B, you must include the following for each table in your database:
• SQL table creation script
• SQL insert script
• Sample data (include results of SELECT statements for each table)

Part C Construct SQL Queries

Question 6 Construct SQL Queries
Identify ten data retrieval and transaction requirements that would be used by the business described in the case study. For each of the requirements that you have identified, construct SQL queries needed to display the data. The queries must include a combination of the following SQL features:
• Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columns
• Use of comparison and logical operators
• Sorting output rows
• Multiple table join (Equijoin and Outerjoin)
• Single row and multiple row functions

Note: You may combine some of the preceding list of features in a single query. The design of each query should demonstrate your understanding of the correct usage of the various features and clauses of SQL. A simple ‘select * from table_name' will fetch you a mark of zero. There should be no more than two queries that involve a single table.
For Part C, you must include the following for each SQL query statement:
- Description of the purpose of the query.
- SQL SELECT query
- The result of the query.

Attachment:- Case Study for Database Project.rar