# Assignment 1

**Topics:**Relational model, Database normalization, Zagreb

**Pages:**12 (1280 words)

**Published:**August 10, 2014

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

Please join StudyMode to read the full document