Design a schema for an airline containing the following information.
i. the unique Id and name of each passenger
ii. the unique Id, type (possible value A, B, and C) and capacity of each plane
iii. the unique Id, date, plane Id, starting location and destination of each flight (a plane
cannot be scheduled for more than one flight on any particular day)
iv. each reservation made by a passenger on a flight
Show all primary and foreign keys.
CREATE TABLE Passengers (
Id INTEGER,
Name CHAR(20),
PRIMARY KEY (Id) )
CREATE TABLE Planes (
Id INTEGER,
Capacity INTEGER,
Type CHAR(1),
CHECK (Type IN ('A', 'B', 'C') )
PRIMARY KEY (Id) )
CREATE TABLE Flights (
Id INTEGER,
PlaneId INTEGER,
Date DATE,
Start TIME,
Destination CHAR(20),
PRIMARY KEY (Id),
UNIQUE (PlaneId, Date),
FOREIGN KEY (PlaneId)
REFERENCES Planes (Id) )
CREATE TABLE Reservations (
PassengerId INTEGER,
FlightId INTEGER,
PRIMARY KEY (PassengerId, FlightId),
FOREIGN KEY (PassengerId)
REFERENCES Travellers (Id),
FOREIGN KEY (FlightId)
REFERENCES Flights (Id) )
You might also like to view...
In a one-to-many relationship, the related table is on the “one” side of the relationship. _________________________
Answer the following statement true (T) or false (F)
Which of the following statements is true?
A) Generic types do not exist at the byte code level. B) A raw type is a class that cannot be extended. C) A raw type is a class that must be extended to be used. D) A generic class cannot be abstract.
____ targeting analyzes frequently used words in your blog to match ads to blog content.
A. Visibility B. Placement C. Twitter D. Contextual
Explain why large firms use data warehouses.
What will be an ideal response?