Relational Model Keys, Tuples, Relational Algebra: Tutorial, Examples, FAQs & Interview Tips

Relational Model in DBMS

The relational model is a database model that organizes data into relations, commonly called tables. It was proposed by Dr. E. F. Codd in 1970 and became the foundation of modern relational database systems such as MySQL, PostgreSQL, Oracle, SQL Server, SQLite, and MariaDB.

In the relational model, data is stored in rows and columns. Each row represents one record, and each column represents one attribute. Relationships between tables are represented using keys such as primary keys and foreign keys.

Simple idea: A college database can store students in a students table, courses in a courses table, and student-course enrollment details in an enrollments table. Keys connect these tables in a meaningful way.

Why Relational Model is Important

It stores data in a simple table format that is easy to understand.
It provides strong data integrity using keys and constraints.
It supports powerful querying using SQL.
It reduces data duplication when tables are designed correctly.
It supports relationships among data using primary and foreign keys.
It is based on mathematical concepts such as sets and relations.

Relational Model Terminology

Formal Term	Common Term	Description	Example
Relation	Table	A set of rows with the same attributes.	students, courses, departments.
Tuple	Row or record	One complete data item in a relation.	One student row.
Attribute	Column or field	A named property of a relation.	student_id, name, email.
Domain	Allowed values or data type	The set of valid values for an attribute.	Age must be a positive integer.
Degree	Number of columns	Total number of attributes in a relation.	students has 4 columns, so degree is 4.
Cardinality	Number of rows	Total number of tuples in a relation.	students has 500 rows, so cardinality is 500.
Relation Schema	Table structure	Relation name with attributes and domains.	Student(Student_ID, Name, Email).
Relation Instance	Current table data	The actual set of rows at a particular time.	All current rows in students table.

Example of a Relation

The following table is a relation named STUDENT. It has four attributes and three tuples.

student_id	name	email	department
101	Asha Sharma	asha@example.com	BCA
102	Rahul Verma	rahul@example.com	BSc IT
103	Neha Singh	neha@example.com	BCA

Here, the degree is 4 because there are four attributes. The cardinality is 3 because there are three tuples.

Properties of Relations

A relation is not just any table. In the relational model, relations follow specific rules.

Each relation has a unique name within the database.
Each attribute in a relation has a unique name.
Each cell contains a single atomic value, not a list or repeating group.
All values in a column come from the same domain.
Duplicate tuples are not allowed in a pure relation.
The order of rows has no meaning in the relational model.
The order of columns has no meaning if attributes are referenced by name.
Null may be used when a value is unknown, unavailable, or not applicable.

Relation Schema and Database Schema

A relation schema describes the structure of one relation. A database schema describes the structure of the entire database, including all relations, attributes, keys, and constraints.

Term	Meaning	Example
Relation Schema	Structure of a single relation.	Student(Student_ID, Name, Email, Dept_ID)
Relation Instance	Actual rows in a relation at a particular time.	Rows currently stored in Student table.
Database Schema	Structure of all relations in a database.	Student, Course, Department, Enrollment schemas.
Database Instance	Actual data stored in the entire database at a particular time.	All current rows in all database tables.

Keys in Relational Model

Keys are attributes or sets of attributes used to identify tuples and define relationships between relations. They are central to the relational model.

Key Type	Description	Example
Super Key	Any attribute set that uniquely identifies a tuple.	{Student_ID}, {Email}, {Student_ID, Name}
Candidate Key	A minimal super key with no unnecessary attribute.	{Student_ID}, {Email}
Primary Key	The candidate key chosen to identify tuples.	Student_ID
Alternate Key	Candidate key not chosen as the primary key.	Email, if Student_ID is primary key.
Foreign Key	Attribute that references the primary key of another relation.	Dept_ID in Student references Department.
Composite Key	Key made of more than one attribute.	(Student_ID, Course_ID) in Enrollment.
Surrogate Key	Artificial key created only for identification.	Auto-increment student_id.
Natural Key	Real-world attribute used as a key.	Email, Aadhaar number, employee code.

Exam tip: Every candidate key is a super key, but every super key is not a candidate key. A candidate key must be minimal.

Super Key vs Candidate Key vs Primary Key

Point	Super Key	Candidate Key	Primary Key
Uniquely identifies row?	Yes	Yes	Yes
Must be minimal?	No	Yes	Yes, because it is selected from candidate keys.
How many possible?	Many	One or more	Only one selected per relation.
Example	{Student_ID, Name}	{Student_ID}	Student_ID

Integrity Constraints

Integrity constraints are rules that ensure the correctness, validity, and consistency of data in a relational database.

Constraint	Meaning	Example
Domain Constraint	Attribute value must belong to its allowed domain.	Age must be an integer greater than 0.
Key Constraint	No two tuples can have the same candidate key value.	Two students cannot have the same Student_ID.
Entity Integrity	Primary key cannot be null.	Student_ID must not be null.
Referential Integrity	Foreign key must match an existing primary key or be null if allowed.	Student.Dept_ID must exist in Department.Dept_ID.
User-Defined Constraint	Business rule defined by the organization.	Course credits must be between 1 and 6.

Relational Schema Example

A relational schema can be written in compact form before writing SQL.

college-schema.txt

Department(Dept_ID, Dept_Name, Location)
Student(Student_ID, Name, Email, Dept_ID)
Course(Course_ID, Course_Name, Credits, Dept_ID)
Enrollment(Student_ID, Course_ID, Semester, Grade)

Primary Keys:
Department: Dept_ID
Student: Student_ID
Course: Course_ID
Enrollment: Student_ID + Course_ID + Semester

Foreign Keys:
Student.Dept_ID references Department.Dept_ID
Course.Dept_ID references Department.Dept_ID
Enrollment.Student_ID references Student.Student_ID
Enrollment.Course_ID references Course.Course_ID

SQL Implementation Example

The same relational design can be implemented using SQL tables, primary keys, foreign keys, unique constraints, and check constraints.

college-relational-schema.sql

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT CHECK (credits BETWEEN 1 AND 6),
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    grade VARCHAR(5),
    PRIMARY KEY (student_id, course_id, semester),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Representing Relationships in Relational Model

Relationships from the ER model are represented using keys and tables in the relational model.

Relationship Type	Relational Mapping Rule	Example
1:1	Place foreign key in one table, preferably the table with total participation.	Person and Passport.
1:N	Place primary key of the one-side table as foreign key in the many-side table.	Department_ID stored in Employee table.
M:N	Create a separate relation containing foreign keys of both participating relations.	Enrollment(Student_ID, Course_ID, Semester, Grade).
Weak Entity	Create a table with owner primary key plus partial key.	Order_Item(Order_ID, Item_No, Product_ID, Quantity).

Null Values in Relational Model

A null value means the value is missing, unknown, not applicable, or not yet available. Null is not the same as zero, blank string, or false.

Situation	Example	Use Null?
Unknown value	Student phone number not yet provided.	Yes
Not applicable	Middle name for a student who has no middle name.	Possible
Required identifier	Primary key Student_ID.	No
Known numeric zero	Fine amount is exactly 0.	No, store 0.

Relational Algebra

Relational algebra is a formal procedural query language for the relational model. It defines operations that take one or more relations as input and produce a new relation as output. SQL is influenced by relational algebra, although SQL is more practical and feature-rich.

Operation	Meaning	SQL Equivalent	Example Idea
Select	Filters rows based on a condition.	WHERE	Students from BCA department.
Project	Selects specific columns.	SELECT column list	Show only name and email.
Union	Combines rows from compatible relations and removes duplicates.	UNION	All students from two campuses.
Intersection	Returns rows common to both compatible relations.	INTERSECT	Students in both sports and music clubs.
Difference	Returns rows in first relation but not in second.	EXCEPT or MINUS	Students enrolled but not paid fees.
Cartesian Product	Combines every tuple of one relation with every tuple of another.	CROSS JOIN	All possible student-course pairs.
Join	Combines related rows from two relations.	JOIN	Student names with department names.
Rename	Renames relation or attribute.	AS alias	students AS s.

Relational Algebra Examples with SQL

Relational algebra is normally written with mathematical symbols, but the same ideas can be understood through SQL.

relational-algebra-examples.sql

-- SELECT operation: filter rows
SELECT *
FROM students
WHERE department = 'BCA';

-- PROJECT operation: choose columns
SELECT name, email
FROM students;

-- JOIN operation: combine related tables
SELECT s.name, d.dept_name
FROM students s
JOIN departments d ON s.dept_id = d.dept_id;

-- DIFFERENCE operation: students not enrolled in any course
SELECT student_id, name
FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM enrollments
);

Union Compatibility

Set operations such as union, intersection, and difference require union compatibility. Two relations are union compatible when they have the same number of attributes and corresponding attributes have compatible domains.

Requirement	Meaning	Example
Same degree	Both relations must have the same number of columns.	Both have Student_ID and Name.
Compatible domains	Corresponding columns must store comparable data.	Student_ID INT with Student_ID INT.
Column names	Names do not have to be identical in theory, but result column names usually come from the first query.	name and full_name may be compatible if both are text.

Relational Model and Normalization

Normalization is the process of organizing relations to reduce redundancy and avoid update, insert, and delete anomalies. It is based on functional dependencies among attributes.

1NF: Attributes must contain atomic values.
2NF: No partial dependency on a composite key.
3NF: No transitive dependency of non-key attributes.
BCNF: Every determinant should be a candidate key.

Advantages of Relational Model

Simple table-based structure.
Strong mathematical foundation.
Data can be queried flexibly using SQL.
Keys and constraints help maintain data integrity.
Tables can be normalized to reduce redundancy.
Physical storage details are hidden from users.
Widely supported by commercial and open-source DBMS products.

Limitations of Relational Model

Complex objects may require many tables and joins.
Very large joins can affect performance if not designed and indexed properly.
Hierarchical or graph-like data may be less natural to model than in specialized databases.
Object-oriented application structures may not map perfectly to tables.
Poor schema design can still cause redundancy and anomalies.

Common Mistakes

Mistake	Correct Understanding
Confusing relation with relationship.	A relation is a table; a relationship is an association between tables or entities.
Thinking primary key and foreign key are the same.	Primary key identifies rows in its own table; foreign key references another table.
Storing multiple values in one column.	Use atomic values and create a separate table for repeated values.
Assuming row order is meaningful.	Relations are unordered. Use ORDER BY when order is needed.
Adding foreign key on the wrong side of 1:N relationship.	Foreign key usually goes on the many side.
Using a non-minimal key as candidate key.	Candidate key must be a minimal super key.

Interview and Exam Questions

Question	Short Answer
Who proposed the relational model?	Dr. E. F. Codd proposed the relational model in 1970.
What is a relation?	A relation is a table made of rows and columns.
What is a tuple?	A tuple is a row or record in a relation.
What is an attribute?	An attribute is a column or field in a relation.
What is a domain?	A domain is the set of valid values for an attribute.
What is a primary key?	A selected candidate key used to uniquely identify each tuple.
What is a foreign key?	An attribute that references the primary key of another relation.
What is entity integrity?	The rule that primary key values cannot be null.
What is referential integrity?	A foreign key must match an existing referenced key or be null if allowed.
What is relational algebra?	A formal query language where operations take relations as input and return relations as output.

Quick Revision Notes

Relational model stores data in relations, commonly called tables.
Relation means table, tuple means row, and attribute means column.
Degree is number of attributes; cardinality is number of tuples.
Relation schema is structure; relation instance is current data.
Candidate key is a minimal super key.
Primary key cannot be null due to entity integrity.
Foreign key supports referential integrity between relations.
1:N relationship usually stores foreign key on the many side.
M:N relationship is represented by a separate relation.
Relational algebra operations include select, project, union, difference, product, join, and rename.

Key Takeaways

The relational model was proposed by E. F. Codd in 1970.
A relation is a table, a tuple is a row, and an attribute is a column.
Domain defines the allowed values for an attribute.
Degree is the number of attributes, while cardinality is the number of tuples.
Relation schema is the table structure; relation instance is the current table data.
A candidate key is a minimal super key, and one candidate key is selected as the primary key.
Primary keys enforce entity integrity, and foreign keys enforce referential integrity.
M:N relationships are represented using separate relationship tables.
Relational algebra provides formal operations such as select, project, union, difference, product, join, and rename.
Normalization improves relational design by reducing redundancy and avoiding anomalies.