The table shown below lists sample dentist/patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day. a. The table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies. b. State why the above table is in 1NF. c. Identify all candidate key(s) and your choice of primary key. d. Identify the functional dependencies in the 1NF table above based on the chosen primary key, including any partial and transitive dependencies. (If necessary, clearly state your assumptions). In answering this, you may use the format: fd#: attribute(s) on the left-hand side) (attribute(s) on the right-hand side (state whether full, partial or transitive dependency) example: fd1: (A1, A2) A3, A4, A5, A6 (full dependency) e. After removing partial dependencies, show all tables, with data, that are in 2NF. For each table, give the name and the primary key attribute, foreign key(s) if any, and which primary key of which table the foreign key(s) reference. f. After removing transitive dependencies, show all tables, with data, that are in 3NF. For each table, give the name, the primary key attribute, foreign key(s) if any, and which primary key of which table the foreign key(s) reference.
The table shown below lists sample dentist/patient appointment data. A patient is given an appointment at a specific time
and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a
specific surgery for that day.
a. The table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies.
b. State why the above table is in 1NF.
c. Identify all candidate key(s) and your choice of primary key.
d. Identify the functional dependencies in the 1NF table above based on the chosen primary key, including any
partial and transitive dependencies. (If necessary, clearly state your assumptions). In answering this, you may use
the format:
fd#: attribute(s) on the left-hand side) (attribute(s) on the right-hand side (state whether full, partial or
transitive dependency)
example: fd1: (A1, A2) A3, A4, A5, A6 (full dependency)
e. After removing partial dependencies, show all tables, with data, that are in 2NF.
For each table, give the name and the primary key attribute, foreign key(s) if any, and which primary key of
which table the foreign key(s) reference.
f. After removing transitive dependencies, show all tables, with data, that are in 3NF.
For each table, give the name, the primary key attribute, foreign key(s) if any, and which primary key of which
table the foreign key(s) reference.
To solve this problem, we will be using the rules of all normal forms and remove all those dependencies which violates the rules of normal form.
Below is the solution:
Trending now
This is a popular solution!
Step by step
Solved in 6 steps with 5 images
