Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Description: The table shown in Figure 1 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 shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies. (b) Identify the functional dependencies represented by the attributes shown in the table of Figure 1. State any assumptions you make about the data and the attributes shown in this table. (c) Describe and illustrate the process of normalizing the table shown in Figure 1 to 3NF relations. Identify the primary, alternate, and foreign keys in your 3NF relations. Note: in order to normalize for 3NF you should do the INF, and 2NF Notes: staffNo dentistName patNo patName $1011 $1011 $1024 $1024 $1032 $1032 Tony Smith P100 Tony Smith P105 Helen Pearson P108 Helen Pearson P108 P105 Robin Plevin Robin Plevin P110 time Ian MacKay Ian MacKay 12-Sep-13 10.00 $15 12-Sep-13 12.00 $15 12-Sep-13 10.00 $10 14-Sep-13 14.00 $10 14-Sep-13 16.30 $15 15-Sep-13 18.00 $13 Jill Bell John Walker Figure 1: Table displaying sample dentist/patient appointment data appointment surgeryNo date Gillian White Jill Bell 1. You should include the design while you are doing normalization. 2. Insert at least two records after normalizing for 3NF in each entity.

Description: The table shown in Figure 1 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 shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies. (b) Identify the functional dependencies represented by the attributes shown in the table of Figure 1. State any assumptions you make about the data and the attributes shown in this table. (c) Describe and illustrate the process of normalizing the table shown in Figure 1 to 3NF relations. Identify the primary, alternate, and foreign keys in your 3NF relations. Note: in order to normalize for 3NF you should do the INF, and 2NF Notes: staffNo dentistName patNo patName $1011 $1011 $1024 $1024 $1032 $1032 Tony Smith P100 Tony Smith P105 Helen Pearson P108 Helen Pearson P108 P105 Robin Plevin Robin Plevin P110 time Ian MacKay Ian MacKay 12-Sep-13 10.00 $15 12-Sep-13 12.00 $15 12-Sep-13 10.00 $10 14-Sep-13 14.00 $10 14-Sep-13 16.30 $15 15-Sep-13 18.00 $13 Jill Bell John Walker Figure 1: Table displaying sample dentist/patient appointment data appointment surgeryNo date Gillian White Jill Bell 1. You should include the design while you are doing normalization. 2. Insert at least two records after normalizing for 3NF in each entity.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question
100%
Description: The table shown in Figure 1 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 shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies. (b) Identify the functional dependencies represented by the attributes shown in the table of Figure 1. State any assumptions you make about the data and the attributes shown in this table. (c) Describe and illustrate the process of normalizing the table shown in Figure 1 to 3NF relations. Identify the primary, alternate, and foreign keys in your 3NF relations. Note: in order to normalize for 3NF you should do the INF, and 2NF Notes: staffNo dentistName patNo patName appointment surgeryNo date time Gillian White Jill Bell P100 P105 Helen Pearson P108 Helen Pearson P108 Robin Plevin P105 Robin Plevin P110 Ian MacKay Ian MacKay Jill Bell 12-Sep-13 10.00 $15 12-Sep-13 12.00 $15 12-Sep-13 10.00 $10 14-Sep-13 14.00 $10 14-Sep-13 16.30 $15 15-Sep-13 18.00 $13 John Walker Figure 1: Table displaying sample dentist/patient appointment data $1011 Tony Smith $1011 Tony Smith $1024 $1024 $1032 $1032 1. You should include the design while you are doing normalization. 2. Insert at least two records after normalizing for 3NF in each entity.
Transcribed Image Text:Description: The table shown in Figure 1 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 shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies. (b) Identify the functional dependencies represented by the attributes shown in the table of Figure 1. State any assumptions you make about the data and the attributes shown in this table. (c) Describe and illustrate the process of normalizing the table shown in Figure 1 to 3NF relations. Identify the primary, alternate, and foreign keys in your 3NF relations. Note: in order to normalize for 3NF you should do the INF, and 2NF Notes: staffNo dentistName patNo patName appointment surgeryNo date time Gillian White Jill Bell P100 P105 Helen Pearson P108 Helen Pearson P108 Robin Plevin P105 Robin Plevin P110 Ian MacKay Ian MacKay Jill Bell 12-Sep-13 10.00 $15 12-Sep-13 12.00 $15 12-Sep-13 10.00 $10 14-Sep-13 14.00 $10 14-Sep-13 16.30 $15 15-Sep-13 18.00 $13 John Walker Figure 1: Table displaying sample dentist/patient appointment data $1011 Tony Smith $1011 Tony Smith $1024 $1024 $1032 $1032 1. You should include the design while you are doing normalization. 2. Insert at least two records after normalizing for 3NF in each entity.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Dataset
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.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS