Databases Test 1

Option 3: P → Q and Q → R

P⁺ = {P, Q, R}

It covers, P → QR, PQ → R and P → Q

Also, Q → R

It is also minimal

According to the 2PL protocol, a transaction handles its locks in two distinct, consecutive phases during the transaction's execution:

Expanding phase: locks are acquired and no locks are released (the number of locks can only increase).

Shrinking phase: locks are released and no locks are acquired.

2PL ensures conflict serializability but does not ensure deadlock freedom.

But time stamp ordering ensures both deadlock freedom and conflict serializabilty.

Let us take example to prove:

R:

S:

A U B

R – S

S – R

R ∩ S ≡ R – (R – S) ≡ S – (S – R) ≡ ((R U S – (S – R)) – (R – S)

R U S – ((S – R) – (R – S))

Hence R U S – ((S – R) – (R – S)) is not equivalent to R ∩ S 

Functional Dependencies are X → Y and X → Z.

{X}+ = {YZ}

8 FD’s are possible

X → ϕ, X → X,  X → Y, X → Z

X → XY, X → XZ,  X → YZ, X → XYZ

The number of functional dependencies possible with attribute B is 4

Shortcuts:

Number of FD’s possible = 2n

where n is the number of attributes in the closure of X

When tuple (3,4) is deleted, then as 3 is referencing to Y because Y is the foreign key. So, in the above table entries that contain 3 in their Y attribute will be deleted.

So, (4,3) will be deleted. Now, we should delete the row which contains 4 in their Y attribute.

So, (2,4) and (6,4) will be deleted. Now, we should delete the row which contains 2 and 6 in their Y attribute

(5,2) and (7, 2) will be deleted. Now, we should delete the row which contains 5 and 7 in their Y attribute

So, (9,5) will be deleted. Since no Y attibute doesn't have 9, deletion stops. .

Concept:

A natural join is based on common attributes or common columns to join two tables or relations.

Explanation:

Consider an example, suppose there are two tables Employee(Eid, Ename) consists of 5 tuples and department(Eid, Did) consists of 3 tuples only.

Natural join of employee and department(Employee * Department) gives:

So, maximum number of tuples in natural join are from the relation having minimum tuples. So, in given question, R2 contains 30 tuples, so , maximum number of tuples in the output of natural join between tables R1 and R2 i.e. R1 * R2 = 30.

Data:

Minimum number of keys = MIN = 23

order of a B+ tree = p

Formula:

MIN = ⌈p ÷ 2⌉ – 1

Maximum number of keys = MAX = p – 1

Calculation:

23 = ⌈p ÷ 2⌉ – 1  

If p = 48 and p = 47

Satisfied the condtion

Maximum number of keys = p – 1 = 48 – 1 

Maximum is 47.

For (Name, Age) to be a key for this instance, the pair (Name, Age) should be unique. Therefore, X should not be equal to 23 to make it a key.

A relation schema R is in BCNF, only if in every non-trivial functional dependency 

X → Y 

X is a superkey

R(ABC)

(AB)⁺ = {A, B, C}

(AC)+ = {A, B, C}

(BC)+ = {C, B, A}

Candidate key: AB, AC and BC

BCNF is the strongest form which is not violated.

A clustered index is when a file is organized so that ordering of the data records is the same as or close to the ordering of data entries in the index.  Alternative 1 by definition is clustered.  An index that uses Alternative2 or3 can be a clustered index only if the data records are sorted on the search key field.

A clustered index offers much better range query performance, but essentially the same equality search performance (modulo duplicates) as an unclustered index. Further, a clustered index is typically more expensive to maintain than an unclus­tered index. Therefore, we should make an index be clustered only if range queries are important on its search key. At most one of the indexes on a relation can be clustered, and if range queries are anticipated on more than one combination of fields, we have to choose the combination that is most important and make that be the search key of the clustered index.

F = {AB → C, AC → B, AD → E, B →D, BC → A, E → G}

R₁(ABC)

AB → C

AC → B

BC → A 

R₂(ACDE)

AD → E

AC → D (From AC → B and B → D)

R₃(ADG)

AD → G (From AD → E and E → G)

Decomposition is not dependency preserving because B → D and E → G cannot be generated after decomposition

Decomposition is lossless:

R₁(ABC) and R2(ACDE) contains (AC) and (AC) is key in R₁ or R₂

Therefore it is lossless

Consider the joined table be R₄

Now R₄(ABCDE) and R3(ADG) contains (AD) and (AD) is key in R₃

Therefore it is lossless

Hence decomposition is lossless. 

no two books have the same price

Assume table BOOK B :

Book T:

One record is compared with all the records of another table. I.e. if we consider A (10) in Book T, then it is compared against all the records of book B. it is not greater than any record.

Now compare B(20) of book T with all records of book B. So, it is greater than 1 record.

Compare C(30) of book T with all records of book B. So, it is greater than 2 record.

Compare D(40) of book T with all records of book B. So, it is greater than 3 record.

Compare E(50) of book T with all records of book B. So, it is greater than 4 record.

Compare F(60) of book T with all records of book B. So, it is greater than 5 record.

Compare G(70) of book T with all records of book B. So, it is greater than 6 record.

Compare H(40) of book T with all records of book B. So, it is greater than 3 record.

Concepts:

Any superset of a key (primary key or candidate key) is a superkey.

Explanation:

T⁺ = {T, P, R, S, Q, V}

T is the only candidate key

∴ T is the primary key

Data:

n → total number of attributes = 6

n(s) → total number of super keys

Since T is a primary key it must be included in every superkey.

Attribute P, Q, R, S, and V may or may not included in superkey.

Formula:

Since only one superkey is present in the relation:

n(S) = 2n -1

Calculation:

n(S) = 26-1 = 25 = 32

Examples of Superkeys are: {T, TP,TQ, TR...}.

Important Point:

A primary key is chosen from a set of candidate key since there is only one candidate key, it is treated as a primary key. 

The relation returned by the division operator between two relations A and B are those tuples from relation A which are associated to every B’s tuple.

R:

S:

T = R/S

T:

There are two 'there exists' clauses in the given tuple relational calculus which are connected by and (ᴧ). The tuple variable u is restricted to departments that are located in the Taylor building, while tuple variable s is restricted to instructors whose dept_name matches that of tuple variable u.

Therefore, the given expression finds the names of all instructors whose department is in the Taylor building.

Inner query:

SELECT Group_B.Age

 FROM Group_B

where Group_B.Student_Name = ‘Y’

Output: 18

Outer query:

Select Group_A.Roll_no

FROM Group_A

Where Group_A.age > All(18)

Output:             

In R(ABCDEFGH), left-hand side of FD has attributes: {G, B, C, F, H, A, E, G} 

Since it doesn't contain D we cannot derive it unless it is the present right-hand side. 

(AD)+ =  {A,D, B, C, F, H, G, E } 

Since all the 8 attributes are present, AD is a candidate key

(BD)+ =  {B, D, C, F, H, G, E, A } 

Since all the 8 attributes are present, BD is a candidate key

(ED)+ =  {E, D, A, B, C, F, H, G } 

Since all the 8 attributes are present, ED is a candidate key

(FD)+ =  {F, D, E, G, A, B, C, H } 

Since all the 8 attributes are present, FD is a candidate key

The non-prime attributes are dependent on a partial candidate key.

A → BC      ---(A → C)
B → CFH    ---((B → H)
F → EG      ----(F → G)

Therefore it is in 1NF but not in 2 NF