/* 
   Creates and empty Airport database
   No values are input to the table for this assignment
*/

-- if person table already exists, remove the table
  drop table if exists person;

-- if corporation table already exists, remove the table
  drop table if exists corporation;

-- if owner table already exists, remove the table
  drop table if exists owner;

-- if pilot table already exists, remove the table
  drop table if exists pilot;

-- if employee table already exists, remove the table
  drop table if exists employee;

-- if owns table already exists, remove the table
  drop table if exists owns;

-- if hangar table already exists, remove the table
  drop table if exists hangar;

-- if airplane table already exists, remove the table
  drop table if exists airplane;

-- if service table already exists, remove the table
  drop table if exists service;

-- if plane_type table already exists, remove the table
  drop table if exists plane_type;

-- if works_on table already exists, remove the table
  drop table if exists works_on;

-- if flies table already exists, remove the table
  drop table if exists flies;

-- if maintain table already exists, remove the table
  drop table if exists maintain;

-- create a new empty table plane_type
create table plane_type
( model     varchar(20) not null,
  capacity  int,
  weight    decimal(8, 2),
  constraint typepk
    primary key (model)
)

-- create a new empty table airplane
create table airplane
( reg#  int 	not null,
  pmod  varchar(20) not null,
  hang# int 	not null,
  constraint planepk
    primary key (reg#),
  constraint planemodfk
    foreign key (pmod) references plane_type(model),
  constraint planehangfk
    foreign key (hang#) references hangar(number)
)

-- create a new empty table hangar
create table hangar
( number int not null,
  capacity  int,
  location varchar(20),
  constraint hangarpk
    primary key (number)
)

-- create a new empty table corporation
create table corporation
( name varchar(20) not null,
  address varchar(30),
  phone char(10),
  constraint corppk
    primary key (name) 
)

-- create a new empty table person
create table person
( ssn char(9) not null,
  name varchar(20) not null,
  address varchar(30),
  phone char(10),
  constraint personpk
    primary key (ssn) 
)

-- create a new empty table owner
create table owner
( ssn char(9) not null,
  name varchar(20) not null,
  constraint ownerpersonpk
    primary key (ssn, name),
  constraint ownperssnfk
    foreign key (ssn) references person(ssn),
  constraint ownpernamefk
    foreign key (name) references person(name),
  constraint ownercorporationpk
    primary key (name, ssn),
  constraint owncorpnamefk
    foreign key (name) references corporation(name),
  constraint ownerpk			-- decides whether owner is dealing with a person or a company
    primary key select if(ssn = '000000000', ownerpersonpk, ownercorporationpk) from owner
)

-- create a new empty table owns
create table owns
( regno int not null,
  ownssn char(9) not null,
  name varchar(20) not null,
  pdate char(8),
  constraint ownspk
    primary key (regno, ownssn, name),
  constraint ownsregfk
    foreign key (regno) references airplane(reg#),
  constraint ownssnfk
    foreign key (ownssn) references owner(ssn),
  constraint ownnamefk
    foreign key (name) references owner(name)
)

-- create a new empty table employee
create table employee
( personssn char(9) not null,
  salary int,
  shift varchar(20),  
  constraint emppk
    primary key (pssn),
  constraint empfk
    foreign key (pssn) references person(ssn)
)

-- create a new empty table pilot
create table pilot
( perssn char(9) not null,
  lic_num varchar(15),
  restr   varchar(50),
  constraint pilotpk
    primary key (perssn),
  constraint pilotfk
    foreign key (perssn) references person(ssn)
)


-- create a new empty table service
create table service
( regnum int not null,
  date char(8) not null,
  workcode varchar(20) not null,
  hours int,
  constraint servicepk
    primary key (regnum, date, workcode),
  constraint service fk
    foreign key (regnum) references airplane(reg#)
)

-- create a new empty table works_on
create table works_on
( plnmodel varchar(20) not null,
  essn char(9) not null,
  constraint workpk
    primary key (plnmodel, essn),
  constraint workmodfk
    foreign key (plnmodel) references plane_type(model),
  constraint workssnfk
    foreign key (essn) references person(ssn)
)

-- create a new empty table flies
create table flies
( pmodel varchar(20) not null,
  pilotssn char(9) not null,
  constraint flypk
    primary key (pmodel, pilotssn),
  constraint flymodfk
    foreign key (pmodel) references plane_type(model),
  constraint flyssnfk
    foreign key (pilotssn) references person(ssn)
)

-- create a new empty table maintain
create table maintain
( empssn char(9) not null,
  regnumber int not null,
  constraint maintpk
    primary key (empssn, regnumber),
  constraint maintssnfk
    foreign key (empssn) references person(ssn),
  constraint maintregfk
    foreign key (regnumber) references airplane(reg#)
)
