Scenario : 1

Create the Following Tables with suitable Primary keys , Foregin keys and identify the order of tables ?

Movie Table : movieid, movie_name, rating, duration, seatno, timeslotid

Theatre Table : theatreid, seatno, moviename, movieid, staffid, timeslotid

Timeslot Table : timeslotid, moviename, movieid, staffid, theatreid

Booking Table : bookingid, bookingdate, bookingseat, booking_timeslot, staffid

Ticket Table : ticketid, movieid, staffid, theatreid, bookingid

Staff Table : staffid, fullname, mobile, email, dob

Solution :

Staff Table:

• staffid (Primary Key)
• fullname
• mobile
• email
• dob

Timeslot Table:

• timeslotid (Primary Key)
• moviename
• movieid (Foreign Key referencing Movie Table)
• staffid (Foreign Key referencing Staff Table)
• theatreid (Foreign Key referencing Theatre Table)

Movie Table:

• movieid (Primary Key)
• movie_name
• rating
• duration
• seatno
• timeslotid (Foreign Key referencing Timeslot Table)

Theatre Table:

• theatreid (Primary Key)
• seatno
• moviename
• movieid (Foreign Key referencing Movie Table)
• staffid (Foreign Key referencing Staff Table)
• timeslotid (Foreign Key referencing Timeslot Table)

Booking Table:

• bookingid (Primary Key)
• bookingdate
• bookingseat
• booking_timeslot
• staffid (Foreign Key referencing Staff Table)

Ticket Table:

• ticketid (Primary Key)
• movieid (Foreign Key referencing Movie Table)
• staffid (Foreign Key referencing Staff Table)
• theatreid (Foreign Key referencing Theatre Table)
• bookingid (Foreign Key referencing Booking Table)

The order of tables takes into account the dependencies between them. For instance:

• Staff Table          :   No other table depends on it, so it comes first.
• Timeslot Table    :   It is referenced by the Movie Table, Theatre Table, and Booking Table.
• Movie Table       :   It is referenced by the Theatre Table and Ticket Table.
• Theatre Table     :   It is referenced by the Timeslot Table and Ticket Table.
•  Booking Table   :   It is referenced by the Ticket Table