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.

CIS017-1 Database and Computer Networks Assignment Sample 1Figure 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 email 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 email 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).

CIS017-1 Database and Computer Networks Assignment Sample 2Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 3Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 4Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 5Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 6Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 7Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 8Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 9Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 10Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 11Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 12Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 13Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 14Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 15Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 16Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 17Figure 17: Query 1 (Source: XAMPP MySQL)

The above query displays the registration id, customer id, name, address, and phone number of the registered customers.

CIS017-1 Database and Computer Networks Assignment Sample 18Figure 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.

CIS017-1 Database and Computer Networks Assignment Sample 19Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 20Figure 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).

CIS017-1 Database and Computer Networks Assignment Sample 21Figure 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 Engineering129, 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. Information11(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. Sensors19(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 Science9(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 Manufacturing23, 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 informatics9(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:

Assignment Writing Help

Essay Writing Help

Dissertation Writing Help

Case Studies Writing Help

MYOB Perdisco Assignment Help

Presentation Assignment Help

Proofreading & Editing Help

Leave a Comment