Built a fully normalized Travel Agency Database from scratch with multiple meaningful relationships between various entities, using Oracle with PHP.Wrote queries in MySQL and implemented a GUI through which the user can easily store, manage, and retrieve useful data related to their booking with the Agency.
Utilizes knowledge of SQL, PHP, Oracle, Data Normalization, Relational Databases.
For this project I used UBC’s CPSC department’s Oracle database system with PHP. Site Hosted at: https://www.students.cs.ubc.ca/~sdhaka/project.php
The domain of our application is a Travel Agency. In our Travel Agency Database, we are able to insert, update, delete and manage information of various entities --Employees(Bus Driver, Tour Guide, Travel Agent),Customers, Tour Groups, Cities, Hotels, Hotel Reservations, Attractions and Attraction Reservations. These entities are interrelated through multiple meaningful relations and come together to give us a Travel Agency database.
Through this database, we are able to store and manage various details of a trip that a customer has planned with our Travel Agency, including the city they are visiting, the hotel they stay in, the attractions they visit, etc. The main focus of our application is the customer and the itinerary of their trip, which is managed by the user(Travel Agent). This Database Application allows the Travel Agent to keep track of all the details of a customer’s trip and ensure that all processes go on smoothly. If a customer wants to change the specifics of their trip, the Travel Agency can easily store, manage and pull useful data from the database to provide them with options that they can choose from.
Through our GUI for this database application, the user is able to count and retrieve information of the Employees, Customers, Hotels and Attractions in the database; insert a new customer to the database; update employee information; delete an existing customer; user can make a selection and retrieve tuples between a choice of 5 tables and using user-chosen attribute and value combination; find available hotels that a customer can stay in; find the city by searching a particular attraction; find the number of customers staying in All hotels; find the customers with more than one piece of luggage; find the average hotel rates of locations above the overall hotel rate average and find customers who have trips for all available cities.
[x] Insert: Insert a new Customer
[x] Delete: Delete an existing Customer
[x] Update: Update an Employee’s info
[x] Selection: Selection on user selected table and attribute-value pair (Perform a selection on 6 tables and using user-selected attribute-value combinations)
[x] Projection: Find hotels from a user entered City
[x] Join: Find the City of an Attraction from its coordinates
[x] Aggregation With Group By: Find the number of customers visiting all Cities that have been visited
[x] Aggregation With Having: Find customers who are bringing more than a single piece of luggage
[x] Nested Aggregation With Group By: Find Hotels whose rates are greater than the average rate of hotels
[x] Division: Find Customers who have stayed at all available Hotels.