Comp2400 - Assignment 2

Tables - Version 4
------------------


City (CityName)

CityName -> CityName

CK = (CityName)

3NF
BCNF?
---------------------------------------------------------

Hotel (Name, Address, Telephone, Manager, CityName,
       NoOfRooms[count(Room.RoomNo)])

Name -> Address, Telephone, Manager, CityName
Address -> Name
Telephone -> Name
Manager -> Name

3NF, BCNF?

CK = (Address
      Name
      Telephone
      Manager)

References (CityName - City.CityName) 
---------------------------------------------------------

Room (RoomNo, HotelName, RoomType, Beds, Smoking)

RoomNo, HotelName -> RoomType, Beds, Smoking

3NF, BCNF?

CK = (RoomNo, HotelName)

References (HotelName - Hotel.Name
            RoomType - RoomType.RoomType)
---------------------------------------------------------

RoomType (RoomType, MaxOccup)

RoomType -> MaxOccup

3NF, BCNF?

CK = (RoomType)
---------------------------------------------------------

RoomTarrif (Single, Double, ExtraPerson, HotelName,             RoomType)

HotelName, RoomType -> Single, Double, ExtraPerson

3NF, BCNF?

CK = (HotelName, RoomType)

References (HotelName - Hotel.Name
            RoomType - RoomType.RoomType)
---------------------------------------------------------

AdvanceBooking (ArrDate, DepDate, Deposit, RoomAll,
                Persons, HotelName, GuestName)

GuestName, HotelName, ArrDate -> DepDate, Deposit,
                                 RoomAll, Persons??

3NF, BCNF?

CK = (GuestName, HotelName, ArrDate)

References (RoomAll - Room.RoomNo
            HotelName - Hotel.Name
            GuestName - Guest.Name)
---------------------------------------------------------

Guest (Name, Address, Telephone, CreditCardNo,        CreditCardType)

Name -> Telephone, CreditCardNo
Telephone -> Address
CreditCardNo -> CreditCardType
CreditCardNo, CreditCardType -> Name

???

CK = (Name
      CreditCardNo, CreditCardType)
---------------------------------------------------------

Allocation (Persons, Date, Reg, RoomNo, GuestName,                   HotelName)

Date, GuestName -> RoomNo, HotelName, Reg, Persons

CK = (Date, GuestName)

References (RoomNo - Room.RoomNo
            GuestName - Guest.Name
            HotelName - Hotel.Name)
---------------------------------------------------------

ResturauntCharge (Meal, Persons, ChargeType, Amount,                       Date, GuestName)

Date, GuestName, Meal -> Amount, Persons, ChargeType

CK = (Date, GuestName, Meal)

References (Date - Allocation.Date
            GuestName - Allocation.Guestname)
---------------------------------------------------------

TelephoneCharge (TelNo, Time, ChargeType, Amount, Date
                 GuestName)

Date, GuestName, TelNo, Time -> ChargeType, Amount

CK = (Date, GuestName, TelNo, Time)

References (Date - Allocation.Date
            GuestName - Allocation.GuestName)
---------------------------------------------------------

RoomService (Service, Time, ChargeType, Amount, Date,              GuestName)

Service, GuestName, Time, Date -> ChargeType, Amount

CK = (Service, GuestName, Time, Date)

References (Date - Allocation.Date
            GuestName, Allocation.GuestName)
---------------------------------------------------------

?????????????????????????????????????????????????????????


a)