Database Management SystemsFD and Keys

Functional Dependencies and Keys

LevelIntermediate

Duration75 mins

TopicFD and Keys

2 / 5

Prime Attributes

The Attributes That Define Identity

In the previous page, we learned to derive candidate keys from functional dependencies. Now we turn our attention to a critical classification: which attributes participate in these keys, and which do not?

This distinction is far from academic. Prime attributes—those appearing in at least one candidate key—enjoy special status in normalization theory. They are protected from certain anomalies and treated differently by normal form definitions. Understanding prime attributes is essential for making informed decisions about schema design and decomposition.

What You Will Learn

By the end of this page, you will understand the formal definition of prime attributes, master the techniques for identifying them from candidate keys, and appreciate their pivotal role in normalization theory—particularly in the definitions of 2NF, 3NF, and BCNF.

Formal Definition of Prime Attributes

Definition:

An attribute A in a relation schema R is a prime attribute if A is a member of at least one candidate key of R.

Equivalently, given the set of candidate keys CK = {K₁, K₂, ..., Kₙ}, the set of prime attributes is:

Prime(R) = K₁ ∪ K₂ ∪ ... ∪ Kₙ = ⋃ Kᵢ

Key Observations:

Membership in ONE key suffices — An attribute need not appear in every candidate key; appearing in just one is enough to be prime
The union over all candidate keys — This means we must first find ALL candidate keys before identifying prime attributes
Primary key members are prime — Since the primary key is a candidate key, all primary key attributes are prime
Alternate key members are also prime — Attributes in non-primary candidate keys are equally prime

Prime vs. Non-Prime Terminology Overview
Term	Definition	Symbol Notation	Example
Prime Attribute	Member of at least one candidate key	A ∈ ⋃CK	StudentID in Student(StudentID, Email, Name)
Non-Prime Attribute	Not a member of any candidate key	A ∉ ⋃CK	Name in the same relation
Key Attribute	Synonym for prime attribute	Same as prime	Used interchangeably in literature
Non-Key Attribute	Synonym for non-prime	Same as non-prime	Used interchangeably in literature

Terminology Variations

Different textbooks use different terminology. 'Prime attribute' and 'key attribute' are synonymous, as are 'non-prime attribute' and 'non-key attribute.' Some sources also use 'secondary attribute' for non-prime. Be aware of these variations when reading database literature.

Identifying Prime Attributes: A Systematic Process

Identifying prime attributes requires first finding all candidate keys, then taking their union. The process is:

Step 1: Find all candidate keys (using techniques from the previous page)

Step 2: Compute the union of all candidate keys

Step 3: All attributes in this union are prime; all others are non-prime

Let's see this in action with a detailed example.

Relation: Employee(EmpID, Name, Email, Salary, DeptID)

Functional Dependencies:

F = {
    EmpID → Name, Email, Salary, DeptID
}

Step 1: Find all candidate keys

Applying attribute classification:

L-only: {EmpID}
R-only: {Name, Email, Salary, DeptID}
Both: {}
Neither: {}

Core = {EmpID} {EmpID}⁺ = {EmpID, Name, Email, Salary, DeptID} = U ✓

Since core is sufficient and there are no 'Both' attributes to try, there is exactly one candidate key: {EmpID}

Step 2: Union of all candidate keys

⋃CK = {EmpID}

Step 3: Classify attributes

Attribute	In ⋃CK?	Classification
EmpID	Yes	PRIME
Name	No	Non-prime
Email	No	Non-prime
Salary	No	Non-prime
DeptID	No	Non-prime

Result: Prime attributes = {EmpID}, Non-prime attributes = {Name, Email, Salary, DeptID}

Algorithmic Implementation

Given the candidate key finding algorithm from the previous page, computing prime attributes is straightforward. Here's a complete implementation:

Prime Attribute Identification
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
from itertools import combinations
 
def compute_closure(attributes: set, fds: list[tuple[set, set]]) -> set:
    """Compute attribute closure X+ under FDs F."""
    closure = set(attributes)
    changed = True
    while changed:
        changed = False
        for lhs, rhs in fds:
            if lhs.issubset(closure) and not rhs.issubset(closure):
                closure = closure.union(rhs)
                changed = True
    return closure
 
 
def find_all_candidate_keys(all_attrs: set, fds: list[tuple[set, set]]) -> list[set]:
    """Find all candidate keys using attribute classification."""
    # Classify attributes
    left_side = set()
    right_side = set()
    for lhs, rhs in fds:
        left_side.update(lhs)
        right_side.update(rhs)
    
    left_only = left_side - right_side
    right_only = right_side - left_side
    both = left_side & right_side
    neither = all_attrs - left_side - right_side
    
    core = left_only | neither
    
    if compute_closure(core, fds) == all_attrs:
        return [core] if core else [all_attrs]
    
    candidate_keys = []
    
    for size in range(1, len(both) + 1):
        for subset in combinations(both, size):
            candidate = core | set(subset)
            
            if compute_closure(candidate, fds) != all_attrs:
                continue
            
            # Check minimality
            is_minimal = True
            for existing in candidate_keys:
                if existing < candidate:
                    is_minimal = False
                    break
            
            if is_minimal:
                for attr in subset:
                    smaller = core | (set(subset) - {attr})
                    if compute_closure(smaller, fds) == all_attrs:
                        is_minimal = False
                        break
            
            if is_minimal:
                candidate_keys.append(candidate)
    
    return candidate_keys if candidate_keys else [all_attrs]
 
 
def find_prime_attributes(all_attrs: set, fds: list[tuple[set, set]]) -> tuple[set, set]:
    """
    Find prime and non-prime attributes.
    
    Returns:
        Tuple of (prime_attributes, non_prime_attributes)
    """
    candidate_keys = find_all_candidate_keys(all_attrs, fds)
    
    # Prime = union of all candidate keys
    prime = set()
    for key in candidate_keys:
        prime.update(key)
    
    # Non-prime = all attributes minus prime
    non_prime = all_attrs - prime
    
    return prime, non_prime
 
 
def analyze_relation(name: str, all_attrs: set, fds: list[tuple[set, set]]):
    """Complete analysis of a relation's key structure."""
    print(f"\n{'='*60}")
    print(f"Relation: {name}")
    print(f"Attributes: {sorted(all_attrs)}")
    print(f"\nFunctional Dependencies:")
    for lhs, rhs in fds:
        print(f"  {sorted(lhs)} → {sorted(rhs)}")
    
    candidate_keys = find_all_candidate_keys(all_attrs, fds)
    print(f"\nCandidate Keys: {[sorted(k) for k in candidate_keys]}")
    
    prime, non_prime = find_prime_attributes(all_attrs, fds)
    print(f"\nPrime Attributes: {sorted(prime)}")
    print(f"Non-Prime Attributes: {sorted(non_prime)}")
    print(f"{'='*60}")
 
 
# Example analyses
if __name__ == "__main__":
    # Example 1: Single key
    analyze_relation(
        "Employee",
        {"EmpID", "Name", "Email", "Salary", "DeptID"},
        [
            ({"EmpID"}, {"Name", "Email", "Salary", "DeptID"})
        ]
    )
    
    # Example 2: Multiple single-attribute keys
    analyze_relation(
        "Student",
        {"StudentID", "SSN", "Email", "Name", "Major"},
        [
            ({"StudentID"}, {"SSN", "Email", "Name", "Major"}),
            ({"SSN"}, {"StudentID", "Email", "Name", "Major"}),
            ({"Email"}, {"StudentID", "SSN", "Name", "Major"})
        ]
    )
    
    # Example 3: Composite key
    analyze_relation(
        "Enrollment",
        {"StudentID", "CourseID", "Semester", "Grade", "InstructorID"},
        [
            ({"StudentID", "CourseID", "Semester"}, {"Grade", "InstructorID"}),
            ({"CourseID", "Semester"}, {"InstructorID"})
        ]
    )

Prime Attributes in Normal Form Definitions

The prime/non-prime distinction is not merely a classification—it's central to the very definitions of normal forms. Let's examine how this classification shapes normalization theory:

Second Normal Form (2NF):

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key.

This definition explicitly treats prime and non-prime attributes differently:

Prime attributes are "exempt" from the 2NF partial dependency check
Only non-prime attributes can cause 2NF violations

Example of 2NF Analysis:

Relation: OrderItem(OrderID, ProductID, Quantity, ProductName, UnitPrice)

FDs:
  {OrderID, ProductID} → Quantity, ProductName, UnitPrice
  ProductID → ProductName, UnitPrice

Candidate Key: {OrderID, ProductID}
Prime: {OrderID, ProductID}
Non-Prime: {Quantity, ProductName, UnitPrice}

2NF Check: Is every non-prime fully dependent on the key?

Quantity: Depends on full key {OrderID, ProductID} ✓
ProductName: Depends on partial key {ProductID} only ✗ VIOLATION
UnitPrice: Depends on partial key {ProductID} only ✗ VIOLATION

Not in 2NF — ProductName and UnitPrice partially depend on the key.

Why Prime Attributes Are Exempt from 2NF

Prime attributes are part of keys themselves, so "partial dependency on a key" becomes logically awkward for them. If attribute A is part of key K, then certainly A depends on K—but this is a trivial dependency. The 2NF definition focuses on meaningful dependency violations involving non-prime attributes.

Third Normal Form (3NF):

A relation is in 3NF if, for every non-trivial FD X → A, at least one of the following holds:

X is a superkey, OR

A is a prime attribute

Again, prime attributes receive special treatment—they can appear on the right side of non-superkey FDs without violating 3NF!

Example of 3NF Analysis:

Relation: Employee(EmpID, DeptID, DeptName, DeptHead)

FDs:
  EmpID → DeptID, DeptName, DeptHead
  DeptID → DeptName, DeptHead

Candidate Key: {EmpID}
Prime: {EmpID}
Non-Prime: {DeptID, DeptName, DeptHead}

3NF Check: For each non-trivial FD X → A:

EmpID → DeptID: EmpID is superkey ✓
EmpID → DeptName: EmpID is superkey ✓
EmpID → DeptHead: EmpID is superkey ✓
DeptID → DeptName: Is DeptID a superkey? No. Is DeptName prime? No. ✗ VIOLATION
DeptID → DeptHead: Is DeptID a superkey? No. Is DeptHead prime? No. ✗ VIOLATION

Not in 3NF — The FDs DeptID → DeptName and DeptID → DeptHead violate 3NF because DeptID is not a superkey and DeptName/DeptHead are not prime.

Prime Attributes in Normal Form Definitions
Normal Form	Role of Prime Attributes	Role of Non-Prime Attributes
2NF	Exempt from partial dependency check	Must be fully dependent on all candidate keys
3NF	Can be on RHS of non-superkey FDs	Cannot be transitively dependent on key
BCNF	No special treatment—must follow superkey rule	No special treatment—must follow superkey rule

BCNF Eliminates the Prime Exception

Boyce-Codd Normal Form (BCNF) provides no special exemption for prime attributes. Every determinant must be a superkey, period. This is why BCNF is sometimes called a 'stronger' form than 3NF—it closes the loophole that 3NF provides for prime attributes.

Prime Attributes and Functional Dependencies

Prime attributes participate in interesting dependency patterns that warrant careful study:

1. Dependencies Among Prime Attributes

When multiple candidate keys exist, there are often dependencies among prime attributes:

Relation: Student(StudentID, SSN, Email, Name)

Candidate Keys: {StudentID}, {SSN}, {Email}
Prime: {StudentID, SSN, Email}
Non-Prime: {Name}

Implied FDs among primes:
  StudentID → SSN
  StudentID → Email
  SSN → StudentID
  SSN → Email
  Email → StudentID
  Email → SSN

These inter-key dependencies are valid but don't violate any normal form because the determinants are superkeys (single-attribute keys in this case).

2. Prime Attributes as Determinants

Prime attributes that are part of one key but not alone sufficient to be a key can be determinants of non-prime attributes:

Relation: Enrollment(StudentID, CourseID, Semester, Grade, RoomNumber)

FDs:
  {StudentID, CourseID, Semester} → Grade, RoomNumber
  {CourseID, Semester} → RoomNumber

Candidate Key: {StudentID, CourseID, Semester}
Prime: {StudentID, CourseID, Semester}
Non-Prime: {Grade, RoomNumber}

Here, {CourseID, Semester} determines RoomNumber. This is interesting:

CourseID and Semester are prime (part of the key)
But {CourseID, Semester} is NOT a superkey (doesn't determine StudentID or Grade)
This violates 3NF because RoomNumber is non-prime and depends on a non-superkey

The fact that the determinant consists entirely of prime attributes doesn't exempt it from 3NF analysis!

Prime ≠ Superkey

Don't confuse 'composed of prime attributes' with 'is a superkey.' A proper subset of a candidate key is made entirely of prime attributes, yet by definition it's NOT a superkey. Prime-ness is a property of individual attributes; superkey-ness is a property of attribute sets.

3. Overlapping Candidate Keys

When candidate keys overlap, the shared attributes are particularly interesting:

Relation: Assignment(ProjectID, EmployeeID, RoleType, StartDate)

FDs:
  {ProjectID, EmployeeID} → RoleType, StartDate
  {ProjectID, RoleType} → EmployeeID, StartDate

Candidate Keys: {ProjectID, EmployeeID}, {ProjectID, RoleType}
Prime: {ProjectID, EmployeeID, RoleType}  (union)
Non-Prime: {StartDate}

Note: ProjectID appears in both keys — it's "doubly prime"

The overlapping attribute (ProjectID) is the anchor—any way to identify an assignment must include the project. The choice between using EmployeeID or RoleType to complete the key represents two different views of the data.

Practical Considerations for Prime Attributes

Understanding prime attributes has direct practical implications for database design and development:

Design Implications

•Index candidates: Prime attributes are natural index candidates since they participate in keys and are frequently used in joins and lookups
•NOT NULL considerations: Prime attributes typically should be NOT NULL since keys cannot contain nulls in most DBMS implementations
•Update frequency: Prime attributes should be relatively stable—frequent changes to key values cascade through foreign key relationships
•Size considerations: For composite keys with multiple prime attributes, consider the total key size for indexing efficiency
•Foreign key choices: When referencing a table with multiple candidate keys, choose based on stability, semantics, and size

Query Design Impact:

Knowing which attributes are prime helps optimize queries:

-- If StudentID, SSN, and Email are all prime (candidate keys)
-- These are equally efficient for exact-match lookups:
SELECT * FROM Student WHERE StudentID = 12345;
SELECT * FROM Student WHERE SSN = '123-45-6789';
SELECT * FROM Student WHERE Email = 'student@university.edu';

-- But for range queries, the primary key's index is typically preferred:
SELECT * FROM Student WHERE StudentID BETWEEN 10000 AND 20000;

-- Non-prime attribute queries may require table scans or secondary indexes:
SELECT * FROM Student WHERE Name LIKE 'John%';  -- Depends on index

Documentation Best Practice

Document all candidate keys, not just the primary key. This makes the prime/non-prime classification explicit and helps developers understand which attributes have key significance even if they're not the designated primary key.

Summary: Prime Attributes

Let's consolidate our understanding of prime attributes and their significance:

Key Takeaways

•Definition clarity — An attribute is prime if it belongs to ANY candidate key; membership in just one is sufficient
•Identification process — First find ALL candidate keys, then compute their union
•Normal form significance — 2NF and 3NF explicitly treat prime and non-prime attributes differently
•BCNF exception — BCNF eliminates the special treatment, requiring superkey determinants regardless of attribute classification
•Practical impact — Prime attributes inform indexing strategies, null constraints, and query optimization

What's Next:

Having understood prime attributes in depth, we now turn to their complement: non-prime attributes. Understanding non-prime attributes is equally essential, as they are the focus of many normalization rules and the source of common design problems like transitive dependencies.

Page Complete

You now understand prime attributes—their formal definition, identification process, and crucial role in normalization theory. This classification is fundamental to evaluating normal forms and making informed schema design decisions.

2 / 5

Loading learning content...

Database Management SystemsFD and Keys

Functional Dependencies and Keys

LevelIntermediate

Duration75 mins

TopicFD and Keys

2 / 5

Prime Attributes

The Attributes That Define Identity

What You Will Learn

Formal Definition of Prime Attributes

Definition:

An attribute A in a relation schema R is a prime attribute if A is a member of at least one candidate key of R.

Equivalently, given the set of candidate keys CK = {K₁, K₂, ..., Kₙ}, the set of prime attributes is:

Prime(R) = K₁ ∪ K₂ ∪ ... ∪ Kₙ = ⋃ Kᵢ

Key Observations:

Membership in ONE key suffices — An attribute need not appear in every candidate key; appearing in just one is enough to be prime
The union over all candidate keys — This means we must first find ALL candidate keys before identifying prime attributes
Primary key members are prime — Since the primary key is a candidate key, all primary key attributes are prime
Alternate key members are also prime — Attributes in non-primary candidate keys are equally prime

Prime vs. Non-Prime Terminology Overview
Term	Definition	Symbol Notation	Example
Prime Attribute	Member of at least one candidate key	A ∈ ⋃CK	StudentID in Student(StudentID, Email, Name)
Non-Prime Attribute	Not a member of any candidate key	A ∉ ⋃CK	Name in the same relation
Key Attribute	Synonym for prime attribute	Same as prime	Used interchangeably in literature
Non-Key Attribute	Synonym for non-prime	Same as non-prime	Used interchangeably in literature

Terminology Variations

Identifying Prime Attributes: A Systematic Process

Identifying prime attributes requires first finding all candidate keys, then taking their union. The process is:

Step 1: Find all candidate keys (using techniques from the previous page)

Step 2: Compute the union of all candidate keys

Step 3: All attributes in this union are prime; all others are non-prime

Let's see this in action with a detailed example.

Relation: Employee(EmpID, Name, Email, Salary, DeptID)

Functional Dependencies:

F = {
    EmpID → Name, Email, Salary, DeptID
}

Step 1: Find all candidate keys

Applying attribute classification:

L-only: {EmpID}
R-only: {Name, Email, Salary, DeptID}
Both: {}
Neither: {}

Core = {EmpID} {EmpID}⁺ = {EmpID, Name, Email, Salary, DeptID} = U ✓

Since core is sufficient and there are no 'Both' attributes to try, there is exactly one candidate key: {EmpID}

Step 2: Union of all candidate keys

⋃CK = {EmpID}

Step 3: Classify attributes

Attribute	In ⋃CK?	Classification
EmpID	Yes	PRIME
Name	No	Non-prime
Email	No	Non-prime
Salary	No	Non-prime
DeptID	No	Non-prime

Result: Prime attributes = {EmpID}, Non-prime attributes = {Name, Email, Salary, DeptID}

Algorithmic Implementation

Given the candidate key finding algorithm from the previous page, computing prime attributes is straightforward. Here's a complete implementation:

Prime Attribute Identification
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
from itertools import combinations
 
def compute_closure(attributes: set, fds: list[tuple[set, set]]) -> set:
    """Compute attribute closure X+ under FDs F."""
    closure = set(attributes)
    changed = True
    while changed:
        changed = False
        for lhs, rhs in fds:
            if lhs.issubset(closure) and not rhs.issubset(closure):
                closure = closure.union(rhs)
                changed = True
    return closure
 
 
def find_all_candidate_keys(all_attrs: set, fds: list[tuple[set, set]]) -> list[set]:
    """Find all candidate keys using attribute classification."""
    # Classify attributes
    left_side = set()
    right_side = set()
    for lhs, rhs in fds:
        left_side.update(lhs)
        right_side.update(rhs)
    
    left_only = left_side - right_side
    right_only = right_side - left_side
    both = left_side & right_side
    neither = all_attrs - left_side - right_side
    
    core = left_only | neither
    
    if compute_closure(core, fds) == all_attrs:
        return [core] if core else [all_attrs]
    
    candidate_keys = []
    
    for size in range(1, len(both) + 1):
        for subset in combinations(both, size):
            candidate = core | set(subset)
            
            if compute_closure(candidate, fds) != all_attrs:
                continue
            
            # Check minimality
            is_minimal = True
            for existing in candidate_keys:
                if existing < candidate:
                    is_minimal = False
                    break
            
            if is_minimal:
                for attr in subset:
                    smaller = core | (set(subset) - {attr})
                    if compute_closure(smaller, fds) == all_attrs:
                        is_minimal = False
                        break
            
            if is_minimal:
                candidate_keys.append(candidate)
    
    return candidate_keys if candidate_keys else [all_attrs]
 
 
def find_prime_attributes(all_attrs: set, fds: list[tuple[set, set]]) -> tuple[set, set]:
    """
    Find prime and non-prime attributes.
    
    Returns:
        Tuple of (prime_attributes, non_prime_attributes)
    """
    candidate_keys = find_all_candidate_keys(all_attrs, fds)
    
    # Prime = union of all candidate keys
    prime = set()
    for key in candidate_keys:
        prime.update(key)
    
    # Non-prime = all attributes minus prime
    non_prime = all_attrs - prime
    
    return prime, non_prime
 
 
def analyze_relation(name: str, all_attrs: set, fds: list[tuple[set, set]]):
    """Complete analysis of a relation's key structure."""
    print(f"\n{'='*60}")
    print(f"Relation: {name}")
    print(f"Attributes: {sorted(all_attrs)}")
    print(f"\nFunctional Dependencies:")
    for lhs, rhs in fds:
        print(f"  {sorted(lhs)} → {sorted(rhs)}")
    
    candidate_keys = find_all_candidate_keys(all_attrs, fds)
    print(f"\nCandidate Keys: {[sorted(k) for k in candidate_keys]}")
    
    prime, non_prime = find_prime_attributes(all_attrs, fds)
    print(f"\nPrime Attributes: {sorted(prime)}")
    print(f"Non-Prime Attributes: {sorted(non_prime)}")
    print(f"{'='*60}")
 
 
# Example analyses
if __name__ == "__main__":
    # Example 1: Single key
    analyze_relation(
        "Employee",
        {"EmpID", "Name", "Email", "Salary", "DeptID"},
        [
            ({"EmpID"}, {"Name", "Email", "Salary", "DeptID"})
        ]
    )
    
    # Example 2: Multiple single-attribute keys
    analyze_relation(
        "Student",
        {"StudentID", "SSN", "Email", "Name", "Major"},
        [
            ({"StudentID"}, {"SSN", "Email", "Name", "Major"}),
            ({"SSN"}, {"StudentID", "Email", "Name", "Major"}),
            ({"Email"}, {"StudentID", "SSN", "Name", "Major"})
        ]
    )
    
    # Example 3: Composite key
    analyze_relation(
        "Enrollment",
        {"StudentID", "CourseID", "Semester", "Grade", "InstructorID"},
        [
            ({"StudentID", "CourseID", "Semester"}, {"Grade", "InstructorID"}),
            ({"CourseID", "Semester"}, {"InstructorID"})
        ]
    )

Prime Attributes in Normal Form Definitions

The prime/non-prime distinction is not merely a classification—it's central to the very definitions of normal forms. Let's examine how this classification shapes normalization theory:

Second Normal Form (2NF):

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key.

This definition explicitly treats prime and non-prime attributes differently:

Prime attributes are "exempt" from the 2NF partial dependency check
Only non-prime attributes can cause 2NF violations

Example of 2NF Analysis:

Relation: OrderItem(OrderID, ProductID, Quantity, ProductName, UnitPrice)

FDs:
  {OrderID, ProductID} → Quantity, ProductName, UnitPrice
  ProductID → ProductName, UnitPrice

Candidate Key: {OrderID, ProductID}
Prime: {OrderID, ProductID}
Non-Prime: {Quantity, ProductName, UnitPrice}

2NF Check: Is every non-prime fully dependent on the key?

Quantity: Depends on full key {OrderID, ProductID} ✓
ProductName: Depends on partial key {ProductID} only ✗ VIOLATION
UnitPrice: Depends on partial key {ProductID} only ✗ VIOLATION

Not in 2NF — ProductName and UnitPrice partially depend on the key.

Why Prime Attributes Are Exempt from 2NF

Third Normal Form (3NF):

A relation is in 3NF if, for every non-trivial FD X → A, at least one of the following holds:

X is a superkey, OR

A is a prime attribute

Again, prime attributes receive special treatment—they can appear on the right side of non-superkey FDs without violating 3NF!

Example of 3NF Analysis:

Relation: Employee(EmpID, DeptID, DeptName, DeptHead)

FDs:
  EmpID → DeptID, DeptName, DeptHead
  DeptID → DeptName, DeptHead

Candidate Key: {EmpID}
Prime: {EmpID}
Non-Prime: {DeptID, DeptName, DeptHead}

3NF Check: For each non-trivial FD X → A:

EmpID → DeptID: EmpID is superkey ✓
EmpID → DeptName: EmpID is superkey ✓
EmpID → DeptHead: EmpID is superkey ✓
DeptID → DeptName: Is DeptID a superkey? No. Is DeptName prime? No. ✗ VIOLATION
DeptID → DeptHead: Is DeptID a superkey? No. Is DeptHead prime? No. ✗ VIOLATION

Not in 3NF — The FDs DeptID → DeptName and DeptID → DeptHead violate 3NF because DeptID is not a superkey and DeptName/DeptHead are not prime.

Prime Attributes in Normal Form Definitions
Normal Form	Role of Prime Attributes	Role of Non-Prime Attributes
2NF	Exempt from partial dependency check	Must be fully dependent on all candidate keys
3NF	Can be on RHS of non-superkey FDs	Cannot be transitively dependent on key
BCNF	No special treatment—must follow superkey rule	No special treatment—must follow superkey rule

BCNF Eliminates the Prime Exception

Prime Attributes and Functional Dependencies

Prime attributes participate in interesting dependency patterns that warrant careful study:

1. Dependencies Among Prime Attributes

When multiple candidate keys exist, there are often dependencies among prime attributes:

Relation: Student(StudentID, SSN, Email, Name)

Candidate Keys: {StudentID}, {SSN}, {Email}
Prime: {StudentID, SSN, Email}
Non-Prime: {Name}

Implied FDs among primes:
  StudentID → SSN
  StudentID → Email
  SSN → StudentID
  SSN → Email
  Email → StudentID
  Email → SSN

These inter-key dependencies are valid but don't violate any normal form because the determinants are superkeys (single-attribute keys in this case).

2. Prime Attributes as Determinants

Prime attributes that are part of one key but not alone sufficient to be a key can be determinants of non-prime attributes:

Relation: Enrollment(StudentID, CourseID, Semester, Grade, RoomNumber)

FDs:
  {StudentID, CourseID, Semester} → Grade, RoomNumber
  {CourseID, Semester} → RoomNumber

Candidate Key: {StudentID, CourseID, Semester}
Prime: {StudentID, CourseID, Semester}
Non-Prime: {Grade, RoomNumber}

Here, {CourseID, Semester} determines RoomNumber. This is interesting:

CourseID and Semester are prime (part of the key)
But {CourseID, Semester} is NOT a superkey (doesn't determine StudentID or Grade)
This violates 3NF because RoomNumber is non-prime and depends on a non-superkey

The fact that the determinant consists entirely of prime attributes doesn't exempt it from 3NF analysis!

Prime ≠ Superkey

3. Overlapping Candidate Keys

When candidate keys overlap, the shared attributes are particularly interesting:

Relation: Assignment(ProjectID, EmployeeID, RoleType, StartDate)

FDs:
  {ProjectID, EmployeeID} → RoleType, StartDate
  {ProjectID, RoleType} → EmployeeID, StartDate

Candidate Keys: {ProjectID, EmployeeID}, {ProjectID, RoleType}
Prime: {ProjectID, EmployeeID, RoleType}  (union)
Non-Prime: {StartDate}

Note: ProjectID appears in both keys — it's "doubly prime"

Practical Considerations for Prime Attributes

Understanding prime attributes has direct practical implications for database design and development:

Design Implications

•Index candidates: Prime attributes are natural index candidates since they participate in keys and are frequently used in joins and lookups
•NOT NULL considerations: Prime attributes typically should be NOT NULL since keys cannot contain nulls in most DBMS implementations
•Update frequency: Prime attributes should be relatively stable—frequent changes to key values cascade through foreign key relationships
•Size considerations: For composite keys with multiple prime attributes, consider the total key size for indexing efficiency
•Foreign key choices: When referencing a table with multiple candidate keys, choose based on stability, semantics, and size

Query Design Impact:

Knowing which attributes are prime helps optimize queries:

-- If StudentID, SSN, and Email are all prime (candidate keys)
-- These are equally efficient for exact-match lookups:
SELECT * FROM Student WHERE StudentID = 12345;
SELECT * FROM Student WHERE SSN = '123-45-6789';
SELECT * FROM Student WHERE Email = 'student@university.edu';

-- But for range queries, the primary key's index is typically preferred:
SELECT * FROM Student WHERE StudentID BETWEEN 10000 AND 20000;

-- Non-prime attribute queries may require table scans or secondary indexes:
SELECT * FROM Student WHERE Name LIKE 'John%';  -- Depends on index

Documentation Best Practice

Summary: Prime Attributes

Let's consolidate our understanding of prime attributes and their significance:

Key Takeaways

•Definition clarity — An attribute is prime if it belongs to ANY candidate key; membership in just one is sufficient
•Identification process — First find ALL candidate keys, then compute their union
•Normal form significance — 2NF and 3NF explicitly treat prime and non-prime attributes differently
•BCNF exception — BCNF eliminates the special treatment, requiring superkey determinants regardless of attribute classification
•Practical impact — Prime attributes inform indexing strategies, null constraints, and query optimization

What's Next:

Page Complete

2 / 5