Lab Tutorial 2

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 ????