DBMS: Keys and Normalisationडीबीएमएस: की और नॉर्मलाइज़ेशन questions and answersप्रश्न और उत्तर

How dbms: keys and normalisation worksडीबीएमएस: की और नॉर्मलाइज़ेशन को समझिए

Start with the closure of attribute sets. Any set whose closure is the whole relation is a superkey; the minimal ones are candidate keys.

Partial dependencies (on part of a key) break 2NF; transitive dependencies (key → X → A) break 3NF; any dependency whose left side is not a superkey breaks BCNF.

Decomposition must be lossless (the common attributes form a key of one part) and should preserve dependencies where possible.

Each trick says when to use it, why it works and when it fails. Formula tricks are checked by code over their whole range.हर ट्रिक बताती है कि उसे कब इस्तेमाल करें, वह क्यों काम करती है और कब नहीं। सूत्र वाली ट्रिक्स को कोड से उनकी पूरी रेंज पर जाँचा गया है।

Answers and full solutions are shown. Hide them to test yourself first.उत्तर और पूरे हल दिखाए गए हैं। पहले खुद को परखने के लिए इन्हें छिपाइए।

1. Example 1उदाहरण 1 · DBMS: Keys and Normalisation · Easyआसान

   A table for students in a Mumbai college stores each student's phone numbers as a comma‑separated list in a single column. Which version of the table satisfies First Normal Form?

   1. Each phone number in a separate row with the same student ID
   2. The original table with comma‑separated phone numbers
   3. A table where the phone column holds an array type
   4. A table that allows NULL in the phone column
   Answer:उत्तर: Each phone number in a separate row with the same student ID

   1. Recall 1NF requires every attribute to hold a single atomic value.
   2. Identify the column that stores multiple phone numbers.
   3. Choose the representation where each phone number appears in its own row.

2. Example 2उदाहरण 2 · DBMS: Keys and Normalisation · Mediumमध्यम

   Relation R(A, B, C, D) has functional dependencies A → B, C → D, and AC → B. Which of the following attribute sets is a super key but not a candidate key?

   1. AC
   2. ABC
   3. A
   4. B
   Answer:उत्तर: ABC

   1. Find candidate keys by computing closures: AC⁺ = {A, C, B, D} = all attributes, and no subset of AC is a key.
   2. A super key is any superset of a candidate key. ABC contains AC, so it is a super key.
   3. Since ABC has a proper subset (AC) that is a key, ABC is not minimal and therefore not a candidate key.

3. Example 3उदाहरण 3 · DBMS: Keys and Normalisation · Hardकठिन

   Relation R(A, B, C, D) has functional dependencies A → B, C → D. It is decomposed into R1(A, B) and R2(C, D). Which of the following statements is true about this decomposition?

   1. The decomposition is lossless‑join but does not preserve dependencies.
   2. The decomposition preserves dependencies but is not lossless‑join.
   3. The decomposition is both lossless‑join and dependency preserving.
   4. The decomposition is neither lossless‑join nor dependency preserving.
   Answer:उत्तर: The decomposition preserves dependencies but is not lossless‑join.

   1. Compute intersection of R1 and R2 → empty set.
   2. Lossless join needs (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2; empty set cannot determine anything → not lossless.
   3. Project FDs: R1 keeps A → B, R2 keeps C → D → all original FDs preserved.
   4. Hence dependency preserving but not lossless.

4. Example 4उदाहरण 4 · DBMS: Keys and Normalisation · Mediumमध्यम

   Relation R(A, B, C, D) has functional dependencies A → B, C → D, and AC → B. Which of the following attribute sets is a super key but not a candidate key?

   1. AC
   2. ABC
   3. A
   4. B
   Answer:उत्तर: ABC

   1. Find candidate keys by computing closures: AC⁺ = {A, C, B, D} = all attributes, and no subset of AC is a key.
   2. A super key is any superset of a candidate key. ABC contains AC, so it is a super key.
   3. Since ABC has a proper subset (AC) that is a key, ABC is not minimal and therefore not a candidate key.

5. Example 5उदाहरण 5 · DBMS: Keys and Normalisation · Mediumमध्यम

   Relation R(A, B, C, D) has functional dependencies A → B, C → D, and AC → B. Which of the following attribute sets is a super key but not a candidate key?

   1. AC
   2. ABC
   3. A
   4. B
   Answer:उत्तर: ABC

   1. Find candidate keys by computing closures: AC⁺ = {A, C, B, D} = all attributes, and no subset of AC is a key.
   2. A super key is any superset of a candidate key. ABC contains AC, so it is a super key.
   3. Since ABC has a proper subset (AC) that is a key, ABC is not minimal and therefore not a candidate key.

6. Example 6उदाहरण 6 · DBMS: Keys and Normalisation · Mediumमध्यम

   Relation R(A, B, C, D) has functional dependencies A → B, C → D, and AC → B. Which of the following attribute sets is a super key but not a candidate key?

   1. AC
   2. ABC
   3. A
   4. B
   Answer:उत्तर: ABC

   1. Find candidate keys by computing closures: AC⁺ = {A, C, B, D} = all attributes, and no subset of AC is a key.
   2. A super key is any superset of a candidate key. ABC contains AC, so it is a super key.
   3. Since ABC has a proper subset (AC) that is a key, ABC is not minimal and therefore not a candidate key.

Common mistakesआम गलतियाँ

• Confusing a superkey with a candidate key.
• Calling a relation BCNF because it is in 3NF.
• Ignoring prime attributes in the 3NF test.

Frequently asked questionsअक्सर पूछे जाने वाले प्रश्न