AIRPORT-project 2

Airport Project Renad yasser Dana Nasser Hala almrdi

01 ❖ First Step: The specification We want to design a database to describe travelers. In the database we are interested in data on: -Travelers (Name composed of first name and middle name last name, Aviation_class, Passport number, Seat Number) -Plain (, Plane-number, Num-Travelers, company) -Cabin crew (pilot, Co-pilot, Num-CrbinCrew, Aviation_Host composed of Aviation Host_1 and Aviation Host_2) -Trips (Trip-Number Trip_destination , time_departure , Arrival time ) Passengers can travel in one plane, and in a single plane, many travelers can travel. The plane has one crew, and one crew works with one plane. Passengers can travel at least one flight.

02 ❖ Second Step: The Entity Relationship model

03 ❖ third Step: The relational model Travelers (Passport number, first name, middle name, last name, Aviation_class, Seat Number , # Plane-number,# Trip-Number) Plain (Plane-number, Num-Travelers, company, #Num-CrbinCrew) Cabin crew (Num-CrbinCrew, Aviation Host_1, Aviation Host_2, pilot, Co-pilot ) Trips (Trip-Number ,Trip_destination , time_departure , Arrival time )

04 ❖ FORTH Step: The Data Definition Language DDL: create Create table Travelers ( Passport number number(10) Notnull, first name varchar2(50), middle name varchar2(50), last name varchar2(50),, Aviation_class char(1), Seat Number number(3), Trip-Number number (5), Plane-number number (5), Primary key (Plane-number), Foreign key ( Trip-Number) references tirp ( Trip-Number) Foreign key ( Plane-number ) references plain( Plane-number ));

Create table Plain ( Plane-number number (3) notnull, Num-Travelers number(4), Company varchar(20), Num-CrbinCrew number(3) , Primary (Plane-number), Foreign key(Num-CrbinCrew ) references cabin crew (Num- CrbinCrew )); Create table Cabin crew ( Num-CrbinCrew number(3), Aviation Host_1 varchar(20) , Aviation Host_2 varchar(20) , Flight duration varchar(20) , pilot varchar(20), Co-pilot varchar(20), Primary(Num-CrbinCrew) );

Create table Trips ( Trip-Number number(3), Trip_destination varchar(20) , time_departure time , Arrival time time , Primary key(Trip-Number )); DDL alter Alter table Travelers Add email varchar2(50);

DML insert Insert into Travelers (Passport number, first name, middle name ,last name, Aviation_class, Seat Number , Plane-number, Trip-Number) Values (8378 , 'ali' , 'mohamed' , ' ahamed' , 23 , 2344, 345) ; Insert into Travelers (Passport number, first name, middle name ,last name, Aviation_class, Seat Number , Plane-number, Trip- Number) Values (9765 , 'naif' , 'ahamrd ' , 'fahed',56, 5678,343 ) ; Insert into Travelers (Passport number, first name, middle name ,last name, Aviation_class, Seat Number , Plane-number, Trip- Number) Values (997 , ' mona' , 'mohamed' , 'ali', 66, 3456, 333 ) ;

DML update Update Travelers Set Trip-Number=444 Where Passport number=997; DML delete Delete from Travelers Where frist name= 'naif' ; DDL Drope Drop table cabin_crew;

Reference: ▪ Edrplas.com https://erdplus.com/ ▪ From our project in the class of Dr. Maryam ▪ From our data base in access DONE BY RENAD – DANA – HALA