Table 4.1 lists patient appointment data with the dentists. A patient is given an appointment with a dentist at a specific time, date and a surgery room. On each day of patients' appointment, a dentist is allocated to a specific surgery room (i.e. column surgeryNo) for that day.
Table 4.1 lists patient appointment data with the dentists. A patient is given an appointment with a dentist at a specific time, date and a surgery room. On each day of patients' appointment, a dentist is allocated to a specific surgery room (i.e. column surgeryNo) for that day.
Database Systems: Design, Implementation, & Management
12th Edition
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Carlos Coronel, Steven Morris
Chapter7: Introduction To Structured Query Language (sql)
Section: Chapter Questions
Problem 97C: The following tables provide a very small portion of the data that will be kept in the database....
Related questions
Question
100%
Subject:
Topic: Normalization
Transcribed Image Text:Table 4.1 lists patient appointment data with the dentists. A patient is given an appointment
with a dentist at a specific time, date and a surgery room. On each day of patients'
appointment, a dentist is allocated to a specific surgery room (i.e. column surgeryNo) for that
day.
Table 4.1: Dentist/Patient Appointment Data
staffNo
dentistName
Patient PatientName appointment appointment surgeryNo
No
Time
09 Nov 2014 8.30 am
Date
s1011 Ahmad
P101
Foon Yew
S15
Zulhilmi
s1011
Ahmad
P105
Saodah
09 Nov 2014 9.30 am
S15
Zulhilmi
Hathim
s1022 Plevin Cruze
s1022 Plevin Cruze
s1030 Mary Anne
P108
Winstor Yee
09 Nov 2014
8.30 am
s10
2.00 pm
11 Nov 2014 3.00 pm
P108
Winstor Yee
11 Nov 2014
s10
P105
Saodah
S15
Hathim
S1030 Mary Anne
P106
Siti Sabariah 12 Nov 2014 | 4.00 pm
S13
Based on the above explanation, answer the following questions:
a)
Table 4.1 is susceptible to update anomalies. Provide ONE example for each of
insertion, deletion, and update anomalies.
b)
Identify in what level of Normal Form the Table 4.1 is in. Justify your answer.
c)
Identify the Primary Key (PK) for Table 4.1.
d)
Identify all functional dependencies that can be derived from the Table 4.1.
e)
Illustrate the process of normalizing Table 4.1 up until Boyce Codd Normal Form
(BCNF) level. State any assumptions you make about the data shown in the table.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
Programming Logic & Design Comprehensive
Computer Science
ISBN:
9781337669405
Author:
FARRELL
Publisher:
Cengage