ISY103 Database Management for Business

Published : 30-Sep,2021 | Views : 10

Question:

When designing the database the manager has asked that you consider questions that may be asked of the database:

Howmany rooms are currently available for booking Whatfacilities are available in particular rooms WhichGuests are booked in this week.Howclear and well organised your report is E- Adherence to the unit standard, assumptions made, inclusion of correct primary and foreign keys, appropriate entities, relationships, and attributes.

Answer:

Assumptions made include:

The tables roomtype an roomprice have a one-to-one relationship as each room type has only one price.
The tables roomtype and roomfacilities have a one-to-many relationship as one type of room may have many facilities.

Relational data structures

Guests(guestID, title, fname, lname, address, email, mobile_number, home_number, work_number, drivers_license_no, passport_no, date_entered, loyalty_card_no)

Staff(staffID,fname,lname,address,phone,employment_type, TFN, login_name, password)

Supervisor(supervisorID,staffID)

Rooms(roomNo,room_rate,status, typeID)

Bookings(bookingNo,booking_date,logged_time, checkin_date, checkin_time,checkout_date,checkout_time,cancellation_date,room_guarantee,no_of_occupants,duration, guestID,roomNo)

Payments(paymentNo,amount,payment_date, bookingID, methodID)

RoomTypes(typeID, name, description)

RoomPrices(rpriceID,price, typeID)

RoomFacilities(rfacilityID,facilityID,typeID)

FacilitiesList(facilityID,name)

ServiceCharges(chargeID,unit_price,GST,service)

BookingCharges(bchargeID,bookingID,chargeID)

PaymentMethods(methodID,name)

GuestNotes(NoteID,entry_date,entry_time,details,followup_date,completed_date,guestID,staffID)

Relational database schema

Field	Type	Number
title	Varchar	7
fname	Varchar	10
lname	Varchar	20
address	Varchar	50
email	Varchar	50
Fname	Varchar	10
Lname	Varchar	20
Address	Varchar	50
Employment_type	Varchar	20
Login_name	Varchar	20
password	Varchar	20