Resit coursework – Room Cleaning Service Database Design
Assignment 1: Designing the database
In this coursework, you are required to design a database for supporting the room cleaning service for a hotel. The requirements are as follow:
The hotel has 9 floors where each floor has a different amount of guest rooms. The floors of this hotel use three different types of materials:
1. 1st to 3rd floors use granite tiles.
2. 4th to 6th floors use hardwood planks.
3. 7th to 9th floors use carpets that changes based on the season. The materials of the carpets are
also not the same in each season.
Different carpet materials require different methods of cleaning. As a result, the floor materials must be recorded in the database.
Each room has a room number, an associated floor number and a list of registered electronic appliances (fridge, cooker, TV etc.). Each electronic appliance is associated with its manufacturer name and the phone number of its manufacturer. Phone number for the same manufacturer is always the same.
Each cleaner hired by this hotel has a cleaning staff ID and the cleaner’s name. Cleaners are formed into teams of three people when cleaning rooms. Each cleaning team has a team ID (integers) and a team leader who is in charge of making records of room cleaning.
The manager of this hotel has already hired other people to put all past room clean records into the database. These records are saved in a table called “room_service_records”. This table does not have any primary key or foreign keys. The table structure is shown below:
…
Each team has its own set of working days in a week and is assigned to service a fixed set of floors. For example:
1. Team 1: Monday to Wednesday. Services floors 1, 2 and 3.
2. Team 2: Wednesday, Friday, Sunday. Services floors 4, 5 and 6.
3. Team 3: Thursday to Sunday. Services floors 9 to 12.
Your database design should accommodate all possible weekly work schedules for all these teams.
team_leader_name
room_no
4003
service_date
Bob Johnson
2016-1-13
Your task:
1. Draw an E/R diagram for the whole database. The final database design should be in the third normal form (3NF). You also need to write a short paragraph (less than one page) to explain your database design and why it is in the 3NF. You do not have to consider “room_service_records” in this task. Both the E/R diagram and the explanation should be put into a Word or PDF document called “Report 1”.
2. Based on your database design, write a working SQL script consisting of CREATE TABLE statements that match the E/R diagram you designed. The SQL script should function on either MySQL or Microsoft Access. In your report, you must explicitly state which database your script is written for. The tables you designed should have proper primary keys and foreign keys. You do not have to consider “room_service_records” in this task. Your SQL script file should be called “cw1.sql”.
3. Write a few INSERT or UPDATE statements for each table to prove that your design works as intended. You should prove your design by showing both successful insertions/updates of correct data and unsuccessful insertions/updates of incorrect data. You do not have to consider “room_service_records” in this task. You should put all these INSERT or UPDATE statements into a separate script file called “cw1_tests.sql”.
Assignment 2: Using the database
Once you have finished designing your database, complete the following tasks:
4. Apart from the requirements above, the manager of the hotel has encountered a problem. The names of team leaders are not unique. Two team leaders have the same name “Gordon” but fortunately, they work on different floors. One Gordon works on floors 1, 2 and 3 while the other works on floors 7, 8 and 9. Write SQL scripts to differentiate the room cleaning records from these two team leaders. Assume that the Gordon who works on floors 1, 2 and 3 belongs to team 5 while the other belongs to team 12. Write two UPDATE statements so that the first Gordon is changed to “Gordon_5” and the second Gordon is changed to “Gordon_12”. Save these two statements in a file called “cw2.sql”.
5. Alter the “room_service_records” so that the new design fixes the issue with duplicate team leader names and also prevents similar issues from happening in the future. The new design should have a proper primary key and also correctly references the information to other parts of the database using foreign keys. Put your ALTER statements or other necessary statements into “cw2.sql”. Once done, you are required to insert a few example tuples into the database to prove that the new design works. The INSERT statements should also be saved into “cw2.sql”.
6. The room cleaning activities in this hotel can be classified into two types: room cleaning is required as soon as a customer leaves the hotel (no longer staying), and regular room cleaning every 2 days when a guest is staying in that room. The manager wants to check whether cleaners are strictly following the rule for the first type of room cleaning. Write a SELECT statement to find out any cleaning staffs who are not following this rule and save this statement in “cw2.sql”.
7. When winter season arrives, all the carpets on floors 7 to 9 will need to be replaced with fluffy red carpets, providing a warmer and cosier experience for the guests. We call this carpet material “Red cosy carpet”. Write an UPDATE statement to update your database so that the tables reflect such a change. Put the statement into “cw2.sql”
8. As 4K TV shows are now becoming popular, the manager wants to upgrade all the hotel rooms’ TVs. A manufacturer known as “TVStar” produces a model of 4K TV called “BrightHDR4k”, which is capable of providing high-quality viewing experience. The manager thus purchased enough of them to replace all the old TVs in the rooms on floors 4 to 9. Write one or more UPDATE statements to update the database with the information of the new TVs and put them into “cw2.sql”.
Assignment 3: Discussion
Propose an alternative design for this database. Please provide the updated SQL create table script and save it into “cw3.sql”. The new design does not need to be in 3NF. A detailed discussion must be included in a report called “report 3”, covering issues like how will the new design affect the queries and a few example queries. If the new design affects any tasks of assignment 2 above, please provide the alternative query for that task. You only need to cover one alternative design in this assignment, but extremely similar designs will be given lower marks.
Mark Distribution
Assignment 1 worth 50% of the total marks:
Assignment 2 worth 30% of the total marks. Each question worth 6 marks. Assignment 3 worth 20% of the total marks.
Item
Marks
Correct E/R diagram
20
SQL table design (SQL Script) matches the E/R diagram.
10
Inserts and updates
10
Report
10
Total
50
Item
Marks
Alternative SQL table design (SQL Script)
10
Discussion on the new design and its queries
10
Total
20
CREATE TABLE Statements:
https://www.learningmall.cn/pluginfile.php/65763/mod_resource/content/0/resit_ddl.txt
ER diagram:
https://www.learningmall.cn/pluginfile.php/65764/mod_label/intro/resit_db_design.PNG