The ERD entity and relationship attributes:
address = string
arrival date = date
Billed for = @invoice number + @room number
Booked by = @guest ID + @employee ID + @room number
Charged to = @guest ID + @invoice number
check out date = date
city = string
comment = string
confirmation number = integer
country = string
Credit card = @credit card type + @credit card name
Credit Card of = @guest ID + @credit card type
credit card expiry = date
credit card name = name
credit card number = string
credit card type = integer
date = string
date ordered = date
date to deliver = date
Delivered by = @employee ID + @room number + @date ordered +
@time ordered
discount name = string
discount rate = flate
duration of stay = integer
Employee = @employee ID + employee first name + employee last name +
security type + password
employee first name = name
employee id = integer
employee last name = name
Fee = @item number + fee description + fee cost
Fee of = @item number + @room number + @date ordered + @time ordered
fee description = string
fee cost = real
food order = float
Guest = @guest ID + guest first name + guest last name + address +
city + province/state + postal code/zip code + photo ID +
discount name + discount percentage + phone number
guest ID = integer
guest first name = name
guest last name = name
GST = float
hotel tax = float
Invoice = @invoice number + room number + employee ID + date +
time + payment method + total bill + food orders + movie
ordering + telephone vill + miscellaneous charge + hotel
tax + GST + guest ID
invoice number = integer
In Room = @guest ID + @room number + confirmation number + arrival
date + duration of stay + check out dat + number of
occupants
Issued by = @invoice number + @employee ID
Item number = integer
miscelaneous charge = float
movie ordered = float
name = string
number of occupants = integer
password = string
payment method = string
phone number = string
province/state = string
postal code/zip code = string
Room = @room number + room type
room number = integer
room rate = real
Room Service = @room number + @date ordered + @time ordered +
Room Type = @room type id + room name + room rate
room type id = integer
room type name = string
Room Type of = @room number + @room type
Security = @security type + security name
Security for = @employee ID + @security
security name = string
security type = integer
Sign-in by = @guest ID + @employee ID + @room number
Sign-out by = @guest ID + @employee ID + @room number
telephone bill = float
time ordered = time
time to deliver = time
time = string
Entities
Weak Entity
Room Service
time ordered
date ordered
time to deliver
date to deliver
room number
employee ID
comments
item number
Relationships
The Data Dictionary for the ERD:
The data dictionary is vague for room, room type, and security type. Please clarify these definitions and possibly the purpose for these attributes in the DD.
* A non key attribute of entity Guest, having a maximum length of 256
* characters. This attribute is not needed when making a reservation
* to stay at the hotel.
* A non key attribute of the relationship In Room. The room booked
* in date.
* This is a 1:Mc relationship that relates an instance of entity
* Invoice to an instance of entity Room. This indicates which room
* an invoice is for.
* This is a 1:Mc:Mc relationship. It relates an instance of entity
* Guest to an instance of entity Employee and entity Room. It
* indicates which employee booked the room for the guest.
* This is a 1:M relationship that relates an instance of entity
* Invoice to an instance of entity Guest. This relationship indicates
* who an invoice should be charged to.
* A non key attribute of the relationship In Room. The date the
* occupant of the room will be checking out.
* A non key attribute of entity Guest. Maximum length is 256
* characters. Allowed to be null when booking.
* A non key attribute of weak entity Room Service. Can be up to 100
* characters long. It is used to provide an special requests or remark.
* Identifies a unique reservation. It is a non key attribute of the
* relationship In Room.
* A non key attribute of entity Guest. Maximum 50 characters long.
* Allowed to be null when booking.
Please clarify credit type as compared to credit card name.
* A entity which contains a credit card name and a unique integer
* identifying the credit type name.
* This is a 1:Mc relationship relates an instance of Guest to an
* instance of Credit Card. It indicates the type of credit card.
* A non key attribute of entity Guest. This is the expiry date of
* the credit card.
* A non key attribute of entity Guest. This is the name that is
* on the physical credit card.
* A non key attribute of entity Guest. Maximum length is 256
* characters.
* A key attribute of entity Credit Card. It uniquely identifies each
* credit card type.
* A typed defined as a string of 10 characters, holding a date.
* it is of the format YYYY/MM/DD
* A key attribute of the weak entity Room Service. It gives the
* date of when the particular room service was requested
* A non key attribute of the weak entity Room Service. Informing
* the hotel, as to when the particular requested room service is
* to be provided/delivered
* This is a 1:Mc relationship that relates an instance of the weak
* entity Room Service to an instance of entity Employee. It indicates
* which employee delivered the room service.
* A non key attribute of entity Guest. Maximum 256 characters
* long. Allows the user to enter the reason for giving discount
* A non key attribute of entity Guest. Has 2 decimal places
* A non key attribute of the relationship In Room. It is 3 digits
* long, holding the number of days the guest will stay for. Its
* from 1 to 999 days.
* This entity contains information of all employees working for the
* hotel.
* A non key attribute of entity Employee, which contain employee's
* first name
* A key attribute of entity employee. It uniquely identifies each
* instance of entity employee.
* A non key attribute of entity Employee, which contain employee's
* last name.
* This entity stores the different room services offered (ie. food
* item, wake up calls). It contains a description and cost for
* each item.
* This is a 1:Mc relationship that relates an instance of the weak
* entity Room Service to an instance of entity Fee. It indicates
* which service was ordered and what it will cost.
* This item describes the type of fee. it is a string 50 characters
* long.
* This item gives the cost for a fee. It has a range of 0.00 to no
* upper bound.
* A non key attribute of Invoice. Carries up to 2 decimal places.
* This is the total sum for the food ordered.
* Is an entity that stores all the information pertaining to a guest
* staying at the hotel. Most of the information is obtained when a
* guest checks into the hotel.
* A key attribute of the entity Guest. It is 6 digits long, and has
* a fixed format.
* A non key attribute of entity Guest, gives first name of a person
* staying at the hotel.
* A non key attribute of entity Guest, gives last name of a person
* staying at the hotel.
* A non key attribute of entity Invoice. Carries up to 2 decimal
* places. GST charged.
* A non key attribute of Entity Invoice. Carries up to 2 decimal
* places. Stores the tax (percentage) for hotel usage.
Length of stay must be recorded on the invoice.
* This entity holds the information contained on an invoice, which
* is issued when the guest checks out.
* An key attribute of entity Invoice. It uniquely identifies each
* instance of entity Invoice. Its a 6 digit number that ranges from
* 1 to the number of instances of entity Invoice.
* This is a relationship between entities Guest and Room
* This is a 1:Mc relation that relates an instance of entity Invoice
* to an instance of entity Employee. This relationship indicates
* which employee generated an invoice.
* A key attribute of entity Fee. It identifies each fee with a
* unique id. Its bounded by the number of instances of Fee. It is
* represented by a 5 digit number.
* A non key attribute of entity Invoice. Carries up to 2 decimal
* places. The sum of cost for anything damaged or any thing charged
* to the guest that is not on the fee list.
* A non key attribute of entity Invoice. Carries up to 2 decimal
* places. The sum for the movie ordered.
* A type, defined to have a maximum length of 256 characters,
* contains no space, only alphabetical characters allowed with
* exception of apostrophe.
* A non key attribute of relationship In Room. It is 1 digit long,
* representing the number of occupants in a room. Bounded between
* the values of 1 and 9
* A non key attribute of entity Employee, which must be at least 8
* characters in length and maximum 11 characters. Every employee,
* has a password, and to access the system, it is required that
* they enter one
* A non key attribute of entity Invoice. Denotes the method of
* payment
* This is a non-key attribute of entity Guest. It contains the contact
* phone number for the guest. Its of the form XXX-XXX-XXXX. The first
* three X's being the area code.
* A non key attribute of entity Guest. Maximum length is 256
* characters. Allowed to be null when booking. Length given is
* to accomodate full name rather than abbreviations.
* A non key attribute of entity Guest. Maximum length is 256
* characters. Allowed to be null when booking
* This entity represents a room in the hotel. It contains a room
* number and the room type.
* This key attribute of entity Room contains the room number.
* Its a 3 digit number ranging from 1 to 999.
* This non-key attribute of entity Room Type indicates the cost of
* the room type for one night. Its ranges from a value of 0.0 to
* 1000.0.
comment + date to deliver + employee ID + item
number + time to deliver
* A weak entity containing all the services requested by a room,
* ie. wake up calls, food orders...
* This entity contains the room type name and cost for each room type.
* This key attribute of entity Room Type uniquely identifies each
* instance of entity Room Type. Its a 2 digit number from 1 to 99.
* This non-key attribute of entity Room Type gives the name for
* each room type. Its a string of 50 characters in lenght.
* This is a 1:Mc relationship that relates an instance of Room
* to an instance of Room Type. This relationship gives the room
* type for each room.
* This entity contains a table of all possible access that an
* employee can have and a name, descriptive of the employee's
* job classification.
* This is a relationship that relates an instance of Employee to an
* instance of Security. The relationship is 1:Mc. It indicates the
* security level for each employee.
* A non key attribute of entity Security. It can be up to 25
* characters long, that is descriptive of employee's job
* classification.
* A key attribute of the entity Security. The value of security type
* is bounded byt he number of items in the table, maximum value will
* be 100.
* This is a 1:Mc:Mc relationship, that relates an instance of entity
* Guest to an instance of entity Employee and entity Room. It
* indicates which employee signed a guest into a room.
* This is a 1:Mc:Mc relationship, that relates an instance of entity
* Guest to an instance of entity Employee and entity Room. It
* indicates which employee signed a guest out of a room.
* A non key attribute of entity Invoice. Carries up to 2 decimal
* places. This charge is not calculated by the system, but rather is
* provided by an outside source.
* A key attribute of weak entity Room Service. Holds the time at
* which a particular room service was requested.
* A non key attribute of weak entity Room Service. Informing the hotel as
* to what time the particular room service is to be provided/delivered
* A type defined as a string of 8 characters, holding a time. It is
* of the format HH:MM:SS.
