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
Q1. Write the algorithm to find minimal cover F for set of functional dependencies E.
Ans. Algorithm:
Q2. Write the difference between BCNF and 3NF.
Ans.
| S. No. | BCNF | 3NF |
| 1. | In BCNF, for any FDs for a relation R, A → B, A should be a super key of relation. | In 3NF, there should be no transitive dependency that is no non prime attribute should be transitively dependent on the candidate key. |
| 2. | lt is comparatively stronger than 3NF. | It is less strong than BCNF. |
| 3. | In BCNF, the functional dependencies are already in INF, 2NF and 3NF. | In 3NF, the functional dependencies are already in 1NF and 2NF. |
| 4. | Redundancy is low. | Redundancy is high. |
| 5. | In BCNF, there may or may not be preservation of all | In 3NF, there is preservation of all functional dependencies. |
| 6. | It is difficult to achieve. | It is comparatively easier to achieve. |
| 7. | Lossless decomposition is hard to achieve in BCNF. | Lossless decomposition can be achieved by 3NF. |
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
AB→B, AB→C, AC→B 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.
Database Management System Btech Quantum PDF, Syllabus, Important Questions
| Label | Link |
|---|---|
| Subject Syllabus | Syllabus |
| Short Questions | Short-question |
| Question paper – 2021-22 | 2021-22 |
Database Management System Quantum PDF | AKTU Quantum PDF:
| Quantum Series | Links |
| Quantum -2022-23 | 2022-23 |
AKTU Important Links | Btech Syllabus
| Link Name | Links |
|---|---|
| Btech AKTU Circulars | Links |
| Btech AKTU Syllabus | Links |
| Btech AKTU Student Dashboard | Student Dashboard |
| AKTU RESULT (One VIew) | Student Result |