SHARE
TWEET

Untitled

a guest May 23rd, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE dbeksamen
  2. DROP TABLE IF EXISTS timeregistration
  3. DROP TABLE IF EXISTS employeeprojects
  4. DROP TABLE IF EXISTS employee
  5. DROP TABLE IF EXISTS boss
  6. DROP TABLE IF EXISTS project
  7. DROP TABLE IF EXISTS customer
  8.  
  9. CREATE TABLE customer
  10. (
  11. id INT IDENTITY PRIMARY KEY,
  12. customername VARCHAR(50)
  13. )
  14. CREATE TABLE project
  15. (
  16. id INT IDENTITY PRIMARY KEY,
  17. projectname VARCHAR(50),
  18. projectstatus CHAR(9) CHECK(projectstatus IN ('active','inactive')),
  19. customerid INT NOT NULL REFERENCES customer,
  20. fixedprice INT,
  21. estimatedhours INT,
  22. hourlyrate INT
  23. )
  24. CREATE TABLE boss
  25. (
  26. id INT IDENTITY PRIMARY KEY,
  27. name VARCHAR(50)
  28. )
  29. CREATE TABLE employee
  30. (
  31. id INT IDENTITY PRIMARY KEY,
  32. employeename VARCHAR(50),
  33. bossid INT NOT NULL REFERENCES boss,
  34. timeoff INT
  35. )
  36. CREATE TABLE employeeprojects
  37. (
  38. id INT IDENTITY PRIMARY KEY,
  39. employeeid INT NOT NULL REFERENCES employee,
  40. projectid INT NOT NULL REFERENCES project
  41. )
  42. CREATE TABLE timeregistration
  43. (
  44. id INT IDENTITY PRIMARY KEY,
  45. starttime datetime,
  46. endtime datetime,
  47. employeeid INT NOT NULL REFERENCES employee,
  48. projectid INT NOT NULL REFERENCES project,
  49. note VARCHAR(500)
  50. )
  51.  
  52. GO
  53. INSERT INTO customer VALUES ('microsoft'), ('apple'), ('internal-projects')
  54.  
  55. INSERT 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.  
  60. INSERT INTO boss VALUES ('jackson'), ('peterson')
  61.  
  62. INSERT INTO employee VALUES ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)
  63.  
  64. INSERT INTO employeeprojects VALUES (1, 1), (1,2), (2,3), (3,4)
  65.  
  66. INSERT 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) ---------------------------------------------------
  73. GO
  74. CREATE INDEX Employeetbl_timeoff
  75. ON employee (timeoff ASC)
  76.  
  77. GO
  78. SELECT * FROM employee WITH(INDEX(Employeetbl_timeoff))
  79.  
  80. CREATE INDEX Projecttbl_fixedprice
  81. ON project (fixedprice ASC)
  82.  
  83. GO
  84. SELECT * FROM project WITH(INDEX(Projecttbl_fixedprice))
  85.  
  86. -- Ex3) ---------------------------------------------------
  87. GO
  88. SELECT e.employeename
  89. FROM employee e
  90. JOIN timeregistration t ON e.id = t.employeeid
  91. JOIN project p ON t.projectid = p.id
  92. JOIN customer c ON c.id = p.customerid
  93. WHERE c.customername = 'apple'
  94.  
  95. --Ex4) -----------------------------------------------------
  96. GO
  97. CREATE OR ALTER TRIGGER timeregistrationTrigger
  98. ON timeregistration
  99. instead OF INSERT
  100. AS
  101. BEGIN
  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))
  137. END
  138.  
  139. --test til krav1)
  140. INSERT 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)
  142. INSERT 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)
  144. INSERT 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) ------------------------------------------------------------------
  147. GO
  148. CREATE OR ALTER FUNCTION dbo.antalhverdageiMinutter(@aar INT ,@maaned INT)
  149. -- returnerer antal minutter i hverdage i den pågældende måned  
  150. RETURNS INT
  151. AS
  152. BEGIN
  153. DECLARE @res INT
  154. DECLARE @firstdayofmonth DATE = datefromparts(@aar,@maaned,1)
  155. DECLARE @firstdayofnextmonth DATE = dateadd(mm,1,@firstdayofmonth)
  156. DECLARE @lastdateofmonth DATE = dateadd(dd,-1,@firstdayofnextmonth)
  157. IF DAY(@lastdateofmonth) = 28
  158.   SET @res = 20
  159. ELSE
  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
  181. RETURN 444*@res
  182. END
  183.  
  184. GO
  185. CREATE OR ALTER proc updatetimeoff
  186.     @monthParameter INT,
  187.     @yearParameter INT
  188. AS
  189. BEGIN
  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
  245. END
  246.  
  247. EXEC updatetimeoff 5, 2019
  248.  
  249. SELECT * FROM employee
  250.  
  251. -- Ex8) ---------------------------------
  252. GO
  253. EXEC sp_addlogin 'admin','123'
  254. EXEC sp_addlogin 'employee','123'
  255.  
  256. EXEC sp_grantdbaccess 'admin'
  257. EXEC sp_grantdbaccess 'employee'
  258.  
  259. EXEC sp_addrole 'administrators'
  260. EXEC sp_addrole 'employees'
  261.  
  262. EXEC sp_addrolemember 'administrators','admin'
  263. EXEC sp_addrolemember 'employees','employee'
  264.  
  265. GRANT SELECT, INSERT, UPDATE, DELETE
  266. ON DATABASE::dbeksamen
  267. TO [admin]
  268.  
  269. GRANT SELECT, INSERT, UPDATE, DELETE
  270. ON object::dbeksamen.dbo.timeregistration
  271. TO [employee]
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top