Database Queries Demonstration Report for Booking.com

Assumption

Write a Report  Demonstrate Queries On The Database. Technology Stack Linux Apache, Mysql, Php i Have Been Tasked With Reverse Engineering a Web Based Relational Database For Commercial Accommodation Booking.

Booking.com is world’s one of the most popular platform for ticket booking, hotel booking, flight ticket, rent taxies on the airport. The aim of this assignment to design the probable database of this website. To make the design of Boking.com at first we need to go through the website. After going through their website we got the assumptions about their database and how they work. At first the website proposes the users to create their account, if they already have an account they can simply login to their account. In that case the website has all the details of the customers. After that the website has so many options to check out. Users can book tickets for travelling, going to a place for visit, book a house for rent, rent a car for hours, book taxies on the airport and so on.

For flight ticket booking the users first choose their source and destination. After that in the class they want to travel, for example business class, economy class, premium economy, first class and multiple class. After that they have to provide the total no of person will travel. The cost of ticket will depend on the number of people travel. The time and date users want to take for travelling are also been provided.

For house booking the users have to choose among their preferred choice of house in their range. The facilities it has are also mentioned in the website and users can choose accordingly.

For renting cars users also need to mention the time they want to take the car. Users also have to choose the pickup place. Depending on the hours they want to book the car and sometime on the pickup spot, charges may vary. The users are also given a choice to choose the age of the driver.

Restaurant booking is quite a good choice. Customers can book their desired restaurant and book table for their family. Depending on the restaurant the cost may vary.  Customer have to provide the data and time for their booking.

For hotel booking the users first need to choose the place, hotel name, and their facilities. After that if users want they can book the hotel depending on the cost. Number of people, no of rooms, facilities offered, room quality, check in and check out time are the basic fundamentals of the cost the hotel.

Information of Keys

The whole database design is dependent on the above discussion. The database design is given below.

There are 7 tables are in the database. They are Customer, AccomodationBooking, FlightBooking, Carbooking, AirportTaxi, Trip and Payment. In customer table users all data are stored. Users name, phone no, email, password are stored. In accommodation table details of homes and rent houses are stored. As well as the customer who booked the house and their according price are also stored. In flightBooking table the record of booked ticket, their price, no of people who will travel, their source and destination, class and date- time of flight are also stored. In Carbooking table the user can book car for their use. The details of car and the customers, date and time, how much time they want to book the car and their pickup point are stored. In trip table the city and their popular locations that users want to visit are stored. Total amount of cost, date and time of their travel are also stored. There are also a table called AirportTaxi. In that table the users who coming back to airport, will book the taxi. In that table the details of taxi, time for pick up, source and destination, fare are also stored. At last there are also a table called Payment. In payment table the id of the customer and all the details of the payment are stored.  

There are some assumptions that are taken depending on developing the database design. The website can be accessed by everyone and every customer can create their account or can smoothly login and make their booking. Each customer can create their account with unique values like email. No one can use multiple email for creating their account. Otherwise the confirmation of booking and other booking details are also sent to someone else. No user can register with null values. That means there should be value each and every attribute of the database. After successfully booking their orders the users will get their information about their booking. The sql queries will perform accordingly while storing data or receiving orders from customers. The database will work properly on each and every stages and servers.

The tables are Customer, AccomodationBooking, FlightBooking, Trip, FlightBooking, CarBooking, AirportTaxi, Paymemt.

CustId (Primary key), CustFirstName, CustLastName, CustEmail, CustPhone, CustPassword.

In the above table CustId is a primary key. It will increment after each data entry. It’s data type is integer.

AccobookingId(Primary key), custId(Foreign key), AccoType, AccoAvailability, AccoName, AccoAddress, AccoIn, AccOut, AccoCustCount, AccoCustChildren, AccoPrice, AccoRating.

In the above table AccoBookingId is the primary key. It is integer in type. CustId is the foreign key and it is also integer in type. It came from the Customer table.

FlightBookinmgID (Primatry key), CustId(Foreign key), FlightSource, FlightDestination, FlightDateTime, FlightClass, FlightCost, FlightSeatCount.

FlightBookingId is the primary key. . It is integer in type. CustId is the foreign key and it is also integer in type. It came from the Customer table.

CarbookingId(Primary key), CustId(ForeignKey), DriverAge, Source, Destination, PickUpdateTime, ReturnDateTime,Fare.

CarbookingId is the primary key. It is integer in type. CustId is the foreign key and it is also integer in type. It came from the Customer table.

TaxiBookingId(Primary key), CustId(ForeignKey), AvailableTaxi, PickUpLocation, DropLocation, BookingDateTime, Passengers, taxifare, TaxiRating.

TaxiBookingId is the primary key. It is integer in type. CustId is the foreign key and it is also integer in type. It came from the Customer table.

TripId Primary key), CustId(ForeignKey), Citry, Location, TripDateTime, TripTime, Cost.

TripId is the primary key. It is integer in type. CustId is the foreign key and it is also integer in type. It came from the Customer table.

In Customer table at first the customer log in with their registered email id or can simply create a new account. After that they can book anything according to the options provided in the website. Each user has a unique customer id. When they logged in, their session got started. After that whenever they book anything from the website, the booking is done with their customer id. The record of their booking are stored in various tables. For each purpose there are some certain tables. Whenever the customer books anything their record is stored in the respective booking table with their customer id. Customer id is the common foreign key in this database. Each table need customer id to store the record of each customer’s specific bookings. After that they have to choose various other things. For example in case of hotel booking customers need to mention the room type, facilities of the room, number of people, number of room and most importantly their check-in and check-out date. After that the customers are sent to the payment gateway where they can make the payment. The payment gateways are reliable and smooth. After completion of booking, their all data are stored in the table with respected foreign key, customer id.

 Each table has some unique attribute called primary key. It would help to find out the details of each data.

The tables should be in 3nf. The design of the database is fully in 3nf. Each attribute has only one value and no null value is present. In each table a unique value is present. There exist no functional transitive dependency. So the table is in 3nf.

The entity relationship diagram is done in MS Visio and all relationships are made properly.

There exists no transitive functional dependency in the table.

Each and every probable attributes are also included.

In the database design each and every table should be in 3nf. So it is important to store one and only one value. The decisions that are taken due to normalization are given below.

Normalization is the process in which the database is structured in a so called normal forms. 3 forms of Normalization are there. They are first normal form (1nf), second normal form (2nf), and third normal form (3nf). In 1nf each and every attribute of the database should have one and only one value. Multiple values or null values should not be present in the database table. If exists then the table in not in 1nf. In the database we have the table which has one and only one value per attribute.

For second normal form all the tables should be in first normal form and also have one uniquely identified attribute, which is known as primary key. In the database each and every table has a primary key. Primary is of two type, simple and composite. If any primary key is made with one on attribute then it is known as simple primary key and if the primary key is made of more than one value then the primary key is known as composite primary key. The primary keys that are used in the database are all simple primary key.

For third normal form all the tables should be in 2nf and no transitive functional dependencies should exist in the relation. The tables we have in our database there exist no transitive functional dependencies.

Therefore the tables we have in our database are in 3nf. This are the normalization decisions which are taken while structuring the database.

Some improvements can be taken for the website. There should be an extra payment table for recording the payments. It would help the company to track, manipulate the costs and benefits from the bookings. There should be some complete and details tables of the cities and travel spots all over the world. Users can go from one table to another to choose their destination among all the travel spots in all over the world. Website should be more reliable and should be user friendly. Users should be given some discounts depending on the fares or charge. Sometime there are not enough seats in the flight, no available taxies, number of rooms, no of cars. There should be enough availability for the customers at all time.

These were the website design recommendations. Now we will discuss the probable improvements that should be performed on the database. Database can be more normalized and there can be more attributes regarding to smoother data handling. For each and every booking platform there should be some more tables to handle the data. For each transport there can be separate table. For each booking, there should be a table which take users details, and their chosen plan or booking.

The focus of this assignment is to create the database of Booking.com. The tables store the daily details of data and the booking history of customers. Users can simply create their account and then make their booking.  The business concerns that had according to the website is that it consists of all the travel supports. It offer the users to travel all over the world. It can make a great difference in their profit. The company also offers the customers to rent a car, book a taxi in the airport. This would help the customer to reach their destination. Customers also can book hotels to stay in their destination place. The users also can book a home for them. They can book a house for rent. They also offers the customers to make payment for their bookings from some reliable sources. Users can easily make transaction without any problem. These supports the business rules and goals of a website.

SQL is the Structure Query Language. The database is checked based upon the SQL query check. Each and every table has some data and they are checked if they are working properly or not. It can be stated that all the SQL statements that are ran for checking the website details are correct and appropriate.

SQL is used to check that if the website is running perfectly or not. If we reverse engineer the website, we can see that the sql queries that are used to create the database, retrieve data from database, data insertion are perfect. While booking any kind of order the website and the structure of the database are working properly.

References

Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), pp.15-26.

Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), pp.15-26.

Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), pp.15-26.

Arenas, M., Diaz, G.I. and Kostylev, E.V., 2016, April. Reverse engineering SPARQL queries. In Proceedings of the 25th International Conference on World Wide Web (pp. 239-249). International World Wide Web Conferences Steering Committee.

Bailis, P., Fekete, A., Franklin, M.J., Ghodsi, A., Hellerstein, J.M. and Stoica, I., 2014. Coordination avoidance in database systems. Proceedings of the VLDB Endowment, 8(3), pp.185-196.

Cellary, W., Morzy, T. and Gelenbe, E., 2014. Concurrency control in distributed database systems (Vol. 3). Elsevier.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Dadjoo, M. and Kheirkhah, E., 2015. An approach for transforming of relational databases to OWL ontology. arXiv preprint arXiv:1502.05844.

Dennis, A., Wixom, B.H. and Roth, R.M., 2018. Systems analysis and design. John wiley & sons.

Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.

Lin, L., Xu, Z. and Ding, Y., 2013. OWL Ontology Extraction from Relational Databases via Database Reverse Engineering. JSW, 8(11), pp.2749-2760.

Oktafianto, M.R., Al Akbar, Y.F., Zulkifli, S. and Wulandari, A.M., 2018. Dismissal working relationship using analytic hierarchy process method. International Journal of Pure and Applied Mathematics, 118(7), pp.177-184.

Paulic, M., Irgolic, T., Balic, J., Cus, F., Cupar, A., Brajlih, T. and Drstvensek, I., 2014. Reverse engineering of parts with optical scanning and additive manufacturing. Procedia engineering, 69, pp.795-803.

Ristić, S., Aleksić, S., ÄŒeliković, M., Dimitrieski, V. and Luković, I., 2014. Database reverse engineering based on meta-models. Open Computer Science, 4(3), pp.150-159.

Taylor, C. and Colberg, C., 2016. A tool for teaching reverse engineering. In 2016 {USENIX} Workshop on Advances in Security Education ({ASE} 16).

Villaverde, A.F. and Banga, J.R., 2014. Reverse engineering and identification in systems biology: strategies, perspectives and challenges. Journal of the Royal Society Interface, 11(91), p.20130505.

Weiss, Y.Y. and Cohen, S., 2017, May. Reverse engineering spj-queries from examples. In Proceedings of the 36th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems (pp. 151-166). ACM.

Xia, Z., 2014. Application of reverse engineering based on computer in product design. International Journal of Multimedia and Ubiquitous Engineering, 9(5), pp.343-354.

Yoshizumi, T., Kirishima, T., Goto, T., Tsuchida, K. and Yaku, T., 2016, July. A graph grammar for entity relationship diagrams. In 2016 IEEE 14th International Conference on Industrial Informatics (INDIN) (pp. 810-815). IEEE.

Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., Lian, Z., Liang, L., Chen, W., Luo, X. and Pei, S., 2017. Probabilistic Entity-Relationship Diagram: A correlation between functional connectivity and spontaneous brain activity during resting state in major depressive disorder. PloS one, 12(6), p.e0178386.