All operations will be performed on the Mall database. You should test your solu
ID: 3719808 • Letter: A
Question
All operations will be performed on the Mall database. You should test your solution using your solution to Hw #11 and the posted solution to HW #11. Perform test cases related to the scenarios you implement below to verify your additions are working properly. [5 pts] Implement the following Foreign Keys. . The eid in Employs references the eid in Employees. . The store in Employs references the name in Stores. . The name in Inventory references the name in Stores. . The item in Inventory references the item in Items. 1. The aid in Customers references the aid in Accounts. [10 pts] Create a trigger that identifies when an insert into the Inventory items list does not exist in the Items table. In this case, insert this new item into the Items table with the brand "generic" and the price found by the average price of all the items currently in the Items table. 2. [10 pts] Implement trigger(s) that will respond to any update or insertion into the database that would give an account type any value other than credit, checking, or cash the value cash. 3.Explanation / Answer
1.
ALTER TABLE Employs
ADD FOREIGN KEY (eid) REFERENCES Employees(eid);
ALTER TABLE Employs
ADD FOREIGN KEY (store) REFERENCES Stores(name);
ALTER TABLE Inventory
ADD FOREIGN KEY (name) REFERENCES Stores(name);
ALTER TABLE Inventory
ADD FOREIGN KEY (item) REFERENCES Inventory(item);
ALTER TABLE Customers
ADD FOREIGN KEY (aid) REFERENCES Accounts(aid);
2.
/* the trigger throws an exception if a item already exists in invetntory */
CREATE TRIGGER trigone BEFORE INSERT ON Inventory
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW WHEN (NEWROW.ID > 100)
BEGIN ATOMIC
IF EXISTS (SELECT * FROM INVENTORY WHERE INVENTORY .ITEM = NEWROW.ITEM) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'already exists';
ELSE INSERT INTO INVENTORY Id,BRAND,PRICE Values (NEWROW.Id , 'Generic' , (SELECT AVG(price) FROM INVENTORY ) )
END IF;
END
3.
Incomplete db given please mention table