· 7 years ago · Dec 03, 2018, 12:20 AM
1#Maria Kalusz
2#Project 6 - Create a DB for Frank's Frames
3
4DROP DATABASE IS EXISTS FranksFrames;
5CREATE IF NOT EXISTS DATABASE FranksFrames;
6
7USE FranksFrames;
8
9CREATE TABLE FranksFrames.Customer(
10 CustomerId INT PRIMARY KEY AUTO_INCREMENT,
11 CustomerName VARCHAR(45) NOT NULL,
12 CustomerPhone CHAR(10)
13);
14
15CREATE INDEX cust_phone_idx
16ON Customer(CustomerPhone);
17
18CREATE TABLE FranksFrames.Mat(
19 MatId INT PRIMARY KEY AUTO_INCREMENT,
20 MatColor ENUM('White','Gray','Black','Sand') NOT NULL,
21 MatPricePSF DECIMAL(4,2) NOT NULL
22);
23
24CREATE TABLE FranksFrames.Frame(
25 FrameId INT PRIMARY KEY AUTO_INCREMENT,
26 FrameName VARCHAR(45) NOT NULL,
27 FrameColor ENUM('Gold','Silver','Black','White') NOT NULL,
28 FrameWidth FLOAT(4,2) NOT NULL,
29 FramePricePLF DECIMAL(5,2) NOT NULL
30);
31
32CREATE TABLE FranksFrames.Glass(
33 GlassId INT PRIMARY KEY AUTO_INCREMENT,
34 GlassStyle VARCHAR(45) NOT NULL,
35 GlassPricePSF DECIMAL(4,2) NOT NULL
36);
37
38CREATE TABLE FranksFrames.Poster(
39 PosterId INT PRIMARY KEY AUTO_INCREMENT,
40 PosterName VARCHAR(45) NOT NULL,
41 PosterPrice DECIMAL(5,2) NOT NULL,
42 PosterWidth FLOAT(5,2),
43 PosterHeight FLOAT(5,2)
44);
45
46CREATE TABLE FranksFrames.Supplier(
47 SupplierId INT PRIMARY KEY AUTO_INCREMENT,
48 SupplierName VARCHAR(45) NOT NULL,
49 SupplierPhone CHAR(10) NOT NULL
50);
51
52CREATE TABLE FranksFrames.PosterSupplier(
53 PosterId INT,
54 SupplierId INT,
55 CONSTRAINT PRIMARY KEY(PosterId, SupplierId),
56 CONSTRAINT Poster_Id_FK FOREIGN KEY(PosterId) REFERENCES Poster(PosterId),
57 CONSTRAINT Supplier_Id_FK FOREIGN KEY(SupplierId) REFERENCES Supplier(SupplierId)
58);
59
60CREATE TABLE FranksFrames.`Order`(
61 OrderId INT PRIMARY KEY AUTO_INCREMENT,
62 CustomerId INT NOT NULL,
63 PosterId INT NOT NULL,
64 MatId INT NOT NULL,
65 FrameId INT NOT NULL,
66 GlassId INT NOT NULL,
67 OrderPromised DATE NOT NULL,
68 OrderPrice DECIMAL(6,2) NOT NULL,
69 CONSTRAINT Customer_Id_FK FOREIGN KEY(CustomerId) REFERENCES Customer(CustomerId),
70 CONSTRAINT Poster_Id2_FK FOREIGN KEY(PosterId) REFERENCES Poster(PosterId),
71 CONSTRAINT Mat_Id_FK FOREIGN KEY(MatId) REFERENCES Mat(MatId),
72 CONSTRAINT Frame_Id_FK FOREIGN KEY(FrameId) REFERENCES Frame(FrameId),
73 CONSTRAINT Glass_Id_FK FOREIGN KEY(GlassId) REFERENCES Glass(GlassId)
74);
75
76INSERT INTO Customer
77 VALUES
78 (1, 'Mary Jones', '2156632345'),
79 (DEFAULT, 'John Jenkins', '2157562321'),
80 (DEFAULT, 'Chris Ford', '2153429676'),
81 (DEFAULT, 'Sara Parker', '2152318678'),
82 (DEFAULT, 'Mike Kelly', '2153456432'),
83 (DEFAULT, 'Joe Baker', '2153232222');
84
85INSERT INTO Mat
86 VALUES
87 (1, 'White', '12.99'),
88 (DEFAULT, 'Gray', '13.99'),
89 (DEFAULT, 'Black', '13.99'),
90 (DEFAULT, 'Sand', '13.99'),
91 (DEFAULT, 'White', '12.99'),
92 (DEFAULT, 'Gray', '13.99');
93
94INSERT INTO Frame
95 VALUES
96 (1, 'Coolest','Gold', 34.53, 10.99),
97 (DEFAULT, 'Antique', 'Silver', 34.53, 11.89),
98 (DEFAULT, 'Sleek', 'Black', 34.53, 9.99),
99 (DEFAULT, 'Futuristic', 'White', 34.53, 59.99),
100 (DEFAULT, 'Popular', 'Gold', 34.53, 9.99),
101 (DEFAULT, 'Family', 'Gold', 34.53, 20.99);
102
103INSERT INTO Glass
104 VALUES
105 (1, 'Plexi', 4.99),
106 (DEFAULT, 'Sheer', 20.99),
107 (DEFAULT, 'Clear', 10.99),
108 (DEFAULT, 'Tinted', 15.99);
109
110INSERT INTO Poster
111 VALUES
112 (1, 'Zeppelin', 35.99, 35, 50),
113 (DEFAULT, 'Queen', 30.50, 35, 50),
114 (DEFAULT, 'Hendrix', 40.00, 35, 50),
115 (DEFAULT, 'Fleetwood', 25.99, 35, 50),
116 (DEFAULT, 'Dylan', 55.99, 35, 50),
117 (DEFAULT, 'Beetles', 80.99, 35, 50);
118
119INSERT INTO Supplier
120 VALUES
121 (1, 'East', '1234567898'),
122 (DEFAULT, 'West', '5465324567'),
123 (DEFAULT, 'South', '8769432165'),
124 (DEFAULT, 'North', '4434446789');
125
126INSERT INTO PosterSupplier
127 VALUES
128 (1, 2),
129 (2, 3),
130 (3, 4),
131 (4, 1),
132 (5, 1),
133 (6, 3);
134
135INSERT INTO `Order`
136 VALUES
137 (1, 1, 4, 3, 4, 1, '2015-04-03', 110.23),
138 (DEFAULT, 2, 2, 6, 3, 2, '2015-04-03', 280.74),
139 (DEFAULT, 3, 3, 5, 2, 3, '2015-04-03', 370.65),
140 (DEFAULT, 4, 4, 4, 1, 4, '2015-04-03', 11.99),
141 (DEFAULT, 5, 5, 4, 3, 3, '2015-04-03', 1100.11),
142 (DEFAULT, 6, 6, 3, 1, 4, '2015-04-03', 340.98),
143 (DEFAULT, 4, 1, 3, 2, 3, '2015-04-03', 560.78),
144 (DEFAULT, 6, 2, 1, 1, 2, '2015-04-03', 789.34);
145
146
147SELECT * FROM Customer
148 JOIN `Order`
149 USING(CustomerId);
150
151SELECT * FROM Frame
152 JOIN `Order`
153 USING(FrameId);
154
155SELECT * FROM PosterSupplier;
156
157SELECT * FROM Poster
158 JOIN PosterSupplier
159 USING(PosterId)
160 JOIN Supplier
161 USING(SupplierId);
162
163SELECT * FROM `Order`
164 JOIN Glass
165 USING(GlassId)
166 JOIN Mat
167 USING(MatId)
168 JOIN Poster
169 USING(PosterId);