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

SQL Commands for Case Study 1

(2 votes, average 5.00 out of 5)

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™. Some of the 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.

 

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