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

SQL Commands for Case Study 2

(2 votes, average 5.00 out of 5)

This is the second 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 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.

 

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