· 6 years ago · May 23, 2019, 04:22 PM
1USE dbeksamen
2DROP TABLE IF EXISTS timeregistration
3DROP TABLE IF EXISTS employeeprojects
4DROP TABLE IF EXISTS employee
5DROP TABLE IF EXISTS boss
6DROP TABLE IF EXISTS project
7DROP TABLE IF EXISTS customer
8
9CREATE TABLE customer
10(
11id INT IDENTITY PRIMARY KEY,
12customername VARCHAR(50)
13)
14CREATE TABLE project
15(
16id INT IDENTITY PRIMARY KEY,
17projectname VARCHAR(50),
18projectstatus CHAR(9) CHECK(projectstatus IN ('active','inactive')),
19customerid INT NOT NULL REFERENCES customer,
20fixedprice INT,
21estimatedhours INT,
22hourlyrate INT
23)
24CREATE TABLE boss
25(
26id INT IDENTITY PRIMARY KEY,
27name VARCHAR(50)
28)
29CREATE TABLE employee
30(
31id INT IDENTITY PRIMARY KEY,
32employeename VARCHAR(50),
33bossid INT NOT NULL REFERENCES boss,
34timeoff INT
35)
36CREATE TABLE employeeprojects
37(
38id INT IDENTITY PRIMARY KEY,
39employeeid INT NOT NULL REFERENCES employee,
40projectid INT NOT NULL REFERENCES project
41)
42CREATE TABLE timeregistration
43(
44id INT IDENTITY PRIMARY KEY,
45starttime datetime,
46endtime datetime,
47employeeid INT NOT NULL REFERENCES employee,
48projectid INT NOT NULL REFERENCES project,
49note VARCHAR(500)
50)
51
52GO
53INSERT INTO customer VALUES ('microsoft'), ('apple'), ('internal-projects')
54
55INSERT INTO project VALUES ('projectOne', 'active', 1, 5000, 10, NULL),
56('projectTwo', 'inactive', 1, NULL, NULL, 500),
57('projectThree', 'active', 2, 15000, 30, NULL),
58('projectFour', 'active', 2, 6000, 11, NULL)
59
60INSERT INTO boss VALUES ('jackson'), ('peterson')
61
62INSERT INTO employee VALUES ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)
63
64INSERT INTO employeeprojects VALUES (1, 1), (1,2), (2,3), (3,4)
65
66INSERT INTO timeregistration VALUES ((CONVERT(datetime,'19-05-19 06:00:00 PM',5)), (CONVERT(datetime,'19-05-19 08:00:00 PM',5)), 1, 1, '2 hours on projectOne'),
67((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectTwo'),
68((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 3, '2 hours on projectThree'),
69((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 3, 4, '2 hours on projectFour'),
70((CONVERT(datetime,'13-05-19 03:00:00 PM',5)), (CONVERT(datetime,'13-05-19 06:00:00 PM',5)), 2, 3, '3 hours on projectThree')
71
72-- Ex2) ---------------------------------------------------
73GO
74CREATE INDEX Employeetbl_timeoff
75ON employee (timeoff ASC)
76
77GO
78SELECT * FROM employee WITH(INDEX(Employeetbl_timeoff))
79
80CREATE INDEX Projecttbl_fixedprice
81ON project (fixedprice ASC)
82
83GO
84SELECT * FROM project WITH(INDEX(Projecttbl_fixedprice))
85
86-- Ex3) ---------------------------------------------------
87GO
88SELECT e.employeename
89FROM employee e
90JOIN timeregistration t ON e.id = t.employeeid
91JOIN project p ON t.projectid = p.id
92JOIN customer c ON c.id = p.customerid
93WHERE c.customername = 'apple'
94
95--Ex4) -----------------------------------------------------
96GO
97CREATE OR ALTER TRIGGER timeregistrationTrigger
98ON timeregistration
99instead OF INSERT
100AS
101BEGIN
102 DECLARE @daysbefore INT
103 DECLARE @employeeonproject INT
104 DECLARE @projectstatus CHAR(9)
105
106 SELECT @daysbefore = DATEDIFF(DAY, GETDATE(), (SELECT starttime FROM inserted))
107 IF(@daysbefore <= -7)
108 BEGIN
109 RAISERROR('Noget gik galt', 16, 1)
110 RETURN
111 END
112
113 SELECT @employeeonproject = ep.employeeid
114 FROM employeeprojects ep
115 WHERE ep.employeeid = (SELECT employeeid FROM inserted) AND ep.projectid = (SELECT projectid FROM inserted)
116 IF(@employeeonproject IS NULL)
117 BEGIN
118 RAISERROR('Noget gik galt', 16, 1)
119 RETURN
120 END
121
122 SELECT @projectstatus = p.projectstatus
123 FROM project p
124 WHERE p.id = (SELECT projectid FROM inserted)
125 IF(@projectstatus = 'inactive')
126 BEGIN
127 RAISERROR('Noget gik galt', 16, 1)
128 RETURN
129 END
130
131 INSERT INTO timeregistration VALUES (
132 (SELECT starttime FROM inserted),
133 (SELECT endtime FROM inserted),
134 (SELECT employeeid FROM inserted),
135 (SELECT projectid FROM inserted),
136 (SELECT note FROM inserted))
137END
138
139--test til krav1)
140INSERT INTO timeregistration VALUES ((CONVERT(datetime,'14-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 1, '2 hours on projectOne')
141--test til krav 2)
142INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 1, '2 hours on projectOne')
143--test til krav 3)
144INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectOne')
145
146--Ex5) ------------------------------------------------------------------
147GO
148CREATE OR ALTER FUNCTION dbo.antalhverdageiMinutter(@aar INT ,@maaned INT)
149-- returnerer antal minutter i hverdage i den pågældende måned
150RETURNS INT
151AS
152BEGIN
153DECLARE @res INT
154DECLARE @firstdayofmonth DATE = datefromparts(@aar,@maaned,1)
155DECLARE @firstdayofnextmonth DATE = dateadd(mm,1,@firstdayofmonth)
156DECLARE @lastdateofmonth DATE = dateadd(dd,-1,@firstdayofnextmonth)
157IF DAY(@lastdateofmonth) = 28
158 SET @res = 20
159ELSE
160 IF DAY(@lastdateofmonth) = 29
161 IF datename(DW,@firstdayofmonth) IN ('Saturday','Sunday')
162 SET @res = 20
163 ELSE
164 SET @res = 21
165 ELSE
166 IF DAY(@lastdateofmonth) = 30
167 IF datename(DW,@firstdayofmonth) = 'Saturday'
168 SET @res = 20
169 ELSE
170 IF datename(DW,@firstdayofmonth) IN ('Friday','Sunday')
171 SET @res = 21
172 ELSE SET @res = 22
173 ELSE
174 IF DAY(@lastdateofmonth) = 31
175 IF datename(DW,@firstdayofmonth) IN ('Friday','Saturday')
176 SET @res = 21
177 ELSE
178 IF datename(DW,@firstdayofmonth) IN ('Thursday','Sunday')
179 SET @res = 22
180 ELSE SET @res = 23
181RETURN 444*@res
182END
183
184GO
185CREATE OR ALTER proc updatetimeoff
186 @monthParameter INT,
187 @yearParameter INT
188AS
189BEGIN
190 DECLARE @employeeid INT
191 DECLARE @timeoff INT
192 DECLARE @starttime datetime
193 DECLARE @endtime datetime
194 DECLARE @lastid INT = 1
195 DECLARE @minutes INT = 0
196 DECLARE @finalminutes INT = 0
197 DECLARE @numberOfRows INT = 0
198
199 DECLARE MY_CURSOR cursor
200 LOCAL static forward_only
201 FOR
202 SELECT e.id, e.timeoff, t.starttime, t.endtime
203 FROM employee e
204 JOIN timeregistration t ON t.employeeid = e.id
205
206 OPEN MY_CURSOR
207 fetch NEXT FROM MY_CURSOR INTO @employeeid, @timeoff, @starttime, @endtime
208 while (@@FETCH_STATUS = 0)
209 BEGIN
210 IF MONTH(@starttime) = @monthParameter AND YEAR(@starttime) = @yearParameter
211 BEGIN
212 IF @lastid = @employeeid
213 BEGIN
214 IF DATENAME(DW, @starttime) = 'Sunday'
215 BEGIN
216 SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
217 END
218 ELSE
219 BEGIN
220 SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
221 END
222 END
223 ELSE
224 BEGIN
225 UPDATE employee
226 SET timeoff += (@minutes - dbo.antalhverdageiMinutter(@yearParameter, @monthParameter))
227 WHERE id = @lastid
228 SET @lastid += 1
229 SET @minutes = 0
230 SET @finalminutes = 0
231 IF DATENAME(DW, @starttime) = 'Sunday'
232 BEGIN
233 SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
234 END
235 ELSE
236 BEGIN
237 SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
238 END
239 END
240 END
241 fetch NEXT FROM MY_CURSOR INTO @employeeid, @timeoff, @starttime, @endtime
242 END
243
244 close MY_CURSOR
245END
246
247EXEC updatetimeoff 5, 2019
248
249SELECT * FROM employee
250
251-- Ex8) ---------------------------------
252GO
253EXEC sp_addlogin 'admin','123'
254EXEC sp_addlogin 'employee','123'
255
256EXEC sp_grantdbaccess 'admin'
257EXEC sp_grantdbaccess 'employee'
258
259EXEC sp_addrole 'administrators'
260EXEC sp_addrole 'employees'
261
262EXEC sp_addrolemember 'administrators','admin'
263EXEC sp_addrolemember 'employees','employee'
264
265GRANT SELECT, INSERT, UPDATE, DELETE
266ON DATABASE::dbeksamen
267TO [admin]
268
269GRANT SELECT, INSERT, UPDATE, DELETE
270ON object::dbeksamen.dbo.timeregistration
271TO [employee]