Relational Database Model: Concepts, Constraints, and Keys

Posted on Oct 14, 2026 in Business Sciences

DBMS Unit 2: Relational Database Model

1. The Relational Model

The relational model is a data model that represents a database as a collection of relations (tables).

Each relation consists of:

• Rows → Tuples

• Columns → Attributes

The relational model was proposed by E. F. Codd in 1970.

It is categorized as an implementation/representational data model.

Examples of Relational DBMS

• MySQL

• Oracle

• PostgreSQL

• SQL Server

Basic Structure

STUDENT
+---------+---------+--------+
| Roll_No | Name    | Branch |
+---------+---------+--------+
| 101     | Amit    | CSE    |
| 102     | Rahul   | IT     |
| 103     | Priya   | CSE    |
+---------+---------+--------+

Here:

• STUDENT → Relation

• Roll_No, Name, Branch → Attributes

• Each individual row → Tuple

2. Relational Model Terminology

2.1 Relation

A relation is a table with a unique name consisting of rows and columns.

Example

STUDENT(Roll_No, Name, Branch)

Here:

STUDENT = Relation

2.2 Tuple

A tuple represents a single row in a relation.

Example

(101, "Amit", "CSE")

This represents one tuple of the STUDENT relation.

Remember

Row = Tuple

2.3 Attribute

An attribute represents a named column in a relation. It describes a property or characteristic of an entity.

Example

Roll_No
Name
Branch

Remember

Column = Attribute

2.4 Domain

A domain is the set of all permissible atomic values that an attribute can hold.

Example

Age = {1, 2, 3, ..., 120}

The domain of Age contains all valid, permitted age values.

3. Mathematical Representation of Relations

Suppose a relation has attributes:

A1, A2, A3, A4, A5

Then:

R(A1, A2, A3, A4, A5)

represents relation R.

If their respective domains are:

D1, D2, D3, D4, D5

then the relation is a subset of their Cartesian product:

r(R) ⊆ D1 × D2 × D3 × D4 × D5

Important Theoretical Concept

Remember this foundational definition:

A relation is mathematically defined as a subset of the Cartesian product of the domains of its attributes.

4. Degree of a Relation

The degree of a relation refers to the total number of attributes (columns) in the relation.

Example

STUDENT(Roll_No, Name, Branch, Age)

Number of attributes = 4.

Therefore:

Degree = 4

Exam Shortcut

Degree = Total Number of Columns

5. Cardinality of a Relation

The cardinality of a relation refers to the total number of tuples (rows) currently in the relation.

Example

If STUDENT contains:

50 rows

then:

Cardinality = 50

Exam Shortcut

Cardinality = Total Number of Rows

Key Difference

Summary:

Degree → Attributes
Cardinality → Tuples

6. Database Intension and Extension

Database intension refers to the schema or structure of the database.

It defines:

What the database looks like, rather than the actual stored data.

Characteristics:

• Describes database structure and constraints

• Rarely changes over time

• Also termed the database schema

Example

STUDENT(
    Roll_No,
    Name,
    Branch,
    Age
)

This schema definition is the intension.

The actual student records populated inside the table represent the extension (or database instance).

7. Database Keys

A key is an attribute or combination of attributes that uniquely identifies each tuple in a relation.

Types of Keys

1. Super Key

2. Candidate Key

3. Primary Key

4. Alternate Key

5. Foreign Key

8. Super Key

A super key is any set of one or more attributes that taken collectively allows us to identify a record/tuple uniquely in a table.

Consider the relation schema:

STUDENT(
    Roll,
    Name,
    Sex,
    Age,
    Address,
    Class,
    Section
)

Possible super keys include:

(Roll, Name, Sex, Age, Address, Class, Section)
(Class, Section, Roll)
(Class, Section, Roll, Sex)
(Name, Address)

Important concept:

A super key can contain redundant or extra attributes and still uniquely identify a tuple.

9. Candidate Key

A minimal super key is called a candidate key.

It contains the minimum number of attributes required to uniquely identify a tuple without any redundant attributes.

Example:

STUDENT(
    Roll,
    Name,
    Sex,
    Age,
    Address,
    Class,
    Section
)

Possible candidate keys:

(Class, Section, Roll)
(Name, Address)

Super Key vs. Candidate Key

Golden Rule

Candidate Key = Minimal Super Key

10. Properties of Candidate Keys

Essential properties to remember:

1. All attributes within the key are necessary for uniqueness.

2. All attributes together are sufficient to identify a tuple uniquely.

3. Removing any attribute destroys the uniqueness property.

4. Candidate key values must be strictly unique across all rows.

5. A candidate key cannot contain NULL values.

6. A relation can possess multiple candidate keys.

7. Attributes that participate in any candidate key are known as prime attributes.

11. Primary Key

A primary key is a candidate key selected by the database designer as the principal means of uniquely identifying tuples within a relation.

Candidate Keys
      ↓
Database Designer selects one
      ↓
Primary Key

Example

Suppose candidate keys are:

Student_ID
Email

If the designer chooses Student_ID as the primary identifier:

Student_ID = Primary Key

and:

Email = Alternate Key

12. Properties of Primary Keys

A primary key must satisfy the following rules:

1. Cannot contain NULL (Entity Integrity).

2. Must be completely unique across all tuples.

3. Must be assigned whenever a new record is inserted.

4. A relation can have only one primary key.

Additionally, primary key values should remain stable and cannot be updated arbitrarily.

Exam Definition

A primary key is a candidate key chosen by the database designer to uniquely identify every tuple in a relation.

13. Alternate Key

All candidate keys that are not chosen as the primary key are designated as alternate keys (or secondary keys).

Example

Suppose the candidate keys are:

Candidate Keys:
1. Student_ID
2. Email

If:

Student_ID → Primary Key

then:

Email → Alternate Key

Selection Process

Candidate Keys
       ↓
One selected
       ↓
Primary Key

Remaining candidate keys
       ↓
Alternate Keys

14. Foreign Key

A foreign key is an attribute or a collection of attributes in one relation whose values match the primary key (or candidate key) of another referenced relation.

It establishes a logical relationship and maintains referential integrity between tables.

Example

Department Table

DEPARTMENT
+---------+--------------+
| dept_no | dept_name    |
+---------+--------------+
| D01     | Computer Sci |
| D02     | Mathematics  |
| D03     | Physics      |
+---------+--------------+

Primary Key:

dept_no

Teacher Table

TEACHER
+------+--------+-------+---------+
| t_no | t_name | t_age | dept_no |
+------+--------+-------+---------+
| T101 | Ravi   | 35    | D01     |
| T102 | Anita  | 29    | D02     |
| T103 | Suresh | 42    | D01     |
+------+--------+-------+---------+

Here:

Department.dept_no → Primary Key
Teacher.dept_no    → Foreign Key

15. Properties of Foreign Keys

Key characteristics of foreign keys:

1. A foreign key references a primary key or candidate key in another table.

2. Its values must match values in the referenced table or be NULL where permitted.

3. A foreign key can have a different attribute name from the referenced primary key.

4. A foreign key can contain NULL values unless explicitly restricted.

5. Foreign key values do not need to be unique.

6. Multiple rows can share the exact same foreign key value.

7. The referenced table is called the master table or primary table.

8. The referencing table is called the foreign table or child table.

16. Summary of All Database Keys

Memory Hierarchy

SUPER KEY
  ↓ (remove unnecessary attributes)
CANDIDATE KEY
  ↓ (select primary)
PRIMARY KEY
  ↓ (remaining candidates)
ALTERNATE KEY

FOREIGN KEY
  ↓ (links distinct relations)

17. Relational Integrity Constraints

Relational constraints are sets of rules enforced on database contents and operations to ensure data validity, consistency, and reliability.

There are four major types of constraints:

1. Domain Constraint

2. Key Constraint

3. Entity Integrity Constraint

4. Referential Integrity Constraint

18. Domain Constraints

A domain constraint states that each attribute in a tuple must hold an atomic value chosen from that attribute’s designated domain.

Atomic implies:

The smallest indivisible unit of data.

Consequently, composite and multivalued attributes cannot be stored directly as standard relational attribute domains.

Example

If:

Age domain = integers 1 to 120

Then:

Age = 20  (✓ Valid)
Age = 50  (✓ Valid)
Age = 150 (✗ Invalid: out of range)
Age = ABC (✗ Invalid: wrong data type)

19. Key Constraints

A key constraint ensures that candidate and primary key values uniquely identify each tuple across the relation.

For a primary key:

All primary-key values must be distinct across all tuples.

Hence, it is also known as the uniqueness constraint.

Invalid Example

STU_ID   Name
S001     Akshay
S001     Abhishek
S003     Shashank

This violates the key constraint because duplicate key value S001 exists.

20. Entity Integrity Constraints

The entity integrity rule states:

No attribute that forms part of the primary key can contain a NULL value.

Invalid Table

STU_ID     Name
S001       Akshay
S002       Abhishek
S003       Shashank
NULL       Rahul

This violates entity integrity because a tuple contains NULL in its primary key attribute (STU_ID).

Remember

PRIMARY KEY = UNIQUE + NOT NULL

21. Referential Integrity Constraints

Referential integrity applies whenever a foreign key references a primary key across relations.

It states:

A foreign-key value must either exist as a valid primary-key value in the referenced relation, or be entirely NULL (where permitted).

Example

Department values:

D01
D02
D03

Teacher assignments:

T101 → D01 (✓ Valid)
T102 → D02 (✓ Valid)
T103 → D01 (✓ Valid)
T104 → D05 (✗ Invalid: D05 does not exist in Department)

22. Constraints Revision and Summary

Relational Constraints
├
└── Domain Constraint
│     └── Valid, atomic values
│
└── Key Constraint
│     └── Unique key identifiers
│
└── Entity Integrity
│     └── Primary key ≠ NULL
│
└── Referential Integrity
      └── Foreign key must reference a valid primary key

Comparison Matrix

23. Converting ER Models to Relational Models

Before implementing an ER diagram in a relational DBMS, it must be mapped into relational tables.

There are 7 major conversion rules:

1. Strong Entity → Relation

2. Weak Entity → Relation

3. 1:1 Relationship → Relation

4. 1:M Relationship → Relation

5. M:N Relationship → Relation

6. Multivalued Attribute → Relation

7. M-ary Relationship → Relation

24. Rule 1: Strong Entity to Relation

For every strong entity type:

Create a distinct relation (table).

Mapping Principles

• Each simple attribute becomes a column.

• Composite attributes are flattened into their simple component attributes.

• Multivalued attributes are handled in a separate table.

• The entity’s key attribute becomes the primary key of the relation.

Example

ER Entity:

STUDENT

Roll_No
Name
Address
Marks

Suppose:

Name = FName + LName

Resulting relation:

STUDENT(
    Roll_No,
    FName,
    LName,
    Marks
)

Primary key:

Roll_No

25. Rule 2: Weak Entity to Relation

A weak entity lacks a complete primary key of its own.

Conversion procedure:

1. Create a dedicated relation for the weak entity.

2. Include all simple attributes of the weak entity.

3. Include the primary key of the identifying (owner/strong) entity.

4. The owner’s primary key serves as a foreign key in the weak entity relation.

5. Combine the owner’s primary key and the partial key (discriminator) of the weak entity.

6. This composite attribute set forms the primary key of the weak entity relation.

Example

Strong Entity:

EMPLOYEE(E_No, E_Name)

Weak Entity:

DEPENDENT(DName, Age)
Partial key: DName

Resulting relation:

DEPENDENT(
    E_No,
    DName,
    Age
)

Primary Key:

(E_No, DName)

Foreign Key:

E_No → EMPLOYEE(E_No)

26. Rule 3: 1:1 Binary Relationship

For a one-to-one (1:1) relationship, include the primary key of one relation as a foreign key in the other relation.

Generally, place the foreign key in the relation with total participation to avoid excessive NULL values.

Example

EMPLOYEE ─── MANAGES ─── DEPARTMENT

Resulting relation:

DEPARTMENT(
    D_No,
    DName,
    Manager_No
)

Here:

Manager_No → Foreign Key referencing EMPLOYEE(E_No)

Handling Relationship Attributes

If the relationship has attributes (e.g., Start_Date for MANAGES):

DEPARTMENT(
    D_No,
    DName,
    Manager_No,
    Start_Date
)

27. Special Case: 1:1 Total Participation

If both participating entity types exhibit total participation in a 1:1 relationship, the two entities and the relationship can be merged into a single table.

Exam Point

1:1 Relationship + Total Participation on Both Sides
                    ↓
        Merge into a Single Relation

28. Rule 4: 1:N Binary Relationship

For a one-to-many (1:N) relationship:

Place the primary key of the “1” side as a foreign key in the “N” (many) side relation.

Golden Rule

1 side ─────────→ N side

PK of 1 side
      ↓
FK in N side

Example

DEPARTMENT (1) ─────── (N) EMPLOYEE

Department:

DEPARTMENT(D_No, DName)

Employee:

EMPLOYEE(E_No, EName, D_No)

Here:

Employee.D_No → Foreign Key referencing DEPARTMENT(D_No)

Attributes on Relationships

Any attributes on a 1:N relationship are placed directly in the relation on the many (N) side.

29. Rule 5: M:N Binary Relationship

For a many-to-many (M:N) relationship, a foreign key cannot simply be placed into either participating relation without introducing severe redundancy.

Therefore:

Create a new relation (junction table).

The junction table contains:

• Primary key of the first entity → Foreign Key

• Primary key of the second entity → Foreign Key

• Any descriptive attributes of the relationship

The combination of both foreign keys forms the composite primary key.

Example

STUDENT (M) ─── ENROLLS ─── (N) COURSE

Tables created:

STUDENT(S_No, Name)
COURSE(C_No, CName)
ENROLLS(S_No, C_No)

Primary Key:

(S_No, C_No)

Foreign Keys:

S_No → STUDENT(S_No)
C_No → COURSE(C_No)

If the relationship contains an attribute such as Grade:

ENROLLS(
    S_No,
    C_No,
    Grade
)

30. Rule 6: Multivalued Attributes

A multivalued attribute cannot be accommodated directly in a single normalized relational column.

Procedure:

Create a dedicated relation for the multivalued attribute.

Include:

• The primary key of the parent entity (as a Foreign Key)

• The multivalued attribute itself

The combination of both attributes forms the primary key.

Example

Entity:

STUDENT(S_No, FName, LName)

Multivalued attribute: Mobile_Number

Create relation:

MOBILE_NUMBER(
    S_No,
    Mobile_Number
)

Primary Key:

(S_No, Mobile_Number)

Foreign Key:

S_No → STUDENT(S_No)

31. Rule 7: M-ary Relationships

An M-ary relationship involves more than two entity types simultaneously.

Structure:

A └───&boxdld
B ├──── R
C ┌───&boxuld

Conversion procedure:

• Create a separate relation representing the relationship R.

• Include the primary keys of all participating entity types as foreign keys.

• Include any relationship attributes.

• The composite of all foreign keys forms the primary key of the new relation.

Example

Given relations A(A1, A2), B(B1, B2), and C(C1, C2) participating in R:

R(
    A1,
    B1,
    C1,
    Relationship_Attributes
)

Primary Key:

(A1, B1, C1)

Foreign Keys:

A1 → A(A1)
B1 → B(B1)
C1 → C(C1)

32. ER-to-Relational Mapping Summary

Quick reference for conversion rules:

STRONG ENTITY
       ↓
Create 1 table (PK = Entity PK)

WEAK ENTITY
       ↓
Create 1 table (Owner PK + Partial Key = PK; Owner PK = FK)

1 : 1 RELATIONSHIP
       ↓
Place FK in one relation (preferably total participation side)
Include relationship attributes

1 : N RELATIONSHIP
       ↓
PK of 1-side → FK on N-side
Include relationship attributes on N-side

M : N RELATIONSHIP
       ↓
Create a NEW junction table
PK1 + PK2 = Composite PK (Both act as FKs)

MULTIVALUED ATTRIBUTE
       ↓
Create a NEW table
Entity PK + Multivalued Attribute = Composite PK

M-ARY RELATIONSHIP
       ↓
Create a NEW relationship table
Participating PKs → Foreign Keys
Combined Foreign Keys → Composite PK

33. Minimum Number of Tables in ER Mapping

A standard exam problem involves calculating the minimum relations needed:

Find the minimum number of tables required to represent the given ER diagram in the relational model.

These problems assess accurate application of table-merging rules (such as merging 1:1 total participation or folding 1:N relations).

For instance, an enterprise banking model typically reduces to 6 tables:

ACCOUNT
BRANCH
LOAN
BORROWER
CUSTOMER
DEPOSITOR

34. Banking Enterprise Mapping Example

The final schemas generated from the standard banking ER diagram are:

1. ACCOUNT

Account(Ac_no, Balance, b_name)

2. BRANCH

Branch(b_name, b_city, Assets)

3. LOAN

Loan(L_no, Amt, b_name)

4. BORROWER

Borrower(C_name, L_no)

5. CUSTOMER

Customer(C_name, C_street, C_city)

6. DEPOSITOR

Depositor(C_name, Ac_no)

Total minimum tables required:

6 tables