Assignment 1
Prof. Li-Yan Yuan
CMPUT 391: Database Management Systems
Solutions to Assignment 1
Due: 18:00, Feb. 10, 2014, at the 391 Drop Box
1. Present a real-life example (Not using ABCD, etc) to show differences between BCNF and 4NF.
Solution: Consider the following table real_estate(realtor_id,listing_property,customer_name) used to store the information for a real estate company with one MVD constraint
→
realtor id → listing property | customer name.
It is not difficult to see that real estate is in BCNF but not in 4NF.
2. Consider a database consisting of the following tables with obvious meanings: employee( employee_name, street, city ) works( employee_name, company_name, salary ) company( company_name, city )
Write triggers, according to Oracle style, to enforce the constraint that no employee is allowed to work for a company NOT located in the city where the employee lives. One needs to (1) give a list of events that must be monitored for imposing the constraint, and (2) present one trigger for any event in (1). [20]
Solution:
[1] The following events must be monitored for imposing the constraint:
INSERT OR UPDATE on all three tables.
[2] A trigger for monitoring the INSERT or UPDATE on employee is given below. create trigger employee_location before insert or update on employee for each row declare dummy integer; begin select count(*) into dummy from works, company where works.company_name = company.company_name and
:new.employee_name = works.employee_name and
:new.city company.city; if ( dummy > 0 ) then raise_application_error(-20502, ’no employee shall live in the same city as the location of the company ’); end if; end; 1
3. Consider a relation schema R = ABCDE, functional dependencies
A→ C
B→ C
C→ D
DE → A and a decomposition D = {AC, AD, BE, AB} of R.
Prove or disprove that D is a join lossless decomposition of R with respect the given set of FDs.
A proof can be done by finding an appropriate decomposition
CMPUT 391: Database Management Systems
Solutions to Assignment 1
Due: 18:00, Feb. 10, 2014, at the 391 Drop Box
1. Present a real-life example (Not using ABCD, etc) to show differences between BCNF and 4NF.
Solution: Consider the following table real_estate(realtor_id,listing_property,customer_name) used to store the information for a real estate company with one MVD constraint
→
realtor id → listing property | customer name.
It is not difficult to see that real estate is in BCNF but not in 4NF.
2. Consider a database consisting of the following tables with obvious meanings: employee( employee_name, street, city ) works( employee_name, company_name, salary ) company( company_name, city )
Write triggers, according to Oracle style, to enforce the constraint that no employee is allowed to work for a company NOT located in the city where the employee lives. One needs to (1) give a list of events that must be monitored for imposing the constraint, and (2) present one trigger for any event in (1). [20]
Solution:
[1] The following events must be monitored for imposing the constraint:
INSERT OR UPDATE on all three tables.
[2] A trigger for monitoring the INSERT or UPDATE on employee is given below. create trigger employee_location before insert or update on employee for each row declare dummy integer; begin select count(*) into dummy from works, company where works.company_name = company.company_name and
:new.employee_name = works.employee_name and
:new.city company.city; if ( dummy > 0 ) then raise_application_error(-20502, ’no employee shall live in the same city as the location of the company ’); end if; end; 1
3. Consider a relation schema R = ABCDE, functional dependencies
A→ C
B→ C
C→ D
DE → A and a decomposition D = {AC, AD, BE, AB} of R.
Prove or disprove that D is a join lossless decomposition of R with respect the given set of FDs.
A proof can be done by finding an appropriate decomposition