Doubts in data base design.

From: Srinivas Kalla <srinivas_k21_at_hotmail.com>
Date: Fri, 30 Apr 1999 20:23:48 +0100
Message-ID: <372A0344.879BB448_at_hotmail.com>

Please send the following questions to the floowing address srinivas_k21_at_hotmail.com

Hi news group members.
Iam designing a data base.. I have the following doubts. Please clear my doubts.

Question1: Database Design
Given an universal relation schema R = {A,B,C,D,E} and the following functional dependencies defined on R:
DB-->A; C-->A; AE-->C; CE-->D; E-->A.

(a) Find a decomposition D1= {R1, R2, ..., Rn}, such that each Ri is
in 3NF, and D1 has dependency-preserving property, but not lossless join property. Specify all steps of the procedure, and prove the correctness of the result.
(b) Find a decomposition D2= {R1, R2, ..., Rm}, such that each Rj is
in BCNF, and D2 has lossless join property, but not dependency-preserving property. Specify all steps of the procedure, and prove the correctness of the result.

Question-2: Transaction Processing Technique T1 T2 T3
write_item(X) write_item(Z) read_item(Y) read_item(Z) read_item(X) read_item(Z)
write_item(Z) write_item(X) write_item(Z) write_item(Y)

Given transactions T1, T2 and T3, and a schedule S shown in Figure 1.
(a) If T1, T2, and T3 follow Multiversion Timestamps Ordering
algorithm, whether S is allowed? Why?
(b) By the end of schedule S, what are the following timestamps
values: (initial value of each of them is Zero).

read_TS(Xi); write_TS(Xi);
read_TS(Yi); write_TS(Yi);
read_TS(Zi); write_TS(Zi);

( Here i=1,2,..... for the versions of each date item)
(c) Whether S is a conflict serializable schedule? Why?
(d) Whether S is a view serializable schedule? Why?
(e) Suppose that there is a system crash at P. Whether is the schedule
recoverable? Why?
(f) Describe the recovery process from the system crash (if it happens
at p). Specify which transactions will be rolled back. Suppose that we use immediate update protocol;

Transaction T1:
write_item(X)
read_item(Z)
write_item(Z)

Transaction T2:
write_item(Z)
read_item(X)
write_item(X)

Transaction T3:
read_item(Y)
read_item(Z)
write_item(Z)
write_item(Y)

The Figure-1 is

S: r3(Y),W2(Z),W1(X),r3(Z),W3(z),W3(Y),C3,(continued in the next line)

      R1(Z),W1(Z),R2(X),W2(X),P(system crash); Received on Fri Apr 30 1999 - 21:23:48 CEST