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));