# (Aktu Btech) Database Management System Important Unit-3 Database Design and Normalization

Aktu’s Quantum Notes for Database Management System can help you succeed in your B.Tech studies. These crucial, repeating questions and notes will help you master key concepts and do well on exams. Unit-3 Database Design and Normalization

```Dudes 🤔.. You want more useful details regarding this subject. Please keep in mind this as well.

Important Questions For Database Management System:
*Quantum               *B.tech-Syllabus
*Circulars                *B.tech AKTU RESULT
* Btech 3rd Year    * Aktu Solved Question Paper```

Ans. Algorithm:

Ans.

## Q3. Prove that BCNF is stronger than 3NF.

Ans. 1. A relation, R, is in 3NF if for every dependency X→A satisfied by R at least one of the following conditions:

a. X→A is trivial (i.e., A is subset of X)

b. X is a superkey for R, or

c. A is a key attribute for R.

BCNF does not permit the third of these options.

2. BCNF identifies some of the anomalies that are not addressed by 3NF

3. A relation in BCNF is also in 3NF but vice-versa is not true,

Hence, BCNF is more striet/ stronger than 3NF.

## Q4. Explain full functional dependency and partial functional dependency.

Ans. Full functional dependency:

• 1. Given a relation scheme R and functional dependency.X→Y, Yis fully functionally-dependent on A, if there is no Z, where Z is a proper subset of Y such that Z→Y.
• 2. The dependency X→Y is left reduced, there being no extraneous attributes in the L.H.S of the dependency.

For example: In the relational schema R (ABCDE) with the FDs.

The dependency A→BC is left reduced and BD is fully functionally dependent on A.

However the functional dependencies ABH→BC is not left reduced because the attribute B being extraneous in this dependency.

Partial functional dependency:

1. Given a relation schema R with the functional dependencies F defined on the attributes of R and K as a candidate keys if X is a proper subset of K and if X→A thenA is said to be partially dependent on K.

For example:

• i. In Fig. [Name + Course] is a candidate key, So Name and Course are prime attributes, Grade is fully functionally dependent on the candidate keys and Phone no., Course-deptt. and roll no. are partially functional dependent on the candidate key.
• ii. Given R (A, B, C, D) and F = {AB→C, B→D}. Then key of this relation is AB and D is partially dependent on the key.

## Q5. Define minimal cover. Suppose a relation R(A, B, C) has FD set F= {A→B, B→C,A→C, AB→B, AB→C, AC→B}. Convert this FD set into minimal cover.

Ans. Minimal cover: A minimal cover of a set of FDs F is a minimal set of functional dependencies Fmin that is equivalent to F.

Numerical :

Given: R(A, B, C)

Non-redundant cover for F:

Step 1: Only one attribute on right hand side

Step 2: No extraneous attribute on left hand side. Since

ABB, ABC, ACB are extraneous attribute. Hence, remove all these we get

Step 3: By rule of transitivity, we can remove. Hence, we get the minimal cover

## Q6. What is meant by the attribute preservation condition on decomposition ? Given relation R(A, B,CD.E) with the functional dependencies F = {AB→CD,A→E, C→D), the decomposition of R into R1(A, B, C), R2(B, C, D), R3(C, D, E) check whether the relation is lossy or lossless.

Ans. Attribute preservation condition on decomposition:

• 1. The relational database design algorithms start from a single universal relation schema R = {A1,A2,…….. ,An} that includes all the attributes of the database.
• 2. We implicitly make the universal relation assumption, which states that every attribute name is unique.
• 3. Using the functional dependencies, the algorithms decompose the universal relation schema R into a set of relation schemas D = {R1, R2,…….Rm} that will become the relational database schema; D is called a decomposition of R.
• 4. Each attribute in R must appear in at least one relation schema Ri in the decomposition so that no attributes are lost; formally, we have

This is called the attribute preservation condition of decomposition.

Numerical:

After applying first two functional dependencies first row contain all “a” symbols. Hence it is lossless join.