In this project, you will work with a modified version of the Star Schema Benchmark (SSBM). This benchmark is widely used in the database systems research community. It combines a realistic distributed data (maintaining correct data types and cross-column correlations) with a synthetic data generator. The table definitions (including the primary keys and foreign keys) and the data to populate these tables can be downloaded from the following link:

http://dbgroup.cdm.depaul.edu/CSC355_Fall2018/FinalProjectData.zip

There is a total of three parts to this project along with an extra credit part. Each part has multiple tasks. Everything that requires a response in your submission is in bold.

A. You are tasked with enforcing the following functional dependency on the customer data:

City Nation

As weve 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 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.