Database ER Model & Normal Forms

(Right Click on the Image -> Then Click on "Open Image In New Tab" for full resolution image)

Business Rules:

Each reservation belongs to a specific customer; customers may or may not have any reservations.

Each customer is a specific type; some customer types may not have any customers.

Each reservation is booked by a specific agent; each agent may or may not have booked any reservations.

Each agent works for a specific department; each department has at least one agent (employee).

Each reservation is for one or more rooms, all with the same rate type within the same reservation, but rates types may vary from reservation-to-reservation; some rooms may not have any reservations.

Based on the information provided above, complete the following:

1. FIRST NORMAL FORM (1NF):

1. Decompose the composite attributes into simple attributes.
2. Convert the table above to 1NF (eliminate repeating groups of data and select an appropriate PK).
3. Show the table structure format (table name with PK and all dependent attributes in parentheses).
4. Create a dependency diagram for the table above.

2. SECOND NORMAL FORM (2NF):

1. Show the table structure format for each table in 2NF.
2. Create the dependency diagrams for the resulting tables.

3. THIRD NORMAL FORM (3NF):

1. Convert to 3NF and show the table structure format for each table.
2. Create the dependency diagrams for the resulting tables.

4. ENTITY-RELATIONSHIP MODEL:

1. Create a relational schema showing all 3NF tables (entities), their attributes, and the connectivity of the relationships. Each entity should be depicted as a rectangle with the name and the top and the attributes listed under the name. This may be done in Access.
2. Using Chen notation, create an ERD showing all of the 3NF tables above. You must show the entities, relationships, connectivity, participation, and cardinality (it is not necessary to show the attributes on the ERD).

Microsoft Visio or other diagramming software is recommended to create the required diagrams.

Res Num Check In Check Out Cust ID Cust Name Cust Phone Cust Type Cust Desc Loyalty ID Agent ID Agent Name Agent Type Agent Desc Guest Count Room Num Room Type Room Desc Rate Type Rate Desc Rate Amt 1001 9/5/2020 9/7/2020 Wesley Tanner FD 85 (817)555- 1193 Corporate 323 Megan Smith 20 FD Front Desk 2 224 K King Bed Corporate $120 225 D C Corporate $125 2 Double Beds 2 Double 1002 9/1/2020 9/10/2020 100 Breanna Rhodes (214)555- 9191 1 Individual 129 5 Janice May T Telephone 4 305 D S Standard $149 Beds 1003 9/9/2020 9/11/2020 15 Jeff Miner 1 Individual 14 John King RC Online Res Center 3 409 D 2 Double Beds W Weekend $99 1004 9/12/2020 9/14/2020 77 Kim Jackson (817)555- 4911 Corporate 210 28 Ray Schultz T Telephone 4 320 D 2 Double Beds Corporate $110 409 D 2 Double Beds Corporate $105 321 K King Bed Corporate $112 1005 9/15/2020 9/18/2020 119 Mary Vaughn (817)555- 2334 | Individual 118 20 Megan Smith FD Front Desk 1 302 K King Bed S Standard $139 1006 9/24/2020 9/26/2020 97 Chris Mancha (469)555- 3440 1 Individual 153 14 John King RC Online Res Center 2 501 KS King Suite w Weekend $119 502 KS King Suite W Weekend $119 1007 9/20/2020 9/25/2020 100 Breanna Rhodes (214)555- 9191 1 Individual 129 20 Megan Smith FD Front Desk 2 302 King Bed S Standard $139 (817)555- Janice 1008 10/1/2020 10/3/2020 85 Wesley Tanner Corporate 323 5 T Telephone 3 320 D 2 Double Beds W Weekend $89 1193 May 321 K King Bed W Weekend $99 1009 10/1/2020 10/4/2020 28 Renee Walker (214)555- 9285 E | Individual 135 14 John King Rc RC Online Res Center 2 502 KS King Suite Weekend $129 409 D 2 Double Beds W Weekend $129 1010 10/1/2020 10/9/2020 23 Shelby Day | Individual 28 Ray Schultz T Telephone 4 225 D 2 Double Beds W Weekend $109