Logical Design and Normalization

Introduction

Logical database design transforms a conceptual description of an information system into a structure that can be implemented by a relational database management system (RDBMS). The conceptual model usually identifies entities, attributes, and relationships. The logical model converts these ideas into relations (tables), attributes (columns), keys, and integrity constraints.

A well-designed relational schema should represent facts once, avoid unnecessary duplication, support reliable updates, and preserve the meaning of the data. Normalization is the systematic process used to achieve these goals. It is based primarily on functional dependencies and a sequence of normal forms: First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF).

Poor design can produce three important anomalies:

• Insertion anomaly: a fact cannot be recorded without also recording an unrelated fact.
• Update anomaly: the same fact must be changed in several rows, creating a risk of inconsistency.
• Deletion anomaly: removing one fact unintentionally removes another fact.

Normalization does not mean splitting every table as far as possible. It means organizing data according to its dependencies while preserving useful relationships and acceptable performance.

Learning Objectives

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

1. Transform an entity–relationship model into relational schemas.
2. Identify candidate keys, primary keys, foreign keys, and integrity constraints.
3. Explain functional dependencies and their role in design.
4. Recognize insertion, update, and deletion anomalies.
5. Apply 1NF, 2NF, 3NF, and BCNF.
6. Decompose relations while considering lossless join and dependency preservation.
7. Evaluate normalization decisions in a practical database project.

From Conceptual Models to Relational Schemas

A relational schema describes a relation by listing its attributes and constraints. For example:

STUDENT(StudentID, StudentName, ProgrammeCode, ProgrammeName)

Here, STUDENT is the relation name, and the items in parentheses are attributes. A relation schema is different from a relation instance: the schema describes the structure, whereas the instance contains the current rows.

Mapping entities

A strong entity in an entity–relationship model normally becomes a relation. Its identifier becomes the primary key.

For example:

• Entity: Student
• Attributes: StudentID, StudentName, Email
• Relational schema: STUDENT(StudentID, StudentName, Email)
• Primary key: StudentID

A multivalued attribute, such as a student's telephone numbers, should generally become a separate relation:

STUDENT_PHONE(StudentID, PhoneNumber)

The combination (StudentID, PhoneNumber) may be used as the primary key, and StudentID is also a foreign key referencing STUDENT.

Mapping relationships

For a one-to-many relationship, the primary key of the “one” side is placed as a foreign key in the “many” side. If one programme has many students, the schema may be:

PROGRAMME(ProgrammeCode, ProgrammeName)

STUDENT(StudentID, StudentName, ProgrammeCode)

STUDENT.ProgrammeCode references PROGRAMME.ProgrammeCode.

For a many-to-many relationship, create an associative relation. If students enrol in many modules and each module has many students:

STUDENT(StudentID, StudentName)

MODULE(ModuleCode, ModuleTitle, Credits)

ENROLMENT(StudentID, ModuleCode, EnrolmentDate, Grade)

The primary key of ENROLMENT is commonly (StudentID, ModuleCode), although the design may require a term or attempt number when a student can take the same module more than once.

For a one-to-one relationship, the key of one relation can be stored as a foreign key in the other. The choice depends on participation, optionality, and practical access requirements.

Integrity constraints

A logical schema should state at least the following constraints:

• Domain constraint: an attribute contains values of an appropriate type and permitted range.
• Entity integrity: a primary-key value is unique and not null.
• Referential integrity: a foreign-key value must match a referenced key value, unless null is explicitly permitted.
• Key constraint: no two rows have the same candidate-key value.
• Business rules: additional requirements, such as a grade being between 0 and 100.

These constraints are not merely technical details. They express organizational rules and protect the reliability of stored information.

Functional Dependencies

A functional dependency (FD) describes a relationship between attributes. In a relation, attribute set X functionally determines attribute set Y, written:

X → Y

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. It does not necessarily mean that X determines a value through a computational formula; it means that the data model guarantees uniqueness of the corresponding Y value.

For example, in a university database:

StudentID → StudentName, ProgrammeCode

A student's identifier determines that student's name and programme. However, ProgrammeCode does not usually determine StudentID, because many students can belong to one programme.

Full, partial, and transitive dependency

A dependency X → Y is a full functional dependency when no proper subset of X determines Y.

A partial dependency occurs when a non-key attribute depends on only part of a composite key. If the key is (StudentID, ModuleCode) and:

StudentID → StudentName

then StudentName depends on part of the key, not on the complete enrolment key.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute through the key. For example:

StudentID → ProgrammeCode

and

ProgrammeCode → ProgrammeName

imply that StudentID determines ProgrammeName indirectly. This is a transitive dependency in the relation containing all three attributes.

Candidate keys and prime attributes

A superkey is an attribute set that uniquely identifies each row. A candidate key is a minimal superkey: removing any attribute from it destroys uniqueness. One candidate key is selected as the primary key; other candidate keys are alternate keys.

An attribute belonging to at least one candidate key is a prime attribute. An attribute belonging to no candidate key is a non-prime attribute. This distinction is particularly important in the formal definition of 3NF.

Armstrong's inference principles

Functional dependencies can be derived using standard inference rules, commonly called Armstrong's axioms:

1. Reflexivity: if Y ⊆ X, then X → Y.
2. Augmentation: if X → Y, then XZ → YZ.
3. Transitivity: if X → Y and Y → Z, then X → Z.

Other useful rules, such as union and decomposition, can be derived from these axioms. In practical design, dependency reasoning helps identify keys and determine whether a decomposition is justified.

Anomalies in an Unnormalized Design

Consider the relation:

ENROLMENT_RECORD(StudentID, StudentName, ModuleCode, ModuleTitle, LecturerName, Grade)

Assume the key is (StudentID, ModuleCode). A student enrolled in three modules causes the student's name to be repeated three times. Similarly, the module title and lecturer are repeated for every enrolled student.

• If a student's name changes, several rows must be updated.
• A new module cannot be recorded until at least one student enrols.
• Deleting the last enrolment in a module may remove the only stored record of that module and lecturer.

These problems arise because the table stores several different facts: student facts, module facts, lecturer facts, and enrolment facts. Normalization separates these facts according to their dependencies.

First Normal Form

A relation is in First Normal Form (1NF) when every attribute contains atomic values for the purposes of the relational model, and repeating groups are not stored within a single row.

A non-1NF design might store:

STUDENT(StudentID, StudentName, Phone1, Phone2, Phone3)

or a single field containing a list of phone numbers. A 1NF design uses:

STUDENT(StudentID, StudentName)

STUDENT_PHONE(StudentID, PhoneNumber)

Atomicity is context-dependent. A full address may be treated as one value if the application never searches its components, but it may be divided into street, city, and postal code when those parts have independent meaning or validation rules.

1NF alone does not remove redundancy. A table can have atomic values and still contain partial and transitive dependencies.

Second Normal Form

A relation is in Second Normal Form (2NF) if:

1. It is in 1NF; and
2. Every non-prime attribute is fully functionally dependent on every candidate key.

The second condition matters mainly when a candidate key is composite. If a relation has a single-attribute key, it is automatically in 2NF once it is in 1NF, because no proper subset of a one-attribute key exists.

Worked example: converting to 2NF

Consider:

ENROLMENT_RECORD(StudentID, ModuleCode, StudentName, ModuleTitle, Grade)

Candidate key: (StudentID, ModuleCode)

Dependencies:

• StudentID → StudentName
• ModuleCode → ModuleTitle
• (StudentID, ModuleCode) → Grade

StudentName depends only on StudentID, and ModuleTitle depends only on ModuleCode. These are partial dependencies. Decompose into:

STUDENT(StudentID, StudentName)

MODULE(ModuleCode, ModuleTitle)

ENROLMENT(StudentID, ModuleCode, Grade)

Now each non-key attribute in each relation depends on the complete key of its relation. The decomposition reduces duplication and permits independent insertion of students and modules.

Third Normal Form

A relation is in Third Normal Form (3NF) if it is in 2NF and has no inappropriate transitive dependency. The formal definition is:

For every non-trivial functional dependency X → A, at least one of the following must hold:

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

At certificate level, the practical interpretation is that non-key attributes should describe the key, the whole key, and nothing but the key.

Worked example: converting to 3NF

Suppose:

STUDENT(StudentID, StudentName, ProgrammeCode, ProgrammeName)

Dependencies:

• StudentID → StudentName, ProgrammeCode
• ProgrammeCode → ProgrammeName

The primary key is StudentID. ProgrammeName depends on ProgrammeCode, not directly on the student identifier. Decompose into:

STUDENT(StudentID, StudentName, ProgrammeCode)

PROGRAMME(ProgrammeCode, ProgrammeName)

ProgrammeCode in STUDENT is a foreign key. Programme information is now stored once, and a programme can be created before students are assigned to it.

Boyce-Codd Normal Form

Boyce-Codd Normal Form (BCNF) is stronger than 3NF. A relation is in BCNF if, for every non-trivial functional dependency X → Y, X is a superkey.

Every BCNF relation is in 3NF, but a relation can satisfy 3NF and fail BCNF when a dependency has a determinant that is not a superkey while the dependent attribute is prime.

Illustrative BCNF example

Consider a relation:

TEACHING(Student, Module, Instructor)

Assume the business rules are:

• A student takes a module with one instructor: (Student, Module) → Instructor.
• Each instructor teaches only one module: Instructor → Module.

Candidate keys are (Student, Module) and (Student, Instructor). The dependency Instructor → Module violates BCNF because Instructor is not a superkey: an instructor may teach many students.

A BCNF decomposition is:

INSTRUCTOR_MODULE(Instructor, Module)

STUDENT_INSTRUCTOR(Student, Instructor)

The decomposition records the instructor's module assignment separately from the students taught by that instructor. In real systems, the design must be checked against the exact business rules; if instructors can teach multiple modules, this decomposition would not be justified by the stated dependency.

Decomposition Quality

Normalization decomposes a relation into smaller relations. Two properties are especially important.

Lossless-join decomposition

A decomposition is lossless if joining the decomposed relations recreates exactly the original valid relation, without losing information or generating spurious rows.

For a simple decomposition of relation R(X, Y, Z) into R1(X, Y) and R2(X, Z), a common sufficient condition is that the shared attributes X functionally determine all attributes of one component, such as X → Y or X → Z. This condition must be assessed with the actual dependency set.

A lossy decomposition can create combinations that never existed. For example, independently joining a student-to-module table with a student-to-lecturer table may falsely imply that every lecturer teaches every module associated with that student.

Dependency preservation

A decomposition is dependency-preserving if the original functional dependencies can be enforced by checking the decomposed relations individually, without always performing joins. Dependency preservation simplifies validation and can improve operational efficiency.

There is sometimes a trade-off: a decomposition may achieve BCNF but fail to preserve all dependencies, whereas a 3NF decomposition can often preserve dependencies while remaining lossless. Designers should select the highest useful normal form that satisfies integrity and operational requirements.

A Practical Normalization Procedure

A disciplined procedure is more reliable than guessing tables from examples.

1. List the attributes and identify the business meaning of each.
2. State the business rules that imply functional dependencies.
3. Determine candidate keys using the dependencies.
4. Remove repeating groups and achieve 1NF.
5. Remove partial dependencies to achieve 2NF.
6. Remove transitive dependencies to achieve 3NF.
7. Test for BCNF violations.
8. Check lossless join and dependency preservation.
9. Add primary keys, foreign keys, domains, and other constraints.
10. Test the design with realistic insert, update, and delete scenarios.

A dependency diagram or attribute-closure calculation can support this process. The closure of an attribute set X, written X^+, is the set of all attributes functionally determined by X under the stated dependencies. If X^+ contains every attribute in a relation, X is a superkey. Closure calculations are useful for verifying candidate keys, but for a certificate-level project they should be accompanied by plain-language business reasoning.

Applied Case: Course Registration Database

Background

This illustrative case concerns a training institution that records learners, programmes, modules, instructors, and registrations. An initial spreadsheet contains:

REGISTRATION(LearnerID, LearnerName, ProgrammeCode, ProgrammeName, ModuleCode, ModuleTitle, InstructorID, InstructorName, RegistrationDate, Result)

The institution reports duplicated learner, programme, module, and instructor information.

Problem analysis

The likely dependencies are:

• LearnerID → LearnerName, ProgrammeCode
• ProgrammeCode → ProgrammeName
• ModuleCode → ModuleTitle
• InstructorID → InstructorName
• (LearnerID, ModuleCode) → RegistrationDate, Result, InstructorID]

The final dependency must be confirmed. If an instructor changes by academic term, then Term must be included in the key, and the dependency becomes (LearnerID, ModuleCode, Term) → .... Dependencies come from policy and practice, not merely from observing existing rows.

Solution

A normalized design might be:

LEARNER(LearnerID, LearnerName, ProgrammeCode)

PROGRAMME(ProgrammeCode, ProgrammeName)

MODULE(ModuleCode, ModuleTitle)

INSTRUCTOR(InstructorID, InstructorName)

REGISTRATION(LearnerID, ModuleCode, RegistrationDate, Result, InstructorID)

Primary keys are LearnerID, ProgrammeCode, ModuleCode, and InstructorID in their respective relations. The registration key is (LearnerID, ModuleCode) under the stated assumption. Foreign keys connect the relations.

Outcome

The design stores each learner, programme, module, and instructor fact once. A programme can be inserted before learners register, and changing an instructor's name requires one update. Registration remains the place for facts specific to a learner–module event.

Learning points

The most important design decision was not the mechanical splitting of columns. It was identifying what each identifier means and stating the rules that determine other attributes. If the rules change, the keys and relations may also need to change.

Practical and Professional Considerations

Normalization and performance

Highly normalized designs reduce redundancy and improve integrity, but queries may require several joins. In reporting systems, carefully controlled denormalization—such as summary tables, materialized views, or dimensional models—may improve read performance. Denormalization should be documented and supported by automated processes that prevent inconsistent copies.

Surrogate keys

A surrogate key, such as an automatically generated integer, has no direct business meaning. It can simplify foreign keys and protect the schema when a natural identifier changes. However, surrogate keys do not eliminate the need to enforce natural uniqueness. If email addresses must be unique, a unique constraint is still required even if UserID is the primary key.

Null values

Null does not mean zero, an empty string, or “not applicable” in every context. Functional-dependency reasoning becomes more difficult when attributes are nullable, and different DBMS products treat nulls differently in unique constraints. Required attributes should be declared not null where the business rule demands them.

Security and governance

Logical design also supports governance. Separating relations can help limit access to sensitive attributes, such as personal contact information, while allowing operational users to access registration data. Designers should apply least-privilege access, retain audit information where required, and avoid duplicating sensitive data unnecessarily. Data protection laws may require appropriate purpose limitation, retention controls, accuracy, and access management.

AI-assisted design

AI tools can suggest tables, keys, or normalization steps from sample data, but they cannot reliably infer unstated business rules. Sample data may accidentally appear unique even when uniqueness is not guaranteed. Professional practice requires human validation with domain experts, explicit dependency statements, privacy review, and tests based on future as well as current data.

Challenges and Limitations

Normalization depends on accurate functional dependencies. If dependencies are misunderstood, a formally tidy schema may still represent the business incorrectly. Conversely, some real-world facts are temporal: prices, instructor assignments, addresses, and programme names may change over time. A design must specify whether it stores current values or historical values.

Another limitation is that BCNF is not always the best operational choice. A BCNF decomposition can make an important dependency difficult to enforce. 3NF is often selected because it provides a practical balance among redundancy reduction, dependency preservation, and query simplicity. Normalization is therefore a design method, not a competition to reach the highest label.

Best Practices Checklist

• Derive tables from documented business rules, not only from sample spreadsheets.
• Give every relation a clear purpose and stable primary key.
• Identify all candidate keys and enforce alternate-key uniqueness.
• Use foreign keys to protect relationships.
• Normalize at least to 3NF for ordinary transactional systems unless a justified exception exists.
• Test for BCNF when determinants and candidate keys are complex.
• Check lossless join and dependency preservation after decomposition.
• Consider time, nullability, security, and changing business rules.
• Document intentional denormalization and its maintenance mechanism.
• Test insertion, update, and deletion scenarios before implementation.

Chapter Summary

Logical design translates conceptual entities and relationships into relational schemas with attributes, keys, foreign keys, and integrity constraints. Functional dependencies express which attributes determine others and provide the reasoning foundation for normalization. Candidate keys identify rows uniquely; partial dependencies involve part of a composite key, while transitive dependencies pass through a non-key attribute.

1NF removes repeating groups and requires atomic values. 2NF removes partial dependencies of non-prime attributes on composite keys. 3NF removes inappropriate transitive dependencies and is commonly summarized as requiring attributes to depend on the key, the whole key, and nothing but the key. BCNF strengthens this requirement by insisting that every determinant be a superkey.

Decomposition should be lossless and, where possible, dependency-preserving. In professional database work, normalization must be balanced with performance, historical requirements, security, governance, and maintainability. The quality of a design ultimately depends on correctly understood business rules and carefully enforced constraints.

Key Terms

Functional dependency: A constraint in which one attribute set determines another.

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

Superkey: An attribute set that uniquely identifies rows, possibly with unnecessary attributes.

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

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

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

Non-prime attribute: An attribute belonging to no candidate key.

Atomic value: A value treated as indivisible for the relation's intended use.

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

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

3NF: 2NF plus removal of inappropriate transitive dependencies.

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

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

Transitive dependency: Dependence of a non-key attribute on a key through another non-key attribute.

Lossless decomposition: A decomposition whose join recreates exactly the valid original relation.

Dependency preservation: The ability to enforce dependencies using the decomposed relations without requiring joins.

Insertion anomaly: Difficulty recording a fact because another fact is absent.

Update anomaly: Inconsistency caused by changing repeated copies of one fact.

Deletion anomaly: Accidental loss of an independent fact when another fact is deleted.

Denormalization: Deliberate introduction of controlled redundancy for operational reasons.

Review Questions

5 Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Define a functional dependency and give one example.
3. Why does a many-to-many relationship require an associative relation?
4. What is a partial dependency?
5. State the main rule of BCNF.

5 Long Answer Questions

1. Explain how an entity–relationship model is transformed into relational schemas, including the treatment of one-to-many and many-to-many relationships.
2. Describe insertion, update, and deletion anomalies using an enrolment relation as an example.
3. Explain 1NF, 2NF, and 3NF, and show how each form addresses a different design problem.
4. Compare 3NF and BCNF, including a situation in which a relation may satisfy 3NF but fail BCNF.
5. Explain lossless join and dependency preservation and discuss why both matter in database decomposition.

3 Analytical / Application Questions

1. Given ORDER_LINE(OrderID, ProductID, ProductName, Quantity, CustomerID, CustomerName), state reasonable functional dependencies, identify the likely key, and propose a normalized design.
2. A college allows a learner to repeat a module in different academic terms. Explain why (LearnerID, ModuleCode) may no longer be a sufficient key and propose an improved registration schema.
3. A reporting team wants to copy programme names into every registration row to avoid joins. Analyze the benefits and risks of this denormalization and recommend controls if it is adopted.

Multiple Choice Questions with Answers

Question 1

Which statement best describes a functional dependency X → Y?

A. Every value of Y determines exactly one value of X

B. Equal X values must have equal Y values

C. X and Y must both be primary keys

D. X and Y must be stored in separate tables

Correct Answer: B. Equal X values must have equal Y values

Question 2

Which relation is most appropriate for a many-to-many relationship between Student and Module?

A. A single Student column in MODULE

B. A single Module column in STUDENT

C. An associative ENROLMENT relation

D. A duplicated copy of all student attributes in MODULE

Correct Answer: C. An associative ENROLMENT relation

Question 3

A relation with key (StudentID, ModuleCode) contains StudentName, and StudentID determines StudentName. Which problem exists?

A. Transitive dependency

B. Partial dependency

C. Referential integrity violation

D. Domain violation

Correct Answer: B. Partial dependency

Question 4

Which condition is required for First Normal Form?

A. Every determinant must be a superkey

B. No foreign keys may exist

C. Attributes contain atomic values with no repeating groups

D. Every relation must have a composite key

Correct Answer: C. Attributes contain atomic values with no repeating groups

Question 5

A relation with a single-attribute candidate key is in 1NF. Which statement about 2NF is correct?

A. It is automatically in 2NF

B. It cannot be in 2NF

C. It is in 2NF only if it has no foreign keys

D. It is in 2NF only if it has a many-to-many relationship

Correct Answer: A. It is automatically in 2NF

Question 6

In the relation STUDENT(StudentID, ProgrammeCode, ProgrammeName), which dependency commonly causes a 3NF violation?

A. StudentID → ProgrammeCode

B. StudentID → StudentID

C. ProgrammeCode → ProgrammeName

D. ProgrammeName → StudentID

Correct Answer: C. ProgrammeCode → ProgrammeName

Question 7

What is the defining requirement of BCNF?

A. Every relation must contain exactly two keys

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

C. Every attribute must be numeric

D. Every table must be denormalized for reporting

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

Question 8

What does a lossless decomposition guarantee?

A. The decomposed tables contain no foreign keys

B. Joining the decomposed relations recreates the valid original information without spurious rows

C. All dependencies are automatically preserved

D. Queries will always require fewer joins

Correct Answer: B. Joining the decomposed relations recreates the valid original information without spurious rows

Question 9

Which item is an update anomaly?

A. A module cannot be inserted without a student

B. Deleting the last enrolment removes module information

C. Changing a student's name requires updating several rows

D. A foreign key prevents an invalid registration

Correct Answer: C. Changing a student's name requires updating several rows

Question 10

Why might a designer choose 3NF rather than BCNF?

A. 3NF always uses fewer tables

B. 3NF can offer dependency preservation while avoiding excessive decomposition

C. BCNF permits more redundancy than 3NF

D. 3NF does not require primary keys

Correct Answer: B. 3NF can offer dependency preservation while avoiding excessive decomposition