zVV7GW2a

1--DDL
2
3-- (1) DROPS
4
5DROP TABLE IF EXISTS book_loan;
6DROP TABLE IF EXISTS slide_loan;
7DROP TABLE IF EXISTS slides;
8DROP TABLE IF EXISTS books;
9DROP TABLE IF EXISTS book_shelf;
10DROP TABLE IF EXISTS slide_shelf;
11DROP TABLE IF EXISTS artifact_card;
12DROP TABLE IF EXISTS location_segment;
13DROP TABLE IF EXISTS dig;
14DROP TABLE IF EXISTS artifact;
15DROP TABLE IF EXISTS staff;
16
17
18-- (2) CREATE TABLES
19/*
20CREATE TABLE staff(
21		PRIMARY KEY(staff_ID),
22		staff_ID		VARCHAR(8)     NOT NULL,
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),
30		number		INT                NOT NULL,
31		grid		INT                NOT NULL,
32		depth		INT                NOT NULL,
33		art_owner	VARCHAR(8),
34		FOREIGN KEY (art_owner)
35		REFERENCES staff(staff_ID)
36);
37
38CREATE TABLE dig(
39		DigName		VARCHAR(20)       NOT NULL,
40		DigNo		INT               NOT NULL,
41		DigDate		VARCHAR(10),
42		PRIMARY KEY(DigName, DigNo)
43);
44
45CREATE TABLE location_segment(
46		location_ID		VARCHAR(8)    NOT NULL,
47		PRIMARY KEY(location_ID)
48);
49
50CREATE TABLE artifact_card(
51		Notes VARCHAR(140)
52);
53
54CREATE TABLE books(
55		book_number	VARCHAR(6)        NOT NULL,
56		title		VARCHAR(30)       NOT NULL,
57		author		VARCHAR(30)       NOT NULL,
58		PRIMARY KEY(book_number));
59
60CREATE TABLE slides(
61		slide_ID		VARCHAR(6)  NOT NULL,
62		title		    VARCHAR(30) NOT NULL,
63		author		    VARCHAR(30) NOT NULL,
64		slide_category  VARCHAR(30) NOT NULL,
65		PRIMARY KEY(slide_ID)
66);
67
68CREATE TABLE slide_loan(
69		PRIMARY KEY(start_date),
70		start_date		INT,
71		slide_borrower		VARCHAR(8),
72		FOREIGN KEY (slide_borrower)
73		REFERENCES staff(staff_ID),
74);
75
76CREATE TABLE book_loan(
77		PRIMARY KEY(start_date),
78		start_date		INT,
79		book_borrower		VARCHAR(8),
80		FOREIGN KEY (book_borrower)
81		REFERENCES staff(staff_ID),
82);
83*/
84-- från william >
85
86
87CREATE TABLE staff(
88		PRIMARY KEY(staff_ID),
89		staff_ID		VARCHAR(8),
90		staff_role		VARCHAR(20)    NOT NULL,
91		staff_name		VARCHAR(30)	   NOT NULL,
92		date_registered VARCHAR(10)	   NOT NULL,
93);
94
95CREATE TABLE artifact(
96		PRIMARY KEY(number, grid, depth),
97		number		INT,
98		grid		INT,
99		depth		INT,
100		art_owner	VARCHAR(8),
101		FOREIGN KEY (art_owner)
102		REFERENCES staff(staff_ID)
103);
104
105CREATE TABLE dig(
106		dig_name	VARCHAR(20)   NOT NULL,
107		dig_no		INT,
108		dig_date	VARCHAR(10),
109		worker		VARCHAR(8),
110		PRIMARY KEY(dig_name, dig_no),
111		FOREIGN KEY (worker)
112		REFERENCES staff(staff_ID)
113);
114
115CREATE TABLE location_segment(
116		location_ID		VARCHAR(8),
117		PRIMARY KEY(location_ID)
118);
119
120
121CREATE TABLE artifact_card(
122		Notes			VARCHAR(140),
123		card_ID			INT,
124		current_pos		VARCHAR(8),
125		dig_origin_no	INT,
126		dig_origin_name	VARCHAR(20),
127		dig_grid		INT,
128		dig_depth		INT,
129		FOREIGN KEY (card_ID, dig_grid, dig_depth)
130		REFERENCES artifact(number, grid, depth),
131		FOREIGN KEY (current_pos)
132		REFERENCES location_segment(location_ID),
133		FOREIGN KEY (dig_origin_no, dig_origin_name)
134		REFERENCES dig(dig_no, dig_name),
135
136);
137
138CREATE TABLE slide_shelf(
139		s_shelf_number	VARCHAR(5),
140		PRIMARY KEY (s_shelf_number)
141);
142
143CREATE TABLE book_shelf(
144		b_shelf_number	VARCHAR(5),
145		PRIMARY KEY (b_shelf_number)
146);
147
148
149CREATE TABLE books(
150		book_number	VARCHAR(6),
151		title		VARCHAR(30) NOT NULL,
152		author		VARCHAR(30) NOT NULL,
153		home_shelf	VARCHAR(5),
154		PRIMARY KEY(book_number),
155		FOREIGN KEY (home_shelf)
156		REFERENCES book_shelf(b_shelf_number)
157
158);
159
160CREATE TABLE slides(
161		slide_number	VARCHAR(6),
162		title		    VARCHAR(30) NOT NULL,
163		author		    VARCHAR(30),
164		slide_category  VARCHAR(30),
165		home_shelf		VARCHAR(5),
166		PRIMARY KEY(slide_number),
167		FOREIGN KEY (home_shelf)
168		REFERENCES slide_shelf(s_shelf_number)
169);
170
171CREATE TABLE slide_loan(
172		start_date_loan		INT,
173		slide_borrower		VARCHAR(8),
174		slide_ID			VARCHAR(6),
175		PRIMARY KEY(start_date_loan),
176		FOREIGN KEY (slide_borrower)
177		REFERENCES staff(staff_ID),
178		FOREIGN KEY (slide_ID)
179		REFERENCES slides(slide_number),
180);
181
182CREATE TABLE book_loan(
183
184		start_date_loan		INT,
185		book_borrower		VARCHAR(8),
186		book_ID				VARCHAR(6),
187		PRIMARY KEY(start_date_loan),
188		FOREIGN KEY (book_borrower)
189		REFERENCES staff(staff_ID),
190		FOREIGN KEY (book_ID)
191		REFERENCES books(book_number)
192);
193-- slut från william
194
195	
196
197
198-- (3) INSERT VALUES
199INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
200VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
201      ('vserf495', 'Excavator', 'Vsera rfoman', 20080905),
202	  ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
203	  ('babol666', 'Excavator', 'Barbro Olev', 19970706);
204
205INSERT INTO books(book_number,title,author)
206VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
207      ('BO1337', 'The good life', 'Trevor Bartly'),
208	  ('PB7583', 'Burning birdges', 'Carl Jarl'),
209	  ('AS7548', 'Relation list', 'Simon Timon');
210
211 
212-- (4) CREATE VIEWS
213
214
215 
216-- (5) CREATE TRIGGERS/PROCEDURES
217
218-- foreign keys ska släppas först?