UNIT-II Relational Data Model BCA SEP SEM III DBMS

Database Management Systems (DBMS) Unit-II BCA Semester III
1
Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432
For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bca
UNIT–II: Relational Data Model
Relational Data Model
• The Relational Data Model is the most widely used data model in modern databases.
• In this model, all data is stored in the form of tables (also called relations).
• Each table represents an entity, and each row in the table represents a record.
• Common relational databases: MySQL, Oracle, PostgreSQL, SQL Server.
Relational Model Concepts
The basic terms in the relational model are:
1. Relation (Table)
A relation is a table consisting of rows and columns.
Example: Student Table

2
Reg_No Name Age Department
101 Rohan 19 CS
102 Priya 20 IT
103 Manoj 18 CS
Here,
• The table name: Student
• Each row = one student record
• Each column = an attribute of the student
2. Tuple (Row)
A tuple is a single row in a table.
Example:
102 Priya 20 IT
is a tuple.
3. Attribute (Column)
An attribute represents a property of the entity.
Example:
In the Student table: Reg_No, Name, Age, Department
4. Domain
A domain is the set of valid values an attribute can take.
Examples:
• Age domain: 15 to 30
• Department domain: {CS, IT, ECE}
• Name domain: Alphabetic string
5. Degree of a Relation
Number of attributes in a table.
Example: Student table has 4 attributes → Degree = 4

3
6. Cardinality of a Relation
Number of tuples (rows) in a table.
Example: There are 3 student records → Cardinality = 3
7. Primary Key
A primary key uniquely identifies each record in a table.
Example:
• Reg_No is unique for each student → Primary Key = Reg_No
Primary keys cannot be NULL and cannot be duplicated.
8. Foreign Key
A foreign key is an attribute that refers to the primary key of another table.
Used to link two tables.
Example:
Department Table
Dept_ID Dept_Name
10 CS
20 IT
Student Table
Reg_No Name Age Dept_ID (FK)
101 Rohan 19 10
102 Priya 20 20
9. Candidate Key
All attributes that can uniquely identify a tuple.
Example:
In the Student table, if both Reg_No and Email are unique, then both are candidate keys.
We choose one of them as the Primary Key.
10. Composite Key
A key formed by combining two or more attributes.

4
Example:
In a Course Registration table:
Student_ID Course_ID Grade
• No single column uniquely identifies a record.
• But (Student_ID, Course_ID) together form a Composite Key.
11. Null Values
Null means value is unknown, missing, or not applicable.
Example:
If a student has not provided an email ID, the Email field may be NULL.
Characteristics of Relations
Relational tables follow certain rules:
1. Rows are Unique: No two rows (tuples) are identical.
2. Columns have Unique Names: Every attribute has a distinct name.
3. Order of Rows Does Not Matter: Rows can appear in any order; it does not affect
the relation.
4. Order of Columns Does Not Matter: Even if the column order changes, the
meaning of the table remains same.
5. Values are Atomic (Single-valued): Each cell must contain one value only, not
multiple.
Wrong: Phone = {9876, 9988}
✔ Correct: Store separate rows or create a separate table for phone numbers.
6. Each Attribute Has a Domain: All values in a column must come from the same
domain.
Example:
Age column → must contain valid age numbers only.
7. No Multi-valued or Composite Attributes in a Relation: These attributes must be
split before creating the table.
8. NULL Values are Allowed: But only when value is unknown or not applicable.

5
Relational Model Constraints
Constraints are rules applied to tables to ensure accuracy, validity, and consistency of the
data stored in a database.
Without constraints, incorrect or meaningless data may enter the system.
The main relational model constraints are:
1. Domain Constraints
2. Key Constraints
3. Integrity Constraints
o Entity Integrity
o Referential Integrity
1. Domain Constraints
A domain constraint defines the set of valid values that an attribute (column) can take.
Examples:
• Age must be between 18 and 60
• Gender must be only 'M' or 'F'
• Email must follow proper format
• Salary must be a positive number
Example Table: Employee
Emp_ID Name Age Salary
101 Arjun 25 30000
102 Reema 17 40000 (Invalid – age < 18)
103 Neha -5 25000 (Invalid – negative age)
Here,
• Age < 18 violates the domain constraint
• Negative age violates the domain constraint
Purpose: Ensures all data values fall within the permitted range or format.

6
2. Key Constraints
A key constraint ensures that a key attribute (primary key or candidate key) uniquely
identifies each row in a table.
Requirements of a Primary Key:
• Must be unique
• Must be NOT NULL
• Cannot change frequently
Example:
Roll_No (PK) Name Branch
1 Raj CS
2 Priya IT
2 Manoj CS (Duplicate Roll_No – violates key constraint)
Here,
• Roll_No must be unique
• Duplicate value is not allowed
Purpose: Prevents duplicate or missing primary key values.
3. Integrity Constraints
Integrity constraints ensure the correctness, consistency, and reliability of the data stored.
There are two important types:
a) Entity Integrity Constraint
Entity integrity ensures that the primary key cannot be NULL.
Example:
Emp_ID (PK) Name
1 Arjun
NULL Reema (Invalid – primary key cannot be NULL)
b) Referential Integrity Constraint
Referential integrity ensures that a foreign key must match an existing primary key in
another table, or be NULL.
It ensures relationships between tables remain valid.

7
Example:
Department Table
Dept_ID (PK) Dept_Name
10 CS
20 IT
Employee Table
Emp_ID Name Dept_ID (FK)
1 Arjun 10
2 Priya 20
3 Neha 30 (Invalid – Dept_ID 30 does not exist)
Here,
• Dept_ID 30 is invalid because the Department table does not contain 30.
• This violates referential integrity.
Purpose: Prevents orphan records and maintains valid links between tables.
Summary
Constraint Type Meaning Example of Violation
Domain Attribute values must be valid Age = -5
Key Primary key must be unique & not
null
Duplicate Roll_No
Entity Integrity Primary key cannot be null Emp_ID = NULL
Referential
Integrity
FK must refer to existing PK Dept_ID = 30 when only 10,20 exist
Relational Algebra
• Relational Algebra is a procedural query language used to operate on relations
(tables) in a database.
• It uses mathematical operations to retrieve data, combine tables, and filter records.
• Each operation takes one or more relations as input and produces a new relation as
output.

8
Basic Relational Algebra Operations
1. UNION ( ∪ )
Union returns all tuples from both relations without duplicates.
It is similar to combining two tables.
Conditions:
• Both relations must have same number of attributes
• Attributes must have the same domain (same type and meaning)
Example:
2. INTERSECTION ( ∩ )
Intersection returns the tuples that are common to both relations.
Example:

9
3. SELECTION ( σ )
Selection chooses specific rows from a relation based on a condition.
Symbol: σ(condition)(Relation)
Example:
Student
Name Age
Asha 20
Ravi 18
John 22
σ(Age > 18)(Student) → Select students older than 18
Name Age
Asha 20
John 22
4. PROJECTION ( π )
Projection selects specific columns from a table.
Symbol: π(column1, column2,...)(Relation)
Example:
Student
Roll Name Age
1 Asha 20
2 Ravi 18
π(Name, Age)(Student)
Name Age
Asha 20
Ravi 18
5. CARTESIAN PRODUCT ( × )
Cartesian Product combines every row of one relation with every row of another relation.
Also called Cross Product.
If R has m rows and S has n rows, output has m × n rows.
Example:

10
JOIN Operations
Join is used to combine related tuples from two relations based on a common attribute.
1. INNER JOIN
Inner join returns only the matching rows from both tables.
Example:
2. EQUI JOIN
Equi join is a join that uses the equality (=) condition only.
Example join condition:
Employee.Dept_ID = Department.Dept_ID
Equi join is a special case of inner join.
Example:
3. OUTER JOIN
Outer join returns:
• Matching rows
• PLUS non-matching rows from one or both tables

11
There are 3 types:
a) LEFT OUTER JOIN
Returns all rows from left table and matching rows from right table.
Example:
EmpID Name DeptID
1 Asha 10
2 Ravi NULL
3 Neha NULL
Ravi and Neha have no department → NULL.
b) RIGHT OUTER JOIN
Returns all rows from right table and matching rows from left.
Example:
EmpID Name DeptID
1 Asha 10
2 Ravi NULL
Right table (EmployeeDept) has only EmpID 1 and 2.
Neha is missing because RIGHT JOIN keeps right table rows only.
c) FULL OUTER JOIN
Returns:
• All matching rows
• All non-matching rows from both tables
Where no match exists, NULL is inserted.
EmpID Name DeptID
1 Asha 10
2 Ravi NULL
3 Neha NULL
Full outer join includes:
✔ Matching (Asha–10)
✔ Non-matching from left (Neha)
✔ Non-matching from right (none here)

12
Summary: Relational Algebra & Join Operations
Operation Purpose (Simple Meaning) Example
Union Combines rows from two tables
without duplicates
Students from Class A ∪ Class B
Intersection Shows only common rows from both
tables
Students common in Class A ∩ Class B
Selection (σ) Filters rows based on a condition σ Course = 'BCA'
Projection (π) Selects specific columns only π (Name, Course)
Cartesian
Product (×)
Combines all rows from two tables
(pairing each row)
Students × Subjects
Inner Join Returns only matching rows from
both tables
Matching Employee–Department IDs
Equi Join Inner join using equality (=) condition
only
Emp.DeptID = Dept.DeptID
Outer Join Matching rows plus non-matching
rows with NULLs
Employees with or without departments
*** ** ***

UNIT-II Relational Data Model BCA SEP SEM III DBMS

More Related Content

Similar to UNIT-II Relational Data Model BCA SEP SEM III DBMS

More from Kuvempu University

Recently uploaded

UNIT-II Relational Data Model BCA SEP SEM III DBMS