Mastering Database Management

0 of 78 lessons complete (0%)

Lab Assignment/Tutorial

Lab Tutorial 2

You don’t have access to this lesson

Please register or sign in to access the course content.

Task:

Step 1: Register to DB Designer
Step 2: Start Creating database model with the below table structure. 

Entities:

  1. Passenger:
    • Attributes: PassengerID (Primary Key), FirstName, LastName, Email, Phone
  2. Flight:
    • Attributes: FlightID (Primary Key), FlightNumber, DepartureAirport, ArrivalAirport, DepartureDateTime, ArrivalDateTime, TotalSeats
  3. Reservation:
    • Attributes: ReservationID (Primary Key), PassengerID (Foreign Key references Passenger), FlightID (Foreign Key references Flight), ReservationDateTime
  4. Seat:
    • Attributes: SeatID (Primary Key), FlightID (Foreign Key references Flight), SeatNumber, IsOccupied

In this model:

  • PassengerID is the primary key for the Passenger entity.
  • FlightID is the primary key for the Flight entity.
  • ReservationID is the primary key for the Reservation entity.
  • SeatID is the primary key for the Seat entity.

Foreign keys establish relationships between entities:

  • In the Reservation entity, PassengerID is a foreign key referencing the Passenger entity, and FlightID is a foreign key referencing the Flight entity.
  • In the Seat entity, FlightID is a foreign key referencing the Flight entity.

This design allows you to associate passengers with reservations, flights with reservations, and seats with flights.

Take Quiz: With the above case now give below questions a try, can these query be solved or not ????