Consider the following business domain:
Airlinehas a unique airline id (
aid) and contains zero-or-more
Planehas a unique plane id (
pid) in the
Airlineit flies for (but planes from different
Airlinescan have overlapping
Seathas a unique seat id (
sid) in its plane (but
Planesmay have overlapping
My attempt so far
Here is my attempt at solving this:
CREATE SEQUENCE planes_seq; CREATE TABLE Airlines ( aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq') ); CREATE TABLE Planes ( aid INTEGER REFERENCES Airlines(aid) , pid INTEGER , PRIMARY KEY(aid, pid) ); CREATE TABLE Seats ( aid INTEGER , pid INTEGER , sid INTEGER , PRIMARY KEY(aid, pid, sid) , FOREIGN KEY(aid, pid) REFERENCES Planes(aid, pid) ); ALTER TABLE Planes ADD CONSTRAINT fk_seats FOREIGN KEY(aid, pid) REFERENCES Seats(aid, pid);
However, it fails because the final
ALTER TABLE is illegal since the pair
(Seats.aid, Seats.pid) is indeed not unique.
- How could I enforce the "a plane has at least one seat" constraint?
- Is this scheme in 3rd Normal Form?
PS: I am new to SQL and this is a (small part) of a homework assignment. I have tried following the parent-with-at-least-one-child example from another DBA question (Constraint to enforce "at least one" or "exactly one" in a database) but there are way too many tricks there that I cannot understand (multiple
WITH queries, a
RETURNING clause and so on). It looks to me like there must be a simpler way to do it.