Conceptual Database Design Using EER Diagram For Courtland Hotels Essay.

1. Select and analyse a problem domain so as to identify data requirements in businesses
2. Design and implement a database system for the identified requirements using database modelling techniques and appropriate data description and manipulation languages

Extended Entity Relation Diagram (EER Diagram) and its use in Conceptual Database Design

To come up with a conceptual database design, an Extended Entity Relation Diagram (EER Diagram) is used. The diagram show all the entities and specifies their types whether strong or weak and shows some attributes for all entities including primary keys for every strong entity.

The following figure shows the EER diagram for the proposed Courtland Hotels (CH) database modelled using Microsoft Visio 2013. Zoom in for better visibility.

To improve on visibility of the diagram, some of the attributes are missing from the entities as modelling the diagram with all the attributes would result to a much cluttered diagram that is not readable. The following is a list of all the entities and their attributes.

• Staff(staffID, fullNames, email)
• Client(clientID, fullNaes, address, tel, email)
• Booking(BookingID, date, time, totalAmount,deposit,type)
• Wedding (bookingID,receptionDate, brideName, groomName,NoOfGuests, foodMenu, eveningReception, openBar, photographer, flowers, weddingCakeKnife,Room)
• Conference (BookingID, orgAddress,NoOfDelegates, mealPackage, Rooms, dates)
• Payment (paymentID, methodOfPayment, amount,date)
• Credit (creditCardNO,paymentID)
• Debit(debitCardNO,paymentID)
• Cheque(chequeNO, paymentID)
• Themed_nights (BookingID, NumberOfPeople,Room,date)
• Individual_stay(BookingID, discount, typeOfEvent, Rooms, dates)
• Room(roomID, name, rate)
• meetingRoom(roomID,  facilities, capacity)
• eventRoom( roomID, capacity)
• bedroom( roomID, type)

To come up with the EER diagram some assumptions were made on top of the requirements specified in the case study. These assumptions were added to improve on the design of the database. The assumptions are;

• A meeting room can have more than one facility in the same room. For example a meeting room can have an electronic white and private tea/coffee facility.
• All types of rooms have a name associated with it even for the meeting rooms and the bedrooms. The name is used to identify the room for example a bedroom can be given a name like B1 while a meeting room can be given a name like M1.
• A conference can be held for more than one day thus a booking for a conference event can be done for example for two consecutive dates.
• A wedding event is held for only one day thus it is associated with only one date.
• An individual stay can be for one or more days thus the booking is associated with one or more days.
• A booking can result more than one payment records where by the client is required to pay an initial deposit and then make the rest of the payment on the day of reporting.
• A payment can only be done using one type of payment. For example a client can only use cash but cannot use cash and credit card at the same time.

The logical database design can be achieved by using normalization on the entities achieved from the EER diagram. Normalization is done on three levels;

• First Normal Form (1NF) - Normalization of entities to 1NF involves elimination of any repeating group in the relation.
• Second Normal Form (2NF) - Normalization to 2NF involves taking the relations acquired as a result of normalization to 1NF and eliminating all partial dependencies. By eliminating partial dependencies the relation will remain with one primary key and all the other attributes should depend on only the primary key.
• Third Normal Form (3NF) - Normalization to 3NF involves taking the relations achieved as a result of normalization to 2NF and eliminating all transitive dependencies. BY eliminating all transitive dependencies, the relation will have only one primary key which determines all the other attributes. No other attribute in the relation should determine another attribute but only the primary key. At 3NF, the relations can be implemented as tables to form a functional database.

Normalization of the relations for the proposed database resulted to the following tables which are all in 3NF.

• Client( ClientID, firstName, surname, address, tel, email)

Primary key (clientID)

• Staff(staffID, firstname, surname,  email)

Primary key (staffID)

• Booking(BookingID, clientID, staffID, bookingDate, bookingTime, totalAmount, deposit, type)

Primary key (BookingID)

Foreign key (clientID) References client (clientID)

Foreign key (staffID) references staff (staffID)

• Payment(paymentID, BookingID, paymentMethod, amount, paymentDate)

Primary key (paymentID)

Foreign key (BookingID) References Booking (BookingID)

• Credit(paymentID, creditCardNO)

Primary key (paymentID)

Foreign key (paymentID) references payment (paymentID)

• Debit(paymentID, debitCardNO)

Primary key (paymentID)

Foreign key (paymentID) references payment (paymentID)

• Cheque(paymentID, chequeNO)

Primary key (paymentID)

Foreign key (paymentID) references payment (paymentID)

• Wedding(BookingID, brideName, groomName, receptionDate, NumberOfGuests, foodMenu, eveningReception. openBar, photographer, flowers, weddingCakeKnife, roomID)

Primary key (BookingID)

Foreign key (BookingID) references booking ( BookingID)

Foreign key (roomID) references room (roomID)

• Conference(BookingID, orgnisationAddress, noOfDelegates, mealPackage)

 Primary key (BookingID)

Foreign key (BookingID) references booking ( BookingID)

• Conference_rooms(BookingID, roomID, layout)

Primary key (BookingID, roomID)

Foreign key (BookingID) references conference (BookingID)

• Conference_dates(BookingID, ConferenceDate)

Primary key (BookingID, conferenceDate)

Foreign key (BookingID) references conference (BookingID)

• Themed_nights (BookingID, maxParticipants, roomID, themeDate)

Primary key (BookingID)

Foreign key (BookingID) references booking (BookingID)

Foreign key (roomID) references room (roomID)

• Individual_stay(BookingID, period, discount, eventType)

Primary key (BookingID)

Foreign key (BookingID) references booking (BookingID)

• Individual_stay_rooms(BookingID, roomID)

Primary key (BookingID, roomID)

Foreign key (BookingID) references individual_stay (BookingID)

Foreign key (roomID) references room (roomID)

• Individual_stay_dates(BookingID, date)

Primary key (bookingID, date)

Foreign key (BookingID) references individual_stay (BookingID)

• Room(roomID, name, rate)

Primary key (roomID)

• Meeting_room(roomID, capacity)

Primary key (roomID)

Foreign key (roomID) references room (roomID)

• Meeting_room_facilities (facilityID, roomID,facility)

Primary key (facilityID)

Foreign key (roomID) references meeting_room (roomID)

• Event_room(roomID, capacity)

Primary key (roomID)

Foreign key (roomID) references room (roomID)

• Bedroom(roomID, type)

Primary key (roomID)

Foreign key (roomID) references room (roomID) 

Each of the relation specified above in 3NF and can be implemented as a table in Oracle DBMS. The implementation is shown in the next sub-task. 

CREATE UNIQUE INDEX IX_conference_dates ON conference_dates (confereceDate);

This index will help in performance of the database because one of the most common actions to be done on the database is finding whether a conference has been booked on a certain date. This will be useful because it will help when customers are making inquiries on whether there is a conference booked for a particular date.

CREATE UNIQUE INDEX IX_themed_nights ON themed_nights (themedate);

This index is important because it will help when the user wants to know if there is a theme night booked for a particular date

CREATE UNIQUE INDEX IX_individual_stay_dates ON individual_stay_dates (stayDate);

This index will be useful because determining the individual stay booking for a particular date will be a very common task in the database.

1. Query 1

Select client.firstname,client.lastname, booking.bookingDate from client inner join booking on booking.clientID=client.clientID where booking.bookingDate between '01-Jul-2018' AND '31-jul-2018';

This query can be used to select all the client by their firstname and lastname and the booking date if the client has made a booking this month. This query can be useful to the organization as it can be used to show all clients that have made a booking between a certain period for example this month or last month, this week or last week. By just substituting the two dates the user can be able to get all the clients.

1. Query 2

select b.type,count(b.bookingID) from booking b

group by b.type;

This query is important to the organization because it can be used to show the number of bookings for each type of booking that has been done. This can help the organization determine which type of booking is booked the most and which type of booking is done the least. This can be helpful in terms of business as it will help the organization to adjust according to the statistics. 

select b.bookingID,b.type from booking b right outer join conference c on c.bookingID=b.bookingID right outer join wedding w on w.bookingID=b.bookingID right outer join themed_nights t on t.bookingID=b.bookingID inner join conference_dates cd on cd.bookingID=c.bookingID inner join conference_rooms cr on cr.bookingID=c.bookingID where cd.confereceDate='30-Oct-2018' OR t.themeDate='30-Oct-2018' OR w.receptionDate='30-Oct-2018' AND w.roomID=1 OR cr.roomID=1 OR t.roomID=1; 

This query can be useful to the organization because it can be used to search if Room 1 which is chatsworth suite is available on 30^th of October 2018. This query can be used to search if a room is available on a certain date. 

1. Query 4

select b.bookingID,c.firstname, c.lastname from booking b inner join client c on c.clientID=b.clientID where b.bookingID not in (select bookingID from individual_stay);

This query can be used to show all bookings that are not individual stays bookings. It can show the first name and last name of the client who have made bookings that are not individual stays.  

1. Query 5

Select sum(amount) from payment where paymentDate<sysdate;

This query is important because it can be used to show how much money the organization has made so far that has already been paid to the organization. The query shows the sum of the money.

1. Query 6

select c.clientID,c.firstName,c.lastname,sum(p.amount) from client c inner join booking b on b.clientID=c.clientID inner join payment p on p.bookingID=b.bookingID

group by c.clientID,c.firstName,c.lastname

order by sum(p.amount) desc;

This query can be used to show the amount that each client has paid to the organization. This query can help the organization to know their most loyal customers for business strategies like customer segmentation where by the most loyal customers are given special treatments to retain their loyalty.