Database Management System (DBMS) Assignment Case Study – Normalisation of Dental Exam Relation

Total Marks: 10

Case Study Activity 1 (Normalisation)

Complete the following case study for the Dental Exam Relation. The conceptual level design for this relation has already been completed. Your job is to normalise this relation (It seems to still have some problems).

Case Study Overview

This entity records the dental tests requested by the patient’s dentist at each appointment. If no test is requested by the dentist, no record will be created. The complete Flag will only be set to ‘Y’ when the requested exam result has been returned.

Business Rules

• A patient is only allowed to visit their own Dentist.
• A patient may have more than one exam requested at any appointment.
• A patient can visit their dentist for examinations at different Clinics.
• Patients are only included in this database when they are required to undergo a dental examination.

Questions

1. The data provided in the Dental Exam table below is in ONF. Copy the table and make corrections till it is in 1NF. List each change you have made.
   • Enter all known values:
     • By filling in data instead of using dittos or “”
     • Fill any unknown values with the null word.
   • Ensure the intersection of every row and column contains only one value (known as atomic) by correcting multivalued attributes.
   • Check the table is in a rectangular format.
2. Write out the relational model for the single relation Dental Exam using the following format. (You should only have one table as no normalisation has been completed as yet).RelationName (Identifier, non-key attributes)
3. Identify and list the functional dependencies (FDs) for the given data. Please use the following format for your functional dependencies:X > Y or Determinant Attribute(s) > Dependent Attribute(s)
4. Transform the data from 1NF to 2NF for the Dental Exam relation. Ensure you use the FD identified to assist you in the normalisation process. Explain your result and write them in the following format. Ensure the primary keys are underlined and foreign keys are marked with asterisks (*):
   • RelationName (Identifier, non-key attributes)
   • or RelationName (Identifier, non-key attributes, Foreign Key*)
   • (Remove partial dependencies – attributes that only depend on part of the key.)
5. Transform the data from 2NF to 3NF for the Dental Exam relation. Ensure you use the FD identified to assist you in the normalisation process. Explain your result and write them in the following format. Ensure the primary keys are underlined and foreign keys are marked with asterisks (*):
   • Relation Name (Identifier, non-key attributes)
   • or Relation Name (Identifier, non-key attributes, Foreign Key*)
   • (Remove transitive dependencies – attributes that are dependent on another attribute.)
6. Transform the data from 3NF to BCNF for the Dental Exam relation. Ensure you use the FD identified to assist you in the normalisation process. Explain your result and write them in the following format. Ensure the primary keys are underlined and foreign keys are marked with asterisks (*):
   • RelationName (Identifier, non-key attributes)
   • or RelationName (Identifier, non-key attributes, ForeignKey*)
   • (Remove Boyce-Codd dependencies – e.g., For any functional dependency (A > B), A should be either the super key or the candidate key.)
7. Some patients have been seeing their dentist more than once per day. The Dental Clinic has decided to add a unique code for each appointment for one dentist. Shown in bold below:DentalExam (PatientNum, Appointment Date, Exam, Clinic Num*, CompleteFlag, Appointment Num)Is this now a correctly normalised relation? If it is not, please correct the relation and explain what you have done to correct it.
8. Write out the final normalised relational model for the DentalExam relations. Please use the following format for your final set of relations, where the primary keys are underlined and the foreign keys are marked with asterisks (*):
   • Relation Name (Identifier, non-key attributes)
   • or Relation Name (Identifier, non-key attributes, Foreign Key*)
   • Add surrogate keys where necessary by explaining their need in the relational model.