· 7 years ago · Dec 06, 2018, 02:24 PM
1--DDL
2
3-- (1) DROPS
4
5DROP TABLE IF EXISTS book_loan;
6DROP TABLE IF EXISTS slide_loan;
7DROP TABLE IF EXISTS slides;
8DROP TABLE IF EXISTS books;
9DROP TABLE IF EXISTS book_shelf;
10DROP TABLE IF EXISTS slide_shelf;
11DROP TABLE IF EXISTS artifact_card;
12DROP TABLE IF EXISTS location_segment;
13DROP TABLE IF EXISTS dig;
14DROP TABLE IF EXISTS artifact;
15DROP TABLE IF EXISTS staff;
16
17
18-- (2) CREATE TABLES
19/*
20CREATE TABLE staff(
21 PRIMARY KEY(staff_ID),
22 staff_ID VARCHAR(8) NOT NULL,
23 staff_role VARCHAR(20) NOT NULL,
24 staff_name VARCHAR(30) NOT NULL,
25 date_registered VARCHAR(10) NOT NULL,
26);
27
28CREATE TABLE artifact(
29 PRIMARY KEY(number),
30 number INT NOT NULL,
31 grid INT NOT NULL,
32 depth INT NOT NULL,
33 art_owner VARCHAR(8),
34 FOREIGN KEY (art_owner)
35 REFERENCES staff(staff_ID)
36);
37
38CREATE TABLE dig(
39 DigName VARCHAR(20) NOT NULL,
40 DigNo INT NOT NULL,
41 DigDate VARCHAR(10),
42 PRIMARY KEY(DigName, DigNo)
43);
44
45CREATE TABLE location_segment(
46 location_ID VARCHAR(8) NOT NULL,
47 PRIMARY KEY(location_ID)
48);
49
50CREATE TABLE artifact_card(
51 Notes VARCHAR(140)
52);
53
54CREATE TABLE books(
55 book_number VARCHAR(6) NOT NULL,
56 title VARCHAR(30) NOT NULL,
57 author VARCHAR(30) NOT NULL,
58 PRIMARY KEY(book_number));
59
60CREATE TABLE slides(
61 slide_ID VARCHAR(6) NOT NULL,
62 title VARCHAR(30) NOT NULL,
63 author VARCHAR(30) NOT NULL,
64 slide_category VARCHAR(30) NOT NULL,
65 PRIMARY KEY(slide_ID)
66);
67
68CREATE TABLE slide_loan(
69 PRIMARY KEY(start_date),
70 start_date INT,
71 slide_borrower VARCHAR(8),
72 FOREIGN KEY (slide_borrower)
73 REFERENCES staff(staff_ID),
74);
75
76CREATE TABLE book_loan(
77 PRIMARY KEY(start_date),
78 start_date INT,
79 book_borrower VARCHAR(8),
80 FOREIGN KEY (book_borrower)
81 REFERENCES staff(staff_ID),
82);
83*/
84-- från william >
85
86
87CREATE TABLE staff(
88 PRIMARY KEY(staff_ID),
89 staff_ID VARCHAR(8),
90 staff_role VARCHAR(20) NOT NULL,
91 staff_name VARCHAR(30) NOT NULL,
92 date_registered VARCHAR(10) NOT NULL,
93);
94
95CREATE TABLE artifact(
96 PRIMARY KEY(number, grid, depth),
97 number INT,
98 grid INT,
99 depth INT,
100 art_owner VARCHAR(8),
101 FOREIGN KEY (art_owner)
102 REFERENCES staff(staff_ID)
103);
104
105CREATE TABLE dig(
106 dig_name VARCHAR(50) NOT NULL,
107 dig_no INT,
108 dig_date VARCHAR(10),
109 worker VARCHAR(8),
110 PRIMARY KEY(dig_name, dig_no),
111 FOREIGN KEY (worker)
112 REFERENCES staff(staff_ID)
113);
114
115CREATE TABLE location_segment(
116 location_ID VARCHAR(8),
117 PRIMARY KEY(location_ID)
118);
119
120
121CREATE TABLE artifact_card(
122 Notes VARCHAR(140),
123 card_ID INT,
124 current_pos VARCHAR(8),
125 dig_origin_no INT,
126 dig_origin_name VARCHAR(50),
127 dig_grid INT,
128 dig_depth INT,
129 FOREIGN KEY (card_ID, dig_grid, dig_depth)
130 REFERENCES artifact(number, grid, depth),
131 FOREIGN KEY (current_pos)
132 REFERENCES location_segment(location_ID),
133 FOREIGN KEY (dig_origin_name, dig_origin_no)
134 REFERENCES dig(dig_name, dig_no),
135
136);
137
138CREATE TABLE slide_shelf(
139 s_shelf_number VARCHAR(5),
140 PRIMARY KEY (s_shelf_number)
141);
142
143CREATE TABLE book_shelf(
144 b_shelf_number VARCHAR(5),
145 PRIMARY KEY (b_shelf_number)
146);
147
148
149CREATE TABLE books(
150 book_number VARCHAR(6),
151 title VARCHAR(30) NOT NULL,
152 author VARCHAR(30) NOT NULL,
153 home_shelf VARCHAR(5),
154 PRIMARY KEY(book_number),
155 FOREIGN KEY (home_shelf)
156 REFERENCES book_shelf(b_shelf_number)
157
158);
159
160CREATE TABLE slides(
161 slide_number VARCHAR(6),
162 title VARCHAR(30) NOT NULL,
163 author VARCHAR(30),
164 slide_category VARCHAR(30),
165 home_shelf VARCHAR(5),
166 PRIMARY KEY(slide_number),
167 FOREIGN KEY (home_shelf)
168 REFERENCES slide_shelf(s_shelf_number)
169);
170
171CREATE TABLE slide_loan(
172 start_date_loan_s INT,
173 end_date_loan_s INT,
174 slide_borrower VARCHAR(8),
175 slide_ID VARCHAR(6),
176 PRIMARY KEY(start_date_loan_s),
177 FOREIGN KEY (slide_borrower)
178 REFERENCES staff(staff_ID),
179 FOREIGN KEY (slide_ID)
180 REFERENCES slides(slide_number),
181);
182
183CREATE TABLE book_loan(
184
185 start_date_loan INT,
186 end_date_loan INT,
187 book_borrower VARCHAR(8),
188 book_ID VARCHAR(6),
189 PRIMARY KEY(start_date_loan),
190 FOREIGN KEY (book_borrower)
191 REFERENCES staff(staff_ID),
192 FOREIGN KEY (book_ID)
193 REFERENCES books(book_number)
194);
195-- slut från william
196
197INSERT INTO slide_shelf(s_shelf_number)
198VALUES('O351'),
199 ('C123'),
200 ('K362'),
201 ('I994'),
202 ('Q122'),
203 ('P125'),
204 ('Y156');
205
206
207INSERT INTO book_shelf(b_shelf_number)
208VALUES ('W126'),
209 ('J563'),
210 ('U898'),
211 ('H759'),
212 ('O340'),
213 ('L998'),
214 ('E127'),
215 ('H784');
216
217
218-- (3) INSERT VALUES
219INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
220VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
221 ('vserf495', 'Excavator', 'Vsera rfoman', 20080905),
222 ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
223 ('babol666', 'Excavator', 'Barbro Olev', 19970706);
224
225INSERT INTO slides(slide_number, title, author, slide_category, home_shelf)
226VALUES('23-001', '15-century Swords', 'Barlos Cardos', 'Science', 'O351'),
227 ('33-001', '18-century Dagger', 'Brevor Tartly', 'Art', 'C123'),
228 ('23-002', 'Old rock formation', 'Jarl Carl', 'Religion', 'K362'),
229 ('33-002', 'legacy grimstroke', 'Timon Simon', 'History', 'P125');
230
231
232
233
234
235INSERT INTO books(book_number,title,author)
236VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
237 ('BO1337', 'The good life', 'Trevor Bartly'),
238 ('PB7583', 'Burning birdges', 'Carl Jarl'),
239 ('AS7548', 'Relation list', 'Simon Timon');
240
241
242
243
244INSERT INTO book_loan(start_date_loan, end_date_loan, book_borrower, book_ID)
245VALUES (20181203, null, 'libra180', 'JO5678'),
246 (20111107, 20181224, 'libra180', 'BO1337'),
247 (20140514, 20180713, 'vserf495', 'PB7583' ),
248 (20050315, 20180305, 'babol666', 'AS7548'),
249 (20130325, 20181203, 'lesar427', 'JO5678');
250
251
252INSERT INTO slide_loan(start_date_loan_s, end_date_loan_s, slide_borrower, slide_ID)
253VALUES (20180302 , null, 'libra180', '23-001'),
254 (20161207, 20181224, 'vserf495', '33-001'),
255 (20180514, null, 'babol666', '23-002'),
256 (20060315, 20180305, 'lesar427', '33-002');
257
258
259
260INSERT INTO dig(dig_name, dig_no, dig_date, worker)
261VALUES ('Tomb of Qin Shi Huangdi', 23, 19970904, 'vserf495'),
262 ('Ziggurat of Ur', 33, 18859394, 'babol666'),
263 ('Moche huaca', 55, 19330506, 'lesar427'),
264 ('Pyramids of Giza', 11, 20050304, 'libra180'),
265 ('the Great Pyramid of Khufu', 77, 20130207, 'libra180'),
266 ('Teotihuacan', 88, 20010709, 'babol666'),
267 ('Pyramid of the Sun at Teotihuacan', 84, 20020305, 'lesar427'),
268 ('Sunken temple of Caral', 67, 20070707, 'vserf495'),
269 ('Karazhan', 51, 20031203, 'vserf495'),
270 ('Razorfen downs', 09, 20010112, 'libra180'),
271 ('Uldaman', 99, 20060112, 'libra180'),
272 ('Uldaman', 77, 20000203, 'libra180');
273
274-- (4) CREATE VIEWS
275
276
277
278-- (5) CREATE TRIGGERS/PROCEDURES
279
280-- foreign keys ska släppas först?
281
282
283----------------------------------------------------------------------
284
285CREATE TABLE slide_loan(
286 start_date_loan_s INT,
287 end_date_loan_s INT,
288 slide_borrower VARCHAR(8),
289 slide_ID VARCHAR(6),
290 PRIMARY KEY(start_date_loan_s),
291 FOREIGN KEY (slide_borrower)
292 REFERENCES staff(staff_ID),
293 FOREIGN KEY (slide_ID)
294 REFERENCES slides(slide_number),
295);
296
297CREATE TABLE book_loan(
298
299 start_date_loan INT,
300 end_date_loan INT,
301 book_borrower VARCHAR(8),
302 book_ID VARCHAR(6),
303 PRIMARY KEY(start_date_loan),
304 FOREIGN KEY (book_borrower)
305 REFERENCES staff(staff_ID),
306 FOREIGN KEY (book_ID)
307 REFERENCES books(book_number)