How to create an account and display free places?

Question

MySQL

I am writing a course project on the topic: "Automation of ticket creation at the airport ticket office". Essence of the question: how to create an accounting and display of free / occupied seats for a flight if there are tables:
Aircraft with fields - id,_sun tail number, number of seats;
Flight - id_flight, id_sun, name, number of seats, number of empty seats, number of occupied seats;

Answer 1

Aircraft
- vessel id (pk, a_i)
- tail number (unique)
- number of seats
- vessel type (id from the following table)
Aircraft types
- type id
- name (airplane, helicopter, whatever)
Flight
- flight id ( pk, a_i)
- flight name
- flight date
- number of free seats (optional)
- number of occupied seats (optional)
Plane-flight linking table
- aircraft id (id, 1st table)
- flight id (id, 2nd table )
Passengers
- passenger id (pk, a_i)
- last name
- first name
Check-in for the flight
- flight id - check-in date
- passenger id
Occupied seats
- flight
id - passenger id
- seat number from the table below
Seats in the aircraft
- seat
id - vessel id
- seat number
I would make such a structure.
The number of seats is optional, they can be calculated according to the table with the check-in for the flight.
Based on two tables, it is difficult to solve this problem: this is never a system for managing an airport + in a table with flights - there will be constant duplication of data if they are not taken out into a separate entity.

Answer 2

I suggest adding the "Document Number" (passport) field to the passenger and making it a natural key, because two Ivanov Boguslavs are coming up, and they forgot the tickets at home, how will you print tickets for them without a reservation number?