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