Database System For Hotel Bookings And Payments

Guest Information

We have created an ER diagram for Car service company management. For creating the RED we followed some business rules that are shown below: (Silberschatz and Korth, 2010)

customer may come for car servicing
When any customer takes one or any number of cars in for repairing or servicing then a service ticket is written for car always one service ticket is written for each car.
The car service company will maintain a service history for each of the cars serviced. The service ticket are referenced by the car id.

Database Schema:

Below we have shown the database schema which shows the description of entity and attribute and keys. (Elmasri and Navathe, 2010)

Forieng Kay Constraints:

Here I am showing the screen captures of relationship between each table by following the below integrity constraints: (Mark, 2011)

A car that are brought for servicing can be serviced by many mechanics, and every mechanic can work on many cars.
A car that is serviced may or may not need parts. (For example, adjusting a carburetor or cleaning a fuel injector nozzle does not require the use of parts).
A customer can initiates for a car service through a service ticket.
For each customer one service ticket will genrate for each car service request.carid of a car to look up history of service ticket.
Mechanics are associated with cars and the services they perform on cars through the service_mechanic table. The service_mechanic table associates information about the service done with the service ticket.
Cars serviced often times use parts. The Parts Used table associates the parts with the service ticket.

Below screen captures will show the foreign key constraints for every tables which are connected through the relationship of primary/foreign key. (Greenspan and Bulger, 2001).

Table Description and Insertion:

We are showing here the table structure and first five row of data for each table and also showing the insertion in table customer and car. (Williams and Lane, 2004)

Table Car:

describe car;

+———–+————-+——+—–+———+—————-+

+———–+————-+——+—–+———+—————-+

| Carid | int(2) | NO | PRI | NULL | auto_increment |

+———–+————-+——+—–+———+—————-+

5 rows in set (0.00 sec)

select * from car limit 5;

+——-+————+——+————+——+

+——-+————+——+————+——+

| 1 | 118743 | Audi | 2009 Audi | 2009 |

| 2 | 230973 | Audi | 2009 Audi | 2009 |

| 3 | 1243 | Audi | 2009 Audi | 2009 |

| 4 | B39109 | Audi | 2009 Audi | 2009 |

| 5 | 574950 | Audi | 2009 Audi | 2009 |

+——-+————+——+————+——+

5 rows in set (0.00 sec)

INSERTION

INSERT INTO `car`(carid,SerialNum,Make,Model,Year) VALUES (default,347656,’Nissan’,’2011 Nissan Altima Coupe’,2011);

Query OK, 1 row affected, 1 warning (0.10 sec)

MariaDB [carservicemanagement]> INSERT INTO `car`(carid,SerialNum,Make,Model,Year) VALUES (default,625998,’Volvo’,’2012 Volvo S80′,2012);

Query OK, 1 row affected, 1 warning (0.09 sec)

Table countrylookup:

describe countrylookup;

+———–+————-+——+—–+———+——-+

+———–+————-+——+—–+———+——-+

| CountryId | int(2) | NO | PRI | NULL | |

+———–+————-+——+—–+———+——-+

2 rows in set (0.00 sec)

select * from countrylookup limit 5;

+———–+————+

| CountryId | Country |

+———–+————+

| 1 | Algeria |

| 2 | Australia |

| 3 | Austria |

| 4 | Bangladesh |

| 5 | Belgium |

+———–+————+

5 rows in set (0.07 sec)

Table customer:

describe customer;

+———–+————-+——+—–+———+—————-+

+———–+————-+——+—–+———+—————-+

| Custid | int(2) | NO | PRI | NULL | auto_increment |

| country | int(2) | NO | MUL | NULL | |

+———–+————-+——+—–+———+—————-+

10 rows in set (0.00 sec)

select * from customer limit 5;

]> +——–+———-+———–+————+————+————+——-+———+———-+————+

+——–+———-+———–+————+————+————+——-+———+———-+————+

| 1 | Ritter | Noel | 0402147779 | 902 91A BR | WESTMEAD | NSW | 2 | | [email protected] |

| 2 | Jainani | William | 0414307905 | 130 HAMRUN | ROOTY HILL | NSW | 2 | 2766 | [email protected] |

| 3 | Franklin | Mark | 0403551056 | 5 FRANLEE | DURAL | NSW | 2 | | [email protected] |

| 4 | Adams | Nancy | 0404892387 | 18 WESTMOR | WEST PENNA | NSW | 2 | 2125 | |

| 5 | Burr | Rick | 0416162738 | 20 MOUNT S | GLENBROOK | NSW | 2 | | mat.shanil |

+——–+———-+———–+————+————+————+——-+———+———-+————+

5 rows in set (0.04 sec)

INSERTION Customer:

INSERT INTO `customer`(custid,lastname,firstname,phone,address,suburb,state,country,postcode,Email) VALUES (default,’Hamlin’,’Bill’,041825836998,’St Helena Parade’

,’Westmead’,’NSW’,2,2145,”);

Query OK, 1 row affected, 2 warnings (0.61 sec)

INSERT INTO `customer`(custid,lastname,firstname,phone,address,suburb,state,country,postcode,Email) VALUES (default,’Marsh’,’Steve’,140255522,’Kings Court’,’Blackt

own’,’NSW’,’2′,2148,”);

Query OK, 1 row affected, 1 warning (0.09 sec)

Table Invoice:

describe invoice;

]> +———+————–+——+—–+———+——-+

+———+————–+——+—–+———+——-+

| InvoId | int(2) | NO | PRI | NULL | |

| InvoNum | int(20) | NO | | NULL | |

| CarId | int(2) | NO | MUL | NULL | |

| CustId | int(2) | NO | | NULL | |

+———+————–+——+—–+———+——-+

6 rows in set (0.00 sec)

select * from invoice limit 5;

+——–+———+————+——-+——–+———+

+——–+———+————+——-+——–+———+

| 1 | 134521 | 2015-01-14 | 1 | 1 | 493.85 |

| 2 | 134522 | 2015-01-02 | 2 | 2 | 38.87 |

| 3 | 134523 | 2015-01-02 | 5 | 3 | 100.00 |

| 4 | 134524 | 2015-01-03 | 7 | 4 | 2000.00 |

| 5 | 134525 | 2015-01-05 | 3 | 18 | 5000.00 |

+——–+———+————+——-+——–+———+

5 rows in set (0.07 sec)

Table Mechanic:

describe mechanic;

+———–+————-+——+—–+———+—————-+

+———–+————-+——+—–+———+—————-+

| MechId | int(2) | NO | PRI | NULL | auto_increment |

+———–+————-+——+—–+———+—————-+

3 rows in set (0.01 sec)

select * from mechanic limit 5;

+——–+———-+————+

| MechId | LastName | FirstName |

+——–+———-+————+

| 1 | Andrew | Dwight |

| 2 | Paul | Velasco |

| 3 | Mary | Sharp |

| 4 | Gloria | Cartwright |

| 5 | David | Singer |

+——–+———-+————+

5 rows in set (0.00 sec)

Table Parts:

describe parts;

+————-+————–+——+—–+———+—————-+

+————-+————–+——+—–+———+—————-+

| PartId | int(2) | NO | PRI | NULL | auto_increment |

+————-+————–+——+—–+———+—————-+

5 rows in set (0.00 sec)

select * from parts limit 5;

+——–+————+————-+————+————-+

+——–+————+————-+————+————-+

| 1 | 118743 | Shoebrake | 493.85 | 493.85 |

| 2 | 230973 | clutch | 38.87 | 38.87 |

| 3 | 1243 | headlight | 45.00 | 45.00 |

| 4 | B39109 | steering | 100.00 | 100.00 |

| 5 | 574950 | oil | 200.00 | 0.00 |

+——–+————+————-+————+————-+

5 rows in set (0.00 sec)

Table Partsused:

describe partsused;

+————+————–+——+—–+———+—————-+

+————+————–+——+—–+———+—————-+

| PartUsedId | int(2) | NO | PRI | NULL | auto_increment |

| ServTickId | int(2) | NO | MUL | NULL | |

| PartId | int(2) | NO | MUL | NULL | |

| NumUsed | int(5) | NO | | NULL | |

+————+————–+——+—–+———+—————-+

5 rows in set (0.00 sec)

select * from partsused limit 5;

+————+————+——–+———+——–+

+————+————+——–+———+——–+

| 1 | 4 | 5 | 1 | 493.85 |

| 2 | 5 | 5 | 1 | 38.87 |

| 3 | 6 | 5 | 1 | 45.00 |

| 4 | 7 | 5 | 1 | 0.00 |

| 5 | 8 | 5 | 1 | 0.00 |

+————+————+——–+———+——–+

5 rows in set (0.00 sec)

Table paymentmethods:

describe paymentmethods;

+—————–+————-+——+—–+———+——-+

+—————–+————-+——+—–+———+——-+

| PaymentMethodId | int(2) | NO | PRI | NULL | |

+—————–+————-+——+—–+———+——-+

3 rows in set (0.00 sec)

select * from paymentmethods limit 5;

+—————–+————+————-+

| PaymentMethodId | MethodType | CreditCart? |

+—————–+————+————-+

| 1 | Cash | FALSE |

| 2 | Amex | TRUE |

| 3 | VISA | TRUE |

| 4 | Cheque | FALSE |

| 5 | Mastercard | TRUE |

+—————–+————+————-+

5 rows in set (0.00 sec)

Table payments:

describe payments;

+—————+————–+——+—–+———+—————-+

+—————+————–+——+—–+———+—————-+

| PaymentId | int(2) | NO | PRI | NULL | auto_increment |

| InvoId | int(2) | NO | MUL | NULL | |

| PaymentMethod | int(2) | NO | MUL | NULL | |

+—————+————–+——+—–+———+—————-+

5 rows in set (0.00 sec)

select * from payments limit 5;

+———–+——–+—————+————-+—————+

+———–+——–+—————+————-+—————+

| 1 | 1 | 567.90 | 2014-08-15 | 1 |

| 2 | 2 | 700.00 | 2014-06-22 | 2 |

| 3 | 3 | 660.00 | 2014-06-30 | 4 |

| 4 | 4 | 523.50 | 2014-05-29 | 2 |

| 5 | 6 | 795.00 | 2014-03-03 | 5 |

+———–+——–+—————+————-+—————+

5 rows in set (0.00 sec)

Table service:

describe service;

+————-+————–+——+—–+———+—————-+

+————-+————–+——+—–+———+—————-+

| ServiceId | int(2) | NO | PRI | NULL | auto_increment |

+————-+————–+——+—–+———+—————-+

3 rows in set (0.00 sec)

select * from service limit 5;

+———–+————-+————+

| ServiceId | ServiceName | HourlyRate |

+———–+————-+————+

| 1 | Schedule M | 200.00 |

| 2 | Brake Repa | 50.00 |

| 3 | Pre Purcha | 100.00 |

| 4 | Fluid Repl | 300.00 |

| 5 | Tyre Repai | 70.00 |

+———–+————-+————+

5 rows in set (0.04 sec)

Table serviceticket:

describe serviceticket;

+————+————-+——+—–+———+—————-+

+————+————-+——+—–+———+—————-+

| ServTickId | int(2) | NO | PRI | NULL | auto_increment |

| Carid | int(2) | NO | MUL | NULL | |

| CustId | int(2) | NO | MUL | NULL | |

+————+————-+——+—–+———+—————-+

6 rows in set (0.00 sec)

select * from serviceticket limit 5;

+————+——-+——–+————+———————-+————+

+————+——-+——–+————+———————-+————+

| 1 | 5 | 3 | 2015-01-12 | PM SERVICE, CHECK TU | 2015-01-14 |

| 2 | 6 | 4 | 2015-01-01 | SERVICEROB,EXT,5604 | 2015-01-02 |

| 3 | 7 | 5 | 2015-01-01 | NEED 4 PLOW PINS | 2015-01-02 |

| 4 | 8 | 8 | 2015-01-02 | INSTALL SPINNER ASSY | 2015-01-03 |

| 5 | 9 | 9 | 2015-01-03 | DONT START | 2015-01-05 |

+————+——-+——–+————+———————-+————+

5 rows in set (0.00 sec)

Table service_mechanic:

describe service_mechanic;

+—————+————–+——+—–+———+—————-+

+—————+————–+——+—–+———+—————-+

| ServiceMechId | int(2) | NO | PRI | NULL | auto_increment |

| ServiceId | int(2) | NO | MUL | NULL | |

| MechId | int(2) | NO | MUL | NULL | |

| ServTickId | int(2) | NO | MUL | NULL | |

+—————+————–+——+—–+———+—————-+

6 rows in set (0.00 sec)

select * from service_mechanic limit 5;

+—————+———–+——–+————+—————+——–+

+—————+———–+——–+————+—————+——–+

| 1 | 5 | 1 | 6 | 00:00:02.0000 | 500.00 |

| 2 | 7 | 6 | 7 | 00:00:03.0000 | 600.00 |

| 3 | 1 | 3 | 8 | 00:00:02.0000 | 200.00 |

| 4 | 3 | 2 | 9 | 00:00:05.0000 | 300.00 |

| 5 | 2 | 5 | 10 | 00:00:06.0000 | 800.00 |

+—————+———–+——–+————+—————+——–+

5 rows in set (0.00 sec)

SQL Select Queries

Question 1:

List the first name, last name of Customer (join guest first and last name with a space in between and use the alias Customer Name for the column heading), and email address (for all guests that have an email address only). Sort the output in ascending order by the guest last name.

SQL

select CONCAT(FirstName, ‘ ‘ , LastName) as”Customer Name” , Email from Customer ORDER BY LASTNAME ASC;

OUTPUT

+—————–+————+

| Customer Name | Email |

+—————–+————+

| Nancy Adams | |

| Julie Blunt | |

| Rick Burr | mat.shanil |

| James Calgary | [email protected] |

| Edwina Calhoun | |

| Sam Clinton | |

| Yvonne Colfax | |

| Jim Daton | [email protected] |

| Mark Franklin | [email protected] |

| Larry Gerry | |

| Bill Hamlin | |

| William Jainani | [email protected] |

| Ken Jefferson | |

| Wendy Johnson | [email protected] |

| Y. Kent | |

| Steve Marsh | |

| E. Poirot | |

| Len Raymond | |

| Noel Ritter | [email protected] |

| U. Sheppard | |

| Don Tompkins | [email protected] |

| Dan Van Buren | |

| Vaughn Wilson | |

+—————–+————+

23 rows in set (0.00 sec)

Question 2:

List the customer last name for all customer that have a suburb that has the word ‘hill’ anywhere in the suburb name. Sort the output ascending order by the customer name.

SQL:

select LastName, suburb from customer where suburb like ‘% hil%’ order by lastName asc;

OUTPUT:

+———-+————+

| LastName | suburb |

+———-+————+

| Jainani | ROOTY HILL |

+———-+————+

1 row in set (0.00 sec)

Question 3:

List the payment method description and sum of paymentamounts for all payments which were made after 1-July-2014.

SQL:

select pm.methodTYPE, sum(p.paymentamount) as Total from payments p, paymentmethods pm where p.paymentmethod=pm.paymentmethodid AND p.paymentdate > ‘2014-07-01’gro

up by pm.methodtype;

OUTPUT:

+————+———+

| methodTYPE | Total |

+————+———+

| Amex | 1302.00 |

| Cash | 1463.04 |

| Cheque | 571.00 |

| Mastercard | 1143.06 |

| VISA | 2363.07 |

+————+———+

5 rows in set (0.14 sec)

Question 4:

List of the mechanic members who have earned more than 400 so far working for Car Service company

SQL:

SELECT m.firstname, m.lastname, sm.rate,(sm.hours*rate)AS TOTAL_EARNING FROM mechanic m, service_mechanic sm WHERE m.mechid=sm.mechid AND (sm.hours*rate)>400 ORDER

BY TOTAL_EARNING DESC;

OUTPUT:

+———–+———-+——–+—————+

+———–+———-+——–+—————+

| Singer | David | 800.00 | 4800.00 |

| Saleh | Fatima | 600.00 | 1800.00 |

| Velasco | Paul | 300.00 | 1500.00 |

| Velasco | Paul | 500.00 | 1500.00 |

| Fenton | Tom | 800.00 | 800.00 |

| Dwight | Andrew | 500.00 | 500.00 |

+———–+———-+——–+—————+

6 rows in set (0.00 sec)

Question 5:

List the details of the part records from the parts table that have not been used in any servicing

SQL:

SELECT * FROM parts P WHERE NOT EXISTS (SELECT partid FROM partsused pu WHERE pu.partid=p.partid);

OUTPUT:

+——–+————+————-+————+————-+

+——–+————+————-+————+————-+

| 6 | A00413 | tyre | 20.00 | 200.00 |

+——–+————+————-+————+————-+

1 row in set (0.07 sec)

Scope of Database

We have developed a well designed database that meets all the requirements for smooth working database. We have create tables with primary key and foreign key constraints so that user can fetch any information user want, the database creation is complex but we create relationship in a way user can easily operate this database. All anomalies of database is solved because the database is normalized very well. (Silberschatz and Korth, 2010)

References

Silberschatz, A and Korth, H. (2010) Database Management System.NewNework: McGraw-Hill Science/Engineering/Math.

Mark L.G.(2011) Database management system. 2^nd ed.USA: John Wiley & Sons.

Greenspan, J. and Bulger, B.( 2001) MySQL/PHP database applications.2^nd ed.Indiana : Wiley Publishing. Inc.

Williams, H.E. and Lane, D.( 2004) Web database applications with PHP and MySQL.2^nd ed.USA: O’Reilly Media. Inc.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Database System For Hotel Bookings And Payments ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order