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