Relational Model in DBMS

Relational Model Concepts

The Relational Model, proposed by E.F. Codd in 1970, organizes data into relations (tables). It is the foundation of all relational database systems (MySQL, PostgreSQL, Oracle, SQL Server).

Formal Term	Common Term	Description
Relation	Table	A set of tuples with the same attributes
Tuple	Row / Record	A single data entry in a relation
Attribute	Column / Field	A named property of a relation
Domain	Data Type	Set of allowed values for an attribute
Degree	Number of columns	Number of attributes in a relation
Cardinality	Number of rows	Number of tuples in a relation
Schema	Table structure	Name + list of attributes with domains

Keys in Relational Model

Key Type	Description	Example
Super Key	Any set of attributes that uniquely identifies a tuple	{Student_ID}, {Student_ID, Name}, {Email}
Candidate Key	Minimal super key (no redundant attributes)	{Student_ID}, {Email}
Primary Key	Chosen candidate key to uniquely identify tuples	Student_ID
Alternate Key	Candidate keys not chosen as primary key	Email (if Student_ID is PK)
Foreign Key	Attribute(s) referencing the primary key of another relation	Dept_ID in Employee references Department
Composite Key	Primary key made of multiple attributes	(Student_ID, Course_ID) in Enrollment

Relational Algebra

Relational Algebra is a procedural query language that provides a set of operations to manipulate relations. It forms the theoretical foundation for SQL.

Operation	Symbol	Description	SQL Equivalent
Select	σ (sigma)	Filters rows based on a condition	WHERE clause
Project	π (pi)	Selects specific columns	SELECT columns
Union	∪	Combines tuples from two relations (removes duplicates)	UNION
Intersection	∩	Returns tuples in both relations	INTERSECT
Difference	−	Returns tuples in first but not second	EXCEPT / MINUS
Cartesian Product	×	Combines every tuple from both relations	CROSS JOIN
Natural Join	⋈	Joins on common attributes	NATURAL JOIN
Rename	ρ (rho)	Renames a relation or attribute	AS alias

Relational Algebra Examples

Consider two relations:

EMPLOYEE (Emp_ID, Name, Dept_ID, Salary)

Emp_ID	Name	Dept_ID	Salary
1	Alice	D1	60000
2	Bob	D2	75000
3	Charlie	D1	55000
4	Diana	D3	80000

Select employees in D1: σ_{Dept_ID='D1'}(EMPLOYEE) → {Alice, Charlie}
Project names and salaries: π_{Name, Salary}(EMPLOYEE) → {(Alice,60000), (Bob,75000), ...}
Employees with salary > 60000: σ_Salary>60000(EMPLOYEE) → {Bob, Diana}
Employees in D1 with salary > 55000: σ_{Dept_ID='D1' AND Salary>55000}(EMPLOYEE) → {Alice}

Integrity Constraints

Domain Constraint: Each attribute value must be from its defined domain (data type). E.g., Age must be a positive integer.
Entity Integrity: Primary key cannot be NULL. Every tuple must be uniquely identifiable.
Referential Integrity: A foreign key value must either be NULL or match an existing primary key value in the referenced relation.
Key Constraint: No two tuples can have the same primary key value.

Previous Next