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