g2ctmT93

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;
57
58CREATE TABLE Weekday (
59DayOfWeek VARCHAR(30),
60Pricefactor DOUBLE,
61Year INTEGER,
62CONSTRAINT pk_weekday PRIMARY KEY(DayOfWeek, Year)); -- ENGINE=InnoDB;
63
64CREATE TABLE Year (
65Year INTEGER,
66Profitfactor DOUBLE,
67CONSTRAINT pk_year PRIMARY KEY(Year)); -- ENGINE=InnoDB;
68
69CREATE TABLE Flight (
70Flightnumber INTEGER,
71Week INTEGER,
72Weekly_Schedule INTEGER,
73freeseats INTEGER,
74CONSTRAINT pk_flight PRIMARY KEY(Flightnumber)); -- ENGINE=InnoDB;
75
76CREATE TABLE Ticket (
77Id INTEGER NOT NULL AUTO_INCREMENT,
78Passenger INTEGER,
79Booking INTEGER,
80CONSTRAINT pk_ticket PRIMARY KEY(Id)); -- ENGINE=InnoDB;
81
82CREATE TABLE Contact (
83Id INTEGER NOT NULL AUTO_INCREMENT,
84Phonenumber BIGINT,
85Email VARCHAR(30),
86CONSTRAINT pk_contact PRIMARY KEY(Id)); -- ENGINE=InnoDB;
87
88CREATE TABLE Passenger (
89Passportnumber INTEGER,
90Name VARCHAR(30),
91CONSTRAINT pk_passenger PRIMARY KEY(Passportnumber)); -- ENGINE=InnoDB;
92
93CREATE TABLE Payment (
94Id INTEGER NOT NULL AUTO_INCREMENT,
95Name VARCHAR(30),
96Creditcardnumber BIGINT,
97CONSTRAINT pk_payment PRIMARY KEY(Id)); -- ENGINE=InnoDB;
98
99CREATE TABLE Booking (
100Id INTEGER NOT NULL AUTO_INCREMENT,
101Payment INTEGER,
102Price DOUBLE,
103CONSTRAINT pk_booking PRIMARY KEY(Id)); -- ENGINE=InnoDB;
104
105CREATE TABLE Reservation (
106Reservationnumber INTEGER,
107Flightnumber INTEGER,
108Contact INTEGER,
109Seatsreserved Integer,
110CONSTRAINT pk_reservation PRIMARY KEY(Reservationnumber)); -- ENGINE=InnoDB;
111
112-- Add foreign keys 
113SELECT 'Creating foreign keys' AS 'Message';
114
115ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_route FOREIGN KEY (Route) 
116      REFERENCES Route(Id);
117
118ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_weekday FOREIGN KEY (DayOfWeek) 
119      REFERENCES Weekday(DayOfWeek);
120
121ALTER TABLE Weekly_Schedule ADD CONSTRAINT fk_ws_year FOREIGN KEY (Year) 
122      REFERENCES Year(Year);
123
124ALTER TABLE Route ADD CONSTRAINT fk_route_to_airport FOREIGN KEY (ToAirport) 
125      REFERENCES Airport(AirportCode);
126
127ALTER TABLE Route ADD CONSTRAINT fk_route_from_airport FOREIGN KEY (FromAirport)	
128      REFERENCES Airport(AirportCode);
129
130ALTER TABLE Route ADD CONSTRAINT fk_route_year FOREIGN KEY (Year) 
131      REFERENCES Year(Year);
132
133ALTER TABLE Weekday ADD CONSTRAINT fk_weekday_year FOREIGN KEY (Year)	
134      REFERENCES Year(Year);
135
136ALTER TABLE Flight ADD CONSTRAINT fk_flight_weekly_schedule FOREIGN KEY (Weekly_Schedule) 
137      REFERENCES Weekly_Schedule (Id);
138
139ALTER TABLE Ticket ADD CONSTRAINT fk_ticket_passenger FOREIGN KEY (Passenger)	
140      REFERENCES Passenger(Passportnumber);
141
142ALTER TABLE Ticket ADD CONSTRAINT fk_ticket_booking FOREIGN KEY (Booking) 
143      REFERENCES Booking(ID);
144
145ALTER TABLE Contact ADD CONSTRAINT fk_contact_id FOREIGN KEY (ID) 
146      REFERENCES Passenger(Passportnumber);
147
148ALTER TABLE Booking ADD CONSTRAINT fk_booking_payment FOREIGN KEY (Payment) 
149      REFERENCES Payment(ID);
150
151ALTER TABLE Booking ADD CONSTRAINT fk_booking_id FOREIGN KEY (Id) 
152      REFERENCES Reservation(Reservationnumber);
153
154ALTER TABLE Reservation ADD CONSTRAINT fk_rv_flight FOREIGN KEY (Flightnumber) 
155      REFERENCES Flight(Flightnumber);
156
157ALTER TABLE Reservation ADD CONSTRAINT fk_rv_contact FOREIGN KEY (Contact) 
158      REFERENCES Contact(ID);
159
160-- PROCEDURES AND FUNCTIONS
161SELECT 'procedure' AS 'Message';
162
163DROP PROCEDURE IF EXISTS addYear;
164DROP PROCEDURE IF EXISTS addDay;
165DROP PROCEDURE IF EXISTS addDestination;
166DROP PROCEDURE IF EXISTS addRoute;
167DROP PROCEDURE IF EXISTS addFlight;
168
169delimiter //
170CREATE PROCEDURE addYear(IN addyear INTEGER,IN addpf DOUBLE)
171BEGIN
172INSERT INTO Year(Year, Profitfactor) VALUES (addyear,addpf);
173END    
174//
175delimiter;
176
177delimiter //
178CREATE PROCEDURE addDay(IN addwd VARCHAR(30),IN addpf DOUBLE,IN addyear INTEGER)
179BEGIN
180INSERT INTO Weekday(DayOfWeek, Year, Pricefactor) VALUES (addwd,addpf,addyear);
181END    
182 //
183 delimiter;
184
185delimiter //
186CREATE PROCEDURE addDestination(IN addac VARCHAR(3),IN addan VARCHAR(30), IN addcou  VARCHAR(30))
187BEGIN
188INSERT INTO Airport(Airportcode, Airportname, Country) VALUES (addac, addan, addcou);
189END    
190//
191delimiter;
192
193delimiter //
194CREATE PROCEDURE addRoute(IN addta VARCHAR(3),IN addfa VARCHAR(3), IN addyear INTEGER, IN addpf DOUBLE)
195BEGIN
196INSERT INTO Route(ToAirport, FromAirport, Year, Pricefactor) VALUES (addta, addfa, addyear,addpf);
197END     
198//
199delimiter;
200
201-- delimiter //
202CREATE PROCEDURE addFlight(IN addyear INTEGER,IN addpf DOUBLE)
203BEGIN
204INSERT INTO Year(Year, Profitfactor) VALUES (addyear,addpf);
205END    
206-- //
207-- delimiter;