airport traffic control simple database model

0 | P a g e

Under the supervision of

Prf. omer gumus

By

Zaid dawad al-rustom

20112465

2013

Oracle database project

Airport traffic control simple

database model

Near East University

Abstract

In this report I will discuss an airport traffic control , first thing every

airport need for control traffic that mange all the flight and try to make

everything is safety and without any error , for that the controller need to

get all the information at the exact time and all the update at time.

What we need?

Simple survey on an airport and register all things related with the flight

and fields and garages ….etc.

Discussion

First thing should detect the important table and the contains and all

relation between them, and what constraint need for table, how to choose

the table names and how to write the columns names how to build the

tables.

What does this database try to do?

This database trying to gather all data and details that air traffic controller

need to control one flight (flying or landing) , gathering the data in table

named checking table to check every details related with flight and at last

the controller will decide if pass or fail and give the signal to responsible

person.

Tables names:

1- Plane

2- Flights

3- Captains

4- fields

5- companies

6- weather

7- controller

8- criteria

9- checking table

Tables Contains with data types and constraints:

1- Plane

Any details related with the plane.

Column name data type constrain

planeID …………………………….NUMBER (20)…… ….pk

planeNo……………….……….. VARCHAR2 (10)...NOTNULL

planeType (type on plane size)..… VARCHAR2 (15)…….

planelandDATE…….. ……….… VARCHAR2 (20)

planelandTime ……………….... … VARCHAR2 (20)

planeflyDATE …………..…..… VARCHAR2 (20)

planeflyTime ………………….... … VARCHAR2 (20)

compID……………………. NUMBER (4)…………….fk

2-flights

Flights information.


flightID………………..….… NUMBER (4)………..….pk

flightNo…………………... VARCHAR2 (10)………..NOTNULL

flightDirecton…...………... VARCHAR2 (20)

flightDate ……………….… VARCHAR2 (20)

flightTime ……………… … VARCHAR2 (20)

weatherID……………………. NUMBER (4)…………….fk

3-captains

A plane's captain information.


captainID…………………… NUMBER (4)…………..….pk

captainName…...……………VARCHAR2 (20)…..…..NOTNULL

captainTel…………………... VARCHAR2 (15)

captainAdd…….………..…... VARCHAR2 (20)

fieldsID…………………… NUMBER (4)………….fk

4- fields


fieldsID…………………… NUMBER (4)………….pk

flyingFNo……………..…….. VARCHAR2 (10)……..NOTNULL

flyingFAvalabilty…………..NUMBER(1) "0-available,1-

notavailable"……………………………………………check

landingFNo ………………….VARCHAR2 (10)……..NOTNULL

landingFAvalability…………..NUMBER(1) "0-available,1-

notavailable"…………………………………………..check

compID……………………. NUMBER (4)…………….fk

5-companies

Airplane Company's information.


compID……………………. NUMBER (4)…………….pk

compName………………… VARCHAR2 (20)……..NOTNULL

compAdd……………………………... VARCHAR2 (20)

compTel………………… VARCHAR2 (15)

controlerID……………..… NUMBER (4)……….….fk

6-weather

This table store the real weather that controller need for.


weatherID……………………. NUMBER (4)…………….pk

windspeed………………… VARCHAR2 (20)……..NOTNULL

cloudDensity………………… VARCHAR2 (20).

Temperature………………… VARCHAR2 (20).

Rain gauge………………… VARCHAR2 (20).

7-controller

The person that control the traffic.


controlerID……………..… NUMBER (4)……….….pk

controlerName……….…… VARCHAR2 (20)……..NOTNULL

controledate……….…… VARCHAR2 (20)

controletime……….…… VARCHAR2 (20)

criteriaID ………….. NUMBER (4)…………………….fk

8-criteria

the criteria that controller depend on it, this table contain some fact and

rules that the controller need for.


criteriaID ………….. NUMBER (4)…………………….…….pk

weatherdetails …..….VARCHAR2 (011)…………....NOTNULL

faieldsdetails…….….VARCHAR2 (011)

captainsdetails…..….VARCHAR2 (011)

planedetails…..….VARCHAR2 (011)

flightID………………..….… NUMBER (4)………..….fk

9-checking table


checkID………….. NUMBER (20)………………….pk

checkNo ………….VARCHAR2 (20)……………....NOTNULL

chekDate………... ..DATE

checkTime..……….DATE

planeID…………....NUMBER(4)……………………….fk

Database schema and show relations:

Sql commands and declaring the work:

1-plane table

Creating table code

CREATE TABLE "SYSTEM"."PLANE"

( "PLANEID" NUMBER(4,0) NOT NULL ENABLE,

"PLANENO" VARCHAR2(10 BYTE) NOT NULL ENABLE,

"PLANETYPE" VARCHAR2(15 BYTE),

"PLANELANDDATE" VARCHAR2(20 BYTE),

"PLANELANDTIME" VARCHAR2(20 BYTE),

"PLANEFLYDATE" VARCHAR2(20 BYTE),

"PLANEFLYTIME" VARCHAR2(20 BYTE),

CONSTRAINT "PLANE_PK" PRIMARY KEY ("PLANEID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE,

CONSTRAINT "CAP" FOREIGN KEY ("PLANEID")

REFERENCES "SYSTEM"."COMPANIES" ("COMPID") DISABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING


PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ;

First inserting code

insert into plane(PLANEID,planeno,planetype,planeflydate,planeflytime,planelanddate,planelandtime)

values ('1212',PLANE_SEQ.NEXTVAL,'boeing717','19\1\2013','2:30am','20\1\2013','3:35pm')


values ('1313',PLANE_SEQ.NEXTVAL,'avanger201','15\1\2013','12:30am','12\1\2013','4:35pm')






values ('1616',PLANE_SEQ.NEXTVAL,'airbus787','20\1\2013','6:30am','19\1\2013','4:35pm')













Figure 1 inserting data

Updating code

update plane set planetype='boeing767' where planeid=1212

Figure 2 update

Delete code

delete plane where planeid=2222

Figure 3

Selecting

select planeno,planetype,planeflytime from plane

select planeid,planetype,planelandtime from plane

Figure 4

View report

CREATE OR REPLACE FORCE VIEW "SYSTEM"."PLANE_REPORT" ("PLANEID",

"PLANENO", "PLANETYPE") AS SELECT planeid,planeno,planetype FROM plane;

Figure 5

Flights tables

Create table

CREATE TABLE "SYSTEM"."FLIGHTS"

( "FLIGHTID" NUMBER(4,0) NOT NULL ENABLE, "FLIGHTNO" VARCHAR2(20 BYTE),

"FLIGHTDIRECTION" VARCHAR2(20 BYTE), "FLIGHTDATE" VARCHAR2(20

BYTE), "FLIGHTTIME" VARCHAR2(20 BYTE), CONSTRAINT "FLIGHTS_PK" PRIMARY KEY

("FLIGHTID")



PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE

DEFAULT CELL_FLASH_CACHE DEFAULT)


CONSTRAINT "COMPID" FOREIGN KEY ("FLIGHTID")

REFERENCES "SYSTEM"."COMPANIES" ("COMPID") ENABLE






Insert data

insert into flights(flightid,flightno,flightdirection,flightdate,flighttime)

values ('3030',flight_seq.nextval,'istanbul','19\1\2013','2:30am')


values ('3131',flight_seq.nextval,'ankara','17\1\2013','4:50am')


values ('3232',flight_seq.nextval,'antip','15\1\2013','7:55am')


values ('3434',flight_seq.nextval,'borsa','23\1\2013','4:30am')


values ('3535',flight_seq.nextval,'diarbaker','22\1\2013','6:30am')


values ('3636',flight_seq.nextval,'istanbul','25\1\2013','11:30am')


values ('3737',flight_seq.nextval,'adana','27\1\2013','9:30am')


values ('3838',flight_seq.nextval,'mersin','22\1\2013','12:30am')


values ('3939',flight_seq.nextval,'mardin','28\1\2013','4:30am')

Figure 6

Update

update flights

set flighttime='2:45'

where flightdirection='istanbul'

Figure 7

Delete

delete flights where flightdirection='borsa'

Figure 8

Select

select flightdirection,flightdate,flighttime from flights;

Figure 9

View report

CREATE OR REPLACE FORCE VIEW "SYSTEM"."FLIGHT_REPORT" ("FLIGHTDIRECTION",

"FLIGHTDATE", "FLIGHTTIME") AS SELECT flightdirection,flightdate,flighttime FROM flights;

Figure 10

CAPTAIN’s tables

Create table

CREATE TABLE "SYSTEM"."CAPTAINS"

( "CAPTAINID" NUMBER(4,0) NOT NULL ENABLE,

"CAPTAINNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,

"CAPTAINADD" VARCHAR2(10 BYTE),

"CAPTAINTEL" VARCHAR2(20 BYTE),

CONSTRAINT "CAPTAINS_PK" PRIMARY KEY ("CAPTAINID")






CONSTRAINT "CAPTAINS_COMPANIES_FK1" FOREIGN KEY ("CAPTAINID")

REFERENCES "SYSTEM"."COMPANIES" ("COMPID") ENABLE






Insert data

insert into captains(captainid,captainname,captainadd,captaintel)

values ('3030','ahmed','lefkosa','05488228807')


values ('3131','zaid','gonyli','0533456679')


values ('3232','mustafa','deraboy','0533421345')


values ('3434','amjad','raies','0533424387')


values ('3535','abdullah','deraboy','0533429876')


values ('3636','safwan','girne kapi','0533422365')


values ('3737','ali','magusa','0533423948')


values ('3838','omer','dipkarpaz','053342039')


values ('3939','dilovan','kacmenkoy','053341234')

Figure 11

Update

update aptains set captainadd='deraboy' where aptainname='zaid';

Figure 12

Delete

delete captains where captainname='dilovan';

Figure 13

Select

select captainname,captainadd,captaintel from captains

Figure 14

View report

Figure 15

COMPANIES

Create table

CREATE TABLE "SYSTEM"."COMPANIES"

( "COMPID" NUMBER NOT NULL ENABLE,

"COMPNAME" VARCHAR2(10 BYTE) NOT NULL ENABLE,

"COMPADD" VARCHAR2(20 BYTE),

"COMPTEL" VARCHAR2(20 BYTE),

CONSTRAINT "COMPANIES_PK" PRIMARY KEY ("COMPID")





TABLESPACE "SYSTEM" ENABLE






Insert data

insert into companies(compid,compname,compadd,comptel)

values ('3030','atlasjet','lefkosa','05488223456')


values ('3131','pegasus','lefkosa','05488274839')


values ('3333','zaid','lefkosa','05488234839')


values ('3232','onur','lefkosa','05488274839')


values ('3434','turkish','lefkosa','0548876839')


values ('3535','greece','lefkosa','0548876839')


values ('3636','emarat','lefkosa','0548876456')


values ('3737','saudia','lefkosa','0533876456')


values ('3838','pegasus','girne','0533876456')


values ('3939','atlas','girne','0533876456')

Figure 16

Updating

update companies

set compadd='girne'

where compname='zaid'

Figure 17

Select

select compname,compadd,comptel from companies

Figure 18

View report

Figure 19

CONTROLLER

Create table

CREATE TABLE "SYSTEM"."CONTROLLER"

( "CONTROLLERID" NUMBER(4,0) NOT NULL ENABLE,

"CONTROLLERNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,

"CONTROLEDATE" VARCHAR2(20 BYTE),

"CONTROLETIME" VARCHAR2(20 BYTE),

CONSTRAINT "CONTROLLER_PK" PRIMARY KEY ("CONTROLLERID")



PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)




PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" ;

Insert data

insert into controller(controllerid,controllername,controledate,controletime)

values ('3030','omet','17\01\2013','2:10')


values ('3131','azad','22\01\2013','3:50')


values ('3232','saman','15\01\2013','4:50')


values ('3434','deyar','16\01\2013','7:30')


values ('3535','waleed','19\01\2013','8:45')


values ('3636','darbaz','24\01\2013','9:30')


values ('3737','ibrahim','26\01\2013','12:30')


values ('3838','mahmood','13\01\2013','10:30')


values ('3939','rayan','11\01\2013','5:45')

Figure 20

Update

update plane set controllername='erdam' where controletime='2:10';

Figure 21

Delete

delete controller where controllername='erdam'

Figure 22

Select

select controllername,controletime from controller

Figure 23

View report

Figure 24

FIELDS

Create table

CREATE TABLE "SYSTEM"."FIELDS "

( "FIELDID" NUMBER(4,0) NOT NULL ENABLE ,

"FLYINGFIELDNO" VARCHAR2(10 BYTE) NOT NULL ENABLE ,

"LANDINGFIELDNO" VARCHAR2(20 BYTE ,)

"FLYINGFAVAIL" NUMBER(1,0 ,)

"LANDINGFAVAIL" NUMBER ,

CONSTRAINT "LANDINGFAVAIL1" CHECK (LANDINGFAVAIL BETWEEN 0 AND 1)

ENABLE ,

CONSTRAINT "FLYINGFAVAIL1" CHECK (FLYINGFAVAIL BETWEEN 0 AND 1) ENABLE ,

CONSTRAINT "FIELDS_PK" PRIMARY KEY ("FIELDID)"











Insert data

insert into fields(fieldid,flyingfieldno,landingfieldno,flyingfavail,landingfavail)

values ('3030','12','14',1,0)


values ('3131','11','15',0,0)


values ('3232','9','13',0,1)


values ('3434','6','3',1,1)


values ('3535','11','2',0,0)


values ('3636','7','12',1,1)


values ('3737','5','11',0,1)


values ('3838','13','14',1,0)


values ('3939','15','6',1,1)

Figure 25

Update

update fields set flyingfavail=1 where flyingfieldno='11'

Figure 26

Delete

delete fields where flyingfieldno='13

Figure 72

Select

select fieldid,flyingfieldno,flyingfavail from fields

select fieldid,landingfieldno,landingfavail from fields

Figure 28

Figure 29

WEATHER

Create table

CREATE TABLE "SYSTEM"."WEATHER"

( "WEATHERID" NUMBER(4,0) NOT NULL ENABLE,

"WEATHER" VARCHAR2(20 BYTE) NOT NULL ENABLE,

"CLOUDDENSITY" VARCHAR2(20 BYTE),

"TEMPERATURE" VARCHAR2(20 BYTE),

"RAINGAUGE" VARCHAR2(20 BYTE),

CONSTRAINT "WEATHER_PK" PRIMARY KEY ("WEATHERID")









Insert data

insert into weather(weatherid,weather,clouddensity,temperature,raingauge)

values ('3030','shiny','partly','17','no')


values ('3131','cloudy','fully','11','2\m2')


values ('3232','shiny','no','15','no')


values ('3434','rainy','full','6','12\m2')


values ('3535','Snow','full','2','no')


values ('3636','snow','full','0','no')






values ('3939','rainy','fully','9','10\m2')

Figure 30

Update

update weather set temperature='2' where temperature='0'

Figure 31

Delete

delete weather where raingauge='10\m2'

Figure 32

Select

select weather,temperature,raingauge from weather

Figure 33

CRITERIA

Create table

CREATE TABLE "SYSTEM"."CRITERIA"

( "CRITERIAID" NUMBER(4,0) NOT NULL ENABLE,

"WEATHERDETAILS" VARCHAR2(100 BYTE) NOT NULL ENABLE,

"FIELDSDETAILS" VARCHAR2(100 BYTE),

"CAPTAINDETAILS" VARCHAR2(100 BYTE),

"PLANEDETAILS" VARCHAR2(100 BYTE),

CONSTRAINT "CRITERIA_PK" PRIMARY KEY ("CRITERIAID")











Insert data

insert into criteria(criteriaid,weatherdetails,fieldsdetails,captaindetails,planedetails)

values

('3030','http://www.fly.faa.gov/flyfaa/usmap.jsp','http://airtrafficcontrol.minneapolis.edu/a

dmissionrequirements','http://www.avjobs.com/careers/detail.asp?RecID=2#.UPMDnh30Bn

E','http://www.bls.gov/ooh/transportation-and-material-moving/air-traffic-controllers.htm')

Figure 34

CHECKINGTABLE

Create table

CREATE TABLE "SYSTEM"."CHECKINGTABLE"

( "CHECKID" NUMBER(4,0) NOT NULL ENABLE,

"CHECKNO" VARCHAR2(10 BYTE) NOT NULL ENABLE,

"CHECKDATE" VARCHAR2(20 BYTE),

"CHECKTIME" VARCHAR2(20 BYTE),

CONSTRAINT "CHECKINGTABLE_PK" PRIMARY KEY ("CHECKID")






CONSTRAINT "C1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."CAPTAINS" ("CAPTAINID") ENABLE,

CONSTRAINT "CL1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."CONTROLLER" ("CONTROLLERID") ENABLE,

CONSTRAINT "CO1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."COMPANIES" ("COMPID") ENABLE,

CONSTRAINT "F1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."FLIGHTS" ("FLIGHTID") ENABLE,

CONSTRAINT "FE1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."FIELDS" ("FIELDID") ENABLE,

CONSTRAINT "P1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."PLANE" ("PLANEID") ENABLE,

CONSTRAINT "W1" FOREIGN KEY ("CHECKID")

REFERENCES "SYSTEM"."WEATHER" ("WEATHERID") ENABLE






Insert data

insert into checkingtable(checkid,checkno,checkdate,checktime)

values ('3030',check_seq.NEXTVAL,'19\1\2013','2:30am')

















Figure 35

airport traffic control simple database model

Documents