Group Project SSK4401 : DATABASE SYSTEM
SEMESTER 2 SESSION 2019/2020
CPS1 (20%)
Group Project SSK4401 : DATABASE SYSTEM
SEMESTER 2 SESSION 2019/2020
CPS1 (20%)
Please form a group maximum of 3 members to carry out this activity. When to Submit ?
A small Hotel owner wish to computerized the management of hotel operations. You are asked to assist in the development of a database for the hotel. The database keeps data about ROOM, EMPLOYEE and FACILITY. From the discussion session with the hotel manager, you have identified the following information:
·
Three types of ROOMS offered by the hotel are single, double and deluxe room. Each room is identified by its unique number.
In general, there are two main types of the hotel jobs, GUEST SERVICES and ADMINISTRATIVE & SUPPORT.
HOTEL MANAGER, EVENT PLANNER and ACCOUNTANT are the administrative and support job positions in the hotel.
Types of employee falls under the guest services are RECEPTIONIST, HOUSEKEEPER, or KITCHEN STAFF.
Each RECEPTIONIST is identified with her/his name, employee number and years of experience.
The receptionists need to assign a HOUSEKEEPER to each room every morning or whenever it required before the room can be assigned to a guest to ensure the room is clean.
The same room may need to be cleaned several times on the same day, before it gets reassigned.
The date and the status need to be recorded for each cleaning assignment
The KITCHEN STAFF works on the front lines of restaurant, cafeteria, and catering kitchens, interacting with cooks, waitstaff, bartenders, and sometimes customers.
The cleaning staff and the kitchen staff are also uniquely identified by their employee number.
The duties and responsibilities of a Receptionists is to receive guest at the front desk by greeting, welcoming, directing and announcing them appropriately, they are also require to allocate a unique room to each guest and specify one group of facilities which is accessible to the guest during his stay.
Guests are uniquely identified with their IC number but other necessary information are also recorded about the guests, including: name, phone numbers, arrival date, departure date, and credit card number.
Each FACILITY GROUP contains specific set of facilities, e.g. the bar or gym, in order to be used by the guests.
The arrival and departure dates of a guest will in turn determine the occupation of a specific room.
1. Draw an enhance entity relationship diagram (EERD) to model the scenario above. Please show all the:
entities, attributes, primary keys, relationships and cardinalities
Identify the strong and weak entities
Indicate the identifying relationship in your EERD
Identify the total participation
Identify the types of generalizations/specializations represented in your EER diagram
Identify Multi-valued in your design
[30 marks]
2. Transform the EERD provided in (1) into relations.
[10 marks]
3. Using the appropriate SQL DDL Statements, create an actual database for the EERD provided in (1)
using any DBMS software (ORACLE/MySQL/MSSQL)
[20 marks]
4. Using the database created in (3), please provide the appropriate SQL statements to display the:
List of ROOM
List of EMPLOYEE
Types of FACILITY
FACILITY provided for the ROOM booked by GUEST named “John”
The most booked ROOM
[25 marks]
5. Transform the answers provided in question 4 (a), (b), (c), (d) and (e) into relational algebra.
[25 marks]