· 7 years ago · Jan 17, 2019, 12:42 PM
1 -- DDL - Data Definition Language - Hur lagret ska se ut; CREATE, DROP...
2
3DROP TRIGGER IF EXISTS delete_employee;
4DROP TRIGGER IF EXISTS set_todays_date;
5DROP VIEW IF EXISTS lent_books_to_employees;
6DROP VIEW IF EXISTS digs_locations;
7DROP VIEW IF EXISTS lent_books_to_students;
8DROP TABLE IF EXISTS artifact;
9DROP TABLE IF EXISTS dig;
10DROP TABLE IF EXISTS dig_location;
11DROP TABLE IF EXISTS slide;
12DROP TABLE IF EXISTS litterature;
13DROP TABLE IF EXISTS employee;
14DROP TABLE IF EXISTS student;
15
16-- EMPLOYEE
17CREATE TABLE employee (
18 PRIMARY KEY (employee_id),
19 employee_id SMALLINT,
20 first_name VARCHAR(25) NOT NULL,
21 last_name VARCHAR(25) NOT NULL,
22 e_role VARCHAR(25) NOT NULL
23 );
24
25-- STUDENT
26CREATE TABLE student (
27 PRIMARY KEY (student_id),
28 student_id SMALLINT,
29 first_name VARCHAR(25) NOT NULL,
30 last_name VARCHAR(25) NOT NULL
31 );
32
33-- LITTERATURE
34--THE CONSTRAINT MAKES SURE THAT BOOKS ARE USED BY ONE PERSON AT THE TIME.
35CREATE TABLE litterature (
36 PRIMARY KEY (litterature_no),
37 litterature_no SMALLINT,
38 title VARCHAR(30) NOT NULL,
39 author VARCHAR(30) NOT NULL,
40 student_id SMALLINT,
41 employee_id SMALLINT,
42 CONSTRAINT [Only one person can loan a book at once.]
43 CHECK( (student_id IS NOT NULL AND employee_id IS NULL) OR
44 (student_id IS NULL AND employee_id IS NOT NULL) OR
45 (student_id IS NULL AND employee_id IS NULL)
46 ),
47 FOREIGN KEY(student_id)
48 REFERENCES student(student_id),
49 FOREIGN KEY(employee_id)
50 REFERENCES employee(employee_id)
51 );
52
53 -- SLIDE
54 -- THE CONSTRAINT CONTROLS THAT SLIDES EITHER ARE LENT OUT OR PUT IN A SPECIFIC SHELF
55CREATE TABLE slide (
56 PRIMARY KEY (slide_no),
57 slide_no SMALLINT,
58 slide_description VARCHAR(50) NOT NULL,
59 loan_date DATE,
60 employee_id SMALLINT,
61 shelf_no CHAR(5),
62 CONSTRAINT [If a slide is not loaned out then it has to have a shelf-number.]
63 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
64 (employee_id IS NOT NULL AND shelf_no IS NULL)
65 ),
66 FOREIGN KEY(employee_id)
67 REFERENCES employee(employee_id)
68 ON DELETE CASCADE
69
70 );
71
72 -- DIG LOCATION
73CREATE TABLE dig_location (
74 PRIMARY KEY (dig_location_no),
75 dig_location_no SMALLINT,
76 location_dig VARCHAR(30) NOT NULL
77 );
78
79 -- DIG
80 --THE CONSTRAINT MAKES SURE A RESEARCHER IS INVOLVED AND THAT A STUDENT CAN'T BE ALONE IN A DIG
81CREATE TABLE dig (
82 PRIMARY KEY (dig_no),
83 dig_no SMALLINT,
84 dig_date DATE NOT NULL,
85 employee_id SMALLINT,
86 student_id SMALLINT,
87 dig_location_no SMALLINT,
88 CONSTRAINT [A researcher has to take part in a dig and a student can not be in a dig alone.]
89 CHECK( (employee_id IS NOT NULL AND student_id IS NULL) OR
90 (employee_id IS NOT NULL AND student_id IS NOT NULL)
91 ),
92 FOREIGN KEY (employee_id)
93 REFERENCES employee(employee_id),
94 FOREIGN KEY (student_id)
95 REFERENCES student(student_id),
96 FOREIGN KEY (dig_location_no)
97 REFERENCES dig_location(dig_location_no)
98 ON DELETE CASCADE
99
100 );
101
102 -- ARTIFACT
103 -- THE CONSTRAINT MAKES SURE NO ARTIFACTS GETS LOST. EITHER AN ARTIFACT IS IN A SHELF OR LENT OUT TO AN EMPLOYEE
104CREATE TABLE artifact (
105 PRIMARY KEY (artifact_no),
106 artifact_no SMALLINT,
107 shelf_no VARCHAR(4),
108 found_date DATE NOT NULL,
109 dig_no SMALLINT,
110 depth VARCHAR(20),
111 employee_id SMALLINT,
112 CONSTRAINT [If an artifact is not loaned out then it has to have a shelf number.]
113 CHECK( (employee_id IS NULL AND shelf_no IS NOT NULL) OR
114 (employee_id IS NOT NULL AND shelf_no IS NULL)
115 ),
116 FOREIGN KEY(employee_id)
117 REFERENCES employee(employee_id),
118 FOREIGN KEY(dig_no)
119 REFERENCES dig(dig_no)
120 ON DELETE CASCADE
121
122
123 );
124
125 -- ADDS EMPLOYEES
126INSERT INTO employee (employee_id, first_name, last_name, e_role)
127 VALUES ('1', 'Torsten', 'Bengtsson', 'Researcher'),
128 ('2', 'Jimmy', 'Larsson', 'Janitor'),
129 ('3', 'Tiger', 'Skog', 'Researcher'),
130 ('4', 'Carl', 'Sone','Researcher'),
131 ('5', 'Moses', 'Mos', 'Assistant'),
132 ('6', 'Kim', 'Källström', 'Master Digger'),
133 ('7', 'Anders', 'Andersson', 'Researcher'),
134 ('8', 'Hasse', 'Ravelli', 'Researcher'),
135 ('9', 'Tomas', 'Tomsson', 'Researcher');
136
137 -- ADDS STUDENTS
138INSERT INTO student (student_id, first_name, last_name)
139 VALUES ('5', 'Mats', 'Olsson'),
140 ('6', 'Maj', 'Snok'),
141 ('7', 'Lego', 'Larsson'),
142 ('8', 'Kanye', 'Bach');
143
144 -- ADDS BOOKS
145INSERT INTO litterature (litterature_no, title, author, student_id, employee_id)
146 VALUES ('10', 'Mazarin', 'George Orwell', null, '1'),
147 ('11', 'Asaklitt', 'Active Life', null, null),
148 ('12', 'Termosmugg', 'ICA CE', null, null),
149 ('13', 'Jag är fotboll', 'Zlatan', '5', null),
150 ('14', 'Lazarus', 'Lars Kepler', null, null),
151 ('15', 'Mamma Mu låtsas', 'Sven Nordqvist', '6', null),
152 ('16','Sju dagar med familjen', 'Francesca Hornak', null, null),
153 ('17', 'Jag kommer hem till jul', 'Joanna Bolouri', null, null),
154 ('18', 'Min historia', 'Michelle Obama', null, '3'),
155 ('19', '1793', 'Niklas Natt', null, null),
156 ('20', 'Ormen i Essex', 'Sarah Perry', null, '2'),
157 ('21', 'En annan Alice', 'Liane Moriarty', null, null),
158 ('22', 'Bränn alla mina brev', 'Alex Schulman', null, '1'),
159 ('23', 'Rymlingarna', 'Ulf Stark', null, null),
160 ('24', 'Min mammas hemlighet', 'Nikola Scott', '7', null),
161 ('25', 'Den frusna trädgården', 'Kristin Hannah', null, null),
162 ('26', 'Himmelskriket', 'Mons Kallentoft', null, null),
163 ('27', 'PÃ¥ hal is', 'Jeff Kinney', null, null),
164 ('28', 'Silvermysteriet', 'Martin Widmark', '8', null),
165 ('29', 'Sagor från Mumindalen', 'Tove Jansson', null, '4'),
166 ('30', 'Alfons hemliga världar', 'Gunilla Bergström', null, null),
167 ('31', 'Spöksystrar', 'Mårten Melin', null, null),
168 ('32', 'Mumiens gåta', 'Kristina Ohlsson', null, null),
169 ('33', 'Mareld', 'Camilla Sten', null, null),
170 ('34', 'Pappa Polis', 'Laura Trenter', null, null);
171
172 -- ADDS SLIDES
173INSERT INTO slide(slide_no, slide_description, loan_date, employee_id, shelf_no)
174 VALUES ('35', 'lorem ipsum', '05-jan-1970', null, '00001'),
175 ('36', 'lorem ipsum', '05-jan-1970', null, '00002'),
176 ('37', 'lorem ipsum', '05-jan-1970', '1', null),
177 ('38', 'lorem ipsum', '05-jan-1970', '1', null),
178 ('39', 'lorem ipsum', '05-jan-1970', '3', null),
179 ('40', 'lorem ipsum', '05-jan-1970', '2', null),
180 ('41', 'lorem ipsum', '25-jun-2011', null, '00007'),
181 ('42', 'lorem ipsum', '25-jun-2011', null, '00008'),
182 ('43', 'lorem ipsum', '25-jun-2011', null, '00009'),
183 ('44', 'lorem ipsum', '25-jun-2011', '4', null),
184 ('45', 'lorem ipsum', '25-jun-2011', '4', null),
185 ('46', 'lorem ipsum', null, null, '00012'),
186 ('47', 'lorem ipsum', null, null, '00013'),
187 ('48', 'lorem ipsum', null, null, '00014'),
188 ('49', 'lorem ipsum', null, null, '00015'),
189 ('50', 'lorem ipsum', null, null, '00016');
190
191
192 -- ADDS ARTIFACTS
193INSERT INTO artifact (artifact_no, shelf_no, found_date, dig_no, depth, employee_id)
194 VALUES ('51', null, '03-may-2010', '88', '200 cm', '2'),
195 ('52', null, '03-may-2010', '88', '200 cm', '2'),
196 ('53', '69B', '03-may-2010','88', '200 cm', null),
197 ('50', null, '03-may-2010', '88', '200 cm', '2'),
198 ('54', null, '03-may-2010', '88', '200 cm', '2'),
199 ('55', '69B', '03-may-2010', '88', '200 cm', null),
200 ('56', '69B', '03-may-2010', '88', '200 cm', null),
201 ('57', null, '03-may-2010', '88', '200 cm', '2'),
202 ('58', null, '03-may-2010', '88', '200 cm', '2'),
203 ('59', '45A', '25-jun-2011', '89', '105 cm', null),
204 ('60', '45A', '25-jun-2011', '89', '105 cm', null),
205 ('61', '45A', '25-jun-2011', '89', '105 cm', null),
206 ('62', '45A', '25-jun-2011', '89', '105 cm', null),
207 ('63', '45A', '25-jun-2011', '89', '105 cm', null),
208 ('64', '45A', '25-jun-2011', '89', '105 cm', null),
209 ('65', '45A', '25-jun-2011', '89', '105 cm', null),
210 ('66', '45A', '25-jun-2011', '89', '105 cm', null),
211 ('67', '45A', '25-jun-2011', '89', '105 cm', null),
212 ('68', '45A', '25-jun-2011', '89', '105 cm', null),
213 ('69', '45A', '25-jun-2011', '89', '105 cm', null),
214 ('70', '12F', '17-feb-2012', '90', '265 cm', null),
215 ('71', '12F', '17-feb-2012', '90', '265 cm', null),
216 ('72', '12F', '17-feb-2012', '90', '265 cm', null),
217 ('73', '12F', '17-feb-2012', '90', '265 cm', null),
218 ('74', '12F', '17-feb-2012', '90', '265 cm', null),
219 ('75', '12F', '17-feb-2012', '90', '265 cm', null),
220 ('76', '12F', '17-feb-2012', '90', '265 cm', null),
221 ('77', '12F', '17-feb-2012', '90', '265 cm', null),
222 ('78', null, '12-mar-2012', '91', '140 cm', '1'),
223 ('79', null, '12-mar-2012', '91', '140 cm', '1'),
224 ('80', null, '12-mar-2012', '91', '140 cm', '1'),
225 ('81', null, '12-mar-2012', '91', '140 cm', '1');
226
227 --ADDS DIG LOCATON (the place of discovery)
228INSERT INTO dig_location (dig_location_no, location_dig)
229 VALUES ('82', 'Linköping'),
230 ('83', 'Norrköping'),
231 ('84', 'Mjölby'),
232 ('85', 'Motala'),
233 ('86', 'Boxholm'),
234 ('87', 'Grebo');
235
236 -- ADDS DIG
237INSERT INTO dig(dig_no, dig_date, employee_id, student_id, dig_location_no)
238 VALUES ('88', '03-may-2010', '1', null, '82'),
239 ('89', '25-jun-2011', '3', null, '83'),
240 ('90', '17-feb-2012', '2', '7', '84'),
241 ('91', '12-mar-2012', '4', '8', '85'),
242 ('92', '12-mar-2012', '1', '6', '86'),
243 ('93', '12-mar-2011', '1', '6', '82'),
244 ('94', '12-mar-2011', '1', '6', '82'),
245 ('95', '12-mar-2011', '2', '5', '82'),
246 ('96', '23-aug-2014', '3', '8', '87');
247
248-- Views
249-- SHOWS MORE INFORMATION ABOUT THE EMPLOYEES THAT BORROWED BOOKS
250
251GO
252CREATE VIEW lent_books_to_employees AS
253 SELECT l.employee_id AS 'employee_ID' ,
254 l.litterature_no AS 'book_number',
255 l.title AS 'book_title',
256 e.first_name AS 'first_name_employee',
257 e.last_name AS 'last_name_employee',
258 e.e_role AS 'employee_role'
259 FROM litterature AS l
260 INNER JOIN employee AS e
261 ON e.employee_id=l.employee_id
262
263GO
264
265GO
266-- SHOWS MORE INFORMATION ABOUT THE STUDENTS THAT BORROWED BOOKS
267CREATE VIEW lent_books_to_students AS
268 SELECT l.student_id AS 'employee_ID' ,
269 l.litterature_no AS 'book_number',
270 l.title AS 'book_title',
271 s.first_name AS 'first_name_student',
272 s.last_name AS 'last_name_student'
273 FROM litterature AS l
274 INNER JOIN student AS s
275 ON s.student_id=l.student_id
276
277GO
278
279-- SHOWS PARTS OF DIG LOCATION AND DIG IN ONE TABELE
280GO
281CREATE VIEW digs_locations AS
282 SELECT d.dig_date AS 'date_of_dig',
283 d.dig_no AS 'dig_number',
284 dl.location_dig AS 'location_of_dig'
285 FROM dig AS d
286 INNER JOIN dig_location AS dl
287 ON d.dig_location_no=dl.dig_location_no
288GO
289
290-- Trigger
291-- If you want to update an employee, you will receive a nice message
292CREATE TRIGGER updating_employee
293 ON employee
294 AFTER UPDATE
295 AS BEGIN
296 IF UPDATE(e_role)
297 PRINT 'Your employee`s title has changed, good for that person'
298 IF UPDATE(last_name)
299 PRINT 'Your employee has gotten married or divorced, good or bad'
300 IF UPDATE(first_name)
301 PRINT 'Your employee has changed name, hope its a good name'
302 END;
303 GO