Information Systems and Databases

CMP111

Information Systems and Databases

Student ID:

Student Name:

Table of Figures

Introduction

This report explains the development of databases using a database tool for an organisation's application. The report focuses on the relational databases for the business operations of a vacation rental management firm Vacation Stay. The key feature addressed is an effective and structured database for storing and maintaining data related to reservations, property information, and guests. This system will ease the booking process, eliminate data redundancy, and ensure that any necessary information is easily accessible to guests and property managers. The report describes steps from database design using normalization up to 3NF and further to the actual implementation of the database using phpMyAdmin and SQL in the XAMPP environment. The following major functions are designed in the database to handle the case of a Vacation Stay.

• Efficient management of your property listings, guest information, and bookings.

• Reduces multiple entries or records of data.

• Making sure of referential integrity between different entities, like guests and bookings.

• Automation in booking and cancellation processes.

• Generation of reports that provide insight, such as revenues by properties and the guest reservation history.

Task1: Design

1.1. Normalization to 3NF

Normalization is the process of organizing data into tables in an attempt to reduce or eliminate redundancy and improve the integrity of data (Albarak, et al., 2020). In this assignment, the database is normalized to the third normal form, 3NF. There are multiple stages of the normalization process of the dataset.

Un-Normalized Data (0NF)

To begin with, all the data concerning bookings of the application Vacation Stay are represented in one table with an unstructured form. The table contains the property to which a guest would be booked, then guest information, and information that concerns the booking itself (Jayasekara, 2022).

Bookings: (BookingID, PropertyName, PropertyType, PropertyAddress, PricePerNight, GuestName, GuestEmail, BookingDate, CheckInDate, CheckOutDate, TotalCost)

In the UnNormalized form (0NF) is that in which the data can have repetitiveness because the properties and guests may have possibly repeating records.

First Normal Form (1NF)

The data has been separated into a distinct table to reach 1NF, where each has atomic values meaning there are no repeating groups (Hazelton and Wu, 2022). There is still some data redundancy, but less than in the previous Un-Normalized form. This database has now increased from one to three separate tables that are ‘Properties’, ‘Guests’ and ‘Bookings’.

Properties: (PropertyID, PropertyName, PropertyType, PropertyAddress, PricePerNight)

Guests: (GuestID, GuestName, GuestEmail, GuestPhone)

Bookings: (BookingID, PropertyID, GuestID, BookingDate, CheckInDate, CheckOutDate, TotalCost)

Second Normal Form (2NF):

To get the properties of 2NF, partial dependencies, and non-key attributes depending only on a part of a composite primary key are removed. Since there are no composite primary keys here, and in each table, non-key attributes depend fully on the primary key, the structure of the database remains as it was, but now it is in 2NF (Ceruti, 2021).

Third Normal Form (3NF):

In this form the transitive dependencies are eliminated that is no non-primary key attribute should depend on another non-primary key attribute. In this case, the tables already meet 3NF since there are no transitive dependencies. The Third Normal form (3NF) normalization has been carried out on the database to minimize redundancy (Link and Wei 2021). Therefore, due to this form, the integrity of the data is maintained.

1.2. Entity Relationship Diagram (ERD)

The ER diagram shall outline the various relationships that are involved in the database structure (Wijaya, Andhika and Ilyas, 2022). The Entities identified are:

Properties: This is an entity that holds the data regarding rental properties, their name, type, address, and price per night.

Guests: This category includes the guest's information, such as name, email address, and phone.

Bookings: This is the entity for every booking by any guest. This may include, among others, the date of the booking, total cost, and relationship between guests and properties.

Relationships:

One guest can make multiple bookings, so there is a one to many relationships between ‘Guests’ and ‘Bookings’.

It would be very unusual if a property could accommodate only one guest booking which is the relationship between Properties and Bookings is one too many.

The Bookings table has the associative entity for the Guests and Properties tables, showing which guest has booked which property.

Figure 1ERD Diagram

1.3. Data Dictionary

The data dictionary explains the fields of each table of the database and their data types.

Properties Table:

• ‘Property_ID’ (Primary Key) – INT, AUTO_INCREMENT

• ‘PropertyName’ – VARCHAR(255)

• ‘Property_Type’ – VARCHAR(50)

• ‘Property_Address’ – VARCHAR(255)

• ‘Price_Per_Night’ – DECIMAL(10, 2)

Guests Table:

• ‘Guest_ID’ (Primary Key) – INT, AUTO_INCREMENT

• ‘Guest_Name’ – VARCHAR(255)

• ‘Guest_Email’ – VARCHAR(255)

• ‘Guest_Phone’ – VARCHAR(15)

Bookings Table:

• ‘Booking_ID’ (Primary Key) – INT, AUTO_INCREMENT

• ‘Property_ID’ (Foreign Key) – INT

• ‘Guest_ID’ (Foreign Key) – INT

• ‘Booking_Date’ – DATE

• ‘Check_In_Date’ – DATE

• ‘Check_Out_Date’ – DATE

• ‘Total_Cost’ – DECIMAL(10, 2)

Task 2: Database Build

2.1. Table Creation

In this task, it involves the creation of tables by executing SQL queries in phpMyAdmin. These are based on the ERD and data dictionary from Task 1 and explain the overview of how tables have been created.

Properties Table

Figure 2 Queries for the Properties Table

Figure 3 Execution of the Properties Table Query

Guest Table

Figure 4 Query for the Guest Table

Figure 5 Execution of the Guest Table Query

Bookings Table33

Figure 6 Query for the Bookings Table

Figure 7 Execution of the Booking Table Query

2.2. Naming Conventions

The table and field names have been done using Camel Case for ease of readability. All primary keys have been suffixed with ‘ID’ to denote their role, while foreign keys utilize the same corresponding field names as the primary keys they point to (Eessaar, 2023).

2.3. Relationship & Integrity

Foreign key constraints have been established between the ‘Bookings’ table and both the ‘Properties’ and ‘Guests’ tables. This kind of relationship maintains integrity in the data that one cannot have any booking without the valid reference of a guest and a property (Suárez-Cabal, et al., 2023).

To confirm the relationships in ‘phpMyAdmin’ which is used for reviewing or setting foreign key constraints. For this relationship, go to the Structure tab of the ‘Bookings’ table and select Relation View.

Task 3: SQL Queries and Procedures

After the creation of the database and various tables regarding the ‘Vacation Stay’ multiple ‘SQL’ can be performed through which the data can be obtained, manipulated, etc.

3.1. SQL Queries

Retrieve Available Properties for a Specific Date Range:

This SQL query returns those properties that are not booked within a certain date range and hence will check efficiently for availability.

Figure 8 SQL query for the Retrieval of the specific date range data

Figure 9 Results of the Retrieval of the specific date Range

Retrieve a Guest’s Booking History:

This SQL query retrieves the history of bookings by a guest showing details of past bookings.

Figure 10 SQL query for the Retrieval of the guest booking history

Figure 11 Execution of the SQL Query for the retrieval of the guest booking history

Calculate the Total Revenue Generated by Each Property:

This SQL query gives the total cost of bookings per property, hence providing insight into revenues.

Figure 12 SQL query for the Total revenue generated by each property

Figure 13 Execution of the SQL query of the total revenue generated by the property

Retrieve the Guest List for a Specific Property:

This is an SQL query that lists the bookings of guests who have booked a particular property.

Figure 14 SQL query for the guest list of a specific property

Figure 15 Execution of the Guest list of a specific property

List All Properties and Their Booking Counts:

This SQL query counts the bookings made for each property.

Figure 16 SQL query for the property booking count

Figure 17 Execution of the booking count of each property

3.2. Stored Procedures

To make the operations easy, and for easy management of the platform, there are stored procedures developed for frequent activities such as adding a booking or updating the status of bookings.

Add Booking Procedure:

The following stored procedure will insert a booking in case the property is available for those dates.

Figure 18 ADD booking procedure.

Figure 19 Execution of the Add Booking Procedure

Update Booking Status Procedure

This Update booking status stored procedure confirms a booking and edits the status to "Confirmed", "Cancelled", etc.

Figure 20 Update booking status Procedure.

Figure 21 Execution of the updated booking status procedure

Calculate Total Cost

This stored procedure calculates the total cost of a booking based on the number of nights between the Check_In and Check_out date, then multiplies it with the Property price per The relational database design for the business operation of a vacation rental night.

Figure 22 Calculate the Total cost procedure.

Add Property

This procedure adds a new property by taking the property name, type, address, and price per night as input. It inserts the given input into the Properties table effectively.

Figure 23 Add property Procedure.

Delete Booking

This procedure removes a booking as per the given booking ID checks if the booking exists before deletion and throws an error in case no records are found.

Figure 24 Delete booking Procedure.

Conclusion

In other words, the development of the database for Vacation Stay by using XAMPP with phpMyAdmin will ensure a well-structured and normalized system of the database. The key functionalities supported include property management, guest management, and booking management, ensuring referential integrity since foreign key constraints are provided. Additionally, SQL queries and stored procedures provide the ability to automate common tasks such as checking the availability of bookings and calculating revenue. This thorough solution enhances data management with minimal redundancy and ensures better operational efficiency for the Company, Vacation Stay. In this project, database design and implementation have laid a pretty strong foundation for any future extension, whether it would be more tables, features, or interfacing with a UI or web application. The flexibility of the designed database schema will allow it to meet new requirements with ease while ensuring data integrity and performance sustainability.

References

Albarak, M., Bahsoon, R., Ozkaya, I. and Nord, R. (2020) ‘Managing technical debt in database normalization’, IEEE Transactions on Software Engineering, 48(3), pp.755-772. https://www.computer.org/csdl/api/v1/periodical/trans/ts/5555/01/09113328/1kxX5eZor28/download-article/pdf?casa_token=WfctAPJ1c8YAAAAA:JRkLjUPjB2J6GIHPFEkAFSLPtILgsSyOTwd4rxKh-nkEx6K6HMjZxBdKGhqKKpbFCEVSbJJ2slCT

Ceruti, M.G., (2021), ‘A Review of Database System Terminology’, Handbook of Data Management 1999 Edition, pp.13-31. https://apps.dtic.mil/sti/pdfs/ADA364505.pdf

Eessaar, E., (2023), ‘On the Naming of Database Objects in the SQL Databases of Some Existing Software’, In Computer Science On-line Conference (pp. 534-550). Cham: Springer International Publishing. https://www.researchgate.net/profile/Erki-Eessaar/publication/372228088_On_the_Naming_of_Database_Objects_in_the_SQL_Databases_of_Some_Existing_Software/links/64df5b9c14f8d173380a4753/On-the-Naming-of-Database-Objects-in-the-SQL-Databases-of-Some-Existing-Software.pdf

Hazelton, N.W.J. and Wu, Y., (2022), ‘Re-thinking Data Models: GIS Is/As a Relational Database’. Surveying and Land Information Science, 81(2), pp.73-88. https://geosages.org/pdfs/Conf_2019/Paper_28_Hazelton.pdf

Jayasekara, G.P.D.C.M., (2022), ‘Library Management System (LMS) Database: Case Study Analysis’, Library Management System (LMS) Database: Case Study Analysis https://www.researchgate.net/profile/Chamoth-Jayasekara-2/publication/364484967_Library_Management_System_LMS_Database_Case_Study_Analysis/links/635bf21996e83c26eb6141e3/Library-Management-System-LMS-Database-Case-Study-Analysis.pdf

Link, S. and Wei, Z., (2021), ‘Logical schema design that quantifies update inefficiency and join efficiency’, In Proceedings of the 2021 International Conference on Management of Data (pp. 1169-1181). https://researchspace.auckland.ac.nz/bitstream/handle/2292/58015/553.pdf?sequence=1&isAllowed=y

Suárez-Cabal, M.J., Suárez-Otero, P., de la Riva, C. and Tuya, J., (2023), ‘MDICA: Maintenance of data integrity in column-oriented database applications’. Computer Standards & Interfaces, 83, p.103642. https://www.sciencedirect.com/science/article/pii/S0920548922000204

Wijaya, S., Andhika, A. and Ilyas, M., (2022), ‘Development of Sales Information System for SME with the Waterfall Method: A Grocery Store BSR Case’. Jurnal Teknik Informatika (JUTIF), 3(4), pp.1043-1049. https://www.researchgate.net/profile/Santo-Wijaya/publication/364835618_DEVELOPMENT_OF_SALES_INFORMATION_SYSTEM_FOR_SME_WITH_THE_WATERFALL_METHOD_A_GROCERY_STORE_BSR_CASE/links/635d0f1e6e0d367d91d908d4/DEVELOPMENT-OF-SALES-INFORMATION-SYSTEM-FOR-SME-WITH-THE-WATERFALL-METHOD-A-GROCERY-STORE-BSR-CASE.pdf