fUzCqUaM

1DROP TABLE IF EXISTS loans, displayed_artifact, artifact_category, slide_category, slide,  artifact, warehouse, borrower, categories, books_journals, dig_site, staff;
2
3CREATE TABLE staff(
4		PRIMARY KEY (staff_num),
5		staff_num				INT IDENTITY(1,1)	NOT NULL UNIQUE,
6		staff_name				VARCHAR(40)			NOT NULL,
7		title					VARCHAR(30)			NOT NULL,
8		phone					VARCHAR(10)			NOT NULL,
9		email					VARCHAR(50)			NOT NULL
10);
11
12CREATE TABLE dig_site(
13		PRIMARY KEY (dig_site_num),
14		dig_site_num			INT IDENTITY(1,1)	NOT NULL UNIQUE,
15		grid_x_max				VARCHAR(3)			NOT NULL,
16		grid_y_max				INT					NOT NULL,
17		dig_site_name			VARCHAR(40)			NOT NULL UNIQUE
18);
19
20CREATE TABLE books_journals(
21		PRIMARY KEY (book_num),
22		book_num				INT IDENTITY(1,1)	NOT NULL UNIQUE,
23		book_title				VARCHAR(255)			NOT NULL,
24		author					VARCHAR(255)			NOT NULL,
25		ISBN					VARCHAR(13)			NULL 
26);
27
28CREATE TABLE categories(
29		PRIMARY KEY (category_num),
30		category_num			INT IDENTITY(1,1)	NOT NULL UNIQUE,
31		category_name			VARCHAR(30)			NOT NULL UNIQUE,
32		category_description	TEXT				NOT NULL
33);
34
35CREATE TABLE borrower(
36		PRIMARY KEY (borrower_num),
37		borrower_num			INT					NOT NULL UNIQUE,
38		borrower_name			VARCHAR(40)			NOT NULL,
39		email					VARCHAR(50)			NOT NULL
40);
41
42CREATE TABLE warehouse(
43		PRIMARY KEY (shelf_num),
44		shelf_num				VARCHAR(4)	NOT NULL UNIQUE,
45--		artifact_num			INT					NULL UNIQUE
46);
47
48CREATE TABLE artifact(
49		PRIMARY KEY (artifact_num),
50		artifact_num			INT IDENTITY(1,1)	NOT NULL UNIQUE,
51		date_found				DATE				NOT NULL,
52		dig_site_num			INT,
53		grid_x					VARCHAR(3)			NOT NULL,
54		grid_y					VARCHAR(3)			NOT NULL,
55		storage_location		VARCHAR(5)			NULL UNIQUE,
56		category_num			VARCHAR(8)			NOT NULL UNIQUE,
57		depth					DECIMAL				NOT NULL,
58		found_by				VARCHAR(8)			NOT NULL,
59		display_location		VARCHAR(10)			NULL,
60		FOREIGN KEY (dig_site_num)
61		REFERENCES dig_site(dig_site_num),
62);
63
64CREATE TABLE slide(
65		PRIMARY KEY (slide_num),
66		slide_num				INT IDENTITY(1,1)	NOT NULL UNIQUE,
67		slide_name				VARCHAR(40)			NOT NULL,
68--		category_num			VARCHAR(10)			NOT NULL,
69		slide_description		text				NOT NULL,
70		artifact_num			INT					NOT NULL UNIQUE
71		FOREIGN KEY (artifact_num)
72		REFERENCES artifact(artifact_num)
73);
74CREATE TABLE slide_category(
75		PRIMARY KEY (slide_num, category_num),
76		slide_num				INT					NOT NULL UNIQUE,
77		category_num			INT					NOT NULL UNIQUE
78		FOREIGN KEY (slide_num)
79		REFERENCES slide(slide_num),
80		FOREIGN KEY (category_num)
81		REFERENCES categories(category_num)
82);
83
84CREATE TABLE artifact_category(
85		PRIMARY KEY (category_num, artifact_num),
86		category_num			INT					NOT NULL UNIQUE,
87		artifact_num			INT					NOT NULL UNIQUE
88		FOREIGN KEY (category_num)
89		REFERENCES categories(category_num),
90		FOREIGN KEY (artifact_num)
91		REFERENCES artifact(artifact_num)
92);
93
94CREATE TABLE displayed_artifact(
95		PRIMARY KEY (display_shelf_num),
96		display_shelf_num		INT IDENTITY(1,1)	NOT NULL UNIQUE,
97		artifact_num			INT					NULL UNIQUE
98);
99
100CREATE TABLE loans(
101		PRIMARY KEY (loan_num),
102		loan_num				INT IDENTITY(1,1)	NOT NULL UNIQUE,
103		slide_num				INT					NOT NULL,
104		book_num				INT					NOT NULL,
105		borrower				VARCHAR(8)			NOT NULL,
106		date_in					DATE				NOT NULL,
107		date_out				DATE				NOT NULL,
108		return_date				DATE				NULL
109		FOREIGN KEY (slide_num)
110		REFERENCES slide(slide_num),
111		FOREIGN KEY (book_num)
112		REFERENCES books_journals(book_num)
113);
114
115
116INSERT INTO staff(staff_name, title, phone, email)
117VALUES ('Martin Andersson', 'Researcher', '0730456597', 'hej@martin.se'),
118	   ('Albin Kindstrad', 'Manager', '070123456', 'hej@albin.se'),
119	   ('Olle Olofsson', 'Librarian', '01355789', 'mail@hotmail.ru'),
120	   ('Hugh Heffner', 'Student', '911', 'grills@mail.com'),
121	   ('Dag Otto', 'Researcher', '112', 'hello@world.com'),
122	   ('Zlatan Ibrahimovic', 'Student', '070*******', 'zl*****i**********.com');
123
124INSERT INTO dig_site(grid_x_max, grid_y_max, dig_site_name)
125VALUES('F',100, 'Titanic Wreck Site'),
126	  ('K',40, 'Tutankhamons tomb'),
127	  ('AB',3000, 'Martin Anderssons basement'),
128	  ('F',1000, 'Stångån'),
129	  ('F',500, 'Unidentified space ship'),
130	  ('L',100, 'Area 51'),
131	  ('O',34, 'Ground 0'),
132	  ('C',345, 'Bombsite Hiroshima'),
133	  ('I',100, 'Battle of Lützen'),
134	  ('S',90, 'Bloodbath of Stångebro'),
135	  ('TY',54, 'Marsian settlement'),
136	  ('U',78, 'The Moon');
137
138INSERT INTO books_journals(book_title, author, ISBN)
139VALUES ('Lunds undre värld, del 2 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457109'),
140	   ('Lunds undre värld, del 1 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457123'),
141	   ('Lunds undre värld, del 3 Utgrävningar 1940-1969', 'Anders W Mårtensson', '9789175457798'),
142	   ('Minnen från utgrävningen 1986-1993 av ostindiefararen Götheborg', 'Anita Steiner', '9789163338366'),
143	   ('Alvastra pålbyggnad : 1976-1980 års utgrävningar - västra schaktet', 'Hans Browall', '9789174024449'),
144	   ('Trafik, broar, tunnelbanor, gator', 'Arne Dufwa', '91-38-08725-1'),
145	   ('Alvastra pålbyggnad : 1909-1930 års utgrävningar', 'Hans Browall', '9789174023978'),
146	   ('Mångkulturella möten kring en forntida lämning : rapport om utgrävningen kring en hällkista i Ber', 'A Sunnestvedt', '9789185411023'),
147	   ('Atoms, molecules, solids, and surfaces: Applications of the generalized gradient approximation for exchange and correlation', 'John P. Perdew, J. A. Chevary, S. H. Vosko, Koblar A. Jackson, Mark R. Pederson, D. J. Singh, and Carlos Fiolhais', '123456789'),
148	   ('Fourth report on the excavations of the Roman fort at Richborough, Kent', 'Bushe-Fox, J.P.', '430789642'),
149	   ('Excavations at Brough-on-Humber 1958–61', 'Wacher, J.S.', '97891754109'),
150	   ('Archaeology of NIDDM: Excavation of the “Thrifty” Genotype', 'Michael Wendorf', '0012-1797'),
151	   ('Excavations at Hengistbury Head, Hampshire, in 1911–12', '	Plunkett Bushe-Fox, Joscelyn', 'xxxxxxxxxx'),
152  	   ('Third Report on the Excavations of the Roman Fort at Richborough, Kent', '	Bushe-Fox, J. P.', 'XXXXXXXXXXX'),
153	   ('First Report on the Excavations of the Roman Fort at Richborough, Kent', '	Bushe-Fox, J.P.', 'XXXXXXXXXXX');
154
155INSERT INTO categories(category_name, category_description)
156VALUES ('The Dark Ages', 'Events taken place a long time ago'),
157	  ('World War 2', 'The second world war'),
158	  ('Linköping', 'City in Sweden'),
159	  ('Japan', 'Country in East Asia'),
160	  ('Egypt', 'Country in north east Africa'),
161	  ('Germany', 'Country in Europe'),
162	  ('Mars', 'Planet in the solar system'),
163	  ('Moon', 'Earths only natural sattelite'),
164	  ('United States of Amaerica', 'Maybe not the greatest country in the world'),
165	  ('USA', 'see United States of America'),
166	  ('Area 51', 'Top Secret military base in the nevada dessert'),
167	  ('Pyramid', 'Graves for high ranking offiials in old Egypt'),
168	  ('Atlantic Ocean', 'Big Ocean'),
169	  ('Hiroshima', 'City in japan, first nuke was dropped here'),
170	  ('30 year war', 'Big european war during the 15th century'),
171	  ('Extraterrestrial life', 'Aliens'),
172	  ('Martin Andersson', 'Kul kille från Askersund, en liten SM-tomte');
173
174	  --*INSERT INTO BORROWER
175
176INSERT INTO warehouse(shelf_num)
177VALUES ('77B'),
178	  ('1A'),
179	  ('1B'),
180	  ('1C'),
181	  ('2A'),
182	  ('2B'),
183	  ('2C'),
184	  ('3A'),
185	  ('3B'),
186	  ('3C'),
187	  ('4A'),
188	  ('4B'),
189	  ('4C'),
190	  ('5A'),
191	  ('5B'),
192	  ('5C'),
193	  ('6A'),
194	  ('6B'),
195	  ('6C'),
196	  ('7A'),
197	  ('7B'),
198	  ('7C'),
199	  ('8A'),
200	  ('8B'),
201	  ('8C'),
202	  ('9A'),
203	  ('9B'),
204	  ('9C'),
205	  ('10A'),
206	  ('10B'),
207	  ('10C'),
208	  ('11A'),
209	  ('11B'),
210	  ('11C'),
211	  ('12A'),
212	  ('12B'),
213	  ('12C'),
214	  ('13A'),
215	  ('13B'),
216	  ('13C'),
217	  ('14A'),
218	  ('14B'),
219	  ('14C'),
220	  ('15A'),
221	  ('15B'),
222	  ('15C');