CIS017-1 Database and Computer Networks Assignment Sample
Module Code And Title : CIS017-1 Database and Computer Networks Assignment Sample
1.0 Introduction
The database is considered one of the most important things that are required in any organization. In the database, various interrelated data are being collected which are basically used for the operations of CRUD. This generally allows several data to store in various forms including schema, reports, tables, and so on. The approach or the technique that is basically used for the management of any database is referred to as DBMS or the “Database Management System”.
This particular approach basically helps in the creation of the database, insertion of data in the created database, modifying tables, and so on. These particular operations are being performed with the help of DDL or the “Data Definition Language” statements and DML or the “Data Manipulation Language” statements.
The added security in the created database is being provided to the database using the approach of DBMS. In addition to the security, DBMS also maintains the consistency of data. In this particular project, the database of a hotel booking system is being created with the help of MySQL. In this, a relational database management system is being created using various approaches.
2.0 Aim and Objective
The primary aim of the project is to create a database along with various designs of a hotel booking system. The database that will be created is based on RDBMS or the “Relational Database Management System” and the database will be developed with the help of MySQL. This creation will allow several data to store in various forms including schema, reports, tables, and so on. In addition to that, security, integrity, and consistency will be maintained in the created database.
The primary objectives of the project are as follows.
- To create a database for a hotel booking system using various approaches.
- To create RDBMS using the technology of MySQL.
- To use DDL and DML statements for the creation and insertion in the database.
- To create various designs like ERD, data dictionary, and so on.
- To normalize the created tables for the purpose of the reduction of data redundancy.
- To provide data security, data consistency, and data integrity in the created database.
3.0 Scenario
The particular section describes the scenario of the hotel booking system and how the operations will take place. In the database of the hotel, firstly, the tables of customers, booking along with the room table need to be created. The customers who have not registered yet need to be registered using various credentials like name, address, email, and credit card information (Nathan et al. 2019). In the system, that needs to be created, the customer should be able to provide the check-in as well as the check-out date. In addition to that, they can also cancel their bookings. The booking also includes the choice of the rooms like the single room and the double room.
After the booking is being done, the manager of the hotel will need to provide a room to the customers depending upon their demands and choices mentioned during booking. The room will only be provided if the room is empty and if there are no existing bookings in that particular room of the hotel. The most important thing is that all the operations will be performed after that particular thing is signed by the manager of the hotel.
4.0 Task 1: ERM
The particular task will describe the ERM or the “Entity-Relationship Model” that is being created on the basis of the hotel booking system and the case study provided. The created ERM is being described below.
Figure 1: Entity-Relationship Model (Source: draw.io)
The above figure displays the ERM or the “Entity-Relationship Model” of the hotel booking system and according to that, the database for the hotel needs to be created. From the above ERM, it can be seen that there are various tables and entities like the customer, manager, registered, booking, and room. Each entity is interconnected with the other with a relationship (Vaisman et al. 2020).
The relationship that is used for the interconnection of tables is the “many-to-many” relationship approach as for the development of the system and the database, each entity needs to perform with the other entities. In addition to that, it is also seen that in each entity, there are various attributes, and one of the attributes there is a primary key.
5.0 Task 2: Normalization
The particular task describes and displays the normalization of the tables that are created for the system of hotel booking. This is basically done for the reduction of data redundancy. The normalization of tables is shown below (Wong et al. 2018).
| customer_id | name | address | telephone | |
| 16147 | Oliver Jones | London | [email protected] | +447841236549 |
| 16152 | Emily Brown | Liverpool | [email protected] | +447641536886 |
| 16281 | Charlie Evans | Manchester | [email protected] | +447552214663 |
| registration_id | customer_id | rname | raddress | phone | creditcard_no |
| 4001 | 16147 | Oliver Jones | London | +447841236549 | 4321567790111234 |
| 4002 | 16152 | Emily Brown | Liverpool | +447641536886 | 4321541136995411 |
| 4003 | 16281 | Charlie Evans | Manchester | +447552214663 | 4322845514772344 |
| customer_id | name | address | phone | |
| 16147 | Oliver Jones | London | [email protected] | +447841236549 |
| 16152 | Emily Brown | Liverpool | [email protected] | +447641536886 |
| 16281 | Charlie Evans | Manchester | [email protected] | +447552214663 |
| registration_id | customer_id | creditcard_no |
| 4001 | 16147 | 4321567790111234 |
| 4002 | 16152 | 4321541136995411 |
| 4003 | 16281 | 4322845514772344 |
| room_id | room_type |
| 1 | Single room |
| 2 | Double room |
| manager_id | username |
| 101 | AlexJames |
| 102 | JackHenderson |
| 103 | OliviaSmith |
Table: Normalization of data (Source: Self-created)
6.0 Task 3: Physical table design
The particular task describes the physical table design including the skeleton tables and the data dictionary. In the below illustrations, both skeleton tables and data dictionary has been included for all the created tables in the database of the hotel booking system (J.H. and Zamzami, 2020).
Figure 2: Physical design of table “customer” (Source: XAMPP MySQL)
The above figure displays the physical design of “customer” including skeleton tables as well as a data dictionary. In the physical design, it can be seen that there are the attributes name, data type, data size, collation, and so on. The primary key that is provided in this physical design is “customer_id”. In the customer table, the attributes can be modified and deleted.
Figure 3: Physical design of table “booking” (Source: XAMPP MySQL)
The above figure displays the physical design of “booking” including skeleton tables as well as a data dictionary. In the physical design, it can be seen that there are the attributes name, data type, data size, collation, and so on. The primary key that is provided in this physical design is “booking_id”. In the booking table, the attributes can also be modified and deleted (Kan et al. 2019).
Figure 4: Physical design of table “room” (Source: XAMPP MySQL)
The above figure displays the physical design of the “room” including skeleton tables as well as a data dictionary. In the physical design, it can be seen that there are the attributes name, data type, data size, collation, and so on. The primary key that is provided in this physical design is “room_id”. In the room table, the attributes can also be modified and deleted.
Figure 5: Physical design of table “registered” (Source: XAMPP MySQL)
The above figure displays the physical design of “registered” including skeleton tables as well as a data dictionary. In the physical design, it can be seen that there are the attributes name, data type, data size, collation, and so on. This table is somewhat different as in this particular table, there are both primary keys as well as foreign keys.
The primary key that is provided in this physical design is “registration_id” and the foreign key that is present in the physical design is the “customer_id” as this particular attribute is the primary key of the table “customer”. In the registered table, the attributes can also be modified and deleted (Coban et al. 2020).
Figure 6: Physical design of table “manager” (Source: XAMPP MySQL)
The above figure displays the physical design of the “manager” including skeleton tables as well as a data dictionary. In the physical design, it can be seen that there are the attributes name, data type, data size, collation, and so on. The primary key that is provided in this physical design is “manager_id”. In the manager table, the attributes can also be modified and deleted.
7.0 Task 4: Implementation of the database
The particular task is totally based on the database implementation and creation of the hotel booking system. The type of database management system is created for the hotel is basically RDBMS or the “Relational Database Management System”. This creation will allow several data to store in various forms including schema, reports, tables, and so on.
In addition to that, security, integrity, and consistency will be maintained in the created database (S.S. and Suh, 2019). The overall implementation is being done using the XAMPP MySQL software over the MySQL technology for the creation of the relational database for the system of hotel booking.
The overall implementation is being described below along with the screenshots. The creation of the table is being done with the help of DDL or the “Data Definition Language” statements and the insertion of the data in the created tables is being done with the help of DML or the “Data Manipulation Language” statements.
Figure 7: Table creation “customer” (Source: XAMPP MySQL)
The above figure displays the creation of the table “customer” for the database of the hotel booking system and this is done using DDL statements. The attributes that are created in the table are “customer_id”, “name”, “address”, “email”, and “telephone”. The primary key of this particular table customer is the “customer_id”. During the creation of the table, the data type and the data size of the attributes are being provided. The values that need to be provided in the table customer should not be null. This table is for storing the data of the customers.
Figure 8: Table creation “booking” (Source: XAMPP MySQL)
The above figure displays the creation of the table “booking” for the database of the hotel booking system and this is done using DDL statements. The attributes that are created in the table are “booking_id”, “start_date”, “end_date”, “double_room”, and “single_room”. The primary key of this particular table booking is the “booking_id”. During the creation of the table, the data type and the data size of the attributes are being provided. The values that need to be provided in the table booking should not be null. This table is for managing the booking done by the customers.
Figure 9: Table creation “room” (Source: XAMPP MySQL)
The above figure displays the creation of the table “room” for the database of the hotel booking system and this is done using DDL statements. The attributes that are created in the table are “room_id” and “room_type”. The primary key of this particular table room is the “room_id”. During the creation of the table, the data type and the data size of the attributes are being provided. The values that need to be provided in the table room should not be null. This table is for the room types of the hotel (L. and Walker, 2018).
Figure 10: Table creation “registered” (Source: XAMPP MySQL)
The above figure displays the creation of the table “registered” for the database of the hotel booking system and this is done using DDL statements. The attributes that are created in the table are “registration_id”, “customer_id”, “rname”, “raddress”, “phone”, and “creditcard_no”. The primary key of this particular table register is the “registration_id” and the foreign key is the “customer_id” as this is the primary key of some other table.
During the creation of the table, the data type and the data size of the attributes are being provided. The values that need to be provided in the table register should not be null. This table is for storing the data of the registered customers.
Figure 11: Table creation “manager” (Source: XAMPP MySQL)
The above figure displays the creation of the table “manager” for the database of the hotel booking system and this is done using DDL statements. The attributes that are created in the table are “manager_id”, “username”, and “password”. The primary key of this particular table manager is the “manager_id”. During the creation of the table, the data type and the data size of the attributes are being provided. The values that need to be provided in the table manager should not be null. This table is for storing the data of the hotel managers (Zhang et al. 2018).
Figure 12: Data insertion “customer” (Source: XAMPP MySQL)
The above figure displays the insertion of the data into the table “customer” according to the attributes that are created in this particular table. The insertion in the table “customer” is done using the DML statements.
Figure 13: Data insertion “booking” (Source: XAMPP MySQL)
The above figure displays the insertion of the data into the table “booking” according to the attributes that are created in this particular table. The insertion in the table “booking” is also done using the DML statements.
Figure 14: Data insertion “room” (Source: XAMPP MySQL)
The above figure displays the insertion of the data into the table “room” according to the attributes that are created in this particular table. The insertion in the table “room” is also done using the DML statements (Vyawahare et al. 2018).
Figure 15: Data insertion “registered” (Source: XAMPP MySQL)
The above figure displays the insertion of the data into the table “registered” according to the attributes that are created in this particular table. The insertion in the table “registered” is also done using the DML statements.
Figure 16: Data insertion “manager” (Source: XAMPP MySQL)
The above figure displays the insertion of the data into the table “manager” according to the attributes that are created in this particular table. The insertion in the table “manager” is also done using the DML statements.
8.0 Task 5: Query design and implementation
The particular section describes the query and the implementation that is being done and executed on the created database. A total of 5 queries are being executed and they are described below (Martinez et al. 2018).
Figure 17: Query 1 (Source: XAMPP MySQL)
The above query displays the registration id, customer id, name, address, and phone number of the registered customers.
Figure 18: Query 2 (Source: XAMPP MySQL)
The above query displays all types of rooms that are available in the hotel. This displays the room id and room type.
Figure 19: Query 3 (Source: XAMPP MySQL)
The above query displays the equijoin that is being done between the tables “customer” and “registered” on the basis of the attribute “customer_id” (Kung et al. 2019).
Figure 20: Query 4 (Source: XAMPP MySQL)
The above query displays the non-equijoin that is being done between the tables “customer” and “registered” on the basis of the range of “customer_id” (Cheng et al. 2020).
Figure 21: Query 5 (Source: XAMPP MySQL)
The above query displays the information of all the managers that are present in the hotel. This includes the attributes like “manager_id” and “username”.
9.0 Critical Analysis
The particular section will analyze all the things that have been done on the project. In this project, the database of the hotel booking system is being created with various approaches (V. and Semenets, 2018). The relational database is being created with the help of MySQL technology. This creation will allow several data to store in various forms including schema, reports, tables, and so on. In addition to that, security, integrity, and consistency will be maintained in the created database.
First of all, ERM or the “Entity-Relationship Model” is being created followed by the normalization of the tables. After that, the physical design of the tables is being done including the skeleton tables as well as the data dictionary. The following task is the creation of the tables with the help of the MySQL DDL statements and the insertion of data is done using the MySQL DML statements. After the creation of the table and the insertion of the data, some queries are being executed to check the performance of the database (Das et al. 2019).
10.0 Conclusion
The project is based on the creation of the database of the hotel booking system with the approach of MySQL. The database that is being developed is a relational database. This development will enable the storage of a variety of data in many formats, such as schema, reporting, tables, and so on. Confidentiality, integrity, and consistency will also be ensured in the database that is established. The whole implementation is based on the XAMPP MySQL software, which uses MySQL technology to create a relational database for the hotel booking system. In addition to the creation of the database, the ERM, normalization, and physical design are also done.
11.0 Reference List
Journal
Awiti, J., Vaisman, A.A. and Zimányi, E., 2020. Design and implementation of ETL processes using BPMN and relational algebra. Data & Knowledge Engineering, 129, p.101837.
Cao, W., Liu, Y., Cheng, Z., Zheng, N., Li, W., Wu, W., Ouyang, L., Wang, P., Wang, Y., Kuan, R. and Liu, Z., 2020. {POLARDB} Meets Computational Storage: Efficiently Support Analytical Workloads in {Cloud-Native} Relational Database. In 18th USENIX Conference on File and Storage Technologies (FAST 20) (pp. 29-41).
Chai, S.S. and Suh, D., 2019. Design and Implementation of a Multi-Disaster Risk Assessment Database System in South Korea. Information, 11(1), p.8.
Chen, S., Zhang, J., Shi, R., Yan, J. and Ke, Q., 2018, July. A comparative testing on performance of blockchain and relational database: Foundation for applying smart technology into current business systems. In International Conference on Distributed, Ambient, and Pervasive Interactions (pp. 21-34). Springer, Cham.
Chen, Y.Y., Lin, Y.H., Kung, C.C., Chung, M.H. and Yen, I.H., 2019. Design and implementation of cloud analytics-assisted smart power meters considering advanced artificial intelligence as edge analytics in demand-side management for smart homes. Sensors, 19(9), p.2047.
Coban, O., Ali, I.N.A.N. and Ozel, S.A., 2020. Towards the design and implementation of an OSN crawler: A case of Turkish Facebook users. International Journal of Information Security Science, 9(2), pp.76-93.
Das, S., Grbic, M., Ilic, I., Jovandic, I., Jovanovic, A., Narasayya, V.R., Radulovic, M., Stikic, M., Xu, G. and Chaudhuri, S., 2019, June. Automatically indexing millions of databases in microsoft azure sql database. In Proceedings of the 2019 International Conference on Management of Data (pp. 666-679).
Filatov, V. and Semenets, V., 2018, October. Methods for synthesis of relational data model in information systems reengineering problems. In 2018 International Scientific-Practical Conference Problems of Infocommunications. Science and Technology (PIC S&T) (pp. 247-251). IEEE.
Kan, J., Xiaojun, C., Aoming, Q., Jia, L. and Jinmei, W., 2019. Design and Implementation of Teaching Quality Evaluation System Based on SpringBoot. DEStech Transactions on Computer Science and Engineering, (iccis).
Louw, L. and Walker, M., 2018. Design and implementation of a low-cost RFID track and trace system in a learning factory. Procedia Manufacturing, 23, pp.255-260.
Lubis, J.H. and Zamzami, E.M., 2020, June. Relational database reconstruction from SQL to Entity Relational Diagrams. In Journal of Physics: Conference Series (Vol. 1566, No. 1, p. 012072). IOP Publishing.
Nathan, S., Govindarajan, C., Saraf, A., Sethi, M. and Jayachandran, P., 2019. Blockchain meets database: Design and implementation of a blockchain relational database. arXiv preprint arXiv:1903.01919.
Simpao, A.F., Ahumada, L.M., Martinez, B.L., Cardenas, A.M., Metjian, T.A., Sullivan, K.V., Gálvez, J.A., Desai, B.R., Rehman, M.A. and Gerber, J.S., 2018. Design and implementation of a visual analytics electronic antibiogram within an electronic health record system at a tertiary pediatric hospital. Applied clinical informatics, 9(01), pp.037-045.
Stonebraker, M., Wong, E., Kreps, P. and Held, G., 2018. The design and implementation of INGRES. In Making Databases Work: The Pragmatic Wisdom of Michael Stonebraker (pp. 561-605).
Vyawahare, H.R., Karde, P.P. and Thakare, V.M., 2018, August. A hybrid database approach using graph and relational database. In 2018 International Conference on Research in Intelligent and Computing in Engineering (RICE) (pp. 1-4). IEEE.
Know more about UniqueSubmission’s other writing services:
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.