Download [UPDATE] - Database - 1622 and more Assignments Computer Science in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 19 /02/ Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Student ID Class 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 Grading grid P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback: Grade: Assessor Signature: Date: Signature & Date:
Table of Contents
- CHAPTER I: STATEMENTS OF USER AND SYSTEM REQUIREMENTS (P1)
- OVERVIEW ABOUT THE PROBLEM
- 1.1 scenario........................................................................................................................................
- 1.2 Requirements of the application
- CHAPTER II: Design the relational database system
- Analyse the requirements
- Database design with explanations
- 2.1 High-level design (ERD)
- Explaining for ERD:
- Explaining the relationship:.........................................................................................................
- Data types:
- Figure 1 ERD for Hotel Management System TABLE OF FIGURES
- Figure 2 ERD Customer and Tables
- Figure 3 ERD Employee and Booking
- Figure 4 ERD of Billing and Booking.............................................................................................................
- Table 1 Data types of Employee table TABLE OF TABLES
- Table 2 Data Types of Customer table
- Table 3 Data of Booking table
- Table 4 Data of Billing
- Table 5 Data of Roomdetails table
- Table 6 Data of Room table
Advanced statistical actions:
- Statistics of 3 type room are the best seller in the booking.
- Statistics of the day with the highest revenue of the day.
- Revenue statistics quarter.
- Statistics of 3 months with the highest revenue to supplement human resources and products.
- Statistics of 2 room type and price with the lowest purchase volume to adjust or replace other room type
CHAPTER II: Design the relational database system
1. Analyse the requirements
According to a recent survey of hotels with customers who have booked room and with store employees, there are a number of issues raised as follows:
- Customers respond to booking and payments quite slowly and take some time.
- There were a few customers who had booking a room but when they arrived the room was full
- The employee said that they had trouble processing the order and payment because of the large number of customers. The management has learned how to operate a hotel as a result of the aforementioned shortcomings. To address the issues and boost sales, they decided to design a hotel management system. Everyone needs a management system that can adapt to a variety of scenarios. And that system must be updated on a regular basis in order for everyone to have access to information on customers, employees and booking.
2. Database design with explanations
2.1 High-level design (ERD)
Entity Relationship Diagram:
Figure 1 ERD for Hotel Management System
Explaining for ERD:
The diagram includes 6 tables with saved values for data needed system. Booking table: The booking table contains the primary key of Booking_ID. Also, the foreign key are Customer_ID aand Employee_ID. These are also contributes like day_Checkin and day_Checkout. Employee table: The Employee table contains the primary key of Employee_ID. Besides, the table has attributes like Employee_FName , Employee_LName, Employee_Gender, Employee_DoB, Employee_PhoneNumber. The above attributes are intended to provide employee information.
Figure 3 ERD Employee and Booking The relationship between Booking and RoomDetails tables: With 1 ID of Booking will be associated with 1 room. And for each room, there will be in many booking - > One to Many relationship
Figure 4 ERD Booking and RoomDetails The relationship from RoomDetails and Room: With Room ID, you can identify the type and the price of difference rooms but the type and price just belongs to 1 identity room ID - > One to Many relationship Figure 5 ERD Room and RoomDetais The relationship between Booking and Billing tables: A Bill will contain a certain Booking Id and all the other information of rooms, customer and employees as well as total price. And a billing will have only one identity booking id, in contrast with a booking id, they can have many bills for each object.
Customer table: Coulumn Name Data Type Customer_ID INT Customer_FName VARCHAR Customer_LName VARCHAR Customer_Gender VARCHAR Customer_DoB DATE Customer_Phonenumber INT Table 2 Data Types of Customer table Booking table: Column Name Data Type Customer_ID INT Employee_ID VARCHAR day_Checkin DATE day_Checkout DATE Table 3 Data of Booking table Billing table: Column Name Data Type BillNo INT BookingID INT Total INT
Table 4 Data of Billing RoomDetails table: Column Name Data Type Room_ID INT Booking_ID INT Amount INT Table 5 Data of Roomdetails table Room table: Column Name Data Type Room_ID INT Room_Type VARCHAR Room_Price INT Table 6 Data of Room table Database Diagram: Tables, attributes, and relationships are all common features of both ERD and database diagrams, as seen in the database diagram graphic above. The ERD, on the other hand, displays the primary and foreign keys in detail so that the reader may grasp the system. Finally, the database diagram maintains the original concept of the ERD in all of its fundamental aspects.
