· 7 years ago · Dec 04, 2018, 03:34 PM
1 -- DDL - Data Definition Language - Hur lagret ska se ut; CREATE, DROP...
2
3DROP TABLE IF EXISTS dig;
4DROP TABLE IF EXISTS dig_location;
5
6DROP TABLE IF EXISTS artifact;
7DROP TABLE IF EXISTS slide;
8DROP TABLE IF EXISTS litterature;
9DROP TABLE IF EXISTS employee;
10DROP TABLE IF EXISTS student;
11
12CREATE TABLE employee (
13 PRIMARY KEY (employee_id),
14 employee_id SMALLINT,
15 first_name VARCHAR(25) NOT NULL,
16 last_name VARCHAR(25) NOT NULL,
17 e_role VARCHAR(25) NOT NULL
18 );
19
20CREATE TABLE student (
21 PRIMARY KEY (student_id),
22 student_id SMALLINT,
23 first_name VARCHAR(25) NOT NULL,
24 last_name VARCHAR(25) NOT NULL
25 );
26
27CREATE TABLE litterature (
28 PRIMARY KEY (litterature_no),
29 litterature_no SMALLINT,
30 title VARCHAR(30) NOT NULL,
31 author VARCHAR(30) NOT NULL,
32 student_loanee SMALLINT,
33 employee_loanee SMALLINT,
34 CONSTRAINT [Only one person can loan a book at once.]
35 CHECK( (student_loanee IS NOT NULL AND employee_loanee IS NULL) OR
36 (student_loanee IS NULL AND employee_loanee IS NOT NULL) OR
37 (student_loanee IS NULL AND employee_loanee IS NULL)
38 ),
39 FOREIGN KEY(student_loanee)
40 REFERENCES student(student_id),
41 FOREIGN KEY(employee_loanee)
42 REFERENCES employee(employee_id)
43 );
44
45CREATE TABLE slide (
46 PRIMARY KEY (slide_no),
47 slide_no SMALLINT,
48 slide_description VARCHAR(50) NOT NULL,
49 loan_date DATE,
50 employee_loanee SMALLINT,
51 shelf_no CHAR(5),
52 CONSTRAINT [If a slide is not loaned out then it has to have a shelf-number.]
53 CHECK( (employee_loanee IS NULL AND shelf_no IS NOT NULL) OR
54 (employee_loanee IS NOT NULL AND shelf_no IS NULL)
55 ),
56 FOREIGN KEY(employee_loanee)
57 REFERENCES employee(employee_id)
58 );
59
60CREATE TABLE dig_location (
61 PRIMARY KEY (dig_location_no),
62 dig_location_no SMALLINT,
63 location_dig VARCHAR(30) NOT NULL
64 );
65
66
67CREATE TABLE dig (
68 PRIMARY KEY (dig_no),
69 dig_no SMALLINT,
70 dig_date DATE NOT NULL,
71 employee_digger SMALLINT,
72 student_digger SMALLINT,
73 dig_location_no SMALLINT,
74 CONSTRAINT [A researcher has to take part in a dig and a student can not be in a dig alone.]
75 CHECK( (employee_digger IS NOT NULL AND student_digger IS NULL) OR
76 (employee_digger IS NOT NULL AND student_digger IS NOT NULL)
77 ),
78 FOREIGN KEY (employee_digger)
79 REFERENCES employee(employee_id),
80 FOREIGN KEY (student_digger)
81 REFERENCES student(student_id),
82 FOREIGN KEY (dig_location_no)
83 REFERENCES dig_location(dig_location_no)
84 );
85
86
87
88CREATE TABLE artifact (
89 PRIMARY KEY (artifact_no),
90 artifact_no SMALLINT,
91 shelf_no VARCHAR(4),
92 found_date DATE NOT NULL,
93 depth VARCHAR(20),
94 employee_loanee SMALLINT,
95 CONSTRAINT [If an artifact is not loaned out then it has to have a shelf-number.]
96 CHECK( (employee_loanee IS NULL AND shelf_no IS NOT NULL) OR
97 (employee_loanee IS NOT NULL AND shelf_no IS NULL)
98 ),
99 FOREIGN KEY(employee_loanee)
100 REFERENCES employee(employee_id)
101
102 );
103
104 INSERT INTO employee (employee_id, first_name, last_name, e_role)
105 VALUES ('1', 'Torsten', 'Bengtsson', 'Researcher'),
106 ('2', 'Jimmy', 'Larsson', 'Researcher'),
107 ('3', 'Tiger', 'Skog', 'Researcher'),
108 ('4', 'Carl', 'Sone','Researcher');
109
110INSERT INTO student (student_id, first_name, last_name)
111 VALUES ('5', 'Mats', 'Olsson'),
112 ('6', 'Maj', 'Snok'),
113 ('7', 'Lego', 'Larsson'),
114 ('8', 'Kanye', 'Bach');
115
116INSERT INTO litterature (litterature_no, title, author, student_loanee, employee_loanee)
117 VALUES ('10', 'Mazarin', 'George Orwell', null, '1'),
118 ('11', 'Asaklitt', 'Active Life', null, null),
119 ('12', 'Termosmugg', 'ICA CE', null, null),
120 ('13', 'Jag är fotboll', 'Zlatan', '5', null),
121 ('14', 'Lazarus', 'Lars Kepler', null, null),
122 ('15', 'Mamma Mu låtsas', 'Sven Nordqvist', '6', null),
123 ('16','Sju dagar med familjen', 'Francesca Hornak', null, null),
124 ('17', 'Jag kommer hem till jul', 'Joanna Bolouri', null, null),
125 ('18', 'Min historia', 'Michelle Obama', null, '3'),
126 ('19', '1793', 'Niklas Natt', null, null),
127 ('20', 'Ormen i Essex', 'Sarah Perry', null, '2'),
128 ('21', 'En annan Alice', 'Liane Moriarty', null, null),
129 ('22', 'Bränn alla mina brev', 'Alex Schulman', null, '1'),
130 ('23', 'Rymlingarna', 'Ulf Stark', null, null),
131 ('24', 'Min mammas hemlighet', 'Nikola Scott', '7', null),
132 ('25', 'Den frusna trädgården', 'Kristin Hannah', null, null),
133 ('26', 'Himmelskriket', 'Mons Kallentoft', null, null),
134 ('27', 'PÃ¥ hal is', 'Jeff Kinney', null, null),
135 ('28', 'Silvermysteriet', 'Martin Widmark', '8', null),
136 ('29', 'Sagor från Mumindalen', 'Tove Jansson', null, '4'),
137 ('30', 'Alfons hemliga världar', 'Gunilla Bergström', null, null),
138 ('31', 'Spöksystrar', 'Mårten Melin', null, null),
139 ('32', 'Mumiens gåta', 'Kristina Ohlsson', null, null),
140 ('33', 'Mareld', 'Camilla Sten', null, null),
141 ('34', 'Pappa Polis', 'Laura Trenter', null, null);
142
143 -- SÄTT IN SHEL NUBER
144INSERT INTO slide(slide_no, slide_description, loan_date, employee_loanee, shelf_no)
145 VALUES ('35', 'lorem ipsum', '05-jan-1970', null, '00001'),
146 ('36', 'lorem ipsum', '05-jan-1970', null, '00002'),
147 ('37', 'lorem ipsum', '05-jan-1970', '1', null),
148 ('38', 'lorem ipsum', '05-jan-1970', '1', null),
149 ('39', 'lorem ipsum', '05-jan-1970', '3', null),
150 ('40', 'lorem ipsum', '05-jan-1970', '2', null),
151 ('41', 'lorem ipsum', '12-aug-1989', null, '00007'),
152 ('42', 'lorem ipsum', '12-aug-1989', null, '00008'),
153 ('43', 'lorem ipsum', '12-aug-1989', null, '00009'),
154 ('44', 'lorem ipsum', '12-aug-1989', '4', null),
155 ('45', 'lorem ipsum', '12-aug-1989', '4', null),
156 ('46', 'lorem ipsum', null, null, '00012'),
157 ('47', 'lorem ipsum', null, null, '00013'),
158 ('48', 'lorem ipsum', null, null, '00014'),
159 ('49', 'lorem ipsum', null, null, '00015'),
160 ('50', 'lorem ipsum', null, null, '00016');
161
162
163
164INSERT INTO artifact (artifact_no, shelf_no, found_date, depth, employee_loanee)
165 VALUES ('51', null, '25-jan-1979', '200 cm', '2'),
166 ('52', null, '25-jan-1979', '200 cm', '2'),
167 ('53', '69B', '25-jan-1979', '200 cm', null),
168 ('54', null, '25-jan-1979', '200 cm', '2'),
169 ('55', '69B', '25-jan-1979', '200 cm', null),
170 ('56', '69B', '25-jan-1979', '200 cm', null),
171 ('57', null, '25-jan-1979', '200 cm', '2'),
172 ('58', null, '25-jan-1979', '200 cm', '2'),
173 ('59', '45A', '12-aug-1989', '105 cm', null),
174 ('60', '45A', '12-aug-1989', '105 cm', null),
175 ('61', '45A', '12-aug-1989', '105 cm', null),
176 ('62', '45A', '12-aug-1989', '105 cm', null),
177 ('63', '45A', '12-aug-1989', '105 cm', null),
178 ('64', '45A', '12-aug-1989', '105 cm', null),
179 ('65', '45A', '12-aug-1989', '105 cm', null),
180 ('66', '45A', '12-aug-1989', '105 cm', null),
181 ('67', '45A', '12-aug-1989', '105 cm', null),
182 ('68', '45A', '12-aug-1989', '105 cm', null),
183 ('69', '45A', '12-aug-1989', '105 cm', null),
184 ('70', '12F', '03-mar-1991', '265 cm', null),
185 ('71', '12F', '03-mar-1991', '265 cm', null),
186 ('72', '12F', '03-mar-1991', '265 cm', null),
187 ('73', '12F', '03-mar-1991', '265 cm', null),
188 ('74', '12F', '03-mar-1991', '265 cm', null),
189 ('75', '12F', '03-mar-1991', '265 cm', null),
190 ('76', '12F', '03-mar-1991', '265 cm', null),
191 ('77', '12F', '03-mar-1991', '265 cm', null),
192 ('78', null, '20-may-1990', '140 cm', '1'),
193 ('79', null, '20-may-1990', '140 cm', '1'),
194 ('80', null, '20-may-1990', '140 cm', '1'),
195 ('81', null, '20-may-1990', '140 cm', '1');
196
197INSERT INTO dig_location (dig_location_no, location_dig)
198VALUES ('82', 'Linköping'),
199 ('83', 'Norrköping'),
200 ('84', 'Mjölby'),
201 ('85', 'Motala'),
202 ('86', 'Boxholm'),
203 ('87', 'Grebo');
204
205
206INSERT INTO dig(dig_no, dig_date, employee_digger, student_digger, dig_location_no)
207VALUES ('88', '03-may-2010', '1', '5', '82'),
208 ('89', '25-jun-2011', '3', null, '83'),
209 ('90', '17-feb-2012', '2', '7', '84'),
210 ('91', '12-mar-2012', '4', '8', '85'),
211 ('92', '12-mar-2012', '1', '6', '86'),
212 ('93', '23-aug-2014', '1', '5', '87');
213
214
215
216
217
218 SELECT * FROM litterature;
219 SELECT * FROM employee;
220 SELECT * FROM student;
221 SELECT * FROM slide;
222 SELECT * FROM artifact;
223 SELECT * FROM dig;