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