RNzJUnQY

1/*
2* FILE : DBcreationScript.sql
3* PROJECT : PROG3070 - Project Milestone 01
4* PROGRAMMER : Andrii Dushkevych, Phil Kempton
5* FIRST VERSION : 2019-03-18
6* DESCRIPTION :
7* This file contains DB creation script and insert statements for ConfigInfo table
8*/
9
10--Drop if exists and create database
11DROP DATABASE IF EXISTS AdvSqlProjectDB
12GO
13CREATE DATABASE AdvSqlProjectDB
14GO
15SET ANSI_NULLS ON
16GO
17
18SET QUOTED_IDENTIFIER ON
19GO
20
21--Create table Part
22CREATE TABLE [AdvSqlProjectDB].[dbo].[Part](
23	[Id] [int] IDENTITY(1,1) NOT NULL,
24	[Name] [varchar](255) NULL,
25PRIMARY KEY CLUSTERED 
26(
27	[Id] ASC
28)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
29) ON [PRIMARY]
30GO
31
32--Create table ConfigInfo
33CREATE TABLE [AdvSqlProjectDB].[dbo].[ConfigInfo](
34	[Id] [int] IDENTITY(1,1) NOT NULL,
35	[Key] [nvarchar](255) NOT NULL,
36	[Value] [nvarchar](255) NOT NULL,
37PRIMARY KEY CLUSTERED 
38(
39	[Id] ASC
40)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
41) ON [PRIMARY]
42GO
43
44--Insert configuration data into ConfigInfo table
45INSERT INTO [AdvSqlProjectDB].[dbo].[ConfigInfo]([Key], [Value]) VALUES 
46('HarnessBinAmount', '55'),
47('ReflectorBinAmount', '35'),
48('HousingBinAmount', '24'),
49('LensBinAmount', '40'),
50('BulbBinAmount', '60'),
51('BezelBinAmount', '75'),
52('CriticalBinAmount', '5'),
53('ExperiencedWorkerAssemblyTimeSeconds', '60'),
54('ExperiencedWorkerAssemblyTimeAdjustmentPrecentage', '10'),
55('NewWorkerAssemblyTimeAdjustmentPrecentage', '50'),
56('SuperWorkerAssemblyTimeAdjustmentPrecentage', '-15'),
57('ExperiencedWorkerDefectRatePercentage', '0.5'),
58('NewWorkerDefectRatePercentage', '0.85'),
59('SuperWorkerDefectRatePercentage', '0.15'),
60('TrayCapacity', '60'),
61('CardPickUpTimeoutSeconds', '300'),
62('BinRefilTimeSeconds', '300'),
63('TimeFactor','60')
64GO
65
66--Create table EmployeeType
67CREATE TABLE [AdvSqlProjectDB].[dbo].[EmployeeType](
68	[Id] [int] IDENTITY(1,1) NOT NULL,
69	[Name] [nvarchar](255) NOT NULL,
70	[TimeFactorConfItemId] [int] NOT NULL,
71	[DefectRateConfItemId] [int] NOT NULL,
72PRIMARY KEY CLUSTERED 
73(
74	[Id] ASC
75)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
76) ON [PRIMARY]
77GO
78
79ALTER TABLE [AdvSqlProjectDB].[dbo].[EmployeeType] ADD FOREIGN KEY([DefectRateConfItemId])
80REFERENCES [AdvSqlProjectDB].[dbo].[ConfigInfo] ([Id])
81GO
82
83ALTER TABLE [AdvSqlProjectDB].[dbo].[EmployeeType] ADD FOREIGN KEY([TimeFactorConfItemId])
84REFERENCES [AdvSqlProjectDB].[dbo].[ConfigInfo] ([Id])
85GO
86
87--Create table Employee
88CREATE TABLE [AdvSqlProjectDB].[dbo].[Employee](
89	[Id] [int] IDENTITY(1,1) NOT NULL,
90	[TypeId] [int] NOT NULL,
91PRIMARY KEY CLUSTERED 
92(
93	[Id] ASC
94)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
95) ON [PRIMARY]
96GO
97
98ALTER TABLE [AdvSqlProjectDB].[dbo].[Employee]  WITH CHECK ADD FOREIGN KEY([TypeId])
99REFERENCES [AdvSqlProjectDB].[dbo].[EmployeeType] ([Id])
100GO
101
102--Create table Tray
103CREATE TABLE [AdvSqlProjectDB].[dbo].[Tray](
104	[Id] [int] IDENTITY(1,1) NOT NULL,
105	[Number] [int] NOT NULL,
106	[LampAmountConfItemId] [int] NOT NULL,
107	[IsFull] [BIT] NOT NULL DEFAULT 0,
108	[WorkstationId] [INT] NOT NULL,
109PRIMARY KEY CLUSTERED 
110(
111	[Id] ASC
112)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
113) ON [PRIMARY]
114GO
115
116ALTER TABLE [AdvSqlProjectDB].[dbo].[Tray]  WITH CHECK ADD FOREIGN KEY([LampAmountConfItemId])
117REFERENCES [AdvSqlProjectDB].[dbo].[ConfigInfo] ([Id])
118GO
119
120
121--Create table Workstation
122CREATE TABLE [AdvSqlProjectDB].[dbo].[Workstation](
123	[Id] [int] IDENTITY(1,1) NOT NULL,
124	[EmployeeId] [int] NOT NULL,
125PRIMARY KEY CLUSTERED 
126(
127	[Id] ASC
128)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
129) ON [PRIMARY]
130GO
131
132ALTER TABLE [AdvSqlProjectDB].[dbo].[Workstation]  WITH CHECK ADD FOREIGN KEY([EmployeeId])
133REFERENCES [AdvSqlProjectDB].[dbo].[Employee] ([Id])
134GO
135
136ALTER TABLE [AdvSqlProjectDB].[dbo].[Tray]  WITH CHECK ADD FOREIGN KEY([WorkstationId])
137REFERENCES [AdvSqlProjectDB].[dbo].[Workstation] ([Id])
138GO
139
140--Create table Bin
141CREATE TABLE [AdvSqlProjectDB].[dbo].[Bin](
142	[Id] [int] IDENTITY(1,1) NOT NULL,
143	[PartId] [int] NOT NULL,
144	[WorkstationId] [int] NOT NULL,
145	[PartCount] [int] NOT NULL,
146	[AmountConfItemId] [int] NOT NULL,
147PRIMARY KEY CLUSTERED 
148(
149	[Id] ASC
150)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
151) ON [PRIMARY]
152GO
153
154ALTER TABLE [AdvSqlProjectDB].[dbo].[Bin]  WITH CHECK ADD FOREIGN KEY([AmountConfItemId])
155REFERENCES [AdvSqlProjectDB].[dbo].[ConfigInfo] ([Id])
156GO
157
158ALTER TABLE [AdvSqlProjectDB].[dbo].[Bin]  WITH CHECK ADD FOREIGN KEY([PartId])
159REFERENCES [AdvSqlProjectDB].[dbo].[Part] ([Id])
160GO
161
162ALTER TABLE [AdvSqlProjectDB].[dbo].[Bin]  WITH CHECK ADD FOREIGN KEY([WorkstationId])
163REFERENCES [AdvSqlProjectDB].[dbo].[Workstation] ([Id])
164GO
165
166
167--Create table Lamp
168CREATE TABLE [AdvSqlProjectDB].[dbo].[Lamp](
169	[Id] [int] IDENTITY(1,1) NOT NULL,
170	[Number] [int] NOT NULL,
171	[TestPassed] [bit] NULL,
172	[TrayId] [int] NOT NULL,
173PRIMARY KEY CLUSTERED 
174(
175	[Id] ASC
176)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
177) ON [PRIMARY]
178GO
179
180ALTER TABLE [AdvSqlProjectDB].[dbo].[Lamp]  WITH CHECK ADD FOREIGN KEY([TrayId])
181REFERENCES [AdvSqlProjectDB].[dbo].[Tray] ([Id])
182GO
183
184
185
186--Create table Runner
187CREATE TABLE [AdvSqlProjectDB].[dbo].[Runner](
188	[Id] [int] IDENTITY(1,1) NOT NULL,
189	[WorkstationId] [int] NOT NULL,
190	[isRunning] [bit] NOT NULL,
191PRIMARY KEY CLUSTERED 
192(
193	[Id] ASC
194)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
195) ON [PRIMARY]
196GO
197
198ALTER TABLE [AdvSqlProjectDB].[dbo].[Runner]  WITH CHECK ADD FOREIGN KEY([WorkstationId])
199REFERENCES [AdvSqlProjectDB].[dbo].[Workstation] ([Id])
200GO
201
202
203-- Insert initial Employee Types
204INSERT INTO [AdvSqlProjectDB].[dbo].[EmployeeType] (Name, TimeFactorConfItemId, DefectRateConfItemId) VALUES ('Experienced', 9, 12);
205INSERT INTO [AdvSqlProjectDB].[dbo].[EmployeeType] (Name, TimeFactorConfItemId, DefectRateConfItemId) VALUES ('Rookie', 10, 13);
206INSERT INTO [AdvSqlProjectDB].[dbo].[EmployeeType] (Name, TimeFactorConfItemId, DefectRateConfItemId) VALUES ('Super', 11, 14);
207
208-- Insert initial Employees (1 of each type)
209INSERT INTO [AdvSqlProjectDB].[dbo].[Employee] (TypeId) VALUES (3);
210INSERT INTO [AdvSqlProjectDB].[dbo].[Employee] (TypeId) VALUES (2);
211INSERT INTO [AdvSqlProjectDB].[dbo].[Employee] (TypeId) VALUES (1);
212
213-- Insert initial Part types
214INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Harness');
215INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Reflector');
216INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Housing');
217INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Lens');
218INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Bulb');
219INSERT INTO [AdvSqlProjectDB].[dbo].[Part] (Name) VALUES ('Bezel');
220
221
222-- Procedure: start_new_lamp
223-- Description: This procedure should be called when a worstation starts making a new lamp
224--				This procedure decrements all bins belonging to a worksation by 1
225--				This procedure should be called synchronously so that the worksation doesn't start counting
226--					down remaining time to assemble parts until the bins have parts in them.
227-- Parameters:	
228--		@StationId:	The Id of the workstation who calls this procedure
229-- Returns: n/a
230USE [AdvSqlProjectDB]
231GO
232CREATE PROCEDURE start_new_lamp 
233@StationId nvarchar(50)
234AS
235	DECLARE
236	@MinPartCount [INT];
237
238	SET @MinPartCount = (SELECT MIN(PartCount) FROM Bin WHERE WorkstationId = @StationId);
239
240	-- Wait until we have parts
241	WHILE (@MinPartCount = 0)
242	BEGIN
243		SET @MinPartCount = (SELECT MIN(PartCount) FROM Bin WHERE WorkstationId = @StationId);
244		WAITFOR DELAY '00:00:01';
245	END
246
247	
248	-- Decrement all bins belonging to this Workstation
249	UPDATE Bin
250	SET Bin.PartCount = PartCount - 1
251	WHERE WorkstationId = @StationId;
252GO
253
254
255-- Procedure: SwapTray
256-- Description: This procedure Swaps out a full tray for a new tray
257-- Parameters:	
258--		@StationId:	The Id of the workstation who calls this procedure
259--		@TrayId:	The Id of the Tray to swap out
260-- Returns: n/a
261CREATE PROCEDURE SwapTray @StationId nvarchar(50), @TrayId nvarchar(50)
262AS
263	-- Declare local variables to store relevant row id's
264	DECLARE
265	@TrayCapacity INT,		-- Max capacity of a tray
266	@Rand INT,				-- Will store random value beteen 0.00 and 100.00, used to process defects
267	@EmployeeId INT,		-- Id of the workstations employee
268	@EmployeeTypeId INT,	-- Id of the type of employee
269	@DefectRate FLOAT,		-- Decimal number that is defect rate
270	@DefectConfId INT,		-- Id in config info table that is our defect rate
271	@DefectRateString VARCHAR(20),	-- String of defect rate
272	@LampNum INT = 1;		-- Keep track of which lamp in tray is being tested
273
274	-- Get the defect rate
275	SELECT @EmployeeId = EmployeeId FROM Workstation WHERE Id = @StationId;
276	SELECT @EmployeeTypeId = TypeId FROM Employee WHERE Id = @EmployeeId;
277	SELECT @DefectConfId = DefectRateConfItemId FROM EmployeeType WHERE Id = @EmployeeTypeId;
278	SELECT @DefectRateString = [Value] FROM ConfigInfo WHERE [Key] = @DefectConfId;
279	SET @DefectRate = CAST(@DefectRateString AS DECIMAL);
280
281	-- Get Tray Capacity to loop through all lamps, assigning defect status
282	SET @TrayCapacity = (SELECT CAST(Value AS INT) 
283						 FROM ConfigInfo 
284						 WHERE Id = 15);
285
286		-- Set the Tray as isFull = 1
287		UPDATE Tray 
288		SET isFull = 1
289		WHERE Id = @TrayId;
290
291		-- Assign all the lamps in the finished tray a Test Result based on the defect rate
292		WHILE (@LampNum <= @TrayCapacity)
293		BEGIN
294			SELECT @Rand = RAND()*100;
295			IF (@Rand <= @DefectRate)
296			-- Lamp Failed Test
297				UPDATE LAMP SET TestPassed = 0 WHERE TrayId = @TrayId AND Number = @LampNum;
298			ELSE
299			BEGIN
300			-- Lamp Passed Test
301				UPDATE LAMP SET TestPassed = 1 WHERE TrayId = @TrayId AND Number = @LampNum;
302			END;
303			SET @LampNum = @LampNum + 1;
304		END;
305
306		-- Insert new tray into tray table
307		INSERT INTO Tray (Number, LampAmountConfItemId, IsFull) 
308		VALUES (0, 15, 0);
309GO
310
311
312-- Procedure: finish_lamp
313-- Description: This procedure should be called when a worstation finishes a lamp.
314--				This procedure inserts a new lamp into the current tray.
315--				This procedure will also swap out the current tray for a new one if it's full.
316--				This procedure shoudl be calle asynchronously so that the workstation can still start a new part
317--					even if runner is getting more bins
318-- Parameters:	
319--		@StationId:	The Id of the workstation who calls this procedure
320-- Returns: n/a
321CREATE PROCEDURE finish_lamp @StationId nvarchar(50)
322AS
323	-- Declare some variables for this procedure
324	DECLARE
325	@TrayId INT,		-- Id of the calling workstation's tray
326	@TrayCapacity INT,	-- Max capacity of a tray
327	@LampPosition INT	-- Position in tray of this newly inserted lamp (1 - @TrayCapacity)
328
329
330	-- Add tray for this workstation if one isn't currently 
331	IF NOT EXISTS(SELECT Id FROM Tray WHERE Id = @StationId AND isFull = 0)
332	BEGIN
333		INSERT INTO Tray (Number, LampAmountConfItemId, IsFull) 
334		VALUES (0, 15, 0);
335	END
336
337	-- Get The Id of current Tray
338	SELECT @TrayId = Id 
339	FROM Tray
340	WHERE Id = @StationId AND isFull = 0;
341
342	-- Increment number of lamps in current tray
343	UPDATE Tray
344	SET Tray.Number = Tray.Number + 1
345	WHERE Tray.ID = @TrayId;
346
347	-- Get Position of this new lamp
348	SELECT @LampPosition = Number 
349	FROM Tray
350	WHERE Tray.Id = @TrayId;
351
352	-- Insert the new lamp into the current Tray
353	INSERT INTO [AdvSqlProjectDB].[dbo].[Lamp] (Number, TestPassed, TrayId) VALUES (@LampPosition, NULL, @TrayId);
354
355	-- Get Tray Capacity to check if tray is full
356	SET @TrayCapacity = (SELECT CAST(Value AS INT) 
357						 FROM ConfigInfo 
358						 WHERE Id = 15);
359
360	-- If the current tray is full, swap it out for a new one.
361	IF @LampPosition = @TrayCapacity
362	BEGIN
363		EXEC SwapTray @StationId, @TrayId;
364	END
365GO
366
367
368-- Procedure: refill_bin
369-- Description: This procedure refills a part bin.
370--				Current count is added to new bin.
371-- Parameters:	
372--		@BinId:	The Id of the bin
373-- Returns: n/a
374CREATE PROCEDURE refill_bin @BinId INT
375AS
376	DECLARE
377	@CurrentAmount INT,
378	@MaxCapacityConfigId INT,
379	@BinMaxCapacity INT;
380
381	SELECT @CurrentAmount = PartCount FROM BIN WHERE Id = @BinId;
382	SELECT @MaxCapacityConfigId = AmountConfItemId FROM Bin WHERE Id = @BinId;
383	SELECT @BinMaxCapacity = [Value] FROM ConfigInfo WHERE Id = @MaxCapacityConfigId;
384	UPDATE Bin SET PartCount = @CurrentAmount + @BinMaxCapacity WHERE Id = @BinId;
385GO
386
387
388
389-- THIS STUFF IS FOR TESTING PROCEDURES
390-- THESE THINGS SHOULD ONLY BE CREATED BY THE SIMULATOR
391-- THIS STUFF SHOULD NOT BE INCLUDED IN FINAL VERSION
392
393-- Insert Workstations for testing, 1 per employees from above (These do not need to exist in final release)
394INSERT INTO [AdvSqlProjectDB].[dbo].[Workstation] (EmployeeId) VALUES (1);
395
396-- Insert initial Bins for testing (These do not need to exist in final release)
397INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (1, 1, 55, 1);
398INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (2, 1, 35, 2);
399INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (3, 1, 24, 3);
400INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (4, 1, 40, 4);
401INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (5, 1, 60, 5);
402INSERT INTO [AdvSqlProjectDB].[dbo].[Bin] (PartId, WorkstationId, PartCount, AmountConfItemId) VALUES (6, 1, 75, 6);
403
404-- Insert a tray for testing purposes
405INSERT INTO [AdvSqlProjectDB].[dbo].[Tray] (Number, LampAmountConfItemId, WorkstationId) VALUES (0, 15, 1);