CSCI235 – Database Systems
2024 S4
Implementation Task 1
Due on 22 October 2024 by 9:00 pm SGT
Scope
The Implementation of Task 1 is related to the contents of the topic of functional dependency and normalization.
This Implementation is due by Tuesday, 22 October 2024, 9:00 pm Singapore time. This task is worth 3% of the total assessment for the subject.
Only electronic submission through Moodle at:
https://moodle.uowplatform.edu.au/
is accepted. All email submission will be deleted and mark 0 (“zero”) will be awarded.
The submission procedure is explained at the end of this specification.
Specification
Task 1 (1.5 marks)
1. Consider a relational schema R(A, B, C, D, E) and the following set of functional dependencies valid in the schema:
(i) {A → B, C → D, E → A}
List all minimal keys valid in the schema. List all derivations of function dependencies that lead to the identification of minimal super keys. Note, a schema can have more than one minimal super key. (0.5 mark)
(ii) {A → B, B → CD, C → E, E → AC}
List all minimal keys valid in the schema. List all derivations of function dependencies that lead to the identification of minimal super keys. Note, a schema can have more than one minimal super key. (0.5 mark)
(iii) {A → B, DB → C}
List all minimal keys valid in the schema. List all derivations of function dependencies that lead to the identification of minimal super keys. Note, a schema can have more than one minimal super key. (0.5 mark)
Deliverable
A file Task1Solution.pdf with the solutions of the problems included in Task 1.
Submission of a file with a different name and/or different extension and/or different type scores no marks!
Task 2 (1.5 marks)
Read the following specification of sample database domain.
The Smiley Dental chain operates multiple clinics across Singapore. The following conditions apply:
• Each clinic serves many patients, and patients may visit different clinics.
• Each clinic employs its own full-time dentists.
• Upon registration, a patient is assigned to a specific dentist, who will attend to the patient in all subsequent visits.
• A dentist may have multiple patients assigned to them.
Smiley Dental stores information about clinics, patients, and dentists in the following relational table:
ClinicPatient(Clinic, Patient, Dentist)
Tasks:
i. Identify and list all functionalities described above.
ii. Identify the minimal super keys.
iii. Normalized the relational tables to BCNF. (1.5 mark)
Submissions
This assignment is due by 9:00 pm (21:00 hours) Tuesday, 22) October 2024, Singapore time.
Submit the files Task1Solution.pdf and Task2Solution.pdf through Moodle in the following way:
1) Access Moodle at http://moodle.uowplatform.edu.au/
2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page
3) When successfully logged in, select a site CSCI235 (SP424) Database Systems
4) Scroll down to the section “Implementation Task 1 Submission link” and click at the link.
5) Click at a button Add Submission
6) Move the file that you want to submit into an area of submission. If you have more than one files to submit, you can drag and drop the remaining files here to add them. You can also use a link Add…
7) Click at the button “Save changes”,
8) Click at the check box to confirm authorship of your submission and click at the button “Continue” .
9) When you are satisfied, remember to click at a button Submit assignment.