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)

);