1.Produce a conceptual database design. Use the given description plus the attached Reports, Views, Queries, and Updates to identify the underlying entities, properties, property domains, and logical relationships. Draw an E-R Diagram of your database. Identify the attributes for each entity. Transform the entities, properties and relationships into the appropriate relational table descriptions. Be sure that each table is normalized (3NF). Your design should include:

a. A Dependency Chart

b. An Entity-Relationship diagram for your database

c. A Relational Schema supporting the ER Diagram

d. An Attribute Chart/ Data Dictionary.

Be sure that each relation is normalized to 3^rd Normal Form (3NF)

Terra Firma Airlines requires a system to schedule flights and to control passenger reservations. The system must be designed to satisfy the following criteria:

A flight, which is identified by a unique flight number, has an airport and city of origin and an airport and city of destination. To simplify maters, there are no intermediate stops (all non-stop/point to point flights). The time of departure from the origin airport and arrival at the destination airport are also recorded. Airports are given by code and name (DFW Dallas Fort Worth). An actual scheduled flight has a date and an aircraft in addition to the above information. A scheduled flight is uniquely identified by the combination of a flight number and a date.

A city may have more than one airport.

Reservations are booked on scheduled flights. Again, to simplify matters, individual seats will not be accounted for.

Customers are given a customer number starting at 1001. Again, to simplify matters, only the last name will be used. Each customer may be booked on more than one scheduled flight.

Here is some info about the data to help with the question--->

TERRA FIRMA AIRLINES Page 1

AIRCRAFT REPORT

Serial# Type Description Capacity Range

T100-001 BO727 5-Seat, 2-engine jet 110 1800

T100-004 DC9 6-Seat, prop w/jet 168 2800

Number of Aircraft: 7 Total Capacity: 1204 Average Capacity: 172

FLIGHT REPORT

-------Origination----------------------- ----------Destination---------------------

Flt City City Airp City City Airp

Nbr Code Name Code TOD Code Name Code TOA

110 025 Austin AUS 0800 380 Memphis MEM 0935

181 052 Houston HOU 0830 520 Chicago ORD 1130

AIRPORT SUMMARY

Airport Airport City State Code Name Name Code

AUS Austin Municpl. Airport Austin TX

HOU Houston Internat. Airport Houston TX

SCHEDULED FLIGHT SUMMARY

Flt Flt Aircraft # Seats # Seats

Date Nbr Serial # Reserved Avail.

7/12 110 T100-001 95 15

181 T200-002 260 40

Total Flights: 5

Seats Reserved: 963

CUSTOMER RESERVATION SUMMARY

Cust Cust Flight Flight

Nbr Name Nbr Date

1001 Ford 181 7/13/2000

1004 Pfeiffer 110 7/12/2000

FLIGHT RESERVATION SUMMARY

Flight Flight Cust

Nbr Date Nbr

110 7/12/2000 1004

1077