Download Assignment 1 - Database Design and Development - Pass and more Assignments Computer Science in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification BTEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 19/02/2022 Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Huynh Minh Huy Student ID GCD Class GCD1001 Assessor name Do Duy Thao Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice.
Student’s signature Huy
Grading grid P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date: Lecturer Signature:
Table Of Contents
- CHAPTER 1: Statements of user and system requirements.
- Overview of the problem.
- Requirements of the application.
- CRUD and advanced statistical actions in the system.
- CHAPTER 2: Design the relational database system.
- Analyse the requirements.
- Database design with explanation.
- Figure 1: ERD for Hotel Management System. Table Of Figures
- Figure 2: Staff Table.
- Figure 3: Guest Table.
- Figure 4: Booking Table.
- Figure 5: Room Table.
- Figure 6: Room Type Table.
- Figure 7: Bill Table.
- Figure 8: The relationship between Booking table and Guest table.
- Figure 9: The relationship between Booking table and Staff table.
- Figure 10: The relationship between Booking table and Room table.
- Figure 11: The relationship between Room table and Room Type table....................................................................
- Figure 12: The relationship between Booking table and Bill table.
- Figure 13: Data type of Staff table.
- Figure 14: Data type of Guest table.
- Figure 15: Data type of Booking table.
- Figure 16: Data type of Room table.
- Figure 17: Data type of Room Type table.
- Figure 18: Data type of Bill table.
CHAPTER 1: Statements of user and system requirements.
1. Overview of the problem. With the tourist sector reopening after a long period of closure due to the COVID-19 outbreak, demand for travel services is on the rise. Because of the rapid return, the hotel system's management faced various challenges, thus this project was established with the goal of making hotel administration easier and more efficient. In addition to handling booking, staff, and customers, the management system assists hotel managers in compiling data on the issues that must be addressed in order to improve the hotel. 2. Requirements of the application. Hotel Management System has three user roles: Staff, Receptionist and Customer. As a Staff: I want to examine employee work data in order to assess job capability, plan work schedules, and reward them more when they work hard. I want to update, add, and delete employee information so that I may edit it if anything is incorrect or someone is new. As a Receptionist: I want to check the information of the customer who has booked a room. I want to record and examine all of a room that customer's book so that I may bill them later. I want to know which room customer booked so that I know which room need to be cleaned after customers pay the invoice. As a Customer: I want to see my payment information to check if it matches with reality. I want to view my payment information to make sure it matches what I've chosen.
CHAPTER 2: Design the relational database system.
1. Analyse the requirements. A recent study of hotels and its visitors, as well as personnel, revealed a number of issues: Customers reply to orders and payments slowly and take some time. A few guests had reserved rooms, but when they came, the hotel was completely packed. Due to the enormous number of clients, the staff stated that it was difficult to process the reserves and payments. Some staff have requested to be excused from work, but reception has not yet understood. Because of these shortcomings, management sought to design a hotel management system to address the issues and enhance income. Everyone needs a management system that is adaptable in a variety of settings. And that system must be updated on a regular basis so that everyone has access to information on customers, workers, or reserves. 2. Database design with explanation. Firstly, I will provide an logical design of the Hotel Management System. Figure 1 : ERD for Hotel Management System.
Explaining for ERD: This diagram includes 6 tables namely Guest, Staff, Booking, Room, Room Type and Bill. To be more detailed, I will explain each table below: Staff table: The Staff table is used to hold all staff information. This table has six attributes including Staff_ID, Staff_FirstName, Staff_LastName, Staff_Gender, Staff_PhoneNumber and Staff_Email. Each staff in the hotel will have a unique ID, hence staff id will be the primary key in this table. Staff information must be kept in this table so that a manager may contact and identify them if needed, hence attributes must not be null. Figure 2 : Staff Table. Guest table: The Guest table is used to hold all guest information. This table has ten attributes including Guest_ID (Primary Key), Guest_FirstName, Guest_LastName, Guest_Gender, Guest_DoB, Guest_PhoneNumber, Guest_Email, Guest_IdentityCard, Guest_Address and Guest_Country to provide customer information.
Room table: The Room table has two attributes including Room_NO and Room_Type_ID. In this table, Room_NO will be the primary key and Room_Type_ID will be foreign key. Figure 5 : Room Table. Room Type table: The Room Type table has three attributes including Room_Type_ID (Primary Key), Room_Type and Room_Price. This table shows the information of the room type and the price of that room. Figure 6 : Room Type Table. Bill table: The Bill table has four attributes including Invoice_ID (Primary Key), Booking_ID (Foreign Key) and information about payment date and room charge.
Figure 7 : Bill Table. Explain the relationship between tables in the database system of the Hotel Management: The relationship between Booking table and Guest table: A customer can book many different bookings, while a booking can only be booked by a customer, so the relationship between Booking table and Guest table is one to many. The relationship between Booking table and Staff table: A staff can create many different bookings, while a booking can only be maded by a staff, so the relationship between Booking table and Staff table is one to many. Figure 8 : The relationship between Booking table and Guest table.
The relationship between Booking table and Bill table: One booking for customer can have many bills but one bill can only have one booking for customer, so the relationship between Booking table and Bill table is one to many. Figure 11 : The relationship between Room table and Room Type table. Figure 12 : The relationship between Booking table and Bill table.
Data type: Staff table: Figure 13 : Data type of Staff table. Guest table: Booking table: Figure 14 : Data type of Guest table. Figure 15 : Data type of Booking table.
