Evaluation of Four Languages for Specifying Conceptual Database Designs - High-Performance Web Databases: Design, Development, and Deployment - page 171

Exhibit 12-3. Conceptual database design using SQL syntax.

CREATE TABLE Department
(DeptName

INTEGER,

Budget INTEGER,

PRIMARY KEY (DeptName))

CREATE TABLE Employee
(EmpId INTEGER,

EmpName CHAR(20),

Address CHAR(40),

SocialSecurityNumber INTEGER,

DeptName INTEGER,

FOREIGN KEY (DeptName) REFERENCES Depart-
ment,
PRIMARY KEY (EmpId),
UNIQUE (SocialSecurityNumber))

CREATE TABLE Project
(ProjectName

CHAR(20),

StartDate DATE,

EndDate DATE,

PRIMARY KEY (ProjectName))

CREATE TABLE Assignment
(EmpId INTEGER,

ProjectName CHAR(20),

Role CHAR(20),

PRIMARY KEY (EmpId, ProjectName),
FOREIGN KEY (EmpId) REFERENCES Employee,
FOREIGN KEY (ProjectName) REFERENCES Project)

CREATE TABLE Dependent
(EmpId CHAR (20),

DependentName CHAR (20),

DependentBirthDate DATE,

PRIMARY KEY (DependentName, EmpId),
FOREIGN KEY (EmpId) REFERENCES Employee)

CREATE TABLE Manager
(EmpId INTEGER,

Bonus INTEGER,

PRIMARY KEY (EmpId),
FOREIGN KEY (EmpId) REFERENCES Employee)