· 6 years ago · May 06, 2019, 11:50 PM
1DROP DATABASE IF EXISTS PhotoShopV1;
2
3CREATE DATABASE PhotoShopV1;
4
5USE PhotoShopV1;
6
7CREATE TABLE Photo
8(
9 PhotoID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
10 PType VARCHAR(20) DEFAULT '',
11 URL VARCHAR(500) DEFAULT '' UNIQUE,
12 Speed VARCHAR(20) DEFAULT '',
13 Film VARCHAR(50) DEFAULT '',
14 FStop VARCHAR(20) DEFAULT '',
15 Color VARCHAR(20) DEFAULT '',
16 Resolution VARCHAR(10) DEFAULT '',
17 Price FLOAT NOT NULL,
18 Date DATE NOT NULL,
19 TransID INT DEFAULT NULL UNIQUE,
20 PName VARCHAR(50),
21 PBDate DATE
22);
23
24CREATE TABLE Landscape
25(
26 LPhotoID INT PRIMARY KEY,
27 Place VARCHAR(50),
28 Country VARCHAR(50)
29);
30
31CREATE TABLE Location
32(
33 Place VARCHAR(50) NOT NULL,
34 Country VARCHAR(50) NOT NULL,
35 Description VARCHAR(200) DEFAULT '',
36 PRIMARY KEY (Place, Country)
37);
38
39CREATE TABLE Abstract
40(
41 APhotoID INT PRIMARY KEY,
42 Comment VARCHAR(50) DEFAULT ''
43);
44
45CREATE TABLE Models
46(
47 MPhotoID INT NOT NULL PRIMARY KEY,
48 MName VARCHAR(50),
49 MBDate DATE,
50 Agency VARCHAR(50) DEFAULT ''
51);
52
53CREATE TABLE Model
54(
55 MName VARCHAR(50) NOT NULL,
56 MBDate DATE NOT NULL,
57 MBio VARCHAR(200) DEFAULT '',
58 MSex VARCHAR(10) DEFAULT '',
59 PRIMARY KEY (MName, MBDate)
60);
61
62CREATE TABLE Photographer
63(
64 PPName VARCHAR(50) NOT NULL,
65 PPBDate DATE NOT NULL,
66 PBio VARCHAR(100) DEFAULT '',
67 PAddress VARCHAR(100) DEFAULT '',
68 PColor VARCHAR(100) DEFAULT '',
69 PNationality VARCHAR(30) DEFAULT '',
70 PRIMARY KEY (PPName, PPBDate)
71);
72
73CREATE TABLE Influences
74(
75 EPName VARCHAR(50) NOT NULL,
76 EPBDate DATE NOT NULL,
77 RPName VARCHAR(50) DEFAULT NULL,
78 RPBDate DATE DEFAULT NULL
79);
80
81CREATE TABLE Transaction
82(
83 TransID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
84 TDate DATE NOT NULL,
85 CardNo VARCHAR(20) NOT NULL,
86 CardType VARCHAR(20) NOT NULL,
87 CardExpDate DATE NOT NULL,
88 TotalAmount FLOAT NOT NULL,
89 LoginName VARCHAR(50)
90);
91
92CREATE TABLE Customer
93(
94 LoginName VARCHAR(50) NOT NULL PRIMARY KEY,
95 Password VARCHAR(15) NOT NULL,
96 CName VARCHAR(50) NOT NULL,
97 CType VARCHAR(30) NOT NULL,
98 BillingAddress VARCHAR(100) NOT NULL,
99 Str1 VARCHAR(100) NOT NULL DEFAULT '',
100 Str2 VARCHAR(100) NOT NULL DEFAULT '',
101 City VARCHAR(50) NOT NULL,
102 State VARCHAR(50) NOT NULL,
103 Zip VARCHAR(10) NOT NULL
104);
105
106ALTER TABLE Photo ADD CONSTRAINT transaction_id_fk FOREIGN KEY (TransID) REFERENCES Transaction(TransID) ON DELETE SET NULL;
107ALTER TABLE Photo ADD CONSTRAINT photographer_fk FOREIGN KEY (PName, PBDate) REFERENCES Photographer(PPName, PPBDate) ON DELETE SET NULL;
108
109ALTER TABLE Landscape ADD CONSTRAINT landscape_photo_id_fk FOREIGN KEY (LPhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
110ALTER TABLE Landscape ADD CONSTRAINT location_fk FOREIGN KEY (Place, Country) REFERENCES Location(Place, Country) ON DELETE SET NULL;
111
112ALTER TABLE Abstract ADD CONSTRAINT abstract_photo_id_fk FOREIGN KEY (APhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
113
114ALTER TABLE Models ADD CONSTRAINT models_photo_id_fk FOREIGN KEY (MPhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
115ALTER TABLE Models ADD CONSTRAINT model_fk FOREIGN KEY (MName, MBDate) REFERENCES Model(MName, MBDate) ON DELETE SET NULL;
116
117ALTER TABLE Influences ADD CONSTRAINT influencee_fk FOREIGN KEY (EPName, EPBDate) REFERENCES Photographer(PPName, PPBDate) ON DELETE CASCADE;
118ALTER TABLE Influences ADD CONSTRAINT influencer_fk FOREIGN KEY (RPName, RPBDate) REFERENCES Photographer(PPName, PPBDate) ON DELETE CASCADE;
119
120ALTER TABLE Transaction ADD CONSTRAINT login_name_fk FOREIGN KEY (LoginName) REFERENCES Customer(LoginName) ON DELETE SET NULL;
121
122INSERT Photographer(PPName, PPBDate, PBio, PAddress, PColor, PNationality)
123VALUES
124('Nick', '1996-03-12', 'Yong, creative, inspiring..', 'Maple street, Canada', 'Colorfull', 'US'),
125('Jack', '1981-08-09', 'Experienced phototaker', 'Downtown, Manhattan', 'B&W, Colorfull', 'Britain');
126
127INSERT Influences(EPName, EPBDate, RPName, RPBDate)
128VALUES
129('Nick', '1996-03-12', 'Jack', '1981-08-09');
130
131INSERT Photo(Price, Date, TransID, PName, PBDate, URL, PType, Speed, Film, FStop, Color, Resolution)
132VALUES
133(0.99, '2019-05-01', NULL, 'Nick', '1996-03-12','https://thenypost.files.wordpress.com/2017/12/171215-women-discrimination-work-feature.jpg', 'Model', '1/16', 'Glance', '20x', 'Colorfull', '1920x1080'),
134(1.29, '2019-03-02', NULL, 'Nick', '1996-03-12', 'https://images.unsplash.com/photo-1529666759085-741eefcd3371?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&w=1000&q=80', 'Abstract', '1/4', 'Glance', '20x', 'Colorfull', '1920x1080'),
135(3.99, '2019-05-02', NULL, 'Jack', '1981-08-09', 'https://mondrian.mashable.com/2014%252F07%252F06%252F43%252Fgillettecas.4a6cc.jpg%252F1200x627.jpg', 'Landscape', '1/12', 'Glance', '20x', 'Colorfull', '1920x1080'),
136(5.00, '2019-02-02', NULL, 'Jack', '1981-08-09', 'https://www.vichealth.vic.gov.au/-/media/Images/VicHealth/Images-and-Files/Funding/Female-Participation-in-Sport/cogfunding3-1200x525.jpg', 'Model', '4', 'Glance', '20x', 'Colorfull', '1920x1080'),
137(1.29, '2019-05-02', NULL, 'Jack', '1981-08-09', 'https://www.worldatlas.com/r/w728-h425-c728x425/upload/2d/9b/b7/thinkstockphotos-536408859.jpg', 'Landscape', '1/16', 'Glance', '20x', 'Colorfull', '1920x1080'),
138(9.99, '2019-05-02', NULL, 'Jack', '1981-08-09', 'https://images.unsplash.com/photo-1528557692780-8e7be39eafab?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&w=1000&q=80', 'Abstract', '1/5', 'Glance', '20x', 'Colorfull', '1920x1080');
139
140INSERT Model(MName, MBDate, MBio, MSex)
141VALUES
142('Anna', '1994-07-11', 'Self inspirin', 'Female'),
143('Helen', '1990-01-23', 'Interesting', 'Female'),
144('Jill', '1992-04-08', 'Charming', 'Female');
145
146INSERT Models(MPhotoID, MName, MBDate, Agency)
147VALUES
148(4, 'Anna', '1994-07-11', 'Quality Pic. Inc.'),
149(1, 'Helen', '1990-01-23', 'Best Shots Co.');
150
151INSERT Location(Place, Country, Description)
152VALUES
153('Castle', 'USA', 'Beautifull castle'),
154('New York', 'USA', 'No hope city'),
155('Forest', 'Canada', 'Some woods');
156
157INSERT Landscape(LPhotoID, Place, Country)
158VALUES
159(3, 'Castle', 'USA'),
160(5, 'Forest', 'Canada');
161
162INSERT Abstract(APhotoID, Comment)
163VALUES
164(2, 'Nice painting'),
165(6, 'Gorgeous picture');
166
167INSERT Customer(LoginName, Password, CName, CType, BillingAddress, Str1, Str2, City, State, Zip)
168VALUES
169('username1', '1234', 'Morty', 'Visa', '123 Best street', '', '', 'Happyville', 'Nebraska', '89883'),
170('user2', 'qwerty', 'Diana', 'AMEX', '322 Shiny valley', '', '', 'Hope City', 'Cansas', '32212');
171
172INSERT Transaction(TDate, CardNo, CardType, CardExpDate, TotalAmount, LoginName)
173VALUES
174('2019-05-01', '4111111111111111', 'Visa', '2021-01-01', 1.29, 'username1'),
175('2019-04-28', '4111111111111111', 'Visa', '2021-01-01', 1.29, 'username1');
176
177UPDATE Photo
178SET TransID = 1
179WHERE PhotoID = 2;
180
181UPDATE Photo
182SET TransID = 2
183WHERE PhotoID = 5;
184
185
186DELIMITER $$
187CREATE PROCEDURE `get_photos`()
188BEGIN
189 SELECT PhotoID, PType, URL, Speed, Film, FStop, Color, Resolution, Price, Date, TransID, PName, PBDate, Comment, NULL as Place, NULL as Country, NULL as MName, NULL as MBDate, NULL as Agency, PBio, PAddress, PColor, PNationality FROM Photo
190 JOIN Abstract ON Photo.PhotoID = Abstract.APhotoID
191 JOIN Photographer ON Photo.PName = Photographer.PPName AND Photo.PBDate = Photographer.PPBDate
192 WHERE TransID IS NULL
193 UNION ALL
194 SELECT PhotoID, PType, URL, Speed, Film, FStop, Color, Resolution, Price, Date, TransID, PName, PBDate, NULL, Place, Country, NULL, NULL, NULL, PBio, PAddress, PColor, PNationality FROM Photo
195 JOIN Landscape ON Photo.PhotoID = Landscape.LPhotoID
196 JOIN Photographer ON Photo.PName = Photographer.PPName AND Photo.PBDate = Photographer.PPBDate
197 WHERE TransID IS NULL
198 UNION ALL
199 SELECT PhotoID, PType, URL, Speed, Film, FStop, Color, Resolution, Price, Date, TransID, PName, PBDate, NULL, NULL, NULL, MName, MBDate, Agency, PBio, PAddress, PColor, PNationality FROM Photo
200 JOIN Models ON Photo.PhotoID = Models.MPhotoID
201 JOIN Photographer ON Photo.PName = Photographer.PPName AND Photo.PBDate = Photographer.PPBDate
202 WHERE TransID IS NULL
203 ORDER BY PhotoID DESC;
204 END$$
205DELIMITER ;
206
207DELIMITER $$
208CREATE PROCEDURE `insert_photo`(Price FLOAT, Date DATE, TransID INT, PName VARCHAR(50), PBDate DATE, URL VARCHAR(500), PType VARCHAR(20), Speed VARCHAR(20), Film VARCHAR(50), FStop VARCHAR(20), Color VARCHAR(20), Resolution VARCHAR(10))
209BEGIN
210 INSERT Photo(Price, Date, TransID, PName, PBDate, URL, PType, Speed, Film, FStop, Color, Resolution) VALUES (Price, Date, TransID, PName, PBDate, URL, PType, Speed, Film, FStop, Color, Resolution);
211 SELECT LAST_INSERT_ID();
212END$$
213DELIMITER ;
214
215CALL get_photos();