98qKjZjG

1-- Ola Fornander (olafo974) and Minhao Li (minli252).
2
3ALTER TABLE Weekly_Schedule DROP FOREIGN KEY fk_ws_route;
4ALTER TABLE Weekly_Schedule DROP FOREIGN KEY fk_ws_weekday;
5ALTER TABLE Weekly_Schedule DROP FOREIGN KEY fk_ws_year;
6ALTER TABLE Route DROP FOREIGN KEY fk_route_to_airport;
7ALTER TABLE Route DROP FOREIGN KEY fk_route_from_airport;
8ALTER TABLE Route DROP FOREIGN KEY fk_route_year;
9ALTER TABLE Weekday DROP FOREIGN KEY fk_weekday_year;
10ALTER TABLE Flight DROP FOREIGN KEY fk_flight_weekly_schedule;
11ALTER TABLE Ticket DROP FOREIGN KEY fk_ticket_passenger;
12ALTER TABLE Ticket DROP FOREIGN KEY fk_ticket_booking;
13ALTER TABLE Contact DROP FOREIGN KEY fk_contact_id;
14ALTER TABLE Booking DROP FOREIGN KEY fk_booking_payment;
15ALTER TABLE Booking DROP FOREIGN KEY fk_booking_id;
16ALTER TABLE Reservation DROP FOREIGN KEY fk_rv_flight;
17ALTER TABLE Reservation DROP FOREIGN KEY fk_rv_contact;
18
19SELECT 'Drop tables' AS 'Message';
20
21DROP TABLE IF EXISTS Weekly_Schedule;
22DROP TABLE IF EXISTS Route;
23DROP TABLE IF EXISTS Weekday;
24DROP TABLE IF EXISTS Flight;
25DROP TABLE IF EXISTS Ticket;
26DROP TABLE IF EXISTS Contact;
27DROP TABLE IF EXISTS Passenger;
28DROP TABLE IF EXISTS Payment;
29DROP TABLE IF EXISTS Booking;
30DROP TABLE IF EXISTS Reservation;
31DROP TABLE IF EXISTS Airport;
32DROP TABLE IF EXISTS Year;
33
34SELECT 'Creating tables' AS 'Message';
35
36CREATE TABLE Airport (
37AirportCode VARCHAR(3),
38AirportName VARCHAR(30),
39Country VARCHAR(30),
40CONSTRAINT pk_airport PRIMARY KEY(Airportcode)); -- ENGINE=InnoDB;
41
42CREATE TABLE Route (
43Id INTEGER NOT NULL AUTO_INCREMENT,
44ToAirport VARCHAR(3),
45FromAirport VARCHAR(3),
46Year INTEGER,
47Pricefactor DOUBLE,
48CONSTRAINT pk_route PRIMARY KEY(Id)); -- ENGINE=InnoDB;
49
50CREATE TABLE Weekly_Schedule (
51Id INTEGER NOT NULL AUTO_INCREMENT,
52Departure TIME,
53Route INTEGER,
54Year INTEGER,
55DayOfWeek VARCHAR(30),
56CONSTRAINT pk_weekly_schedule PRIMARY KEY(Id)); -- ENGINE=InnoDB;
57CREATE TABLE Weekday (
58DayOfWeek VARCHAR(30),
59Pricefactor DOUBLE,
60Year INTEGER,
61CONSTRAINT pk_weekday PRIMARY KEY(DayOfWeek, Year)); -- ENGINE=InnoDB;
62
63CREATE TABLE Year (
64Year INTEGER,
65Profitfactor DOUBLE,
66CONSTRAINT pk_year PRIMARY KEY(Year)); -- ENGINE=InnoDB;
67
68CREATE TABLE Flight (
69Flightnumber INTEGER,
70Week INTEGER,
71Weekly_Schedule INTEGER,
72freeseats INTEGER,
73CONSTRAINT pk_flight PRIMARY KEY(Flightnumber)); -- ENGINE=InnoDB;
74
75CREATE TABLE Ticket (
76Id INTEGER NOT NULL AUTO_INCREMENT,
77Passenger INTEGER,
78Booking INTEGER,
79CONSTRAINT pk_ticket PRIMARY KEY(Id)); -- ENGINE=InnoDB;
80
81CREATE TABLE Contact (
82Id INTEGER NOT NULL AUTO_INCREMENT,
83Phonenumber BIGINT,
84Email VARCHAR(30),
85CONSTRAINT pk_contact PRIMARY KEY(Id)); -- ENGINE=InnoDB;
86
87CREATE TABLE Passenger (
88Passportnumber INTEGER,
89Name VARCHAR(30),
90CONSTRAINT pk_passenger PRIMARY KEY(Passportnumber)); -- ENGINE=InnoDB;
91
92CREATE TABLE Payment (
93Id INTEGER NOT NULL AUTO_INCREMENT,
94Name VARCHAR(30),
95Creditcardnumber BIGINT,
96CONSTRAINT pk_payment PRIMARY KEY(Id)); -- ENGINE=InnoDB;
97
98CREATE TABLE Booking (
99Id INTEGER NOT NULL AUTO_INCREMENT,
100Payment INTEGER,
101Price DOUBLE,
102CONSTRAINT pk_booking PRIMARY KEY(Id)); -- ENGINE=InnoDB;
103
104CREATE TABLE Reservation (
105Reservationnumber INTEGER,
106Flightnumber INTEGER,
107Contact INTEGER,
108Seatsreserved Integer,
109CONSTRAINT pk_reservation PRIMARY KEY(Reservationnumber)); -- ENGINE=InnoDB;
110
111-- Add foreign keys 
112SELECT 'Creating foreign keys' AS 'Message';
113
114ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_route FOREIGN KEY (Route) 
115      REFERENCES Route(Id);
116
117ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_weekday FOREIGN KEY (DayOfWeek) 
118      REFERENCES Weekday(DayOfWeek);
119
120ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_year FOREIGN KEY (Year) 
121      REFERENCES Year(Year);
122
123ALTER TABLE Route ADD CONSTRAINT fk_route_to_airport FOREIGN KEY (ToAirport) 
124      REFERENCES Airport(AirportCode);
125
126ALTER TABLE Route ADD CONSTRAINT fk_route_from_airport FOREIGN KEY (FromAirport)	
127      REFERENCES Airport(AirportCode);
128
129ALTER TABLE Route ADD CONSTRAINT fk_route_year FOREIGN KEY (Year) 
130      REFERENCES Year(Year);
131
132ALTER TABLE Weekday ADD CONSTRAINT fk_weekday_year FOREIGN KEY (Year)	
133      REFERENCES Year(Year);
134
135ALTER TABLE Flight ADD CONSTRAINT fk_flight_weekly_schedule FOREIGN KEY (Weekly_Schedule) 
136      REFERENCES Weekly_Schedule (Id);
137
138ALTER TABLE Ticket ADD CONSTRAINT fk_ticket_passenger FOREIGN KEY (Passenger)	
139      REFERENCES Passenger(Passportnumber);
140
141ALTER TABLE Ticket ADD CONSTRAINT fk_ticket_booking FOREIGN KEY (Booking) 
142      REFERENCES Booking(ID);
143
144ALTER TABLE Contact ADD CONSTRAINT fk_contact_id FOREIGN KEY (ID) 
145      REFERENCES Passenger(Passportnumber);
146
147ALTER TABLE Booking ADD CONSTRAINT fk_booking_payment FOREIGN KEY (Payment) 
148      REFERENCES Payment(ID);
149
150ALTER TABLE Booking ADD CONSTRAINT fk_booking_id FOREIGN KEY (Id) 
151      REFERENCES Reservation(Reservationnumber);
152
153ALTER TABLE Reservation ADD CONSTRAINT fk_rv_flight FOREIGN KEY (Flightnumber) 
154      REFERENCES Flight(Flightnumber);
155
156ALTER TABLE Reservation ADD CONSTRAINT fk_rv_contact FOREIGN KEY (Contact) 
157      REFERENCES Contact(ID);
158
159-- PROCEDURES AND FUNCTIONS
160SELECT 'procedure' AS 'Message';
161
162DROP PROCEDURE IF EXISTS addYear;
163
164-- delimiter //
165CREATE PROCEDURE addYear(IN addyear INTEGER,IN addpf DOUBLE)
166BEGIN
167INSERT INTO Year(Year, Profitfactor) VALUES (addyear,addpf);
168END    
169-- //
170-- delimiter;