· 7 years ago · Dec 18, 2018, 02:06 PM
1--DDL
2
3-- (1) DROPS
4
5DROP VIEW IF EXISTS liban_artifacts;
6DROP VIEW IF EXISTS student_staff;
7DROP TABLE IF EXISTS book_loan;
8DROP TABLE IF EXISTS slide_loan;
9DROP TABLE IF EXISTS slides;
10DROP TABLE IF EXISTS books;
11DROP TABLE IF EXISTS book_shelf;
12DROP TABLE IF EXISTS slide_shelf;
13DROP TABLE IF EXISTS artifact_card;
14DROP TABLE IF EXISTS location_segment;
15DROP TABLE IF EXISTS dig;
16DROP TABLE IF EXISTS artifact;
17DROP TABLE IF EXISTS staff;
18
19-- (2) CREATE TABLES
20
21CREATE TABLE staff(
22 PRIMARY KEY(staff_ID),
23 staff_ID VARCHAR(8),
24 staff_role VARCHAR(20) NOT NULL,
25 staff_name VARCHAR(30) NOT NULL,
26 date_registered VARCHAR(10) NOT NULL,
27);
28
29CREATE TABLE artifact(
30 PRIMARY KEY(number, grid, depth),
31 number INT,
32 grid INT,
33 depth INT,
34 art_owner VARCHAR(8),
35 art_date INT,
36 FOREIGN KEY (art_owner)
37 REFERENCES staff(staff_ID)
38);
39
40CREATE TABLE dig(
41 dig_name VARCHAR(50) NOT NULL,
42 dig_no INT,
43 dig_date VARCHAR(10),
44 worker VARCHAR(8),
45 PRIMARY KEY(dig_name, dig_no),
46 FOREIGN KEY (worker)
47 REFERENCES staff(staff_ID)
48);
49
50CREATE TABLE location_segment(
51 location_ID VARCHAR(8),
52 PRIMARY KEY(location_ID)
53);
54
55
56CREATE TABLE artifact_card(
57 Notes VARCHAR(140),
58 card_ID INT,
59 current_pos VARCHAR(8),
60 dig_origin_no INT,
61 dig_origin_name VARCHAR(50),
62 dig_grid INT,
63 dig_depth INT,
64 FOREIGN KEY (card_ID, dig_grid, dig_depth)
65 REFERENCES artifact(number, grid, depth),
66 FOREIGN KEY (current_pos)
67 REFERENCES location_segment(location_ID),
68 FOREIGN KEY (dig_origin_name, dig_origin_no)
69 REFERENCES dig(dig_name, dig_no),
70
71);
72
73CREATE TABLE slide_shelf(
74 s_shelf_number VARCHAR(5),
75 PRIMARY KEY (s_shelf_number)
76);
77
78CREATE TABLE book_shelf(
79 b_shelf_number VARCHAR(5),
80 PRIMARY KEY (b_shelf_number)
81);
82
83
84CREATE TABLE books(
85 book_number VARCHAR(6),
86 title VARCHAR(30) NOT NULL,
87 author VARCHAR(30) NOT NULL,
88 home_shelf VARCHAR(5),
89 PRIMARY KEY(book_number),
90 FOREIGN KEY (home_shelf)
91 REFERENCES book_shelf(b_shelf_number)
92
93);
94
95CREATE TABLE slides(
96 slide_number VARCHAR(6),
97 title VARCHAR(30) NOT NULL,
98 author VARCHAR(30),
99 slide_category VARCHAR(30),
100 home_shelf VARCHAR(5),
101 PRIMARY KEY(slide_number),
102 FOREIGN KEY (home_shelf)
103 REFERENCES slide_shelf(s_shelf_number)
104);
105
106CREATE TABLE slide_loan(
107 start_date_loan_s INT,
108 end_date_loan_s INT,
109 slide_borrower VARCHAR(8),
110 slide_ID VARCHAR(6),
111 PRIMARY KEY(start_date_loan_s),
112 FOREIGN KEY (slide_borrower)
113 REFERENCES staff(staff_ID),
114 FOREIGN KEY (slide_ID)
115 REFERENCES slides(slide_number),
116);
117
118CREATE TABLE book_loan(
119
120 start_date_loan INT,
121 end_date_loan INT,
122 book_borrower VARCHAR(8),
123 book_ID VARCHAR(6),
124 PRIMARY KEY(start_date_loan),
125 FOREIGN KEY (book_borrower)
126 REFERENCES staff(staff_ID),
127 FOREIGN KEY (book_ID)
128 REFERENCES books(book_number)
129);
130
131
132-- (3) INSERT VALUES
133
134INSERT INTO slide_shelf(s_shelf_number)
135VALUES('O351'),
136 ('C123'),
137 ('K362'),
138 ('I994'),
139 ('Q122'),
140 ('P125'),
141 ('Y156');
142
143INSERT INTO book_shelf(b_shelf_number)
144VALUES ('W126'),
145 ('J563'),
146 ('U898'),
147 ('H759'),
148 ('O340'),
149 ('L998'),
150 ('E127'),
151 ('H784');
152
153
154
155
156
157INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
158VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
159 ('vserf495', 'Excavator', 'Vsera Rfoman', 20080905),
160 ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
161 ('babol666', 'Excavator', 'Barbro Olev', 19970706),
162 ('Jndio123', 'Excavator', 'Jndiana Iones', 19420713),
163 ('johdo321', 'Student', 'John Doe', 19650621),
164 ('namna146', 'Student', 'Name Nameson', 19990214),
165 ('bombe534', 'Professor', 'Bom Betty', 1986042);
166
167INSERT INTO slides(slide_number, title, author, slide_category, home_shelf)
168VALUES('23-001', '15-century Swords', 'Barlos Cardos', 'Science', 'O351'),
169 ('33-001', '18-century Dagger', 'Brevor Tartly', 'Art', 'C123'),
170 ('23-002', 'Old rock formation', 'Jarl Carl', 'Religion', 'K362'),
171 ('33-002', 'legacy grimstroke', 'Timon Simon', 'History', 'P125');
172
173INSERT INTO books(book_number, title, author)
174VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
175 ('BO1337', 'The good life', 'Trevor Bartly'),
176 ('PB7583', 'Burning birdges', 'Carl Jarl'),
177 ('AS7548', 'Relation list', 'Simon Timon'),
178 ('BO1234', 'The Lord Of The Rings', 'J.R.R Tolkien'),
179 ('GS1536', 'Lord of the Flies', 'William Golding'),
180 ('PO6845', 'SQL For Dummies', 'Robinson Crusoe');
181
182
183INSERT INTO book_loan(start_date_loan, end_date_loan, book_borrower, book_ID)
184VALUES (20181203, NULL, 'libra180', 'JO5678'),
185 (20111107, 20181224, 'libra180', 'BO1337'),
186 (20140514, 20180713, 'vserf495', 'PB7583' ),
187 (20050315, 20180305, 'babol666', 'AS7548'),
188 (20130325, 20181203, 'lesar427', 'JO5678');
189
190INSERT INTO slide_loan(start_date_loan_s, end_date_loan_s, slide_borrower, slide_ID)
191VALUES (20180302 , NULL, 'libra180', '23-001'),
192 (20161207, 20181224, 'vserf495', '33-001'),
193 (20180514, NULL, 'babol666', '23-002'),
194 (20060315, 20180305, 'lesar427', '33-002');
195
196
197INSERT INTO artifact(number, grid, depth, art_owner, art_date)
198VALUES (25, 45.46, 256, 'libra180', 19450505),
199 (45, 23.27, 322, 'vserf495', 20080905),
200 (38, 10.12, 800, 'lesar427', 19951202),
201 (10, 32.30, 300, 'babol666', 19970706),
202 (13, 55.76, 450, 'johdo321', 20090507),
203 (43, 32.56, 145, 'namna146', 20070928),
204 (66, 45.87, 201, 'jndio123', 20120128),
205 (23, 13.67, 384, 'bombe534', 20070717),
206 (43, 54.98, 158, 'babol666', 20010101),
207 (24, 43.11, 168, 'libra180', 19770325),
208 (67, 85.96, 631, 'vserf495', 20060725),
209 (85, 99.94, 351, 'lesar427', 20020523),
210 (54, 24.62, 842, 'babol666', 20100505),
211 (70, 17.63, 471, 'johdo321', 20130704),
212 (20, 52.73, 279, 'namna146', 19940808),
213 (36, 66.27, 417, 'jndio123', 20170313),
214 (17, 81.47, 749, 'bombe534', 20140224),
215 (63, 53.33, 826, 'babol666', 20021224);
216
217INSERT INTO dig(dig_name, dig_no, dig_date, worker)
218VALUES ('Tomb of Qin Shi Huangdi', 23, 19970904, 'vserf495'),
219 ('Ziggurat of Ur', 33, 18859394, 'babol666'),
220 ('Moche huaca', 55, 19330506, 'lesar427'),
221 ('Pyramids of Giza', 11, 20050304, 'libra180'),
222 ('the Great Pyramid of Khufu', 77, 20130207, 'libra180'),
223 ('Teotihuacan', 88, 20010709, 'babol666'),
224 ('Pyramid of the Sun at Teotihuacan', 84, 20020305, 'lesar427'),
225 ('Sunken temple of Caral', 67, 20070707, 'vserf495'),
226 ('Karazhan', 51, 20031203, 'vserf495'),
227 ('Razorfen downs', 09, 20010112, 'libra180'),
228 ('Uldaman', 99, 20060112, 'libra180'),
229 ('Uldaman', 77, 20000203, 'libra180');
230
231INSERT INTO location_segment(location_ID)
232VALUES ('SH01'),
233 ('SH02'),
234 ('SH03'),
235 ('SH04'),
236 ('MU01'),
237 ('MU02'),
238 ('MU03'),
239 ('MU04'),
240 ('ST01'),
241 ('ST02'),
242 ('ST03'),
243 ('ST04');
244
245INSERT INTO artifact_card(Notes, dig_origin_no, current_pos, card_ID, dig_grid, dig_depth, dig_origin_name)
246VALUES ('epic instance!' , 77, 'SH01', 25, 45.46, 256, 'Uldaman'),
247 ('great big dig', 99, 'ST01', 45, 23.27, 322, 'Uldaman'),
248 ('Amazing place!', 55, 'MU01', 85, 99.94, 351, 'Moche huaca'),
249 ('impressive artwork', 67, 'ST04', 10, 32.30, 300, 'Sunken temple of Caral');
250
251-- (4) CREATE VIEWS
252/*Påvisa databasens funktionalitet med SQL. a.Skapa tre vyer (views) som sammanställer data från era tabeller.
253b.Skapa fyra frågor (queries) för att demonstrera era tabeller i användning
254i.Alla frågor ska använda sig av ORDER BY eller GROUP BY
255ii.Max en simpelfråga (Fråga utan join, aggregatfunktion och villkor)
256iii.Minst två frågor som ska använda Villkor (WHERE)
257iv.En fråga som använder aggregatfunktioner (MAX, SUM, AVG, etc.). c.Tänk på att koppla ihop tabeller med INNER JOIN */
258
259GO
260CREATE VIEW student_staff AS
261SELECT staff_name, staff_role
262FROM staff
263WHERE staff_role = 'Student'
264GO
265
266SELECT * FROM student_staff
267
268GO
269
270CREATE VIEW liban_artifacts AS
271SELECT number, art_owner
272FROM artifact
273WHERE art_owner = 'libra180'
274GO
275
276
277SELECT * FROM liban_artifacts
278
279SELECT slide_number
280FROM slides
281ORDER BY slide_number ASC;
282
283SELECT AVG(depth)
284FROM artifact
285
286SELECT book_borrower, book_ID
287FROM staff
288INNER JOIN book_loan ON book_loan.book_borrower = staff.staff_ID
289
290
291-- (5) CREATE TRIGGERS/PROCEDURES
292
293-- foreign keys ska släppas först?