JetGreen Airline Database

IetGresn Airlines is a new small zirline company for domestic flights. The company needs to build a databsse

system to stors information about its flights, aircrafts, employees, reservations, and customarn. As the database

angineer for this project, you are assignod the task of desiguing and building the datzbase waed for the woftwars

systems of JatGreen. Here are some details about the compary operations that will belp you in the database

design procens:

The airline operates noustop flights with a single origin and a single destimation.

The company has an open sezting policy which meam thare are no asigned seat for each customar.

Flight data includes a unique flight number, departure sirport, destination airport, and ostimated.

departure and anival times.

The company Flight Schedule includes the details of schoduled flights such as:

Flight mumber

$0$ Date

The sircraft used for the flight on that specific date.

The ramsining open saats for this flight. If this is zaro, it moans the flight is full.

The list of crew mombars assiguad to the flight on that date.

Each zircraft has a umique serial mumber, $2$ model, a capacity, and a mext scheduled maintemamce date.

Each crew member has a unique amployee number, $z$ mame, $z$ phome, and a job title.

Each customer has a umique customer number, a phone number, and a nsme.

Reservation information include a urique revervation mubar, the flight information, flight date, a

customar number, and the fare prid.

A sample of the data itams mantioned above is provided in the attached three raports; JotGreenReportl.pdf$,$

JetGreanReport$2$ pdf$,$ and JatGrsonRsport$3.$pdf$.$

Using the information provided to you, parform the following takk:

$1-$ Identify all the antities in this database.

$2-$ Draw the Entity Relationship Dizgram $($ERD$)$ for the database wsing the Craw's Foot notation.

$3-$ For each outity in your initial ERD, specify the following:

The attributes $($columens$)$ for avch antity.

i$.$ Specify the primary koy.

ii$.$ Specify if any of the attributos is a foraign koy.

Identify ALL the Functionsl Dapandascies $($FDs$)$ and construct normalired tables in the $4$NF$.$

$5-$ If neadad, update your ERD to reflect any changes rosulting from the mormalization process.

$6-$ Use your design to create the tables in the Microvoft SQL Sarver DBMS$.$

Submit your answers through Blackbourd. You abould submit the following:

A Word document that contains:

The rovised ERD

A list of all the entitios normalined to the $4$NF with the following information:

Entity mame

The attributes of the eatity

The primary key

Any foraign keys

The Functional Dependencios in each eutity $($table$)$

The MS SQL Servar Database File for your database.Report $1$

$\backslash$table$[[$Origin$,$Origin$\_$Name,Destination,Destination$\_$Name,Flight$\_$Num,Flight$\_$Date,Depart$\_$Time,Arrive$\_$Time,$\backslash$table$[[$Airplane$],[$Serial$\_$Num$]],\backslash$table$[[$Airplane$],[$Model$]],\backslash$table$[[$Airplane$],[$Next$\_$Maint$\_$Date$]],\backslash$table$[[$Airplane$],[$Capacity$]],\backslash$table$[[$Flight$],[$Rem$\_$Seats$]]],[$MCO$,$Orlando,ORD,Chicago,$110,10/4/2018,8$:$25,11$:$25,$A$29393,$Airbus A$220,7/13/2019,135,3],[1/23/2019,8$:$25,11$:$25,$A$25882,$Airbus A$330,2/20/2020,335,9],[$FLL$,$Fort Lauderdale,TUS,Tucson,$104,8/9/2018,17$:$35,21$:$50,$A$28936,$Boeing $767,4/1/2020,351,0],[\backslash$table$[[12/31/2018]],17$:$35,21$:$50,$A$37557,$Airbus A$220,1/14/2020,135,5],[$TUS$,$Tucson,DFW$,$Dallas,$324,11/23/2018,18$:$05,20$:$15,$A$29393,$Airbus A$220,7/13/2019,135,5],[$FLL$,$Fort Lauderdale,$103,9/20/2018,10$:$25,14$:$40,$A$27766,$Airbus A$330,12/8/2019,335,5],[10/19/2018,10$:$25,14$:$40,$A$27766,$Airbus A$330,12/8/2019,335,16],[$OAK$,$Oakland,DFW$,$Dallas,$645,5/14/2018,14$:$25,19$:$05,$A$28936,$Boeing $767,4/1/2020,351,5],[\backslash$table$[[11/21/2018]],14$:$25,19$:$05,$A$26106,$Boeing $777,12/1/2020,320,3],[$ORD$,$Chicago,MCO,Orlando,$111,1/30/2019,13$:$45,16$:$50,$A$37557,$Airbus A$220,1/14/2020,135,68],[3/10/2019,13$:$45,16$:$50,$A$44356,$Boeing $777,9/25/2019,320,12],[3/21/2019,13$:$45,16$:$50,$A$26106,$Boeing $777,12/1/2020,320,5],[4/1/2019,13$:$45,16$:$50,$A$44356,$Boeing $777,9/25/2019,320,2],[$DFW$,$Dallas,EWR,Newark,$416,4/13/2018,9$:$35,14$:$20,$A$37897,$Boeing $767,11/1/2019,351,22],[5/12/2018,9$:$35,14$:$20,$A$37897,$Boeing $767,11/1/2019,351,3],[8/31/2018,9$:$35,14$:$20,$A$37897,$Boeing $767,11/1/2019,351,10],[10/1/2018,9$:$35,14$:$20,$A$25882,$Airbus A$330,2/20/2020,335,45],[$OAK$,$Oakland,$644,\backslash$table$[[12/23/2018]],7$:$45,12$:$35,$A$26106,$Boeing $777,12/1/2020,320,34],[644,2/12/2019,7$:$45,12$:$35,$A$26106,$Boeing $777,12/1/2020,320,30],[$rN$/$D$,,,,,1/31/2019,16$:$45,21$:$25,$A$44356,$Boeing $777,9/25/2019,320,0],[$EWR$,$Newark,DFW$,$Dallas,$417,4/2/2019,16$:$45,21$:$25,$A$27766,$Airbus A$330,12/8/2019,335,42]]$Report $2$

$\backslash$table$[[$Flight$\_$Num,Flight$\_$Date,Origin,Origin$\_$Name,Destination,$\backslash$table$[[$Destination$],[$Name$]],$Depart$\_$Time,Arrive$\_$Time,$\backslash$table$[[$Reservation$],[$Number$]],$Customer$\_$ID$,$Customer$\_$Name,Customer$\_$Phone,Fare$],[645,5/14/2018,$OAK,Oakland,DFW$,$Dallas,$14$:$25,19$:$05,\backslash$table$[[$R$23587]],$C$2388597,$Beth Taylor,$(206)421-1276,918],[\backslash$table$[[$R$35372]],$C$9403348,$Mike Boren,$(406)921-4468,670],[\backslash$table$[[$R$16907]],$C$8543321,$Ron Thompson,$(206)561-2264,712],[$R$27166,$C$8574932,$Wally Jones,$(303)$