Database Normal Forms Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What condition must be satisfied for a relation schema R to be in second normal form (2NF)?

All attributes should be dependent on multiple candidate keys.

All attributes must depend on the whole key. (correct)

Every non-prime attribute must be indirectly dependent on the primary key.

All attributes must depend only on the primary key.

Which statement correctly defines a relation schema in third normal form (3NF)?

Every non-prime attribute is fully functionally dependent and nontransitively dependent on every key. (correct)

All non-prime attributes need to be dependent solely on the primary key.

Every non-prime attribute is fully functionally dependent and transitively dependent on every key.

All attributes must depend on both the key and an additional non-prime attribute.

What characterizes a relation in Boyce-Codd Normal Form (BCNF)?

Every nontrivial, left-irreducible functional dependency must have a candidate key as its determinant. (correct)

Only trivial functional dependencies are allowed.

Every functional dependency must have a non-prime attribute on the left-hand side.

All attributes must depend exclusively on the primary key.

How can a relation schema achieve second normal form (2NF) from a non-2NF state?

By decomposing it to eliminate partial dependencies of non-prime attributes. Signup and view all the answers

Which of the following accurately describes a trivial functional dependency?

A dependency where the right-hand side is a subset of the left-hand side. Signup and view all the answers

What is required for a relation schema to be considered in third normal form (3NF)?

Every non-prime attribute must be dependent on a candidate key. Signup and view all the answers

In terms of normal forms, which of the following is true?

There exist relations that are in 3NF but not in BCNF. Signup and view all the answers

Which functional dependency (FD) indicates a candidate key in the TEACH relation?

{ student, course } → { instructor } Signup and view all the answers

What happens if a relation does not satisfy BCNF but is in 3NF?

It can still have transitive dependencies. Signup and view all the answers

Which decomposition maintains the lossless join property while sacrificing functional dependency preservation in the TEACH relation?

{ instructor, course } and { instructor, student } Signup and view all the answers

What defines a prime attribute in the context of database normalization?

An attribute that is part of a candidate key. Signup and view all the answers

What is the purpose of Boyce-Codd Normal Form (BCNF)?

To ensure every determinant is a candidate key. Signup and view all the answers

Which statement is true about the relationship between 2NF, 3NF, and BCNF?

A relation in BCNF is also in 3NF. Signup and view all the answers

What defines a relation to be in Fourth Normal Form (4NF)?

Whenever there is a multivalued dependency, all attributes must also be functionally dependent on the determinant. Signup and view all the answers

Which statement is true about Fagin’s Theorem?

R(A, B, C) can only be decomposed if A —>> B|C holds. Signup and view all the answers

In the CTL relation example, what does the multivalued dependency imply?

Courses determine multiple tutors. Signup and view all the answers

What occurs when you join the two attribute projections CT and TL from the CTL relation?

You lose some information about the original relation. Signup and view all the answers

Which of the following relations can be considered a projection of CTL?

Courses and Levels. Signup and view all the answers

What is a major characteristic of a 3-decomposable relation?

It has exactly three distinct attributes. Signup and view all the answers

In the EMP relation, what do the multivalued dependencies ENAME —>> PNAME and ENAME —>> DNAME suggest?

Projects and departments can exist independently of each employee. Signup and view all the answers

Which of these options best describes a projection in database terms?

A selection of some attributes from a relation. Signup and view all the answers

What does it mean for a relation R to be nonloss-decomposed?

It can retain all original information upon joining projections. Signup and view all the answers

Which of these attributes must be functionally dependent on A for R to meet 4NF requirements?

All attributes of R, denoted as x. Signup and view all the answers

What is required for a relation schema to be in fifth normal form (5NF)?

Every join dependency in R must be implied by the candidate keys of R. Signup and view all the answers

Which of the following correctly describes a join dependency (JD)?

It specifies constraints on the states of R, requiring a nonadditive join decomposition. Signup and view all the answers

What is the main purpose of Domain-Key Normal Form (DKNF)?

To allow constraints to be enforced using only domain and key constraints. Signup and view all the answers

Which of the following statements about join dependencies is false?

Join dependencies are easily detectable in practice. Signup and view all the answers

What is a characteristic of a relation in DKNF?

All constraints must be logical consequences of domain and key constraints. Signup and view all the answers

Given the relation schema R(A,B,C,D,E) with the functional dependencies A→B, BC→E, and D→A, what is the key for R?

CD Signup and view all the answers

Why is normalization into 5NF rarely done in practice?

Join dependencies are not commonly encountered. Signup and view all the answers

In the context of the given functional dependencies, which dependency is involved in defining the implications of the keys?

BC→E Signup and view all the answers

Which functional dependency establishes the key for the relation R(A,B,C,D,E)?

C is the key Signup and view all the answers

In the CARSALE relation, what functional dependency indicates a non-prime attribute?

date_sold → discount Signup and view all the answers

What is the highest normal form that the CARSALE relation is in before decomposition?

1NF Signup and view all the answers

After decomposing CARSALE based on functional dependencies, which sets of attributes belong to R2?

salesman#, commission% Signup and view all the answers

In the context of the relation TRIP, which statement is true regarding the functional dependencies?

trip_id → startdate Signup and view all the answers

Which of the following functional dependencies indicates a multi-valued dependency in the TRIP relation?

trip_id →→ cities_visited Signup and view all the answers

How is the relation R = {A, B, C, D, E, H} classified based on the functional dependencies given?

In 3NF but not BCNF Signup and view all the answers

What is the correct decomposition for R1 based on the dependencies for relation R?

R1(A,B,C) Signup and view all the answers

Study Notes

Normal Forms

1NF: All attributes depend on the key.
2NF: All attributes depend on the whole key.
3NF: All attributes depend on nothing but the key.

Second Normal Form

2NF is defined for relations with multiple candidate keys.
2NF requires that every non-prime attribute is fully functionally dependent on every key of the relation.
A relation is in 2NF if every non-prime attribute is not partially dependent on any key.

Third Normal Form

3NF is also defined for relations with multiple candidate keys.
A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on any key.
A non-prime attribute is transitively dependent on a key if it depends on another non-prime attribute that itself depends on the key.

Boyce-Codd Normal Form

BCNF is a stricter form of normalization than 3NF.
BCNF requires that every determinant of a nontrivial, left-irreducible functional dependency must be a candidate key.
BCNF ensures that every functional dependency is based on a candidate key.

Fourth Normal Form

4NF addresses multi-valued dependencies (MVDs).
A relation is in 4NF if it is in 3NF and every MVD has a determinant that is also a candidate key.
4NF ensures that a relation is free from redundant multi-valued dependencies.

Fifth Normal Form

5NF addresses join dependencies.
A relation is in 5NF if it is in 4NF and every join dependency is implied by the candidate keys of the relation.
5NF ensures that a relation is free from redundant join dependencies.

Domain-Key Normal Form

DKNF is a stricter form of normalization than 5NF.
DKNF ensures that all constraints on the relation are logical consequences of the definition of keys and domains.
DKNF is not always achievable.

Example Problems

Problem 1: Consider a relation R(A,B,C,D,E) with functional dependencies: A→B, BC→E, and D→A. The key for R is CD.
Problem 2: Consider a relation R(A,B,C,D,E) with functional dependencies: A→D, C→AB, and DB→E. The key for R is C.
Problem 3: Consider the relation CARSALE(car#, date_sold, salesman#, commission%, discount). The relation is in 1NF but not in 2NF or 3NF. Normalization can be achieved by creating three relations:
- R1(car#, date_sold, salesman#)
- R2(salesman#, commission%)
- R3(date_sold, discount)
Problem 4: Given a relation R = {A, B, C, D, E, H} with functional dependencies F = {{A → BC}, {CD → E}, {E → C},{D → A, E, H}, {A, B, H → B, D},{D, H → B, C}}. The key for R is D.
Problem 5: The relation TRIP (trip_id, startdate, cities_visited, cards_used) has functional dependencies: trip_id → startdate and multi-valued dependencies: trip_id →→ cities_visited and trip_id →→ cards_used. Normalization requires three relations:
- R1(trip_id, startdate)
- R2(trip_id, cities_visited)
- R3(trip_id, cards_used)
Problem 6: A 3-decomposable relation is a relation where every tuple can be reconstructed from combining any two projections of the relation.
Problem 7: A join dependency specifies a constraint stating that every legal state of a relation should have a non-additive join decomposition into its projections.
Problem 8: A join dependency is a generalization of a multi-valued dependency.

Normalization in Practice

Normalization beyond 3NF is rarely done in practice.
5NF is often difficult to detect and may not be achievable for a given relation.
DKNF is optimal in terms of normalization but is not achievable for all relations.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your understanding of database normalization with this quiz focused on Normal Forms such as 1NF, 2NF, 3NF, and BCNF. Learn how these forms affect relational database design and ensure data integrity. Challenge yourself with questions that delve into the dependencies of attributes on keys.