· 7 years ago · Dec 18, 2018, 12:46 PM
1--DDL
2
3-- (1) DROPS
4
5DROP VIEW IF EXISTS liban_artifacts;
6DROP VIEW IF EXISTS student_staff;
7DROP TABLE IF EXISTS book_loan;
8DROP TABLE IF EXISTS slide_loan;
9DROP TABLE IF EXISTS slides;
10DROP TABLE IF EXISTS books;
11DROP TABLE IF EXISTS book_shelf;
12DROP TABLE IF EXISTS slide_shelf;
13DROP TABLE IF EXISTS artifact_card;
14DROP TABLE IF EXISTS location_segment;
15DROP TABLE IF EXISTS dig;
16DROP TABLE IF EXISTS artifact;
17DROP TABLE IF EXISTS staff;
18
19-- (2) CREATE TABLES
20
21CREATE TABLE staff(
22 PRIMARY KEY(staff_ID),
23 staff_ID VARCHAR(8),
24 staff_role VARCHAR(20) NOT NULL,
25 staff_name VARCHAR(30) NOT NULL,
26 date_registered VARCHAR(10) NOT NULL,
27);
28
29CREATE TABLE artifact(
30 PRIMARY KEY(number, grid, depth),
31 number INT,
32 grid INT,
33 depth INT,
34 art_owner VARCHAR(8),
35 art_date INT,
36 FOREIGN KEY (art_owner)
37 REFERENCES staff(staff_ID)
38);
39
40CREATE TABLE dig(
41 dig_name VARCHAR(50) NOT NULL,
42 dig_no INT,
43 dig_date VARCHAR(10),
44 worker VARCHAR(8),
45 PRIMARY KEY(dig_name, dig_no),
46 FOREIGN KEY (worker)
47 REFERENCES staff(staff_ID)
48);
49
50CREATE TABLE location_segment(
51 location_ID VARCHAR(8),
52 PRIMARY KEY(location_ID)
53);
54
55
56CREATE TABLE artifact_card(
57 Notes VARCHAR(140),
58 card_ID INT,
59 current_pos VARCHAR(8),
60 dig_origin_no INT,
61 dig_origin_name VARCHAR(50),
62 dig_grid INT,
63 dig_depth INT,
64 FOREIGN KEY (card_ID, dig_grid, dig_depth)
65 REFERENCES artifact(number, grid, depth),
66 FOREIGN KEY (current_pos)
67 REFERENCES location_segment(location_ID),
68 FOREIGN KEY (dig_origin_name, dig_origin_no)
69 REFERENCES dig(dig_name, dig_no),
70
71);
72
73CREATE TABLE slide_shelf(
74 s_shelf_number VARCHAR(5),
75 PRIMARY KEY (s_shelf_number)
76);
77
78CREATE TABLE book_shelf(
79 b_shelf_number VARCHAR(5),
80 PRIMARY KEY (b_shelf_number)
81);
82
83
84CREATE TABLE books(
85 book_number VARCHAR(6),
86 title VARCHAR(30) NOT NULL,
87 author VARCHAR(30) NOT NULL,
88 home_shelf VARCHAR(5),
89 PRIMARY KEY(book_number),
90 FOREIGN KEY (home_shelf)
91 REFERENCES book_shelf(b_shelf_number)
92
93);
94
95CREATE TABLE slides(
96 slide_number VARCHAR(6),
97 title VARCHAR(30) NOT NULL,
98 author VARCHAR(30),
99 slide_category VARCHAR(30),
100 home_shelf VARCHAR(5),
101 PRIMARY KEY(slide_number),
102 FOREIGN KEY (home_shelf)
103 REFERENCES slide_shelf(s_shelf_number)
104);
105
106CREATE TABLE slide_loan(
107 start_date_loan_s INT,
108 end_date_loan_s INT,
109 slide_borrower VARCHAR(8),
110 slide_ID VARCHAR(6),
111 PRIMARY KEY(start_date_loan_s),
112 FOREIGN KEY (slide_borrower)
113 REFERENCES staff(staff_ID),
114 FOREIGN KEY (slide_ID)
115 REFERENCES slides(slide_number),
116);
117
118CREATE TABLE book_loan(
119
120 start_date_loan INT,
121 end_date_loan INT,
122 book_borrower VARCHAR(8),
123 book_ID VARCHAR(6),
124 PRIMARY KEY(start_date_loan),
125 FOREIGN KEY (book_borrower)
126 REFERENCES staff(staff_ID),
127 FOREIGN KEY (book_ID)
128 REFERENCES books(book_number)
129);
130
131
132-- (3) INSERT VALUES
133
134INSERT INTO slide_shelf(s_shelf_number)
135VALUES('O351'),
136 ('C123'),
137 ('K362'),
138 ('I994'),
139 ('Q122'),
140 ('P125'),
141 ('Y156');
142
143INSERT INTO book_shelf(b_shelf_number)
144VALUES ('W126'),
145 ('J563'),
146 ('U898'),
147 ('H759'),
148 ('O340'),
149 ('L998'),
150 ('E127'),
151 ('H784');
152
153
154
155
156
157INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
158VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
159 ('vserf495', 'Excavator', 'Vsera Rfoman', 20080905),
160 ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
161 ('babol666', 'Excavator', 'Barbro Olev', 19970706),
162 ('Jndio123', 'Excavator', 'Jndiana Iones', 19420713),
163 ('johdo321', 'Student', 'John Doe', 19650621),
164 ('namna146', 'Student', 'Name Nameson', 19990214),
165 ('bombe534', 'Professor', 'Bom Betty', 1986042);
166
167INSERT INTO slides(slide_number, title, author, slide_category, home_shelf)
168VALUES('23-001', '15-century Swords', 'Barlos Cardos', 'Science', 'O351'),
169 ('33-001', '18-century Dagger', 'Brevor Tartly', 'Art', 'C123'),
170 ('23-002', 'Old rock formation', 'Jarl Carl', 'Religion', 'K362'),
171 ('33-002', 'legacy grimstroke', 'Timon Simon', 'History', 'P125');
172
173INSERT INTO books(book_number, title, author)
174VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
175 ('BO1337', 'The good life', 'Trevor Bartly'),
176 ('PB7583', 'Burning birdges', 'Carl Jarl'),
177 ('AS7548', 'Relation list', 'Simon Timon'),
178 ('BO1234', 'The Lord Of The Rings', 'J.R.R Tolkien'),
179 ('GS1536', 'Lord of the Flies', 'William Golding'),
180 ('PO6845', 'SQL For Dummies', 'Robinson Crusoe');
181
182
183INSERT INTO book_loan(start_date_loan, end_date_loan, book_borrower, book_ID)
184VALUES (20181203, NULL, 'libra180', 'JO5678'),
185 (20111107, 20181224, 'libra180', 'BO1337'),
186 (20140514, 20180713, 'vserf495', 'PB7583' ),
187 (20050315, 20180305, 'babol666', 'AS7548'),
188 (20130325, 20181203, 'lesar427', 'JO5678');
189
190INSERT INTO slide_loan(start_date_loan_s, end_date_loan_s, slide_borrower, slide_ID)
191VALUES (20180302 , NULL, 'libra180', '23-001'),
192 (20161207, 20181224, 'vserf495', '33-001'),
193 (20180514, NULL, 'babol666', '23-002'),
194 (20060315, 20180305, 'lesar427', '33-002');
195
196
197INSERT INTO artifact(number, grid, depth, art_owner, art_date)
198VALUES (25, 45.46, 256, 'libra180', 19450505),
199 (45, 23.27, 322, 'vserf495', 20080905),
200 (38, 10.12, 800, 'lesar427', 19951202),
201 (10, 32.30, 300, 'babol666', 19970706),
202 (13, 55.76, 450, 'johdo321', 20090507),
203 (43, 32.56, 145, 'namna146', 20070928),
204 (66, 45.87, 201, 'jndio123', 20120128),
205 (23, 13.67, 384, 'bombe534', 20070717),
206 (43, 54.98, 158, 'babol666', 20010101),
207 (24, 43.11, 168, 'libra180', 19770325),
208 (67, 85.96, 631, 'vserf495', 20060725),
209 (85, 99.94, 351, 'lesar427', 20020523),
210 (54, 24.62, 842, 'babol666', 20100505),
211 (70, 17.63, 471, 'johdo321', 20130704),
212 (20, 52.73, 279, 'namna146', 19940808),
213 (36, 66.27, 417, 'jndio123', 20170313),
214 (17, 81.47, 749, 'bombe534', 20140224),
215 (63, 53.33, 826, 'babol666', 20021224);
216
217INSERT INTO dig(dig_name, dig_no, dig_date, worker)
218VALUES ('Tomb of Qin Shi Huangdi', 23, 19970904, 'vserf495'),
219 ('Ziggurat of Ur', 33, 18859394, 'babol666'),
220 ('Moche huaca', 55, 19330506, 'lesar427'),
221 ('Pyramids of Giza', 11, 20050304, 'libra180'),
222 ('the Great Pyramid of Khufu', 77, 20130207, 'libra180'),
223 ('Teotihuacan', 88, 20010709, 'babol666'),
224 ('Pyramid of the Sun at Teotihuacan', 84, 20020305, 'lesar427'),
225 ('Sunken temple of Caral', 67, 20070707, 'vserf495'),
226 ('Karazhan', 51, 20031203, 'vserf495'),
227 ('Razorfen downs', 09, 20010112, 'libra180'),
228 ('Uldaman', 99, 20060112, 'libra180'),
229 ('Uldaman', 77, 20000203, 'libra180');
230
231
232-- (4) CREATE VIEWS
233
234
235GO
236CREATE VIEW student_staff AS
237SELECT staff_name, staff_role
238FROM staff
239WHERE staff_role = 'Student'
240GO
241
242SELECT * FROM student_staff
243
244GO
245
246CREATE VIEW liban_artifacts AS
247SELECT number, art_owner
248FROM artifact
249WHERE art_owner = 'libra180'
250GO
251
252SELECT * FROM liban_artifacts
253
254
255-- (5) CREATE TRIGGERS/PROCEDURES
256
257-- foreign keys ska släppas först?