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