1

I am currently following notes on a certain "7 step strategy" to transform an ERD to a relational schema and was wondering some points about step 4, converting one-to-many (1:N) relationships.

The relevant ERD is the following one:

ER Diagram

Questions

  1. Would SEAT contain

    • Seat_no, Date (from LEG_INSTANCE)

    or

    • Seat_no, Customer_name (from RESERVATION), Cphone (from RESERVATION), Date (from LEG_INSTANCE)?
  2. Would FLIGHT_LEG contain Scheduled_dep_time & Scheduled_arr_time?

  • All I can say from having worked in the industry is that flight_leg must contain a scheduled_dep and scheduled_arr which are both datetimes. There should also be blocks_off and blocks_off and other VR details. – Vérace Jan 16 '19 at 20:44
2

First, a 1-N relationship, the table on the N side would have the key of the table on the 1 side configured as Foreign Key.

So depending how leg_instance is keyed, either:

  Seat: seat_no, Date(from leg_instance), leg_no (from flight_leg), Number (from flight), 
  with key (Number,leg_no,seat_no)
  and foreign key (leg_no,Date) => leg_instance(leg_no,Date)

or

Seat: seat_no, leg_instance_id (from leg_instance)
with key (leg_instance_id, seat_no)
and foreign key (leg_instance_id) => leg_instance(leg_instance_id)

Then, the table on the N side caries all attributes of the relationship, so here Seat would also have Customer_name and Cphone. It would be permissible to introduce a separate table for Reservation that is 1-1 with Seat, but 1-1 relationships are normally just merged into a single table. That table might end up being named SEAT, RESERVATION or something like SEAT_RESERVATION, as it implements the ERD shapes with both those names.

Another question I have is would flight_leg contain scheduled_dep_time & scheduled arr_time ?

You should be able to answer that yourself, now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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