Table Normalisation #1 (20 marks) Assume that you own a small motel in Waihi Beach, which contains several different style units (e.g., one-bedroom studios, two-bedroom units etc.). Your business provides accommodation services to people who take short- term holidays in the beach. You want to design and build a database to keep track of the bookings and the money earned. The start of your revenue cycle is that one guest reserves a unit in the motel for his/her vacation with families and friends. The reservation is made for specific days. For the reserved unit, each day is associated with a specific cost. In other words, the price per day can be different on different days and for different units. You do not charge extra for additional guests as long as the total guest number does not exceed the maximum number allowed in the unit. The total stay cost must be paid at the same time of reservation. Guests then come to the beach, stay in the reserved unit and enjoy their holiday. As you want to keep track of the overall satisfaction associated with each guest stay, the satisfaction rating forms are available in the units. Customers can fill out the form when staying in the motel unit. After customers check out, one of your employees cleans the unit. The employee also checks the unit condition during the cleaning. You keep a record of the condition of each unit after each guest stay. Assume that there are no cancellation cases. Assume that there are no cancellation cases. Time left 0:46:15 The REA diagram for the above process is provided, and the required database attributes are also listed below. |' Guest Stay Appliance ' Clean 8: Check 7 \\ Note: Price rate is not a real. \"entity\" but needed as d tdbLe in the I D Employee Customer L (Click here to download the REA diagram if you like) Database Attributes: The database tables that you create for the business process must contain attributes for the following information (Note that some \"information\" may require multiple attributes; one attribute is one field/column in a specific table): Customer# Customer name Customer phone number Customer email Customer address Employee# Employee name Employee# lH Employee name Employee position Date when booking is made and payment is received Dates the unit has been reserved (i.e. the booked check-in and check-out dates) Total amount charged for the reservation Dates actually stayed by guests Satisfaction rating for each guest stay Date that cleaning is done Condition of each unit after each guest stay Bank account# Bank balance Bank name and address Unit# Unit style Maximum guest number allowed in the unit Description on the features of the unit Price rate# Price rate for each unit by date Description of the appliance in each unit Current condition of each appliance Required: Design the necessary tables to implement a relational database for the REA diagram provided. All required attributes need to be included in the appropriate tables. Create new primary and foreign keys when necessary for specific tables (if not listed in the question). Remember to indicate all of mMy and foreignkeys. Your designed tables should not violate any normalisation rules. Use the following notation: Tables in ( ) Primary keys Bold Foreign keys in Italics