Relational Database Systems
Objective: This is an individual assignment aimed to give the student exposure in understanding, designing, building and analyzing database systems for a given real-time based scenario and be able to conduct optimal strategies for efficient management of databases.
Intended Learning Outcomes covered:
1. Design the logical structure of a database using Entity-Relationship diagram.
2. Apply normalization techniques to reduce redundancy in a database.
Task 1:
Complete the work proposal in Microsoft Word file format (may include possible answers based on your initial understanding). Work proposal for the assignment must be submitted before the end of week 7 and must include:
a) Understanding of deliverables - a detail description of deliverables.
b) General overview of proposed plan - initial understanding of solution to task 2 which includes, the name of entities, associative entities and relationships. Initial understanding of solution to task 3.
c) Timeline for completion of task 2 and task 3.
The work proposal must be submitted in a word file through the link available in Moodle.
Task 2:
Scenario:
Fun Studio provides exciting games, exciting rides for children in Oman. It offers several games to entertain its customers. Each game has an ID, name, duration. Once a customer visits the center, the Customer Relation Officer will record the details of the customer such as name, contact number, address and residence ID number, which is unique to every customer. Customer can choose any number of games or he may not choose any game, a game can be chosen by at least one customer. Whenever a customer chooses a game, the date and score will be recorded to calculate the credits of the customer. The recreation center offers different royalty programs such as diamond, platinum, gold, and silver to all its customers. Each program has program code, name, term and price. A customer can buy any number of royalty programs, or may not buy one. A royalty program will be bought by at least one customer; otherwise the center cancels the program. When a customer buys a program, the center wishes to record the date to maintain the term of the program.
a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, associative entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.
b) Analyze the above given scenario and discuss about the possible structure of the relations (the number and name of the columns) and the referential integrity constraints to be implemented in the above design.
Task 3:
a) Normalize the below given Attendance Summary Report to First Normal Form, Second Normal Form and Third Normal Form. Make assumptions for the identification of the primary key wherever necessary.
b) Discuss how the data consistency can be maintained during normalization for each normal forms. Support your answer with reference to the above Question.
Task 4
Be ready to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the demonstration will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the satisfactory pass for the demonstration in task 4.