2.26 [GATE 2006 : 2 Marks]
Question:
The following functional dependencies are given: AB -> CD, AF -> D, DE -> F, C -> G, F -> E, G -> A. Which one of the following options is false?
(a) {CF}+ = {ACDEFG}
(b) {BG}+ = {ABCDG}
(c) {AF}+ = {ACDEFG}
(d) {AB}+ = {ABCDFG}
Answer: (c) {AF}+ = {ACDEFG} is FALSE. [Also (d) as per this print is FALSE]
Solution:
Closure निकालो:
CF+ = C,F -> G (C->G) -> A (G->A) -> E (F->E) -> D (AF->D) = {A,C,D,E,F,G} => (a) True.
BG+ = B,G -> A (G->A) -> CD (AB->CD) = {A,B,C,D,G} => (b) True.
AF+ = A,F -> E (F->E) -> D (AF->D) = {A,F,D,E} only. C,G कभी नहीं आएगा। तो {ACDEFG} False है।
AB+ = A,B -> CD (AB->CD) -> G (C->G) = {A,B,C,D,G} => {ABCDFG} False है।
GATE Original में (c) को False माना है।
1-Line Trick: AF से C, G कभी नहीं आएगा – इसलिए AF वाला Closure False है।
2.27 [GATE 2007 : 2 Marks]
Question:
Which one of the following statements is FALSE?
(a) Any relation with two attributes is in BCNF
(b) A relation in which every key has only one attribute is in 2NF
(c) A prime attribute can be transitively dependent on a key in 3NF relation
(d) A prime attribute can be transitively dependent on a key in a BCNF relation
Answer: (d) A prime attribute can be transitively dependent on a key in a BCNF relation
Solution:
(a) True – 2 attribute में सारे FD में LHS Superkey ही होगा।
(b) True – Single attribute Key में Partial Dependency Possible ही नहीं।
(c) True – 3NF में Non-Prime -> Prime Transitive Allowed है।
(d) False – BCNF में हर FD का LHS Super Key होना चाहिए, Transitive चाहे Prime पर भी हो, BCNF Fail हो जाता है।
1-Line Trick: BCNF में कोई भी Transitive नहीं चलता – चाहे Prime हो या Non-Prime।
2.28 [GATE 2007 : 2 Marks]
Question:
Consider the following implications relating to functional and multivalued dependencies given below, which may or may not be correct.
(i) If A ->-> B and A ->-> C then A ->-> BC
(ii) If A -> B and A -> C then A ->-> BC
(iii) If A ->-> BC and A -> B then A ->-> C
(iv) If A -> BC and A -> B then A ->-> C
Exactly how many of the above implications are valid?
(a) 0 (b) 1 (c) 2 (d) 3
Answer: (c) 2
Solution:
(i) MVD में A->->B और A->->C => A->->BC? True है – MVD Complementation Rule।
(ii) FD से MVD Replication होता है: A->B => A->->B, इसलिए A->B, A->C से A->->BC भी True।
(iii) और (iv) MVD Coalescence वाले Cases Fail होते हैं।
Total 2 Valid हैं।
1-Line Trick: FD -> MVD Replication से (i) और (ii) दोनों True हैं।
2.29 [GATE 2008 : 2 Marks]
Question:
Let R (A, B, C, D) be a relational schema with the following functional dependencies: A -> B, B -> C, C -> D and D -> B. The decomposition of R into (A, B), (B, C), (B, D)
(a) Gives a lossless join, and is dependency preserving
(b) Gives a lossless join, but is not dependency preserving
(c) Does not give a lossless join, but is dependency preserving
(d) Does not give a lossless join and is not dependency preserving
Answer: (c) Does not give a lossless join, but is dependency preserving
Solution:
Dependency: A->B (A,B में), B->C (B,C में), D->B और B->D? Actually D->B दिया है, B->D? C->D से B->D via B->C->D मिल जाता है, तो B->D भी (B,D) में है। C->D Preserve नहीं हुआ? B->C, C->D => B->D से Cover हो जाता है, पर C->D Direct नहीं। GATE Answer के हिसाब से Dependency Preserving माना है।
Lossless: (A,B) ∩ (B,C) = B, B->C है तो (A,B,C) Lossless, फिर (A,B,C) ∩ (B,D) = B, B->D है तो Lossless होना चाहिए पर इस Print में (c) दिया है।
1-Line Trick: B Common है और B->C, B->D है तो Lossless का Check B से करो।
2.30 [GATE 2008 : 2 Marks]
Question:
Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold: AB -> CD, DE -> P, C -> E, P -> C and B -> G. The relational schema R is
(a) In BCNF
(b) In 3NF, but not in BCNF
(c) In 2NF, but not in 3NF
(d) Not in 2NF
Answer: (d) Not in 2NF
Solution:
AB Key है: AB+ = AB -> CD => C,D -> E (C->E) -> P (DE->P) -> G (B->G) = ABCDEPG सब।
अब B -> G देखो: B Key का हिस्सा है (AB का), G Non-Prime है। Proper Subset of Key -> Non-Prime = Partial Dependency = 2NF Fail।
इसलिए Not in 2NF।
1-Line Trick: B -> G, B Key का हिस्सा है – Partial Dependency = 2NF Fail।
2.31 [GATE 2006 : 2 Marks]
Question:
Consider the following relational schemes for a library database:
Book (Title, Author, Catalog_no, Publisher, Year, price)
Collection (Title, Author, Catalog_no)
with the following functional dependencies:
I. Title Author -> Catalog_no
II. Catalog_no -> Title Author Publisher Year
III. Publisher Title Year -> Price
Assume {Author, Title} is the key for both schemes: which of the following statements is true?
(a) Both Book and Collection are in BCNF
(b) Both Book and Collection are in 3NF only
(c) Book is in 2NF and Collection is in 3NF
(d) Both Book and Collection are in 2NF only
Answer: (c) Book is in 2NF and Collection is in 3NF [Collection is actually BCNF]
Solution:
Collection: FDs Title Author -> Catalog_no, Catalog_no -> Title Author। दो Keys हैं: {Title,Author} और {Catalog_no}। सारे LHS SuperKey हैं तो BCNF है, जो 3NF भी है।
Book: Catalog_no -> Publisher Year Transitive है (Title Author -> Catalog_no -> Publisher Year)। Publisher Year Non-Prime है, इसलिए 3NF Fail। Partial नहीं है क्योंकि Key {Title,Author} 2 attributes का है और Price -> Publisher Title Year में Title Year Key का हिस्सा है, तो 2NF Pass।
1-Line Trick: Collection में सारे LHS Key हैं = BCNF, Book में Catalog_no -> Publisher = Transitive = 3NF Fail।
2.32 [GATE 2008 : 2 Marks]
Question:
The following functional dependencies hold for relations R(A, B, C) and S(B, D, E): B -> A, A -> C. The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join R ⋈ S?
(a) 100 (b) 200 (c) 300 (d) 2000
Answer: (a) 100
Solution:
R में B -> A और A -> C है तो B Key है R का। R में 200 Distinct B होंगे।
S में 100 Tuples हैं। Natural Join B पर होगा।
Max तब होगा जब S के सारे 100 B, R के B से Match कर जाएं। R का B Unique है तो एक S का Tuple R के सिर्फ एक Tuple से Join होगा। तो Max = 100।
1-Line Trick: R में B Key है = Unique, इसलिए Join Max = S की Size = 100।
2.33 [GATE 2010 : 2 Marks]
Question:
Consider a relational table with a single record from each registered student with the following attributes.
- Registration_Num: Unique registration number of each registered student
- UID: Unique identity number, unique at the national level for each citizen
- BankAccount_Num: Unique account number at the bank. A student can have multiple accounts and joint accounts. This attribute stores the primary account number
- Name: Name of the student
- Hostel_Room: Room number of the hostel
Which of the following options is INCORRECT?
Answer: [Full options कटे हैं – Standard Answer: Hostel_Room -> Registration_Num is incorrect]
Solution:
Registration_Num Unique है तो Candidate Key है। UID भी Unique है तो वो भी Candidate Key।
BankAccount_Num Unique है पर एक Student के Multiple हो सकते हैं, पर Primary Account Unique है तो वो भी Key बन सकता है।
Hostel_Room से Registration_Num Determine नहीं होता क्योंकि एक Room में Multiple Students रह सकते हैं। इसलिए Hostel_Room -> Registration_Num False है।
1-Line Trick: जो Unique है वही Key है, Room से Student नहीं निकलता।
2.34 [GATE 2012 : 1 Mark]
Question:
Which of the following is TRUE?
(a) Every relation in 3NF is also in BCNF
(b) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
(c) Every relation in BCNF is also in 3NF
(d) No relation can be in both BCNF and 3NF
Answer: (c) Every relation in BCNF is also in 3NF
Solution:
BCNF, 3NF से Strict है। Hierarchy: 1NF < 2NF < 3NF < BCNF. (a) False – 3NF, BCNF में होना जरूरी नहीं। Example: R(A,B,C) with AB->C, C->A
(b) False – ये Definition 2NF की है, 3NF की नहीं।
(c) True – BCNF में होने के लिए पहले 3NF में होना जरूरी है।
(d) False – BCNF की हर relation 3NF में भी होती है।
1-Line Trick: BCNF में हो तो 3NF तो पक्का है, उल्टा जरूरी नहीं।
2.35 & 2.36 Linked [GATE 2013 : 2 Marks]
Common Data: Relation R has eight attributes ABCDEFGH. Fields contain only atomic values. F = {CH->G, A->BC, B->CFH, E->A, F->EG} is a set of FDs so that F+ is exactly the set of FDs that hold for R.
2.35 How many candidate keys does the relation R have?
(a) 3 (b) 4 (c) 5 (d) 6
Answer: (b) 4
Solution:
D किसी भी FD के RHS में नहीं है, और LHS में भी नहीं है। तो D हर Key में आएगा।
A+ = ABC…F…G,H = ABCDEFGH – D = सब except D
B+ = BCFH + EG + A + BC = ABCDEFGH – D
E+ = E + A + BC + CFH + EG = ABCDEFGH – D
F+ = F + EG + A + BC + CFH = ABCDEFGH – D
तो AD, BD, ED, FD ये 4 Candidate Keys हैं।
1-Line Trick: जो Attribute कभी RHS में ना आए, वो हर Key में – यहाँ D, और A,B,E,F से बाकी सब आ जाता है।
2.36 The relation R is
(a) in 1NF, but not in 2NF
(b) in 2NF, but not in 3NF
(c) in 3NF, but not in BCNF
(d) in BCNF
Answer: (a) in 1NF, but not in 2NF
Solution:
Keys = AD, BD, ED, FD. Non-Prime = B,C,G,H? Wait check: Prime Attributes = A,B,D,E,F. Non-Prime = C,G,H.
FD A->BC में A, Key AD का हिस्सा है (Partial Dependency), B Prime है लेकिन C Non-Prime है। Non-Prime C, Key के हिस्से A पर Depend कर रहा है।
Partial Dependency होते ही 2NF Fail। Atomic है तो 1NF में है।
1-Line Trick: Key का हिस्सा -> Non-Prime = Partial = 2NF Fail.
2.37 [GATE 2014 Set-1 : 1 Mark]
Question:
Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {EF->G, F->I,J, EH->K,L, K->M, L->N} on R. What is the key for R?
(a) {E,F} (b) {E,F,H} (c) {E,F,H,K,L} (d) {E}
Answer: (b) {E,F,H}
Solution:
E, F, H कभी भी RHS में नहीं आते, तो ये हर Key में होंगे।
EFH+ = EFH + G(EF->G) + I,J(F->I,J) + K,L(EH->K,L) + M(K->M) + N(L->N) = EFGHIJKLMN = सब आ गया।
EF से H नहीं आएगा, तो {E,F} Key नहीं है। {E,F,H} ही Minimal है।
1-Line Trick: जो RHS में कभी ना आए, वो Key में पक्का।
2.38 [GATE 2014 Set-1 : 1 Mark]
Question:
Given the following statements:
S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.
S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
CREATE TABLE S(a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY(d), FOREIGN KEY(a) references R)
Which one of the following statements is CORRECT?
(a) S1 is TRUE and S2 is FALSE
(b) Both S1 and S2 are TRUE
(c) S1 is FALSE and S2 is TRUE
(d) Both S1 and S2 are FALSE
Answer: (d) Both S1 and S2 are FALSE
Solution:
S1 FALSE: Foreign Key में Referential Integrity और Cascade Actions होते हैं, जो Simple Check Assertion से Replace नहीं हो सकते।
S2 FALSE: R का Primary Key (a,b) Composite है। S में Foreign Key सिर्फ (a) को Refer कर रहा है, जो R के PK से Match नहीं करता। FK को पूरे PK (a,b) को Refer करना चाहिए। इसलिए Invalid है।
1-Line Trick: FK = Composite PK का पूरा Reference चाहिए, आधा नहीं चलेगा।
2.39 [GATE 2014 Set-1 : 1 Mark]
Question:
Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies AB->C, D->E, AB->E, E->C.
Which one of the following is CORRECT?
(a) S1 is TRUE and S2 is FALSE
(b) Both S1 and S2 are TRUE
(c) S1 is FALSE and S2 is TRUE
(d) Both S1 and S2 are FALSE
Answer: (a) S1 is TRUE and S2 is FALSE
Solution:
S1 TRUE: 2 Attributes वाली हर Relation BCNF में होती है, क्योंकि या तो एक Attribute Key होगा या दोनों मिलकर Key होंगे, कोई Non-Trivial Partial/Transitive नहीं बनता।
S2 FALSE: AB->E को AB->C और E->C से Derive नहीं किया जा सकता। Actually E->C है, AB->E से AB->C आता है, तो AB->C redundant नहीं है। Minimal Cover में AB->C की जगह AB->E होना चाहिए था, इसलिए दिया गया Cover Minimal नहीं है। GATE के अनुसार S2 FALSE है।
1-Line Trick: 2-Attribute Relation = Automatic BCNF।
2.40 [GATE 2014 Set-2 : 1 Mark]
Question:
The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is __?
Answer: 8
Solution:
E Key है, तो Superkey वो होगा जिसमें E हो।
Total Attributes = 4 (E,F,G,H). बाकी 3 Attributes (F,G,H) का कोई भी Subset E के साथ मिलकर Superkey बना सकता है।
Number = 2^(n-1) = 2^(4-1) = 2^3 = 8.
Superkeys: E, EF, EG, EH, EFG, EFH, EGH, EFGH.
1-Line Trick: Key वाले Relation में Superkeys = 2^(Total-1).
2.41 [GATE 2014 Set-2 : 1 Mark]
Question:
Given an instance of the STUDENTS relation:
StudentID | StudentName | StudentEmail | StudentAge | CPI
2345 Shankar shankar@math X 9.4
1287 Swati swati@ee 18 9.5
7863 Shankar shankar@cse 19 9.6
9870 Swati swati@mech 18 9.3
8765 Ganesh ganesh@civil 19 8.7
For (StudentName, StudentAge) to be a key for this instance, the value X should NOT be equal to?
Answer: 19
Solution:
Key का मतलब (StudentName, StudentAge) का Pair Unique होना चाहिए।
Table में Shankar नाम से 2 Rows हैं: एक में Age = 19 (7863), दूसरी में Age = X (2345).
अगर X = 19 कर दिया तो (Shankar, 19) दो बार हो जाएगा, Duplicate Key बन जाएगा।
इसलिए X, 19 नहीं होना चाहिए।
1-Line Trick: Same Name वाले दो Rows की Age Same नहीं होनी चाहिए।
2.42 [GATE 2014 Set-3 : 1 Mark]
Question:
A prime attribute of a relation scheme R is an attribute that appears
(a) in all candidate keys of R
(b) in some candidate key of R
(c) in a foreign key of R
(d) only in the primary key of R
Answer: (b) in some candidate key of R
Solution:
Prime Attribute की Definition ही है – वो Attribute जो किसी भी Candidate Key का हिस्सा हो। सारे Keys में होना जरूरी नहीं, किसी एक में भी हो तो Prime है।
1-Line Trick: Prime = Candidate Key का हिस्सा।
2.43 [GATE 2015 Set-3 : 1 Mark]
Question:
Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies
F = { P,R -> S,T ; P,S,U -> Q,R }
Which of the following is the trivial functional dependency in F+ (closure of F)?
(a) {P,R} -> {S,T}
(b) {P,R} -> {R,T}
(c) {P,S} -> {S}
(d) {P,S,U} -> {Q}
Answer: (c) {P,S} -> {S}
Solution:
Trivial FD का Rule है – RHS, LHS का Subset होना चाहिए।
(a) {S,T} subset नहीं है {P,R} का – Non-Trivial
(b) {R,T} में R तो है पर T नहीं है – Non-Trivial
(c) {S} subset है {P,S} का – Trivial, और हर Trivial FD F+ में होती है।
(d) {Q} subset नहीं है {P,S,U} का – Non-Trivial
इसलिए सिर्फ (c) Trivial है।
1-Line Trick: RHS, LHS में ही हो तो Trivial – {P,S}->{S}.
2.44 [GATE 2016 Set-1 : 1 Mark]
Question:
Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y?
(a) V W X Y Z
(b) V W X Z
(c) V W X Y
(d) V W X Y Z (duplicate)
Answer: (b) V W X Z
Solution:
Primary Key VY है, तो Superkey बनने के लिए VY दोनों होना जरूरी है।
V W X Y Z में VY है – Superkey है।
V W X Z में Y ही नहीं है – VY पूरा नहीं है, इसलिए Superkey नहीं है।
V W X Y में VY है – Superkey है।
1-Line Trick: Superkey में Primary Key पूरा होना चाहिए – Y गायब है तो Fail।
2.45 [GATE 2016 Set-1 : 1 Mark]
Question:
A database of research articles in a journal uses the following schema.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)
The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER) -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE
The database is redesigned to use the following schemas.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)
Which is the weakest normal form that the new database satisfies, but the old one does not?
(a) 1NF (b) 2NF (c) 3NF (d) BCNF
Answer: (b) 2NF
Solution:
Old Schema: Key = 4 columns। (VOLUME,NUMBER) -> YEAR में LHS Key का हिस्सा है (Partial Dependency) – Non-Prime YEAR को Determine कर रहा है। इसलिए Old सिर्फ 1NF में है।
New Schema: Table1 में Key वही 4 columns, TITLE, PRICE पूरी Key पर Depend है – कोई Partial नहीं, BCNF। Table2 में (VOLUME,NUMBER) -> YEAR, LHS पूरा Key है – BCNF।
तो New दोनों Tables BCNF में हैं, Old 1NF में था। Weakest जो New Satisfy करता है पर Old नहीं – 2NF।
1-Line Trick: (VOLUME,NUMBER)->YEAR = Partial = Old 1NF, New में अलग Table बनते ही BCNF।
2.46 [GATE 2016 Set-1 : 1 Mark]
Question:
The following functional dependencies hold true for the relational schema R(V, W, X, Y, Z):
V -> W
VW -> X
Y -> VX
Y -> Z
Which of the following is irreducible equivalent (Minimal Cover) for this set?
Answer: V->W, V->X, Y->V, Y->Z
Solution:
Step1: Y->VX को तोड़ो => Y->V, Y->X
अब Set = {V->W, VW->X, Y->V, Y->X, Y->Z}
Step2: VW->X को देखो – V->W है तो V -> VW, फिर VW->X से V->X मिलता है, इसलिए VW->X को V->X से Replace कर सकते हैं।
Set = {V->W, V->X, Y->V, Y->X, Y->Z}
Step3: Y->X Redundant है क्योंकि Y->V और V->X से Y->X आ जाता है।
Final Minimal Cover = {V->W, V->X, Y->V, Y->Z}
1-Line Trick: VW->X = V->X और Y->X = Y->V + V->X से Redundant।
2.47 [GATE 2017 Set-2 : 1 Mark]
Question:
Consider the following tables T1 and T2.
T1(P,Q): (2,2),(3,8),(5,3),(6,3),(6,8),(9,8)
T2(R,S): (8,2),(8,3),(9,3),(5,7),(7,2)
In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with on-delete set NULL and on-update cascade. In order to delete record (3,8) from table T1, the number of additional records that need to be deleted from table T1 is __?
[Original GATE Question is to delete (8,3) from T2]
Answer: If you delete (3,8) from T1, 0 additional. If you delete (8,3) from T2 (Original GATE), 3 additional from T1.
Solution:
Case 1 – Delete (3,8) from T1: P=3 है। T2 में S=3 वाले 2 Records हैं (8,3) और (9,3)। उनका On-Delete SET NULL है, तो वो Delete नहीं होंगे, NULL हो जाएंगे। T1 में और कोई Record P=3 पर Depend नहीं करता। तो Additional 0।
Case 2 – Original GATE (Delete R=8 from T2): T2 से R=8 Delete करने पर T1 में Q=8 वाले 3 Records (3,8),(6,8),(9,8) Cascade से Delete होंगे। उनमें से P=3 वाले को T2 में 2 Records Reference करते हैं, पर वो SET NULL हो जाएंगे। तो T1 से Total Additional 3 Records Delete होंगे।
1-Line Trick: T1(Q)->T2(R) Cascade = T2 Delete तो T1 Delete, T2(S)->T1(P) SET NULL = T1 Delete तो T2 NULL।
2.48 [GATE 2017 : 2 Marks]
Question:
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.
Schema I: Registration(rollno, courses) Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for. Non-trivial FD: rollno -> courses
Schema II: Registration(rollno, courseid, email) Non-trivial FDs: rollno,courseid -> email, email -> rollno
Answer: Schema I – Not even in 1NF, Schema II – In 3NF but not in BCNF
Solution:
Schema I: ‘courses’ Set-Valued है, मतलब Atomic Value नहीं है। 1NF का पहला Rule ही है – हर Attribute Atomic होना चाहिए। इसलिए ये 1NF में भी नहीं है।
Schema II: Keys निकालो – rollno,courseid Key है, email->rollno से email,courseid भी Key है। Prime Attributes = rollno, courseid, email सब Prime हैं।
FD email->rollno में LHS email Superkey नहीं है (email अकेला Key नहीं), लेकिन RHS rollno Prime है। इसलिए 3NF Pass, BCNF Fail।
1-Line Trick: Set-Valued = 1NF Fail, email->rollno Prime है तो 3NF Pass BCNF Fail।
2.48 (Continued) [GATE 2018 : 2 Marks]
Question:
Schema III: Registration (rollno, courseid, marks, grade) Non-trivial FDs: rollno, courseid -> marks, grade ; marks -> grade
Schema IV: Registration (rollno, courseid, credit) Non-trivial FDs: rollno, courseid -> credit ; courseid -> credit
Which one of the relational schemas above is in 3NF but not in BCNF?
(a) Schema I (b) Schema II (c) Schema III (d) Schema IV
Answer: (b) Schema II
Solution:
Schema I: courses Set-Valued है = Not even 1NF.
Schema II: Keys = {rollno,courseid} और {email,courseid}. FD email->rollno में LHS Superkey नहीं लेकिन RHS rollno Prime है, तो 3NF Pass, BCNF Fail = 3NF but not BCNF.
Schema III: rollno,courseid -> marks,grade Key है। marks->grade में marks Non-Key, grade Non-Prime = Transitive = 3NF Fail, सिर्फ 2NF।
Schema IV: courseid -> credit में courseid Key का हिस्सा है, credit Non-Prime = Partial = 2NF Fail, सिर्फ 1NF।
1-Line Trick: II में RHS Prime है = 3NF Pass, BCNF Fail।
2.49 [GATE 2019 : 2 Marks]
Question:
Let the set of functional dependencies F = {QR -> S, R -> P, S -> Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z where Y = (PR) and Z = (QRS). Consider the two statements given below:
I. Both Y and Z are in BCNF
II. Decomposition of X into Y and Z is dependency preserving and lossless
Which of the above statements is/are correct?
(a) I only (b) Neither I nor II (c) Both I and II (d) II only
Answer: (d) II only
Solution:
FDs: QR->S, R->P, S->Q
Key निकालो: QR+ = QRS + P(R->P) = QRSP = सारा X। तो QR Key है।
I Check:
Y = (PR): FD R->P है। R, Y = PR में Key है? R+ = RP = PR, तो R Superkey है Y का। Y BCNF में है।
Z = (QRS): FDs QR->S और S->Q। QR तो Z में Key है (QR+ = QRS), लेकिन S->Q में S Key नहीं है Z का (S+ = SQ only)। इसलिए Z BCNF में नहीं है, 3NF में है। तो I False।
II Check:
Lossless: Y ∩ Z = R, Y = PR, Z = QRS, Common = R, R->P है तो R, Y का Superkey है – Lossless True।
Dependency Preserving: R->P Y में है, S->Q Z में है, QR->S Z में है (QRS में)। सारी FDs Preserve हो रही हैं – Dependency Preserving True।
तो II True।
1-Line Trick: Z में S->Q है और S Key नहीं है = BCNF Fail, पर Lossless + Dependency दोनों Pass।
2.50 [GATE 2019 : 2 Marks]
Question:
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
(a) R has a non-trivial functional dependency X -> A, where X is not a superkey and A is a prime attribute.
(b) R has a non-trivial functional dependency X -> A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key.
(c) R has a non-trivial functional dependency X -> A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.
(d) …
Answer: (a) R has a non-trivial FD X -> A, where X is not a superkey and A is a prime attribute.
Solution:
3NF का Definition: हर FD X->A में या तो X Superkey हो, या A Prime हो।
BCNF का Definition: हर FD X->A में X Superkey होना ही चाहिए।
तो जो Relation 3NF में है पर BCNF में नहीं, उसमें ऐसा FD जरूर होगा जहाँ X Superkey नहीं है, पर 3NF Pass करने के लिए A Prime होगा।
अगर A Non-Prime होता और X Superkey नहीं होता तो 3NF भी Fail हो जाता।
1-Line Trick: 3NF Pass + BCNF Fail = LHS Non-Superkey + RHS Prime वाला FD होना ही चाहिए।