MODULE – II

1. Explain integrity constraints with suitable examples?

Integrity Constraints in DBMS
Integrity constraints are rules enforced on a database to maintain accuracy, consistency, and
reliability of data. These constraints ensure that data entered into the database follows predefined
rules and does not lead to inconsistencies or anomalies.
Types of Integrity Constraints with Examples
1. Domain Constraint
 Ensures that the values in a column must be from a predefined domain (valid set of
values).
 Example:
CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Age INT CHECK (Age >= 18 AND Age <= 65)
);
 Here, the Age column has a constraint that ensures only values between 18 and 65
are allowed.
2. Entity Integrity Constraint
 Ensures that each row in a table is uniquely identifiable.
 A Primary Key is used to enforce this constraint.
 Example:
CREATE TABLE Student
( Student_ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL
);
 The Student_ID column is the Primary Key, meaning each student must have a unique
ID, and NULL values are not allowed.
3. Referential Integrity Constraint
 Maintains relationships between tables by ensuring foreign key values in one table match
primary key values in another table.
 Example:
CREATE TABLE Department (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(50)
);

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
 Here, Dept_ID in the Employee table must match an existing Dept_ID in the
Department table.
4. Key Constraints (Primary Key and Unique Constraints)
 Ensures that each row in a table has a unique identifier.
 Example (Primary Key):
CREATE TABLE Product (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100) NOT NULL
);
 Product_ID uniquely identifies each product.
 Example (Unique Constraint):
CREATE TABLE Customer
(
Customer_ID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
 The Email column must have unique values across all rows.
5. Not Null Constraint
 Ensures that a column cannot have NULL values.
 Example:
CREATE TABLE Orders (
Order_ID INT PRIMARY
KEY,
Order_Date DATE NOT NULL
);
 Order_Date must always have a value.
6. Check Constraint
 Ensures that column values meet a specified condition.
 Example:
CREATE TABLE Employees (
Emp_ID INT PRIMARY KEY,
Salary DECIMAL(10,2),
CHECK (Salary >= 3000)
);
 Ensures that the salary is always at least 3000.
2. Explain Relational algebra fundamentals with suitable example.

Introduction
Relational Algebra is a procedural query language used in databases to retrieve and manipulate
data. It provides a foundation for query processing and optimization in relational database management
systems (RDBMS).
It consists of a set of operations that take one or more relations (tables) as input and produce
a new relation as output.

Basic Operations in Relational Algebra

1. Selection (σ - Sigma)
 Used to filter rows based on a condition.
 Syntax: σcondition(Relation)\sigma_{condition}
(Relation)σcondition(Relation)
 Example:
Suppose we have a Student table:
Student_ID Name Age Department

101 John 20 CS

102 Alice 22 IT
103 Bob 19 CS

104 Eve 21 IT

 Query: Find students from the "CS" department.

 σDepartment=′CS′(Student)\sigma_{Department='CS'}(Student)σDepartment=′CS′(Student)
 Result:

Student_ID Name Age Department

101 John 20 CS

103 Bob 19 CS
2. Projection (π - Pi)
 Used to retrieve specific columns from a table.
 Syntax:
πcolumn1,column2,...(Relation)\pi_{column1, column2, ...}(Relation)πcolumn1,column2,...(Relation)
 Example:
Query: Get names of all students.
πName(Student)\pi_{Name}(Student)πName(Student)
Result:

Name

John
Alice
Bob
Eve
3. Union (∪ - Set Union)
 Combines rows from two relations (tables) and removes duplicates.
 Conditions:
o Both relations must have the same attributes.
o Data types of corresponding columns must match.
 Syntax:
Relation1∪Relation2Relation1 \cup Relation2Relation1∪Relation2
 Example:
Suppose we have two tables:
CS_Students:

Student_ID Name

101 John

103 Bob
IT_Students:

Student_ID Name

102 Alice

104 Eve
Query: Find all students.
CS_Students∪IT_StudentsCS\_Students \cup IT\_StudentsCS_Students∪IT_Students
Result:

Student_ID Name

101 John

103 Bob
102 Alice

104 Eve
4. Set Difference (-)
 Returns rows from one relation that are not present in another relation.
 Syntax:
Relation1−Relation2Relation1 - Relation2Relation1−Relation2
 Example:
Query: Find CS students who are not in IT.
CS_Students−IT_StudentsCS\_Students - IT\
_StudentsCS_Students−IT_Students Result:

Student_ID Name

101 John

103 Bob
5. Cartesian Product (× - Cross Join)
 Returns all possible combinations of rows from two tables.
 Syntax:
Relation1×Relation2Relation1 \times Relation2Relation1×Relation2
 Example:
Suppose we have two tables:
Students:

Student_ID Name

101 John

102 Alice
Courses:

Course_ID Course_Name

C1 DBMS

C2 OS
Query: Get all possible student-course pairs.
Students×CoursesStudents \times CoursesStudents×Courses
Result:

Student_ID Name Course_ID Course_Name

101 John C1 DBMS

101 John C2 OS
102 Alice C1 DBMS

102 Alice C2 OS
6. Join (⋈ - Theta Join, Natural Join, etc.)
 Combines rows from two tables based on a common attribute.
 Example (Natural Join):
Suppose we have:
Students Table:

Student_ID Name Dept_ID

101 John 1

102 Alice 2
Department Table:
Dept_ID Dept_Name

1 CS

2 IT

Query: Find students along with their department names.

Students⋈DepartmentStudents \bowtie DepartmentStudents⋈Department
Result:

Student_ID Name Dept_ID Dept_Name

101 John 1 CS

102 Alice 2 IT
3. Distinguish between primary key and foreign key with example
Comparison PRIMARY KEY FOREIGN KEY
Basis
Basic It is used to identify each record It is used to links two tables together. It
into the database table uniquely. means the foreign key in one table refers to
the primary key of another table.
NULL The primary key column value can The foreign key column can accept a
never be NULL. NULL value.
Count A table can have only one primary A table can have more than one foreign
key. key.
Duplication The primary key is a unique We can store duplicate values in the
attribute; therefore, it cannot stores foreign key column.
duplicate values in relation.
Indexing The primary key is a clustered A foreign key is not a clustered index by
index by default, which means it is default. We can make clustered indexes
indexed automatically. manually.
Deletion The primary key value can't be The foreign key value can be removed
removed from the table. If you from the table without bothering that it
want to delete it, then make sure refers to the primary key of another table.
the referencing foreign key does
not contain its value.
Insertion We can insert the values into the The value that is not present in the column
primary key column without any of a primary key cannot be inserted into
limitation, either it present in a the referencing foreign key.
foreign key or not.
Temporary The primary key constraint can be A foreign key constraint cannot be defined
table defined on the temporary tables. on the temporary tables.
Relationship It cannot create a parent-child It can make a parent-child relationship in a
relationship in a table. table.

4. Discuss about views in relational model with example

A view in the relational model is a virtual table derived from one or more base tables. It does
not store data physically but provides a dynamic representation of data from the underlying
tables based on a specified query.
Features of Views
1. Security – Restricts access to certain rows and columns of a table.
2. Simplification – Provides a simplified representation of complex queries.
3. Logical Independence – Abstracts underlying schema changes from users.
4. Data Consistency – Ensures that users see up-to-date and consistent data.
 Creating a View
A view is created using the CREATE VIEW statement in SQL:
CREATE VIEW StudentView AS
SELECT sid, name, gpa
FROM Students
WHERE gpa > 3.0;
This view, StudentView, contains only the sid, name, and gpa of students whose gpa is greater than
3.0.
Example: Related Tables
Consider two tables:
Students Table:

sid name age gpa

101 Alice 20 3.5
102 Bob 19 2.8
103 Carol 21 3.2
Enrolled Table:

sid course_id grade

101 CS101 A
102 CS102 B
103 CS103 A

Using the StudentView, we get:

sid name gpa

101 Alice 3.5
103 Carol 3.2
Advantages of Using Views
 Security: Prevents unauthorized access to sensitive information.
 Simplified Queries: Users can query a view without needing complex joins.
 Logical Data Independence: Underlying tables can change without affecting users.
Updating Views
Views based on a single table can be updated if they do not contain aggregate functions.
Example:
UPDATE StudentView SET gpa = 3.8 WHERE sid = 103;
This will update Carol's gpa in the base Students table.
Dropping a View
To remove a view:
DROP VIEW StudentView;
Thus, views play a crucial role in the relational model by providing security, data abstraction,
and ease of access.
5. Describe the usage of null values. Compare various SQL operations with and without
null values.
Usage of Null Values in SQL
A NULL value in SQL represents missing, unknown, or inapplicable information. It is different
from zero or an empty string.
Scenarios Where NULL is Used
1. Unknown Value – When data is not available yet (e.g., a student’s grade is not assigned).
2. Not Applicable – When a column does not apply to a specific row (e.g., a middle
name for a person without one).
3. Intentionally Hidden – When sensitive data is hidden for privacy.
Example Table: Students

Student_ID Name Age Grade

101 Alice 20 A
102 Bob 21 NULL
103 Carol 19 B
104 Dave 22 NULL
Here, Bob and Dave have NULL values in the Grade column, meaning their grades are not yet
assigned.
Comparison of SQL Operations With and Without NULL Values
1. IS NULL vs. IS NOT NULL
To find students with no grade assigned:
SELECT * FROM Students WHERE Grade IS NULL;
Output:

Student_ID Name Age Grade

102 Bob 21 NULL
104 Dave 22 NULL

To find students who have received a grade:

SELECT * FROM Students WHERE Grade IS NOT NULL;
Output:

Student_ID Name Age Grade

101 Alice 20 A
103 Carol 19 B

2. Comparison Operators (=, !=) with NULL

SELECT * FROM Students WHERE Grade = 'A';
This works fine and returns students with grade 'A'. However, the following query will not
return students with NULL values:
SELECT * FROM Students WHERE Grade != 'A';
Incorrect Output (Excludes NULLs):

Student_ID Name Age Grade

103 Carol 19 B

To include NULLs, use:

SELECT * FROM Students WHERE Grade != 'A' OR Grade IS NULL;
Correct Output (Includes NULLs):

Student_ID Name Age Grade

102 Bob 21 NULL
103 Carol 19 B
104 Dave 22 NULL

3. Aggregate Functions (COUNT, AVG, SUM) with NULLs

 COUNT(*) includes NULL values:
SELECT COUNT(*) FROM Students;
Output: 4 (Counts all rows, including NULLs)
 COUNT(column_name) excludes NULL
values: SELECT COUNT(Grade) FROM
Students;
Output: 2 (Counts only A and B, ignoring NULLs)
 AVG() ignores NULL values:
SELECT AVG(Age) FROM Students;
It calculates the average excluding NULL values.
4. NULL in Arithmetic Operations
Any arithmetic operation with NULL results in NULL:
SELECT Age + 5 FROM Students;
For Bob and Dave (who have NULL grades), the result will be NULL.
To handle NULL values, use COALESCE():
SELECT Student_ID, COALESCE(Grade, 'No Grade') FROM Students;
Output:

Student_ID Grade
101 A
102 No Grade
103 B
104 No Grade
5. NULL in Joins
If Students table is joined with another table (Courses), NULL values can cause missing results
in INNER JOINs.
Example Tables
Courses Table

Course_ID Student_ID Course_Name

CS101 101 Database
CS102 103 Networks
CS103 105 AI

Query Using INNER JOIN

SELECT [Link], Courses.Course_Name
FROM Students INNER JOIN Courses ON Students.Student_ID = Courses.Student_ID;
Output (Excludes students with NULL matches):

Name Course_Name
Alice Database
Carol Networks

To include NULLs, use LEFT JOIN:

SELECT [Link], COALESCE(Courses.Course_Name, 'No Course')
FROM Students LEFT JOIN Courses ON Students.Student_ID = Courses.Student_ID;
Output (Includes students without a course):

Name Course_Name
Alice Database
Bob No Course
Carol Networks
Dave No Course

6. Write notes on Relational Model with an Example

The Relational Model is a way to structure and organize data in the form of tables, also known
as relations. It was introduced by E.F. Codd in 1970 and forms the foundation of Relational
Database Management Systems (RDBMS) such as MySQL, PostgreSQL, and Oracle.
Key Concepts of the Relational Model
1. Relation (Table) – A table with rows and columns.
2. Tuple (Row) – A single record in a table.
3. Attribute (Column) – A field in a table representing data type.
4. Domain – The set of allowable values for an attribute.
5. Primary Key – A unique identifier for each row in a table.
6. Foreign Key – An attribute that establishes a relationship between two tables.
7. Schema – The logical structure of the database.
Example of the Relational Model
Consider a Student Database with two tables:
1. Student Table (Stores student details)
2. Course Table (Stores course details)
Student Table
Student_ID Course_ID
Name Age
(PK) (FK)
101 Alex 20 C001
102 Ben 21 C002
103 Cara 22 C001
Course Table
Course_ID
Course_Name Credits
(PK)
C001 Database Systems 4
C002 Data Structures 3

Relational Model Features in the Example

1. Student_ID is the Primary Key (PK) in the Student Table.
2. Course_ID is the Primary Key (PK) in the Course Table.
3. Course_ID in the Student Table is a Foreign Key (FK) referring to the Course Table.
4. The two tables are related using the Course_ID field.
This relational model ensures data integrity, redundancy reduction, and efficient data
retrieval using SQL queries.
7. Write a short note on DDL and DML Commands with example.
1. DDL (Data Definition Language) Commands
DDL commands are used to define, modify, or delete the structure of a database.
Common DDL Commands:
1. CREATE – Creates a new database object (table, view, index).
2. ALTER – Modifies the structure of an existing table.
3. DROP – Deletes a table or database.
4. TRUNCATE – Removes all records from a table but keeps its structure.
Example of DDL Commands
Creating a Table:
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Course_ID VARCHAR(10)
);
Altering a Table:
ALTER TABLE Students ADD Email VARCHAR(100);
Dropping a Table:
DROP TABLE Students;
2. DML (Data Manipulation Language) Commands
DML commands are used to insert, update, delete, and retrieve data from the database.
Common DML Commands:
1. INSERT – Adds new records to a table.
2. UPDATE – Modifies existing records in a table.
3. DELETE – Removes specific records from a table.
4. SELECT – Retrieves data from tables.
Example of DML Commands
Inserting Data:
INSERT INTO Students (Student_ID, Name, Age, Course_ID)
VALUES (101, 'Alex', 20, 'C001');
Updating Data:
UPDATE Students
SET Age = 21
WHERE Student_ID = 101;
Deleting Data:
DELETE FROM Students
WHERE Student_ID = 101;
Selecting Data:
SELECT * FROM Students;
Differences Between DDL and DML
Feature DDL (Data Definition Language) DML (Data Manipulation Language)
Purpose Defines database structure Modifies data in tables
Examples CREATE, ALTER, DROP INSERT, UPDATE, DELETE
Effect Affects schema (structure) Affects records (data)
Rollback Auto-committed (cannot be undone) Can be rolled back using transactions
8. Explain Basic Form of SQL
Query. Basic Form of SQL Query
SQL (Structured Query Language) is used to interact with relational databases. The basic
form of an SQL query consists of three main clauses:
SELECT [DISTINCT] column_names
FROM table_name
WHERE condition;
Components of an SQL Query
1. SELECT Clause – Specifies the columns to retrieve from the table.
2. FROM Clause – Specifies the table from which data is retrieved.
3. WHERE Clause (Optional) – Filters the records based on a condition.
Example with Related Tables
Consider a university database with a table named Students:
Students Table

Student_ID Name Age GPA

101 Alice 20 3.5
102 Bob 21 2.8
103 Carol 19 3.2
104 Dave 22 3.9

Examples of Basic SQL Queries

1. Selecting All Columns
SELECT * FROM Students;
Output:
Student_ID Name Age GPA
101 Alice 20 3.5
102 Bob 21 2.8
103 Carol 19 3.2
104 Dave 22 3.9
2. Selecting Specific Columns
SELECT Name, GPA FROM Students;
Output:

Name GPA
Alice 3.5
Bob 2.8
Carol 3.2
Dave 3.9

3. Using the WHERE Clause (Filtering Records)

SELECT * FROM Students WHERE GPA > 3.0;
Output:

Student_ID Name Age GPA

101 Alice 20 3.5
103 Carol 19 3.2
104 Dave 22 3.9

4. Using DISTINCT to Remove Duplicates

SELECT DISTINCT GPA FROM Students;
If multiple students have the same GPA, this query will return unique values only.
5. Sorting Results Using ORDER BY
SELECT * FROM Students ORDER BY GPA DESC;
This query sorts students by GPA in descending order (highest GPA first).
6. Counting Records Using COUNT()
SELECT COUNT(*) FROM Students;
Output: 4 (Total number of students in the table)
9. Explain Relational Calculus with
examples. Relational Calculus in DBMS
Relational Calculus is a non-procedural query language in the Relational Model, which
means that instead of specifying how to retrieve data, we specify what data to retrieve based
on certain conditions. It uses mathematical logic and predicates to describe queries.
Types of Relational Calculus
1. Tuple Relational Calculus (TRC) – Uses tuples (rows) as variables.
2. Domain Relational Calculus (DRC) – Uses domain values (column values) as
variables.
1. Tuple Relational Calculus (TRC)
 Uses tuple variables to represent rows.
 Queries are written in the form:
{t | condition(t)}
o t represents a tuple (row).
o The condition specifies constraints that the tuple must satisfy.
Example
Consider a Students table:

Student_ID Name Age GPA

101 Alice 20 3.5
102 Bob 21 2.8
103 Carol 19 3.2
104 Dave 22 3.9

TRC Query: Find students with GPA greater than 3.0

{t | t ∈ Students ∧ [Link] > 3.0}
Result:

Student_ID Name Age GPA

101 Alice 20 3.5
103 Carol 19 3.2
104 Dave 22 3.9
TRC Query: Retrieve names of students aged 20 or above
{[Link] | t ∈ Students ∧ [Link] ≥ 20}
Result:

Name
Alice
Bob
Dave

2. Domain Relational Calculus (DRC)

 Uses domain variables instead of tuples.
 Queries are written in the form:
{<d1, d2, ...> | condition(d1, d2, ... )}
o d1, d2, ... represent column values.
Example
DRC Query: Find Student_ID and Name of students with GPA > 3.0
{<s_id, s_name> | ∃ age, gpa ( <s_id, s_name, age, gpa>∈ Students ∧ gpa > 3.0 )}
Result:

Student_ID Name
101 Alice
103 Carol
104 Dave

DRC Query: Find all students aged 21

{<s_id, s_name> | ∃ gpa ( <s_id, s_name, 21, gpa>∈ Students )}
Result:

Student_ID Name
102 Bob
Comparison: TRC vs. DRC

Feature Tuple Relational Calculus (TRC) Domain Relational Calculus (DRC)

Uses Tuples (rows) Domain values (column values)
Representation `{t condition(t)}`

t ∈ Students ∧ [Link] > 3.0}`

Example
`{t
Query
Feature Tuple Relational Calculus (TRC) Domain Relational Calculus (DRC)

10. Discuss about Derived operations in Relational algebra with

example. Derived Operations in Relational Algebra
Relational algebra is a procedural query language that consists of basic and derived operations.
Derived operations are those that can be expressed in terms of basic operations (such as
selection, projection, union, set difference, and Cartesian product). These operations are not
fundamental but are useful in simplifying complex queries.
Common Derived Operations
1. Intersection (∩)
2. Join (𝔚)
3. Division (÷)
4. Assignment (←)

1. Intersection (∩)
The intersection operation retrieves common tuples from two relations.
Example
Consider two relations:
Student1

ID Name
101 Alice
102 Bob
103 Charlie
Student2

ID Name
102 Bob
103 Charlie
104 David

Query: Find students who are present in both relations.

Student1∩Student2Student1 \cap Student2Student1∩Student2
Result

ID Name
102 Bob
103 Charlie

2. Join (𝔚)
The join operation combines related tuples from two relations based on a common attribute.
Example
Consider two relations:
Employee

Emp_ID Name
1 John
2 Alice
3 Bob

Department

Dept_ID Dept_Name
101 HR
102 IT
Query: Retrieve employees along with their department names.

Result

Emp_ID Name Dept_ID Dept_Name

1 John 101 HR
2 Alice 102 IT

3. Division (÷)
The division operation is used when we want to find tuples in one relation that are related to all
tuples in another relation.
Example
Consider two relations:
Course_Registered

Student Course
Alice Math
Alice Science
Bob Math
Bob Science
Charlie Math

All_Courses

Course
Math
Science

Query: Find students who have registered for all courses in All_Courses.
Result
Student
Alice
Bob
Charlie is not included because he has not registered for "Science."
4. Assignment (←)
The assignment operation is used to store intermediate results for use in complex queries.
Example

Here, Temp stores the joined table of employees and departments, and Result filters employees
working in the IT department.