-
Notifications
You must be signed in to change notification settings - Fork 0
/
TableCreationScript.sql
352 lines (305 loc) · 11.8 KB
/
TableCreationScript.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
/*
Author: Ashwin Kannalath
ISC 567
Date: 2/16/2015
TABLE CREATION SCRIPT
/*************************
CREATE DATABASE AND TABLES
**************************/
*/
PRINT 'BEGIN SCRIPT EXECUTION'
GO
--Don't display number of rows inserted. Restricts Messages to print statements
SET NOCOUNT ON
GO
USE master
GO
-- Check if database exists and drop it if it does
IF EXISTS (SELECT * FROM sysdatabases WHERE name='ISC567_SSRS_Database')
BEGIN
DROP DATABASE ISC567_SSRS_Database;
PRINT 'Dropped database (Database with name ISC567_SSRS_Database Existed)';
END
GO
/* Create the Database */
--DROP DATABASE ISC567_SSRS_Database
CREATE DATABASE ISC567_SSRS_Database
GO
PRINT 'Database Created'
USE ISC567_SSRS_Database
GO
/* Create tables with appropriate attributes and data types */
--DROP TABLE Employee
CREATE TABLE Employee (
EmployeeID INT IDENTITY (1,1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
JobTitle VARCHAR(30) NOT NULL,
Supervisor BIT NULL,
ContractLabor BIT NULL,
WageRateRT MONEY NULL,
WageRateOT MONEY NULL,
HomePhone CHAR(15) NULL,
CellPhone CHAR(15) NULL,
Address VARCHAR(100) NULL,
EmailAddress VARCHAR(60) NULL,
Notes VARCHAR(100) NULL
)
GO
--DROP TABLE Customer
CREATE TABLE Customer (
CustomerID INT IDENTITY (1,1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Company VARCHAR(30) NULL,
Title VARCHAR(30) NULL,
BusinessPhone CHAR(15) NULL,
HomePhone CHAR(15) NULL,
CellPhone CHAR(15) NULL,
FaxNumber CHAR(15) NULL,
EmailAddress VARCHAR(60) NULL,
Notes VARCHAR(100) NULL
)
GO
--DROP TABLE Rate
CREATE TABLE Rate (
RateID INT IDENTITY (1,1) PRIMARY KEY,
JobType VARCHAR(40) NOT NULL,
Supervisor Bit,
RateRT MONEY NOT NULL,
RateOT MONEY NOT NULL
)
GO
--DROP TABLE Attendance
CREATE TABLE Attendance (
AttendanceID INT IDENTITY (1,1) PRIMARY KEY,
EmployeeID INT NOT NULL,
Date DATE NOT NULL,
Attendance VARCHAR(15) NULL,
HoursMissed DECIMAL NULL,
Reason VARCHAR(100) NULL,
Excused BIT NULL
)
GO
--DROP TABLE HoursWorked
CREATE TABLE HoursWorked (
HoursWorkedID INT IDENTITY (1,1) PRIMARY KEY,
EmployeeID INT NOT NULL,
JobID INT NOT NULL,
ItemNumberID INT NULL,
Date DATE NOT NULL,
HoursWorkedRT DECIMAL NULL,
HoursWorkedOT DECIMAL NULL
)
GO
--DROP TABLE Job
CREATE TABLE Job (
JobID INT IDENTITY (1,1) PRIMARY KEY,
JobNumber VARCHAR(100) NOT NULL,
CustomerID INT NOT NULL,
RateID INT NOT NULL,
VesselName VARCHAR(100) NOT NULL,
Priority INT NULL,
Status BIT NULL,
Description VARCHAR(100) NULL,
StartDate DATE NULL,
EndDate DATE NULL
)
GO
--DROP TABLE ItemNumber
CREATE TABLE ItemNumber (
ItemNumberID INT IDENTITY (1,1) PRIMARY KEY,
ItemNumber INT NOT NULL,
Description VARCHAR(100) NULL
)
GO
--DROP TABLE MaterialsExpense
CREATE TABLE MaterialsExpense (
MaterialsExpenseID INT IDENTITY (1,1) PRIMARY KEY,
Expense MONEY NOT NULL,
JobID INT NOT NULL,
ItemNumberID INT NULL, -- Remember an assumption/requirement is that each expense can be related to a particular task/ItemNumber
ExpenseDescription VARCHAR(50) NULL,
PONumber CHAR(7) NULL,
InvoiceNumber CHAR(8) NULL,
TaxIncluded BIT NULL,
TaxPercentage DECIMAL NULL,
MarkupPercentage DECIMAL NULL
)
GO
--DROP TABLE WageHistory
CREATE TABLE WageHistory (
WageHistoryID INT IDENTITY (1,1) PRIMARY KEY,
EmployeeID INT NOT NULL,
WageRT INT NOT NULL,
WageOT INT NOT NULL,
DateStart DATE NULL,
DateEnd DATE NULL,
IsCurrent BIT NOT NULL
)
GO
PRINT 'Tables Created'
--Insert data into Tables
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('DNU_In-house',0,46.00,46.00);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Billable Hours', 0, 57.00,70.00);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Affiliated',0,40,40);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Billable Hours',0,57,70);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Machine In-house',0,46,46);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Machine Billable Hours',0,66.50,81.50);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('DNU_Machine In-house',0,55,65);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('DNU_Machine Billable Hours',0,60,70);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Paid Time Off',0,0,0);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Crescent Towing',0,50,50);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('Crescent Towing',0,62,62);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('DNU_New In-house',0,46,46);
INSERT INTO Rate(JobType,Supervisor,RateRT,RateOT)
VALUES ('In-house',0,49,49);
PRINT ('table Rate Inserted');
SELECT * FROM Rate;
INSERT INTO Employee (FirstName,LastName, JobTitle, SuperVisor,ContractLabor,WageRateRT,WageRateOT,HomePhone,CellPhone,Address,EmailAddress,Notes)
VALUES('Donnie','Tibbetts', 'Welder',0,1,40.00,60.00,'(123)-234-2344','(456)-543-5697','12,cdr street,Pascagoula,MS-39581','xjcijjcdc@aol.com','');
INSERT INTO Employee (FirstName,LastName, JobTitle, SuperVisor,ContractLabor,WageRateRT,WageRateOT,HomePhone,CellPhone,Address,EmailAddress,Notes)
VALUES('Liza','Guzman', 'Programmer',0,1,60.00,80.00,'(123)-264-2944','(456)-533-5297','3452,abc street,Pascagoula,MS-39581','tyjcijdc@aol.com','');
INSERT INTO Employee (FirstName,LastName, JobTitle, SuperVisor,ContractLabor,WageRateRT,WageRateOT,HomePhone,CellPhone,Address,EmailAddress,Notes)
VALUES('Phil','Catlett', 'Cutter',0,1,42.00,62.00,'(123)-234-2394','(456)-243-5197','4562,vfdt street,Pascagoula,MS-39581','mnijjcdc@aol.com','');
GO
PRINT ('table Employee Inserted');
GO
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Ashwin', 'Kannalath','ABC Inc','Manager','(434)-374-9098',null,null,null,'npcijjcdc@aol.com','' );
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Sridhar Reddy','Mallu', 'XYZ Ltd.','CEO','(356)-462-4362',null,null,null,'lsjcijdc@aol.com','');
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Srisairamreddy','Menakuru','PDQ Org.','Project Manager','(836)-414-7676',null,null,null,'rrijjcdc@aol.com','');
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Karthik Chandra','Cherukuri','CTS Org.','CEO','(836)-454-7676',null,null,null,'rcndkjneioucdc@aol.com','' );
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Kamalnadh','Nimmagadda','EFG Org.','Manager','(836)-494-7676',null,null,null,'rsieudc@aol.com','');
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Lindsey','Cerkovnik','IRS Org.','Project Manager','(836)-424-7676',null,null,null,'rqwwerdc@aol.com','');
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Nisha','Patel','USA Org.','Manager','(836)-484-7676',null,null,null,'rrwjxeunbdc@aol.com','');
INSERT INTO Customer(FirstName,LastName,Company,Title, BusinessPhone,HomePhone,CellPhone,FaxNumber,EmailAddress,Notes)
VALUES('Ramya Keerthi','Katta','PPL Org.','Project Manager','(836)-334-7676',null,null,null,'rocirjdc@aol.com','');
GO
PRINT ('table Customer Inserted');
GO
INSERT INTO Attendance(EmployeeID,Date,Attendance,HoursMissed,Reason,Excused)
VALUES (1,'3-4-2015','7.5 Hours',0.5,'Late',1);
INSERT INTO Attendance(EmployeeID,Date,Attendance,HoursMissed,Reason,Excused)
VALUES (2,'3-4-2015','7.75 Hours',0.25,'Left Early',0);
INSERT INTO Attendance(EmployeeID,Date,Attendance,HoursMissed,Reason,Excused)
VALUES (3,'3-4-2015','7.90 Hours',0.10,'Traffic',1);
GO
PRINT ('table Attendence Inserted');
GO
INSERT INTO WageHistory(EmployeeID,WageRT,WageOT,DateStart,DateEnd,IsCurrent)
VALUES(1,40,60,'1-1-2015',Null,1);
INSERT INTO WageHistory(EmployeeID,WageRT,WageOT,DateStart,DateEnd,IsCurrent)
VALUES(2,60,80,'1-1-2015',Null,1);
INSERT INTO WageHistory(EmployeeID,WageRT,WageOT,DateStart,DateEnd,IsCurrent)
VALUES(3,42,62,'1-1-2015',Null,1);
GO
PRINT ('table WageHistory Inserted');
GO
INSERT INTO Job(JobNumber,CustomerID,RateID,VesselName,Priority,Status,Description,StartDate,EndDate)
VALUES(1,1,1,'VesselA',1,0,Null,'3-4-2015',Null);
INSERT INTO Job(JobNumber,CustomerID,RateID,VesselName,Priority,Status,Description,StartDate,EndDate)
VALUES(2,2,6,'VesselB',2,1,Null,'3-4-2015',null);
INSERT INTO Job(JobNumber,CustomerID,RateID,VesselName,Priority,Status,Description,StartDate,EndDate)
VALUES(3,3,4,'VesselC',3,0,null,'3-4-2015',null);
GO
PRINT ('table Job Inserted');
GO
INSERT INTO ItemNumber(ItemNumber,Description)
VALUES(1,'ItemA');
INSERT INTO ItemNumber(ItemNumber,Description)
VALUES(2,'ItemB');
INSERT INTO ItemNumber(ItemNumber,Description)
VALUES(3,'ItemC');
PRINT ('table ItemNumber Inserted');
GO
SELECT * FROM ItemNumber
INSERT INTO HoursWorked(EmployeeID,JobID,ItemNumberID,Date,HoursWorkedRT, HoursWorkedOT)
VALUES(1,1,1,'3-4-2015',7.5,0);
INSERT INTO HoursWorked(EmployeeID,JobID,ItemNumberID,Date,HoursWorkedRT, HoursWorkedOT)
VALUES(2,2,2,'3-4-2015',7.75,0);
INSERT INTO HoursWorked(EmployeeID,JobID,ItemNumberID,Date,HoursWorkedRT, HoursWorkedOT)
VALUES(3,3,3,'3-4-2015',7.90,0);
GO
PRINT ('table HoursWorked Inserted');
GO
INSERT INTO MaterialsExpense(Expense,JobID,ItemNumberID,ExpenseDescription,PONumber,InvoiceNumber,TaxIncluded,TaxPercentage,MarkUpPercentage)
VALUES(1000,1,1,Null,'1234567','00003746',1,6,15);
INSERT INTO MaterialsExpense(Expense,JobID,ItemNumberID,ExpenseDescription,PONumber,InvoiceNumber,TaxIncluded,TaxPercentage,MarkUpPercentage)
VALUES(20000,2,2,Null,'3747367','93847612',1,6,12);
INSERT INTO MaterialsExpense(Expense,JobID,ItemNumberID,ExpenseDescription,PONumber,InvoiceNumber,TaxIncluded,TaxPercentage,MarkUpPercentage)
VALUES(333333,3,3,Null,'1237654','09090909',1,6,15);
GO
PRINT ('table MaterialsExpense Inserted');
GO
PRINT ('All tables Inserted');
GO
--------------------------------------------------------------------------------------------------------
/******************
CREATE CONSTRAINTS
*******************/
/* Create foreign key constraints */
--ALTER TABLE Job DROP CONSTRAINT FK_Job_Rate
ALTER TABLE Job
ADD CONSTRAINT FK_Job_Rate
FOREIGN KEY (RateID) REFERENCES Rate(RateID)
GO
--ALTER TABLE Job DROP CONSTRAINT FK_Job_Customer
ALTER TABLE Job
ADD CONSTRAINT FK_Job_Customer
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
GO
--ALTER TABLE HoursWorked DROP CONSTRAINT FK_HoursWorked_Employee
ALTER TABLE HoursWorked
ADD CONSTRAINT FK_HoursWorked_Employee
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
GO
--ALTER TABLE HoursWorked DROP CONSTRAINT FK_HoursWorked_Job
ALTER TABLE HoursWorked
ADD CONSTRAINT FK_HoursWorked_Job
FOREIGN KEY (JobID) REFERENCES Job(JobID)
GO
--ALTER TABLE HoursWorked DROP CONSTRAINT FK_HoursWorked_ItemNumber
ALTER TABLE HoursWorked
ADD CONSTRAINT FK_HoursWorked_ItemNumber
FOREIGN KEY (ItemNumberID) REFERENCES ItemNumber(ItemNumberID)
GO
--ALTER TABLE MaterialsExpense DROP CONSTRAINT FK_MaterialsExpense_Job
ALTER TABLE MaterialsExpense
ADD CONSTRAINT FK_MaterialsExpense_Job
FOREIGN KEY (JobID) REFERENCES Job(JobID)
GO
--ALTER TABLE MaterialsExpense DROP CONSTRAINT FK_MaterialsExpense_ItemNumber
ALTER TABLE MaterialsExpense
ADD CONSTRAINT FK_MaterialsExpense_ItemNumber
FOREIGN KEY (ItemNumberID) REFERENCES ItemNumber(ItemNumberID)
GO
--ALTER TABLE Attendance DROP CONSTRAINT FK_Attendance_Employee
ALTER TABLE Attendance
ADD CONSTRAINT FK_Attendance_Employee
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
GO
--ALTER TABLE WageHistory DROP CONSTRAINT FK_WageHistory_Employee
ALTER TABLE WageHistory
ADD CONSTRAINT FK_WageHistory_Employee
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
GO
PRINT 'Foreign Key Constraints Created'