Question

Using MySQL commands answer the questions listed below using the Premier Products Company schema.

1.Using Views

a) Create a view called part_location that has the following attributes: part_num, part_description, part_quantity, warehouse_name, warehouse_address. This data comes from the part and warehouse entities.

b) Write a query using the view that shows the total number of parts ordered from each warehouse. The output should look like this:

2. Using Triggers

a) Execute the following SQL to create the customer_audit table in the premier schema.

CREATE TABLE IF NOT EXISTS customer_audit ( customer_num CHAR(3) NOT NULL, customer_name VARCHAR(35) NOT NULL, street VARCHAR(15), city VARCHAR(15), state CHAR(2), zip CHAR(5), credit_limit DECIMAL(8,2), date_changed DATETIME NOT NULL, changed_by VARCHAR(45) NOT NULL);

b) Notice that the audit table does not have a primary key defined. Explain why this might be acceptable.

c) Based on the current attributes in the customer_audit table, suggest a possible primary key and explain why it might be unique.

d) Create a trigger that will insert a record into the customer_audit table when the customer data is changed. Only insert a new record if the attributes that are being audited change. The attributes being audited are those that exist in the customer_audit table. For example the customer.balance attribute is not being audited so if it is the only attribute that changes as part of the transaction then we do not need to insert a new record into the audit table. Provide the SQL used to create this trigger.

e) Write an SQL update statement that will change a customer and cause an audit record to be created. Provide the SQL statement you executed for this.

f) Write an SQL statement that will display what is in the customer_audit table. Provide the SQL used

Answer 1

Question 1 a:

View :

create view part_location
as
select part_num,description as 'part_description',on_hand as 'part_quantity',
name as 'warehouse_name',address as 'warehouse_address'
from part,warehouse
where
part.warehouse_id=warehouse.id;

Use view in SQL query :

select * from part_location;

Explanation :

• Above sql query will create view called part_location and return the details.

******************************

Question 1 b:

SQL Query :

select warehouse_name,count(part_num) as 'total number of parts ordered'
from part_location
group by warehouse_name;

Explanation :Above sql query will get the warehouse_name and total number of parts from part_location view.

****************************

Question 2 b:

• Here this customer_audit table is used to track the records from the customer table when it is updated or inserted and it is allowed even if table is without primary key.

*******************************

Question 2 c:

Here primary key will be date_changed because it stores date and time when records has been updated.