In Database

Design & Normalize - Define the tables, primary keys and foreign keys and any indexes you think useful. Do not provide actual examples with data. Only structure, datatypes, and keys.

There are 3 different airlines in 3 different countries: Canada - AirCan

USA - United UK - BA

Their flights involve only the following cities: Toronto and Montreal in Canada,

New York and Chicago in the USA, London and Edinburgh in the UK, Paris and Nice in France,

Flights within each country are provided only by the domestic airline.

Each airline flies domestic connections within it's own country, eg. AirCan only flies between Toronto &

Montreal.

Each airline can fly international, e.g. AirCan flies Toronto to New York, to Chicago, London, Endinburgh, Nice

and Paris.

In each of the cities, there is a single booking office.

Design a central air-reservation database to be used by all booking offices. The customers may come from any country, not just the ones above, and from any province/state, and from any city.

Known information about the Customer:

One first name, one last name, one mailing address, may have none, one, or more phone numbers, may have none, one, or more e-mail addresses

The customer's mailing address has one street, one city, one province or state, one postal code, one country

The customer's phone number has one country code, has one city/area code, has one local number

And the customer has an email that is just a string, no structure is assumed

Known information about each flight:

the code of flight-origin (the city to fly from),

the code of flight-destination (the city to fly to),

the unique flight number,

the code of air line,

the length-of-flight (in hours and minutes)

unique booking number,

code of the city in which the booking is made,

date of booking,

flight number,

the date+time of departure (in local date+time), [time always in hours and minutes],

the one date+time of arrival (in local date+time),

customer who is responsible for payment,

customer's name that will be printed on the ticket

Other information: A customer may be booked to one or more flights. Two or more customers may have the same mailing address and/or the same phone number(s) (separate people from the same household that may or may not be traveling together). The e-mail addresses are unique, i.e. no two customers may have the same e-mail address. More than one customer from the same household may be booked on the same flight, but only one person will be responsible for the payment of the flights.

To Do:

1) Define & normalize where appropriate the tables for this system 2) Implement in Postgres or SQLite 3) Post the table creation script to your github with the name HW5.sql