· 7 years ago · Oct 27, 2018, 06:56 PM
1-- MySQL dump 10.16 Distrib 10.3.8-MariaDB, for osx10.13 (x86_64)
2--
3-- Host: localhost Database: company
4-- ------------------------------------------------------
5-- Server version 10.3.8-MariaDB
6
7
8--
9-- Dumping data for table `artist`
10--
11
12
13drop SCHEMA if exists project;
14create SCHEMA project COLLATE = utf8_general_ci;
15use project;
16
17
18
19create table artist (
20 name VARCHAR(255) NOT NULL,
21 country_of_origin VARCHAR(255) NOT NULL,
22 epoch VARCHAR(255) NOT NULL,
23 main_style VARCHAR(255) NOT NULL,
24 picture VARCHAR(255) NOT NULL,
25 date_born DATE NULL,
26 date_died DATE NULL,
27 description VARCHAR(255) NULL,
28 primary key (name)
29);
30
31
32
33
34create table `collection` (
35 name VARCHAR(255) NOT NULL,
36 phone VARCHAR(255) NULL,
37 address VARCHAR(255) NULL,
38 description VARCHAR(255) NULL,
39 type VARCHAR(255) NULL,
40 contact_person VARCHAR(255) NULL,
41 primary key (name));
42
43
44
45
46
47create table art_objects (
48 id_no VARCHAR(45) NOT NULL,
49 epoch VARCHAR(255) NOT NULL,
50 origin VARCHAR(255) NOT NULL,
51 description VARCHAR(255) NOT NULL,
52 title VARCHAR(255) NOT NULL,
53 picture VARCHAR(255) NOT NULL,
54 year INT NOT NULL,
55 type VARCHAR(255) NOT NULL,
56 artist_name VARCHAR(255) NULL,
57 collection_name VARCHAR(255) NOT NULL,
58 primary key (id_no),
59 foreign key (artist_name) references artist(name),
60 foreign key (collection_name) references collection(name)
61 );
62
63
64
65
66create table painting (
67 art_no VARCHAR(45) NOT NULL,
68 paint_type VARCHAR(255) NOT NULL,
69 drawn_on VARCHAR(255) NOT NULL,
70 style VARCHAR(255) NOT NULL,
71 primary key (art_no),
72 foreign key (art_no) references art_objects(id_no)
73 );
74
75
76-- -----------------------------------------------------
77-- Table `project`.`sculpture`
78-- -----------------------------------------------------
79
80create table sculpture (
81 art_no VARCHAR(45) NOT NULL,
82 material VARCHAR(255) NOT NULL,
83 height VARCHAR(255) NOT NULL,
84 weight VARCHAR(255) NOT NULL,
85 style VARCHAR(255) NOT NULL,
86 primary key (art_no),
87 foreign key (art_no) references art_objects(id_no)
88);
89
90
91-- -----------------------------------------------------
92-- Table `project`.`other`
93-- -----------------------------------------------------
94
95create table other (
96 art_no VARCHAR(45) NOT NULL,
97 style VARCHAR(255) NOT NULL,
98 type VARCHAR(255) NOT NULL,
99 primary key (art_no),
100 foreign key (art_no) references art_objects(id_no)
101);
102
103
104-- -----------------------------------------------------
105-- Table `project`.`permanent_collection`
106-- -----------------------------------------------------
107
108create table permanent_collection (
109 id_no VARCHAR(255) NOT NULL,
110 cost INT NOT NULL,
111 status VARCHAR(255) NOT NULL,
112 date_acquired DATE NOT NULL,
113 primary key (id_no),
114 foreign key (id_no) references art_objects(id_no)
115);
116
117
118
119-- -----------------------------------------------------
120-- Table `project`.`borrowed`
121-- -----------------------------------------------------
122
123create table borrowed (
124 id_no VARCHAR(45) NOT NULL,
125 date_borrowed DATE NOT NULL,
126 date_returned DATE NOT NULL,
127 primary key (id_no),
128 foreign key (id_no) references art_objects(id_no)
129
130 );
131
132
133-- -----------------------------------------------------
134-- Table `project`.`exhibition`
135-- -----------------------------------------------------
136
137create table exhibition (
138 name VARCHAR(255) NOT NULL,
139 start_date DATE NOT NULL,
140 end_date DATE NOT NULL,
141 number_of_people INT NULL,
142 primary key (name)
143 );
144
145
146-- -----------------------------------------------------
147-- Table `project`.`guide_service`
148-- -----------------------------------------------------
149
150create table guide_service (
151 guide_id INT NOT NULL,
152 name VARCHAR(45) NOT NULL,
153 rating VARCHAR(45) NULL,
154 time DOUBLE NULL,
155 primary key(guide_id)
156);
157
158
159-- -----------------------------------------------------
160-- Table `project`.`museum_goer`
161-- -----------------------------------------------------
162
163create table museum_goer (
164 goer_id INT NOT NULL,
165 primary key (goer_id));
166
167
168-- -----------------------------------------------------
169-- Table `project`.`museum_has_exhibition`
170-- -----------------------------------------------------
171
172create table exhibition_view (
173 exhibitions_id INT NOT NULL,
174 exhibitions_name VARCHAR(255) NOT NULL,
175 primary key (exhibitions_id, exhibitions_name),
176 foreign key (exhibitions_id) references museum_goer(goer_id),
177 foreign key (exhibitions_name) references exhibition(name)
178 );
179
180
181-- -----------------------------------------------------
182-- Table `project`.`guide_view`
183-- -----------------------------------------------------
184
185create table guide_view (
186 exhibitions_id INT NOT NULL,
187 guide_id INT NOT NULL,
188 primary key (exhibitions_id, guide_id),
189 foreign key (exhibitions_id) references museum_goer(goer_id),
190 foreign key (guide_id) references guide_service(guide_id)
191
192 );
193
194
195-- -----------------------------------------------------
196-- Table `project`.`general_admissiion`
197-- -----------------------------------------------------
198
199create table general_admission (
200 general_id VARCHAR(45) NOT NULL,
201 primary key (general_id));
202
203
204
205-- -----------------------------------------------------
206-- Table `project`.`general_view`
207-- -----------------------------------------------------
208
209create table general_view (
210 exhibitions_id INT NOT NULL,
211 general_id VARCHAR(45) NOT NULL,
212 primary key (exhibitions_id, general_id),
213 foreign key (exhibitions_id) references museum_goer(goer_id),
214 foreign key (general_id) references general_admission(general_id)
215 );
216
217
218-- -----------------------------------------------------
219-- Table `project`.`user`
220-- -----------------------------------------------------
221
222create table user (
223 username VARCHAR(255) NOT NULL,
224 password VARCHAR(255) NOT NULL,
225 name VARCHAR(255) NOT NULL,
226 primary key (username));
227
228
229-- -----------------------------------------------------
230-- Table `project`.`admin`
231-- -----------------------------------------------------
232
233create table admin (
234 username VARCHAR(255) NOT NULL,
235 password VARCHAR(255) NOT NULL,
236 name VARCHAR(255) NOT NULL,
237 primary key (username),
238 foreign key (username) references user(username)
239
240 );
241 INSERT INTO `artist` (name, country_of_origin, epoch, main_style,picture,date_born,date_died,description) VALUES
242 ('terry kritler','California','1900','mixed','terry','1960-06-01','NULL','NULL'),
243 ('Beatrice Hatala','London','1900','indie','Beatrice','1961-05-09','NULL','NULL'),
244 ('Canhavato','London','1800','mixed','NULL','1805-06-14','1860-06-06','RIP'),
245 ('Alamy','Thai','1800','rock','NULL','1990-06-14','NULL','NULL'),
246 ('Kandinsky’s','Dutch','1900','indie','Kandinsky’s','1934-03-11','NULL','NULL'),
247 ('Willem de Kooning','Moscow','1900','indie','Willem de Kooning','1911-11-06','NULL','NULL'),
248 ('TOY','Thai','2000','mixed','the_toy','2538-12-01','NULL','singer');
249
250 INSERT INTO `collection` (name,phone,address,description,type,contact_person) VALUES
251 ('P1','(274)893-7925',"3472 Flint Street","It is the first time someone made a painting that wasnt of something",'museum','Sjakie Diederick'),
252 ('O1','(284)873-7429',"5061 Atlanta Georgia Street",'the motion and emotion of a train station rather than the physical depiction of passengers and trains.','personal','Fleur Emma'),
253 ('S1','(448)213-3450',"30329 GA","American painter, is often given as the ideal representation of Abstract Expressionism",'etc','Stef Mechteld');
254
255 INSERT INTO `art_objects` (id_no,epoch,origin,description,title,picture,year,artist_name,collection_name,type) VALUES
256 ('1','1900','California','wood sculpture','wood sculpture','king','1996','terry kritler','S1','sculpture'),
257 ('2','1900','London','Pablo Picasso','Bull’s Head','bullhead','1973','Beatrice Hatala','S1','sculpture'),
258 ('3','1800','London','Throne of Weapons','Throne of Weapons','gunchair','1872','Canhavato','S1','sculpture'),
259 ('4','1800','Thai','The Makapansgat Pebble','The Natural','pabble','1886','Alamy','S1','sculpture'),
260 ('5','1800','Thai','The toy','The toy picture','the_toy','1995','TOY','O1','other'),
261 ('6','1900','London','The Uncomfortable Project is Fun, Crafty and Probing','everyday-objects-art','everyday-objects-art','2016','TOY','O1','other'),
262 ('7','1800','Dutch','In the years after World War II, de Kooning painted in a style that came to be referred to as Abstract expressionism or "action painting"','womanIII','Woman','1997','Willem de Kooning','P1','painting'),
263 ('8','1900','Moscow','Composition VII is a combination of several themes namely Resurrection, the Judgment Day, the Flood and the Garden of Eden','Composition VII','Composition VII','1913','Kandinsky’s','P1','painting');
264
265
266 --
267 -- Dumping data for table `dept_locations`
268 --
269
270
271
272 --
273 -- Dumping data for table `collection`
274 --
275
276 INSERT INTO `sculpture` (art_no,material,height,weight,style) VALUES
277 ('1','wood','30cm','1kg','Abstract'),
278 ('2','stone','42cm','1kg','Indie'),
279 ('3','etc','90cm','3kg','Indie'),
280 ('4','etc','60cm','3kg','Mixed');
281
282
283 --
284 -- Dumping data for table `sculpter`
285 --
286
287 INSERT INTO `painting` (art_no, paint_type, drawn_on, style) VALUES
288 ('7','Oil','Canvas','Impressionism'),
289 ('8','Water colour','Paper','Abstract');
290
291
292
293 --
294 -- Dumping data for table `painting`
295 --
296
297
298 INSERT INTO `permanent_collection` (id_no, cost, status,date_acquired) VALUES
299 ('1',20000,'on display',"1997-08-01"),
300 ('2',30000,'on display',"2007-01-08"),
301 ('3',5000,'on display',"2017-08-01"),
302 ('6',8000,'on loan',"2018-01-08"),
303 ('7',15000,'stored',"2018-08-01");
304
305
306
307
308 INSERT INTO `borrowed` (id_no, date_borrowed, date_returned) VALUES
309 ('4','2016-05-04','2016-06-04'),
310 ('5','2018-08-05','2018-08-05'),
311 ('6','2016-09-09','2017-10-11');
312
313
314
315 INSERT INTO `other` (art_no, style, type) VALUES
316 ('5','Abstract','Print'),
317 ('6','Indie','Photo');
318
319
320
321 INSERT INTO `general_admission` (general_id) VALUES
322 ("14471"),
323 ("24602"),
324 ("12218"),
325 ("14433"),
326 ("15517"),
327 ("14652"),
328 ("24140"),
329 ("19063");
330
331
332 INSERT INTO `guide_service` (guide_id,name,rating,time) VALUES
333 (242410,"Tommy Kelley","91",3),
334 (413157,"Paige Hernandez","73",5),
335 (312374,"Alyssa O'Doherty","72",7),
336 (364926,"Faye Walsh","56",6),
337 (362831,"Samantha Gutierrez","85",2),
338 (298897,"Aaliyah Mendoza","61",4),
339 (429643,"Rebecca Lewis","64",6),
340 (165108,"Tommy Miller","51",8),
341 (317979,"Leonard Wright","60",10),
342 (333567,"Esme Coleman","87",6.5),
343 (382029,"Traci Johnston","93",8.5),
344 (182876,"Alfred Rodriguez","54",9.5),
345 (331369,"Casey Alvarez","86",5.5),
346 (406157,"Nathaniel Meyer","66",3),
347 (386974,"Nicholas Scott","96",2.5),
348 (325579,"Tommy Jimenez","61",6.5);
349
350 INSERT INTO `museum_goer` (goer_id) VALUES
351 (10468),--
352 (14409),--
353 (11107),--
354 (12293),--
355 (14880),--
356 (11552),--
357 (10180),--
358 (12090),--
359 (11485),--
360 (10059),--
361 (13787),--
362 (13788),--
363 (12197),--
364 (12900),--
365 (11872),--
366 (10902);--
367
368
369 INSERT INTO `general_view` (exhibitions_id,general_id) VALUES
370 (14409,"14652"),
371 (14880,"24140"),
372 (12090,"19063"),
373 (10059,"15517"),
374 (12197,"14433"),
375 (10902,"12218");
376
377
378 INSERT INTO `guide_view` (exhibitions_id,guide_id) VALUES
379 (10468,242410),
380 (11107,312374),
381 (12293,298897),
382 (10180,165108),
383 (13787,317979),
384 (12900,406157),
385 (11872,325579);
386
387
388 INSERT INTO `exhibition` (name,start_date,end_date,number_of_people) VALUES
389 ("High Society","2016-05-08","2016-06-03","200"),
390 ("Grayson Perry","2016-04-02","2016-05-01","150"),
391 ("Delacroix","2016-06-06","2016-10-08","250"),
392 ("Rembrandt","2017-08-11","2017-10-01","360"),
393 ("Guggenheim","2016-10-11","2017-03-14","220"),
394 ("Wanderlust","2017-05-10","2017-07-16","80"),
395 ("Royal Museums of Fine Arts","2017-10-05","2018-01-27","400"),
396 ("Dada is Dada","2018-02-25","2018-05-06","150"),
397 ("Idea of North","2018-05-16","2018-09-01","350"),
398 ("Tate Modern","2018-06-11","2018-12-31","120");
399
400
401
402 INSERT INTO `exhibition_view` (exhibitions_id,exhibitions_name) VALUES
403 (11552,"Delacroix"),
404 (11485,"Royal Museums of Fine Arts"),
405 (13788,"Dada is Dada");
406
407
408
409 INSERT INTO `user` (username,password,name) VALUES
410 ("Monet","308556","Rowland"),
411 ("Dustin","866299","Howard"),
412 ("Webster","756981","Woodley"),
413 ("Blair","756699","Savage"),
414 ("Mcghee","876524","Conner"),
415 ("Daniel","367355","Combs"),
416 ("Schaefer","713089","Chen"),
417 ("Burks","907452","Herbert"),
418 ("Rennie","295645","Walmsley"),
419 ("Helen","692798","Susan");
420
421
422 INSERT INTO `admin` (username,password,name) VALUES
423 ("Dustin","866299","Howard"),
424 ("Schaefer","713089","Chen"),
425 ("Rennie","295645","Walmsley");