##### Quick Review

Example:

Consider two functional dependency set F and G: F={ A→B, B→C, C→A} and G= {A→BC, B→AC, AB→C, BC→A} which of the following is true?

A)F⊃G

B))G⊃F

C)F≡G

D)None of these

Solution:

Here, According to FD set F,
A+= {ABC}, B+= {ABC}, C+= {ABC}
According to FD set G,
A+= {ABC}, B+= {ABC}, C+= {C}
So, F covers G but G doesn’t covers F. Hence, option A is correct.

Example:

When converting one (1) to many (N) binary relationship into tables, the recommended solution is usually

A)One big table with all attribute from both entites included

B)Foreign key added on child  (Many side) referencing the parent.

C)Foreign key added on parent  (one side) referencing the child.

D)foreign key added on the both side(both table)

Solution:

When converting one (1) to many(N) binary relationship into tables, the recommended is foreign key added on the Child(many side) referencing the parent.

Example:

Which cardinality best describes relationship runs where each team has drag many experiment to runs?

A)1:1

B)1:N

C)N:1

D)N:M

Solution:

From the above cardinalities, we see that ratios like 1:1, 1:N, N:1 and N:M gives cardinality constraint or numeric restriction on possible relationships. From this, we see that the ratio 1:N relation is most comfortable to describe the constant of ER team.

Example : Let a Relation R have attributes {a1,a2,a3} & a1 is the candidate key. Then how many super keys are possible?

Solution:

Here, any superset of a1 is the super key.
Super keys are = {a1, a1 a2, a1 a3, a1 a2 a3}
Thus we see that 4 Super keys are possible in this case.

In general, if we have ‘N’ attributes with one candidate key then the number of possible superkeys are 2(N – 1).

Example : Let a Relation R have attributes {a1, a2, a3,…,an}. Find Super key of R.

Solution

Maximum Super keys = 2n – 1.
If each attribute of relation is candidate key.

Example: In relation STUDENT( STUD_NO,STUD_NAME,STUD_STATE,STUD_COUNTRY,STUD_AGE)

FD set:

{STUD_NO -> STUD_NAME, STUD_NO -> STUD_STATE, STUD_STATE -> STUD_COUNTRY, STUD_NO -> STUD_AGE, STUD_STATE -> STUD_COUNTRY}

Candidate Key: {STUD_NO}.find the normal form and  if it is not in 3NF decompose into 3NF.

Solution:

For this relation

STUD_NO -> STUD_STATE and STUD_STATE -> STUD_COUNTRY are true.

So STUD_COUNTRY is transitively dependent on STUD_NO. It violates third normal form.

To convert it in third normal form, we will decompose the relation STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY_STUD_AGE) as:
STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_AGE)
STATE_COUNTRY (STATE, COUNTRY)