• Presentations
  • Presentations
  • IMG 0341
  • Presentations
  • IMG 0409

Case Study 1

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. Get a copy of Six-Step Relational Database Design™ to get the details of all six steps.

 

Scenario

Below is the scenario for Case Study 1 as described in Six-Step Relational Database Design™:

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 Six-Step Relational Database Design™:

Employees
(PK) EmployeeId
SSNumber
LastName
FirstName
MiddleName
Gender
DOB
Email
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

 

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 Six-Step Relational Database Design™:

Case Study 1 simplified E-R diagram

 

Below is the Detailed Entity-Relationship diagram that is output from Step 5 of the six step database design process as described in Six-Step Relational Database Design™:

Case Study 1 detailed E-R diagram

 

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 Six-Step Relational Database Design™:

Case Study 1 Relational Model

 

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 Six-Step Relational Database Design™.

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