Relational Database Systems
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 (before 10 PM, April 21, 2016) and must include:
- What you will do with the given tasks: task 2, task 3 and the dates (timeline) by when they will be completed
- General overview of initial understanding of solutions to task 2 and task 3
- Identification of Literature Resources
Task 2: Scenario:
Party Palace has several party/conference halls where different events can be held. A party/conference hall is identified by a room number with other attributes like room name, room type, room description and room rate. Party/conference hall are being rented by customers to hold events (party, workshop, seminar, etc.) or may not be rented at all. When a customer wants to rent a party/conference hall, Party Palace will assign a unique number for the customer and will also records the customer name, contact number and address. A customer can rent at least one party/conference hall or any number of party/conference halls. Whenever a customer rent a party/conference hall(s) to hold an event, he/she will give an initial deposit and the expected number of audience/participants and will set the date and time of the event which will then be recorded by Party Palace. Every event requires exactly one caterer to manage the refreshments to be served to the audience/participants. Information about caterer is caterer code, caterer name, address and contact number. A caterer can provide service to one or more events or may not provide any service at all. All events held will be assisted by at least one employee of Party Palace. Information about employee is employee number, name, contact number and position. An employee may be assigned to assist one or more events held in any of the party/conference hall or may not be assigned.
a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.
b) For the task 2a, write your reflections describing the understanding of the following concepts in 300 to 400 own words by referring to literature. The literature can include Text book, Reference books, Journal/Conference papers/scholarly articles, web sources, etc.
i) Description of the ERD in your own words.
ii) Benefits of ERD.
iii) Applications of ERD in modeling real-world applications.
Task 3:
a) Normalize the below given Machine Performance Maintenance Report to First Normal Form, Second Normal Form and Third Normal Form.
Note: Machine Performance Maintenance (MPM) is being conducted quarterly for all the machines of every client. A machine can one appear once in every MPM report for a given MPM coverage. Every client/company can have many machines which belong to different departments. PM date column is the date when a specific machine is being checked for performance maintenance for a specific MPM report in a given MPM coverage.
b) For the task 3a, write your reflections describing the understanding of the following concepts in 300 to 400 own words by referring to literature. The literature can include Text book, Reference books, Journal/Conference papers/scholarly articles, web sources, etc.
i) Description of each normal form in your own words.
ii) Benefits of normalization.
iii) Applications of normalization in real-world.
Task 4: Be ready for a written viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the written viva will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the written viva.