CREATE DATABASE IF NOT EXISTS SCAD; USE SCAD; -- Basic Pilot Information CREATE TABLE Pilot (
Question:
CREATE DATABASE IF NOT EXISTS SCAD;
USE SCAD;
-- Basic Pilot Information
CREATE TABLE Pilot
(
license VARCHAR(10),
pname VARCHAR(50),
phone CHAR(10),
miles int,
CONSTRAINT Pilot_PK PRIMARY KEY(license)
);
-- Basic Aircraft Information
CREATE TABLE Aircraft
(
id VARCHAR(10),
make VARCHAR(25),
model VARCHAR(25),
capacity int,
CONSTRAINT Aircraft_PK PRIMARY KEY (id)
);
-- Basic Custome Information
CREATE TABLE Customer
(
cname VARCHAR(50),
phone CHAR(10),
email VARCHAR(50),
CONSTRAINT Customer_PK PRIMARY KEY (email)
);
CREATE TABLE Flight
(
departure VARCHAR(25),
destination VARCHAR(25),
aTime TIME,
dTime TIME,
fNumber VARCHAR(10),
id_FK VARCHAR(10),
CONSTRAINT Flight_PK PRIMARY KEY (fNumber),
CONSTRAINT Flight_FK1 FOREIGN KEY (id_FK)
REFERENCES Aircraft(id)
);
-- Containns information on what pilots are assigned ot Aircraft
CREATE TABLE Assigned
(
aDate DATE,
aTime TIME,
id_FK VARCHAR(10),
license_FK VARCHAR(10),
CONSTRAINT Assigned_PK PRIMARY KEY (aDate, aTime, id_FK, license_FK),
CONSTRAINT Assigned_FK1 FOREIGN KEY (id_FK)
REFERENCES Aircraft(id),
CONSTRAINT Assigned_FK2 FOREIGN KEY (license_FK)
REFERENCES Pilot(license)
);
-- Contains information on Customer Bookinks for a Scheduled Flight
CREATE TABLE Books
(
seat VARCHAR(10) NOT NULL,
email_FK VARCHAR(50),
fNumber_FK VARCHAR(10),
bookNumber INT AUTO_INCREMENT,
CONSTRAINT Books_PK PRIMARY KEY (bookNumber),
CONSTRAINT Books_FK1 FOREIGN KEY (email_FK)
REFERENCES Customer(email),
CONSTRAINT Books_FK2 FOREIGN KEY (fNumber_FK)
REFERENCES Flight(fNumber)
);
-- The Seat Table Holds the total seats that are on an Aircraft
CREATE TABLE Seat
(
row VARCHAR(2),
Number VARCHAR(2),
id_FK VARCHAR(10),
CONSTRAINT Seat_PK PRIMARY KEY (row, Number, id_FK),
CONSTRAINT Seat_FK1 FOREIGN KEY (id_FK)
REFERENCES Aircraft(id)
);
Database Systems Design Implementation and Management
ISBN: 978-1337627900
13th edition
Authors: Carlos Coronel, Steven Morris