So I have normalized the data but am not sure if it is correct. Here it is: The

Question

So I have normalized the data but am not sure if it is correct. Here it is:
The PK (primary key) is underlined once while the FK (foreign key) is underlined twice.
Could you please correct my mistakes? Thank you so much! 47. LEARNING ACTIVITTES 45 4.8.2 Normalization Exercise- Hotel Booking A family owsed hotel keps records ofs us,employes and rooms. Thetak repeatedly to their hotel. A small group of emplogees maintain and clean the roo Draw a logical ER dingram and then a physieal Guests come is guests, employees and rooms. They take in guest bookings rt guests have left. ER diagram from the information given. Show all cardinality 1. A guest can make more than one booking 2. A room can be occupied by a guest on many occasions 3. An employee maintains several rooms 4. A room can be maintained oe cleaned by any employee Hotel Rules 1. Same RoomCost applies to all guests 2. BookingID is unique for the hotel. Each room cocupancy has a unique BookingID. 3. Sample data shows bookings and maintenance for two rooms 4. Every time a guest leaves the room undergoes maintenance 5. One maintenance is done per Bookingtb. 6. A new BookingID is given to a guest who books more than one room at the same time. 7. Only one employee cleans a particular room. Aside: For simplicity, a convention if used for names, numbers and ID's 1. Guest names are chosen as five letters 2. GuestID is coded as X-999, X is the first leter of the gonsts first name 3. Employee names are four letters 4. RoonNumber is three numerals a room number is unique to the botel. first name 5. ExployeeID is coded as X-99, X is the first letter of an emplogees f Checkout inchudes date and time. The format is yyy-mm-dd, hhmm, 24 hour time o. checkin. Normalize the database to 3NF, Write the column namaes for each normal form; do not show data elements. Roomllumber, RoonType, RoosCost, (GuestID, Guestline, BookiagID, MaintenanceDate) checkin. Chackout, EmloyeeID, Esployeean Sample Data: 235, Single BR, 11200, (FS17, Fiwix, 4, 20 V385, Vitee, 7, 2014-06-21 01:30-00, 2014-06-26 1400:00, P29, Peec, 4-10-3007 30-00, 2014-11-02 09 -30:00, K35, Kiox, 2014-11-02 342, Suite, 180.00, (9624, Qusar, 6, 2014-11-07 18.30:00, 2014-11-111 Ars3t, Megit, 5, 201 1-06-29 18:30:00, 2014-07-01 19.30.00, G58, Gav, 2014-07Ge Gav 108, Double BR, 140.00, (F17, Fiwi, 12, 2015-01-07 12.3000, 2015-01-09 15.00 59, Gav, 2014-11-12 08:00-00 P29, Pee, 2015-01-10 08:00:00]

Explanation / Answer

We have 11 Eleven Columns to consider for normalization of Database.

They are RoomNumber, Room Type, Room Rent, Employee Id, Employee Name, Guest Id, Guest Name, Check In, Check Out, MaintenanceDate, Booking ID

NOTE : Table name is in Bold & Italic. Primary Key is Bold and Underline. Foreign Key is Underline

The Given database is in 1NF form as Booking ID is unique for every booking.

The Given database in 2NF will be :

BookingTable : BookingID, GuestID, EmployeeID, RoomNumber, CheckIn, CheckOut, Maintenance

GuestTable : GuestID, GuestName

EmployeeTable : EmployeeID, EmployeeName

RoomTabel : RoomNumber, RoomType, RoomRent

When we normalize it for 2NF we notice that it satifies the property of 3NF. Therefore the answer is

BookingTable : BookingID, GuestID, EmployeeID, RoomNumber, CheckIn, CheckOut, Maintenance

GuestTable : GuestID, GuestName

EmployeeTable : EmployeeID, EmployeeName

RoomTabel : RoomNumber, RoomType, RoomRent

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.