Contact

 fidel@fidelcaptain.com
  +592 697 0080.
  +592 697 0080.

SiteLock

Follow

Case Studies
Three case studies are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book, each increasing in complexity. Each case study begins with a real world scenario and takes the user through the E-R and R-M diagrams, ending with executable SQL commands to implement the derived designs.

Case Study 2

(28 votes, average 4.54 out of 5)

 

This is the second of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. 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 outlined in the book.

Scenario

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

The owners of a small computer repair shop would like to keep track of the repair jobs for computers they repair, the items used for each repair job, the labor costs for each repair job, the repairmen performing each repair job, and the total cost of each repair job.

When customers bring their computers in to be repaired, they make a deposit on the repair job and are given a date to return and uplift their computer. Repairmen then perform repairs on the customers’ computers based on the repair job, and detail the labor costs and the items used for each repair job.

When customers return they pay the total cost of the repair job less the deposit, collect a receipt for their payment, and uplift the repaired computer using this payment receipt.

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:

RepairJob
(PK) JobNum
DateReceived
DateToReturn
DateReturned
DateStarted
DateEnded
RepairDetails
LaborDetails
LaborCost
TotalCost
PaidInFull
AdditionalComments
Computers
(PK) ComputerId
SerialNum
Make
Model
ComputerDescription
Items
(PK) ItemId
PartNum
ShortName
ItemDescription
Cost
NumInStock
ReorderLow
Repairmen
(PK) RepairmenId
LastName
FirstName
MI
Email
Mobile
HTel
Extension
Customers
(PK) CustomerId
LastName
FirstName
MI
Email
Mobile
HTel
AddressLine1
AddressLine2
City
State
PostCode
Deposits
(PK) DepositNum
DepositDate
Amount
Payments
(PK) PaymentNum
PaymentDate
Amount

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:

Case Study 2 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 the Six-Step Relational Database Design™ book:

Case Study 2 detailed E-R diagram

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

Case Study 2 Relational Model

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 Items (
  ItemId int(11) NOT NULL AUTO_INCREMENT,
  PartNum varchar(50) NOT NULL,
  ShortName varchar(75) NOT NULL,
  Cost decimal(10,2) NOT NULL DEFAULT '0.00',
  NumInStock smallint(6) NOT NULL DEFAULT 1,
  PRIMARY KEY (ItemId),
  INDEX PartNum (PartNum)
);

CREATE TABLE Customers (
  CustomerId int(11) NOT NULL AUTO_INCREMENT,
  LastName varchar(50) NOT NULL,
  FirstName varchar(50) NOT NULL,
  MI varchar(1) DEFAULT 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 (CustomerId),
  INDEX PostCode (PostCode),
  INDEX FullName (LastName,FirstName)
);

CREATE TABLE Repairmen (
  RepairmenId int(11) NOT NULL AUTO_INCREMENT,
  LastName varchar(50) NOT NULL,
  FirstName varchar(50) NOT NULL,
  MI varchar(1) DEFAULT NULL,
  Email varchar(75) NOT NULL,
  Mobile varchar(14) NOT NULL,
  HTel varchar(14) DEFAULT NULL,
  PRIMARY KEY (RepairmenId),
  INDEX FullName (LastName,FirstName)
);

CREATE TABLE Computers (
  ComputerId int(11) NOT NULL AUTO_INCREMENT,
  SerialNum varchar(50) NOT NULL,
  Make varchar(50) DEFAULT NULL,
  Model varchar(50) DEFAULT NULL,
  ComputerDescription varchar(250) DEFAULT NULL,
  CustomerId int(11) NOT NULL,
  PRIMARY KEY (ComputerId),
  FOREIGN KEY CustomerId (CustomerId) 
              REFERENCES Customers (CustomerId) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX SerialNum (SerialNum)
);

CREATE TABLE Items (
  ItemId int(11) NOT NULL AUTO_INCREMENT,
  PartNum varchar(50) NOT NULL,
  ShortName varchar(75) NOT NULL,
  Cost decimal(10,2) NOT NULL DEFAULT '0.00',
  NumInStock smallint(6) NOT NULL DEFAULT 1,
  PRIMARY KEY (ItemId),
  INDEX PartNum (PartNum)
);

CREATE TABLE Customers (
  CustomerId int(11) NOT NULL AUTO_INCREMENT,
  LastName varchar(50) NOT NULL,
  FirstName varchar(50) NOT NULL,
  MI varchar(1) DEFAULT 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 (CustomerId),
  INDEX PostCode (PostCode),
  INDEX FullName (LastName,FirstName)
);

CREATE TABLE Repairmen (
  RepairmenId int(11) NOT NULL AUTO_INCREMENT,
  LastName varchar(50) NOT NULL,
  FirstName varchar(50) NOT NULL,
  MI varchar(1) DEFAULT NULL,
  Email varchar(75) NOT NULL,
  Mobile varchar(14) NOT NULL,
  HTel varchar(14) DEFAULT NULL,
  PRIMARY KEY (RepairmenId),
  INDEX FullName (LastName,FirstName)
);

CREATE TABLE Computers (
  ComputerId int(11) NOT NULL AUTO_INCREMENT,
  SerialNum varchar(50) NOT NULL,
  Make varchar(50) DEFAULT NULL,
  Model varchar(50) DEFAULT NULL,
  ComputerDescription varchar(250) DEFAULT NULL,
  CustomerId int(11) NOT NULL,
  PRIMARY KEY (ComputerId),
  FOREIGN KEY CustomerId (CustomerId) 
              REFERENCES Customers (CustomerId) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX SerialNum (SerialNum)
);

CREATE TABLE RepairJobs (
  JobNum int(11) NOT NULL AUTO_INCREMENT,
  DateReceived date NOT NULL,
  DateReturned date NOT NULL,
  DateEnded date DEFAULT NULL,
  LabourCost decimal(10,2) NOT NULL DEFAULT '0.00',
  TotalCost decimal(10,2) NOT NULL DEFAULT '0.00',
  CustomerId int(11) NOT NULL,
  ComputerId int(11) NOT NULL,
  PRIMARY KEY (JobNum),
  FOREIGN KEY CustomerId (CustomerId) 
              REFERENCES Customers (CustomerId) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY ComputerId (ComputerId) 
              REFERENCES Computers (ComputerId) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX DateReceived (DateReceived),
  INDEX DateReturned (DateReturned)
);

CREATE TABLE Deposits (
  DepositNum int(11) NOT NULL AUTO_INCREMENT,
  DepositDate date NOT NULL,
  Amount decimal(10,2) NOT NULL DEFAULT '0.00',
  JobNum int(11) NOT NULL,
  PRIMARY KEY (DepositNum),
  FOREIGN KEY JobNum (JobNum) 
              REFERENCES RepairJobs (JobNum) 
              ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Payments (
  PaymentNum int(11) NOT NULL AUTO_INCREMENT,
  DepositDate date NOT NULL,
  Amount decimal(10,2) NOT NULL DEFAULT '0.00',
  JobNum int(11) NOT NULL,
  PRIMARY KEY (PaymentNum),
  FOREIGN KEY JobNum (JobNum) 
              REFERENCES RepairJobs (JobNum) 
              ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE RepairJobRepairmen (
  RepJobMenId int(11) NOT NULL AUTO_INCREMENT,
  DateStarted date DEFAULT NULL,
  DateEnded date DEFAULT NULL,
  Comments text,
  JobNum int(11) NOT NULL,
  RepairmenId int(11) NOT NULL,
  PRIMARY KEY (RepJobMenId),
  FOREIGN KEY JobNum (JobNum) 
              REFERENCES RepairJobs (JobNum) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY RepairmenId (RepairmenId) 
              REFERENCES Repairmen (RepairmenId) 
              ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE RepairmenItems (
  RepmenItemId int(11) NOT NULL AUTO_INCREMENT,
  DateOrdered date DEFAULT NULL,
  Quantity smallint(6) NOT NULL DEFAULT 1,
  TotalCost decimal(10,2) NOT NULL DEFAULT '0.00',
  ItemId int(11) NOT NULL,
  RepairmenId int(11) NOT NULL,
  PRIMARY KEY (RepmenItemId),
  FOREIGN KEY ItemId (ItemId) 
              REFERENCES Items (ItemId) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY RepairmenId (RepairmenId) 
              REFERENCES Repairmen (RepairmenId) 
              ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE RepairJobItems (
  RepJobItemId int(11) NOT NULL AUTO_INCREMENT,
  DateUsed date DEFAULT NULL,
  Quantity smallint(6) NOT NULL DEFAULT 1,
  TotalCost decimal(10,2) NOT NULL DEFAULT '0.00',
  JobNum int(11) NOT NULL,
  ItemId int(11) NOT NULL,
  PRIMARY KEY (RepJobItemId),
  FOREIGN KEY JobNum (JobNum) 
              REFERENCES RepairJobs (JobNum) 
              ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY ItemId (ItemId) 
              REFERENCES Items (ItemId) 
              ON UPDATE CASCADE ON DELETE RESTRICT
);

Other Case Studies
  Case Study 1

  Case Study 3