DATA BASE MANAGEMENT SYSTM

TigerOne Flight Operations Database

TigerOne is newly formed Flight Operations Company that is targeting domestic travelers. The company

needs a database to track flights, customers, fares, airplane performance, and personnel assignment. Since

the company is promoting itself as providing "fast way out of town flights," individual seats are not

assigned. More specific notes about different parts of the database are listed below:

 Information about a flight includes its unique flight number, its origin, its destination, and

estimated departure and arrival times. To reduce costs, the company only has nonstop flights with

a single origin and destination.

 Flights are scheduled for one or more dates with an airplane and a crew assigned to each

scheduled flight, and the remaining capacity (seats remaining) noted. In a crew assignment, the

employee number and job title (e.g., pilot, flight attendant) are noted.

 Airplanes have a unique serial number, a model, a capacity, and a next scheduled maintenance

date.

 Crew members have a unique employee number, a name, a phone, and a job title.

 Customers have a unique customer number, a phone number, and a name.

 Records are maintained for reservations of scheduled flights including a unique reservation

number, the flight information, flight date, a customer number, and the fare.  

 

A sample of the data items mentioned above is provided in the attached three reports; Report1.pdf,

Report2.pdf, and Report3.pdf. Using the information provided to you, perform the following tasks:

1- Draw the Entity Relationship Diagram (ERD) for database. Use he Craw’s Foot notation to draw

the ERD.

2- Identify ALL the Functional Dependencies (FDs) and construct normalized tables in the 4NF.

3- If needed, update your ERD to reflect any changes resulting from the normalization process and

make.

4- Use your design to create the tables in the Microsoft SQL Server DBMS.

Submit your answers through Blackboard. You should submit the following:

- A Word document that contains the FDs, the list of tables and their attributes in the 4NF, and

the ERD.

- The MS SQL Server Database File.