You are given a task to create a Database for Holiday Inn. Here is some of the information you have.

HOTEL

hNum: It is a unique id assigned to each hotel

hNoOfRooms: Number of rooms in a hotel

hPhone: It is the hotel phone number

hAdd: It is the hotel address

ROOM

rNum: It is the room number in a hotel

rRate: It is the rate of a room in a hotel per night

rSize: It is an integer to indicate the size of the room (ex: 2 means two bedroom)

rOccupied: It has a value of True or False to indicate if the room is occupied in a hotel

rLoc: It refers to the location of a room in a hotel (ex: 2 means second-floor)

MANAGER

mId: It is the emp id of the hotel manager

mName: It refers to the name of the manager who manages the hotel

mAdd: It refers to the address of the manager

mPhone: It is the personal cell phone number of the manager (not the hotel phone Num)

mEmail: It is the email of the manager

mSal: It is the salary of the manager

CARETAKER

ctId: It is the emp id of the hotel care taker

ctName: It is the name of the caretaker who takes care of the maintenance of a hotel

ctAdd: It refers to address of the caretaker

ctPhone: It is the personal cell phone number of the caretaker

ctEmail: It refers to email of the caretaker

ctSal: It is the salary of the caretaker

CUSTOMER

cNum: It is the customer number of a customer who is staying in a hotel

cSSN: It is the SSN of the customer

cName: It is the name of the customer

cCreditCart: It is the credit cart number of the customer

cAdd: It is the personal address of the customer

cStartDate: It indicates the date when the customer has checked in

cEndDate: It indicates the date the customer checks out.

cAmtOwing: It is the amount the customer owes to the hotel

Assumptions:

-Since you are creating this database for the Holiday Inn Hotel, you do not have to give any name to a specific hotel. They are all called Holiday Inn. Holiday Inn has several branches in North America. Each branch is uniquely identified by its hNum. In fact hNum attribute refers to the branch number of the hotel

-Each manager may manage more than one branch of the Holiday Inn, but each hotel is managed by only 1 manager

-Each customer can stay in one hotel and can only be given one room in that hotel

-Each caretaker works for only one hotel but a hotel can have many caretakers.

-Rooms in a particular hotel are unique but may not be unique across all branches. For example, branch 10 has only one room with room number 100 but branch 20 may also have a room number 100. These rooms may be completely different in terms of size, rate, etc.

A)Draw your ERD based on the above assumptions and data

B)Draw tables from the initial ERD and normalize them. Place all tables in 3^rd normal form (if necessary)

C)Revise your ERD (if necessary)

D)Create your tables based on the given types and restrictions using the following rules:

a.Create all table primary key constraints on the Create Table statement. Add all Foreign Key constraints after all tables have been created using the Alter Table statement.

b.Beside the constraint you use for the primary and foreign keys, add the following constraints (on the Create Table statement):

hNoOfRooms: > 0 and

Phone: unique and should be >= 2202222 and

rNum: > 0 and

rRate: >= 50

rSize: >= 2 and

rOccupied: = false or = true

rLoc: > 0 and

mId: >= 111111

mPhone: >= 2222222 and

ctId: >= 111111

ctPhone: >= 2222222 and

ctSal: > 20000 and

cSSN: unique and > 660000000 and

TYPE DATA ITEM (COLUMN NAME) hNum hNOOfRooms hPhone hAdd [INum [tRate [iSize [rOccupied [Floc [mld [mName (mAdd [m Phone New : mEmail [msal [ctld [ctName [ctAdd ctPhone [ctEmail [ctsal [CNum (cSSN [cName cCreditCart CAdd cStartDate [cEndDate New: CAmtowing Integer Integer Fixed Character 7 Character Up to 50 Integer Decimal 5 with 2 decimals Integer Fixed Character i s Integer Integer Character Up to 50 Character Up to 50 Fixed Character 7 Character Up to 100 Decimal 7 with 2 decimals Integer Character Up to 50 Character Up to 50 Fixed Character 7 Character Up to 200 I Decimal 7 with 2 decimals Integer Fixed Character 9 Character Up to 50 Character Up to 25 Character Up to 50 DATE 1 DATE 1 Decimal 7 with 1 decimals TYPE DATA ITEM (COLUMN NAME) hNum hNOOfRooms hPhone hAdd [INum [tRate [iSize [rOccupied [Floc [mld [mName (mAdd [m Phone New : mEmail [msal [ctld [ctName [ctAdd ctPhone [ctEmail [ctsal [CNum (cSSN [cName cCreditCart CAdd cStartDate [cEndDate New: CAmtowing Integer Integer Fixed Character 7 Character Up to 50 Integer Decimal 5 with 2 decimals Integer Fixed Character i s Integer Integer Character Up to 50 Character Up to 50 Fixed Character 7 Character Up to 100 Decimal 7 with 2 decimals Integer Character Up to 50 Character Up to 50 Fixed Character 7 Character Up to 200 I Decimal 7 with 2 decimals Integer Fixed Character 9 Character Up to 50 Character Up to 25 Character Up to 50 DATE 1 DATE 1 Decimal 7 with 1 decimals