COMP2400/6240 – Relational Databases
Assignment 1: Entity-Relationship Model, Functional Dependencies and Normalisation
Due date: 23:59pm, 1 October 2018
This assignment will be marked out of 10. It will count for 10% of the final grade. Below you will find 4 questions to reach this score. Marks are assigned for the process of finding a solution, not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution.
Instructions:
- This assignment should be done by a group of two students or individually. COMP2400 students can only be paired with other COMP2400 students. COMP6240 students can only be paired with other COMP6240 students.
- You must submit two files on Wattle before the due date:
- “u1234567-u7654321-ass2.pdf” (replace u1234567 and u7654321 with your UID and your group part- ner’s UID, respectively). Make sure you only upload the PDF file, not a Word or text file. This PDF file should contain the JPG figure as the EER diagram exported from TerraER.
- “u1234567-u7654321-ass2.xml” (replace u1234567 and u7654321 with your UID and your group part- ner’s UID, respectively). This is the XML file that corresponds to the EER diagram drawn using TerraER.
- For each group, you only need to submit a single copy of your assignment on Wattle under either your UID or your partner’s UID.
- Late submission is not granted under any circumstance. You will be marked on whatever you have submitted at the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself in a situation beyond your control that you believe significantly affects an assessment, you should follow the ANU’s special consideration process (http://www.anu.edu.au/students/program- administration/assessments-exams/special-assessment-consideration).
- Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this course is expected to be able to explain and defend any submitted assessment item. The course convener can conduct or initiate an additional interview about any submitted assessment item for any student. If there is a significant discrepancy between the two forms of assessment, it will be automatically treated as a case of suspected academic misconduct.
Question 1 4 Marks
A fitness center aims to setup a database to maintain the following information.
The database stores the name, the address and a unique ID of each employee. The employees are classified into the three categories: trainers, administrators and technicians. Each trainer has one or more specialties, e.g., weight gain, weight loss, strength building, etc. An administrator can help to register new members and can be reached through a landline phone number. Each member is identified by their email address. The fitness center organizes fitness classes for members. Each class has a name, a start date, an end date and a daily time slot. No two fitness classes have the same daily time slot, and for each fitness class the start date must be before the end date. A member may choose to participate in one or more fitness classes and may also choose one of the trainers as their personal trainer. Each fitness class is instructed by only one trainer and not all trainers deliver fitness classes. After receiving the approval from an administrator, a member may choose to add multiple associate members who will have partial access to the services provided by the fitness center, and an associate number (i.e., 1, 2, . . .) and the name of each associate member are recorded in the database. The database keeps track of all equipment available in the fitness center. Each piece of equipment has a unique ID
1
and must have one of the following three status types: new, working or broken. Each technician has a mobile phone number. Technicians maintain equipment in the fitness center and the database records the date and the report of each maintenance event. Each piece of equipment should be maintained at least twice a year.
1.1 Your task is to design an Enhanced Entity Relationship (EER) diagram for the above database, which should include entities, relationships, attributes and constraints wherever appropriate (you can make more assumptions if necessary). You also need to identify the requirements that cannot be captured in an EER-diagram.
Question 2 2 Marks
Consider the relation schema R={A, B, C, D, E} and the following set Σ of Functional Dependencies (FDs): •A→B
- AB → CD
- BC → D
- DE → A
- 2.1 What are the (candidate) keys of R? Justify your answer (i.e., show the main steps and your working used for finding all the keys). (1 Mark)
- 2.2 What is a minimal cover of Σ? Justify your answer (i.e., show the main steps and your working used for finding a minimal cover). (1 Mark)
Question 3 2 Marks
Consider the relation schema Teach={Course, Professor, Time, Room, Student, Grade} with the following set Σ of Functional Dependencies (FDs):
- Course → Professor
- {Time, Room} → Course
- {Time, Professor} → Room
- {Time, Student} → Room
- {Student, Course} → Grade
- 3.1 What are the prime attributes of Teach? Justify your answer (i.e., show the main steps and your working used for computing the prime attributes). (0.5 Mark)
- 3.2 Justify whether Teach is in BCNF with respect to Σ. If Teach is not in BCNF with respect to Σ, identify a BCNF decomposition for Teach (you need to show the main steps used for identifying this BCNF decomposition) and justify whether this BCNF decomposition is dependency preserving. (1.5 Mark)
Question 4 2 Marks
Consider the relation schema R={A, B, C, D} with an unknown set of functional dependencies:
4.1 What is the maximum number of (candidate) keys that R may have? Design a set of functional depen- dencies on R to achieve the maximum number of (candidate) keys. Justify your answers. (2 Mark)
+++++
2