Database Systems: MakeUpTest Assignment - Queries, Views, and Triggers

This document presents a comprehensive solution to a database assignment. The assignment begins with the creation of a database named 'MakeUpTest' and proceeds with detailed instructions on creating several tables (CUSTOMER, VENDOR, PRODUCT, INVOICE, LINE, and ACCT_TRANSACTION) with specified primary and foreign keys. The solution includes SQL queries for inserting data into these tables, simulating customer purchases and transactions. It then addresses adding columns to an existing table and creating a view to display items sold within the last 18 months. Furthermore, the assignment covers the implementation of a trigger to handle item returns, updating the LINE, PRODUCT, and CUSTOMER tables accordingly. The solution also includes stored procedures to display returned items and vendor details based on product IDs, along with a function to calculate the total price per invoice. Finally, the solution concludes with a query to delete returned items from the LINE table. This assignment showcases a strong understanding of database design, SQL query writing, and the implementation of advanced database features like triggers, views, procedures, and functions.

Documentation
1. Create database named MakeUpTest [2]
Query:
CREATE DATABASE MakeUpTest;
Output:
2. Create table as shown in the diagram. Please make sure you specify the primary and
foreign key in the tables. [6+2]
Query:
CREATE TABLE CUSTOMER (
CUST_NUMBER INT PRIMARY KEY,
CUST_LNAME VARCHAR(30),
CUST_FNAME VARCHAR(30),
CUST_INITIAL VARCHAR(5),
CUST_AREACODE VARCHAR(3),
CUST_PHONE VARCHAR(12),
CUST_BALANCE FLOAT(8)
);
CREATE TABLE VENDOR (
VEND_NUMBER INT PRIMARY KEY,
VEND_NAME VARCHAR(15),
VEND_CONTACT VARCHAR(50),
VEND_AREACODE VARCHAR(4),
VEND_PHONE VARCHAR(12),

VEND_STATE VARCHAR(3),
VEND_ORDER VARCHAR(1)
);
CREATE TABLE PRODUCT (
PROD_CODE VARCHAR(10) PRIMARY KEY,
PROD_DESCRIPT VARCHAR(255),
PROD_INDATE DATETIME,
PROD_QOH INT,
PROD_MIN INT,
PROD_PRICE FLOAT(8),
PROD_DISCOUNT FLOAT(8),
VEND_NUMBER INT,
FOREIGN KEY (VEND_NUMBER) REFERENCES
VENDOR(VEND_NUMBER)
);
CREATE TABLE INVOICE (
INV_NUMBER INT PRIMARY KEY,
CUST_NUMBER INT,
INV_DATE DATETIME,
INV_SUBTOTAL FLOAT(8),
INV_TAX FLOAT(8),
INV_TOTAL FLOAT(8),
INV_PAY_TYPE VARCHAR(5),
INV_PAY_AMOUNT FLOAT(8),
INV_BALANCE FLOAT(8),
FOREIGN KEY(CUST_NUMBER) REFERENCES
CUSTOMER(CUST_NUMBER)
);
CREATE TABLE LINE (
INV_NUMBER INT,
LINE_NUMBER INT,
PROD_CODE VARCHAR(10),
LINE_UNITS FLOAT(8),
LINE_PRICE FLOAT(8),
LINE_AMOUNT FLOAT(8),

PRIMARY KEY(INV_NUMBER, LINE_NUMBER),
FOREIGN KEY(INV_NUMBER) REFERENCES INVOICE(INV_NUMBER),
FOREIGN KEY(PROD_CODE) REFERENCES PRODUCT(PROD_CODE)
);
CREATE TABLE ACCT_TRANSACTION(
ACCT_TRANS_NUM INT PRIMARY KEY,
ACCT_TRANS_DATE DATETIME,
CUST_NUMBER INT,
ACCT_TRANS_TYPE VARCHAR(10),
ACCT_TRANS_AMOUNT FLOAT(8),
FOREIGN KEY(CUST_NUMBER) REFERENCES
CUSTOMER(CUST_NUMBER)
);
Output:
Insert Data:
Query:
INSERT INTO VENDOR VALUES(21225, 'iXtreme', 'Smithson', 615, '223-3234',
'TN', 'Y');
INSERT INTO VENDOR VALUES(21344, 'Nike', 'Flushing', 904, '215-8995', 'FL',
'N');
INSERT INTO PRODUCT VALUES('4243213', 'Snow Suit', '2015-11-03', 8, 5, 39, 0,
21225);

INSERT INTO PRODUCT VALUES('0637032', 'Menu Training Shoes', '2015-12-13',
32, 15, 85, 0.05 , 21344);
INSERT INTO PRODUCT VALUES('1200247', 'Womens Basketbal', '2015-02-24',
237, 100, 69.99, 0 , 21344);
INSERT INTO CUSTOMER VALUES (10014,'Orlando', 'Myron', NULL, 615, '222-
1672', 0);
INSERT INTO CUSTOMER VALUES (10015,'OBrian', 'Amy', 'B', 713, '442-3381',
0);
INSERT INTO ACCT_TRANSACTION VALUES ( 4243213,'2016-11-17',
10014 ,'charge', 39);
INSERT INTO ACCT_TRANSACTION VALUES ( 1200247,'2016-11-17',
10014 ,'charge', 69.99);
INSERT INTO ACCT_TRANSACTION VALUES ( 4243214,'2016-01-18',
10015 ,'charge', 39);
INSERT INTO ACCT_TRANSACTION VALUES ( 1200248,'2016-01-18',
10015 ,'charge', 69.99);
INSERT INTO ACCT_TRANSACTION VALUES ( 637032, '2016-01-18',
10015 ,'charge', 85);
INSERT INTO INVOICE VALUES (1001, 10014, '2016-11-17', 108.99, 8.05,
117.04, 'cc',117.04, 0);
INSERT INTO INVOICE VALUES (1003, 10015, '2016-01-18', 193.99, 14.32,
208.31, 'cc',208.31, 0);
INSERT INTO LINE VALUES (1001, 1, '4243213', 1, 39, 39);
INSERT INTO LINE VALUES (1001, 2, '1200247', 1, 69.99, 69.99);
INSERT INTO LINE VALUES (1003, 1, '4243213', 1, 39, 39);
INSERT INTO LINE VALUES (1003, 2, '0637032', 1, 85, 85);
INSERT INTO LINE VALUES (1003, 3, '1200247', 1, 69.99, 69.99);
Output:

3. Assume customer 1 purchased 2 items from the store and customer 2 purchased 3
items from the store. Please use insert command to generate the output as shown in the
attached document. [3+2]
Query:
SELECT * FROM LINE;
Output:

4. Add a columns titled OrderStatus and Cust_Number in the LINE table [5]
Query:
ALTER TABLE LINE ADD OrderStatus VARCHAR(10),
Cust_Number INT CONSTRAINT LineCustNumberFK FOREIGN
KEY(Cust_Number) REFERENCES Customer(Cust_Number);
SELECT * FROM LINE;
Output:
5. Write a view that will display the items sold within the last 18 months. You can find
the items sold in LINE table and product information in PRODUCTS table [5]
Query:
UPDATE INVOICE SET INV_DATE='2017-11-17' ;
CREATE VIEW Last18MonthSoldItems AS
SELECT DISTINCT PRODUCT.PROD_CODE, PROD_DESCRIPT, PROD_PRICE
FROM
INVOICE INNER JOIN LINE ON LINE.INV_NUMBER
=INVOICE.INV_NUMBER
INNER JOIN PRODUCT ON PRODUCT.PROD_CODE=PRODUCT.PROD_CODE
WHERE INV_DATE >= DATEADD(MONTH, -18, CAST(GETDATE() AS
DATE));
SELECT * FROM Last18MonthSoldItems;

Output:
6.
Assume customer 10014 returns all his/her purchases to the store.
a. Create a trigger for delete on invoice table that will update OrderStatus column of line table
to ‘Returned’ and load the customer number in CUST_NUMBER, if the purchase date is
within the last 18 months. [5]
b. Update the quantity (PROD_QOH) in the product table to add the items that were returned,
and [5]
c. Update CUST_BALANCE in Customers table by adding the total amount of the returned
items. [5]
d. If the item being returned was purchased more than 18 months earlier, display (or print)
error message – ‘these items cannot be returned as it has passed the return date’ and perform
ROLLBACK. [5]
Query:
CREATE or alter TRIGGER PurchaseReturn
ON INVOICE
INSTEAD OF DELETE
AS
begin
Declare @inv_number int
select @inv_number=INV_NUMBER FROM DELETED WHERE
INV_DATE >= DATEADD(MONTH, -18, CAST(GETDATE() AS DATE))
if (@inv_number IS null)
begin
PRINT 'these items cannot be returned as it has passed the return date'

rollback;
end
ELSE
BEGIN
UPDATE LINE SET OrderStatus='Returned',
CUST_NUMBER=(SELECT CUST_NUMBER FROM INVOICE WHERE
INV_NUMBER= @inv_number) WHERE INV_NUMBER= @inv_number
DECLARE @PROD_ID VARCHAR(10)
DECLARE db_cursor CURSOR FOR SELECT PROD_CODE FROM
LINE WHERE INV_NUMBER= @inv_number
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @PROD_ID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE PRODUCT SET PROD_QOH += (SELECT
LINE_UNITS FROM LINE WHERE INV_NUMBER=@inv_number AND
PROD_CODE= @PROD_ID) WHERE PROD_CODE= @PROD_ID
FETCH NEXT FROM db_cursor INTO @PROD_ID
END
UPDATE CUSTOMER SET CUST_BALANCE +=
(SELECT SUM(INV_PAY_AMOUNT) FROM INVOICE WHERE
INV_NUMBER = @inv_number)
WHERE CUST_NUMBER=(SELECT CUST_NUMBER FROM
DELETED)
END
END
DELETE FROM INVOICE WHERE CUST_NUMBER=10014;

Output:
7. Write a procedure that takes orderstatus as a parameter and execute it to display the
customer name, name of products returned and the purchase date of those customers
who have returned the products. [Hint: You need to use LINE table and CUSTOMER
table][10]
Query:
CREATE OR ALTER PROCEDURE CheckOnOrderStatus @orderStatus
VARCHAR(10)
AS
SELECT concat(CUST_INITIAL,' ',CUST_FNAME,' ', CUST_LNAME) AS
CUSTOMER_NAME, PROD_DESCRIPT, INV_DATE FROM
LINE INNER JOIN CUSTOMER ON
LINE.Cust_Number=CUSTOMER.Cust_Number
INNER JOIN INVOICE ON INVOICE.INV_NUMBER=LINE.INV_NUMBER
INNER JOIN PRODUCT ON PRODUCT.PROD_CODE=LINE.PROD_CODE
WHERE OrderStatus = @orderStatus
GO
EXEC [CheckOnOrderStatus] @orderStatus= 'Returned';

Output:
8. Write a procedure that takes product ID as the input parameter and displays the vendor
name of the product as well as the quantity on hand. [Hint: You need to use
PRODUCT table and VENDOR table] [10]
Query:
CREATE OR ALTER PROCEDURE SelectProductDetails @productID
VARCHAR(10)
AS
SELECT VEND_NAME, PROD_QOH FROM PRODUCT INNER JOIN VENDOR
ON PRODUCT.VEND_NUMBER=VENDOR.VEND_NUMBER WHERE
PROD_CODE = @productID
GO
EXEC [SelectProductDetails] @productID= '0637032';
Output:

9. Write a function that calculates the total price of the items sold per each invoice/order.
Use LINE table to make the calculation [10]
Query:
CREATE OR ALTER FUNCTION CalculateTotalPrice (@inv_number INT)
RETURNS FLOAT(8)
AS
BEGIN
DECLARE @total_price FLOAT(8);
SELECT @total_price= SUM(LINE_AMOUNT) FROM LINE WHERE
INV_NUMBER=@inv_number;
RETURN @total_price;
END;
SELECT dbo.CalculateTotalPrice(1001) AS TotalPrice;
Output:

10. Delete the returned items from LINE table [5]
Query:
DELETE FROM LINE WHERE OrderStatus='Returned';
SELECT * FROM LINE;
Output: