This project models a database for transport options in the Caribbean provided by six fictional transportation companies. This database can be used as a tool for travelers to compare modes of travel within the Caribbean provided by the selection of companies. The journeys booked by passengers from this website for this week are listed in the travels table. This table relates the passengers with the journeys they booked with the class of their ticket as an attribute.
The database includes a table listing the stations with the station ID as the primary, a table listing the transport vessels, its primary key will be the vessel ID and its attributes will include, type of vessel, company and capacity. Each vessel belongs to a transport company which has its name and type of services as its attributes. Another table includes the journey relationship, relating the vessels each to two airports (origin and destination), with attributes such as time and day, cost, travel duration, and the primary key will be the travel number. An entity was included that holds data of the passengers who have booked tickets through this database for this week. Passengers are allowed a check or carry-on luggage, which is represented as a weak entity. Also, infants may accompany the passengers and this is also represented as a weak entity. A weak entity was also added that lists the meals requested by the passengers going on trips this week. It takes its primary key from the travels table, with attributes including class of the meal, passenger dietary restrictions and whether or not a meal is a kid’s meal. The database also includes information on the employees of the companies and their assigned jobs by adding an employees entity. The attributes include employee name, surname, dob, their job title, department and the company name as the foreign key. Each employee must work for exactly one travel company. Also, a relationship was added called journey crew in which crew members are assigned to work the journeys. Each journey must have at least one employee and this is captured in the ER diagram. However, due to the inability for postgresql to implement assertions, this is not captured in the database itself. Finally, passengers have the option of booking a journey through a travel agency. This is captured using a travel agency entity, with its name and phone number as attributes. We used a relationship called booking to connect a passenger’s journey to the travel agency the passenger used. Since the passenger can choose to book directly with the travel company or the travel agency, there are no constraints on this relationship.
Assume management decides to boost revenue and customer retention by introducing a Customer Loyalty Program. The program allows customers to acquire points by booking trips through the database. These points allow customers to access exclusive offers on their upcoming trips. Customers are asked to email the company if they were interested in joining the Loyalty Program, after which they were able to start collecting points and a record of their trip history began to be compiled. In order to accommodate this new program, two new composite types were introduced: customers and trips. We altered the passengers table to be of type “customers” and the journey table to be of type “trip”. We stored all the information on the customers in the Loyalty Program in a new entity table “Loyal Customers” which inherited the attributes of passengers. This new table also holds other attributes including “points” which holds the loyalty points the customer has acquired; “travel history” which contains an array of the composite type “trip” and represents the past trips the customer has booked through the database; and “application” which is the text of the email through which the customer requested to join the Loyalty Program. These modifications allow the database to keep track of Loyal Customers and their progress, so as to target those that qualify with discounts and offers.