Databases | SQL | Oracle | MS Access Answers

CREATE TABLE Passenger
(
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL );

CREATE TABLE Plane
(
id INTEGER PRIMARY KEY,
capacity INTEGER,
type CHAR(1) );

CREATE TABLE Flight
(
id INTEGER,
planeId INTEGER,
flightDate DATE,
startLocation VARCHAR(20),
destination VARCHAR(20),
PRIMARY KEY (Id),
FOREIGN KEY (PlaneId) REFERENCES Plane );

CREATE TABLE Reservation
(
passengerId INTEGER,
flightId INTEGER,
PRIMARY KEY (passengerId, flightId),
FOREIGN KEY (passengerId) REFERENCES Passenger,
FOREIGN KEY (flightId) REFERENCES Flight );
Give an integrity constraint PlaneCapacity that enforces the restriction that the number of passengers on a plane cannot exceed the plane’s capacity