· 6 years ago · Dec 13, 2019, 09:26 AM
1DROP TABLE IF EXISTS loans, displayed_artifact, artifact_category, slide_category, slide, artifact, warehouse, borrower, categories, books_journals, dig_site, staff;
2
3CREATE TABLE staff(
4 PRIMARY KEY (staff_num),
5 staff_num INT IDENTITY(1,1) NOT NULL UNIQUE,
6 staff_name VARCHAR(40) NOT NULL,
7 title VARCHAR(30) NOT NULL,
8 phone VARCHAR(10) NOT NULL,
9 email VARCHAR(50) NOT NULL
10);
11
12CREATE TABLE dig_site(
13 PRIMARY KEY (dig_site_num),
14 dig_site_num INT IDENTITY(1,1) NOT NULL UNIQUE,
15 grid_x_max VARCHAR(3) NOT NULL,
16 grid_y_max INT NOT NULL,
17 dig_site_name VARCHAR(40) NOT NULL UNIQUE
18);
19
20CREATE TABLE books_journals(
21 PRIMARY KEY (book_num),
22 book_num INT IDENTITY(1,1) NOT NULL UNIQUE,
23 book_title VARCHAR(255) NOT NULL,
24 author VARCHAR(255) NOT NULL,
25 ISBN VARCHAR(13) NULL
26);
27
28CREATE TABLE categories(
29 PRIMARY KEY (category_num),
30 category_num INT IDENTITY(1,1) NOT NULL UNIQUE,
31 category_name VARCHAR(30) NOT NULL UNIQUE,
32 category_description TEXT NOT NULL
33);
34
35CREATE TABLE borrower(
36 PRIMARY KEY (borrower_num),
37 borrower_num INT NOT NULL UNIQUE,
38 borrower_name VARCHAR(40) NOT NULL,
39 email VARCHAR(50) NOT NULL
40);
41
42CREATE TABLE warehouse(
43 PRIMARY KEY (shelf_num),
44 shelf_num VARCHAR(4) NOT NULL UNIQUE,
45-- artifact_num INT NULL UNIQUE
46);
47
48CREATE TABLE artifact(
49 PRIMARY KEY (artifact_num),
50 artifact_num INT IDENTITY(1,1) NOT NULL UNIQUE,
51 date_found DATE NOT NULL,
52 dig_site_num INT,
53 grid_x VARCHAR(2) NOT NULL,
54 grid_y INT NOT NULL,
55 storage_location VARCHAR(5) NULL UNIQUE,
56 category_num VARCHAR(8) NOT NULL,
57 depth DECIMAL(4,2) NOT NULL,
58 found_by VARCHAR(8) NOT NULL,
59 display_location VARCHAR(10) NULL,
60 FOREIGN KEY (dig_site_num)
61 REFERENCES dig_site(dig_site_num),
62);
63
64CREATE TABLE slide(
65 PRIMARY KEY (slide_num),
66 slide_num INT IDENTITY(1,1) NOT NULL UNIQUE,
67 slide_name VARCHAR(40) NOT NULL,
68-- category_num VARCHAR(10) NOT NULL,
69 slide_description text NOT NULL,
70 artifact_num INT NOT NULL UNIQUE
71 FOREIGN KEY (artifact_num)
72 REFERENCES artifact(artifact_num)
73);
74CREATE TABLE slide_category(
75 PRIMARY KEY (slide_num, category_num),
76 slide_num INT NOT NULL UNIQUE,
77 category_num INT NOT NULL UNIQUE
78 FOREIGN KEY (slide_num)
79 REFERENCES slide(slide_num),
80 FOREIGN KEY (category_num)
81 REFERENCES categories(category_num)
82);
83
84CREATE TABLE artifact_category(
85 PRIMARY KEY (category_num, artifact_num),
86 category_num INT NOT NULL UNIQUE,
87 artifact_num INT NOT NULL UNIQUE
88 FOREIGN KEY (category_num)
89 REFERENCES categories(category_num),
90 FOREIGN KEY (artifact_num)
91 REFERENCES artifact(artifact_num)
92);
93
94CREATE TABLE displayed_artifact(
95 PRIMARY KEY (display_shelf_num),
96 display_shelf_num INT IDENTITY(1,1) NOT NULL UNIQUE,
97 artifact_num INT NULL UNIQUE
98);
99
100CREATE TABLE loans(
101 PRIMARY KEY (loan_num),
102 loan_num INT IDENTITY(1,1) NOT NULL UNIQUE,
103 slide_num INT NULL,
104 book_num INT NULL,
105 borrower INT NOT NULL,
106 date_in DATE NOT NULL,
107 date_out DATE NOT NULL,
108 return_date DATE NULL
109 FOREIGN KEY (slide_num)
110 REFERENCES slide(slide_num),
111 FOREIGN KEY (book_num)
112 REFERENCES books_journals(book_num),
113 FOREIGN KEY (borrower)
114 REFERENCES borrower(borrower_num)
115);
116
117
118INSERT INTO staff(staff_name, title, phone, email)
119VALUES ('Martin Andersson', 'Researcher', '0730456597', 'hej@martin.se'),
120 ('Albin Kindstrad', 'Manager', '070123456', 'hej@albin.se'),
121 ('Olle Olofsson', 'Librarian', '01355789', 'mail@hotmail.ru'),
122 ('Hugh Heffner', 'Student', '911', 'grills@mail.com'),
123 ('Dag Otto', 'Researcher', '112', 'hello@world.com'),
124 ('Zlatan Ibrahimovic', 'Student', '070*******', 'zl*****i**********.com');
125
126INSERT INTO dig_site(grid_x_max, grid_y_max, dig_site_name)
127VALUES('F',100, 'Titanic Wreck Site'),
128 ('K',40, 'Tutankhamons tomb'),
129 ('AB',3000, 'Martin Anderssons basement'),
130 ('F',1000, 'Stångån'),
131 ('F',500, 'Unidentified space ship'),
132 ('L',100, 'Area 51'),
133 ('O',34, 'Ground 0'),
134 ('C',345, 'Bombsite Hiroshima'),
135 ('I',100, 'Battle of Lützen'),
136 ('S',90, 'Bloodbath of Stångebro'),
137 ('TY',54, 'Marsian settlement'),
138 ('U',78, 'The Moon');
139
140INSERT INTO books_journals(book_title, author, ISBN)
141VALUES ('Lunds undre värld, del 2 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457109'),
142 ('Lunds undre värld, del 1 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457123'),
143 ('Lunds undre värld, del 3 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457798'),
144 ('Minnen från utgrävningen 1986-1993 av ostindiefararen Götheborg', 'Anita Steiner', '9789163338366'),
145 ('Alvastra pålbyggnad : 1976-1980 års utgrävningar - västra schaktet', 'Hans Browall', '9789174024449'),
146 ('Trafik, broar, tunnelbanor, gator', 'Arne Dufwa', '91-38-08725-1'),
147 ('Alvastra pålbyggnad : 1909-1930 års utgrävningar', 'Hans Browall', '9789174023978'),
148 ('Mångkulturella möten kring en forntida lämning : rapport om utgrävningen kring en hällkista i Ber', 'A Sunnestvedt', '9789185411023'),
149 ('Atoms, molecules, solids, and surfaces: Applications of the generalized gradient approximation for exchange and correlation', 'John P. Perdew, J. A. Chevary, S. H. Vosko, Koblar A. Jackson, Mark R. Pederson, D. J. Singh, and Carlos Fiolhais', '123456789'),
150 ('Fourth report on the excavations of the Roman fort at Richborough, Kent', 'Bushe-Fox, J.P.', '430789642'),
151 ('Excavations at Brough-on-Humber 1958–61', 'Wacher, J.S.', '97891754109'),
152 ('Archaeology of NIDDM: Excavation of the “Thrifty” Genotype', 'Michael Wendorf', '0012-1797'),
153 ('Excavations at Hengistbury Head, Hampshire, in 1911–12', ' Plunkett Bushe-Fox, Joscelyn', 'xxxxxxxxxx'),
154 ('Third Report on the Excavations of the Roman Fort at Richborough, Kent', ' Bushe-Fox, J. P.', 'XXXXXXXXXXX'),
155 ('First Report on the Excavations of the Roman Fort at Richborough, Kent', ' Bushe-Fox, J.P.', 'XXXXXXXXXXX');
156
157INSERT INTO categories(category_name, category_description)
158VALUES ('The Dark Ages', 'Events taken place a long time ago'),
159 ('World War 2', 'The second world war'),
160 ('Linköping', 'City in Sweden'),
161 ('Japan', 'Country in East Asia'),
162 ('Egypt', 'Country in north east Africa'),
163 ('Germany', 'Country in Europe'),
164 ('Mars', 'Planet in the solar system'),
165 ('Moon', 'Earths only natural sattelite'),
166 ('United States of Amaerica', 'Maybe not the greatest country in the world'),
167 ('USA', 'see United States of America'),
168 ('Area 51', 'Top Secret military base in the nevada dessert'),
169 ('Pyramid', 'Graves for high ranking offiials in old Egypt'),
170 ('Atlantic Ocean', 'Big Ocean'),
171 ('Hiroshima', 'City in japan, first nuke was dropped here'),
172 ('30 year war', 'Big european war during the 15th century'),
173 ('Extraterrestrial life', 'Aliens'),
174 ('Martin Andersson', 'Kul kille från Askersund, en liten SM-tomte');
175
176 --*INSERT INTO BORROWER
177
178INSERT INTO warehouse(shelf_num)
179VALUES ('77B'),
180 ('1A'),
181 ('1B'),
182 ('1C'),
183 ('2A'),
184 ('2B'),
185 ('2C'),
186 ('3A'),
187 ('3B'),
188 ('3C'),
189 ('4A'),
190 ('4B'),
191 ('4C'),
192 ('5A'),
193 ('5B'),
194 ('5C'),
195 ('6A'),
196 ('6B'),
197 ('6C'),
198 ('7A'),
199 ('7B'),
200 ('7C'),
201 ('8A'),
202 ('8B'),
203 ('8C'),
204 ('9A'),
205 ('9B'),
206 ('9C'),
207 ('10A'),
208 ('10B'),
209 ('10C'),
210 ('11A'),
211 ('11B'),
212 ('11C'),
213 ('12A'),
214 ('12B'),
215 ('12C'),
216 ('13A'),
217 ('13B'),
218 ('13C'),
219 ('14A'),
220 ('14B'),
221 ('14C'),
222 ('15A'),
223 ('15B'),
224 ('15C');
225
226INSERT INTO artifact(date_found, dig_site_num, grid_x, grid_y, storage_location, category_num, depth, found_by, display_location)
227VALUES ('2018-12-24', 1, 'A', 23, '15C', 4, 1.30, '1', '15-99'),
228 ('2018-12-23', 1, 'B', 15, '14C', 4, 1.30, '1', '15-98'),
229 ('2018-12-24', 1, 'C', 20, '13C', 4, 1.30, '1', '15-96'),
230 ('2018-12-22', 1, 'A', 20, '12C', 4, 0.30, '1', '15-69'),
231 ('2018-12-25', 1, 'B', 21, '11C', 4, 1.00, '1', '15-100'),
232 ('2018-12-31', 1, 'C', 21, '10C', 5, 1.10, '1', NULL),
233 ('2018-12-31', 1, 'A', 21, '19C', 1, 1.10, '1', NULL),
234 ('2018-12-31', 1, 'B', 14, '18C', 1, 1.20, '1', NULL),
235 ('2019-01-01', 2, 'C', 14, '17C', 2, 1.25, '2', NULL),
236 ('2019-12-01', 2, 'A', 12, '16C', 2, 2.50, '2', NULL),
237 ('2019-12-01', 2, 'B', 13, '15A', 3, 2.30, '2', NULL),
238 ('2019-12-01', 2, 'C', 1, '15B', 5, 1.30, '2', '12-100'),
239 ('2019-12-02', 2, 'A', 10, '1A', 3, 1.30, '2', '11-100'),
240 ('2019-12-02', 2, 'A', 16, '1B', 7, 1.30, '3', '13-100'),
241 ('2019-12-02', 2, 'A', 17, '1C', 8, 1.30, '3', '14-100'),
242 ('2019-12-03', 2, 'B', 17, '2A', 9, 1.30, '3', '9-100'),
243 ('2019-12-03', 2, 'C', 18, '2C', 8, 1.30, '3', '8-100'),
244 ('2019-12-12', 3, 'C', 19, '2B', 5, 1.30, '3', '7-100'),
245 ('2019-12-12', 3, 'C', 1, '3A', 5, 1.30, '3', '6-100'),
246 ('2019-12-12', 3, 'B', 2, '3B', 4, 1.30, '4', '5-100'),
247 ('2019-12-15', 3, 'B', 1, '3C', 4, 1.30, '4', '4-100'),
248 ('2019-12-15', 3, 'A', 3, '4A', 2, 1.30, '4', '3-100'),
249 ('2019-12-15', 3, 'A', 3, '4B', 2, 1.30, '4', '2-100'),
250 ('2019-12-15', 3, 'B', 4, '4C', 1, 1.30, '4', '1-100'),
251 ('2019-12-23', 4, 'B', 5, '5A', 9, 1.30, '4', '10-10'),
252 ('2019-12-24', 5, 'A', 4, '5B', 12, 1.30, '5', '10-11'),
253 ('2019-12-24', 5, 'C', 6, '5C', 11, 1.30, '5', '10-12'),
254 ('2019-12-24', 5, 'A', 8, '6A', 13, 1.30, '5', '10-13'),
255 ('2019-12-24', 5, 'B', 8, '6B', 14, 1.30, '5', '10-14'),
256 ('2019-12-24', 5, 'A', 7, '6C', 16, 1.30, '5', NULL);
257
258INSERT INTO slide(slide_name, slide_description, artifact_num)
259VALUES ('Tuttan Kamo', 'Legendarik bror', 1),
260 ('Tomtefar', 'vitt skägg', 2),
261 ('Mårtens svärd', 'Tungt! Vasst! Asballt!', 3),
262 ('Fotboll', 'värdelös', 4),
263 ('iPhone 18X', 'prototyp', 5),
264 ('smörkniv', 'i trä', 6),
265 ('liten sten', 'rund', 7),
266 ('kedja', 'tung', 8),
267 ('bildäck', 'inte gammalt eller så, oklart varför det visas', 9),
268 ('guldtand', 'Charles', 10),
269 ('Silversten', 'fint', 11),
270 ('Vasaskeppet', 'lol', 12),
271 ('Go-kart', 'lit', 13),
272 ('Pikatchu', 'rip', 14),
273 ('100lök', 'stonksy', 15);
274
275INSERT INTO displayed_artifact(artifact_num)
276VALUES (1),
277 (2),
278 (9),
279 (13);
280
281INSERT INTO borrower(borrower_num,borrower_name,email)
282VALUES(1,'Kungen Kalle', 'kungen@sverige.se'),
283 (2,'Bänge Bengtsson', 'bänge@bangbang.se'),
284 (3,'J Stalin', 'stalin@cccp.ru'),
285 (4,'Luke Skywalker', 'jediknight1337@jedicounsil.org'),
286 (5,'Yoda', 'superJediXXX@jedicounsil.org'),
287 (6,'Hacke Hackspett', 'hacke@saks.se'),
288 (7,'Göran Goldkhul', 'goran.goldkhul@liu.se'),
289 (8,'Astrid Lindgren', 'astrid@lonneberga.se'),
290 (9,'Henry Heroin', 'overdose3000@dude.com'),
291 (10,'Berra Bärs', 'inkop@systembolaget.se');
292
293
294--INSERT av böcker i loans, ej tillbakalämnade,
295INSERT INTO loans(book_num, borrower,date_in, date_out)
296VALUES (1, 1, '2019-10-10', '2019-11-10'),
297(2,2,'2019-11-28','2019-11-29');
298
299--INSERT av böcker, tillbakalämnade
300INSERT INTO loans(book_num, borrower,date_in,date_out,return_date)
301VALUES (3,3, '2019-03-03','2019-04-03','2019-03-15'),
302(4,4,'2019-06-14','2019-06-23','2019-08-12')
303
304--INSERT av slides, ej tillbakalämnade
305INSERT INTO loans(slide_num, borrower,date_in, date_out)
306VALUES (1, 1, '2019-10-10', '2019-11-10'),
307(2,2,'2019-11-28','2019-11-29');
308
309--INSERT av slides, tillbakalämnade
310INSERT INTO loans(slide_num, borrower,date_in,date_out,return_date)
311VALUES (3,3, '2019-03-03','2019-04-03','2019-03-15'),
312 (1,1, '2019-02-03','2019-04-03','2019-11-29');