Database «Airport»

The database schema consists of 4 tables:

Company (ID_comp, name)
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)

The Company table contains IDs and names of the airlines transporting passengers. The Trip table contains information on the schedule of flights: trip (flight) number, company (airline) ID, plane type, departure city, destination city, departure time, and arrival time. The Passenger table holds IDs and names of the passengers. The Pass_in_trip table contains data on flight bookings: trip number, departure date (day), passenger ID and her seat (place) designation during the flight. It should be noted that

• scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;

• all time and date values are assumed to belong to the same time zone;

• departure and arrival times are specified with one minute precision;

• there can be several passengers bearing the same first name and surname (for example, Bruce Willis);

• the seat (place) designation consists of a number followed by a letter; the number stands for the row, while the letter (a – d) defines the seat position in the grid (from left to right, in alphabetical order;

• connections and constraints are shown in the database schema below.

Such question is quite often asked: “Why the Trip table has the day of 1900-01-01 in departure/arrival dates?”

According to the description of a subject area, Trip table contains only the time of departure/arrival, not the days, because flights are being carried out daily. Presence of date have been caused by the absence in early versions of SQL Server of separate data types for date (DATE) and time (TIME) which have appeared only in version 2008. Therefore DATETIME data type corresponding to standard TIMESTAMP and including all components of time stamp was used.

As for specific date of 1900-01-01, this date corresponds to an starting point of counting time, i.e. zero. Execute query:

SELECT CAST(0 AS DATETIME);

I.e. if  you insert into a column of DATETIME data type only time, date of this value becomes 1900-01-01. You can convince yourself in it by doing explicit type conversion of time value to DATETIME data type, for example:

SELECT CAST('13:44:00' AS DATETIME);