Table Normalisation - Please Help

I have an airport database with 5 tables:

AIRLINE
AirlineID (PK)
AirlineName

PLANE
PlaneID (PK)
PlaneType
AirlineID

PILOT
Name (PK)
Adress
Licence
AirlineName
PlaneType

PLANE TYPES
PlaneType (PK)
MaximumRange
DateOfManufacture

AIRPORT
Airport(PK)
AirlineName

Each pilot only works for one airline. Several types of planes use the airport. Theres only one airport but many airlines use it.

Am I on the right track? Do I have the right tables?Do I have fields in the right tables?

Any help would be appreciated!!

I think you're on the right track. However, there are a few things to consider. Tables are part of the issue,but the relationships between tables is another. Once you get all your questions defined --such as your Each pilot only works for one airline-- you can then test these against your data model (tables and relationships). You won't know if you have all the required tables until you get the complete set of questions defined. You may find that you have most of it answered but then find you need another table/relationship to answer a necessary question.

For example, I don't think PlaneType has a DateOfManufacture. Each individual plane would have a DateOfManufacture.

A pilot is probably qualified to fly move than 1 PlaneType, so a separate Table tblPilotPlaneType consisting of PilotId and PlaneType could be necessary. Again depends on your "problems set" to be handled by your database/application.

Is PilotName really a good unique identifier for Pilot. Could your database ever have 2 pilots with the same name --eg Jim Smith.
These are the types of questions that will help determine if you have the right tables, all the tables and all the necessary relationships.

Hope this helps.

Here's a link that may be useful
http://r937.com/relational.html

Hi Larry S,

Although i have only taken a quick look at your tables, you do seem to be on the right track. But i would also urge you to try and create a "Data Model" of your database or some kind of conceptual database with diagrams and stuff. just so as to get a feel of what your trying to do. And in line with what "Orange" said its not until you do things like that you that you get an idea of how many tables and stuff might be need.

Speaking of tables i am not entirely sure why you have created the table "Airport" since there is only one airport and the database itself represents this entity. Also choose your primary key carefully, the idea being that they are supposed to be unique and not duplicated.


cheers 1databyte

Thank you for all the suggestions. I modified the tables:

AIRLINE
AirlineID (PK)
AirlineName

PLANE
PlaneID (PK)
MaximumRange
DateOfManufacture
AirlineID

PILOT
PilotNo (PK)
Name
Adress
Licence

PLANE TYPE
PlaneID(PK)
PlaneType

PILOTPLANES
PilotID(PK)
PlaneType
AirlineID

Does that look right? Any fields in the wrong tables? Any suggestions?

Thanks

Do you care about the expiration of the pilot's license? Is it relevant?
Are there issues regarding Aircraft maintenance or would that be in a separate dB? Availability of aircraft?
I would suggest you not use the pilot name as a PK. Give it an autonumber as the Pilot ID and use the name as a field. Will save you lots of aggravation in the future.

Alan

Larry,

From the responses received, you are on the right track.
I think i's time to make a list of statements as to what your database application is supposed to do.

This will assist you and anyone trying to help you with scope and details as to whether or not the fields are in the right table, the proper tables etc.

For example, you mention airport, but is your application only concerned with 1 airport?
As Alan asked, is the pilots' license information relevant to your "business"?

A pilot is licensed to fly a specific planeType, but I don't think it has anything specific to do with Airline. But I could be wrong.

Can a Plane be sold/leased to another Airline? Do you intend to track which Plane belongs to which Airline?
Individual planes may not need to have MaximumRange data. Maximum range may be associated with a PlaneType.

These are the kinds of questions you must identify, and determine if they are important to your database or not. Then you'll have the base to test your data model, and adjust it as necessary. It's your "business", and you know what's important and what isn't. We can only suggest/ask a few things that may help you.