Yurttas/PL/DBL/mssql/F/01/Company-A/C-all.sql

  1/*
  2REM
  3REM c-all.sql
  4REM
  5REM Create Company-A database tables.
  6REM
  7*/
  8
  9\. Employee-cre.sql
 10
 11/*
 12REM
 13REM
 14REM Employee-cre.sql
 15REM
 16REM
 17*/
 18
 19CREATE TABLE Employee(fname VARCHAR(12) NOT NULL,
 20                      minit CHAR,
 21                      lname VARCHAR(16) NOT NULL, 
 22                      ssn CHAR(9) NOT NULL,
 23                      bdate CHAR(9),
 24                      address VARCHAR(24),
 25                      sex CHAR,
 26                      salary FLOAT,
 27                      superssn CHAR(9),
 28                      dno INTEGER NOT NULL,
 29          PRIMARY KEY(ssn));
 30\. Dependent-cre.sql
 31
 32/*
 33REM
 34REM
 35REM Dependent-cre.sql
 36REM
 37REM
 38*/
 39
 40CREATE TABLE Dependent(essn CHAR(9) NOT NULL,
 41                       dependentname VARCHAR(16) NOT NULL,
 42                       sex CHAR,
 43                       bdate CHAR(9),
 44                       relationship VARCHAR(8),
 45           PRIMARY KEY(essn, dependentname),
 46           FOREIGN KEY(essn) REFERENCES Employee(ssn));
 47
 48/*
 49REM
 50REM
 51REM Department-cre.sql
 52REM
 53REM
 54*/
 55
 56CREATE TABLE Department(dname VARCHAR(16) NOT NULL,
 57                        dnumber INTEGER NOT NULL,
 58                        mgrssn CHAR(9) NOT NULL,
 59                        mgrstartdate CHAR(9),
 60            PRIMARY KEY(dnumber),
 61            FOREIGN KEY(mgrssn) REFERENCES Employee(ssn));
 62
 63/*
 64REM
 65REM
 66REM DeptLocation-cre.sql
 67REM
 68REM
 69*/
 70
 71CREATE TABLE DeptLocation(dnumber INTEGER NOT NULL,
 72                          dlocation VARCHAR(24) NOT NULL,
 73              PRIMARY KEY(dnumber, dlocation),
 74              FOREIGN KEY(dnumber) REFERENCES Department(dnumber));
 75
 76/*
 77REM
 78REM
 79REM Project-cre.sql
 80REM
 81REM
 82*/
 83
 84CREATE TABLE Project(pname VARCHAR(16) NOT NULL,
 85                     pnumber INTEGER NOT NULL,
 86                     plocation VARCHAR(24),
 87                     dnum INTEGER NOT NULL,
 88         PRIMARY KEY(pnumber),
 89         FOREIGN KEY(dnum) REFERENCES Department(dnumber));
 90
 91
 92/*
 93REM
 94REM
 95REM WorksOn-cre.sql
 96REM
 97REM
 98*/
 99
100CREATE TABLE WorksOn(essn CHAR(9) NOT NULL,
101                     pno INTEGER NOT NULL,
102                     hours FLOAT NOT NULL,
103         PRIMARY KEY(essn, pno),
104         FOREIGN KEY(essn) REFERENCES Employee(ssn),
105         FOREIGN KEY(pno) REFERENCES Project(pnumber));