· 7 years ago · Dec 03, 2018, 10:04 AM
1-- DDL
2-- (1) DROPS
3-- (2) CREATE TABLES
4-- (3) INSERT VALUES
5-- (4) CREATE VIEWS
6-- (5) CREATE TRIGGERS/PROCEDURES
7
8
9DROP TABLE IF EXISTS artifact;
10DROP TABLE IF EXISTS lab_shelf;
11DROP TABLE IF EXISTS dig;
12DROP TABLE IF EXISTS slide;
13DROP TABLE IF EXISTS book;
14DROP TABLE IF EXISTS journal;
15DROP TABLE IF EXISTS person;
16
17DROP VIEW IF EXISTS Employees;
18DROP VIEW IF EXISTS Students;
19DROP VIEW IF EXISTS Artifacts_Report;
20
21-- Person (person ID (PK), name, faculty, role
22
23CREATE TABLE person (
24 PRIMARY KEY (person_id),
25 person_id VARCHAR(11),
26 p_firstname VARCHAR(15) NOT NULL,
27 p_lastname VARCHAR(15) NOT NULL,
28 p_faculty VARCHAR(30) NOT NULL,
29 p_role VARCHAR(30) NOT NULL,
30
31);
32
33
34
35
36-- Lab Shelf (shelf_id (PK), shelf position, row)
37CREATE TABLE lab_shelf (
38 PRIMARY KEY (shelf_id),
39 shelf_id VARCHAR(4),
40 s_row INT NOT NULL,
41);
42
43-- Dig (dig ID(PK), person ID(FK), dig site, dig equipment, digital documentation)
44
45CREATE TABLE dig (
46 PRIMARY KEY (dig_id),
47 dig_id INT,
48 d_site VARCHAR(20) NOT NULL,
49 d_equipment VARCHAR(50),
50 documentation VARCHAR(100),
51);
52
53-- Slide (slide ID(PK), person ID (FK), description, dig number, seq number)
54
55CREATE TABLE slide (
56 PRIMARY KEY (slide_id),
57 slide_id VARCHAR(20),
58 s_description VARCHAR(20),
59 dig_number INT NOT NULL,
60 seq_number INT NOT NULL,
61 person_id VARCHAR(11),
62 FOREIGN KEY (person_id)
63 REFERENCES person(person_id)
64);
65
66-- Book (
67CREATE TABLE book (
68 PRIMARY KEY (book_id),
69 book_id INT,
70 author_name VARCHAR(30) NOT NULL,
71 publisher VARCHAR(30) NOT NULL,
72 barcode INT NOT NULL,
73 );
74
75CREATE TABLE journal (
76 PRIMARY KEY (journal_id),
77 journal_id INT,
78 author_name VARCHAR(30) NOT NULL,
79 publisher VARCHAR(30) NOT NULL,
80);
81 -- Artifact (artifact ID (PK), person ID (FK), shelf ID (FK), dig ID (FK), description.
82CREATE TABLE artifact (
83 PRIMARY KEY (artifact_id),
84 artifact_id VARCHAR(5),
85 a_description VARCHAR(100) NOT NULL,
86 person_id VARCHAR(11),
87 shelf_id VARCHAR(4),
88 dig_id INT,
89 FOREIGN KEY (person_id)
90 REFERENCES person(person_id),
91 FOREIGN KEY (shelf_id)
92 REFERENCES lab_shelf(shelf_id),
93 FOREIGN KEY (dig_id)
94 REFERENCES dig(dig_id),
95 );
96
97
98/* Student, Field researcher, slide librarian, museum librarian */
99INSERT INTO person (person_id, p_firstname, p_lastname, p_faculty, p_role)
100 VALUES ('930521-5543', 'Markus', 'Johansson', 'Archeological Faculty', 'Student'),
101 ('860523-1314', 'Truls', 'Joelsson', 'Archeological faculty', 'Field Researcher'),
102 ('870523-2487', 'Kruls', 'Poelsson', 'Archeological faculty', 'Slide Librarian'),
103 ('681011-9965', 'Erik', 'Babbensson', 'Archeological faculty', 'Student'),
104 ('050528-6541', 'Keno', 'Lettovinne', 'Archeological faculty', 'Director'),
105 ('760526-4343', 'Flemming', 'Dansk', 'Archeological faculty', 'Museum Librarian'),
106 ('370522-5442', 'Indiana', 'John', 'Archeological faculty', 'Lab Supervisor'),
107 ('860523-5465', 'Petder', 'Pikatchu', 'Archeological faculty', 'Field Researcher'),
108 ('800523-7815', 'Toel', 'Dillonsson', 'Archeological faculty', 'Field Researcher'),
109 ('840524-1214', 'Dillon', 'Haggarmannen', 'Archeological faculty', 'Student'),
110 ('960205-6216', 'Hannes', 'Hackersson', 'Archeological faculty', 'Student'),
111 ('830625-2519', 'Oskar', 'Von Sfvinhufvud', 'Archeological faculty', 'Field Researcher'),
112 ('821223-8921', 'Masse', 'Mattsson', 'Archeological faculty', 'Field Researcher'),
113 ('831027-5234', 'Johanna', 'Isakasson', 'Archeological faculty', 'Student'),
114 ('810921-5516', 'Johan', 'Svensson', 'Archeological faculty', 'Student'),
115 ('750317-8212', 'Joakim', 'Storboga', 'Archeological faculty', 'Student');
116
117INSERT INTO lab_shelf (shelf_id, s_row)
118 VALUES ('77B', 1),
119 ('28C', 1),
120 ('69B', 2),
121 ('124B', 2),
122 ('1A', 1),
123 ('2A', 1),
124 ('3A', 1),
125 ('4A', 2),
126 ('5A', 2),
127 ('6A', 2),
128 ('7A', 3),
129 ('8A', 3),
130 ('9A', 3),
131 ('10B', 1),
132 ('11B', 1),
133 ('12B', 1),
134 ('13B', 2),
135 ('14B', 2),
136 ('15B', 2),
137 ('16B', 3),
138 ('17B', 3),
139 ('18B', 3),
140 ('19C', 1),
141 ('20C', 1),
142 ('21C', 1),
143 ('22C', 2),
144 ('23C', 2),
145 ('24C', 2),
146 ('25C', 3),
147 ('26C', 3),
148 ('27D', 1),
149 ('288D', 1),
150 ('29D', 2),
151 ('30D', 2);
152
153INSERT INTO dig (dig_id, documentation, d_equipment, d_site)
154 VALUES (300, 'Digital Documentation', 'Shovel', 'Hannes backyard'),
155 (301, 'Digital Documentation', 'Drill', 'Flemmingsberg'),
156 (302, 'Digital Documentation', 'Shovel and Drill', 'Ängelholm'),
157 (303, 'Digital Documentation', 'Plastic Shovel & Plastic Bucket', 'Tofta Beachclub'),
158 (304, 'Digital Documentation', 'Jack Vegas Machine', 'Säffle Bar&Krog'),
159 (305, 'Digital Documentation', 'Nose', 'Näs');
160
161INSERT INTO artifact (artifact_id, a_description, person_id, shelf_id, dig_id)
162 VALUES ('00001', 'A small rauk from Fårö', '860523-1314', '10B', 300),
163 ('00002', 'Old rocket ship from Mars', '800523-7815', '69B', 301),
164 ('00003', 'Koenigsegg', '860523-5465', '124B', 302),
165 ('00004', 'Schtekare', '860523-5465', '288D', 303);
166
167
168/* Vy över museets anställda. */
169GO
170CREATE VIEW Employees AS
171 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
172 FROM person AS P
173 WHERE P.p_role != 'Student'
174GO
175
176/* Vy över museets studenter. */
177GO
178CREATE VIEW Students AS
179 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
180 FROM person AS P
181 WHERE P.p_role = 'Student'
182GO
183
184/* Vy över artefakter i museet. */
185GO
186CREATE VIEW Artifacts_Report AS
187 SELECT A.artifact_id AS 'ID', A.a_description AS 'Description', P.p_lastname + ', ' + P.p_firstname AS 'Found By', D.d_site AS 'Dig Site', L.shelf_id AS 'Shelf Location'
188 FROM artifact AS A
189 INNER JOIN person AS P
190 ON P.person_id = A.person_id
191 INNER JOIN lab_shelf AS L
192 ON L.shelf_id = A.shelf_id
193 INNER JOIN dig AS D
194 ON D.dig_id = A.dig_id
195GO
196
197SELECT * FROM Employees
198
199SELECT * FROM Students
200
201SELECT * FROM Artifacts_Report