· 6 years ago · May 06, 2019, 04:24 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 URL VARCHAR(500) DEFAULT '' UNIQUE,
11 Speed VARCHAR(20) DEFAULT '',
12 Film VARCHAR(50) DEFAULT '',
13 FStop VARCHAR(20) DEFAULT '',
14 Color VARCHAR(10) DEFAULT '',
15 Resolution VARCHAR(10) DEFAULT '',
16 Price FLOAT NOT NULL,
17 Date DATE NOT NULL,
18 TransID INT DEFAULT NULL UNIQUE,
19 PName VARCHAR(50),
20 PBDate DATE
21);
22
23CREATE TABLE Landscape
24(
25 PhotoID INT PRIMARY KEY,
26 Place VARCHAR(50),
27 Country VARCHAR(50)
28);
29
30CREATE TABLE Location
31(
32 Place VARCHAR(50) NOT NULL,
33 Country VARCHAR(50) NOT NULL,
34 Description VARCHAR(200) DEFAULT '',
35 PRIMARY KEY (Place, Country)
36);
37
38CREATE TABLE Abstract
39(
40 PhotoID INT PRIMARY KEY,
41 Comment VARCHAR(50) DEFAULT ''
42);
43
44CREATE TABLE Models
45(
46 PhotoID INT NOT NULL PRIMARY KEY,
47 MName VARCHAR(50),
48 MBDate DATE,
49 Agency VARCHAR(50) DEFAULT ''
50);
51
52CREATE TABLE Model
53(
54 MName VARCHAR(50) NOT NULL,
55 MBDate DATE NOT NULL,
56 MBio VARCHAR(200) DEFAULT '',
57 MSex VARCHAR(4) DEFAULT '',
58 PRIMARY KEY (MName, MBDate)
59);
60
61CREATE TABLE Photographer
62(
63 PName VARCHAR(50) NOT NULL,
64 PBDate DATE NOT NULL,
65 PBio VARCHAR(100) DEFAULT '',
66 PAddress VARCHAR(100) DEFAULT '',
67 Color VARCHAR(10) DEFAULT '',
68 PNationality VARCHAR(30) DEFAULT '',
69 PRIMARY KEY (PName, PBDate)
70);
71
72CREATE TABLE Influences
73(
74 EPName VARCHAR(50) NOT NULL,
75 EPBDate DATE NOT NULL,
76 RPName VARCHAR(50) DEFAULT NULL,
77 RPBDate DATE DEFAULT NULL
78);
79
80CREATE TABLE Transaction
81(
82 TransID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
83 TDate DATE NOT NULL,
84 CardNo VARCHAR(20) NOT NULL,
85 CardType VARCHAR(20) NOT NULL,
86 CardExpDate DATE NOT NULL,
87 TotalAmount FLOAT NOT NULL,
88 LoginName VARCHAR(50)
89);
90
91CREATE TABLE Customer
92(
93 LoginName VARCHAR(50) NOT NULL PRIMARY KEY,
94 Password VARCHAR(15) NOT NULL,
95 CName VARCHAR(50) NOT NULL,
96 CType VARCHAR(30) NOT NULL,
97 BillingAddress VARCHAR(100) NOT NULL,
98 Str1 VARCHAR(100) NOT NULL DEFAULT '',
99 Str2 VARCHAR(100) NOT NULL DEFAULT '',
100 City VARCHAR(50) NOT NULL,
101 State VARCHAR(50) NOT NULL,
102 Zip VARCHAR(10) NOT NULL
103);
104
105ALTER TABLE Photo ADD CONSTRAINT transaction_id_fk FOREIGN KEY (TransID) REFERENCES Transaction(TransID) ON DELETE SET NULL;
106ALTER TABLE Photo ADD CONSTRAINT photographer_fk FOREIGN KEY (PName, PBDate) REFERENCES Photographer(PName, PBDate) ON DELETE SET NULL;
107
108ALTER TABLE Landscape ADD CONSTRAINT landscape_photo_id_fk FOREIGN KEY (PhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
109ALTER TABLE Landscape ADD CONSTRAINT location_fk FOREIGN KEY (Place, Country) REFERENCES Location(Place, Country) ON DELETE SET NULL;
110
111ALTER TABLE Abstract ADD CONSTRAINT abstract_photo_id_fk FOREIGN KEY (PhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
112
113ALTER TABLE Models ADD CONSTRAINT models_photo_id_fk FOREIGN KEY (PhotoID) REFERENCES Photo(PhotoID) ON DELETE CASCADE;
114ALTER TABLE Models ADD CONSTRAINT model_fk FOREIGN KEY (MName, MBDate) REFERENCES Model(MName, MBDate) ON DELETE SET NULL;
115
116ALTER TABLE Influences ADD CONSTRAINT influencee_fk FOREIGN KEY (EPName, EPBDate) REFERENCES Photographer(PName, PBDate) ON DELETE CASCADE;
117ALTER TABLE Influences ADD CONSTRAINT influencer_fk FOREIGN KEY (RPName, RPBDate) REFERENCES Photographer(PName, PBDate) ON DELETE CASCADE;
118
119ALTER TABLE Transaction ADD CONSTRAINT login_name_fk FOREIGN KEY (LoginName) REFERENCES Customer(LoginName) ON DELETE SET NULL;
120
121INSERT Photographer(PName, PBDate)
122VALUES
123('Nick', '1996-03-12'),
124('Jack', '1981-08-09');
125
126INSERT Influences(EPName, EPBDate, RPName, RPBDate)
127VALUES
128('Nick', '1996-03-12', 'Jack', '1981-08-09');
129
130INSERT Photo(Price, Date, TransID, PName, PBDate, URL)
131VALUES
132(0.99, '2019-05-01', NULL, 'Nick', '1996-03-12','https://thenypost.files.wordpress.com/2017/12/171215-women-discrimination-work-feature.jpg'),
133(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'),
134(3.99, '2019-05-02', NULL, 'Jack', '1981-08-09', 'https://mondrian.mashable.com/2014%252F07%252F06%252F43%252Fgillettecas.4a6cc.jpg%252F1200x627.jpg'),
135(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'),
136(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'),
137(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');
138
139INSERT Model(MName, MBDate)
140VALUES
141('Anna', '1994-07-11'),
142('Helen', '1990-01-23'),
143('Jill', '1992-04-08');
144
145INSERT Models(PhotoID, MName, MBDate)
146VALUES
147(4, 'Anna', '1994-07-11'),
148(1, 'Helen', '1990-01-23');
149
150INSERT Location(Place, Country)
151VALUES
152('Castle', 'USA'),
153('New York', 'USA'),
154('Forest', 'Canada');
155
156INSERT Landscape(PhotoID, Place, Country)
157VALUES
158(3, 'Castle', 'USA'),
159(5, 'Forest', 'Canada');
160
161INSERT Abstract(PhotoID)
162VALUES
163(2),
164(6);
165
166INSERT Customer(LoginName, Password, CName, CType, BillingAddress, Str1, Str2, City, State, Zip)
167VALUES
168('username1', '1234', 'Morty', 'Visa', '123 Best street', '', '', 'Happyville', 'Nebraska', '89883'),
169('user2', 'qwerty', 'Diana', 'AMEX', '322 Shiny valley', '', '', 'Hope City', 'Cansas', '32212');
170
171INSERT Transaction(TDate, CardNo, CardType, CardExpDate, TotalAmount, LoginName)
172VALUES
173('2019-05-01', '4111111111111111', 'Visa', '2021-01-01', 1.29, 'username1'),
174('2019-04-28', '4111111111111111', 'Visa', '2021-01-01', 1.29, 'username1');
175
176UPDATE Photo
177SET TransID = 1
178WHERE PhotoID = 2;
179
180UPDATE Photo
181SET TransID = 2
182WHERE PhotoID = 5;