· 7 years ago · Dec 18, 2018, 06:16 PM
1DROP TABLE IF EXISTS trans;
2DROP TABLE IF EXISTS work;
3DROP TABLE IF EXISTS customer_artist_int;
4DROP TABLE IF EXISTS customer;
5DROP TABLE IF EXISTS listofartists;
6
7CREATE TABLE listofartists (
8 ArtistID INT(11) NOT NULL AUTO_INCREMENT,
9 LastName CHAR(25) NOT NULL,
10 FirstName CHAR(25) NOT NULL,
11 Nationality CHAR(30) DEFAULT NULL,
12 DateDeceased INT(11) DEFAULT NULL,
13 DateOfBirth INT(11) DEFAULT NULL,
14 CONSTRAINT ListOfArtists_PK PRIMARY KEY (ArtistID)
15);
16
17INSERT INTO listofartists
18 (ArtistID,LastName,FirstName,Nationality,DateDeceased,DateOfBirth)
19VALUES
20 (1,'Miro','Joan','Spanish',1983,1893),
21 (2,'Kandinsky','Wassily','Russian',1944,1866),
22 (3,'Klee','Paul','German',1940,1879),
23 (4,'Matisse','Henri','French',1954,1869),
24 (5,'Chagall','Marc','French',1985,1887),
25 (11,'Sargent','John Singer','American',1925,1856),
26 (17,'Tobey','Mark','American',1976,1890),
27 (18,'Horiuchi','Paul','American',1999,1906),
28 (19,'Graves','Morris','American',1999,1920),
29 (20,'Dali','Salvador','Spanish',1989,1904),
30 (21,'Rodin','Auguste','French',1917,1840);
31
32CREATE TABLE customer (
33 CustomerID INT NOT NULL AUTO_INCREMENT,
34 LastName CHAR(25) NOT NULL,
35 FirstName CHAR(25) NOT NULL,
36 Street CHAR(30) DEFAULT NULL,
37 City CHAR(35) DEFAULT NULL,
38 ZipPostalCode CHAR(9) DEFAULT NULL,
39 Country CHAR(50) DEFAULT NULL,
40 AreaCode CHAR(3) DEFAULT NULL,
41 PhoneNumber CHAR(8) DEFAULT NULL,
42 Email VARCHAR(100) DEFAULT NULL,
43 PRIMARY KEY (CustomerID),
44 UNIQUE KEY EmailAK1 (Email)
45);
46
47INSERT INTO customer (CustomerID,LastName,FirstName,Street,City,
48 ZipPostalCode,Country,AreaCode,PhoneNumber,Email)
49VALUES
50 (1000,'Cohen','Shalom','23 Habonim St','Haifa','94325','Israel','04',
51 '5432345','Cohen.Sh@gmail.com'),
52 (1001,'Arad','Keren','17 Jabotinsky St','Tel Aviv','64325','Israel','03',
53 '5226874','AradK@hotmail.com'),
54 (1002,'Kaminzky','Ariel','43 Yordei Hasira','Tel Aviv','65321','Israel','03',
55 '5117643','AKam@bezeqint.net'),
56 (1003,'Wu','Susan','105 Locust Ave','Atlanta','30322','USA','404',
57 '6533465',NULL),
58 (1004,'Zur','Michael','35 Magpie Lane','London','WC13CW','UK','020',
59 '98231450','ZurM@yahoo.com');
60
61CREATE TABLE customer_artist_int (
62 ArtistID INT NOT NULL,
63 CustomerID INT NOT NULL,
64 PRIMARY KEY (ArtistID,CustomerID),
65 FOREIGN KEY CAInt_ArtistFK (ArtistID)
66 REFERENCES ListOfArtists (ArtistID)
67 ON DELETE CASCADE
68 ON UPDATE NO ACTION,
69 FOREIGN KEY CAInt_CustomerFK (CustomerID)
70 REFERENCES customer (CustomerID)
71 ON DELETE CASCADE
72 ON UPDATE NO ACTION
73);
74
75INSERT INTO customer_artist_int (ArtistID,CustomerID)
76VALUES
77 (1,1001),
78 (2,1001),
79 (1,1004),
80 (2,1004),
81 (18,1002),
82 (19,1001);
83
84CREATE TABLE work (
85 WorkID INT NOT NULL AUTO_INCREMENT,
86 Title CHAR(35) NOT NULL,
87 Copy CHAR(12) NOT NULL,
88 Medium CHAR(35) DEFAULT NULL,
89 Description VARCHAR(1000) DEFAULT 'Unknown provenance',
90 ArtistID INT NOT NULL,
91 PRIMARY KEY (WorkID),
92 UNIQUE KEY WorkAK1 (Title,Copy),
93 FOREIGN KEY ArtistFK (ArtistID)
94 REFERENCES listofartists (ArtistID)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION
97);
98
99INSERT INTO work (WorkID,Title,Copy,Medium,Description,ArtistID)
100VALUES
101 (500,'Memories 4','Unique','Casein rice paper collage','31 X 24.8 in.',18),
102 (511,'Surf and Bird','142/500','High Quality Limited Print','NorthWest School Expressionist style',19),
103 (521,'The Tilled Field','788/1000','High Quality Limited Print','Early Surrealist style',1),
104 (522,'La Lecon de Ski','353/500','High Quality Limited Print','Surrealist style',1),
105 (524,'Woman With A Hat','596/750','High Quality Limited Print','A very colourful impressionist piece',4),
106 (548,'Night Bird','Unique','Watercolour on paper','50X72.5 cm. - Signed',19),
107 (553,'The Dance','734/1000','High Quality Limited Print','An Impressionist masterpiece',4),
108 (586,'Spanish Dancer','588/750','High Quality Limited Print','American Realist style - From work in Spain',11),
109 (590,'Blue Interior','Unique','Tempera on card','43.9 X 28 in.',17);
110
111CREATE TABLE trans (
112 TransactionID INT NOT NULL AUTO_INCREMENT,
113 DateAcquired DATETIME NOT NULL,
114 AcquisitionPrice INT NOT NULL,
115 DateSold DATETIME DEFAULT NULL,
116 AskingPrice INT DEFAULT NULL,
117 SalesPrice INT DEFAULT NULL,
118 CustomerID INT DEFAULT NULL,
119 WorkID INT NOT NULL,
120 PRIMARY KEY (TransactionID),
121 FOREIGN KEY TransCustomerFK (CustomerID)
122 REFERENCES customer (CustomerID)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION,
125 FOREIGN KEY TransWorkFK (WorkID)
126 REFERENCES work (WorkID)
127 ON DELETE NO ACTION
128 ON UPDATE NO ACTION
129);
130
131INSERT INTO trans (TransactionID,DateAcquired,AcquisitionPrice,
132 DateSold,AskingPrice,SalesPrice,CustomerID,WorkID)
133VALUES
134 (100,'2005-04-11 00:00:00',30000,'2005-04-12 00:00:00',45000,42500,1001,500),
135 (104,'2005-11-07 00:00:00',250,'2005-12-19 00:00:00',500,500,1001,511),
136 (108,'2005-11-17 00:00:00',250,'2006-12-12 00:00:00',500,400,1004,522);