· 4 years ago · Feb 22, 2021, 02:56 PM
1DROP DATABASE IF EXISTS `advert_marketing`;
2
3-- =============================================
4-- Author: Jamie Young, Mena Oweh, James Roundtree & Harsh Mulani
5-- Create date: 21/02/2021
6-- Description: An implementation of our relational schema for C1.
7-- =============================================
8-- =============================================
9-- Table `Client`
10-- =============================================
11
12CREATE DATABASE IF NOT EXISTS advert_marketing ;
13USE advert_marketing;
14
15DROP TABLE IF EXISTS `Client`;
16CREATE TABLE `Client` (
17 -- table for a Client with Unique Identifier
18 `clientID` INTEGER NOT NULL AUTO_INCREMENT,
19 -- First and Last Name of the Client Company's Representative
20 `firstName` VARCHAR(150),
21 `lastName` VARCHAR(150),
22 `companyName` VARCHAR(100) NOT NULL,
23 `email` Varchar(1024),
24 `campaignID` INTEGER,
25 `invoiceID` INTEGER,
26 PRIMARY KEY (`clientID`, `companyName`)
27);
28CREATE TABLE `Campaign` (
29-- table for Campaigns with unique ID
30 `campaignID` INTEGER NOT NULL AUTO_INCREMENT,
31 `clientID` INTEGER,
32 `campaignName` VARCHAR(100) NOT NULL,
33 -- total cost of campaign in GBP (Sterling Pounds)
34 `totalCost` FLOAT,
35 -- Web, Radio or TV advert type
36 `advertType` VARCHAR(25) NOT NULL,
37 `numberOfAdsBought` INTEGER,
38 -- Campaign run length
39 `startDate` DATETIME,
40 `endDate` DATETIME,
41 PRIMARY KEY (`campaignID`, `campaignName`, `advertType`),
42 FOREIGN KEY (`clientID`) REFERENCES `Client` (`clientID`)
43);
44CREATE TABLE `Advert` (
45 `startDate` DATETIME,
46 `endDate` DATETIME,
47 -- If TV or Radio, will state time slot and length in seconds and number of broadcasts and other details
48 `timeOfDay` TIME,
49 `lengthOfAd` INTEGER,
50 `numberOfBroadcasts` INTEGER,
51 `station` VARCHAR(50),
52 `releaseDate` DATETIME,
53 `advertID` INTEGER,
54 `targetDemographic` VARCHAR(50),
55 `targetRegion` VARCHAR (50),
56 `totalCost` FLOAT,
57 -- If Magazine advert, will state magazine name, position of ad in inches with no margins and size of ad in inches and the number of issues the ad is placed in.
58 `magazineName` VARCHAR (100),
59 `position` VARCHAR (50),
60 `size` INTEGER,
61 `numberOfIssues` INTEGER
62);
63CREATE TABLE `Invoice` (
64-- Unique ID for each invoice issued to a client.
65 `invoiceID` INTEGER UNIQUE,
66 `campaignID` INTEGER,
67 `totalCost` FLOAT,
68 PRIMARY KEY (`invoiceID`, `totalCost`),
69 FOREIGN KEY (`campaignID`) REFERENCES `Campaign` (`campaignID`)
70);
71CREATE TABLE `Department` (
72-- UNIQUE ID for department and the name of the department
73 `departmentID` INTEGER NOT NULL UNIQUE AUTO_INCREMENT,
74 `department` VARCHAR(50),
75 -- manager and unique ID of the manager
76 `departmentManager` VARCHAR(100),
77 `managerID` INTEGER,
78 PRIMARY KEY (`departmentID`, `department`, `departmentManager`),
79 FOREIGN KEY (`managerID`) REFERENCES `Manager` (`managerID`)
80);
81CREATE TABLE `Employee` (
82-- Unique ID of employee and personal ID as in, National Insurance Number or WORK ID/Work Visa.
83 `employeeID` INTEGER UNIQUE,
84 `personalID` INTEGER,
85 -- Hourly salary in GBP (Sterling Pound)
86 `hourlyRate` FLOAT,
87 -- Employee's department details and personal details.
88 `departmentID` INTEGER,
89 `department` VARCHAR(50),
90 `firstName` VARCHAR(150),
91 `lastName` VARCHAR(150),
92 `dateOfBirth` DATE,
93 `gender` CHAR(1),
94 `employeeEmail` VARCHAR(1024),
95 PRIMARY KEY (`employeeID`),
96 FOREIGN KEY (`departmentID`) REFERENCES `Department` (`departmentID`),
97 FOREIGN KEY (`department`) REFERENCES `Department` (`department`)
98);
99CREATE TABLE `Employee Wage Invoice` (
100 `campaignID` INTEGER,
101 `employeeID` INTEGER,
102 `invoiceID` INTEGER,
103 `clientID` INTEGER,
104 `totalCost` FLOAT,
105 `hourlyRate` FLOAT,
106 -- number of hours worked on the campaign by the Employee.
107 `hours` INTEGER,
108 -- hourly rate * hours
109 `totalPay` FLOAT,
110 FOREIGN KEY (`campaignID`) REFERENCES `Campaign` (`campaignID`),
111 FOREIGN KEY (`employeeID`) REFERENCES `Employee` (`employeeID`),
112 FOREIGN KEY (`invoiceID`) REFERENCES `Invoice` (`invoiceID`),
113 FOREIGN KEY (`clientID`) REFERENCES `Client` (`clientID`),
114 FOREIGN KEY (`invoiceID`) REFERENCES `Invoice` (`invoiceID`),
115 FOREIGN KEY (`totalCost`) REFERENCES `Campaign` (`totalCost`),
116 FOREIGN KEY (`hourlyRate`) REFERENCES `Employee` (`hourlyRate`)
117);
118CREATE TABLE `Manager` (
119-- Unique ID for managers as there will be multiple.
120 `managerID` INTEGER UNIQUE,
121 `employeeID` INTEGER,
122 `departmentID` INTEGER,
123 `department` VARCHAR(50),
124 FOREIGN KEY (`employeeID`) REFERENCES `Employee` (`employeeID`),
125 PRIMARY KEY (`managerID`),
126 FOREIGN KEY (`departmentID`) REFERENCES `Department` (`departmentID`),
127 FOREIGN KEY (`department`) REFERENCES `Department` (`department`)
128);
129
130INSERT INTO `Client` VALUES (
131 NULL,
132 "Bob",
133 "Merlock",
134 "MerCo",
135 "BobMerlock@Merco.com",
136 24,
137 59
138);
139
140INSERT INTO `Campaign` VALUES (
141 NULL,
142 24,
143 "The big campaign",
144 24.22,
145 "media",
146 4052,
147 "2021-02-22",
148 "2021-02-23"
149);
150
151INSERT INTO `Advert` VALUES (
152 "2021-02-22",
153 "2021-02-23",
154 1112,
155 45,
156 3,
157 "Random station",
158 "2021-04-10",
159 42,
160 "Adults",
161 "Blackpool",
162 4396.42,
163 "Random magazine name",
164 "front",
165 2,
166 45993
167);
168
169INSERT INTO `Invoice` VALUES (
170 32,
171 01,
172 49253.53
173);
174INSERT INTO `Campaign` VALUES
175 (
176 NULL,
177 24,
178 "The big campaign",
179 24.22,
180 "media",
181 4052,
182 "2021-02-22",
183 "2021-02-23"
184 );
185INSERT INTO `Department` VALUES (
186 NULL,
187 "PROMOTION",
188 "Alice",
189 01
190);
191INSERT INTO `Department` VALUES (
192 NULL,
193 "CLEANING",
194 "Jeremy",
195 02
196);
197INSERT INTO `Employee` VALUES (
198 24,
199 24,
200 12,
201 01,
202 "PROMOTION",
203 "Alice",
204 "Chains",
205 "1976-02-21",
206 "F",
207 "alicechain@hwmarketing.com"
208);
209INSERT INTO `Employee` VALUES (
210 36,
211 36,
212 8.24,
213 01,
214 "PROMOTION",
215 "Checo",
216 "Perez",
217 "1990-01-02",
218 "M",
219 "checoperez@hwmarketing.com"
220);
221
222INSERT INTO `Employee Wage Invoice` VALUES (
223 01,
224 36,
225 32,
226 01,
227 49253.53,
228 8.24,
229 48,
230 395.52
231);
232
233INSERT INTO `Manager` VALUES (
234 01,
235 24,
236 01,
237 "PROMOTION"
238);