Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema 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:



Questions


Would SEAT contain


Seat_no, Date (from LEG_INSTANCE)


or


Seat_no, Customer_name (from RESERVATION), Cphone (from RESERVATION), Date (from LEG_INSTANCE)?

Would FLIGHT_LEG contain Scheduled_dep_time &amp; Scheduled_arr_time?
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) =&gt; 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) =&gt; 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 &amp; scheduled arr_time ?


You should be able to answer that yourself, now.