r8N3MuAK

1DROP TABLE IF EXISTS Instructor_Quals;
2DROP TABLE IF EXISTS Sniv;
3DROP TABLE IF EXISTS Pair_List;
4DROP TABLE IF EXISTS Planes_Log;
5DROP TABLE IF EXISTS Planes_Down;
6DROP TABLE IF EXISTS Student_Flights;
7DROP TABLE IF EXISTS Schedule;
8DROP TABLE IF EXISTS Planes;
9DROP TABLE IF EXISTS Greenies;
10DROP TABLE IF EXISTS Course_List;
11DROP TABLE IF EXISTS Instructor;
12DROP TABLE IF EXISTS Midshipman;
13DROP TABLE IF EXISTS Contact_Information;
14
15CREATE TABLE Contact_Information (
16    Email varchar(40) NOT NULL,
17    Phone_Number varchar(30) NOT NULL,
18    Carrier varchar(30) NOT NULL,
19    constraint PK_Email primary key (Email)
20);
21
22CREATE TABLE Midshipman (
23    Last_Name varchar(30) NOT NULL,
24    First_Name varchar(30) NOT NULL,
25    Alpha int(6) NOT NULL,
26    Email varchar(40) NOT NULL UNIQUE,
27    License varchar(30) DEFAULT NULL,
28    Pilot_Status varchar(30) DEFAULT NULL,
29    Weight int(11) NOT NULL,
30    Wing1 varchar(30) NULL,
31    Wing2 varchar(30) NULL,
32    constraint FK_EmailMid foreign key (Email) references Contact_Information(Email) ON UPDATE CASCADE ON DELETE CASCADE
33);
34
35CREATE TABLE Pair_List (
36    Email1 varchar(40) NOT NULL UNIQUE,
37    Email2 varchar(40) NOT NULL UNIQUE,
38    constraint FK_EmailMid1 foreign key (Email1) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE,
39    constraint FK_EmailMid2 foreign key (Email2) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE
40);
41
42CREATE TABLE Sniv (
43    Email varchar(40) NOT NULL,
44    Sniv_Date DATE NOT NULL,
45    constraint FK_Sniv_Email foreign key (Email) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE
46);
47
48CREATE TABLE Instructor (
49    Last_Name varchar(30) NOT NULL,
50    First_Name varchar(30) NOT NULL,
51    Call_Sign varchar(30) NOT NULL,
52    Weight int(11) NOT NULL,
53    Wave_Pref varchar(5) NOT NULL,
54    Email varchar(40) NOT NULL UNIQUE,
55    Worker_Status varchar(10) NOT NULL,
56    constraint FK_EmailIns foreign key (Email) references Contact_Information(Email) ON UPDATE CASCADE ON DELETE CASCADE
57);
58
59CREATE TABLE Instructor_Quals (
60    Email varchar(40) NOT NULL,
61    Flight_Review_Date varchar(8) NOT NULL,
62    Flight_Hours int(6) NOT NULL,
63    Qualifications varchar(1000) NOT NULL,
64    constraint FK_InsQuals_Email foreign key (Email) references Instructor(Email) ON UPDATE CASCADE ON DELETE CASCADE
65);
66
67CREATE TABLE Planes (
68    Plane_Name varchar(30) NOT NULL,
69    Plane_Type varchar(15) NOT NULL,
70    Useful_load varchar(50) NOT NULL,
71    constraint PK_Name primary key (Plane_Name)
72);
73
74CREATE TABLE Planes_Log (
75    Plane_Name varchar(30) NOT NULL,
76    Status varchar(4) NOT NULL,
77    Hobbs_Time varchar(50) NOT NULL,
78    Tach_Time varchar(50) NOT NULL,
79    constraint FK_PlaneLog_PlaneName foreign key (Plane_Name) references Planes(Plane_Name) ON UPDATE CASCADE ON DELETE CASCADE
80);
81
82CREATE TABLE Planes_Down(
83    Plane_Name varchar(30) NOT NULL,
84    Status varchar(30) NOT NULL,
85    Out_Date_Start DATE NOT NULL,
86    Out_Date_End DATE NOT NULL,
87    constraint FK_PlanesDown_PlaneName foreign key (Plane_Name) references Planes(Plane_Name) ON UPDATE CASCADE ON DELETE CASCADE
88);
89
90CREATE TABLE Schedule (
91    FlightID INT NOT NULL AUTO_INCREMENT,
92    Date_of_Flight varchar(15) NOT NULL,
93    Plane_Name varchar(20) NOT NULL,
94    Student1_Email varchar(40) NULL,
95    Student1_Course varchar(10) NULL,
96    Student2_Email varchar(40) NULL,
97    Student2_Course varchar(10) NULL,
98    Ins_Email varchar(40) NOT NULL,
99    Wave int(1) NOT NULL,
100    constraint PK_FlightID primary key (FlightID),
101    constraint FK_Sched_InsEmail foreign key (Ins_Email) references Instructor(Email) ON UPDATE CASCADE ON DELETE CASCADE,
102    constraint FK_Sched_Stu1Email foreign key (Student1_Email) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE,
103    constraint FK_Sched_Stu2Email foreign key (Student2_Email) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE,
104    constraint FK_Schedule_PlaneName foreign key (Plane_Name) references Planes(Plane_Name) ON UPDATE CASCADE ON DELETE CASCADE
105);
106
107CREATE TABLE Course_List (
108    Course varchar(20) NOT NULL,
109    constraint PK_Course primary key (Course)
110);
111
112CREATE TABLE Greenies (
113    Course varchar(20) NOT NULL,
114    Email varchar(40) NOT NULL,
115    Date_of_Flight varchar(15) NOT NULL,
116    Grade varchar(1) NOT NULL,
117    Comments varchar(200) DEFAULT NULL,
118    constraint FK_Course foreign key (Course) references Course_List(Course),
119    constraint FK_Mid_Email foreign key (Email) references Midshipman(Email) ON UPDATE CASCADE ON DELETE CASCADE
120);
121
122insert into Course_List (Course) VALUES ("Course1");
123insert into Course_List (Course) VALUES ("Course2");
124insert into Course_List (Course) VALUES ("Course3");
125insert into Course_List (Course) VALUES ("Course4");
126insert into Course_List (Course) VALUES ("Course5");
127insert into Course_List (Course) VALUES ("Course6");
128insert into Course_List (Course) VALUES ("Course7");
129insert into Course_List (Course) VALUES ("Course8");
130insert into Course_List (Course) VALUES ("Course9");
131insert into Course_List (Course) VALUES ("Course10");
132insert into Course_List (Course) VALUES ("Course11");
133insert into Course_List (Course) VALUES ("Course12");
134insert into Course_List (Course) VALUES ("Course13");
135insert into Course_List (Course) VALUES ("Course14");