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;
