Download Assignment 2 - Database Design and Development - Pass and more Assignments Computer Science in PDF only on Docsity!
ASSIGNMENT 2 FRONT SHEET
Qualification BTEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date 12/03/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
P2 P3 P4 P5 M2 M3 M4 M5 D2 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date:
Lecturer Signature:
Acknowledgement
First and foremost, I want to thank the curators at the University of Greenwich for making these
courses available to students. I'd want to thank all of the writers and researchers who worked
on this data. Furthermore, I'd like to thank speaker Do Duy Thao for his excellent and
professional lectures and tutorials. Finally, I'd want to thank our university's lecturers and
administrative personnel for creating a positive learning atmosphere.
Table Of Contents
- CHAPTER 1: Develop the database system.
- Final Mock-up of the application.......................................................................................................................
- Queries to create database with results.
- Data Validation.
- CHAPTER 2: Produce Queries.
- Queries to INSERT data with illustrations of final result.
- Queries to SELECT data with illustrations of final result.
- Queries to UPDATE data with illustrations of final result.
- Queries to DELETE data with illustrations of final result.
- Advanced queries : Stored Procedures, Triggers, Functions
- CHAPTER 3: Test the System.
- CHAPTER 4: Produce technical and user documentation.
- Technical document.
- 1.1. Introduction.
- 1.2. User Requirement.
- 1.3. Diagram.
- References
- Figure 1: Main Layout. Table Of Figures
- Figure 2: Menu of View Option.
- Figure 3: View guest list layout......................................................................................................................................
- Figure 4: View staff list layout.
- Figure 5: View room list layout....................................................................................................................................
- Figure 6: View room type list layout.
- Figure 7: View booking list layout.
- Figure 8: View bill list layout.
- Figure 9: Menu of Add Option.
- Figure 10: Add new staff layout.
- Figure 11: Add new guest layout.
- Figure 12: Add new room layout.
- Figure 13: Add new room type layout.
- Figure 14: Add new booking layout.............................................................................................................................
- Figure 15: Successfully layout......................................................................................................................................
- Figure 16: Menu of Update option.
- Figure 17: Menu of Statistic option.
- Figure 18: Create Database of system.........................................................................................................................
- Figure 19: Create Guest table.
- Figure 20: Create Staff table........................................................................................................................................
- Figure 21: Create RoomType table.
- Figure 22: Create Room table......................................................................................................................................
- Figure 23: Create Booking table.
- Figure 24: Create Bill table.
- Figure 25: Result of creating tables.
- Figure 26: Database Diagram.
- Figure 27: Example of INT data type.
- Figure 28: Example of VARCHAR data type.
- Figure 29: Example of DATE data type.
- Figure 30: Example for NOT NULL constraint in Staff table.
- Figure 31: Example for IDENTITY constraint in Staff table
- Figure 32: Insert data to Staff table.............................................................................................................................
- Figure 33: Insert data to Guest table.
- Figure 34: Insert data to RoomType table.
- Figure 35: Insert data to Room table.
- Figure 36: Insert data to Booking table.
- Figure 37: Insert data to Bill table.
- Figure 38: Select tables.
- Figure 39: Staff table.
- Figure 40: Guest table.
- Figure 41: Room table.
- Figure 42: RoomType table.
- Figure 43: Booking table.
- Figure 44: Bill table.
- Figure 45: Update data.
- Figure 46: Data before Update.
- Figure 47: Data after Update.
- Figure 48: Delete Data.
- Figure 49:Data before delete.
- Figure 50: Data after delete.
- Figure 51: Calculate total price....................................................................................................................................
- Figure 52: Result of Calculating total price.
- Figure 53: Revenue statistics by quarter.
- Figure 54: Result of revenue statistics by quarter.
- Figure 55: Revenue statistics by day.
- Figure 56: Result of revenue statistics by day.
- to attract new clients. Figure 57: Statistics on the days with the lowest income to enhance advertising on social networking sites in order
- Figure 58: Result of statistics on the days with the lowest income.
- Figure 59: ERD Of System.
- Table 1: Test Plan. Table Of Tables
- Table 2: Test Result.
CHAPTER 1: Develop the database system.
1. Final Mock-up of the application.
In the first report, I described the hotel management system in detail in terms of its
users and features. And in this report, to develop and perfect the system, I have
designed a user interface that integrates the features that the system offers. And here
are the interfaces of the hotel management system:
The first is the system's primary interface, which includes View, Add, Update,
Statistic, and Exit buttons.
When the user hits the View button, the user is presented with the following
interface. Items connected to employee, customer, room, and other information
will be presented, and users can choose to examine the information by clicking
on the choice.
Figure 1 : Main Layout.
When the user chooses View Staff List, the following interface will appear:
Same goes for Room List, Room Type List, Booking List and Bill List. Users can
look up the information of the above data through the ID and room number for
Room List. Each interface will have a Back button for the user to return to the
previous screen.
Figure 4 : View staff list layout. Figure 5 : View room list layout.
Figure 6 : View room type list layout. Figure 7 : View booking list layout.
Adding information is similar to Customer, Room, Room Type, and Booking.
Figure 10 : Add new staff layout. Figure 11 : Add new guest layout.
Figure 12 : Add new room layout. Figure 13 : Add new room type layout.
The interface when the user clicks the Update button. Items related to additional
information of employees, customers, room, room type, booking will be
displayed, users can click on options to edit necessary information.
The interface when the user clicks the Statistic button. The statistics and other
functions of the system will be displayed. Users can choose View or Update for
new updates.
Figure 17 : Menu of Statistic option. Figure 16 : Menu of Update option.
2. Queries to create database with results.
The initial step in building the system is to generate the database.
Next, we need to create the data tables as shown in the design.
Figure 18 : Create Database of system. Figure 19 : Create Guest table. Figure 20 : Create Staff table.
During the table construction process, foreign and primary keys are produced and
we get the following result after building the table:
Figure 25 : Result of creating tables. Figure 26 : Database Diagram.
3. Data Validation.
Data Type:
INT: It is used to represent an integer value. Its signed range is - 2147483648 to
2147483647, while its unsigned range is 0 to 4294967295. The size option
defines the maximum display width, which is set to 255.
VARCHAR: It is a character string data type with configurable width. Its length
is limited to 8000 characters.
DATE: It specifies the date format YYYY-MM-DD. It accepts values ranging
from '1000- 01 - 01' to '9999- 12 - 31'.
Presence:
NOT NULL: When we assign the value NOT NULL to a table attribute, we are
indicating that the values in that attribute cannot be left blank. Here are some
NOT NULL examples.
IDENTITY: When you apply IDENTITY to a property, such as ID. In other words,
when you add the Employee's details, the ID will increase in ascending order.
Figure 27 : Example of INT data type. Figure 28 : Example of VARCHAR data type. Figure 29 : Example of DATE data type. Figure 30 : Example for NOT NULL constraint in Staff table. Figure 31 : Example for IDENTITY constraint in Staff table
