1. Design a normalized database and produce an ER database using WorkBench.

2. Add fields and data types as necessary.

3. Business Rules:

1. Only one dentist per patient per appointment.
2. Only one patient per appointment.
3. Only one patient or dentist per room per appointment.
4. A dentist cannot have more than one appointment per time period, but can have multiple appointments per day.
5. The dental office has 5 surgery rooms, numbered S01 to S05, which are shared by the three dentist.
6. The dental office has 4 offices for the dentist.
7. The dental office is planning to add a new dentist in the future.

A local dentist office keeps track of schedules using an Excel spreadsheet. Given the example spreadsheet below, design a database to handle scheduling.

 

Dentist Number	Dentist Name	Patient Number	Patient Name	Appointment Date Time	Surgery Room
S1011	Tony Smith	P100	Gillian White	9/12/98 10:00	S01
S1011	Tony Smith	P105	Jill Bell	9/12/1998 12:00	S04
S1024	Helen Pearson	P108	Ian MacKay	9/12/1998 10:00	S02
S1024	Helen Pearson	P108	Ian MacKay	9/14/1998 14:00	S02
S1032	Robin Plevin	P105	Jim Bell	9/14/1998 16:30	S01
S1032	Robin Plevin	P110	John Walker	9/15/1998 18:00	S05

Answer rating: 100% (QA)

To design a normalized database and produce an ER diagram for the given scenario we will follow thes... View the full answer