This is the first of three case studies that are used to guide the reader through the six steps outlined in Six-Step Relational Database Design™. The most important outputs of the six step database design process are depicted here, but the details of each of the steps and intermediary outputs are detailed in the book.
| What's on this page |
|---|
| Scenario List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario
Below is the scenario for Case Study 1 as described in the Six-Step Relational Database Design™ book:
A small accounting firm wants a simple HR application that will help it to keep track of its employees, their positions, allowances, salary scales, and which company vehicles their employees drive.
The application must keep track of all the positions at the firm, the employees filling these positions, the allowances for these positions, the salary scales for these positions, and the company vehicles assigned to these positions.
List of entities and their corresponding attributes
Below is the list of entities and their corresponding attributes that is output from Step 1 of the six step database design process as described in the Six-Step Relational Database Design™ book:
| Employees |
|---|
| (PK) EmployeeId |
| SSNumber |
| LastName |
| FirstName |
| MiddleName |
| Gender |
| DOB |
| Mobile |
| HTel |
| AddressLine1 |
| AddressLine2 |
| City |
| State |
| PostCode |
| Positions |
|---|
| (PK) PositionId |
| PositionName |
| PositionDescription |
| Details |
| Allowances |
|---|
| (PK) AllowanceId |
| AllowanceName |
| AllowanceDescription |
| Amount |
| SalaryScales |
|---|
| (PK) SalaryScaleCode |
| SalaryScaleName |
| SalaryScaleDescription |
| MinimumSalary |
| MaximumSalary |
| Vehicles |
|---|
| (PK) VehicleId |
| VIN |
| RegistrationNo |
| Year |
| Make |
| Model |
| Color |
Top
Entity-Relationship diagrams
Below is the Simplified Entity-Relationship diagram that is output from Step 3 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Below is the Detailed Entity-Relationship diagram that is output from Step 5 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
Relational-Model diagram
Below is the Relational-Model diagram that is output from Step 6 of the six step database design process as described in theSix-Step Relational Database Design™ book:
Top
SQL commands
The SQL commands below can be used to implement the design depicted above in a MySQL database. Some modifications will be necessary to execute these commands on MS SQL Server, Oracle, or any other RDBMS. Detailed implementation considerations can be found in the Six-Step Relational Database Design™ book.
CREATE TABLE Employees (
EmployeeId int(11) NOT NULL AUTO_INCREMENT,
SSNumber varchar(11) NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) DEFAULT NULL,
Gender varchar(6) NOT NULL DEFAULT 'MALE',
DOB date NOT NULL,
Email varchar(75) NOT NULL,
Mobile varchar(14) DEFAULT NULL,
HTel varchar(14) NOT NULL,
AddressLine1 varchar(75) NOT NULL,
AddressLine2 varchar(75) DEFAULT NULL,
City varchar(50) NOT NULL,
State varchar(50) NOT NULL,
PostCode varchar(10) NOT NULL,
PRIMARY KEY (EmployeeId),
INDEX SSNumber (SSNumber),
INDEX DOB (DOB),
INDEX PostCode (PostCode),
INDEX FullName (LastName,FirstName)
);
CREATE TABLE Allowances (
AllowanceId int(11) NOT NULL AUTO_INCREMENT,
AllowanceName int(11) NOT NULL,
AllowanceDescription varchar(250) DEFAULT NULL,
Amount decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (AllowanceId),
INDEX AllowanceName (AllowanceName)
);
CREATE TABLE SalaryScales (
SalaryScaleCode tinyint(4) NOT NULL,
SalaryScaleName varchar(50) NOT NULL,
SalaryScaleDescription varchar(250) DEFAULT NULL,
MinimumSalary decimal(10,2) NOT NULL DEFAULT '0.00',
MaximumSalary decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (SalaryScaleCode),
INDEX SalaryScaleName (SalaryScaleName)
);
CREATE TABLE Positions (
PositionId int(11) NOT NULL AUTO_INCREMENT,
PositionName varchar(75) NOT NULL,
PositionDesctiption varchar(250) DEFAULT NULL,
Details text,
SalaryScaleCode tinyint(4) NOT NULL,
PRIMARY KEY (PositionId),
FOREIGN KEY SalaryScaleCode (SalaryScaleCode)
REFERENCES SalaryScales (SalaryScaleCode)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX PositionName (PositionName)
);
CREATE TABLE Vehicles (
VehicleId int(11) NOT NULL AUTO_INCREMENT,
VIN varchar(17) NOT NULL,
RegistrationNo varchar(10) NOT NULL,
Year year(4) DEFAULT NULL,
Make varchar(25) DEFAULT NULL,
Model varchar(25) DEFAULT NULL,
Color varchar(25) DEFAULT NULL,
PositionId int(11) NOT NULL,
PRIMARY KEY (VehicleId),
FOREIGN KEY PositionId (PositionId)
REFERENCES Positions (PositionId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX VIN (VIN),
INDEX RegistrationNo (RegistrationNo)
);
CREATE TABLE PositionAllowances (
PosAllowId int(11) NOT NULL AUTO_INCREMENT,
AllowanceId int(11) NOT NULL,
PositionId int(11) NOT NULL,
PRIMARY KEY (PosAllowId),
FOREIGN KEY AllowanceId (AllowanceId)
REFERENCES Allowances (AllowanceId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY PositionId (PositionId)
REFERENCES Positions (PositionId)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE EmployeePositions (
EmpPosId int(11) NOT NULL AUTO_INCREMENT,
EmployeeId int(11) NOT NULL,
PositionId int(11) NOT NULL,
StartDate date NOT NULL,
EndDate date DEFAULT NULL,
Comments text,
PRIMARY KEY (EmpPosId),
FOREIGN KEY EmployeeId (EmployeeId)
REFERENCES Employees (EmployeeId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY PositionId (PositionId)
REFERENCES Positions (PositionId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX StartDate (StartDate)
);
| Other Case Studies |
|---|
| Case Study 2 Case Study 3 |
Follow