Logical Design and Normalization

Introduction

A conceptual data model describes important business objects and their relationships. A logical relational design converts that model into relations—commonly implemented as tables—whose structure supports accurate storage, efficient querying, and controlled change. This conversion is not merely a mechanical translation. Designers must decide which attributes belong together, how relationships are represented, which constraints must hold, and how to avoid storing the same fact repeatedly.

Poor logical design creates redundancy. Redundancy, in turn, can produce update anomalies: inconsistent values after an update, inability to record a fact without an unrelated fact, or accidental loss of information when a row is deleted. Normalization provides a systematic way to identify and reduce these problems using functional dependencies and normal forms.

Normalization is not a rule that every database must apply maximally. It is a design discipline. Highly normalized structures usually improve integrity, while selective denormalization may improve performance in carefully justified analytical or operational contexts. A professional database designer must understand both the theory and the trade-offs.

Learning Objectives

By the end of this chapter, you should be able to:

• transform entities, attributes, and relationships into relational schemas;
• identify candidate keys, primary keys, foreign keys, and integrity constraints;
• describe functional dependencies and infer their implications;
• recognize insertion, update, and deletion anomalies;
• apply first, second, and third normal form, and explain Boyce–Codd normal form;
• distinguish lossless decomposition from lossy decomposition;
• assess normalization decisions in operational and analytical environments;
• communicate logical design decisions to developers, analysts, and data stewards.

From Conceptual Model to Relational Schema

A relational schema is a formal description of a relation, usually written as a relation name followed by its attributes. For example:

STUDENT(StudentID, StudentName, ProgrammeCode)

The relation name is STUDENT, and the attributes are StudentID, StudentName, and ProgrammeCode. A database instance is the current set of rows that conforms to the schema.

Mapping entities

A strong entity normally becomes a relation. Its identifying attribute becomes the primary key, and its descriptive attributes become columns. Thus, an entity STUDENT with identifier StudentID becomes:

STUDENT(StudentID, StudentName, Email, ProgrammeCode)
Primary key: StudentID

A primary key must uniquely identify each row and must not be null. A candidate key is any minimal attribute set capable of uniquely identifying rows. One candidate key is selected as the primary key; remaining candidate keys are often enforced with UNIQUE constraints.

A natural key has meaning in the business domain, such as a national registration number. A surrogate key is an artificial identifier, such as an automatically generated integer. Surrogate keys can provide stability when natural identifiers change, but they do not eliminate the need to enforce business uniqueness.

Mapping relationships

The mapping depends on cardinality.

One-to-one relationship. A foreign key may be placed in either relation, preferably in the relation representing optional or dependent participation. It should usually be constrained as UNIQUE to preserve the one-to-one rule.

One-to-many relationship. Place the primary key of the “one” side as a foreign key in the “many” side. For example:

DEPARTMENT(DepartmentID, DepartmentName)
EMPLOYEE(EmployeeID, EmployeeName, DepartmentID)
Foreign key: EMPLOYEE.DepartmentID → DEPARTMENT.DepartmentID

Many-to-many relationship. Create an associative relation containing the primary keys of both participating relations. The combination commonly becomes the primary key.

ENROLLMENT(StudentID, CourseID, EnrolmentDate, Grade)
Primary key: (StudentID, CourseID)
Foreign keys: StudentID → STUDENT, CourseID → COURSE

Attributes of the relationship, such as EnrolmentDate and Grade, belong in the associative relation because they describe a particular student-course occurrence.

Multivalued attribute. A repeating attribute, such as several telephone numbers for one customer, should become a separate relation:

CUSTOMER(CustomerID, CustomerName)
CUSTOMER_PHONE(CustomerID, PhoneNumber)
Primary key: (CustomerID, PhoneNumber)

Weak entity. A weak entity depends on an owner entity for identification. Its relation generally contains the owner’s primary key together with the weak entity’s partial key. The combined attributes form the primary key.

Integrity constraints

Logical design is incomplete without constraints. Important constraints include:

• Domain integrity: values have appropriate data types, ranges, and formats.
• Entity integrity: primary-key values are unique and non-null.
• Referential integrity: a foreign-key value must match an existing referenced key, unless null is explicitly permitted.
• Cardinality constraints: relationships permit the intended number of related rows.
• Business rules: for example, an employee’s termination date cannot precede the hire date.

Constraints should be enforced by the database wherever practical, not only by application code. Database-enforced constraints protect data when multiple applications, scripts, or users access the same system.

Functional Dependencies

A functional dependency expresses a determination between attributes. In a relation, X functionally determines Y, written:

This means that whenever two rows have the same values for all attributes in X, they must also have the same values for all attributes in Y. X is the determinant, and Y is functionally dependent on X.

For example, if each CourseID identifies exactly one course title:

The dependency is a statement about the business meaning of the data, not merely an accidental pattern in current rows. If two rows happen to share the same instructor today, that does not establish InstructorName → Office unless the business rules guarantee it.

A functional dependency may have multiple attributes on either side. In an enrollment relation, the grade may depend on both the student and the course:

It generally does not depend on StudentID alone, because one student can receive different grades in different courses.

Full, partial, and transitive dependency

A dependency is full when the dependent attribute depends on the whole determinant and not on any proper subset. In (StudentID, CourseID) → Grade, Grade is fully dependent on the composite key if neither StudentID nor CourseID alone determines Grade.

A partial dependency occurs when a non-key attribute depends on only part of a composite candidate key. If CourseTitle is stored in ENROLLMENT and CourseID → CourseTitle, then CourseTitle depends on part of (StudentID, CourseID).

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

DepartmentName is transitively dependent on EmployeeID. The term “transitive” here refers to dependency implication, not to a physical database operation.

Armstrong’s inference principles

Functional dependencies can be inferred using sound rules known as Armstrong’s axioms:

1. Reflexivity: if Y is a subset of X, then X → Y.
2. Augmentation: if X → Y, then XZ → YZ.
3. Transitivity: if X → Y and Y → Z, then X → Z.

Additional useful rules include union, decomposition, and pseudotransitivity. These rules support reasoning about keys and normalization. They do not create business facts; the initial dependencies must come from domain knowledge, policies, and requirements analysis.

Finding candidate keys

To find candidate keys, identify attribute sets that determine every attribute in the relation. A candidate key must also be minimal: removing any attribute must cause it to stop determining all attributes. A superkey is any determining set, including non-minimal sets.

For a relation R(A, B, C, D), suppose the dependencies are A → B, B → C, and A → D. The closure of A is:

Because A determines every attribute, A is a superkey. Since A contains one attribute, it is also minimal and therefore a candidate key.

Attribute closure is written X⁺ and means the set of attributes functionally determined by X under the specified dependency set. Closure calculations are valid only relative to the assumed dependencies.

Why Normalize?

Consider the following unnormalized relation:

ORDER_LINE(OrderID, OrderDate, CustomerID, CustomerName,
           ProductID, ProductName, UnitPrice, Quantity)

Assume:

OrderID → OrderDate, CustomerID
CustomerID → CustomerName
ProductID → ProductName, UnitPrice
(OrderID, ProductID) → Quantity

An order with five products repeats its date and customer information five times. Product name and price are repeated in every order containing that product.

This produces three classic anomalies:

• Update anomaly: changing a customer’s name requires updates to many rows. If one row is missed, the database contains contradictory names.
• Insertion anomaly: a new product cannot be recorded unless it appears on an order, if the relation requires OrderID and Quantity.
• Deletion anomaly: deleting the last order line for a product may also remove the only stored record of that product’s name and price.

Normalization decomposes the relation so that each fact is stored in an appropriate place and dependencies are represented by keys and foreign keys.

Normal Forms

Normal forms are cumulative design conditions. A relation in a higher normal form must satisfy the relevant lower-level conditions, although the exact formal definitions depend on the relation’s candidate keys and functional dependencies.

First Normal Form

A relation is in First Normal Form (1NF) when each attribute contains atomic values and each row-column intersection contains one value from the relevant domain. Repeating groups and lists in a single column violate 1NF.

For example, this design is problematic:

CUSTOMER(CustomerID, Name, PhoneNumbers)

where PhoneNumbers contains “020-1111; 020-2222”. A separate CUSTOMER_PHONE relation is preferable. Atomicity is interpreted relative to the intended operations: a value may be considered atomic for one application but not another. In relational design, a phone number is commonly treated as one value, while a semicolon-separated list is not.

1NF does not remove redundancy. A relation can satisfy 1NF and still contain severe update anomalies.

Second Normal Form

A relation is in Second Normal Form (2NF) if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. A prime attribute is one that belongs to at least one candidate key.

2NF primarily matters when a candidate key is composite. Consider:

ENROLLMENT_DETAIL(StudentID, CourseID, StudentName, CourseTitle, Grade)

with dependencies:

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

StudentName and CourseTitle depend on only part of the composite key. Decompose as:

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

The decomposition removes partial dependencies and ensures that student and course facts are stored once.

If every candidate key consists of a single attribute, partial dependency on a proper subset cannot occur; therefore, a 1NF relation with only single-attribute candidate keys is automatically in 2NF.

Third Normal Form

A relation is in Third Normal Form (3NF) if, for every non-trivial functional dependency X → A, at least one of the following holds:

• X is a superkey; or
• A is a prime attribute.

A dependency is non-trivial when A is not already contained in X. Informally, 3NF removes problematic transitive dependencies of non-key attributes on keys, while allowing some dependencies involving prime attributes.

The common design intuition is that non-key facts should depend on “the key, the whole key, and nothing but the key.” This phrase is useful pedagogically but is not a complete formal definition.

Consider:

EMPLOYEE(EmployeeID, EmployeeName, DepartmentID, DepartmentName)

with:

EmployeeID → EmployeeName, DepartmentID
DepartmentID → DepartmentName

Decompose into:

EMPLOYEE(EmployeeID, EmployeeName, DepartmentID)
DEPARTMENT(DepartmentID, DepartmentName)

Now DepartmentName is maintained once per department.

Boyce–Codd Normal Form

A relation is in Boyce–Codd Normal Form (BCNF) if, for every non-trivial functional dependency X → Y, X is a superkey. BCNF is stricter than 3NF. A relation can satisfy 3NF while failing BCNF when a determinant is not a superkey but the dependent attribute is prime.

BCNF usually reduces redundancy more strongly, but decomposing into BCNF can fail to preserve all functional dependencies. 3NF decomposition algorithms can guarantee both lossless join and dependency preservation under standard conditions. Consequently, a designer must evaluate which property is more important for the application.

Higher normal forms

Fourth Normal Form (4NF) addresses problematic independent multivalued dependencies. For example, if an instructor independently teaches several subjects and speaks several languages, storing both lists in one relation can create unnecessary combinations. Separate relations may be needed.

Fifth Normal Form (5NF) addresses certain join dependencies where a relation should be decomposed into smaller relations to eliminate redundancy that cannot be explained by ordinary functional or multivalued dependencies. These forms are less common in routine transactional designs, but they illustrate that normalization is a broader theory of dependency and join structure.

Decomposition: Losslessness and Dependency Preservation

A decomposition replaces one relation with several relations. It is successful only if the original information can be reconstructed correctly.

A decomposition is lossless-join if joining the decomposed relations produces exactly the original relation, with no spurious tuples. A decomposition is lossy if the join can generate combinations that were not present originally.

For a binary decomposition of R into R₁ and R₂, a standard lossless-join condition under functional dependencies is that the common attributes R₁ ∩ R₂ functionally determine all attributes of R₁ or all attributes of R₂. In practical designs, placing a primary key in one relation as a foreign key in another commonly supports a lossless reconstruction, provided the relationship semantics are correct.

A decomposition is dependency-preserving if constraints can be enforced by checking the decomposed relations individually, without requiring a join. Dependency preservation simplifies enforcement. However, not every lossless BCNF decomposition preserves all dependencies; this is one reason 3NF is often selected for operational systems.

Worked Design Example: University Course Registration

Requirements

A university wants to record students, departments, courses, course offerings, and enrollments. A course belongs to one department. A course may be offered in several academic terms. Students enroll in offerings, and each enrollment may receive a grade.

Initial conceptual translation

The main entities are STUDENT, DEPARTMENT, COURSE, and OFFERING. ENROLLMENT is an associative entity between STUDENT and OFFERING.

A suitable logical schema is:

DEPARTMENT(DepartmentID, DepartmentName)
STUDENT(StudentID, StudentName, Email, DepartmentID)
COURSE(CourseID, CourseTitle, CreditPoints, DepartmentID)
OFFERING(OfferingID, CourseID, TermCode, RoomCode, InstructorID)
ENROLLMENT(StudentID, OfferingID, EnrolmentDate, Grade)

Primary keys are DepartmentID, StudentID, CourseID, OfferingID, and (StudentID, OfferingID), respectively. Foreign keys connect DepartmentID in STUDENT and COURSE to DEPARTMENT; CourseID in OFFERING to COURSE; and StudentID and OfferingID in ENROLLMENT to their parent relations.

Dependency analysis

Assume the following business rules:

DepartmentID → DepartmentName
StudentID → StudentName, Email, DepartmentID
CourseID → CourseTitle, CreditPoints, DepartmentID
OfferingID → CourseID, TermCode, RoomCode, InstructorID
(StudentID, OfferingID) → EnrolmentDate, Grade

Each determinant is a key in its own relation. Therefore, the relations are designed to satisfy 3NF and, assuming no additional dependencies, BCNF. If the business rule says that a room can host only one offering in a term, an additional candidate key may be (TermCode, RoomCode). That rule should be represented with a UNIQUE constraint or an appropriate relation design.

Example SQL DDL

CREATE TABLE Department (
    DepartmentID   INTEGER PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Student (
    StudentID      INTEGER PRIMARY KEY,
    StudentName    VARCHAR(120) NOT NULL,
    Email          VARCHAR(255) NOT NULL UNIQUE,
    DepartmentID   INTEGER NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

CREATE TABLE Enrollment (
    StudentID      INTEGER NOT NULL,
    OfferingID     INTEGER NOT NULL,
    EnrolmentDate  DATE NOT NULL,
    Grade          VARCHAR(5),
    PRIMARY KEY (StudentID, OfferingID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (OfferingID) REFERENCES Offering(OfferingID)
);

The DDL is illustrative. Data types and syntax vary among database management systems. The design also requires a definition of permitted grade values and policies for updates or deletions of referenced records.

Applied Case: Retail Order Management

This is an illustrative case based on common retail requirements.

Background and problem

A growing retailer stores orders in a spreadsheet-like table containing order number, order date, customer details, product details, quantity, and price. Analysts report inconsistent customer addresses, while customer-service staff sometimes see different prices for the same product. Developers also struggle to determine whether a missing row means that a product was discontinued or that it has never been ordered.

Analysis

The initial relation is:

ORDER_RECORD(OrderNo, OrderDate, CustomerNo, CustomerName,
             CustomerAddress, ProductNo, ProductName,
             CurrentPrice, Quantity)

Business rules establish:

OrderNo → OrderDate, CustomerNo
CustomerNo → CustomerName, CustomerAddress
ProductNo → ProductName, CurrentPrice
(OrderNo, ProductNo) → Quantity

The presumed key is (OrderNo, ProductNo). Customer attributes depend on OrderNo indirectly and Product attributes depend on ProductNo, creating partial and transitive dependencies.

Solution

Decompose the design:

CUSTOMER(CustomerNo, CustomerName, CustomerAddress)
PRODUCT(ProductNo, ProductName, CurrentPrice)
ORDER_HEADER(OrderNo, OrderDate, CustomerNo)
ORDER_LINE(OrderNo, ProductNo, Quantity, SalePrice)

SalePrice is deliberately stored in ORDER_LINE rather than derived from current PRODUCT.CurrentPrice. This preserves the historical price charged at the time of sale. The distinction illustrates an important design principle: a fact may be dependent on a transaction event even when a similar current fact exists elsewhere.

Outcome

Customer details can be updated once. Products can be created before their first sale. Deleting an order does not delete the product catalogue. Historical sales remain accurate even after a product’s current price changes. Queries require joins, but the additional complexity is justified by improved integrity and auditability.

Learning points

1. Dependencies must be derived from business rules, not from a small sample of rows.
2. Historical facts and current master data should not be confused.
3. Associative relations often contain event-specific attributes.
4. A normalized design supports clearer ownership of data and more reliable governance.

Normalization in Analytics and Modern Data Platforms

Operational transaction-processing systems commonly favor 3NF or BCNF because they handle frequent inserts and updates. Analytical systems may use dimensional models, such as star schemas, in which facts and dimensions are intentionally arranged for readable and performant aggregation. Such models may contain controlled redundancy, including repeated descriptive attributes in a dimension or precomputed measures.

Denormalization is justified only after identifying a performance, usability, or integration requirement. Appropriate controls include:

• documenting the source of duplicated attributes;
• defining which table is authoritative;
• automating refresh or synchronization;
• testing consistency between copies;
• monitoring query performance before and after the change;
• retaining historical versions where time matters.

Cloud warehouses, lakehouses, and semantic layers increasingly separate storage design from user-facing analytical models. ELT pipelines may preserve normalized source data while publishing denormalized marts for dashboards. This is not a rejection of normalization; it is a deliberate separation of operational integrity from analytical access.

AI-assisted schema design can suggest entities, keys, and dependencies from documentation or sample data. Such suggestions are not proof. AI may infer dependencies from accidental correlations, overlook exceptions, expose sensitive data during processing, or encode an incorrect interpretation of policy. Human validation, data classification, access control, and test data are essential.

Governance, Privacy, and Professional Practice

Logical design influences privacy and compliance. Separating customer identity from transaction data can support access control, but joins may still reconstruct sensitive profiles. Designers should apply data minimization: store only attributes needed for legitimate purposes. Sensitive identifiers should have appropriate protection, auditing, retention rules, and role-based access.

Foreign keys and uniqueness constraints support data quality, but they do not establish that collected data is lawful, fair, or fit for purpose. Data stewards, business owners, security professionals, and database engineers should agree on definitions, retention, lineage, and accountability.

Best practices include:

• document assumptions and functional dependencies;
• distinguish business keys from surrogate keys;
• name constraints explicitly and consistently;
• test insert, update, and delete scenarios;
• use representative data, including nulls and exceptions;
• review candidate keys whenever business rules change;
• avoid storing derived values unless their purpose and refresh policy are clear;
• measure performance before denormalizing;
• maintain an authoritative data dictionary.

Challenges and Limitations

Normalization does not solve every data problem. Functional dependencies may be uncertain, changing, or conditional. A dependency valid for one business unit may not apply globally. Null values complicate informal reasoning because unknown values are not ordinary values. Temporal data adds another dimension: a dependency may hold at one point in time but not across history.

Highly normalized schemas can require many joins, making queries harder for inexperienced users and sometimes affecting performance. Indexes, query optimization, materialized views, caching, and analytical marts can address these issues without abandoning sound source design. Conversely, excessive denormalization can create silent inconsistencies and complicated synchronization logic.

The goal is not to maximize the normal-form label. The goal is to represent business facts accurately, enforce meaningful rules, and support required workloads at acceptable cost.

Chapter Summary

Logical design translates conceptual entities and relationships into relational schemas. Entities usually become relations, one-to-many relationships place a foreign key on the many side, and many-to-many relationships require associative relations. Primary keys identify rows; foreign keys represent references; constraints protect domain, entity, and referential integrity.

Functional dependencies state which attributes determine others. Candidate keys are minimal determinants of all relation attributes. Partial dependencies motivate second normal form, while transitive dependencies motivate third normal form. BCNF requires every determinant of a non-trivial dependency to be a superkey. Higher normal forms address multivalued and join dependencies.

Normalization reduces redundancy and prevents insertion, update, and deletion anomalies. Decompositions should be assessed for lossless join and dependency preservation. Operational systems commonly favor normalized designs, while analytical systems may use controlled denormalization for performance and usability. Sound practice combines formal dependency analysis with business knowledge, governance, security, testing, and workload evaluation.

Key Terms

Functional dependency: A rule stating that one attribute set determines another.

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

Candidate key: A minimal attribute set that uniquely identifies each row.

Superkey: An attribute set that uniquely identifies rows, whether minimal or not.

Primary key: The selected candidate key used as the principal row identifier.

Foreign key: An attribute set referencing a key in another relation.

Natural key: An identifier with business meaning.

Surrogate key: An artificially generated identifier without inherent business meaning.

Referential integrity: The requirement that foreign-key references point to valid parent rows or permitted nulls.

1NF: Normal form requiring atomic values and no repeating groups.

2NF: 1NF plus no partial dependency of non-prime attributes on a composite candidate key.

3NF: A form requiring each non-trivial dependency to have a superkey determinant or a prime dependent attribute.

BCNF: A form requiring every determinant in a non-trivial dependency to be a superkey.

Partial dependency: Dependency on only part of a composite candidate key.

Transitive dependency: Dependency arising through an intermediate determinant.

Lossless decomposition: Decomposition whose correct join recreates exactly the original relation.

Dependency preservation: Ability to enforce dependencies without reconstructing the original relation.

Denormalization: Deliberate introduction of redundancy for a documented purpose.

Associative relation: A relation representing a many-to-many relationship.

Attribute closure: The attributes functionally implied by a given attribute set.

Review Questions

5 Short Answer Questions

1. What is the difference between a candidate key and a superkey?
2. How is a many-to-many relationship represented relationally?
3. Define a functional dependency and give one example.
4. What problem does second normal form address?
5. Why should a foreign key usually reference a primary or candidate key?

5 Long Answer Questions

1. Explain how entities and relationship cardinalities are transformed into relational schemas. Include examples of one-to-one, one-to-many, and many-to-many mappings.
2. Explain insertion, update, and deletion anomalies using a single denormalized relation.
3. Compare 1NF, 2NF, 3NF, and BCNF, identifying the dependency problem addressed by each.
4. Explain lossless decomposition and dependency preservation. Why might a designer prefer 3NF over BCNF in some cases?
5. Discuss the advantages and risks of denormalization in an analytical data warehouse.

3 Analytical / Application Questions

1. Given R(A, B, C, D) with dependencies A → B, B → C, and A → D, determine whether A is a candidate key and explain your reasoning using attribute closure.
2. A relation SALES(OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity) has dependencies OrderID → CustomerID, CustomerID → CustomerName, ProductID → ProductName, and (OrderID, ProductID) → Quantity. Identify the likely key and propose a 3NF decomposition.
3. A company wants to store a customer’s current address in CUSTOMER and the address used on each historical invoice in INVOICE. Explain why these may legitimately be different facts and propose suitable attributes and constraints.

Multiple Choice Questions with Answers

Question 1

Which relation best represents a many-to-many relationship between STUDENT and COURSE?

A. A STUDENT relation with a single CourseID column

B. A COURSE relation with a single StudentID column

C. An associative ENROLLMENT relation containing StudentID and CourseID

D. A relation containing only StudentName and CourseName

Correct Answer: C. An associative ENROLLMENT relation containing StudentID and CourseID

Question 2

If ProductID → ProductName, what does the dependency mean?

A. Every product has many names

B. ProductName uniquely determines ProductID

C. ProductID determines a single ProductName

D. ProductID and ProductName must be composite keys

Correct Answer: C. ProductID determines a single ProductName

Question 3

Which anomaly occurs when deleting the last order for a product also removes the only stored product description?

A. Insertion anomaly

B. Update anomaly

C. Deletion anomaly

D. Domain anomaly

Correct Answer: C. Deletion anomaly

Question 4

A relation is in 1NF when it has:

A. Only single-attribute keys

B. Atomic attribute values and no repeating groups

C. No foreign keys

D. No functional dependencies

Correct Answer: B. Atomic attribute values and no repeating groups

Question 5

Which dependency is a partial dependency in ENROLLMENT(StudentID, CourseID, CourseTitle, Grade), assuming CourseID → CourseTitle?

A. (StudentID, CourseID) → Grade

B. CourseID → CourseTitle

C. Grade → CourseID

D. StudentID → Grade

Correct Answer: B. CourseID → CourseTitle

Question 6

What is required for a relation to be in BCNF?

A. Every attribute must be part of a primary key

B. Every determinant of a non-trivial dependency must be a superkey

C. Every relation must contain a foreign key

D. All keys must be composite

Correct Answer: B. Every determinant of a non-trivial dependency must be a superkey

Question 7

For R(A, B, C, D), if A → B, B → C, and A → D, what is A⁺?

A. {A}

B. {A, B}

C. {A, C, D}

D. {A, B, C, D}

Correct Answer: D. {A, B, C, D}

Question 8

Which design best preserves the price actually paid on a historical order?

A. Store only the current price in PRODUCT

B. Store SalePrice in ORDER_LINE

C. Recalculate all historical prices from PRODUCT

D. Store price only in CUSTOMER

Correct Answer: B. Store SalePrice in ORDER_LINE

Question 9

A lossless decomposition is one that:

A. Always removes every foreign key

B. Produces exactly the original information when correctly joined

C. Contains no candidate keys

D. Requires no functional dependencies

Correct Answer: B. Produces exactly the original information when correctly joined

Question 10

Which statement about denormalization is most accurate?

A. It is always an error

B. It eliminates the need for constraints

C. It may be justified for performance if redundancy is controlled and documented

D. It guarantees better update consistency

Correct Answer: C. It may be justified for performance if redundancy is controlled and documented