Database Management SystemsFunctional Dependencies

Functional Dependency Concept

LevelIntermediate

Duration60 mins

TopicFunctional Dependencies

3 / 5

Determinant and Dependent

The Two Sides of Every Dependency

Every functional dependency tells a story of determination. On one side stands the determinant—the attribute or set of attributes whose values dictate what must follow. On the other side stands the dependent—the attribute(s) whose values are constrained by the determinant.

Understanding these roles deeply is essential because normalization algorithms treat determinants and dependents differently. A determinant might become a primary key in a decomposed table. A dependent might need to move to a different relation. The decisions depend on precisely identifying which attributes play which roles.

This page gives you the complete picture: formal definitions, properties, examples, and the crucial distinctions that inform database design decisions.

What You Will Learn

By the end of this page, you will master the terminology and properties of determinants and dependents, understand how these concepts relate to keys and candidate keys, recognize patterns in how attributes can serve in either role, and apply this understanding to analyze real schemas.

Formal Definitions: Determinant and Dependent

Let's establish precise definitions for these fundamental terms.

Determinant vs. Dependent: Formal Definitions
Term	Symbol Position	Definition	Also Called
Determinant	Left-Hand Side (X in X → Y)	The attribute(s) whose values uniquely identify the values of other attributes	LHS, Determining Set, Antecedent
Dependent	Right-Hand Side (Y in X → Y)	The attribute(s) whose values are uniquely determined by the determinant	RHS, Consequent, Determined Set

Formal Statement:

Given a functional dependency X → Y:

X is the determinant: the set of attributes on which Y is functionally dependent
Y is the dependent: the set of attributes that are functionally determined by X

In tuple terms:

The determinant values act as a "lookup key"
The dependent values are "looked up" using that key
Any two tuples with matching determinant values MUST have matching dependent values

determinant_dependent_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Examples of Determinant and Dependent Identification
 
-- FD: EmpID → {Name, DeptID, Salary}
-- Determinant: EmpID
-- Dependent: {Name, DeptID, Salary}
-- Meaning: The employee ID determines name, department, and salary
 
-- FD: {StudentID, CourseID} → Grade
-- Determinant: {StudentID, CourseID}  (composite)
-- Dependent: Grade
-- Meaning: The student-course pair determines the grade
 
-- FD: DeptID → DeptName
-- Determinant: DeptID
-- Dependent: DeptName  
-- Meaning: Department ID determines department name
 
-- FD: ISBN → {Title, Author, Publisher, Year}
-- Determinant: ISBN
-- Dependent: {Title, Author, Publisher, Year}
-- Meaning: ISBN determines all book metadata
 
-- Verification Query: Check if DeptID is truly a determinant for DeptName
-- If this returns rows, DeptID is NOT a valid determinant
SELECT d1.DeptID, d1.DeptName, d2.DeptName
FROM Department d1, Department d2
WHERE d1.DeptID = d2.DeptID 
  AND d1.DeptName <> d2.DeptName;
-- Zero rows = dependency holds

Think of Database Lookups

A helpful analogy: the determinant is like a dictionary word, and the dependent is like the definition. If you know the word (determinant), you can look up the exact definition (dependent). Two people looking up the same word must find the same definition—that's what functional dependency guarantees.

Properties of Determinants

Determinants have specific properties that affect how we analyze and use them in database design.

Key Properties of Determinants

•Uniqueness Requirement — For a functional dependency to hold, the determinant values must consistently map to the same dependent values. This doesn't mean determinant values must be unique (not a key), just consistent.
•Can Be Composite — A determinant can be a single attribute or multiple attributes together. {StudentID, CourseID} is a valid determinant—neither part alone suffices.
•Minimality Is Distinct from Validity — A determinant doesn't need to be minimal. {SSN, Name} → BirthDate is valid if SSN → BirthDate holds, though SSN alone is sufficient.
•Superkeys Are Universal Determinants — A superkey of a relation functionally determines ALL other attributes. It's the 'ultimate determinant' for that relation.
•Can Appear in Multiple FDs — The same attribute can be a determinant in multiple FDs. EmpID can determine Name, DeptID, and Salary in separate (or combined) FDs.
•Order Within Set Doesn't Matter — {A, B} and {B, A} are the same determinant. Sets are unordered.

Minimal Determinant:

A determinant X for Y is minimal if no proper subset of X also determines Y.

If {A, B} → C but NOT A → C and NOT B → C, then {A, B} is a minimal determinant for C
If {A, B} → C and ALSO A → C, then {A, B} is NOT minimal—it contains an extraneous attribute

Why Minimality Matters:

Minimal determinants become candidate keys in normalized schemas. Non-minimal determinants indicate potential for simplification. The canonical cover algorithm (later module) specifically eliminates non-minimal determinants.

determinant_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Analyzing Determinant Minimality
 
-- Consider relation R(A, B, C, D) with FDs:
-- F = { AB → C, A → D, B → D }
 
-- Is AB minimal for determining C?
-- Check: Does A → C? No (not in F, not derivable)
-- Check: Does B → C? No (not in F, not derivable)
-- Therefore: AB IS a minimal determinant for C
 
-- Is AB minimal for determining D?
-- Check: Does A → D? YES! (explicitly in F)
-- Therefore: AB is NOT minimal for D — A alone suffices
 
-- Example: Simplifying an FD set
-- Original: { AB → C, AB → D, A → D }
-- Notice: AB → D is redundant because A → D exists
--         and A is a subset of AB
-- Simplified: { AB → C, A → D }
 
-- Finding all determinants for an attribute
-- Question: What determines C in the above example?
-- Answer: AB (minimal), ABC (non-minimal), ABD (non-minimal), etc.
-- Technically any superset of AB determines C

Properties of Dependents

Dependents also have important properties that influence normalization decisions.

Key Properties of Dependents

•Can Be Decomposed — If X → YZ, then X → Y and X → Z. The dependent set can always be split into individual attributes without losing information.
•Can Be Composite — A dependent can include multiple attributes. EmpID → {Name, Address, Phone} is perfectly valid.
•Transitively Determined — If A → B and B → C, then C is transitively dependent on A. Transitive dependencies cause 3NF violations.
•Fully vs Partially Determined — In AB → C, C is fully dependent on AB if neither A nor B alone determines C. Otherwise, C is partially dependent (2NF violation).
•Can Overlap with Determinant — A → AB is valid (trivial for A, non-trivial for B). When analyzing, focus on the non-trivial part.
•Can Be Determinant Elsewhere — An attribute can be dependent in one FD and determinant in another. This is common and creates transitive chains.

Full Dependency

•Definition: Y is fully dependent on X if Y is dependent on X but not on any proper subset of X
•Example: Grade is fully dependent on {StudentID, CourseID}
•Neither StudentID alone nor CourseID alone determines Grade
•Required for 2NF satisfaction
•Composite keys should have full dependencies only

Partial Dependency

•Definition: Y is partially dependent on X if Y is dependent on some proper subset of X
•Example: StudentName depends on StudentID, which is part of {StudentID, CourseID}
•StudentName doesn't need CourseID—it's determined by StudentID alone
•Violates 2NF
•Causes redundancy in tables with composite keys

Partial Dependencies Mean Redundancy

If {A, B} is your primary key and C depends only on A (partial dependency), then C is repeated for every different value of B. This is exactly the redundancy normalization eliminates. Recognizing partial dependencies is key to achieving 2NF.

Determinants, Dependents, and Keys

The concepts of determinant and dependent are intimately connected to database keys. Understanding this relationship clarifies both concepts.

Keys Expressed as Determinants
Key Type	As Determinant	Dependent	Property
Superkey	K (where K ⊇ candidate key)	All attributes in R	May be non-minimal
Candidate Key	K (minimal superkey)	All attributes in R	Minimal determinant for entire relation
Primary Key	Chosen candidate key	All attributes in R	Selected for enforcement
Non-Key Determinant	X (not a superkey)	Some attributes Y	Potentially problematic for normalization

Key Insight: Non-Key Determinants Cause Normalization Issues

The most important distinction is between key determinants and non-key determinants:

Key Determinant: A determinant that is a superkey of the relation
- Example: EmpID → {Name, DeptID} where EmpID is the primary key
- This is expected and non-problematic
Non-Key Determinant: A determinant that is NOT a superkey
- Example: DeptID → DeptName in EMPLOYEE(EmpID, Name, DeptID, DeptName)
- DeptID is NOT a key of EMPLOYEE, yet it determines DeptName
- This causes redundancy: everyone in the same department repeats DeptName

BCNF Requirement: "Every determinant must be a superkey"

This single rule captures most of normalization. If all determinants are superkeys, most redundancy problems disappear.

key_vs_nonkey_determinant.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Analyzing Determinants as Keys or Non-Keys
 
-- Schema: EMPLOYEE(EmpID, Name, DeptID, DeptName, DeptLocation)
-- Key: EmpID
 
-- FDs:
-- EmpID → {Name, DeptID, DeptName, DeptLocation}  -- Key determinant
-- DeptID → {DeptName, DeptLocation}               -- Non-key determinant!
 
-- Analysis:
-- EmpID is a key, so EmpID → anything is fine
-- DeptID is NOT a key (multiple employees share DeptID)
-- But DeptID determines DeptName and DeptLocation
 
-- Problem illustrated:
-- EmpID  Name    DeptID  DeptName     DeptLocation
-- E001   Alice   D10     Engineering  Building A
-- E002   Bob     D10     Engineering  Building A  -- Redundant!
-- E003   Carol   D10     Engineering  Building A  -- Redundant!
-- E004   David   D20     Marketing    Building B
 
-- "Engineering" and "Building A" repeated 3 times!
-- This is because DeptID is a non-key determinant
 
-- Solution: Decompose based on the non-key determinant
CREATE TABLE EMPLOYEE_2 (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT  -- Foreign key to DEPARTMENT
);
 
CREATE TABLE DEPARTMENT (
    DeptID INT PRIMARY KEY,  -- Now a key determinant!
    DeptName VARCHAR(100),
    DeptLocation VARCHAR(100)
);

Normalization in One Sentence

If someone asks 'what is normalization?' you can now answer: 'Making every determinant a key.' Tables where all determinants are keys have no redundancy from functional dependencies.

Prime and Non-Prime Attributes

Related to determinants and dependents is another classification: prime vs. non-prime attributes. This classification is essential for understanding 2NF and 3NF.

Prime Attributes

•Definition: An attribute that is part of some candidate key
•Also called: Key attributes
•Example: In R(A, B, C) with key {A, B}, both A and B are prime
•If multiple candidate keys exist, an attribute in ANY is prime
•Prime attributes may not be removed from keys
•Central to 2NF and 3NF definitions

Non-Prime Attributes

•Definition: An attribute that is NOT part of any candidate key
•Also called: Non-key attributes, Descriptive attributes
•Example: In R(A, B, C) with key {A, B}, C is non-prime
•These are the attributes 'described by' the key
•Most normalization focuses on non-prime attributes
•Partial/transitive dependencies on non-primes cause violations

Example Analysis:

Relation: ENROLLMENT(StudentID, CourseID, Semester, Grade, StudentName)
Candidate Key: {StudentID, CourseID, Semester}
FDs:
  {StudentID, CourseID, Semester} → Grade
  StudentID → StudentName

Classification:

Prime attributes: StudentID, CourseID, Semester (all part of the key)
Non-prime attributes: Grade, StudentName

Problem Identified:

StudentName is non-prime
StudentName depends on StudentID (part of the key), not the full key
This is a partial dependency: a non-prime attribute depends on part of a composite key
This violates 2NF!

Why This Classification Matters

The 2NF rule says: 'No non-prime attribute should be partially dependent on any candidate key.' The 3NF rule says: 'No non-prime attribute should be transitively dependent on any candidate key.' Both rules specifically target non-prime attributes—that's where problematic dependencies occur.

Transitive Dependencies: The Chain Effect

When one dependent becomes a determinant for another attribute, we get transitive dependencies—chains of determination that cause redundancy.

Definition of Transitive Dependency:

Given a relation R with functional dependencies:

If A → B and B → C hold
And B is not a candidate key
And B → A does NOT hold (B doesn't determine A)
Then C is transitively dependent on A through B

Visual Chain:

A → B → C

C depends on A, but indirectly through B. B is the "middleman."

transitive_dependency_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Transitive Dependency Example
 
-- Schema: EMPLOYEE(EmpID, Name, DeptID, DeptName, DeptManager)
-- Key: EmpID
 
-- FDs:
-- EmpID → {Name, DeptID}      -- Direct dependencies on key
-- DeptID → {DeptName, DeptManager}  -- Transitive via DeptID
 
-- Chain:
-- EmpID → DeptID → DeptName
-- EmpID → DeptID → DeptManager
 
-- DeptName and DeptManager are transitively dependent on EmpID
 
-- Problem manifestation:
-- EmpID  Name   DeptID  DeptName     DeptManager
-- E001   Alice  D10     Engineering  M001
-- E002   Bob    D10     Engineering  M001  -- Redundant!
-- E003   Carol  D10     Engineering  M001  -- Redundant!
-- E004   David  D20     Marketing    M002
 
-- "Engineering" and "M001" repeated because:
-- 1. EmpID determines DeptID
-- 2. DeptID determines DeptName and DeptManager  
-- 3. The chain creates indirect (transitive) dependency
 
-- Solution: Break the transitive chain
-- Extract the second FD into its own table
 
CREATE TABLE EMPLOYEE_3NF (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT  -- FK to DEPARTMENT_3NF
);
 
CREATE TABLE DEPARTMENT_3NF (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100),
    DeptManager INT
);
 
-- Now DeptID is a KEY in its own table
-- No more transitive dependency (chain broken)

Transitive Dependencies = 3NF Violations

Transitive dependencies on non-prime attributes violate 3NF. The solution is always the same: extract the intermediate determinant (B in A → B → C) into its own table, where it becomes a key. This breaks the chain and eliminates redundancy.

Analyzing Real Schemas: Determinant/Dependent Identification

Let's apply our knowledge to analyze a realistic schema, identifying determinants, dependents, and potential issues.

Case Study: Library Book Lending System

LOAN(LoanID, BookID, BookTitle, BookAuthor, MemberID, MemberName, 
     MemberAddress, LoanDate, DueDate, ReturnDate)

Step 1: Identify Candidate Keys

LoanID uniquely identifies each loan transaction. Each loan is for one book by one member. Therefore:

Candidate Key: {LoanID}

Step 2: Identify All FDs

LoanID → {BookID, MemberID, LoanDate, DueDate, ReturnDate}
BookID → {BookTitle, BookAuthor}
MemberID → {MemberName, MemberAddress}

Step 3: Classify Determinants

Determinant	Type	Dependent Attributes
LoanID	Key determinant	BookID, MemberID, LoanDate, DueDate, ReturnDate
BookID	Non-key determinant	BookTitle, BookAuthor
MemberID	Non-key determinant	MemberName, MemberAddress

Step 4: Identify Issues

BookID is a non-key determinant → BookTitle and BookAuthor are transitively dependent on LoanID through BookID. If a book is loaned 100 times, its title and author are stored 100 times.
MemberID is a non-key determinant → MemberName and MemberAddress are transitively dependent. If a member borrows 50 books, their name and address are stored 50 times.

Step 5: Solution (3NF Decomposition)

BOOK(BookID PK, BookTitle, BookAuthor)
MEMBER(MemberID PK, MemberName, MemberAddress)  
LOAN_3NF(LoanID PK, BookID FK, MemberID FK, LoanDate, DueDate, ReturnDate)

Now every determinant is a key in its own table.

The Analysis Pattern

When analyzing any schema: (1) Find candidate keys, (2) List all FDs, (3) Classify each determinant as key or non-key, (4) Non-key determinants indicate normalization opportunities. This systematic approach works for any relation.

Summary: Determinants and Dependents in Practice

We've thoroughly explored the two sides of functional dependencies. Let's consolidate the key insights:

Key Takeaways

•Determinant (LHS) — The attribute(s) that uniquely determine others. Acts as a lookup key. Can be single or composite.
•Dependent (RHS) — The attribute(s) whose values are constrained by the determinant. Can be decomposed into individual FDs.
•Key vs. Non-Key Determinants — Key determinants are expected; non-key determinants cause redundancy and indicate normalization opportunities.
•Prime vs. Non-Prime Attributes — Prime attributes are in some candidate key; non-prime attributes are not. Normalization rules focus on non-prime attributes.
•Partial Dependency — Non-prime attribute depends on part of a composite key. Violates 2NF.
•Transitive Dependency — Non-prime attribute depends on key indirectly through another non-prime. Violates 3NF.
•BCNF Principle — 'Every determinant must be a superkey' captures the essence of normalization.

What's Next:

We've now mastered the terminology and mechanics of functional dependencies. The next page addresses a critical question: Where do FDs come from? We'll explore FD from Data vs. Schema—understanding whether FDs are discovered from data or defined from semantic understanding, and why this distinction fundamentally matters.

Page Complete

You now understand determinants and dependents at a deep level—their properties, their relationship to keys, and their role in causing or preventing data redundancy. This understanding is essential for all normalization work that follows.

3 / 5

Loading learning content...

Database Management SystemsFunctional Dependencies

Functional Dependency Concept

LevelIntermediate

Duration60 mins

TopicFunctional Dependencies

3 / 5

Determinant and Dependent

The Two Sides of Every Dependency

This page gives you the complete picture: formal definitions, properties, examples, and the crucial distinctions that inform database design decisions.

What You Will Learn

Formal Definitions: Determinant and Dependent

Let's establish precise definitions for these fundamental terms.

Determinant vs. Dependent: Formal Definitions
Term	Symbol Position	Definition	Also Called
Determinant	Left-Hand Side (X in X → Y)	The attribute(s) whose values uniquely identify the values of other attributes	LHS, Determining Set, Antecedent
Dependent	Right-Hand Side (Y in X → Y)	The attribute(s) whose values are uniquely determined by the determinant	RHS, Consequent, Determined Set

Formal Statement:

Given a functional dependency X → Y:

X is the determinant: the set of attributes on which Y is functionally dependent
Y is the dependent: the set of attributes that are functionally determined by X

In tuple terms:

The determinant values act as a "lookup key"
The dependent values are "looked up" using that key
Any two tuples with matching determinant values MUST have matching dependent values

determinant_dependent_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Examples of Determinant and Dependent Identification
 
-- FD: EmpID → {Name, DeptID, Salary}
-- Determinant: EmpID
-- Dependent: {Name, DeptID, Salary}
-- Meaning: The employee ID determines name, department, and salary
 
-- FD: {StudentID, CourseID} → Grade
-- Determinant: {StudentID, CourseID}  (composite)
-- Dependent: Grade
-- Meaning: The student-course pair determines the grade
 
-- FD: DeptID → DeptName
-- Determinant: DeptID
-- Dependent: DeptName  
-- Meaning: Department ID determines department name
 
-- FD: ISBN → {Title, Author, Publisher, Year}
-- Determinant: ISBN
-- Dependent: {Title, Author, Publisher, Year}
-- Meaning: ISBN determines all book metadata
 
-- Verification Query: Check if DeptID is truly a determinant for DeptName
-- If this returns rows, DeptID is NOT a valid determinant
SELECT d1.DeptID, d1.DeptName, d2.DeptName
FROM Department d1, Department d2
WHERE d1.DeptID = d2.DeptID 
  AND d1.DeptName <> d2.DeptName;
-- Zero rows = dependency holds

Think of Database Lookups

Properties of Determinants

Determinants have specific properties that affect how we analyze and use them in database design.

Key Properties of Determinants

•Uniqueness Requirement — For a functional dependency to hold, the determinant values must consistently map to the same dependent values. This doesn't mean determinant values must be unique (not a key), just consistent.
•Can Be Composite — A determinant can be a single attribute or multiple attributes together. {StudentID, CourseID} is a valid determinant—neither part alone suffices.
•Minimality Is Distinct from Validity — A determinant doesn't need to be minimal. {SSN, Name} → BirthDate is valid if SSN → BirthDate holds, though SSN alone is sufficient.
•Superkeys Are Universal Determinants — A superkey of a relation functionally determines ALL other attributes. It's the 'ultimate determinant' for that relation.
•Can Appear in Multiple FDs — The same attribute can be a determinant in multiple FDs. EmpID can determine Name, DeptID, and Salary in separate (or combined) FDs.
•Order Within Set Doesn't Matter — {A, B} and {B, A} are the same determinant. Sets are unordered.

Minimal Determinant:

A determinant X for Y is minimal if no proper subset of X also determines Y.

If {A, B} → C but NOT A → C and NOT B → C, then {A, B} is a minimal determinant for C
If {A, B} → C and ALSO A → C, then {A, B} is NOT minimal—it contains an extraneous attribute

Why Minimality Matters:

determinant_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Analyzing Determinant Minimality
 
-- Consider relation R(A, B, C, D) with FDs:
-- F = { AB → C, A → D, B → D }
 
-- Is AB minimal for determining C?
-- Check: Does A → C? No (not in F, not derivable)
-- Check: Does B → C? No (not in F, not derivable)
-- Therefore: AB IS a minimal determinant for C
 
-- Is AB minimal for determining D?
-- Check: Does A → D? YES! (explicitly in F)
-- Therefore: AB is NOT minimal for D — A alone suffices
 
-- Example: Simplifying an FD set
-- Original: { AB → C, AB → D, A → D }
-- Notice: AB → D is redundant because A → D exists
--         and A is a subset of AB
-- Simplified: { AB → C, A → D }
 
-- Finding all determinants for an attribute
-- Question: What determines C in the above example?
-- Answer: AB (minimal), ABC (non-minimal), ABD (non-minimal), etc.
-- Technically any superset of AB determines C

Properties of Dependents

Dependents also have important properties that influence normalization decisions.

Key Properties of Dependents

•Can Be Decomposed — If X → YZ, then X → Y and X → Z. The dependent set can always be split into individual attributes without losing information.
•Can Be Composite — A dependent can include multiple attributes. EmpID → {Name, Address, Phone} is perfectly valid.
•Transitively Determined — If A → B and B → C, then C is transitively dependent on A. Transitive dependencies cause 3NF violations.
•Fully vs Partially Determined — In AB → C, C is fully dependent on AB if neither A nor B alone determines C. Otherwise, C is partially dependent (2NF violation).
•Can Overlap with Determinant — A → AB is valid (trivial for A, non-trivial for B). When analyzing, focus on the non-trivial part.
•Can Be Determinant Elsewhere — An attribute can be dependent in one FD and determinant in another. This is common and creates transitive chains.

Full Dependency

•Definition: Y is fully dependent on X if Y is dependent on X but not on any proper subset of X
•Example: Grade is fully dependent on {StudentID, CourseID}
•Neither StudentID alone nor CourseID alone determines Grade
•Required for 2NF satisfaction
•Composite keys should have full dependencies only

Partial Dependency

•Definition: Y is partially dependent on X if Y is dependent on some proper subset of X
•Example: StudentName depends on StudentID, which is part of {StudentID, CourseID}
•StudentName doesn't need CourseID—it's determined by StudentID alone
•Violates 2NF
•Causes redundancy in tables with composite keys

Partial Dependencies Mean Redundancy

Determinants, Dependents, and Keys

The concepts of determinant and dependent are intimately connected to database keys. Understanding this relationship clarifies both concepts.

Keys Expressed as Determinants
Key Type	As Determinant	Dependent	Property
Superkey	K (where K ⊇ candidate key)	All attributes in R	May be non-minimal
Candidate Key	K (minimal superkey)	All attributes in R	Minimal determinant for entire relation
Primary Key	Chosen candidate key	All attributes in R	Selected for enforcement
Non-Key Determinant	X (not a superkey)	Some attributes Y	Potentially problematic for normalization

Key Insight: Non-Key Determinants Cause Normalization Issues

The most important distinction is between key determinants and non-key determinants:

Key Determinant: A determinant that is a superkey of the relation
- Example: EmpID → {Name, DeptID} where EmpID is the primary key
- This is expected and non-problematic
Non-Key Determinant: A determinant that is NOT a superkey
- Example: DeptID → DeptName in EMPLOYEE(EmpID, Name, DeptID, DeptName)
- DeptID is NOT a key of EMPLOYEE, yet it determines DeptName
- This causes redundancy: everyone in the same department repeats DeptName

BCNF Requirement: "Every determinant must be a superkey"

This single rule captures most of normalization. If all determinants are superkeys, most redundancy problems disappear.

key_vs_nonkey_determinant.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Analyzing Determinants as Keys or Non-Keys
 
-- Schema: EMPLOYEE(EmpID, Name, DeptID, DeptName, DeptLocation)
-- Key: EmpID
 
-- FDs:
-- EmpID → {Name, DeptID, DeptName, DeptLocation}  -- Key determinant
-- DeptID → {DeptName, DeptLocation}               -- Non-key determinant!
 
-- Analysis:
-- EmpID is a key, so EmpID → anything is fine
-- DeptID is NOT a key (multiple employees share DeptID)
-- But DeptID determines DeptName and DeptLocation
 
-- Problem illustrated:
-- EmpID  Name    DeptID  DeptName     DeptLocation
-- E001   Alice   D10     Engineering  Building A
-- E002   Bob     D10     Engineering  Building A  -- Redundant!
-- E003   Carol   D10     Engineering  Building A  -- Redundant!
-- E004   David   D20     Marketing    Building B
 
-- "Engineering" and "Building A" repeated 3 times!
-- This is because DeptID is a non-key determinant
 
-- Solution: Decompose based on the non-key determinant
CREATE TABLE EMPLOYEE_2 (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT  -- Foreign key to DEPARTMENT
);
 
CREATE TABLE DEPARTMENT (
    DeptID INT PRIMARY KEY,  -- Now a key determinant!
    DeptName VARCHAR(100),
    DeptLocation VARCHAR(100)
);

Normalization in One Sentence

If someone asks 'what is normalization?' you can now answer: 'Making every determinant a key.' Tables where all determinants are keys have no redundancy from functional dependencies.

Prime and Non-Prime Attributes

Related to determinants and dependents is another classification: prime vs. non-prime attributes. This classification is essential for understanding 2NF and 3NF.

Prime Attributes

•Definition: An attribute that is part of some candidate key
•Also called: Key attributes
•Example: In R(A, B, C) with key {A, B}, both A and B are prime
•If multiple candidate keys exist, an attribute in ANY is prime
•Prime attributes may not be removed from keys
•Central to 2NF and 3NF definitions

Non-Prime Attributes

•Definition: An attribute that is NOT part of any candidate key
•Also called: Non-key attributes, Descriptive attributes
•Example: In R(A, B, C) with key {A, B}, C is non-prime
•These are the attributes 'described by' the key
•Most normalization focuses on non-prime attributes
•Partial/transitive dependencies on non-primes cause violations

Example Analysis:

Relation: ENROLLMENT(StudentID, CourseID, Semester, Grade, StudentName)
Candidate Key: {StudentID, CourseID, Semester}
FDs:
  {StudentID, CourseID, Semester} → Grade
  StudentID → StudentName

Classification:

Prime attributes: StudentID, CourseID, Semester (all part of the key)
Non-prime attributes: Grade, StudentName

Problem Identified:

StudentName is non-prime
StudentName depends on StudentID (part of the key), not the full key
This is a partial dependency: a non-prime attribute depends on part of a composite key
This violates 2NF!

Why This Classification Matters

Transitive Dependencies: The Chain Effect

When one dependent becomes a determinant for another attribute, we get transitive dependencies—chains of determination that cause redundancy.

Definition of Transitive Dependency:

Given a relation R with functional dependencies:

If A → B and B → C hold
And B is not a candidate key
And B → A does NOT hold (B doesn't determine A)
Then C is transitively dependent on A through B

Visual Chain:

A → B → C

C depends on A, but indirectly through B. B is the "middleman."

transitive_dependency_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Transitive Dependency Example
 
-- Schema: EMPLOYEE(EmpID, Name, DeptID, DeptName, DeptManager)
-- Key: EmpID
 
-- FDs:
-- EmpID → {Name, DeptID}      -- Direct dependencies on key
-- DeptID → {DeptName, DeptManager}  -- Transitive via DeptID
 
-- Chain:
-- EmpID → DeptID → DeptName
-- EmpID → DeptID → DeptManager
 
-- DeptName and DeptManager are transitively dependent on EmpID
 
-- Problem manifestation:
-- EmpID  Name   DeptID  DeptName     DeptManager
-- E001   Alice  D10     Engineering  M001
-- E002   Bob    D10     Engineering  M001  -- Redundant!
-- E003   Carol  D10     Engineering  M001  -- Redundant!
-- E004   David  D20     Marketing    M002
 
-- "Engineering" and "M001" repeated because:
-- 1. EmpID determines DeptID
-- 2. DeptID determines DeptName and DeptManager  
-- 3. The chain creates indirect (transitive) dependency
 
-- Solution: Break the transitive chain
-- Extract the second FD into its own table
 
CREATE TABLE EMPLOYEE_3NF (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    DeptID INT  -- FK to DEPARTMENT_3NF
);
 
CREATE TABLE DEPARTMENT_3NF (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100),
    DeptManager INT
);
 
-- Now DeptID is a KEY in its own table
-- No more transitive dependency (chain broken)

Transitive Dependencies = 3NF Violations

Analyzing Real Schemas: Determinant/Dependent Identification

Let's apply our knowledge to analyze a realistic schema, identifying determinants, dependents, and potential issues.

Case Study: Library Book Lending System

LOAN(LoanID, BookID, BookTitle, BookAuthor, MemberID, MemberName, 
     MemberAddress, LoanDate, DueDate, ReturnDate)

Step 1: Identify Candidate Keys

LoanID uniquely identifies each loan transaction. Each loan is for one book by one member. Therefore:

Candidate Key: {LoanID}

Step 2: Identify All FDs

LoanID → {BookID, MemberID, LoanDate, DueDate, ReturnDate}
BookID → {BookTitle, BookAuthor}
MemberID → {MemberName, MemberAddress}

Step 3: Classify Determinants

Determinant	Type	Dependent Attributes
LoanID	Key determinant	BookID, MemberID, LoanDate, DueDate, ReturnDate
BookID	Non-key determinant	BookTitle, BookAuthor
MemberID	Non-key determinant	MemberName, MemberAddress

Step 4: Identify Issues

BookID is a non-key determinant → BookTitle and BookAuthor are transitively dependent on LoanID through BookID. If a book is loaned 100 times, its title and author are stored 100 times.
MemberID is a non-key determinant → MemberName and MemberAddress are transitively dependent. If a member borrows 50 books, their name and address are stored 50 times.

Step 5: Solution (3NF Decomposition)

BOOK(BookID PK, BookTitle, BookAuthor)
MEMBER(MemberID PK, MemberName, MemberAddress)  
LOAN_3NF(LoanID PK, BookID FK, MemberID FK, LoanDate, DueDate, ReturnDate)

Now every determinant is a key in its own table.

The Analysis Pattern

Summary: Determinants and Dependents in Practice

We've thoroughly explored the two sides of functional dependencies. Let's consolidate the key insights:

Key Takeaways

•Determinant (LHS) — The attribute(s) that uniquely determine others. Acts as a lookup key. Can be single or composite.
•Dependent (RHS) — The attribute(s) whose values are constrained by the determinant. Can be decomposed into individual FDs.
•Key vs. Non-Key Determinants — Key determinants are expected; non-key determinants cause redundancy and indicate normalization opportunities.
•Prime vs. Non-Prime Attributes — Prime attributes are in some candidate key; non-prime attributes are not. Normalization rules focus on non-prime attributes.
•Partial Dependency — Non-prime attribute depends on part of a composite key. Violates 2NF.
•Transitive Dependency — Non-prime attribute depends on key indirectly through another non-prime. Violates 3NF.
•BCNF Principle — 'Every determinant must be a superkey' captures the essence of normalization.

What's Next:

Page Complete

3 / 5