GATE CSE 1998 | Question: 1.34

Question

Which normal form is considered adequate for normal relational database design?

• A. $2NF$
• B. $5NF$
• C. $4NF$
• D. $3NF$

Comments

in the question "adequate normalization method" is asked for real database design ,not " which is best one". i think "adequate means "not more and not less but sufficient"

even though BCNF will have no redundancy while 3NF will have some . but still 3NF is adequate for database design because of following reasons

1) BCNF may cause "loss of dependency"

2) to obtain BCNF , we may require to split relation into smaller one and its number can be huge in large database .which leads to  complex maintenance .

while in 3NF  , even though some redundancies are there but it is better to have some redundancies than having above two problems of BCNF

---

3NF removes all the anomalies.

So 3Nf is considered  adequate for relational database dedign.

But then also BCNF is more better than 3NF. But here we are asked adequate one so 3NF is adequate

Answer 1

BCNF doesn't guarantee dependency preservation.

Comments

"1st priority is Non Redundancy". reference ??

---

respected sir ,

here is the video i saw it in neptel heard. this topic is covered at 30.02 minutes.plz clarify my doubt if i misunderstood it. here at a point the professor says we thought of decomposing due to redundancy

---

@ANI  We want to reduce redundancy, but lossless join and dependency preservation are more important. Why we reduce redundancy? To save space and avoid anomaly. But if our database becomes inconsistent in this, it will be of no use. Suppose you save 1 GB space but you lose thousands of customer informmation. What will you prefer? Information r8?

Answer 2

Answer- Option D BCNF

Comments

Yes, i got the same answer because redundancy is less in BCNF compare to other Normal forms. But Given answer is 3NF.

---

The answer is 3NF. As we cannot have a lossless join always to BCNF.

Answer 3

There is not always a decomposition into BCNF i.e lossless and dependency preserving. So 3 NF.

Answer 4

Ans: D

Comments

But why not BCNF, sure 3NF is dependency preserving. BCNF is less redundant compare to 3NF. Size of DB is an concern.

---

3NF is always lossless and dependency preserving but BCNF is always lossless only. Dependency preserving is not guaranteed in BCNF

---

Why we want the dependency preserving property in our database ,can anyone explain it ?

iknow that bcnf is less redundant then 3nf that's why i am thinking that bcnf should be optimal choice

---

@ Shivani gaikawad

The reason we want to preserve dependencies, is that each dependency represents a constraint on the database, and this constraint must be obeyed by the data in the DB. When a relation is decomposed, if some dependency is not preserved, then to check whether the data follows that lost constraint or not, we need to perform a join on the sub-relations to get back the original relation, just so that we can check if the constraint is satisfied.

So if we ensure that dependencies are preserved, then we don't need to perform a join to check constraints.

---

@SPluto thanks your explanation cleared my doubts

---

very nice @SPluto

So even after decomposition database Relation meaning doesn't change, i.e. NO CHANGES IN ORIGINAL CONSTRAINTS. 

that's thing which i liked in GATE OVERFLOW