# Assignment 1

Topics: Relational model, Database normalization, Zagreb Pages: 12 (1280 words) Published: August 10, 2014
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 diﬀerences 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 diﬃcult 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 ﬁnding an appropriate decomposition tree, and a counter-example is good enough for a disproof.[10]

Solution: We are going to disprove the claim using a counter-example. Consider a table r as follows:
A B C D E
a1 b c d e1
a2 b c d e2
Then r satisﬁes the given set of FDs, and further we have
ΠAB (r) 1 ΠAC (r) 1 ΠAD (r) 1 ΠBE =
A
a1
a2

B
A
b 1 a1
b
a2

A
a1
= a1
a2
a2

B
b
b
b
b

C
c
c
c
c

C
A
c 1 a1
c
a2
D
d
d
d
d

D
B
d 1 b
d
b

E
A
e1 = a1
e2
a2

B
b
b

C
c
c

D
B
d 1 b
d
b

E
e1
e2

E
e1
e2 = r.
e1
e2

Therefore, the decomposition is NOT join lossless.
Solution2: This can also be done by using the Tabular test as follows. The initial table is: A
a1
a1
b31
a1

B
b12
b22
a2
a2

C
a3
b23
b33
b43

D
b14
a4
b34
b44

E
b15
b25
a5
b45

C
a3
a3
b33
a3

D
b14
a4
b34
b44

E
b15
b25
a5
b45

Apply A → C:
A
a1
a1
b31
a1

B
b12
b22
a2
a2

Apply B → C:

2

A
a1
a1
b31
a1

B
b12
b22
a2
a2

C
a3
a3
a3
a3

D
b14
a4
b34
b44

E
b15
b25
a5
b45

C
a3
a3
a3
a3

D
a4
a4
a4
a4

E
b15
b25
a5
b45

D
a4
a4
a4
a4

E
b15
b25
a5
b45

Apply C → D:
A
a1
a1
b31
a1

B
b12
b22
a2
a2

Apply DE → A:
A
a1
a1
b31
a1

B
b12
b22
a2
a2

C
a3
a3
a3
a3

Since no row is all a’s, the decomposition is not a lossless join. 4. Consider R = ABCDEGHK and the following set F of functional dependencies: E → DK
C→ K
EK → BC
HK → A
(a) Find a join loss-less, dependency preserving and 3NF decomposition of R. (b) Indicate whether your database schema is in BCNF with respect to F . Explain. [20] Solution:
(a) We ﬁrst ﬁnd a minimal cover of the FDs, as shown below. Right reduced Left Reduced: Minimal Cover
E→ D
E→ D
E→ D
E→ K
E→ K
C→ K
C→ K
C→ K
E→ B
EK → B
E→ B
E→ C
EK → C
E→ C
HK → A
HK → A
HK → A
Then construct a database D = {EDBC, CK, HKA}.
Now, we need to check if D contains any candidate key.
Since no...