Логички и физички дизајн - Креирање база податоци: ddlscript-drop-create.sql

File ddlscript-drop-create.sql, 17.2 KB (added by 173067, 7 weeks ago)
Line 
1-- ==========================================
2-- Drop tables if they exist
3-- ==========================================
4DROP TABLE IF EXISTS SupportTicket CASCADE;
5DROP TABLE IF EXISTS Wishlist CASCADE;
6DROP TABLE IF EXISTS Notification CASCADE;
7DROP TABLE IF EXISTS Payment CASCADE;
8DROP TABLE IF EXISTS Review CASCADE;
9DROP TABLE IF EXISTS BookingFlight CASCADE;
10DROP TABLE IF EXISTS Booking CASCADE;
11DROP TABLE IF EXISTS Flight CASCADE;
12DROP TABLE IF EXISTS Airport CASCADE;
13DROP TABLE IF EXISTS Destination CASCADE;
14DROP TABLE IF EXISTS ApplicationUser CASCADE;
15DROP TABLE IF EXISTS Administrator CASCADE;
16
17-- ==========================================
18-- 1. Users
19-- ==========================================
20CREATE TABLE ApplicationUser (
21 UserID SERIAL PRIMARY KEY,
22 Name VARCHAR(100) NOT NULL,
23 Surname VARCHAR(100) NOT NULL,
24 Email VARCHAR(255) NOT NULL UNIQUE,
25 Password VARCHAR(255) NOT NULL,
26 PhoneNumber VARCHAR(50) UNIQUE,
27 DateJoined TIMESTAMP NOT NULL DEFAULT NOW()
28);
29
30-- ==========================================
31-- 2. Destinations
32-- ==========================================
33CREATE TABLE Destination (
34 DestinationID SERIAL PRIMARY KEY,
35 Name VARCHAR(255) NOT NULL,
36 Country VARCHAR(100) NOT NULL,
37 Description TEXT,
38 PopularAttraction TEXT,
39 BestTimeToVisit VARCHAR(50),
40 IsActive BOOLEAN NOT NULL DEFAULT TRUE
41);
42
43-- ==========================================
44-- 3. Airports
45-- ==========================================
46CREATE TABLE Airport (
47 AirportID SERIAL PRIMARY KEY,
48 Name VARCHAR(255) NOT NULL,
49 Country VARCHAR(100) NOT NULL,
50 Code VARCHAR(10) UNIQUE NOT NULL,
51 DestinationID INT NOT NULL,
52 CONSTRAINT fk_airport_destination FOREIGN KEY (DestinationID)
53 REFERENCES Destination(DestinationID)
54);
55
56-- ==========================================
57-- 4. Flights
58-- ==========================================
59CREATE TABLE Flight (
60 FlightID SERIAL PRIMARY KEY,
61 FlightNumber VARCHAR(50) NOT NULL,
62 DepartureAirportID INT NOT NULL,
63 ArrivalAirportID INT NOT NULL,
64 DepartureDate DATE NOT NULL,
65 ReturnDate DATE NOT NULL,
66 Price DECIMAL(10,2),
67 AvailableSeats INT NOT NULL,
68 CONSTRAINT fk_flight_dep FOREIGN KEY (DepartureAirportID) REFERENCES Airport(AirportID),
69 CONSTRAINT fk_flight_arr FOREIGN KEY (ArrivalAirportID) REFERENCES Airport(AirportID)
70);
71
72-- ==========================================
73-- 5. Bookings
74-- ==========================================
75CREATE TABLE Booking (
76 BookingID SERIAL PRIMARY KEY,
77 UserID INT NOT NULL,
78 BookingDate TIMESTAMP NOT NULL DEFAULT NOW(),
79 PaymentStatus VARCHAR(20) CHECK (PaymentStatus IN ('Processing','Failed','Success')),
80 TotalCost DECIMAL(10,2),
81 CONSTRAINT fk_booking_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
82);
83
84-- ==========================================
85-- 6. BookingFlight (M:N)
86-- ==========================================
87CREATE TABLE BookingFlight (
88 BookingID INT NOT NULL,
89 FlightID INT NOT NULL,
90 SeatNumber INT,
91 PRIMARY KEY (BookingID, FlightID),
92 CONSTRAINT fk_bf_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
93 CONSTRAINT fk_bf_flight FOREIGN KEY (FlightID) REFERENCES Flight(FlightID)
94);
95
96-- ==========================================
97-- 7. Reviews
98-- ==========================================
99CREATE TABLE Review (
100 ReviewID SERIAL PRIMARY KEY,
101 UserID INT NOT NULL,
102 BookingID INT NOT NULL,
103 TargetID INT NOT NULL,
104 TargetType VARCHAR(20) CHECK (TargetType IN ('Flight','Destination','Airport')),
105 ReviewComment TEXT,
106 Rating INT,
107 Date TIMESTAMP NOT NULL DEFAULT NOW(),
108 CONSTRAINT fk_review_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
109 CONSTRAINT fk_review_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID)
110);
111
112-- ==========================================
113-- 8. Payments
114-- ==========================================
115CREATE TABLE Payment (
116 PaymentID SERIAL PRIMARY KEY,
117 BookingID INT NOT NULL,
118 UserID INT NOT NULL,
119 PaymentMethod VARCHAR(20) CHECK (PaymentMethod IN ('Credit','Debit','PayPal')),
120 Amount DECIMAL(10,2),
121 TransactionDate TIMESTAMP NOT NULL DEFAULT NOW(),
122 PaymentStatus VARCHAR(20) CHECK (PaymentStatus IN ('Success','Failed','Processing')),
123 CONSTRAINT fk_payment_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
124 CONSTRAINT fk_payment_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
125);
126
127-- ==========================================
128-- 9. Notifications
129-- ==========================================
130CREATE TABLE Notification (
131 NotificationID SERIAL PRIMARY KEY,
132 UserID INT NOT NULL,
133 Message TEXT NOT NULL,
134 Type VARCHAR(50) CHECK (Type IN ('BookingConfirmation','FlightDelay','GeneralUpdate')),
135 DateSent TIMESTAMP NOT NULL DEFAULT NOW(),
136 CONSTRAINT fk_notification_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
137);
138
139-- ==========================================
140-- 10. Wishlist
141-- ==========================================
142CREATE TABLE Wishlist (
143 WishlistID SERIAL PRIMARY KEY,
144 UserID INT NOT NULL,
145 TargetID INT NOT NULL,
146 TargetType VARCHAR(20) CHECK (TargetType IN ('Flight','Destination')),
147 DateAdded TIMESTAMP NOT NULL DEFAULT NOW(),
148 CONSTRAINT fk_wishlist_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
149 CONSTRAINT uq_wishlist UNIQUE (UserID, TargetID)
150);
151
152-- ==========================================
153-- 11. Administrators
154-- ==========================================
155CREATE TABLE Administrator (
156 AdminID SERIAL PRIMARY KEY,
157 Email VARCHAR(255) NOT NULL UNIQUE
158);
159
160-- ==========================================
161-- 12. Support Tickets
162-- ==========================================
163CREATE TABLE SupportTicket (
164 TicketID SERIAL PRIMARY KEY,
165 UserID INT NOT NULL,
166 Subject VARCHAR(255) NOT NULL,
167 Description TEXT NOT NULL,
168 Status VARCHAR(20) CHECK (Status IN ('Open','InProgress','Resolved')),
169 DateCreated TIMESTAMP NOT NULL DEFAULT NOW(),
170 DateResolved TIMESTAMP,
171 AssignedTo INT,
172 CONSTRAINT fk_ticket_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
173 CONSTRAINT fk_ticket_admin FOREIGN KEY (AssignedTo) REFERENCES Administrator(AdminID)
174);
175
176
177-- Indexes
178CREATE INDEX idx_booking_user ON Booking(UserID);
179CREATE INDEX idx_flight_departuredate ON Flight(DepartureDate);
180CREATE INDEX idx_payment_booking ON Payment(BookingID);
181CREATE INDEX idx_review_user ON Review(UserID);
182CREATE INDEX idx_review_target_type_id ON Review(TargetType, TargetID);
183
184-- Trigger: Deactivate destination on negative review
185CREATE OR REPLACE FUNCTION trg_after_insert_review_negative_dest()
186 RETURNS TRIGGER AS $$
187BEGIN
188 IF NEW.TargetType = 'Destination' AND NEW.Rating IS NOT NULL AND NEW.Rating < 3 THEN
189 UPDATE Destination
190 SET IsActive = FALSE
191 WHERE DestinationID = NEW.TargetID;
192 END IF;
193 RETURN NEW;
194END;
195$$ LANGUAGE plpgsql;
196
197DROP TRIGGER IF EXISTS trg_review_after_insert ON Review;
198CREATE TRIGGER trg_review_after_insert
199 AFTER INSERT ON Review
200 FOR EACH ROW
201EXECUTE FUNCTION trg_after_insert_review_negative_dest();
202
203-- Trigger: Restore seats on deletion of BookingFlight
204CREATE OR REPLACE FUNCTION trg_bookingflight_after_delete_restore_seat()
205 RETURNS TRIGGER AS $$
206BEGIN
207 UPDATE Flight
208 SET AvailableSeats = AvailableSeats + 1
209 WHERE FlightID = OLD.FlightID;
210 RETURN OLD;
211END;
212$$ LANGUAGE plpgsql;
213
214DROP TRIGGER IF EXISTS trg_bf_after_delete ON BookingFlight;
215CREATE TRIGGER trg_bf_after_delete
216 AFTER DELETE ON BookingFlight
217 FOR EACH ROW
218EXECUTE FUNCTION trg_bookingflight_after_delete_restore_seat();
219
220-- Function: average rating for destination
221CREATE OR REPLACE FUNCTION avg_rating_for_destination(dest_id INT)
222 RETURNS NUMERIC AS $$
223DECLARE
224 res NUMERIC;
225BEGIN
226 SELECT AVG(r.Rating)::NUMERIC INTO res
227 FROM Review r
228 WHERE r.TargetType = 'Destination' AND r.TargetID = dest_id;
229 RETURN COALESCE(res, 0);
230END;
231$$ LANGUAGE plpgsql;
232
233-- Procedure: create booking transactionally
234CREATE OR REPLACE PROCEDURE create_booking(
235 p_user_id INT,
236 p_flight_ids INT[],
237 p_seat_numbers INT[],
238 p_total_cost DECIMAL(10,2),
239 p_payment_method VARCHAR
240)
241LANGUAGE plpgsql
242AS $$
243DECLARE
244 v_booking_id INT;
245 v_flight_id INT;
246 v_seat_number INT;
247 i INT;
248 v_method VARCHAR(20);
249BEGIN
250 BEGIN
251 -- Validate payment method
252 v_method := INITCAP(p_payment_method); -- Capitalizes first letter (Credit, Debit, PayPal)
253 IF v_method NOT IN ('Credit','Debit','PayPal') THEN
254 RAISE EXCEPTION 'Invalid payment method: %', p_payment_method;
255 END IF;
256
257 -- Create Booking
258 INSERT INTO Booking(UserID, BookingDate, PaymentStatus, TotalCost)
259 VALUES (p_user_id, NOW(), 'Processing', p_total_cost)
260 RETURNING BookingID INTO v_booking_id;
261
262 -- Reserve seats
263 FOR i IN array_lower(p_flight_ids,1)..array_upper(p_flight_ids,1) LOOP
264 v_flight_id := p_flight_ids[i];
265 v_seat_number := p_seat_numbers[i];
266
267 IF (SELECT AvailableSeats FROM Flight WHERE FlightID = v_flight_id FOR UPDATE) <= 0 THEN
268 RAISE EXCEPTION 'No seats available for flight %', v_flight_id;
269 END IF;
270
271 INSERT INTO BookingFlight(BookingID, FlightID, SeatNumber)
272 VALUES (v_booking_id, v_flight_id, v_seat_number);
273
274 UPDATE Flight
275 SET AvailableSeats = AvailableSeats - 1
276 WHERE FlightID = v_flight_id;
277 END LOOP;
278
279 -- Create Payment
280 INSERT INTO Payment(BookingID, UserID, PaymentMethod, Amount, TransactionDate, PaymentStatus)
281 VALUES (v_booking_id, p_user_id, v_method, p_total_cost, NOW(), 'Processing');
282
283 COMMIT;
284 EXCEPTION
285 WHEN OTHERS THEN
286 ROLLBACK;
287 RAISE;
288 END;
289END;
290$$;
291
292
293-- Trigger: Update Booking.PaymentStatus after Payment
294CREATE OR REPLACE FUNCTION trg_update_booking_payment_status()
295 RETURNS TRIGGER AS $$
296BEGIN
297 IF NEW.PaymentStatus = 'Success' THEN
298 UPDATE Booking
299 SET PaymentStatus = 'Completed'
300 WHERE BookingID = NEW.BookingID;
301 ELSIF NEW.PaymentStatus = 'Failed' THEN
302 UPDATE Booking
303 SET PaymentStatus = 'Cancelled'
304 WHERE BookingID = NEW.BookingID;
305 END IF;
306 RETURN NEW;
307END;
308$$ LANGUAGE plpgsql;
309
310DROP TRIGGER IF EXISTS trg_payment_after_update ON Payment;
311CREATE TRIGGER trg_payment_after_update
312 AFTER UPDATE OF PaymentStatus ON Payment
313 FOR EACH ROW
314EXECUTE FUNCTION trg_update_booking_payment_status();
315
316-- Views
317CREATE OR REPLACE VIEW v_user_bookings AS
318SELECT
319 u.UserID,
320 u.Name || ' ' || u.Surname AS FullName,
321 b.BookingID,
322 b.BookingDate,
323 b.PaymentStatus,
324 b.TotalCost,
325 f.FlightID,
326 f.FlightNumber,
327 f.DepartureDate,
328 f.ReturnDate,
329 dep.Name AS DepartureAirport,
330 arr.Name AS ArrivalAirport,
331 f.Price
332FROM Booking b
333 JOIN ApplicationUser u ON b.UserID = u.UserID
334 JOIN BookingFlight bf ON b.BookingID = bf.BookingID
335 JOIN Flight f ON bf.FlightID = f.FlightID
336 JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
337 JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID;
338
339CREATE OR REPLACE VIEW v_flight_summary AS
340SELECT
341 f.FlightID,
342 f.FlightNumber,
343 dep.Name AS DepartureAirport,
344 arr.Name AS ArrivalAirport,
345 f.DepartureDate,
346 f.ReturnDate,
347 f.Price,
348 f.AvailableSeats,
349 COUNT(bf.BookingID) AS TotalBookings
350FROM Flight f
351 JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
352 JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID
353 LEFT JOIN BookingFlight bf ON f.FlightID = bf.FlightID
354GROUP BY f.FlightID, f.FlightNumber, dep.Name, arr.Name, f.DepartureDate, f.ReturnDate, f.FlightNumber, dep.Name, arr.Name, f.DepartureDate, f.FlightID, f.Price, f.AvailableSeats, f.Price, f.AvailableSeats;
355
356CREATE OR REPLACE VIEW v_reviews AS
357SELECT
358 r.ReviewID,
359 u.Name || ' ' || u.Surname AS Reviewer,
360 r.TargetID,
361 r.TargetType,
362 r.ReviewComment,
363 r.Rating,
364 r.Date
365FROM Review r
366 JOIN ApplicationUser u ON r.UserID = u.UserID;
367
368CREATE OR REPLACE VIEW v_user_payments AS
369SELECT
370 u.UserID,
371 u.Name || ' ' || u.Surname AS FullName,
372 COUNT(p.PaymentID) AS NumPayments,
373 SUM(p.Amount) AS TotalPaid,
374 AVG(p.Amount) AS AvgPayment
375FROM Payment p
376 JOIN ApplicationUser u ON p.UserID = u.UserID
377GROUP BY u.UserID, u.Name, u.Surname;
378
379CREATE OR REPLACE VIEW v_user_wishlist AS
380SELECT
381 u.UserID,
382 u.Name || ' ' || u.Surname AS FullName,
383 w.WishlistID,
384 w.TargetID,
385 w.TargetType,
386 w.DateAdded
387FROM Wishlist w
388 JOIN ApplicationUser u ON w.UserID = u.UserID;
389
390CREATE OR REPLACE VIEW v_best_value_flights AS
391SELECT
392 f.FlightID,
393 f.FlightNumber,
394 f.Price,
395 COALESCE(AVG(r.Rating), 0) AS AvgRating
396FROM Flight f
397 LEFT JOIN Review r ON r.TargetType = 'Flight' AND r.TargetID = f.FlightID
398GROUP BY f.FlightID, f.FlightNumber, f.Price
399HAVING COALESCE(AVG(r.Rating),0) > 4 AND f.Price < 200;
400
401CREATE OR REPLACE VIEW TopMonthlyReport AS
402WITH MonthlyBookings AS (
403 SELECT
404 DATE_TRUNC('month', b.BookingDate) AS Month,
405 d.Name AS DestinationName,
406 COUNT(b.BookingID) AS BookingsCount,
407 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
408 ORDER BY COUNT(b.BookingID) DESC) AS Rank
409 FROM Booking b
410 JOIN BookingFlight bf ON b.BookingID = bf.BookingID
411 JOIN Flight f ON bf.FlightID = f.FlightID
412 JOIN Airport a ON f.ArrivalAirportID = a.AirportID
413 JOIN Destination d ON a.DestinationID = d.DestinationID
414 GROUP BY Month, d.Name
415),
416MonthlyFlights AS (
417 SELECT
418 DATE_TRUNC('month', b.BookingDate) AS Month,
419 f.FlightNumber,
420 COUNT(b.BookingID) AS BookingsCount,
421 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
422 ORDER BY COUNT(b.BookingID) DESC) AS Rank
423 FROM Booking b
424 JOIN BookingFlight bf ON b.BookingID = bf.BookingID
425 JOIN Flight f ON bf.FlightID = f.FlightID
426 GROUP BY Month, f.FlightNumber
427),
428MonthlyAirports AS (
429 SELECT
430 DATE_TRUNC('month', b.BookingDate) AS Month,
431 a.Name AS AirportName,
432 COUNT(b.BookingID) AS DeparturesCount,
433 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
434 ORDER BY COUNT(b.BookingID) DESC) AS Rank
435 FROM Booking b
436 JOIN BookingFlight bf ON b.BookingID = bf.BookingID
437 JOIN Flight f ON bf.FlightID = f.FlightID
438 JOIN Airport a ON f.DepartureAirportID = a.AirportID
439 GROUP BY Month, a.Name
440)
441SELECT Month, 'Destination' AS Category, DestinationName AS Name, BookingsCount AS Count
442FROM MonthlyBookings WHERE Rank <= 3
443UNION ALL
444SELECT Month, 'Flight' AS Category, FlightNumber AS Name, BookingsCount AS Count
445FROM MonthlyFlights WHERE Rank <= 3
446UNION ALL
447SELECT Month, 'Airport' AS Category, AirportName AS Name, DeparturesCount AS Count
448FROM MonthlyAirports WHERE Rank <= 3
449ORDER BY Month, Category, Count DESC;
450