Question

Draw a data model for the following:

A travel agency is frequently asked questions about tourist destinations. For example, customers want to know details of the climate for a particular month, the population of the city, and other geographic facts. Sometimes they request the flying time and distance between two cities. The manager has asked you to create a database to maintain these facts.

Answer 1

To create the data model for the given system, let’s follow the below steps:

Step 1: Identify the Tables/Entities in the system:

The system given can have below tables in the data mode:

• Destination_City: To store the data about the destination city’s address, name etc.
• City_details: To store the data about weather, geographical details and additional details
• Climate: To store the data about the monthly climate of given destination city
• Schedule: To store the data about the scheduled departure to destination cities
• Distances: To store the data about distances between the cities

Step 2: Identify the attributes the system can have in the data model

The identified tables can have below attributes in it:

• Destination_City(Dest_ID, Name, Street_Address, City, State, ZipCode, Country)
• City_details(Dest_ID, Map_Location, Population, Area)
• Climate(Climate_ID, Dest_ID, Month, Weather, temperature)
• Schedule(Schedule_ID, Source_City, Destination_City, Departure_DateTime, Arrival_DateTime)
• Distances(Distance_ID, Source_City, Destination_City, Distance_kilometers)

Step 3: Identify the key and foreign key attributes in the identified tables

**Primary Key is BOLD and underlined whereas Foreign key is having FK next to the attribute name

• Destination_City(Dest_ID, Name, Street_Address, City, State, ZipCode, Country)
• City_details(Dest_ID, Weather, Map_Location, Population, Area)
• Climate(Climate_ID, Dest_ID(FK), Month, Weather, temperature)
• Schedule(Schedule_ID, Source_City(FK), Destination_City(FK), Departure_DateTime, Arrival_DateTime)
• Distances(Distance_ID, Source_City(FK), Destination_City(FK), Distance_kilometers)

Step 4: Draw the data model for the identified tables and attributes:

Step 5: Relationships in the data model:

• One destination has one City_details record (One to one relationship)
• One city_details record can have many climate records for multiple months (one to many relationship)
• Destination can have many or 0 schedule (One to many relationship)
• Destination can have multiple distances with different destinations (many to many relationship)

Step 6: Relationship notation in the data model:

1. One to one is displayed as below:

2. One to many is displayed as below:

3. Many to many is displayed as below: