AMS Table - Airport Management System For Database PDF

Preview

Summary

Airport Management System For Database ...

Description

Data Requirements •

Database management system required an operating system that have capability to run and perform the operation of database with queries

•

Every city which have city id and name can have one or more than one airport and it is identified by unique IATA code and name.

•

There are many employees (Employee ID, Name, Age, Address and Phone Number) in airport working Full Time and Part Time at different departments e.g. Commercial which is for case inquiry and customer support, HR for jobs, Security for VIP and from terrorist, visa department for visa checking and Visa Type. Full time employees have fixed salary and part time gets pay on number of hours.

•

There are many airline companies of different countries with ID and Name having aeroplanes of different capacity and ID, these are used for flights. Where every flight have fixed departure and arrival time.

•

Passengers (ID, Name, Country and Address) purchase tickets with unique number at certain price of Economy or Business from supplier at his location and name of different type of packages Hajj or Umrah etc.

•

Airport has different control towers with controlling capacity and range that control flights.

•

Passengers wait for airplane in gatehouse where there is seating, counters and jet bridges and they use internal transport which might be bus to reach airplane for certain flight.

•

There is many landing area for airport consists runways of different width and length and helipad more than one.

•

Airport also have different parkings of different capacity and fee.

•

There are many gates each for different purposes e.g. enter, exit, cargo, VIP.

Entities Airport

Employees

Emp_phone Full_time Part_time

Department

Attributes IATA (Primary Key) Name City Emp_ID (Primary Key) Name Address Salary Emp_ID (Foreign Key) Phone_no Emp_ID (Foreign Key) Salary Emp_ID (Foreign Key) No_of_hours Fee_per_hour Dept_ID (Primary Key) 1

Communication_Dept

Commercial_Dept

Security_Dept

Infrastructure_Maintainece_Dept

Visa

Passengers

Passenger_emp Passengers_phone Luggage

Tickets Economy Business Supplier

Purchases

Packages

Control_tower

No_of_Emp Dept_ID (Foreign Key) Inquiry Customer_support Dept_ID (Foreign Key) Package Jobs Dept_ID (Foreign Key) Terrorists Personal_Guards Dept_ID (Foreign Key) Airplane_status Helicopter_status Renovation Dept_ID (Foreign Key) Check Visa_Type Passenger_ID (Primary Key) Name Country Address Passenger_ID (Foreign Key) Emp_ID (Foreign Key) Passenger_ID (Foreign Key) Phone_no Luggage_ID (Primary Key) Passenger_ID (Foreign Key) Bags Weight Extra Ticket_ID (Primary Key) Price Ticket_ID (Foreign Key) Seatno Ticket_ID (Foreign Key) Cabin_no Supplier_ID Name Location Ticket_ID (Foreign Key) Passenger_ID (Foreign Key) Supplier_ID (Foreign Key) Package_ID (Primary Key) Supplier_ID (Foreign Key) Hajj Umrah CT_ID (Primary key) Airport (Foreign Key) Range No_of_towers Controlling capacity 2

Flights

Domestic International

Gatehouse

Airplane

Internal_transport

Operate_between

Airline_company

Landing_area

Runaway

Helipad Lightning

Transit_hotel

Parking

Capacity Gate

F_no (Primary key) Ticket_id (Foreign key) CT_ID (Forign Key) Departure_time Arrival_time F_no (Foreign Key) City F_no (Foreign Key) Country City GH_no (Primary key) Airport (Foreign Key) Seating Counters Jet_bridge Airplane_id (Primary key) F_no (Foreign key) Capacity Transport_no (Primary key) Bus_no F_no GH_no (Foreign key) Airplane_id (Foreign key) Transport_no (Foreign key) AC_ID (Primary key) Name Country LA_no (Primary Key) Airport_id (Foreign key) Area_covered LA_no (Foreign key) Length Width No_of_runways LA_no (Foreign key) How_many Light_no (Primary key) LA_no (Foreign key) No_of_lights Types_of_lights TH_no (Primary key) Airport_id (Foreign key) Rooms Rent Park_no (Primary key) Airport_id (Foreign key) Fee Park_no (Foreign key) Capacity Gate_no (Primary key) Airport_id (Foreign key) 3

City

Type City_id (Primary key) Airport_id (Foreign key) Name Country

There is no repeated value in table and each attribute is identified by superkey. So above tables are 1NF. There is no composite key in table. So above tables are 2NF. There is no transitive dependency in the table as all fields depending on primary key. So above tables are 3NF. And no non-primary key is pointing out primary key. So all tables are in BCNF

4...