CREATE TABLE airplane
( RegNumber	varchar(10) NOT NULL,
  constraint reg_pk
	primary key (RegNumber)
);

CREATE TABLE corporation
( Name		varchar(50) NOT NULL,
  Address	varchar(50),
  Phone		varchar(10),
  constraint name_pk
	primary key (Name)
);

CREATE TABLE person
( ssn		varchar(10) NOT NULL,
  Name		varchar(50),
  Address	varchar(50),
  Phone		varchar(10),
  constraint ssn_pk
	primary key (ssn)
);

CREATE TABLE hangar
( HNumber	varchar(10) NOT NULL,
  Capacity	varchar(10),
  Location	varchar(10),
  constraint  number_pk
	primary key (HNumber)
);

CREATE TABLE plane_type
( Model		varchar(10) NOT NULL,
  Capacity	varchar(10),
  Weight	varchar(10),
  constraint plane_type_pk  
	primary key (Model)
);

CREATE TABLE Employee
( Essn		varchar(10) NOT NULL,
  Salary	decimal(18, 2),
  Shift		varchar(10),
  constraint employee_pk
	primary key (Essn),
  constraint employee_fk
	foreign key (Essn) references person(ssn)
);

CREATE TABLE Pilot
( Pssn		varchar(10) NOT NULL,
  Lic_Num	varchar(10),
  Restr		varchar(10),
  constraint pilot_pk
	primary key (Pssn),
  constraint pilot_fk
	foreign key (Pssn) references person(ssn)
);

CREATE TABLE service
( RegNum	varchar(10) NOT NULL,
  SDate		varchar(10) NOT NULL,
  Workcode	varchar(10) NOT NULL,
  Hours		varchar(10),
  constraint service_pk  
	primary key (RegNum, SDate, Workcode),
  constraint service_fk
	foreign key (RegNum) references airplane(RegNumber)
);

CREATE TABLE owner
( OName		varchar(50) NOT NULL,
  Ossn		varchar(10) NOT NULL,
  IsCorp	boolean,
  IsPerson	boolean,
  constraint Ossn_fk
  	foreign key (IF IsPerson = TRUE THEN Ossn END IF) REFERENCES person(ssn),
  constraint OName_fk
 	foreign key (IF IsCorp = TRUE THEN OName END IF) REFERENCES corporation(Name)
);

CREATE TABLE works_on
( Modelnum	varchar(10) NOT NULL,
  Empssn	varchar(10) NOT NULL,
  constraint work_pk
	primary key (Modelnum, Empssn),
  constraint work_fk1
	foreign key (Modelnum) references plane_type(Model),
  constraint work_fk2
	foreign key (Empssn) references employee(Essn)
);

CREATE TABLE flies
( Modelnum	varchar(10) NOT NULL,
  Pilotssn	varchar(10) NOT NULL,
  constraint flies_pk
	primary key (Modelnum, Pilotssn),
  constraint flies_fk1
	foreign key (Modelnum) references plane_type(Model),
  constraint flies_fk2
	foreign key (Pilotssn) references pilot(Pssn)
);

CREATE TABLE maintain
( Empssn	varchar(10) NOT NULL,
  MDate		varchar(10) NOT NULL,
  MWorkcode	varchar(10) NOT NULL,
  MRegnum	varchar(10) NOT NULL,
  constraint maint_pk
	primary key (Empssn, MRegnum),
  constraint maint_fk1
	foreign key (Empssn) references employee(ssn),
  constraint maint_fk2
	foreign key (MDate) references service(SDate),
  constraint maint_fk3
	foreign key (MWorkcode) references service(Workcode),
  constraint maint_fk4
	foreign key (MRegnum) references service(RegNum)
);

CREATE TABLE owns
( Pdate		varchar(10) NOT NULL,
  CName		varchar(50) NOT NULL,
  Pssn		varchar(10) NOT NULL,
  Regnum	varchar(10) NOT NULL,
  constraint owns_pk
	primary key (Regnum, CName, Pssn),
  constraint owns_fk1
	foreign key (Regnum) references airplane(RegNumber),
  constraint owns_fk2
	foreign key (Pssn) references owner(Ossn),
  constraint owns_fk3
	foreign key (CName) references owner(OName)
);

