SHARE
TWEET

Untitled

a guest Feb 19th, 2019 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE WP
  2. GO
  3.  
  4. CREATE  TABLE DEPARTMENT(
  5.     DepartmentName      Char(35)        NOT NULL,
  6.     BudgetCode          Char(30)        NOT NULL,
  7.     OfficeNumber        Char(15)        NOT NULL,
  8.     DepartmentPhone     Char(12)        NOT NULL,
  9.     CONSTRAINT          DEPARTMENT_PK   PRIMARY KEY(DepartmentName)
  10.     );
  11.  
  12. CREATE  TABLE EMPLOYEE(
  13.     EmployeeNumber      Int             NOT NULL IDENTITY (1, 1),
  14.     FirstName           Char(25)        NOT NULL,
  15.     LastName            Char(25)        NOT NULL,
  16.     Department          Char(35)        NOT NULL DEFAULT 'Human Resources',
  17.     Position            Char(35)        NULL,
  18.     Supervisor          Int             NULL,
  19.     OfficePhone         Char(12)        NULL,
  20.     EmailAddress        VarChar(100)    NOT NULL UNIQUE,
  21.     CONSTRAINT          EMPLOYEE_PK     PRIMARY KEY(EmployeeNumber),
  22.     CONSTRAINT          EMP_DEPART_FK   FOREIGN KEY(Department)
  23.                             REFERENCES DEPARTMENT(DepartmentName)
  24.                             ON UPDATE CASCADE,
  25.     CONSTRAINT          EMP_SUPER_FK    FOREIGN KEY (Supervisor)
  26.                             REFERENCES EMPLOYEE (EmployeeNumber)                       
  27.     );
  28.  
  29. CREATE  TABLE PROJECT (
  30.     ProjectID           Int             NOT NULL IDENTITY (1000, 100),
  31.     ProjectName         Char(50)        NOT NULL,
  32.     Department          Char(35)        NOT NULL,
  33.     MaxHours            Numeric(8,2)    NOT NULL DEFAULT 100,
  34.     StartDate           Date            NULL,
  35.     EndDate             Date            NULL,
  36.     CONSTRAINT          PROJECT_PK      PRIMARY KEY (ProjectID),
  37.     CONSTRAINT          PROJ_DEPART_FK  FOREIGN KEY(Department)
  38.                             REFERENCES DEPARTMENT(DepartmentName)
  39.                                 ON UPDATE CASCADE
  40.         );
  41.  
  42. CREATE  TABLE ASSIGNMENT (
  43.     ProjectID           Int             NOT NULL,
  44.     EmployeeNumber      Int             NOT NULL,
  45.     HoursWorked         Numeric(6,2)    NULL,
  46.     CONSTRAINT          ASSIGNMENT_PK   PRIMARY KEY (ProjectID, EmployeeNumber),
  47.     CONSTRAINT          ASSIGN_PROJ_FK  FOREIGN KEY (ProjectID)
  48.                             REFERENCES PROJECT (ProjectID)
  49.                                 ON UPDATE NO ACTION
  50.                                 ON DELETE CASCADE,
  51.     CONSTRAINT          ASSIGN_EMP_FK   FOREIGN KEY (EmployeeNumber)
  52.                             REFERENCES EMPLOYEE (EmployeeNumber)
  53.                                 ON UPDATE NO ACTION
  54.                                 ON DELETE NO ACTION
  55.     );
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