For VTU 10 Marks, you can write the answer as follows.

Explain the Relational Model Constraints

Definition
Relational model constraints are rules that restrict the values stored in a database to
maintain accuracy, consistency, and integrity of data.
The constraints are classified into three categories:
1. Inherent (Implicit) Constraints
2. Schema-Based (Explicit) Constraints
3. Application-Based (Semantic) Constraints

1. Inherent (Implicit) Constraints

• These constraints are built into the relational model.

• They cannot be changed by the user.

Example:
• A relation cannot have duplicate tuples.
• Each table consists of rows and columns.

2. Schema-Based (Explicit) Constraints

These constraints are specified while creating the database schema using DDL.
They include:

(a) Domain Constraints

• Every attribute must contain values from its defined domain (value set).

• Ensures correct data type and valid values.

Example:
• Age → Integer (18–60)
 • Gender → {Male, Female}

(b) Key Constraints

• Ensures uniqueness of tuples.

Super Key
• A set of attributes that uniquely identifies a tuple.
Example:
{USN}, {USN, Name}

Candidate Key
• A minimal super key.
Example:
USN

Primary Key
• One candidate key chosen to identify tuples uniquely.
• Cannot contain duplicate or NULL values.
Example:
Student(USN, Name, Age)

(c) NULL Constraints

• Specifies whether an attribute can have NULL values.

Example:
Name VARCHAR(30) NOT NULL

(d) Entity Integrity Constraint

• Primary Key cannot be NULL.

• Ensures every tuple is uniquely identifiable.

Example:
Every Student must have a valid USN.

(e) Referential Integrity Constraint

• A Foreign Key must either:

• Match an existing Primary Key in another table, or

• Be NULL.
Example:
Department
DeptID (PK)
10
20
Employee
EmpID DeptID (FK)
101 10
102 20
DeptID = 30 is not allowed unless it exists in the Department table.

3. Application-Based (Semantic)
Constraints
• Business rules that cannot be specified directly in the schema.
• Implemented using application programs, Triggers, or Assertions.

Examples:
• Employee salary should not exceed manager's salary.
• Maximum working hours per week should not exceed 56.

Relational Database Schema

A Relational Database Schema is a collection of relation schemas and integrity
constraints.
Example:
COMPANY = {
EMPLOYEE,
DEPARTMENT,
PROJECT,
WORKS_ON,
DEPENDENT
}

Relational Database State

A Database State is the current contents of all relations.
• Valid State: Satisfies all constraints.

• Invalid State: Violates one or more constraints.

Other Types of Constraints

Functional Dependency Constraint
• Specifies that one attribute determines another.
Notation:
X → Y

Example:
USN → Student_Name

State Constraints (Static Constraints)

• Conditions that must always hold in a valid database state.
Example:
Salary > 0
Transition Constraints (Dynamic Constraints)
• Restrict how database values can change from one state to another.
Example:
An employee's salary can increase but should not decrease below the minimum
salary.

Summary
Constraint Purpose Example
Inherent Constraint Built into relational model No duplicate tuples
Domain Constraint Valid values Age = Integer
Key Constraint Unique tuples USN
NULL Constraint Restricts NULL values Name NOT NULL
Entity Integrity Primary Key cannot be NULL USN
Foreign Key must reference a valid Primary
Referential Integrity DeptID
Key
Semantic Constraint Business rules Salary ≤ Manager's Salary
Functional Dependency Attribute dependency USN → Name
State Constraint Valid database state Salary > 0
Transition Constraint Valid state changes Controlled salary updates

Conclusion
Relational model constraints ensure data accuracy, consistency, and integrity by
enforcing rules such as domain constraints, key constraints, entity integrity,
referential integrity, and business rules, thereby maintaining a reliable database.

For VTU 10 Marks, write the answer in two parts.

Explain Entity Integrity and Referential

Integrity Constraints with Suitable
Examples. Why is Each Important?
Database integrity constraints ensure that data stored in the database is accurate,
consistent, and valid.
1. Entity Integrity Constraint
Definition
The Entity Integrity Constraint states that the Primary Key of a relation cannot
be NULL.
Since the primary key uniquely identifies each tuple, it must always contain a valid
and unique value.

Example
STUDENT
USN (PK) Name Age
1TJ21IS001 Alice 20

❌
1TJ21IS002 Bob 21
NULL Carol 22
The third record violates Entity Integrity because the Primary Key (USN) is
NULL.

Importance
• Uniquely identifies every record.
• Prevents duplicate or unidentified tuples.
• Maintains data integrity.
• Allows other tables to reference records correctly.

2. Referential Integrity Constraint

Definition
The Referential Integrity Constraint states that a Foreign Key in one relation must
either:
• Match an existing Primary Key in another relation, or

• Be NULL.

It maintains consistency between related tables.

Example
DEPARTMENT
DeptID (PK) DeptName
10 HR
20 IT
30 Sales
EMPLOYEE

✔
EmpID Name DeptID (FK)

✔
101 Alice 10

❌
102 Bob 20
103 Carol 40
The third record violates Referential Integrity because DeptID = 40 does not exist
in the DEPARTMENT table.

Importance
• Maintains consistency between related tables.
• Prevents invalid foreign key values.
• Avoids orphan records.
• Ensures every foreign key references a valid primary key.

Difference Between Entity Integrity and

Referential Integrity
Entity Integrity Referential Integrity
Applies to Primary Key Applies to Foreign Key
Foreign Key must reference an existing Primary Key or be
Primary Key cannot be NULL
NULL
Ensures each record is uniquely
Ensures consistency between related tables
identified
Applied within a single table Applied between two related tables

Conclusion
• Entity Integrity ensures that every record has a valid, non-NULL Primary
Key, so each tuple can be uniquely identified.
 • Referential Integrity ensures that every Foreign Key correctly references an
existing Primary Key, maintaining consistency between related tables. Both
constraints are essential for maintaining a reliable and error-free database.

For VTU 10 Marks, this concise answer is sufficient.

Discuss Update Operations and How

Constraint Violations are Handled with
Suitable Examples
Definition
Update operations are operations that modify the contents (state) of a database. The three basic
update operations are:
1. Insert
2. Delete
3. Update (Modify)
Whenever these operations are performed, the integrity constraints of the database must not be
violated.

1. Insert Operation
Definition
The INSERT operation adds one or more new tuples (rows) into a relation.

Constraint Violations
a) Domain Constraint
• Occurs when a value is outside its domain or has an invalid data type.

❌
Example:
Age = 'ABC'

b) Key Constraint
• Occurs when the primary key value already exists.
Example:
USN (PK) Name

❌
101 Alice
101 Bob
Duplicate primary key is not allowed.

c) Entity Integrity Constraint

• Occurs when the primary key is NULL.
Example:

❌
USN (PK) Name
NULL Alice

d) Referential Integrity Constraint

• Occurs when a foreign key refers to a non-existing primary key.
Example:
Department

DeptID
10
20
Employee

❌
EmpID DeptID
101 30
DeptID 30 does not exist.

Handling
• Reject the insertion (default action).
• Display an appropriate error message.
• Correct the invalid values before inserting.

2. Delete Operation
Definition
The DELETE operation removes tuples from a relation.

Constraint Violation
Deletion may violate Referential Integrity if other tables reference the deleted tuple.
Example
Department

DeptID
10
Employee

EmpID DeptID
101 10
Deleting DeptID = 10 causes a referential integrity violation because Employee refers to it.

Handling Methods
1. Restrict
• Reject the delete operation.
2. Cascade
• Delete all related tuples automatically.
3. Set NULL / Set DEFAULT
• Change foreign key values to NULL or a default value.

3. Update (Modify) Operation

Definition
The UPDATE operation changes the values of one or more attributes in existing tuples.

Possible Violations
a) Domain Constraint
• Invalid data type or value.

❌
Example:
Salary = 'ABC'

b) Key Constraint
• Duplicate primary key.
Example:
Changing USN 102 → 101 when 101 already exists.

c) Referential Integrity Constraint

 • Updating a foreign key to a value that does not exist.
Example:
Changing DeptID from 10 to 50 when Department 50 does not exist.

Handling
• Reject the update.
• Cascade the update to related tables (if allowed).
• Set foreign key to NULL or DEFAULT.

Summary Table
Operation Possible Constraint Violations Handling
Domain, Key, Entity Integrity, Referential
Insert Reject, correct values
Integrity
Restrict, Cascade, Set NULL, Set
Delete Referential Integrity
DEFAULT
Reject, Cascade, Set NULL, Set
Update Domain, Key, Referential Integrity
DEFAULT

Conclusion
The three update operations—Insert, Delete, and Update—modify the database state. During these
operations, the DBMS enforces Domain, Key, Entity Integrity, and Referential Integrity
constraints. If a violation occurs, it is handled using methods such as Reject (Restrict), Cascade,
Set NULL, or Set DEFAULT, ensuring that the database remains accurate, consistent, and
reliable.

For VTU 10 Marks, this answer is sufficient.

Explain the Characteristics (Properties) of

Relations with Suitable Examples
Definition
A relation is a table consisting of rows (tuples) and columns (attributes). Every relation in the
relational model follows certain properties.
1. Ordering of Tuples
• A relation is a set of tuples, and a set has no specific order.
• Therefore, the order of rows in a relation does not matter.

Example
USN Name
101 Alice
102 Bob
or

USN Name
102 Bob
101 Alice
Both tables represent the same relation.

2. Ordering of Attributes (Columns)

• The order of attributes is not important as long as attribute names remain the same.

Example
USN Name Age
is equivalent to

Age Name USN

because the attribute names identify the values.

3. Atomic Values and NULL Values

Atomic Values
• Every attribute must contain only one (atomic) value.
• A cell cannot contain multiple values.
Correct:

USN Phone
101 9876543210
Incorrect:

❌
USN Phone
101 9876543210, 8765432109

NULL Values
• NULL represents:
 • Unknown value
• Value not available
• Value not applicable
Example

USN Office Phone

101 NULL

4. Interpretation (Meaning) of a Relation

• A relation schema represents a real-world entity.
• Each tuple represents one instance (record) of that entity.

Example
STUDENT(USN, Name, Age)

USN Name Age

101 Alice 20
This tuple means:
Student Alice with USN 101 is 20 years old.

Summary Table
Property Description Example
Ordering of Tuples Row order does not matter Rows can be rearranged
Ordering of Attributes Column order does not matter USN, Name = Name, USN
Atomic & NULL Values Each cell has one value; NULL allowed Phone = NULL
Interpretation of Relation Each tuple represents one real-world entity Student record

Conclusion
The main characteristics of a relation are:
1. No ordering of tuples
2. No ordering of attributes
3. Atomic values with NULL allowed
4. Each tuple represents a real-world fact or entity
These properties ensure that relations remain consistent, flexible, and easy to manage in a
relational database.
This question is almost the same as "Explain the characteristics (properties) of relations."
However, if VTU asks "Discuss the characteristics of relations that make them different from
ordinary tables and files", you should emphasize the differences.

Discuss the Characteristics of Relations that

Make Them Different from Ordinary Tables
and Files
A relation in the relational model has several characteristics that distinguish it from ordinary tables
and file systems.

1. No Ordering of Tuples
• A relation is defined as a set of tuples.
• Since a set has no order, tuples in a relation do not have any particular order.
• Tuple ordering is not part of the relation definition, whereas records in ordinary files may
have a fixed order.
Example: Rearranging employee records does not change the relation.

2. No Ordering of Attributes
• The order of attributes and their values is not important as long as the correspondence
between attributes and values is maintained.
• A tuple can be considered as a set of (Attribute, Value) pairs.
• In ordinary tables, column positions are often significant.
Example: STUDENT(USN, Name, Age) and STUDENT(Age, Name, USN) represent the same
relation.

3. Atomic Values and NULL Values

• Each value in a tuple is atomic (indivisible).
• NULL values are used to represent attribute values that may be unknown or may not
apply to a tuple.
• In general, NULL can have several meanings:
• Value is unknown.
• Value exists but is not available.
• Attribute does not apply to the tuple (value undefined).
Example:

USN Name Office_Phone

101 Alice NULL
Here, Office_Phone = NULL because Alice does not have an office phone.

4. No Duplicate Tuples
• A relation cannot contain duplicate tuples.
• Every tuple must be unique.
• Ordinary tables or files may contain duplicate records.
Example:

USN Name

❌
101 Alice
101 Alice
Duplicate tuples are not allowed in a relation.

5. Interpretation (Meaning) of a Relation

• A relation schema can be interpreted as a declaration (assertion) about an entity.
• Each tuple represents a particular instance of that assertion.
• Alternatively, a relation schema can be viewed as a predicate, where each tuple satisfies the
predicate.
Example:
STUDENT(USN, Name, Age)

USN Name Age

101 Alice 20
This tuple asserts that Alice is a student with USN 101 and Age 20.

Summary: Differences from Ordinary Tables and Files

Relation Ordinary Tables/Files
No ordering of tuples Records may have a fixed order
No ordering of attributes Column order may matter
Atomic values only May contain repeating groups
Duplicate tuples are not allowed Duplicate records may exist
Supports NULL values NULL handling is not standardized
Represents real-world facts logically Mainly stores raw records
Conclusion:
Relations differ from ordinary tables and files because they have no ordering of tuples or
attributes, contain only atomic values, do not allow duplicate tuples, support NULL values,
and each tuple represents a real-world fact, making the relational model more consistent and
reliable.

Yes. Based on your reference notes, here is a VTU-style answer that keeps the textbook wording,
converts paragraphs into points, and includes examples.

Illustrate the SELECT (σ) and PROJECT (π)

Relational Algebra Operators with Suitable
Examples
Relational Algebra
Relational Algebra is the basic set of operations for the relational model. These operations enable a
user to specify retrieval requests as relational algebra expressions. The result of every operation is
a new relation.

Importance of Relational Algebra

• It provides a formal foundation for relational model operations.
• It is used for implementing and optimizing queries in RDBMS.
• Many of its concepts are incorporated into SQL.

1. SELECT (σ) Operation

Definition
• The SELECT (σ) operation selects tuples (rows) from a relation based on a selection
condition.
• The selection condition acts as a filter, keeping only those tuples that satisfy the condition.
• It can also be viewed as a horizontal partition of the relation:
• Tuples satisfying the condition are selected.
• Remaining tuples are discarded.

General Form
σ<selection condition>(R)

Where:
 • σ → Select operator
• R → Relation
• Selection condition → Boolean expression

Selection Condition
A condition may be of the form:
Attribute Comparison_Operator Constant

or
Attribute Comparison_Operator Attribute

Clauses can be combined using:

• AND
• OR
• NOT

Examples
1. Select employees working in Department 4
σDno = 4 (EMPLOYEE)

2. Select employees whose salary is greater than ₹30,000

σSalary > 30000 (EMPLOYEE)

3. Select employees who

• work in Department 4 and earn more than ₹25,000
• OR work in Department 5 and earn more than ₹30,000
σ(Dno=4 AND Salary>25000) OR
(Dno=5 AND Salary>30000)(EMPLOYEE)

Properties of SELECT
• It is a Unary Operation (operates on one relation).
• Degree (number of attributes) remains unchanged.
• Number of tuples is less than or equal to the original relation.
• It is commutative, i.e., multiple SELECT operations can be applied in any order.
SQL Equivalent
Relational Algebra
σDno=4 AND Salary>25000 (EMPLOYEE)

SQL
SELECT *
FROM EMPLOYEE
WHERE Dno=4 AND Salary>25000;

2. PROJECT (π) Operation

Definition
• The PROJECT (π) operation selects required columns (attributes) from a relation and
discards the remaining columns.
• It can be viewed as a vertical partition of the relation.
• It is used when only certain attributes are required.

General Form
π<attribute list>(R)

Where:
• π → Project operator
• Attribute List → Required columns
• R → Relation

Example
Display only Last Name, First Name and Salary.
πLname, Fname, Salary (EMPLOYEE)

Duplicate Elimination
• If only non-key attributes are projected, duplicate tuples may occur.
• PROJECT automatically removes duplicate tuples because the result must be a valid
relation.
Example
πGender, Salary (EMPLOYEE)

If two employees have the same Gender and Salary, only one tuple appears in the result.

Properties of PROJECT
• It is a Unary Operation.
• It reduces the degree (number of attributes).
• Number of tuples is less than or equal to the original relation.
• Duplicate tuples are eliminated automatically.
• PROJECT is not commutative.

SQL Equivalent
Relational Algebra
πGender, Salary (EMPLOYEE)

SQL
SELECT DISTINCT Gender, Salary
FROM EMPLOYEE;

Difference Between SELECT and PROJECT

SELECT (σ) PROJECT (π)
Selects rows (tuples) Selects columns (attributes)
Horizontal partition Vertical partition
Degree remains the same Degree decreases
Number of tuples may decrease Number of attributes decreases
Uses selection condition Uses attribute list

Conclusion
• SELECT (σ) retrieves required rows based on a condition (horizontal partition).
• PROJECT (π) retrieves required columns and removes duplicate tuples (vertical
partition).
• Both are unary relational algebra operations and form the foundation of query processing
in relational databases.
RENAME (ρ) Operation
Definition
• The RENAME (ρ) operation is used to rename a relation or its attributes.
• It is useful when the same relation is used more than once in a query or when meaningful
names are required.

General Form
Rename a relation:
ρNewRelation(R)

Rename attributes:
ρNewRelation(A1, A2, ..., An)(R)

Example
Rename relation EMPLOYEE as EMP.
ρEMP(EMPLOYEE)

Rename relation STUDENT as LEARNER.

ρLEARNER(STUDENT)

Rename attributes:
ρEMP(Eid, Ename, Salary)(EMPLOYEE)

Properties
• Unary operation.
• Changes only the name of the relation or attributes.
• Does not change the data.
• Useful in complex relational algebra expressions.

For VTU 10 Marks, keep the important textbook points and add examples.

Discuss Equijoin and Natural Join with

Suitable Examples using Relational Algebra
Notation
JOIN Operation
• A JOIN operation combines related tuples from two relations into a single relation.
 • It is used to present related information stored in different tables.
• JOIN is also known as an Inner Join.
• Informally, a JOIN is a combination of CARTESIAN PRODUCT (×) and SELECT (σ).

1. Equijoin
Definition
• The most common use of JOIN involves equality comparisons (=) only.
• Such a JOIN is called an EQUIJOIN.
• In the result of an EQUIJOIN, the join attributes from both relations are retained, resulting
in duplicate columns having the same values.

General Form
R ⋈R.A=S.B S

or
σR.A=S.B(R × S)

Example
EMPLOYEE

Ssn Name Dno

101 Alice 10
102 Bob 20
DEPARTMENT

Dnumber Dname
10 HR
20 IT
Find employee details along with department details.
Relational Algebra
EMPLOYEE ⋈[Link] = [Link] DEPARTMENT

Result
Ssn Name Dno Dnumber Dname
101 Alice 10 10 HR
102 Bob 20 20 IT
Here, Dno and Dnumber both appear in the result because Equijoin keeps both join attributes.
Characteristics of Equijoin
• Uses only the equality (=) operator.
• Both join attributes are retained.
• Duplicate join columns may appear.
• Combines related tuples from two relations.

2. Natural Join
Definition
• A NATURAL JOIN is an Equijoin in which the join attributes have the same name in both
relations.
• If attribute names are different, a RENAME (ρ) operation is applied first.
• Duplicate join attributes are eliminated from the result.

General Form
R ⋈ S

Example
Suppose both relations have the attribute Dnumber.
EMPLOYEE

Ssn Name Dnumber

101 Alice 10
102 Bob 20
DEPARTMENT

Dnumber Dname
10 HR
20 IT
Relational Algebra
EMPLOYEE ⋈ DEPARTMENT

Result
Ssn Name Dnumber Dname
101 Alice 10 HR
Ssn Name Dnumber Dname
102 Bob 20 IT
Here, only one Dnumber column appears because Natural Join removes duplicate join attributes.

If Attribute Names Differ

Suppose PROJECT has Dnum and DEPARTMENT has Dnumber.
First rename:
ρ(Dname, Dnum, Mgr_ssn, Mgr_start_date)(DEPARTMENT)

Then apply Natural Join:

PROJECT ⋈ DEPARTMENT

Characteristics of Natural Join

• Join attributes must have the same name.
• If names differ, use RENAME (ρ) first.
• Duplicate join columns are removed.
• If no tuples satisfy the join condition, the result is an empty relation.
• If no join condition exists, JOIN becomes a Cartesian Product (Cross Join).

Difference Between Equijoin and Natural Join

Equijoin Natural Join
Uses equality (=) condition Uses equality on attributes with the same name
Duplicate join columns are retained Duplicate join columns are removed
Join condition must be specified Join condition is generated automatically
Works even if attribute names differ Attribute names must be the same (or renamed first)

Conclusion
• Equijoin combines tuples using the equality operator (=) and retains both join attributes in
the result.
• Natural Join is a special type of Equijoin that joins attributes with the same name and
automatically removes duplicate join columns, producing a cleaner relation.

Yes! For VTU, remembering the story is much easier than memorizing definitions. Here's a simple
10-mark answer.
Discuss Equijoin and Natural Join with
Suitable Examples
JOIN
Story
• JOIN means merging two tables to get related information.
• It combines rows from two tables based on a common attribute.

1. Equijoin
Simple Story
• Equijoin means merging two tables using the equality (=) operator.
• The joining attributes may have different names or the same names.
• Both joining columns are kept in the result, so duplicate columns appear.

Syntax
R ⋈R.A = S.B S

or
σR.A=S.B(R × S)

Example
EMPLOYEE

EmpID Name Dno

101 Alice 10
102 Bob 20
DEPARTMENT

Dnumber Dname
10 HR
20 IT
Join Condition
[Link] = [Link]

Relational Algebra
EMPLOYEE ⋈[Link]=[Link] DEPARTMENT
Result
EmpID Name Dno Dnumber Dname
101 Alice 10 10 HR

👉
102 Bob 20 20 IT
Notice:
• Dno and Dnumber are both shown.
• Duplicate join columns are not removed.

Key Points
• Uses = operator.
• Joins two tables.
• Join attribute names may be different.
• Duplicate join columns remain.

2. Natural Join
Simple Story
• Natural Join means merging two tables automatically using attributes having the same
name.
• Duplicate join columns are removed.
• If attribute names are different, first use RENAME (ρ).

Syntax
R ⋈ S

Example
EMPLOYEE

EmpID Name Dnumber

101 Alice 10
102 Bob 20
DEPARTMENT

Dnumber Dname
10 HR
20 IT
Relational Algebra
EMPLOYEE ⋈ DEPARTMENT

Result
EmpID Name Dnumber Dname
101 Alice 10 HR

👉
102 Bob 20 IT
Notice:
• Only one Dnumber column appears.
• Duplicate column is removed automatically.

If Attribute Names Are Different

Rename first:
ρ(Dname, Dnum)(DEPARTMENT)

Then apply Natural Join:

PROJECT ⋈ DEPARTMENT

Key Points
• Uses attributes with the same name.
• Removes duplicate join columns.
• Cleaner result than Equijoin.
• If names differ → use RENAME first.

Difference Between Equijoin and Natural Join

Equijoin Natural Join
Merge two tables using = Merge two tables using same attribute names
Attribute names may be different Attribute names must be the same
Duplicate columns remain Duplicate columns are removed
Join condition is written explicitly Join condition is automatic

Easy Trick to Remember (Exam)

Equijoin
"Equal condition (=), Duplicate columns stay."
Example:

➡️
Dno = Dnumber
Result contains both Dno and Dnumber.
Natural Join
"Same name, Same column, Duplicate removed."

Example:

➡️
Dnumber
Result contains only one Dnumber.
This "story + syntax + example + key points + difference" format is ideal for a VTU 10-mark
answer and is much easier to remember during exams.

For VTU 10 Marks, here's an easy story + points + syntax + example answer.

Discuss the Aggregation Functions and

Grouping in Relational Algebra with Suitable
Examples
Aggregation Functions
Simple Story
• Aggregation functions are used to summarize information from a relation.
• Instead of displaying every tuple, they produce a single statistical value.
• They are mainly used for reports and analysis.

Common Aggregation Functions

Function Purpose
SUM Calculates the total of numeric values
AVERAGE (AVG) Calculates the average value
MAXIMUM (MAX) Finds the highest value
MINIMUM (MIN) Finds the lowest value
COUNT Counts the number of tuples or values

Examples
Find the total salary of all employees.
ℱSUM Salary (EMPLOYEE)

Find the average salary.

ℱAVERAGE Salary (EMPLOYEE)

Find the highest salary.

ℱMAX Salary (EMPLOYEE)

Find the lowest salary.

ℱMIN Salary (EMPLOYEE)

Count the number of employees.

ℱCOUNT Ssn (EMPLOYEE)

Grouping
Simple Story
• Grouping means dividing tuples into groups based on the value of an attribute.
• After grouping, an aggregate function is applied separately to each group.
Example:
Group employees by Department Number (Dno) and calculate the average salary of each
department.

General Syntax
ℱ<grouping attributes>
<function list>(R)

Where:
• Grouping attributes → Attributes used to form groups.
• Function list → Aggregate functions like SUM, AVG, MAX, MIN, COUNT.
• R → Relation.

Example
Retrieve:
• Department Number
• Number of Employees
• Average Salary
Relational Algebra
ℱDno
COUNT(Ssn), AVERAGE(Salary)(EMPLOYEE)

Renaming the result

ρ(Dno, No_of_Employees, Average_Salary)
(ℱDno COUNT(Ssn), AVERAGE(Salary)(EMPLOYEE))

Result
Dno No_of_Employees Average_Salary
1 5 35000
2 3 42000
3 4 39000

Key Points
• Aggregation functions summarize data.
• Grouping divides tuples into groups based on an attribute.
• Aggregate functions are applied independently to each group.
• Common functions are SUM, AVG, MAX, MIN, and COUNT.

Applications
• Finding department-wise average salary.
• Counting employees in each department.
• Finding the highest or lowest salary.
• Generating summary reports.

Conclusion
Aggregation functions summarize data using SUM, AVG, MAX, MIN, and COUNT, while
grouping divides tuples based on an attribute and applies these functions separately to each
group. They are widely used for statistical analysis and report generation in relational databases.

For VTU 10 Marks, this is the easiest way to remember. The answer is simply the ER-to-
Relational Mapping Algorithm.
Explain the Steps to Convert the Basic ER
Model into a Relational Database Schema
Definition
ER-to-Relational Mapping is the process of converting an Entity-Relationship (ER) model into a
Relational Database Schema (Tables).

Step 1: Map Regular (Strong) Entity Type

Story
• Every strong entity becomes a relation (table).
• All simple attributes become columns.
• The key attribute becomes the Primary Key.

Example
ER Entity
EMPLOYEE(EmpID, Name, Salary)

Relation
EMPLOYEE(EmpID, Name, Salary)
PK → EmpID

Step 2: Map Weak Entity Type

Story
• Create a separate table for the weak entity.
• Include:
• All attributes of the weak entity.
• Primary Key of the owner (strong) entity as a Foreign Key.
• The Primary Key is a combination of:
• Owner's Primary Key
• Partial Key of the weak entity.
Example
DEPENDENT(EmpID, Dependent_Name, Age)

PK → (EmpID, Dependent_Name)
FK → EmpID references EMPLOYEE

Step 3: Map Binary 1:1 Relationship

Story
• Add the Primary Key of one relation as a Foreign Key in the other relation.
• Prefer the relation with total participation.

Example
EMPLOYEE(EmpID, Name, DeptID)

DEPARTMENT(DeptID, Dname, ManagerID)

ManagerID → FK referencing EMPLOYEE

Step 4: Map Binary 1:N Relationship

Story
• Add the Primary Key of the One (1) side as a Foreign Key in the Many (N) side.

Example
Department → Employee
DEPARTMENT(DeptID, Dname)

EMPLOYEE(EmpID, Name, DeptID)

DeptID → FK

Step 5: Map Binary M:N Relationship

Story
• Create a new relation.
• Include the Primary Keys of both participating entities.
• These become:
 • Composite Primary Key
• Foreign Keys
• Include any relationship attributes.

Example
Employee works on Project
WORKS_ON(EmpID, ProjectID, Hours)

PK → (EmpID, ProjectID)

FK → EmpID references EMPLOYEE

FK → ProjectID references PROJECT

Step 6: Map Multivalued Attribute

Story
• Create a new relation for every multivalued attribute.
• Include:
• Primary Key of the owner entity.
• Multivalued attribute.
• Composite Primary Key = (Owner PK + Attribute).

Example
Employee has multiple phone numbers.
EMP_PHONE(EmpID, PhoneNo)

PK → (EmpID, PhoneNo)

FK → EmpID references EMPLOYEE

Step 7: Map N-ary (Ternary) Relationship

Story
• Create a new relation.
• Include the Primary Keys of all participating entities.
• Add relationship attributes if any.
Example
Supplier supplies Part to Project.
SUPPLY(SupplierID, PartID, ProjectID, Quantity)

PK → (SupplierID, PartID, ProjectID)

Summary Table
ER Component Relational Mapping
Strong Entity Create one relation
Weak Entity Create relation + owner's PK as FK
1:1 Relationship Add FK to one relation
1:N Relationship Add PK of "1" side as FK in "N" side
M:N Relationship Create new relation
Multivalued Attribute Create new relation
N-ary Relationship Create new relation with all PKs

Example
ER Model
EMPLOYEE ── Works For ── DEPARTMENT
\
\ Works On
\
PROJECT

Relational Schema
EMPLOYEE(EmpID, Name, DeptID)

DEPARTMENT(DeptID, Dname)

PROJECT(ProjectID, Pname)

WORKS_ON(EmpID, ProjectID, Hours)

Conclusion
The ER-to-Relational Mapping process converts an ER model into tables by mapping entities,
relationships, weak entities, multivalued attributes, and constraints into relational schemas.
This process forms the foundation for designing relational databases.