Database Design and Implementation Sample Assignment

Store Procedures

Implement the following stored procedures. Ensure that each stored procedure is tested with appropriate sample data and appropriate error messages raised when required. Test cases should be saved in a separate test script.

(1) Create a customer order

Procedure Name

usp_addCustomerOrder

Parameters

customer – customer who places the order
products – a list of products and quantity of each product (as a table valued parameter)
discount – discount amount (default is zero)

Functionality

Add an order complete with all the products ordered, amount due, etc.

SQL script

CREATE PROCEDURE &qout;createDB&qout;.&qout;dbo&qout;.&qout; create_usp_addCustomerOrder&qout;(
cn IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.companyName%TYPE,
cp IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.contactPerson%TYPE,
e IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.email%TYPE,
g IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.gender%TYPE,
ca IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.custaddress%TYPE,
p IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.phone%TYPE,
ec IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;.emaxCredit%TYPE
co IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerOrder&qout;.CustomerOrderID%TYPE
) AS
counted NUMBER;
BEGIN
SELECT count(*) into counted FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;;
IF(counted=0) then
INSERT INTO &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout; VALUES (companyName(cn), contactPerson(cp),e,g,ca,p,ec,co, 'N');
else
insert into &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout; VALUES (companyName(cn), contactPerson(cp),e,g,ca,p,ec,co,
(select max(CustomerOrderID) +1 FROM  &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout;), &qout;N');
END IF;
END;

Test script

EXEC &qout;createDB&qout;.&qout;dbo&qout;.&qout; create_usp_addCustomerOrder&qout;;

Execution context

This stored procedure can be executed only by sales staff. The sales employee who executes this stored procedure is the employee making the order. Ensure that you create appropriate roles; users etc. and grant appropriate permissions.

(2) Find outstanding customers and amounts

Procedure name

usp_printOutstandingCustomers

Parameters

None

Functionality

Print the customer id, name and total outstanding amount for all customers who owe funds to OfficeWizard

SQL script

CREATE PROCEDURE &qout;createDB&qout;.&qout;dbo&qout;.&qout;create_usp_printOutstandingCustomers&qout; (
do IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.dateOfOrder%TYPE,
d IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.discount%TYPE,
of IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.overdueFee%TYPE,
cf IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.cancellationFee%TYPE,
bd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.billingDate%TYPE,
dd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.dueDate%TYPE,
cs IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.custstatus%TYPE
cd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;.custdescription%TYPE
) AS
counted NUMBER;
BEGIN
SELECT count(*) into counted FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;;
IF(counted=0) then
INSERT INTO &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout; VALUES ( do,d,of.cf,bd,dd,cs,cd,'N');
else
insert into&qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout; VALUES (do,d,of.cf,bd,dd,cs,cd,
(select max(CustomerRecordID) +1 FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;), &qout;N');
END IF;
END;

Test script

EXEC &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout;;

Execution context

Any employee can execute this stored procedure

(3) Print products to order

Procedure name

usp_ProductsToOrder

Description

Print the products and their respective

Parameters

None

Functionality

Print all products (product IDs) and suppliers who supply these products for products whose re-order level >= to available quantity

SQL script

CREATE PROCEDURE &qout;createDB&qout;.&qout;dbo&qout;.&qout;create_usp_ProductsToOrder&qout; (
n IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.name%TYPE,
m IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.manufacturer%TYPE,
c IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.category%TYPE,
pd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.prodescription%TYPE,
qd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.quantityDescription%TYPE,
up IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.unitPrice%TYPE,
ps IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.prostatus%TYPE,
qa IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.quantityAvailable%TYPE,
el IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.reorderLevel%TYPE,
md IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;.maxDiscount%TYPE
) AS
BEGIN
SELECT * FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;;
END;

Test script

EXEC &qout;createDB&qout;.&qout;dbo&qout;.&qout;Product&qout;;

Execution context

Any employee can execute this stored procedure

(4) Add quotation

Procedure name

usp_addQuotation

Parameters

Supplier id
Date (default is current date)
Validity period for quotation (in months)
Products, quantity and their respective prices (as a table valued parameter)
Description (if any)
Employee managing the quotation
Quotation ID of the newly created quotation (output parameter)

Functionality

Add the complete quotation to the database

SQL script

CREATE PROCEDURE &qout;createDB&qout;.&qout;dbo&qout;.&qout;create_usp_ProductsToOrder&qout; (
qd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;.qtDate%TYPE,
vp IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;.validityPeriod%TYPE,
qd IN &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;.QuotationDescription%TYPE
) AS
BEGIN
SELECT * FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;;
IF(counted=0) then
INSERT INTO &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout; VALUES ( qd, vp, qd, 'N');
else
insert into &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout; VALUES (qd, vp, qd,
(select max(qtNumber) +1 FROM &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;), &qout;N');
END IF;
END;

Test script

EXEC &qout;createDB&qout;.&qout;dbo&qout;.&qout;Quotation&qout;;

Execution context

This stored procedure can be executed only by administration staff. The admin employee who executes this stored procedure is the employee requesting the quotation. Ensure that you create appropriate roles; users etc. and grant appropriate permissions.

Also, the quotation id of the newly created quotation needs to be returned as an output parameter.

Business Rules

Business Rule 1:

SQL script :

SELECT cr.companyName from &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout; as cs , &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout; as cr,
 &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerOrder&qout; as co
where  cs.CustomerRecordID = cr.CustomerRecordID
and
cr.CustomerOrderID = co.CustomerOrderID
and
cs.overdueFee >=5000;

Business Rule 2:

SQL script :

SELECT cr.companyName from &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerSale&qout; as cs , &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerRecord&qout; as cr,
 &qout;createDB&qout;.&qout;dbo&qout;.&qout;CustomerOrder&qout; as co,  &qout;createDB&qout;.&qout;dbo&qout;.&qout;Employee&qout; as e
where  cs.CustomerRecordID = cr.CustomerRecordID
and
cr.CustomerOrderID = co.CustomerOrderID
and
e.EmployeeID = co.EmployeeID;