Logical Database Design and Normalization

Introduction

Logical database design transforms a conceptual model—typically an entity–relationship (ER) model—into relational schemas that can be implemented in a database management system. A good logical design represents business semantics accurately while minimizing redundancy, update anomalies and unintended information loss.

Normalization provides a formal basis for improving relational designs. It uses dependencies among attributes to determine whether a relation should be decomposed. A successful decomposition should ideally be lossless, so that the original relation can be reconstructed exactly, and dependency preserving, so that important constraints can be enforced without joining relations.

Normalization is not merely a mechanical process. Functional, multivalued and join dependencies must be derived from verified business rules rather than inferred from coincidental patterns in sample data.

Learning Objectives

After studying this chapter, students should be able to:

• transform conceptual models into relational schemas;
• identify superkeys, candidate keys and prime attributes;
• analyze functional, multivalued and join dependencies;
• compute attribute closures and minimal covers;
• identify partial and transitive dependencies;
• apply 1NF, 2NF, 3NF, BCNF, 4NF and 5NF;
• test decompositions for lossless join and dependency preservation;
• use 3NF synthesis and BCNF decomposition methods;
• evaluate normalization decisions in operational and AI-oriented systems.

1. From Conceptual Models to Relational Schemas

A conceptual model describes entities, relationships and constraints independently of a particular relational implementation. Logical design translates these concepts into relations, primary keys, foreign keys and integrity constraints.

1.1 Mapping entities and attributes

A regular entity type usually becomes one relation. Its simple attributes become columns, and its identifier becomes a candidate key. One candidate key is selected as the primary key.

For example:

`STUDENT(StudentID, Name, Email)`

A composite attribute is normally divided into its components. An address might therefore be stored as Street, City and PostalCode rather than as a single structure when the components must be searched or validated independently.

A derived attribute, such as Age calculated from DateOfBirth, is normally not stored unless performance, historical or auditing requirements justify materialization.

A multivalued attribute requires a separate relation. If a student may have several telephone numbers, the mapping is:

• `STUDENT(StudentID, Name)`
• `STUDENT_PHONE(StudentID, PhoneNumber)`

The key of STUDENT_PHONE is commonly `(StudentID, PhoneNumber)`.

1.2 Mapping relationship types

For a one-to-many relationship, the primary key of the relation on the “one” side is placed as a foreign key in the relation on the “many” side. If one department employs many lecturers:

• `DEPARTMENT(DepartmentID, DepartmentName)`
• `LECTURER(LecturerID, Name, DepartmentID)`

A one-to-one relationship may be implemented by placing a foreign key in either relation, preferably on the side with total participation. A uniqueness constraint is required to preserve the one-to-one cardinality.

A many-to-many relationship becomes an associative relation containing the keys of the participating entities and any attributes of the relationship:

• `STUDENT(StudentID, Name)`
• `COURSE(CourseID, Title)`
• `ENROLLMENT(StudentID, CourseID, Grade)`

An n-ary relationship is similarly mapped to a relation containing foreign keys to all participating entity relations. Its key depends on the relationship’s cardinality constraints and cannot be determined solely from its degree.

1.3 Weak entities and specialization

A weak entity is mapped to a relation containing its partial key and the owner entity’s key. The combined attributes normally form the weak entity’s primary key.

Specialization or inheritance can be mapped using several strategies:

1. one relation for the superclass and one for each subclass;

2. one relation for the entire hierarchy, with a type discriminator and potentially nullable columns;

3. one relation for each concrete subclass, duplicating inherited attributes.

The choice affects nullability, integrity enforcement, query complexity and support for overlapping subclasses.

2. Functional Dependencies and Keys

Let relation schema `R` contain a set of attributes. A functional dependency (FD) `X → Y` holds when any two valid tuples that agree on attribute set `X` must also agree on attribute set `Y`. The dependency expresses a semantic rule, not merely a pattern observed in current data.

For example:

`StudentID → StudentName, Email`

means that each StudentID identifies one student name and one email value under the assumed business rules.

2.1 Types of functional dependency

An FD `X → Y` is trivial if `Y ⊆ X`; otherwise, it is nontrivial.

An attribute set `X` is a superkey if `X → R`. A candidate key is a minimal superkey: no proper subset of it is also a superkey. An attribute belonging to at least one candidate key is a prime attribute.

A dependency `X → A` is a full functional dependency when no proper subset of `X` determines `A`. It is a partial dependency when some proper subset of `X` determines `A`.

A transitive dependency informally occurs when a key determines a non-key attribute through another determinant. For example:

`EmployeeID → DepartmentID`

`DepartmentID → DepartmentName`

implies `EmployeeID → DepartmentName` by transitivity.

2.2 Armstrong’s axioms

Armstrong’s axioms are sound and complete for reasoning about FDs:

• Reflexivity: if `Y ⊆ X`, then `X → Y`.
• Augmentation: if `X → Y`, then `XZ → YZ`.
• Transitivity: if `X → Y` and `Y → Z`, then `X → Z`.

Useful derived rules include union, decomposition and pseudotransitivity.

2.3 Attribute closure

The closure `X+` of an attribute set `X` under FD set `F` contains all attributes functionally determined by `X`.

A closure algorithm is:

1. Set `X+ = X`.

2. For each FD `Y → Z`, if `Y ⊆ X+`, add `Z` to `X+`.

3. Repeat until no attribute can be added.

Consider `R(A,B,C,D)` with:

`F = {A → B, B → C, AC → D}`.

Starting with `A+ = {A}`:

• `A → B` adds B;
• `B → C` adds C;
• `AC → D` now applies and adds D.

Thus `A+ = {A,B,C,D}`, so A is a candidate key. It is minimal because it has no proper nonempty subset.

3. Minimal Covers and Extraneous Attributes

A minimal cover, or canonical cover, is an equivalent FD set in which:

• every FD has one attribute on its right-hand side;
• no left-hand-side attribute is extraneous;
• no FD is redundant.

For an FD `X → A` with singleton right-hand side, an attribute `b ∈ X` is extraneous on the left if:

`A ∈ (X − {b})+`

where the closure is computed under the current FD set. It is not generally valid to remove `X → A` first and then use only the remaining dependencies for this test.

For example, let:

`F = {AB → C, A → B, B → D}`.

To determine whether B is extraneous in `AB → C`, compute `A+` under the current set F:

• A determines B;
• with A and B available, `AB → C` determines C;
• B also determines D.

Therefore `C ∈ A+`, so B is extraneous and `AB → C` can be replaced by `A → C`. If `AB → C` were incorrectly removed before this closure test, C would not be obtained and the extraneous attribute could be missed.

After simplifying left sides, a dependency `X → A` is redundant if `A ∈ X+` under the FD set with that entire dependency removed. Minimal-cover algorithms may produce different but equivalent covers depending on processing order.

4. Normal Forms Based on Functional Dependencies

4.1 First Normal Form

A relation is in first normal form (1NF) when each attribute value is a single value from its declared domain and repeating groups are absent. Atomicity is relative to the intended operations: a geographic point may be treated as one domain value in one design but separated into latitude and longitude in another.

A column containing comma-separated telephone numbers violates the conventional relational treatment because individual telephone numbers cannot be constrained and referenced cleanly.

4.2 Second Normal Form

A relation is in second normal form (2NF) if it is in 1NF and every nonprime attribute is fully functionally dependent on every candidate key. Thus no nonprime attribute may depend on only part of a composite candidate key.

Consider:

`ENROLLMENT(StudentID, CourseID, StudentName, CourseTitle, Grade)`

with:

• `StudentID → StudentName`
• `CourseID → CourseTitle`
• `(StudentID, CourseID) → Grade`

The candidate key is `(StudentID, CourseID)`. StudentName and CourseTitle depend on proper subsets of the key, so the relation violates 2NF. A suitable decomposition is:

• `STUDENT(StudentID, StudentName)`
• `COURSE(CourseID, CourseTitle)`
• `ENROLLMENT(StudentID, CourseID, Grade)`

4.3 Third Normal Form

A relation is in third normal form (3NF) if, for every nontrivial FD `X → A`, at least one of the following holds:

1. X is a superkey; or

2. A is a prime attribute.

Suppose:

`EMPLOYEE(EmployeeID, DepartmentID, DepartmentName)`

with `EmployeeID → DepartmentID` and `DepartmentID → DepartmentName`. EmployeeID is the key, but DepartmentID is not a superkey and DepartmentName is nonprime. The second FD violates 3NF.

Decompose into:

• `EMPLOYEE(EmployeeID, DepartmentID)`
• `DEPARTMENT(DepartmentID, DepartmentName)`

4.4 Boyce–Codd Normal Form

A relation is in Boyce–Codd normal form (BCNF) if, for every nontrivial FD `X → Y`, X is a superkey. BCNF is stricter than 3NF because it does not permit an FD merely because its right-hand side is prime.

Consider `R(A,B,C)` with:

`F = {AB → C, C → B}`.

The candidate keys are AB and AC. The relation is in 3NF: in `C → B`, B is prime. It is not in BCNF because C is not a superkey.

Decomposing into `R1(C,B)` and `R2(A,C)` produces BCNF relations. However, the dependency `AB → C` is not preserved directly: the projected dependencies include `C → B`, but they cannot derive C from AB without joining relations. This illustrates the potential trade-off between BCNF and dependency preservation.

5. Lossless and Dependency-Preserving Decomposition

5.1 Lossless join

A decomposition of R into schemas `R1, R2, ..., Rn` is lossless with respect to dependencies F if every legal instance r of R satisfies:

`r = πR1(r) ⋈ πR2(r) ⋈ ... ⋈ πRn(r)`.

A lossy decomposition can generate spurious tuples when the projections are joined.

For a binary decomposition of R into R1 and R2, the decomposition is lossless with respect to F if and only if at least one of the following is in `F+`:

• `(R1 ∩ R2) → R1`
• `(R1 ∩ R2) → R2`

This test is specific to binary decomposition. General decompositions can be tested using a chase or tableau procedure.

For example, let `R(A,B,C)` have `A → B` and decompose it into `R1(A,B)` and `R2(A,C)`. Their intersection is `{A}`. Since `A → AB`, the decomposition is lossless.

5.2 Dependency preservation

For each component Ri, the projection `πRi(F)` contains every FD in `F+` whose attributes are entirely contained in Ri. Let:

`G = πR1(F) ∪ πR2(F) ∪ ... ∪ πRn(F)`.

The decomposition is dependency preserving if every dependency in F follows from G; equivalently, `F ⊆ G+`.

#### Worked projection-and-closure example

Let:

`R(A,B,C)` and `F = {A → B, B → C}`.

Decompose R into:

• `R1(A,B)`
• `R2(A,C)`

The projection onto R1 contains `A → B`. The projection onto R2 contains `A → C`, even though it was not explicitly listed in F, because it follows from `A → B` and `B → C`.

Thus:

`G = {A → B, A → C}`.

Test the original dependencies using closure under G:

• `A+ = {A,B,C}`, so `A → B` is preserved.
• `B+ = {B}`, so `B → C` is not preserved.

Therefore the decomposition is not dependency preserving. It is nevertheless lossless because the intersection is A and `A → AB`.

By contrast, decomposing R into `R1(A,B)` and `R2(B,C)` yields projected dependencies `A → B` and `B → C`. Both original dependencies are preserved, and the decomposition is lossless because the intersection B determines `R2(B,C)`.

5.3 3NF synthesis

A standard 3NF synthesis procedure is:

1. Compute a minimal cover of F.

2. For each FD `X → A`, create a relation containing `X ∪ {A}`; dependencies with the same determinant may be grouped.

3. Remove a relation schema contained in another generated schema.

4. If no generated relation contains a candidate key of the original schema, add a relation containing one candidate key.

The resulting decomposition is dependency preserving, lossless and in 3NF. It may not be in BCNF.

5.4 BCNF decomposition

If an FD `X → Y` violates BCNF in R, decompose R into:

• `R1 = X ∪ Y`
• `R2 = R − (Y − X)`

The decomposition is lossless because the common attributes include X, which determines R1. The procedure is repeated until every component is in BCNF. Dependency preservation is not guaranteed.

6. Multivalued Dependencies and Fourth Normal Form

A multivalued dependency (MVD) `X ↠ Y` holds in R when, for each value of X, the set of Y values is independent of the remaining attributes `Z = R − (X ∪ Y)`. Formally, when two tuples agree on X, valid tuples must exist that exchange their Y and Z components while retaining X.

An MVD `X ↠ Y` is trivial if either:

• `Y ⊆ X`; or
• `X ∪ Y = R`.

Every FD `X → Y` implies the corresponding MVD `X ↠ Y`, but the converse does not generally hold.

A relation is in fourth normal form (4NF) if, for every nontrivial MVD `X ↠ Y`, X is a superkey.

Consider:

`STUDENT_INTEREST(StudentID, Hobby, Language)`.

Suppose a student’s hobbies and spoken languages are independent. Then:

`StudentID ↠ Hobby`

and

`StudentID ↠ Language`.

Storing both sets together creates every hobby–language combination and causes redundancy. The 4NF decomposition is:

• `STUDENT_HOBBY(StudentID, Hobby)`
• `STUDENT_LANGUAGE(StudentID, Language)`

This decomposition is lossless under the stated MVD.

7. Join Dependencies and Fifth Normal Form

A join dependency (JD) `*(R1, R2, ..., Rn)` on schema R requires every legal relation instance r to satisfy:

`r = πR1(r) ⋈ πR2(r) ⋈ ... ⋈ πRn(r)`.

The component schemas collectively cover R. A JD is trivial if at least one component schema Ri equals R. It is nontrivial when no component equals R. Whether a JD is implied by candidate keys is a separate question used in the definition of 5NF; it is not the definition of triviality.

An MVD is a special case of a join dependency. If `Z = R − (X ∪ Y)`, then `X ↠ Y` corresponds to the binary JD `*(XY, XZ)`.

A relation is in fifth normal form (5NF), also called project-join normal form, if every nontrivial join dependency that holds on the relation is implied by its candidate keys.

Consider `SUPPLY(Supplier, Part, Project)`. Suppose the business rule states that a supplier supplies a part to a project whenever all three pairwise associations are valid:

• the supplier can supply the part;
• the supplier is approved for the project;
• the project uses the part.

This rule establishes:

`*(SupplierPart, SupplierProject, PartProject)`.

All three component schemas are proper subsets of SUPPLY, so the JD is nontrivial. If it is not implied by candidate keys, SUPPLY violates 5NF and can be decomposed into the three pairwise relations.

Without the stated business rule, this decomposition would be unsafe: joining the pairwise relations could invent supplier–part–project combinations that were never authorized. Join dependencies must therefore be validated semantically.

Under conventional definitions, the hierarchy is:

`5NF ⇒ 4NF ⇒ BCNF ⇒ 3NF ⇒ 2NF ⇒ 1NF`.

The reverse implications do not generally hold.

8. Applied Case: Normalizing an AI Feature Registry

Background

This is an illustrative case. A machine-learning team stores feature observations and governance metadata in one relation:

`FEATURE_RECORD(RunID, EntityID, FeatureID, Value, FeatureName, Unit, OwnerID, OwnerEmail, Tag)`.

The organization establishes the following rules:

• `FeatureID → FeatureName, Unit, OwnerID`
• `OwnerID → OwnerEmail`
• `(RunID, EntityID, FeatureID) → Value`
• each feature may have several governance tags;
• tags are independent of observation records for runs and entities.

Problem

A feature name, unit and owner are repeated for every observation. Owner email addresses are also repeated for every feature belonging to that owner. If a feature has several tags, each observation is repeated once for every tag. This design produces update, insertion and deletion anomalies.

For example, changing an owner’s email requires updates to many rows. A new feature cannot be registered cleanly until an observation exists. Deleting the last observation could accidentally remove the only stored feature metadata.

Analysis

The observation identifier `(RunID, EntityID, FeatureID)` determines Value. Feature metadata depends only on FeatureID, causing partial dependency when Tag participates in the full row key. OwnerEmail depends transitively through OwnerID.

Because tags are independent of observation records, the relation also satisfies a nontrivial MVD corresponding to feature tags. Combining observations and tags violates 4NF.

Approach and solution

The schema is decomposed into:

• `FEATURE(FeatureID, FeatureName, Unit, OwnerID)`
• `OWNER(OwnerID, OwnerEmail)`
• `OBSERVATION(RunID, EntityID, FeatureID, Value)`
• `FEATURE_TAG(FeatureID, Tag)`

Primary keys are respectively FeatureID, OwnerID, `(RunID, EntityID, FeatureID)` and `(FeatureID, Tag)`. Foreign keys connect FEATURE to OWNER and connect OBSERVATION and FEATURE_TAG to FEATURE.

Each determinant is a key in its relation. The independent multivalued facts—observations and tags—are no longer combined. Appropriate domain constraints should also validate Unit, Value type, identifier format and permitted governance tags.

Outcome

The design removes repeated metadata, permits owners and features to exist before observations, and prevents tag changes from duplicating measurement data. It also supports governance queries such as identifying all features owned by a team or marked as containing personal information.

For production use, temporal requirements may require versioned relations. If a feature’s unit or owner can change over time, a simple FD such as `FeatureID → Unit` no longer describes all historical records. A version or validity interval must become part of the determinant.

Learning points

• Dependencies must reflect time scope and business meaning.
• 3NF or BCNF addresses functional redundancy, while 4NF addresses independent multivalued facts.
• Normalization improves lineage, ownership and access-control administration in AI systems.
• Keys, foreign keys and domain constraints remain necessary after decomposition.

9. Practical Challenges, Governance and Contemporary Practice

Normalization assumes that dependencies are known and stable. In practice, rules may change, data may be incomplete, and exceptions may be concealed in application code. Profiling data can suggest dependencies, but it cannot prove that they are valid business constraints.

Highly normalized schemas may require additional joins. Analytical warehouses therefore often use denormalized star schemas, materialized views or columnar representations for performance. Denormalization should be deliberate, documented and supported by mechanisms that prevent inconsistent copies.

Many SQL systems directly support primary keys, unique constraints, foreign keys and checks, but arbitrary cross-table dependencies may require transactions, triggers or application logic. Distributed databases introduce further trade-offs because joins and synchronous constraint enforcement may cross partitions.

Normalization also has governance consequences. Separating identity, ownership and sensitive attributes can support least-privilege access, data retention and auditability. However, decomposition alone does not guarantee privacy: joined data may still permit re-identification. Designers must incorporate legal purpose limitations, authorization, encryption, lineage and deletion obligations.

Recommended practice is to:

• derive dependencies from approved business rules;
• identify all candidate keys, not only the chosen primary key;
• test both losslessness and dependency preservation;
• normalize before considering performance-driven denormalization;
• document any constraints that the DBMS cannot enforce directly;
• include temporal scope in dependencies where facts change over time;
• test migrations and joins for missing or spurious tuples.

Chapter Summary

Logical database design maps conceptual entities, attributes and relationships into relational schemas with keys and integrity constraints. Functional dependencies formalize how determinants identify other attributes and support closure computation, key discovery and normalization.

1NF removes repeating groups, 2NF eliminates partial dependency of nonprime attributes on candidate keys, and 3NF controls dependencies whose determinants are not superkeys. BCNF strengthens this requirement by demanding that every nontrivial FD determinant be a superkey. Fourth normal form addresses nontrivial multivalued dependencies, while fifth normal form addresses nontrivial join dependencies not implied by candidate keys.

A decomposition should be lossless so that it does not create spurious information. Dependency preservation allows constraints to be checked without reconstructing the original relation. 3NF synthesis guarantees a lossless, dependency-preserving design, whereas BCNF decomposition guarantees losslessness but may sacrifice dependency preservation. Effective normalization ultimately depends on accurate business semantics, governance requirements and deliberate performance decisions.

Key Terms

Attribute Closure: The set of all attributes functionally determined by an attribute set under given dependencies.

BCNF: A normal form requiring every determinant of a nontrivial functional dependency to be a superkey.

Candidate Key: A minimal attribute set that uniquely identifies every tuple.

Dependency Preservation: The ability to enforce original dependencies using constraints projected onto decomposed relations.

Determinant: The attribute set on the left side of a dependency.

Fifth Normal Form: A normal form in which every nontrivial join dependency is implied by candidate keys.

First Normal Form: A form requiring single domain values and no repeating groups.

Foreign Key: An attribute set that references a candidate key of another or the same relation.

Fourth Normal Form: A form requiring the determinant of every nontrivial multivalued dependency to be a superkey.

Functional Dependency: A constraint stating that one attribute set uniquely determines another.

Join Dependency: A constraint requiring a relation to equal the join of specified projections.

Lossless Decomposition: A decomposition from which every legal original relation can be reconstructed exactly.

Minimal Cover: An equivalent FD set with singleton right sides, no extraneous left attributes and no redundant dependencies.

Multivalued Dependency: A constraint expressing independence between multiple sets of values associated with a determinant.

Normalization: The systematic restructuring of relations to reduce redundancy and anomalies.

Prime Attribute: An attribute belonging to at least one candidate key.

Second Normal Form: A form eliminating partial dependencies of nonprime attributes on candidate keys.

Superkey: An attribute set that functionally determines all attributes of a relation.

Third Normal Form: A form in which every nontrivial FD has a superkey determinant or a prime right-hand attribute.

Review Questions

Short Answer Questions

1. Distinguish between a superkey and a candidate key.

2. What is an extraneous left-hand attribute in a functional dependency?

3. State the binary lossless-join test.

4. How does BCNF differ from 3NF?

5. When is a join dependency trivial?

Long Answer Questions

1. Explain how entities, weak entities, multivalued attributes and relationship types are mapped to relations.

2. Describe attribute closure and show how it is used to identify candidate keys.

3. Compare 1NF, 2NF, 3NF and BCNF using suitable examples.

4. Explain the 3NF synthesis and BCNF decomposition procedures, including their guarantees.

5. Discuss multivalued and join dependencies and their roles in 4NF and 5NF.

Analytical / Application Questions

1. Given `R(A,B,C,D)` and `F = {A → B, B → C, AC → D}`, identify all candidate keys and determine the highest normal form satisfied by R.

2. For `R(A,B,C)` with `F = {A → B, B → C}`, analyze the decompositions `{AB, AC}` and `{AB, BC}` for losslessness and dependency preservation.

3. A hospital stores `(Doctor, Specialty, Patient, Language)` where each doctor has several specialties and languages independently of assigned patients. Identify the relevant dependencies and propose a justified normalized design.

Multiple Choice Questions with Answers

Question 1

Which relational mapping is most appropriate for a multivalued PhoneNumber attribute of STUDENT?

A. Store all phone numbers as a comma-separated STUDENT column

B. Create STUDENT_PHONE(StudentID, PhoneNumber)

C. Add PhoneNumber to every relation in the database

D. Create one PhoneNumber column for each possible number

Correct Answer: B. Create STUDENT_PHONE(StudentID, PhoneNumber)

Question 2

Given F = {AB → C, A → B, B → D}, which attribute is extraneous on the left side of AB → C?

A. A only

B. B only

C. Both A and B

D. Neither A nor B

Correct Answer: B. B only

Question 3

For R(A,B,C) with F = {AB → C, C → B}, what are the candidate keys?

A. A and C

B. AB and AC

C. AB and BC

D. B and C

Correct Answer: B. AB and AC

Question 4

A binary decomposition of R into R1 and R2 is lossless with respect to F when which condition holds?

A. R1 and R2 have the same number of attributes

B. Every attribute belongs to both component schemas

C. R1 ∩ R2 functionally determines R1 or R2

D. Neither component contains a candidate key

Correct Answer: C. R1 ∩ R2 functionally determines R1 or R2

Question 5

For F = {A → B, B → C}, why is the decomposition {AB, AC} not dependency preserving?

A. A → B cannot be derived from the projected dependencies

B. B → C cannot be derived from the projected dependencies

C. A → C is not implied by F

D. The decomposition does not cover attribute C

Correct Answer: B. B → C cannot be derived from the projected dependencies

Question 6

Why can a relation satisfy 3NF but violate BCNF?

A. 3NF permits a non-superkey determinant when the dependent attribute is prime

B. BCNF permits repeating groups while 3NF does not

C. 3NF ignores all functional dependencies

D. BCNF applies only to relations with composite keys

Correct Answer: A. 3NF permits a non-superkey determinant when the dependent attribute is prime

Question 7

If StudentID ↠ Hobby and hobbies are independent of languages, which decomposition addresses the 4NF violation in STUDENT_INTEREST(StudentID, Hobby, Language)?

A. (StudentID) and (Hobby, Language)

B. (StudentID, Hobby) and (StudentID, Language)

C. (StudentID, Hobby, Language) and (StudentID)

D. (Hobby) and (Language)

Correct Answer: B. (StudentID, Hobby) and (StudentID, Language)

Question 8

When is a join dependency *(R1, R2, ..., Rn) trivial?

A. When every component is a candidate key

B. When at least one component schema equals the original schema R

C. When all component schemas are proper subsets of R

D. When the relation has no functional dependencies

Correct Answer: B. When at least one component schema equals the original schema R

Question 9

Which condition defines fifth normal form?

A. Every attribute must be part of the primary key

B. Every nontrivial FD must have a prime right-hand side

C. Every nontrivial join dependency must be implied by candidate keys

D. Every multivalued dependency must be trivial

Correct Answer: C. Every nontrivial join dependency must be implied by candidate keys

Question 10

What does the standard 3NF synthesis procedure guarantee?

A. A lossless and dependency-preserving 3NF decomposition

B. A dependency-preserving 5NF decomposition

C. A BCNF decomposition that always preserves dependencies

D. A single relation containing no foreign keys

Correct Answer: A. A lossless and dependency-preserving 3NF decomposition