Question about 3rd Normal Form 1

[dcusick]

Hey everyone.. I'm working on a database schema that I need to have in 3NF. It's looking good to me as I have it, but I just wanted an extra set of eyes on it to make sure I'm not tricking myself into believing it's in the correct form. The Database is for booking Cruises and Flight Reservations through a travel agent. I have the following 10 tables...

a) Customer (SSN, CName, EmployeeNo)
b) Agent (EmployeeNo, AName, AgencyName)
c) Flight (FlightNo, FlightDate, AirplaneID)
d) Airplane (AirplaneID, AirplaneCapacity)
e) Cruise (CruiseNo, CruiseDate, ShipID)
f) Ship (ShipID, ShipCapacity)
g) FlightReservation (EmployeeNo, SSN, FlightNo)
h) CruiseReservation (EmployeeNo, SSN, CruiseNo)
i) FlightBooking (SSN, FlightNo, Row, Seat)
j) CruiseBooking (SSN, CruiseNo, Deck, Room)

Any input is greatly appreciated. Thanks.

Doug

 

[CajunCenturion]

I see a couple of issue right off the top.
Customer Table: Using SSN as a key may not always work in that some people do not have a SSAN.

In the Flight table, FlightNo by itself is not unique as the same flight number is used daily. The FlightDate needs to be part of hte key as well.

The AirplaneID in an of itself may not be able determine the capacity as different airlines configure the same aircraft type differently resulting in a different number of seats. (If the AirplaneID has a Airline component part, then this may not apply.)

In the Cruise Table, is the data also necessary as part of the key? Or is an individual CruiseID unique.

In the customer table you have an EmployeeID and in the Agent Table you have an EmployeeID. In your reservations table you have an EmployeeID. What is the relationship between all of these EmployeeID fields?

Do the Flight and Cruise Dates need to be included in your reservation and booking tables? Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dcusick]

Thanks for the response. Much appreciated.

Okay. A very valid point about the dates. I added them into the primary keys of both the Flight and Cruise tables. I also added them to the reservation and booking tables, as per your suggestion.

For SSN, I'm going to assume everyone has a SSN. As for AirplaneID, I'm going to assume this is the unique ID for each airplane no matter what airline it belongs to, sort of like how a VIN # on a car is unique to each and every car, no matter what make it is or which dealer it came from.

The EmployeeID has the following relationships...

There is a many-to-one relationship between Customer and Agent, or many customers can have the same agent, but each customer can only have one agent.

There is a many-to-one relationship between the reservation tables and Agent, or many reservations can be made by an agent, but each reservation can only be made by one agent.

Thanks again,

Doug

 

[CajunCenturion]

I you find out the actual N-number of the aircraft, that would be unique and would be a good choice for the Aircraft ID. To the best of my knowledge, they are unique, at least to aircraft registered in the USA. I don't know about foreign aircraft.

With respect to the EmployeeNo, if an agent were to in fact also be a customer, would that individual have two different EmployeeNo's - one for their Customer record, and one for their Agent record?

Perhaps the confusion is with the field name itself. It might make more sense to have a CustomerID and an AgentID. I would think it would more clearly define the relationships, unless I'm complete misunderstanding what these fields are. Also, given that the Customer is keyed on SSN, what role does the EmployeeNo play in the Customer table? Do you even need a CustomerID?

That brings me to the key of the two reservation tables. Is that EmployeeID the customer for whom the reservation is made, or the agent who made the reservation. Because the SSN is part of the key, I surmise that the EmployeeNo refers to the agent, and if so, I wouldn't think that it needs to be part of the key. The SSN, FlightNo, and FlightDate should be sufficient to uniquely define a specific reservation. I would keep the AgentID in the table, but not as part of the key.

Again, without a full understanding of the application, you might want to consider a Event (for lack of a better term)
EVENT(EventID, SSN, AgentID)
To indicate that this booking was made by this agent (AgentID) for this customer (SSN). Then, the key for the reservations, and the two booking tables would be the EventID, concatenated with the flight and/or cruise details. I suggest this because there may be a one-to-many relationship between an event and a series and flights and cruises all part of the same vacation package which may include multiple flight segments to a cruise and the return flights.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dcusick]

You bring up another valid point with the EventID. I think I'm going to go with that. Also, your assumption about EmployeeID really being AgentID is correct. I guess I put it wrong. AgentID as a field name would make more sense than EmployeeID. Thanks for all the help. This will bring me to the normalized database that I've been looking for.

Doug

 

[ceh4702]

I dont know if this will make sense to you or not. In a class I took at a university I remember this from a similar saying about swearing on the bible in the courtroom to tell the truth.

I promise to tell the truth, the whole truth, and nothing but the whole truth.

Similarly this a simple version of 1st, 2nd and 3rd normal form.

1. Identifies the key.
2. Identifies the whole key.
3. Identifies nothing but the whole key.

If you do not like my post feel free to point out your opinion or my errors.

 

[ceh4702]

I was looking at your definitions you have cruise and flight. When you book a flight for a cruise, you may want to relate that to the actual cruise you are booking it for.

When you think about this do not identify the tables first. Identify what type of information or reports you want to be able to obtain from the database.

I am guessing you are booking the flight to the location the cruise ship leaves from. What if you want to get a list of all flight bookings for a specific cruise?

For instance when a cruise is cancelled, how do you have to track cancell all the flights you booked? Will it be the agents job to cancel the flight if the cruise is cancelled? Another thing is Trip Insurance. What about the cancellation policy?

On the bookings there may some levels of service you have to account for. For example on a plane you may have 1st class, coach etc, normal passenger. The same may apply to a cruise ship. Different cabins may have different booking rates.

I think a cruise is a package containing
A: Plane to the cruise ship.
B: A cruise that starts on one date and ends on another date.
C: Plane from the Cruse to go home?

To book the plane home, don't you need to know when the cruise ship returns?

Good Luck!



If you do not like my post feel free to point out your opinion or my errors.