0

I am trying to make a generic database for airport management and DBMS would be oracle 11g xe. Since, i am amateur and still learning so i have tried to kept the problem statement simple with limited entities involved:

Airport serves a city but every city may not have an airport. An airline has office(ticketing booth) at airports they cover and hence an airport may have many airline offices. Airport accommodates airplanes and airplanes may or may not belong to a certain airline(considering private jets). Airports may have one runway or more than one. Runway has a name and length. Every airport has a manager. Manager can cross view the other data but he can alter data for airport under his belt only- this i think will be applied via front-end application using 'APEX'.

Here is the relational schema i have worked so far:

City (CityID{PK}, CityName, CountryName)

Airport (AirportID{PK},AirportName, ICAO, IATA,ManagerID{FK},CityID{FK})

AirportRunway (AirportID{FK}, RunwayID{FK}, RunwayName, length)

Airline (AirlineID{PK}, AirlineName, IATA, ICAO)

AirportAirlineOffice (AirlineID{FK}, AirportID{FK}) both foreign keys make a primary key.

Airplane (AirplaneID, Model, Make, AirlineID{FK})

Manager (ManagerID{PK}, username, password, ManagerName)

I am confused over manager relation considering what i am trying to implement. Do i need to make relationsship of manager with other entities as well? Do i need to make AirlineOffice as entity or it is good as it is. And what changes do i need to do so that i can convert this schema to 3rd normalized form!

edit: here is my ERD:

ERD

Improve this question

1 Answer 1

Reset to default
1

You mention every airport has a manager so the manager table should have AirportID as an FK. The Office table should have AirportId and AirlineID as FKs

Improve this answer
6
  • kindly clarify, manager manages airport so i put a primary key of manager in airport that makes one relationship. Now, If i also put airport PK in manager table, will that change the relationship nature? i.e. binary
    – chaudry
    Commented May 17, 2014 at 15:35
  • OK sorry I didn't see the ManagerID in the Airport table. Leave that as it is.
    – James Anderson
    Commented May 17, 2014 at 15:37
  • A mistake on my part, i was using AirlineAirport table as a result of relationship: airline has office at airport. I will fix that. Basically when i make a front-end application, i want to restrict the access of a manager to his own airport e.g. airplanes at his airport and airlines covering his airport now do i need to relate manager to airplane and airline or it is fine the way it is. Thanks for your help
    – chaudry
    Commented May 17, 2014 at 15:41
  • Well you can already join manager to airport and with a join to aiirportAirline and then another to airline you have all the relations needed. Remember the database is a data store and business logic is belt dealt with at the application layer.
    – James Anderson
    Commented May 17, 2014 at 15:48
  • Thanks, that was pretty helpful. My last question, above schema satisfies 3rd normal form or not?
    – chaudry
    Commented May 17, 2014 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.