Part 3: Implementation – Database Script (5 marks) Create a T-SQL script for the database design in...

Question

Part 3: Implementation – Database Script (5 marks) Create a T-SQL script for the database design in Part 2. You will create a database with all the necessary tables and constraints: primary key, foreign key, not null, unique and check constraints. The database must be populated with sufficient and meaningful records for evaluation. Part 4: Stored Procedure (10 marks) Implement the following stored procedures. Ensure that each stored procedure is tested with appropriate sample data. Test cases should be saved in a separate test script. (1) Create an order Procedure name usp_createCustomerOrder Description This stored procedure creates a new customer order. The sales tax is 10% of order amount. Input Parameters Customer id – Id of customer Items – A Table-valued Parameter (TVP) of items (item number, quantity, discountPromotionCode). Note that the discountPromotionCode is null for items where a discount does not apply or items are not part of promotion FulfilmentType – Type of order fulfilment (delivery or pickup) OrderType – Type of order (phone, walk-in, app, website) Employee id – Employee id of employee taking the order. This will be null for an online order OrderDateTime – Date and time of order is placed DeliveryAddress – Delivery address if it is a delivery order ExpectedOrderFulfilmentDateTime – Date and time when the order needs to be fulfilled. Output Parameter Order number of the newly created order Functionality Creates a new order with the provided input parameters. After each order, the ingredients used for the order are deducted from the current stock levels of the ingredients. Returns the newly created order number. If there is any error an appropriate error message is raised. SQL script create_usp_createCustomerOrder.sql Test script test_usp_createCustomerOrder.sql Section 5: Business Rule (5 marks) Business Rule: Order Satisfiability Before an order can be taken, it is important to verify that the order can be satisfied with the available ingredients in the store. If the ingredients available are insufficient to fulfill the order an appropriate error message needs to be generated and the order cancelled. Ensure that the above business rule is enforced in the database. You need to generate appropriate error messages if an attempt to violate the constraint is attempted.

Ali Asgar · Accepted Answer

test_usp_createCustomerOrder.sql
SET NOCOUNT ON
DECLARE @varitem as itemtype,
	@res as INT
insert into @varitem
values (3,1,NULL),
	(2,1,NULL)
EXECUTE usp_createCustomerOrder 
1,@varitem,'pickup','walk-in',1,'2021-07-02 09:30:20','123, new lane, sydney 11090','2021-07-02 09:55:20',@OID=@res OUTPUT;
PRINT 'The New Order ID is ' + convert(varchar(10),@res);
GO
DECLARE @varitem as itemtype,
	@res as INT
insert into @varitem
values (1,3,NULL),
	(3,1,NULL)
EXECUTE usp_createCustomerOrder 
2,@varitem,'Delivery','Phone',1,'2021-07-02 09:30:20','127, old lane, sydney 11091','2021-07-02 09:55:20',@OID=@res OUTPUT;
PRINT 'The New Order ID is ' + convert(varchar(10),@res);
GO
create_usp_createCustomerOrder.sql
USE YummyPizza
GO
CREATE OR ALTER PROCEDURE usp_createCustomerOrder (
@cid INT, 
@item itemType READONLY,
@fulfil VARCHAR(30),
@type VARCHAR(30),
@emp INT,
@ordDate DATETIME,
@dlvryAdd VARCHAR(100),
@expDate DATETIME,
@OID INT OUTPUT
)
AS
BEGIN
	DECLARE @OrdrVal DECIMAL(10,2),
	@maxOrdID INT,
	@logID VARCHAR(30)
	;
	
	
	SELECT @OrdrVal = SUM(MI.itemPrice*I.quantity)
		FROM @item I join MenuItems MI
			ON I.itemNum=MI.itemID;
	BEGIN TRY
		INSERT INTO orders (orderDate,orderStatus,fulfilType,orderType,orderValue,payApproveNum,empID,custID)
			VALUES (@ordDate,'Confirmed',@fulfil,@type,@OrdrVal,NULL,@emp,@cid);
		
		SELECT @maxOrdID = MAX(orderID) FROM Orders
			IF @maxOrdID IS NULL
				SET @maxOrdID=1;
		
		INSERT INTO OrderItems (orderID,itemID,quantity)
		SELECT @maxOrdID,itemNum,quantity
			FROM @item
		UPDATE Ing
		SET	Ing.currStockLvl=Ing.currStockLvl-M.quantity
		FROM Ingredients Ing JOIN 
			(MenuIngred M JOIN @item I ON I.itemNum=M.itemID) 
			ON Ing.ingCode=M.ingCode
		IF @fulfil='Delivery'
			BEGIN
				INSERT INTO PickupDelivery (orderID,DeliveryTime,DeliveryAddress)
					VALUES (@maxOrdID,@expDate,@dlvryAdd);
			END
		ELSE
			BEGIN
				INSERT INTO PickupDelivery (orderID,pickupTime)
					VALUES (@maxOrdID,@expDate);

Part 3: Implementation – Database Script (5 marks) Create a T-SQL script for the database design in Part 2. You will create a database with all the necessary tables and constraints: primary key,...

Answer To: Part 3: Implementation – Database Script (5 marks) Create a T-SQL script for the database design in...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment