get help with homework

Part I. Database Design (40 points)
A. You are tasked with enforcing the following functional dependency on the customer data:
City – Nation
As we’ve learned in class, this functional dependency can be enforced through decomposition.
However, in the schema you are given, the customer table was unnecessarily decomposed (it
happens in the real-world) into CUSTOMER_T1 and CUSTOMER_T2. Therefore, this
functional dependency must be enforced across multiple tables. Luckily, we learned that triggers
can be used in this situation.
Q.PI.A1. Write a trigger that enforces the functional dependency City  Nation for
INSERT, UPDATE, and DELETE.
Hint: triggers can only be attached to one table at a time, so the same trigger would have to be
attached to each table involved in the FD.
B. You are tasked with tracking the total price of all orders for each customer. Fortunately,
someone previously added the column C_TotalOrderPrice column to the customer table.
However, one of our employees currently spends 2 hours each day manually updating these
entries. Rather than have someone manually update these values, we want to streamline our
business, and automatically update this value.
Q.PI.B1. Write a trigger that updates the C_TotalOrderPrice column in the customer table
each time a new order is added, updated, or deleted from the LINEORDER table.
C. Due to federal regulations, your company can only fulfill orders on Monday, Tuesday, and
Wednesday. You need to guarantee that orders will not occur on Thursday, Friday, Saturday, or
Sunday. Luckily for us, Oracle has a function that returns the day of the week for a given date:
SELECT TO_CHAR(date ‘2018-11-01’, ‘DAY’) day FROM animal;
returns ‘Thursday’. In the data you are given, the LO_Orderdate column in the LINEORDER
table is unfortunately an integer (data will not always be clean and friendly in the real-world).
Therefore, you will need to convert the integer to the proper date format (Hint: this can be done
with SUBSTRING and string concatenation, ||).
Q.PI.C1. Write a trigger that prevents records from being added to the LINEORDER table
where the LO_OrderDate is a Thursday, Friday, Saturday, or Sunday.
Hint: You will want to create a view that replicates lineorder (CREATE VIEW Lineorderview
AS SELECT * FROM Lineorder), load the data by inserting into LineorderView and use an
INSTEAD OF INSERT trigger over that view.
D. Using the DDL file provided and the triggers you created in Parts I.A I.B, and I.C, load the
data from the csv files provided.
Q.PI.D1. Report the time to load each table and the number of records for each table using
SELECT COUNT(*) FROM [table_name].
Hint: You may want to call executeBatch() every 10,000 records for reasonable performance.

You are tasked with enforcing the following functional dependency on the customer data: City – Nation
5 (100%) 1 vote

20% Discount on all orders. Use the code: HELP20 - Order Now Dismiss