· 7 years ago · Dec 10, 2018, 11:04 AM
1--drops
2--create tables
3--insert values
4--create views
5--create triggers/procedures
6
7
8DROP TABLE IF EXISTS journals
9DROP TABLE IF EXISTS slide_loan
10DROP TABLE IF EXISTS book_loans
11DROP TABLE IF EXISTS artefact_loans
12DROP TABLE IF EXISTS slides
13DROP TABLE IF EXISTS boxes
14DROP TABLE IF EXISTS artefacts
15DROP TABLE IF EXISTS digs
16DROP TABLE IF EXISTS shelves
17DROP TABLE IF EXISTS books
18DROP TABLE IF EXISTS shelf_sections
19DROP TABLE IF EXISTS category
20DROP TABLE IF EXISTS users
21
22
23--Lägg tables som har foreign keys först och de som inte har sist.
24
25
26 CREATE TABLE users(
27 PRIMARY KEY (email),
28 email VARCHAR(70),
29 first_name VARCHAR(30),
30 last_name VARCHAR(40),
31 title VARCHAR(10),
32 phone_num INT--VARCHAR(12), --kanske int?
33 )
34
35
36
37CREATE TABLE category (
38 PRIMARY KEY (categoryid),
39 categoryid INT,
40 categoryname VARCHAR(15),
41
42)
43
44
45CREATE TABLE shelf_sections(
46 PRIMARY KEY (section),
47 section INT,
48 )
49
50
51CREATE TABLE books(
52 PRIMARY KEY (bookid),
53 bookid INT,
54 section INT,
55 title VARCHAR(50),
56 author VARCHAR(70),
57 publish_year INT,
58 FOREIGN KEY (section)
59 REFERENCES shelf_sections(section),
60 )
61
62CREATE TABLE shelves(
63 PRIMARY KEY (shelf_number),
64 shelf_number INT,
65 )
66
67CREATE TABLE digs(
68 PRIMARY KEY (digid),
69 digid INT,
70 digname VARCHAR(50)
71 )
72
73
74
75CREATE TABLE artefacts (
76 PRIMARY KEY (cardid),
77 cardid VARCHAR(7), --kombination av arteID o digid
78 artefactid INT,
79 digid INT,
80 shelf_number INT,
81 discovery_site VARCHAR(30),
82 depth INT,
83 date_of_discovery DATE, --not sure of int
84 notes VARCHAR(75),
85 categoryid INT
86
87 FOREIGN KEY (shelf_number)
88 REFERENCES shelves(shelf_number),
89
90 FOREIGN KEY (categoryid)
91 REFERENCES category(categoryid),
92
93 FOREIGN KEY (digid)
94 REFERENCES digs(digid)
95 )
96
97CREATE TABLE boxes(
98 PRIMARY KEY (boxid),
99 boxid INT,
100 )
101
102CREATE TABLE slides(
103 PRIMARY KEY (slideid),
104 slideid VARCHAR(7),
105 seqid INT,
106 digid INT,
107 boxid INT,
108 notes VARCHAR(500),
109 categoryid INT
110
111 FOREIGN KEY (digid)
112 REFERENCES digs(digid),
113
114 FOREIGN KEY (boxid)
115 REFERENCES boxes(boxid),
116
117 FOREIGN KEY (categoryid)
118 REFERENCES category(categoryid),
119
120 )
121
122CREATE TABLE artefact_loan(
123 PRIMARY KEY (loanid),
124 cardid VARCHAR(7),
125 loanid INT,
126 date_of_loan DATE,
127 artefactid INT,
128 digid INT,
129 email VARCHAR(70),
130 destination VARCHAR(30),
131 date_of_return DATE,
132 FOREIGN KEY (email)
133 REFERENCES users(email),
134
135 FOREIGN KEY (cardid)
136 REFERENCES artefacts(cardid),
137
138 FOREIGN KEY (digid)
139 REFERENCES digs(digid),
140 )
141
142CREATE TABLE book_loans (
143 PRIMARY KEY (loanid),
144 loanid INT,
145 date_of_loan DATE,
146 bookid INT,
147 email VARCHAR(70),
148 date_of_return DATE,
149 FOREIGN KEY (bookID)
150 REFERENCES books(bookid),
151
152 FOREIGN KEY (email)
153 REFERENCES users(email),
154 )
155
156CREATE TABLE slide_loan(
157 PRIMARY KEY (loanid),
158 loanid INT,
159 date_of_loan DATE,
160 slideid VARCHAR(7),
161 digid INT,
162 email VARCHAR(70),
163 date_of_return DATE,
164
165 FOREIGN KEY (slideid)
166 REFERENCES slides(slideid),
167
168 FOREIGN KEY (digid)
169 REFERENCES digs(digid),
170
171 FOREIGN KEY (email)
172 REFERENCES users(email),
173
174 )
175
176
177CREATE TABLE journals(
178 PRIMARY KEY (journalid),
179 section INT,
180 journalid INT,
181 title VARCHAR(20),
182 release_number BIGINT,
183 publish_year BIGINT,
184 FOREIGN KEY (section)
185 REFERENCES shelf_sections(section),
186)
187
188
189Insert INTO users (email, first_name, last_name,title, phone_num)
190VALUES ('davme844@liu.se', 'David', 'Mellheden', 'Kassör', 0762453642),
191 ('jakle223@liu.se', 'Jakob', 'Lennström', 'Spons', 0760856561),
192 ('jenko935@liu.se', 'Jens', 'Korsvold', 'Tryck', 0765656501),
193 ('emeng234@liu.se', 'Emmy', 'Englund', 'Info/PR', 0760856595),
194 ('agnal234@liu.se', 'Agnes', 'Alm', 'Utbildningsansvarig', 0760878780)
195
196INSERT INTO shelf_sections (section)
197VALUES (1),
198 (2),
199 (3),
200 (4),
201 (5),
202 (6),
203 (7)
204
205
206INSERT INTO shelves (shelf_number)
207VALUES (100),(101),(102),(103),(104),(105)
208
209
210
211INSERT INTO books (bookid, section, title, author, publish_year)
212VALUES (23, 1, 'Mr Bojangles', 'Vene Riktigt', 1940),
213 (48, 3, 'David fina äventyr', 'Vene Riktigt', 1940),
214 (165, 4, 'Emmys romantiska problem', 'Vene Riktigt', 1940),
215 (187, 10, 'jakle', 'Vene Riktigt', 1940),
216 (177, 7, 'NÄEH', 'Vene Riktigt', 1940),
217 (132, 2, 'jue', 'Vene Riktigt', 1940)
218
219
220INSERT INTO book_loans ( loanid, date_of_loan, bookid, email, date_of_return)
221VALUES (000001, '2018-12-03', 23, 'jakle223@liu.se', '2018-12-15'),
222 (000002, '2018-12-04', 48, 'jenko935@liu.se', '2018-12-16'),
223 (000003, '2018-12-05', 165, 'davme844@liu.se', '2018-12-17')
224
225
226INSERT INTO journals (journalid,section,title, release_number, publish_year)
227 VALUES (345345, 1, 'fantomen', 000111, 1987),
228 (345346, 2, 'batman', 000112, 1947),
229 (345347, 3, 'stålmannen', 000113, 1989),
230 (345348, 4, 'spindelmannen', 000114, 1946),
231 (345349, 5, 'thegreatjakob', 000115, 2011),
232 (345350, 6, 'bamse', 000116, 1978),
233 (345351, 7, 'historigrävningar', 000117, 1990)
234
235INSERT INTO boxes (boxid)
236VALUES (001),(002),(003),(004),(005),(006),(007),(008),(009),(010)
237
238INSERT INTO slides (slideid,digid, boxid, notes, categoryid)
239VALUES ('S001001', 001, 001,'Det var en gång en liten, liten jappist som bodde i Plattenbau. Denna jappist gick vid namnet Jake, men till damerna hade han som vana att presentera sig som Kleptoklåpp.', 1),
240 ('S001002', 001, 002, 'Det var en annan gång en stor, stor karl som bodde på Colonia. Denna bjässe, mer lik en hårlös björn än något annat, gick vid namnet Echbi och var fruktad på hela campus.', 2),
241 ('S002001', 002, 001, 'Det var en gång en fågel i Australien. Det korrekta namnet på denna fågelart var Emu, men om man frågade fågeln vad den själv ville bli kallad så svarade den alltid Emmy.', 3);
242
243
244INSERT INTO artefact_loan (loanid, cardid, date_of_loan, artefactid, digid, email, destination, date_of_return)
245VALUES (123456, 'A001001', '2018-06-07', 001,001, 'jakle223@liu.se', null,null),
246 (234567, 'A002001', '2018-10-22', 002,001, 'jenk935@liu.se', 'Norra flygeln',null),
247 (115432, 'A001002', '2016-10-09', 002,002, 'davme448@liu.se', null,null)
248
249
250INSERT INTO artefacts(cardid, artefactid, digid, shelf_number, discovery_site, depth, date_of_discovery, notes, categoryid)
251 VALUES ('A001001', 001, 001, 101, 'P22', 60, '2018-05-15', 'En gammal penna.', 1),
252 ('A002001', 002, 001, 102, 'C4', 25, '2018-07-08', 'En gammal ölburk', 2),
253 ('A001002', 001, 002, 103,'A2', 75, '2001-09-11', 'Ser ut som en del av en raserad byggnad.', 5)
254
255
256INSERT INTO slide_loan (loanid, date_of_loan, slideid, digid, email, date_of_return)
257VALUES (123456, '2018-12-05', 'S001001', 001, 'jakle223@liu.se', '2018-12-20'),
258 (123457, '2018-12-06', 'S001002', 002, 'jenko935@liu.se', '2018-12-21'),
259 (123458, '2018-12-07', 'S002001', 001, 'davme445@liu.se', '2018-12-22'),
260 (123459, '2018-12-08', 'S001003', 003, 'crusoe@liu.se', '2018-12-23'),
261 (123460, '2018-12-09', 'S001004', 004, 'trump@liu.se', '2018-12-24')
262
263INSERT INTO digs (digid, digname)
264VALUES (123, 'träsk'),
265 (124, 'norra skogen'),
266 (125, 'under skolan'),
267 (126, 'öknen'),
268 (127, 'apelsin lunden')