ISY103 Database Management for Business Hotel Database Modelling

Question: Discuss about the Database Management for Business ISY103. Answer: ER diagram Relations and table structure GUEST (guestID, title, gName, streetNo, city, postcode, email, drivingLicense, passportNo, dateOfRegistration, loyaltyCardNo) Table Name Field Type Description GUEST guestID Number Primary Key title Char(3) gName Char(30) streetNo Varchar(40) city Char(10) Postcode Number email Varchar(255) drivingLicense Varchar(10) passportNo Varchar(10) dateOfRegistration DATE Format: dd/mm/yyy loyaltyCardNo Varchar(10) PHONE_TYPE (phoneType) Table Name Field Type Description PHONE _TYPE phoneType Char(10) Primary Key GUEST_PHONE (phoneType, guestID, phoneNo) Table Name Field Type Description GUEST_PHONE phoneType char(10) Foreign Key refers to PHONE_TYPE. phoneType guestID Number Foreign Key refers to GUEST.guestID phoneNo Number STAFF_TYPE(staffType) Table Name Field Type Description STAFF_TYPE staffType Char(10) Primary Key STAFF (TFN, address, phone, supervisor, staffType, login, password) Table Name Field Type Description STAFF TFN Number Primary Key address Varchar(30) Phone Number Supervisor Number Fpreign key refers to STAFF.TFN staffType Char(10) Fpreign key refers to STAFF_TYPE.staffType Login Varchar(12) Password Varchar(20) NOTE(noteID, entryDateTime, guest,userInitials, description, staff, followUpDate, completedDate) Table Name Field Type Description NOTE noteID Number Primary Key entryDateTime DATETIME guest Number Foreign Key references to GUEST.guestID userInitials Char(2) Description Varchar(20) Staff Number Foreign Key references to staff.TFN followUpDate DATE completedDate DATE PRICES (priceCode, price) Table Name Field Type Description PRICES priceCode Char(10) Primary Key price Double ROOM_PRICE (priceCode, roomNo, bookingID) Table Name Field Type Description ROOM_PRICE priceCode Char(10) Primary Key; Foreign Key refers to PRICES.priceCode roomNo Number Primary Key; Foreign key refersto ROOM.roomNo bookingID Number Primary Key; Foreign key refersto BOOKING.bookingID ROOM_TYPE (roomType) Table Name Field Type Description ROOM_TYPE roomType Char(10) Primary Key FACILITES (facilityCode, description) Table Name Field Type Description FACILITES facilityCode Char(10) Primary Key; Description Varchar(30) ROOM_FACILITY (roomNo, facilityCode) Table Name Field Type Description ROOM_FACILITY roomNo Char(10) Primary Key; Foreign key refers to ROOM.roomNo facilityCode Varchar(30) Primary Key; Foreign key refers to FACILITY. facilityCode ROOM (roomNo, roomType, defaultRate, vacant) Table Name Field Type Description ROOM roomNo Number Primary Key; Foreign key refers to ROOM.roomNo roomType Char(10) Primary Key; Foreign key refers to ROOM_TYPE. roomType defaultRate Double Vacant Char(1) PAYMENT_METHOD (paymentMethod) Table Name Field Type Description PAYMENT_METHOD paymentMethod Char(10) Primary Key PAYMENT (paymentID, booking, amount, paymentDate, paymentMethod) Table Name Field Type Description PAYMENT paymentID Number Primary Key Booking Number Foreign Key refers to BOOKING.bookingID Amount Double paymentDate DATE Format: dd/mm/yyy paymentMethod Char(10) Foreign Key refers to PAYMENT_METHOD.paymentMethod SERVICES (serviceCode, description) Table Name Field Type Description SERVICES serviceCode Char(10) Primary Key Description varchar(40) SERVICE_CHARGE(bookConfNo, serviceCode, unirPrice, GST, description) Table Name Field Type Description SERVICE_CHARGE bookConfNo Number Primary Key; Foreign Key refers to Booking.bookingID serviceCode Char(10) Primary key Foreign key refers to SERVICE_CODE.serviceCode unitPrice Double GST Number Description varchar(40) BOOKING (bookConfNo, bookingDate, expcheckInDateTime, expcheckOutDateTime, roomNo, roomGuaranted, cancellationDate, noOfOccupants, [noOfnights]) Table Name Field Type Description BOOKING bookConfNo Number Primary Key bookingDate DATE Format: dd/mm/yyy expcheckInDateTime DATETIME Format: dd/mm/yyy HH:MM:SS expcheckOutDateTime DATETIME Format: dd/mm/yyy HH:MM:SS roomNo Number Foreign key refers to ROOM.roomNo roomGuaranted Char(1) cancellationDate DATE Format: dd/mm/yyy noOfOccupants Number noOfnight number References George, J. (2004).Object-oriented systems analysis and design. Upper Saddle River, N.J.: Pearson Prentice Hall. Hoffer, J., George, J., Valacich, J. (1999).Modern systems analysis and design. Reading, Mass.: Addison-Wesley. Valacich, J., George, J., Hoffer, J. (2001).Essentials of systems analysis and design. Upper Saddle River, N.J.: Prentice Hall.