Database Management: ER Diagram, Data Structures, and Schema Design

This document presents a comprehensive solution to a database management assignment. It begins with an Entity Relation (ER) diagram illustrating the relationships between various entities such as Guests, Staff, Rooms, Bookings, Payments, and related tables. The solution then details the relational data structures, specifying the attributes and data types for each table, including Guests, Staff, Supervisor, Rooms, Bookings, Payments, RoomTypes, RoomPrices, RoomFacilities, FacilitiesList, ServiceCharges, BookingCharges, PaymentMethods, and GuestNotes. Finally, the document provides a relational database schema, defining each table's fields, data types, and primary/foreign key relationships, offering a complete and structured approach to database design and implementation. This assignment is designed to demonstrate understanding of database principles, including the design of the ER diagram, relational data structures, and database schema.

Database Management 1
Database Management
Student’s Name
Course Name
Lecturer
University
Date

Database Management 2
Table of Contents
Entity relation diagram.............................................................................................................................................................................3
Relational data structures.........................................................................................................................................................................3
Relational database schema......................................................................................................................................................................4

Database Management 3
Entity relation diagram
Assumptions
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)

Database Management 4
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
Table Name Field Type Numbe
r
Description
Guests guestID Int Primary Key
title Varchar 7
fname Varchar 10
lname Varchar 20
address Varchar 50
email Varchar 50
mobile_number Int
home_number Int
Work_number Int
Drivers_license_n
o
Int
Passport_no Int
Date_entered Date
Loyalty_card_no Int
Staff staffID Int Primary Key
Fname Varchar 10
Lname Varchar 20
Address Varchar 50
Phone Int
Employment_type Varchar 20
TFN Int
Login_name Varchar 20
password Varchar 20
Supervisor supervisorID Int Primary Key
staffID Int Foreign key references

Database Management 5
staff.staffID
Rooms roomNO Int Primary Key
Room_rate Int
Status Boolea
n
typeID Int Foreign key references
roomtypes.typeID
Bookings bookingNo Int Primary Key
Booking_date Date
Logged_time Time
Checkin_date Date
Checkin_time Time
Checkout_date Date
Checkout_time Time
Cancellation_date Date
Room_guarantee Char 1
No_of_occupants Int
Duration Int
guestID Int Foreign key references
guests.guestID
roomNo Int Foreign key references
rooms.roomNo
Payments paymentNo Int Primary Key
Amount Int
Payment_date Date
bookingID Int Foreign Key references
bookings.bookingID
methodID Int Foreign Key references
paymentmethods.methodI
D
RoomTypes typeID Int Primary Key
Name Varchar 20
Description Text

Database Management 6
RoomPrices rpriceID Int Primary Key
price Int
typeID Int Foreign key references
roomtypes.typeID
RoomFacilities rfacilityID Int Primary Key
facilityID Int Foreign key references
facilitieslist.facilityID
typeID Int Foreign key references
roomtypes.typeID
FacilitiesList facilityID Int Primary Key
name
ServiceCharges chargeID Int Primary Key
Unit_price Int
GST Int
service Varchar 20
BookingCharges bchargeID Int Primary Key
bookingID Int Foreign Key references
bookings.bookingID
chargeID Int Foreign Key references
servicecharges.chargeID
PaymentMethod
s
methodID Int Primary Key
name Varchar 30
GuestNotes noteID Int Primary key
Entry_date Date
Entry_time Time
Details Text
Followup_date Date
Completed_date Date
guestID Int Foreign key references
guests.guestID
staffID Int Foreign key references