· 7 years ago · Dec 06, 2018, 07:44 AM
1-- DDL
2
3-- (1) DROPS
4-- (2) CREATE TABLES
5-- (3) INSERT VALUES
6-- (4) CREATE VIEWS
7-- (5) CREATE TRIGGERS/PROCEDURES
8DROP VIEW IF EXISTS books_loans;
9DROP VIEW IF EXISTS slide_loans;
10
11DROP TABLE IF EXISTS dig_extension;
12DROP TABLE IF EXISTS slides;
13DROP TABLE IF EXISTS books;
14DROP TABLE IF EXISTS digs;
15DROP TABLE IF EXISTS artefacts;
16DROP TABLE IF EXISTS documents;
17DROP TABLE IF EXISTS persons;
18
19
20
21
22-- person (person_id, p_name)
23CREATE TABLE persons (
24 PRIMARY KEY(p_id),
25
26 p_id INT,
27 firstname VARCHAR(35),
28 lastname VARCHAR(35),
29);
30
31-- documents (document_id, title, published_year, d_location, author, person)
32CREATE TABLE documents (
33 PRIMARY KEY(d_id),
34
35 d_id INT,
36 title VARCHAR(30),
37 published_year INT,
38 d_location VARCHAR(30),
39 author VARCHAR(30),
40 person INT,
41
42 FOREIGN KEY (person)
43 REFERENCES persons(p_id)
44 );
45
46-- artefacts(a_id, sequential_artefact, coordinate, depth, date_found, description, location, date_out, date_back)
47
48
49
50CREATE TABLE artefacts(
51 PRIMARY KEY(a_id),
52
53 a_id AS CONCAT (dig_id, '-', sequential_artefact) PERSISTED NOT NULL,
54 sequential_artefact INT,
55 coordinate VARCHAR(30),
56 depth VARCHAR(30),
57 date_found DATE,
58 descritpion VARCHAR(50),
59 a_location VARCHAR(30),
60 date_out DATE,
61 date_back DATE,
62 person INT,
63 dig_site VARCHAR(100),
64 dig_id INT,
65
66 FOREIGN KEY (person)
67 REFERENCES persons(p_id)
68
69-- FOREIGN KEY (a_dig_site)
70-- REFERENCES dig_extension(dig_site)
71
72-- FOREIGN KEY (a_dig_id)
73-- REFERENCES dig_extension(dig_id)
74);
75 SELECT CONCAT (dig_id, '-', sequential_artefact) FROM artefacts;
76-- digs( d_dig_site, d_dig_id, d_description, d_p_id)
77
78CREATE TABLE digs (
79 PRIMARY KEY (dig_id),
80
81 dig_site VARCHAR(100),
82 dig_id INT,
83 d_description VARCHAR(250),
84 p_id INT,
85
86 FOREIGN KEY (p_id)
87 REFERENCES persons(p_id),
88
89);
90
91CREATE TABLE books(
92 PRIMARY KEY (b_id),
93
94 b_id INT,
95 title VARCHAR(30),
96 published_year VARCHAR(10),
97 author VARCHAR(35),
98 date_out DATE,
99 b_location VARCHAR(50),
100 date_back DATE,
101 person INT,
102
103 FOREIGN KEY (person)
104 REFERENCES persons(p_id)
105);
106
107CREATE TABLE slides(
108 PRIMARY KEY (s_id),
109
110 s_id AS CONCAT (dig_id, '-', sequential_slide) PERSISTED NOT NULL,
111 sequential_slide INT,
112 s_subject VARCHAR(35),
113 s_description VARCHAR(40),
114 date_out DATE,
115 date_back DATE,
116 s_location VARCHAR(35),
117 person_id INT,
118 dig_id INT,
119
120
121 FOREIGN KEY (person_id)
122 REFERENCES persons(p_id),
123
124 FOREIGN KEY (dig_id)
125 REFERENCES digs(dig_id)
126
127);
128
129SELECT CONCAT (dig_id, '-', sequential_slide) FROM slides;
130
131
132SELECT COL_LENGTH ( 'artefacts', 'a_id')
133SELECT COL_LENGTH ( 'slides', 's_id')
134SELECT COL_LENGTH ( 'slides', 's_description')
135
136CREATE TABLE dig_extension(
137
138 -- PRIMARY KEY(id, sequential_artefact, dig_site, dig_id,b_id, s_id, sequential_slide),
139
140 id VARCHAR (25),
141 dig_id INT,
142 b_id INT,
143 s_id VARCHAR(25),
144
145
146 FOREIGN KEY (id)
147 REFERENCES artefacts (a_id),
148
149 FOREIGN KEY (dig_id)
150 REFERENCES digs(dig_id),
151
152 FOREIGN KEY (b_id)
153 REFERENCES books (b_id),
154
155 FOREIGN KEY (s_id)
156 REFERENCES slides (s_id)
157);
158
159
160
161
162
163 INSERT INTO persons (p_id, firstname, lastname)
164 VALUES (21, 'Bengt', 'Andersson'),
165 (32, 'Anna', 'Nilsson'),
166 (33, 'Johan', 'Ahlberg'),
167 (97, 'Anders', 'Svensson'),
168 (12, 'Jennifer', 'Berg'),
169 (11, 'Emelie', 'Palner'),
170 (77, 'Elinor', 'Nyberg'),
171 (87, 'Hannes', 'Lindgren'),
172 (99, 'Jacob', 'Lycke'),
173 (94, 'Olivia', 'Wennborg'),
174 (55, 'Johan', 'Haggar'),
175 (67, 'Axel', 'Guditz'),
176 (61, 'Jonathan', 'Solberg'),
177 (81, 'Sara', 'Andersdotter'),
178 (85, 'Amanda', 'Silfversparre'),
179 (74, 'Niklas', 'Guldsked'),
180 (49, 'Sven', 'Laxnacke'),
181 (41, 'Calle', 'Hoffeinheim');
182
183
184 INSERT INTO documents (d_id, title, published_year, d_location, author, person)
185 VALUES (392, 'Life On Earth', 1992, 'Shelf: A', 'James Cameron', 41),
186 (3582, 'Book Of Life', 2002, 'Shelf: D', 'Niklas Berg', 85),
187 (4921, 'Artefacts 101', 1952, 'Shelf: B', 'Gunnar Martin', 94),
188 (353, 'Cosmos', 2016, 'Shelf: C', 'Joseph Bang', 77),
189 (981, 'Space', 2002, 'Shelf: G', 'Anna Smith', 49),
190 (111, 'Moon landing 1969', 1970, 'Shelf: C', 'Anna Smith', 12),
191 (455, 'Festerist 101', 1994, 'Shelf: A', 'Clarence Coleman', 97),
192 (12, 'History of Tour de France', 2005, 'Shelf: A', 'Phil Stevenson', 87),
193 (71, 'How to dig a tunnel', 2010, 'Shelf: A', 'Christopher O.Neil', 33),
194 (9746, 'Basejump', 2013, 'Shelf: B', 'Dustin Hoffman', 32),
195 (32221, 'History of dinosaurs', 1987, 'Shelf C', 'Michael Bay', 21),
196 (4942837, 'Formel 1', 2018, 'Shelf: D', 'Stan Lee', 55);
197
198
199 INSERT INTO books (b_id, title, published_year, author, date_out, date_back, b_location, person)
200 VALUES (123, 'FOR AZEROTH!', 2007,'Dillon McNillon', '2018-11-05 ','2018-12-05 ', 'Borrowed', 77),
201 (435, 'National Treasure', 2003, 'Nicolas Cage',NULL, NULL, 'Shelf:A22', NULL),
202 (563, 'WAOOW', 2001, 'Owen Wilson', NULL, NULL, 'Shelf:A22', NULL),
203 (152, 'Nonstop', 2018, 'Drake', '2018-10-12', '2018-11-12', 'Borrowed', 99),
204 (880, 'Bridget Jones dagbok', 2002, 'Yung Nylla', NULL, NULL, 'Shelf:B12', NULL),
205 (981, 'Life of Dillon', 2001, 'John Cleese', '2018-09-11', '2018-10-11', 'Borrowed', 67),
206 (654, 'Hejhej', 2005, 'J&J', '2018-08-23', '2018-09-23', 'Borrowed', 21);
207
208
209
210
211
212
213 INSERT INTO digs (dig_site, dig_id, d_description, p_id)
214 VALUES ('Chan-Chan', 12, 'Beach on the coast of the commune of MehuÃn in southern Chile.', 41),
215 ('Monte Verde', 43, 'Monte Verde is an archaeological site in southern Chile, located near Puerto Montt, Southern Chile', 21),
216 ('Fell Cave', 9, 'Cueva Fell is a natural cave and archaeological site in southern Patagonia.', 32),
217 ('Pali Aike', 4, 'The Pali-Aike National Park is a park located in the Magallanes Region of the Chilean Patagonia.', 99),
218 ('An Yang', 6, 'An yang is a prefecture-level city in Henan province, China.', 67),
219 ('Kykkos Monastery', 21, 'Kykkos Monastery which lies 20 km west of Pedoulas, is one of the wealthiest and best-known monasteries in Cyprus.', 85),
220 ('Notre Dame de tyre', 67, 'Notre Dame de Tyre or Our Lady of Tyre is a monastic church in Nicosia.', 41);
221
222
223 INSERT INTO artefacts (sequential_artefact, coordinate, depth, date_found, descritpion, a_location, date_out, date_back, person,dig_id)
224 VALUES (465, '30:40', '20 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-01-24', 55, 12),
225 (266, '30:45', '15 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-02-24', 67, 43),
226 (266, '30:35', '39 cm', '2017-05-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-03-24', 81, 9),
227
228 (275, '32:42', '10 cm', '1997-05-29', 'Silver coin', 'Borrowed', '2018-12-01', '2019-01-30', 21,6),
229 (765, '32:42', '180 cm', '1997-05-12', 'Gold Coin', 'Borrowed', '2018-12-01', '2019-01-12', 41,6),
230 (865, '32:42', '20 cm', '1997-05-27', 'Chinese sword', 'Borrowed', '2018-12-01', '2019-01-22', 41,6),
231 (145, '32:32', '0.5 cm', '1997-01-12', 'Chinese golden knife', 'Borrowed', '2018-12-01', '2019-01-20', 77,6),
232 (132, '32:41', '200 cm', '1997-09-01', 'Chinese helmet', 'Borrowed', '2018-12-01', '2019-04-24', 41,6),
233 (166, '29:45', '234 cm', '1997-07-14', 'Chinese breast plate', 'Borrowed', '2018-12-01', '2019-05-24', 77,6),
234 (125, '27:43', '500 cm', '1997-06-14', 'Chinese shield', 'Borrowed', '2018-12-01', '2019-09-24', 41,6),
235
236 (151, '12:92:14', '37 cm', '1999-02-24', 'Wooden crucifix', 'In-House', NULL, NULL, NULL,21),
237 (118, '30:40:21', '94 cm', '1992-05-19', 'Golden ring', 'In-House', NULL, NULL, NULL, 21),
238 (119, '30:40:23', '122 cm', '1993-08-17', 'Monk cape', 'In-House', NULL, NULL, NULL,21),
239 (117, '30:40:55', '431 cm', '1992-01-12', 'Golden chain', 'In-House', NULL, NULL, NULL,21),
240 (110, '30:40:12', '12 cm', '1992-08-24', 'Candle holder', 'In-House', NULL, NULL, NULL,21),
241 (109, '30:40:98', '542 cm', '1992-02-02', 'Golden candle holder', 'In-House', NULL, NULL, NULL, 21),
242 (108, '30:40:24', '122 cm', '1993-02-23', 'Metal crucifix', 'In-House', NULL, NULL, NULL,21),
243 (102, '30:40:77', '32 cm', '1993-05-29', 'Wooden bowl', 'In-House', NULL, NULL, NULL,21),
244 (100, '30:40:98', '96 cm', '1992-06-28', 'black priest cape', 'In-House', NULL, NULL, NULL, 21),
245
246 (964, '98:34', '200 cm', '2015-02-23', 'wolf skeleton', 'Borrowed', '2016-04-01', '2019-01-10', 41,4),
247 (965, '95:42', '95 cm', '2015-03-24', 'Puma skeleton', 'Borrowed', '2016-04-01', '2019-01-10', 74,4),
248 (936, '93:43', '94 cm', '2015-03-22', 'Patagonian Hairy Armadillo', 'Borrowed', '2016-04-01', '2019-01-10', 74,4),
249
250 (462, '432:431', '0,1 cm', '1992-04-11', 'Old brick wall', 'Borrowed', '1992-12-01', '2017-12-24', 11,67),
251
252 (732, '84:42', '43 cm', '2013-05-21', 'Stuffed Lizard', 'In-House', NULL, NULL, NULL,43),
253 (757, '82:53', '53 cm', '2013-08-22', 'Snake skin', 'In-House', NULL, NULL, NULL,43),
254 (752, '81:95', '123 cm', '2017-08-19', 'Puma Skeleton', 'In-House', NULL, NULL, NULL, 43),
255
256
257 (343, '11:40', '13 cm', '1982-01-23', 'Fish skeleton', 'In-house',NULL, NULL, NULL, 9),
258 (365, '11:42', '11 cm', '1983-04-13', 'Big fish skeleton', 'In-House', NULL, NULL, NULL, 9),
259 (375, '11:53', '8 cm', '1982-06-19', 'Medium fish skeleton', 'In-House', NULL, NULL, NULL,9),
260 (392, '11:52', '1.5 cm', '1983-05-29', 'Random fish skeleton', 'In-House', NULL, NULL, NULL,9),
261 (326, '11:39', '5.5 cm', '1982-09-27', 'Old seaweed', 'Borrowed', '1997-05-29', '1997-09-29', NULL,9),
262
263 (753, '321:401', '00 cm', '1999-05-23', 'Rock statue', 'Borrowed', '2018-12-03', '2019-04-24', 21,12),
264 (725, '324:402', '45 cm', '1999-03-23', 'Wooden statue', 'Borrowed', '2018-12-03', '2019-04-24', 67,12),
265 (769, '381:471', '921 cm', '1999-06-28', 'Metal statue', 'Borrowed', '2018-12-03', '2019-04-24', 33,12),
266 (726, '339:412', '549 cm', '2000-03-12', 'Round random thing', 'Borrowed', '2018-12-03', '2019-04-24', 85,12),
267
268
269
270 (212, '282:52', '70 cm', '2017-02-22', 'Small round object', 'In-House', NULL, NULL, NULL,12),
271 (214, '284:42', '30 cm', '2017-05-23', 'Dinosaur bone', 'Borrowed', '2018-12-01', '2019-01-24', 41,12),
272 (285, '295:15', '65 cm', '2017-03-18', 'Skin bone', 'In-House', NULL, NULL, NULL,9),
273 (286, '251:12', '800 cm', '2015-05-23', 'Mammoth', 'In-House', NULL, NULL, NULL,12),
274 (203, '295:09', '300 cm', '2012-11-23', 'Tiger bone', 'Borrowed', '2018-10-01', '2019-02-24', 55,12),
275
276 (434, '302:140', '80 cm', '2002-02-06', 'Small round object', 'Borrowed', '2018-10-01', '2019-03-24', 55,6),
277 (491, '350:470', '470 cm', '2004-03-12', 'Big round object', 'In-House', NULL, NULL, NULL,4),
278 (461, '730:430', '205 cm', '2007-06-21', 'Small round object', 'Borrowed', '2018-12-01', '2019-12-24', 55,12),
279
280 (453, '230:240', '240 cm', '2011-02-23', 'Small round object', 'Borrowed', '2018-12-01', '2019-01-24', 55,12),
281 (361, '530:340', '120 cm', '2013-01-23', 'Small round object', 'Borrowed', '2018-12-01', '2017-01-14', 55,12);
282
283
284
285 INSERT INTO slides(sequential_slide, s_subject, s_description, s_location, date_out, date_back, person_id,dig_id)
286 VALUES (721, 'Animal tooth', 'Pictures of tiger tooth', 'In-House', NULL, NULL, NULL, 12),
287 (123, 'Animal tooth', 'Pictures of Megaladon tooth', 'In-House', NULL, NULL, NULL,12),
288 (46, 'Animal tooth', 'Pictures of snake tooth', 'Borrowed', '2018-03-21', '2019-01-20', 77,12),
289 (15, 'Fish', 'Fish documents', 'In-House', NULL, NULL, NULL,43),
290 (426, 'Human', 'Pictures of human bone', 'Borrowed', '2018-11-21', '2019-01-29', 87,4),
291 (646, 'Wand', 'Picture of Voldemorts wand', 'Borrowed', '2018-10-10', '2019-10-20', 55,67),
292 (223, 'Fish', 'Octopus', 'In-House', NULL, NULL, NULL,21),
293
294 (536, 'Animal tooth', 'Pictures of mammoth bone', 'Borrowed', '2018-11-12', '2019-02-20', 97,67),
295 (234, 'Coin', 'Documents of chinese Coins','Borrowed','2018-05-12','2019-01-12', 74,9),
296 (921, 'Sword', 'Journals swords from the roman empire', 'Borrowed', '2018-08-18', '2019-01-03', 12,6),
297 (391, 'Fish', 'Pictures of dead fishes', 'In-house', NULL, NULL, NULL,21),
298 (999, 'Crucifix', 'Journals of crucifixs', 'In-house', NULL, NULL, NULL,43),
299
300 (175, 'Statue', 'Drawings of old statues', 'Borrowed', '2018-09-21', '2019-08-10', 32,67),
301 (382, 'Wood', 'Wooden artefacts', 'Borrowed', '2012-05-13', '2020-11-17', 99,4),
302 (322, 'Wood', 'Wooden artefacts', 'Borrowed', '2018-05-13', '2019-08-17', 11,4);
303
304GO
305CREATE VIEW books_loans AS
306 SELECT SA.b_id,
307 (ST.title) AS title,
308 (CO.p_id) AS person
309 FROM books AS SA
310 INNER JOIN books AS ST
311 ON SA.b_id = ST.b_id
312 INNER JOIN persons AS CO
313 ON SA.person = CO.p_id;
314GO
315SELECT * FROM books_loans;
316
317SELECT * FROM books
318
319
320
321GO
322CREATE VIEW slide_loans AS
323 SELECT SA.s_id,
324 (ST.s_description) AS s_description,
325 (CO.p_id) AS person
326 FROM slides AS SA
327 INNER JOIN slides AS ST
328 ON SA.s_id = ST.s_id
329 INNER JOIN persons AS CO
330 ON SA.person_id = CO.p_id;
331GO
332SELECT * FROM slide_loans;
333
334
335SELECT * FROM slides;
336SELECT * FROM digs;