DBMS-II Assignment I Solution

Problem Designing ER diagram for transportation statistics.

Design an ER diagram for transportation statistics by maintaining the referential integrity.

The dataset stores the information of the departure and arrival of di erent ights at di erent airports during a month of a year.

There are various airlines and each airline o ers ight services from and to di erent airports.

The airlines are identi ed by their unique carrier code.The IDs are given to airlines by Government as well as another organization. The id given by the Govt is unique and that by the other organization may not be unique.

A ight is identi ed by a ight number which is assigned according to the reporting airline.

Flight also has a tail number that represents information related to the aircraft.

A ight has an origin airport, destination airport, date of the ight, scheduled elapsed time, actual elapsed time and air time.

Each airport has a long-term unique identi cation number, temporary unique sequence ID and address. And obviously, an airport can either be an origin or a destination or a diverted port for a ight.

The address is complete if it conveys airport name, city name, state name, state id, state ps code, and the world area code.

Scheduled elapsed time, actual elapsed time and air time are measured in minutes.

For ight date, you may want to store the year, month, day of the month, day week etc separately. Storing these elds separately may be bene cial to draw summaries and make the searches faster. It may also be redundant at the same time to waste space in the database.


1

A  ight-trip may include any number of connecting  ights.

A ight may get cancelled, diverted or delayed. The reason for cancellation is repre-sented by a code. Similar to not found error on networks which is coded as 404.

Each ight has an arrival time and a departure time. Scheduled arrival time may be di erent from actual arrival time.

A ight will be called late if the di erence between the scheduled time and the departure time is greater than 15 minutes.

A ight may be delayed by some minutes at arrival or departure. And the delay time can be quantized to draw meaningful summaries.

A delay can have many causes eg. security delay, weather delay, etc. Delay is associated with a cause.

Many other timings may also be associated with a ight. These may include Wheels on, Wheels o , Taxi in, Taxi out etc.

CRS stands for Computer Reservation system. A CRS may provide information about ight timing.

Elapsed time is the function of actual departure time and actual arrival time. CRS may also provide information about the elapsed time of ight.

Each ights arrival time is associated with an arrival time block. Multiple ights can have the same arrival time block.

The format for any type of arrival or departure time and wheels o time is hhmm. Delay time of any type and taxi out time is measured in minutes.

A ight may get diverted at more than one airport. And the maximum number of diversions can be ve.

The diversion information is associated with the diverted airports, delay to diversion, elapsed time of the ight after diversion, total diverted distance etc.

Please note: In your ER diagram, you may include other attributes from the dataset or website. You are encouraged to have an exhaustive ER diagram. Please ensure that you have designed your ER diagram maintaining all the required constraints and inheritance properties.

Submission: Please use the website https://erdplus.com/ for creating the ER Diagram. The functioning is pretty straight-forward. You might have to create an account in order to save your progress. There may be constraints on the number of relations that can be formed from/to an entity, due to visualization issues. Please only submit the nal diagram.




2