INFO 330: Travel Database Project

In my INFO 330 Class, we were asked to create a database and conduct multiple analyses on it using the SQL, data management, and data processing skills we learned throughout the class. I, along with 3 of my peers in the class, collaborated together on this project. Here is a compilation of all the work we did for the project:

Step 1: Defining the Industry of our Potential Database

The industry we chose for our database was Travel. Our typical user would be a travel agency website, like Expedia, that allows customers to book flights, hotels, meals, car rentals, excursions, tourist attractions, and any other travel-related experience.

As a business, they surface products and services that customers search for, and make money when customers book experiences through them. Travel agencies also make money from displaying ads. As such, they would benefit from having a database that could inform them on the latest trends and patterns. Some examples of patterns that could inform business decisions include what destinations are most popular with travelers from X location, what kinds of restaurants or excursions are purchased most frequently at a given destination or time frame, or how popular a given experience is over the past 6 months. Typical users of this database would include data analysts or business analysts at online travel agency companies like Expedia, Priceline, and TripAdvisor that want to ensure their customers are satisfied with their travel choices.

Data-informed decisions from our database can help travel agencies increase conversion rate by recommending products and services that commonly go together, index popular experiences higher on a given query, and serve more relevant ads. Knowledge of certain seasonal, demographic, or other patterns can also help travel agencies price their products and services. For example, suppose there’s a new COVID-19 variant developing in, say, Eastern Europe, flights to Greece will likely have to be priced lower. Like so, data of all the flights, hotels, meals, car rentals, excursions, tourist attractions will be stored within our database and updated regularly based on new trends, changes, ratings, or popularity of a given travel-related experience. This will, at the end, not only help inform customers of the several options they might be deciding among but aid travel agency websites keep track of products and services based on patterns.

Step 2: Creating the Entity Relationship Diagram for our Database

Our next step in this project was to create an ERD for our travel database. We went through many iterations of the ERD, and continually revised based on feedback from our professor. In the end, we had an ERD with 27 total tables, 10 look-up tables, and 5 associative entity tables. Attached is an image of our final Entity Relationship Diagram.

Step 3: Creating the Tables + Writing Queries

After creating the ERD, we implemented each of our tables, and began populating them with arbitrary values. After we were satisfied with the amount of values in our tables, we began to write queries. All together, we wrote 8 stored procedures, 8 business rules, 8 complex queries, and 8 computed columns. You can check out the code on my github.

Step 4: Tableau Data Visualizations

After we wrote queries and implemented the database, I took on the role of creating Tableau Visualizations for our work. I created a dashboard with three visualizations:

A Bar Chart containing the number of Hotels in each price range for the city of France
A Pie Chart consisting of all the different cuisines in New York City
A Line Graph visualizing the change in Bookings for each month based on the year

You can look at the visualization here.

Step 5: Presentation

After all of our work, we presented our project to the class! Here is our presentation.

At the end of this project, I was able to successfully build and analyze a database from start to finish. I strengthened by SQL skills and build Tableau data visualizations from scratch by connecting our database to Tableau. I also was able to collaborate effectively with my team, and we were all able to learn from our mistakes. We all felt extremely accomplished after finishing the project! :)