From Sharp Kangaroo, 5 Months ago, written in Text.
Embed
  1. CREATE TABLE [dbo].[EMPLOYEE]
  2. (
  3.         [employeeID] NCHAR(10) NOT NULL PRIMARY KEY,
  4.     [firstName] NCHAR(30) NOT NULL,
  5.     [lastName] NCHAR(30) NOT NULL,
  6.     [gender] CHAR(1) NOT NULL,
  7.     [dob] DATE NOT NULL,
  8.     [dateJoined] DATE NOT NULL,
  9.     [dateLeft] DATE NULL,
  10.     [address] NCHAR(50) NOT NULL,
  11.     [contactNumber] NCHAR(11) NOT NULL,
  12.     [email] NCHAR(30) NOT NULL,
  13.     [password] NCHAR(20) NOT NULL,
  14.     [icNumber] NCHAR(14) NOT NULL,
  15.     [position] NCHAR(20) NOT NULL,
  16.     [baseSalary] DECIMAL(10,2) NOT NULL
  17. );
  18.  
  19. CREATE TABLE [dbo].[LEAVE]
  20. (
  21.         [leaveID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  22.     [leaveType] NCHAR(20) NOT NULL,
  23.     [description] NCHAR(100) NOT NULL,
  24.     [leaveDateFrom] DATE NOT NULL,
  25. [leaveDateTo] DATE NOT NULL,
  26.     [leaveStatus] NCHAR(15) NULL,
  27.     [employeeID] NCHAR(10) NOT NULL,
  28.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  29. );
  30.  
  31. CREATE TABLE [dbo].[ATTENDANCE]
  32. (
  33.         [attendanceNo] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  34.     [checkinDate] DATE NOT NULL,
  35.     [checkinTime] TIME NOT NULL,
  36. [checkoutTime] TIME,
  37.     [workingHours] INT ,
  38.     [overtimeHours] INT ,
  39.     [employeeID] NCHAR(10) NOT NULL,
  40.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  41. );
  42.  
  43. CREATE TABLE [dbo].[PERFORMANCE]
  44. (
  45.         [performanceNo] NCHAR(10) NOT NULL PRIMARY KEY,
  46.     [dateOfReview] DATE NOT NULL,
  47.     [reviewerID] NCHAR(10) NOT NULL,
  48.     [overallStatus] NCHAR(20) NULL,
  49.     [reviewerComments] NCHAR(100) NOT NULL,
  50.     [employeeID] NCHAR(10) NOT NULL,
  51.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  52. );
  53.  
  54. CREATE TABLE [dbo].[DEPARTMENT]
  55. (
  56.         [departmentID] NCHAR(10) NOT NULL PRIMARY KEY,
  57.     [departmentName] NCHAR(30) NOT NULL,
  58.     [employeeID] NCHAR(10) NOT NULL,
  59.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  60. );
  61.  
  62.  
  63. CREATE TABLE [dbo].[PROJECT] (
  64.     [projectID]        NCHAR (10)  NOT NULL,
  65.     [projectType]      NCHAR (20)  NOT NULL,
  66.     [description]      NCHAR (100) NOT NULL,
  67.     [employeeRequired] INT         NOT NULL,
  68.     [employeeNum]      INT         NOT NULL,
  69.     [startDate]        DATE        NOT NULL,
  70.     [endDate]          DATE        NOT NULL,
  71.     PRIMARY KEY CLUSTERED ([projectID] ASC)
  72. );
  73.  
  74. CREATE TABLE [dbo].[PROJECTASSIGNMENT] (
  75.     [paID] NCHAR (10) NOT NULL,
  76.     [employeeID]  NCHAR (10) NOT NULL,
  77.     [projectID] NCHAR(10) NOT NULL,
  78.     PRIMARY KEY CLUSTERED ([paID] ASC),
  79.     FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID]),
  80.         FOREIGN KEY ([projectID]) REFERENCES [dbo].[PROJECT] ([projectID])
  81. );
  82.  
  83. CREATE TABLE [dbo].[SKILL]
  84. (
  85.         [skillID] NCHAR(10) NOT NULL PRIMARY KEY,
  86.     [skillDescription] NCHAR(100) NOT NULL
  87. );
  88.  
  89. CREATE TABLE [dbo].[SKILLACQUIRED] (
  90.     [saID]    NCHAR (10) NOT NULL,
  91.     [skillID] NCHAR (10) NOT NULL,
  92.     [employeeID] NCHAR(10) NOT NULL,
  93.     PRIMARY KEY CLUSTERED ([saID] ASC),
  94.     FOREIGN KEY ([skillID]) REFERENCES [dbo].[SKILL] ([skillID]),
  95.     FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  96. );
  97.  
  98. CREATE TABLE [dbo].[PAYROLL]
  99. (
  100.         [payrollID] NCHAR(10) NOT NULL PRIMARY KEY,
  101.     [employeeID] NCHAR(10) NOT NULL,
  102.         [payDate] NCHAR(15) NOT NULL,
  103.         [payTime] NCHAR(30) NOT NULL,
  104.         [allowanceAmt] DECIMAL(10,2) NOT NULL,
  105.         [deductionAmt] DECIMAL(10,2) NOT NULL,
  106.     [netPay] DECIMAL(10,2) NOT NULL
  107.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  108. );
  109.  
  110. CREATE TABLE [dbo].[DEDUCTION]
  111. (
  112.         [deductionID] NCHAR(10) NOT NULL PRIMARY KEY,
  113.     [employeeID] NCHAR(10) NOT NULL,
  114.         [deductionDate] NCHAR(15) NOT NULL,
  115.         [deductionType] NCHAR(50) NOT NULL,
  116.         [deductionDesc] NCHAR(50) NOT NULL,
  117.     [deductionAmt] DECIMAL(10,2) NOT NULL,
  118.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  119. );
  120.  
  121. CREATE TABLE [dbo].[ALLOWANCE]
  122. (
  123.         [allowanceID] NCHAR(10) NOT NULL PRIMARY KEY,
  124.     [employeeID] NCHAR(10) NOT NULL,
  125.         [allowanceDate] NCHAR(15) NOT NULL,
  126.         [allowanceType] NCHAR(50) NOT NULL,
  127.         [allowanceDesc] NCHAR(50) NOT NULL,
  128.     [allowanceAmt] DECIMAL(10,2) NOT NULL,
  129.         FOREIGN KEY ([employeeID]) REFERENCES [dbo].[EMPLOYEE] ([employeeID])
  130. );
  131.  
  132. CREATE TABLE [dbo].[SKILLREQUIRED]
  133. (
  134.         [srID] NCHAR(10) NOT NULL PRIMARY KEY,
  135.     [skillID] NCHAR(10) NOT NULL,
  136.     [projectID] NCHAR(10) NOT NULL,
  137.         FOREIGN KEY ([skillID]) REFERENCES [dbo].[SKILL] ([skillID]),
  138.         FOREIGN KEY ([projectID]) REFERENCES [dbo].[PROJECT] ([projectID])
  139. );
  140.  
  141.