Table Creation.sql

--DROP DATABASE IF EXISTS AIRPORT;
--CREATE DATABASE AIRPORT;

USE AIRPORT;


--DROP TABLE IF EXISTS PLANE_TYPE;
CREATE TABLE PLANE_TYPE (
	Model			VARCHAR(15)	NOT NULL, 
	Capacity		SMALLINT,
	Weight			INT,

	CONSTRAINT P_Model PRIMARY KEY (Model)

);


--DROP TABLE IF EXISTS CORPORATION;
CREATE TABLE CORPORATION (
	ID			CHAR(7)			NOT NULL, 
	Name		VARCHAR(30)		NOT NULL,
	Address		VARCHAR(50),
	Phone		NUMERIC(11, 0),

	CONSTRAINT C_ID PRIMARY KEY (ID)

);


--DROP TABLE IF EXISTS HANGAR;
CREATE TABLE HANGAR (
	Number			SMALLINT	NOT NULL, 
	Capacity		TINYINT,
	Location		VARCHAR(20),
	Owner			CHAR(7), 

	CONSTRAINT H_Number PRIMARY KEY (Number),
	CONSTRAINT H_Owner FOREIGN KEY (Owner) REFERENCES CORPORATION(ID)

);


--DROP TABLE IF EXISTS AIRPLANE;
CREATE TABLE AIRPLANE (
	RegNo			INT			NOT NULL, 
	Of_Type			VARCHAR(15)	NOT NULL,
	Stored_In		SMALLINT	NOT NULL,

	CONSTRAINT A_RegNo PRIMARY KEY (RegNo),
	CONSTRAINT A_Of_Type FOREIGN KEY (Of_Type) REFERENCES PLANE_TYPE(Model),
	CONSTRAINT A_Stored_In FOREIGN KEY (Stored_In) REFERENCES HANGAR(Number)

);


--DROP TABLE IF EXISTS PERSON;
CREATE TABLE PERSON (
	SSN			CHAR(9)			NOT NULL, 
	FName		VARCHAR(15)		NOT NULL,
	LName		VARCHAR(15)		NOT NULL,
	Address		VARCHAR(20),
	Location	VARCHAR(20),
	Phone		NUMERIC(11, 0),

	CONSTRAINT P_SSN PRIMARY KEY (SSN)

);


--DROP TABLE IF EXISTS PILOT;
CREATE TABLE PILOT (
	PSSN		CHAR(9)			NOT NULL, 
	Lic_num		VARCHAR(5)		NOT NULL,
	Restr		VARCHAR(30),

	CONSTRAINT Lic_num UNIQUE (Lic_num),
	CONSTRAINT SP_PSSN PRIMARY KEY (PSSN),
	CONSTRAINT SP_SSN FOREIGN KEY (PSSN) REFERENCES PERSON(SSN)

);


--DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (
	PSSN		CHAR(9)			NOT NULL, 
	Salary		DECIMAL(10, 2),
	EShift		CHAR(5),

	CONSTRAINT SE_PSSN PRIMARY KEY (PSSN),
	CONSTRAINT SE_SSN FOREIGN KEY (PSSN) REFERENCES PERSON(SSN)

);


--DROP TABLE IF EXISTS SERVICE;
CREATE TABLE SERVICE (
	ID			INT			NOT NULL, 
	Plane_Id	INT			NOT NULL,
	Work_code	SMALLINT,
	SHours		SMALLINT,
	Status		CHAR,
	SDate		DATE,

	CONSTRAINT S_ID PRIMARY KEY (ID),
	CONSTRAINT S_Plane_Id FOREIGN KEY (Plane_Id) REFERENCES AIRPLANE(RegNo)

);


--DROP TABLE IF EXISTS OWNED_BY;
CREATE TABLE OWNED_BY (
	PlaneID			INT		NOT NULL,
	PDate			DATE,
	PersonSSN		CHAR(9),
	CorporationID	CHAR(7),
	PurchasedFrom	CHAR(7),

	CONSTRAINT O_PlaneID FOREIGN KEY (PlaneID) REFERENCES AIRPLANE(RegNo),
	CONSTRAINT O_PersonSSN FOREIGN KEY (PersonSSN) REFERENCES PERSON(SSN),
	CONSTRAINT O_CorporationID FOREIGN KEY (CorporationID) REFERENCES CORPORATION(ID),
	CONSTRAINT O_PurchasedFrom FOREIGN KEY (PurchasedFrom) REFERENCES CORPORATION(ID)

);


--DROP TABLE IF EXISTS MAINTAIN;
CREATE TABLE MAINTAIN (
	SSN			CHAR(9)		NOT NULL, 
	Service_Id	INT			NOT NULL,

	CONSTRAINT M_SSN FOREIGN KEY (SSN) REFERENCES EMPLOYEE(PSSN),
	CONSTRAINT M_Service_Id FOREIGN KEY (Service_Id) REFERENCES SERVICE(ID)

);


--DROP TABLE IF EXISTS FLIES;
CREATE TABLE FLIES (
	SSN			CHAR(9)		NOT NULL, 
	Model		VARCHAR(15)	NOT NULL,

	CONSTRAINT F_SSN FOREIGN KEY (SSN) REFERENCES PILOT(PSSN),
	CONSTRAINT F_Model FOREIGN KEY (Model) REFERENCES PLANE_TYPE(Model)

);


--DROP TABLE IF EXISTS WORKS_ON;
CREATE TABLE WORKS_ON (
	SSN			CHAR(9)		NOT NULL, 
	Model		VARCHAR(15)	NOT NULL,

	CONSTRAINT W_SSN FOREIGN KEY (SSN) REFERENCES EMPLOYEE(PSSN),
	CONSTRAINT W_Model FOREIGN KEY (Model) REFERENCES PLANE_TYPE(Model)

);