caumGV33

1DROP DATABASE IF EXISTS ski;
2CREATE DATABASE IF NOT EXISTS ski;
3USE ski;
4
5CREATE TABLE IF NOT EXISTS difficulties(
6	difficulty_id INT AUTO_INCREMENT PRIMARY KEY,
7    rating VARCHAR(15) NOT NULL UNIQUE
8);
9
10INSERT INTO difficulties(rating)
11VALUES('Green'),('Blue'),('Red'),('Black');
12
13CREATE TABLE IF NOT EXISTS lift_types(
14	lift_type_id INT AUTO_INCREMENT PRIMARY KEY,
15    type VARCHAR(45) NOT NULL UNIQUE
16);
17
18INSERT INTO lift_types(type)
19VALUES('Cabin'),('Seat'),('Drag');
20
21CREATE TABLE IF NOT EXISTS lifts(
22	lift_id INT AUTO_INCREMENT PRIMARY KEY,
23    name VARCHAR(45) NOT NULL,
24	lift_type_id INT NOT NULL,
25    CONSTRAINT fk_lift_lift_type FOREIGN KEY (lift_type_id) REFERENCES lift_types(lift_type_id),
26    length DOUBLE(3,2) NOT NULL,
27    price INT NOT NULL DEFAULT 45,
28    status BIT DEFAULT 0,
29    capacity INT NOT NULL DEFAULT 5
30);
31
32CREATE TABLE IF NOT EXISTS slopes(
33	slope_id INT AUTO_INCREMENT PRIMARY KEY,
34    name VARCHAR(45) NOT NULL,
35    length INT NOT NULL,
36    snow_thickness DOUBLE(5,2) NOT NULL DEFAULT 0,
37    altitude DOUBLE NOT NULL,
38    difficulty_id INT NOT NULL,
39    CONSTRAINT fk_slope_difficuty FOREIGN KEY (difficulty_id) REFERENCES difficulties(difficulty_id),
40    lift_id INT NOT NULL UNIQUE,
41    CONSTRAINT fk_slope_lift FOREIGN KEY (lift_id) REFERENCES lifts(lift_id)
42);
43
44CREATE TABLE IF NOT EXISTS ride_types(
45	ride_type_id INT AUTO_INCREMENT PRIMARY KEY,
46    ride_type VARCHAR(45) NOT NULL UNIQUE
47);
48
49INSERT INTO ride_types(ride_type)
50VALUES('Morning'),('Afternoon'),('Night');
51
52CREATE TABLE IF NOT EXISTS card_types(
53	card_type_id INT AUTO_INCREMENT PRIMARY KEY,
54    card_type VARCHAR(45) NOT NULL UNIQUE
55);
56
57INSERT INTO card_types(card_type)
58VALUES('Child'),('Student'),('Normal');
59
60CREATE TABLE IF NOT EXISTS ski_cards(
61	ski_card_id INT AUTO_INCREMENT PRIMARY KEY,
62    ride_type_id INT NOT NULL,
63    CONSTRAINT fk_skicard_ridetype FOREIGN KEY (ride_type_id) REFERENCES ride_types(ride_type_id),
64    price INT NOT NULL DEFAULT 15,
65    card_type_id INT NOT NULL,
66    CONSTRAINT fk_skicard_cardtype FOREIGN KEY (card_type_id) REFERENCES card_types(card_type_id)
67);
68
69CREATE TABLE IF NOT EXISTS cards_lifts(
70	ski_card_id INT NOT NULL,
71    CONSTRAINT fk_cards_lifts_skicards FOREIGN KEY (ski_card_id) REFERENCES ski_cards(ski_card_id),
72    lift_id INT NOT NULL,
73    CONSTRAINT fk_cards_lifts_lifts FOREIGN KEY (lift_id) REFERENCES lifts(lift_id),
74    PRIMARY KEY (ski_card_id, lift_id)
75);
76
77INSERT INTO lifts(name, lift_type_id, length, price, status, capacity)
78VALUES('Bansko', 1, 4.30, 45, 1, 6),
79('Mosta', 2, 2.50, 25, 1, 4),
80('Shiligarnic', 2, 2, 25, 1, 4),
81('Challin Vollog', 2, 2, 25, 1, 4),
82('Detska Kotva', 3, 1, 15, 1, 1),
83('Vlak Chalin', 3, 2, 25, 0, 1),
84('Alaska 8', 1, 4, 45, 1, 5),
85('Todorka', 2, 4.50, 45, 1, 6);
86      
87INSERT INTO slopes(name, length, snow_thickness, altitude, difficulty_id, lift_id)
88VALUES('Strajite', 3000, 120.50, 1529, 3, 1),  
89('Balkaniada', 2600, 112, 1467, 3, 7),
90('Shiligarnik 1', 1950, 125.5, 1981, 2, 2),
91('Tomba', 4200, 140.50, 2221, 4, 6),
92('Rukav na Tomba', 4100, 137, 2211, 4, 3),
93('Ski path 1', 891, 86.50, 1305, 1, 4),
94('Julen', 2065, 103.75, 1640, 3, 5);
95
96INSERT INTO ski_cards(ride_type_id, price, card_type_id)
97VALUES(1, 25, 1),
98(1, 25, 2),
99(1, 25, 3),
100(2, 50, 1),
101(2, 50, 2),
102(2, 50, 3),
103(3, 75, 2),
104(3, 75, 3);
105
106INSERT INTO cards_lifts(ski_card_id, lift_id)
107VALUES(1, 7),
108(2, 6),
109(3, 5),
110(4, 4),
111(5, 3),
112(6, 2),
113(7, 1),
114(8, 2),
115(7, 3),
116(6, 4),
117(5, 5),
118(4, 6),
119(3, 7);