DatabaseRelational Model

🔗 Relational Model

Understand relations, keys, constraints, and relational algebra — the foundation of RDBMS.

Relation

A table with rows (tuples) and columns (attributes). Each relation has a unique name.

Tuple

A single row in a relation representing an entity or relationship instance.

Attribute

A named column of a relation describing a property of the entity.

Domain

The set of allowable values for an attribute (e.g., integer, string, date).

Key

A set of attributes that uniquely identifies a tuple. Includes candidate, primary, foreign keys.

Relationship

An association between relations. Implemented via foreign keys.

Properties of a Relation

•Each relation has a unique name within the database schema

•Each cell contains exactly one atomic value (1NF compliant)

•Each attribute has a distinct name

•The order of attributes is irrelevant

•The order of tuples is irrelevant

•All tuples are distinct (no duplicate rows)

•Every attribute value comes from its defined domain

Types of Keys

Key Type	Description	Example
Super Key	Set of attributes that uniquely identifies a tuple	{StudentID}, {StudentID, Name}
Candidate Key	Minimal super key (no unnecessary attributes)	{StudentID}, {Email}
Primary Key	Selected candidate key for the relation	StudentID
Foreign Key	References primary key of another relation	CourseID in Enrollment table
Composite Key	Primary key with two or more attributes	{StudentID, CourseID}
Alternate Key	Candidate keys not chosen as primary key	Email (if StudentID is primary)

Relational Algebra Operations

SELECT (σ)

Filter rows based on condition

σ salary > 50000 (Employee)

PROJECT (π)

Select specific columns

π name, salary (Employee)

UNION (∪)

Combine rows from two relations

Students ∪ Teachers

INTERSECT (∩)

Rows common to both relations

Students ∩ Teachers

DIFFERENCE (−)

Rows in first but not second

Students − Teachers

JOIN (⋈)

Combine related rows from two relations

Employee ⋈ Department

DIVISION (÷)

Rows in one relation matching all in another

R1 ÷ R2

RENAME (ρ)

Rename relation or attribute

ρ new_name (Relation)

Interview Questions

Q1: What is a relation in the relational model?

A: A relation is a table consisting of rows (tuples) and columns (attributes). Each relation represents an entity set or relationship.

Q2: What are the properties of a relation?

A: Unique name, atomic values, distinct attribute names, unordered attributes and tuples, all tuples distinct, values from defined domains.

Q3: What is the difference between a primary key and a foreign key?

A: Primary key uniquely identifies each tuple in its relation. Foreign key references the primary key of another relation to establish relationships.

Q4: What is referential integrity?

A: A foreign key value must either match a primary key value in the referenced relation or be NULL. Ensures data consistency across relations.

Q5: What are the five basic operations in relational algebra?

A: SELECT (σ) filters rows, PROJECT (π) selects columns, UNION (∪) combines relations, DIFFERENCE (−) subtracts relations, and RENAME (ρ) renames.