Module – 1

----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul – 1
1) Define database. Elaborate component modules of DBMS and their interactions.
2) Describe the three-schema architecture. Why do we need mappings among schema levels?
3) Explain the difference between logical and physical data independence.
4) Draw an ER diagram for a COMPANY database with employee, department, project as strong entities and
dependent as weak entity. Specify the constraints, relationships and ratios in the ER diagram.
5) Define the following terms with example for each using ER notations:
Entity, attribute, composite attribute, multivalued attribute, participation role.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul – 2
6) What is DBMS? List the characteristics of database approach. Bring out major advantages of the
database approach.
7) Explain data independence. Draw 3 schema architecture and discuss the mapping.
8) Define the following: i) Database Administrator ii) Canned transaction
iii) Weak entity iv) Meta data v) Database Instance
9) Describe components modules of DBMS and its interaction with neat diagram.
10) Draw ER diagram of library database schema (at least 4 entities). Also specify primary keys, structural
constraints and explain.
11) Briefly discuss different types of end users of Database.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq dec/jan
12) Define the following terms: (i) Database (ii) Schema (iii) Entity
(iv) DDL (v) Degree of a relationship
13) Briefly explain characteristics of database approach.
14) List and explain advantages of using DBMS approach.
15) Define the following terms: (i) Cardinality (ii) Weak entity
(iii) Program data independence (iv) DML (v) Value sets
16) Describe three-schema architecture. Why do we need mappings between schema levels?
17) Explain different types of attributes in ER model with suitable example for each.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp- 1
18) Explain the types of end users with examples.
19) What are the advantages of using DBMS? Explain.
20) Describe the characteristics of database.
21) Explain three schema architecture. Why mappings b/w schema levels are required?
22) Explain the different types of attributes in ER model.
23) Explain the following. 1. Cardinality Ratio 2. Weal Entity
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp- 2
24) What is a Database? Explain the three schema architecture with neat diagram.
25) What are the advantages of using DBMS approach? Explain
26) Explain the following terms. 1. Data Dictionary 2. Weak Entity
27) Explain the categories of Data Models.
28) Explain the component modules of DBMS & their interactions with diagram.
29) What are the responsibilities of DBA & database designers?
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul – 1
1) Define database. Elaborate component modules of DBMS and their interactions.
----------------------------------------------------------------------------------------------------------------------------------------------
* A database is a structured collection of related data that can be easily accessed, managed, and updated.
It is designed to store, retrieve, and manipulate data efficiently.
Example: A student database that stores details like roll number, name, marks, etc.
----------------------------------------------------------------------------------------------------------------------------------------------

A DBMS consists of the following major components:

● Storage Manager: Manages the storage of data and meta-data.
● Query Processor: Converts user queries into low-level instructions for efficient access.
● Transaction Manager: Manages the execution of transactions while ensuring ACID properties (Atomicity,
Consistency, Isolation, andDurability).
● Concurrency Control Manager: Ensures transactions do not interfere with each other.

These modules interact to process user queries, ensure data consistency, manage concurrent access, and
store the data efficiently .

1. Interactive Query Interface

Users run ad-hoc queries via an interface; the query compiler and optimizer generate efficient plans.
Statistics from the system catalog help in optimizing query performance.

2. Application Programs
Programs in Java/C include DML statements extracted by a precompiler and compiled into canned
transactions.
These transactions are repeatedly executed with user-supplied parameters for consistent operations.

3. Runtime Database Processor

Executes queries and transactions, manages buffer, concurrency, and recovery modules.
Also updates statistics and coordinates with the stored data manager for disk-memory operations.

4. Client-Server Architecture
Clients or middle-tier servers communicate with a remote DB server; OS or DBMS handles scheduling.
This allows multiple users to access and operate on shared or distributed databases efficiently.

5. DBMS Interaction with OS

DBMS relies on OS for disk I/O and networking, and interfaces with compilers and clients.
It may bypass OS buffering in dedicated systems to optimize performance and control.
6) What is DBMS? List the characteristics of database approach. Bring out major advantages of the
database approach.
----------------------------------------------------------------------------------------------------------------------------------------------
A database is a structured collection of related data that can be easily accessed, managed, and updated. It
is designed to store, retrieve, and manipulate data efficiently.
Example: A student database that stores details like roll number, name, marks, etc.
----------------------------------------------------------------------------------------------------------------------------------------------
Database management system: Database management system (DBMS) is a collection of programs that
enables users to create and maintain a database. The DBMS is a general-purpose software system that
facilitates the processes of defining, constructing, manipulating, and sharing databases among various
users and applications
----------------------------------------------------------------------------------------------------------------------------------------------
Characteristics
Self-describing nature of a database system:
A database system stores not only data but also its structure and metadata (e.g., schema, constraints) in a
catalog, allowing it to describe and manage itself.
Structured Data Storage: Databases organize data into tables or collections with predefined schemas. This
structure helps in efficiently storing, retrieving, and managing data.
Data Integrity: Databases enforce rules and constraints to ensure the accuracy and consistency of the data.
Common integrity constraints include primary keys (which uniquely identify records), foreign keys (which
establish relationships between tables), and various data validation rules
Support of multiple views of the data: The database system allows different users to see customized views
of the same data based on their needs, enhancing flexibility and security.
Sharing of data and multiuser transaction processing: It enables multiple users to access and modify data
simultaneously while ensuring transaction integrity through coordinated processing and concurrency
control.
Data Security: Databases implement security measures to protect data from unauthorized access and
breaches. This includes user authentication, authorization, encryption, and auditing.
Backup and Recovery: Databases provide mechanisms for backing up data and recovering it in case of
failures or data loss. Regular backups and recovery plans are essential for data protection and continuity.
Data Relationships: Databases can represent complex relationships between data entities through
mechanisms such as foreign keys and joins. This allows for the efficient organization and retrieval of related
information.
----------------------------------------------------------------------------------------------------------------------------------------------

advantages
Controlling Redundancy.
• In traditional file processing technique, every user group maintains its own files for handling its data-
processing applications, each group independently keeps files which leads to redundancy. Redundancy is
storing the same data multiple times in the database. A DBMS reduces data redundancy by integrating all
the data into a single database
Restricting Unauthorized Access
• When multiple users share a large database, it is likely that most users will not be authorized to access all
information in the database. For example, financial data is often considered confidential, and only
authorized persons are allowed to access such data.
Providing Persistent Storage for Program Objects.
• Databases can be used to provide persistent storage for program objects and data structures. This is one
of the main reasons for object-oriented database systems.
Providing Storage Structures and Search Techniques for Efficient Query Processing.
• The DBMS often has a buffering or caching module that maintains parts of the database in main memory
buffers. The query processing and optimization module of the DBMS is responsible for choosing an efficient
query execution plan for each query based on the existing storage structures.
Providing Backup and Recovery.
• The backup and recovery subsystem of the DBMS is responsible for recovery. In case of hardware or
computer system fails in the middle of a complex update transaction
Providing Multiple User Interfaces.
• Different types of users with varying levels of technical knowledge use a database, a DBMS provide a
variety of user interfaces.. Users can be granted different levels of access, ensuring data security and
integrity.
Representing Complex Relationships among Data.
• A database may include numerous varieties of data that are interrelated in many ways. • A DBMS have
the capability to represent a variety of complex relationships among the data.
Data Integrity:
• Constraints: Integrity constraints such as primary keys, foreign keys, and unique constraints help maintain
the accuracy and consistency of data.
Data Independence:
• Logical Data Independence: Changes to the logical schema do not affect the application programs.
• Physical Data Independence: Changes to the physical storage of data do not affect the logical schema or
application programs.
2) Explain data independence. What is a Database? Describe the three-schema architecture. Why do we
need mappings among schema levels? with neat diagram.

----------------------------------------------------------------------------------------------------------------------------------------------
A database is a structured collection of related data that can be easily accessed, managed, and updated. It
is designed to store, retrieve, and manipulate data efficiently.
Example: A student database that stores details like roll number, name, marks, etc
----------------------------------------------------------------------------------------------------------------------------------------------
Data Independence:
• Logical Data Independence: Changes to the logical schema do not affect the application programs.
• Physical Data Independence: Changes to the physical storage of data do not affect the logical schema or
application programs.
----------------------------------------------------------------------------------------------------------------------------------------------
The goal of the three-schema architecture is to separate the user applications from the physical database.
In this architecture, schemas can be defined at the following three levels:

Internal Schema (Physical Schema):

* This schema describes how data is physically stored in the database. It deals with the storage structure,
file organization, indexing, and access methods.
* The internal schema uses a physical data model and describes the complete details of data storage and
access paths for the database

Conceptual Schema (Logical Schema):

which describes the structure of the whole database for a community of users. The conceptual schema
hides the details of physical storage structures and concentrates on describing entities, data types,
relationships, user operations, and constraints. A representational data model is used to describe the
conceptual schema when a database system is implemented.

External Schema (View Schema):

This schema defines different user views or perspectives of the database. Each view is a subset of the
database that is tailored to specific user needs or application requirements. It provides customized views of
the data for different users or applications, enhancing security and usability by restricting access to only the
relevant parts of the database.
----------------------------------------------------------------------------------------------------------------------------------------------
Importance of Mappings Between Schema Levels
1. Data Abstraction:
Mappings allow for data abstraction, separating the physical storage of data from its logical representation
and user views. This separation ensures that changes to the physical storage do not impact the logical
schema or user views.
2. Flexibility and Independence:
By defining mappings between schemas, databases can evolve independently at different levels. For
instance, if the internal schema changes the conceptual schema and external schemas can
remain unchanged, preserving the consistency of user views and logical data organization.
3. Consistency and Integrity:
Mappings ensure that the logical schema accurately reflects the data stored at the physical level and that
user views are consistently represented. This helps in maintaining data integrity across various schemas.
4. User-Specific Views:
Mappings allow different users to interact with the database according to their needs without affecting the
overall database structure.
5. Simplified Management:
Managing and maintaining the database becomes easier when different schemas are mapped correctly.
Database administrators can optimize physical storage and indexing without altering the logical schema or
user views.
----------------------------------------------------------------------------------------------------------------------------------------------
3) Explain the difference between logical and physical data independence.
----------------------------------------------------------------------------------------------------------------------------------------------
1. Logical Data Independence:

* Ability to change the logical schema (e.g., adding/removing attributes, entities) without affecting
external views or application programs.

* Example: Adding a new column like Email to the Student table without modifying user queries using
Name or Roll_No.
----------------------------------------------------------------------------------------------------------------------------------------------
2. Physical Data Independence:

* Ability to change the physical storage (e.g., indexing method, file organization) without affecting the
logical schema.

* Example: Changing storage from heap files to B+ trees without changing the table definition or
application code.
----------------------------------------------------------------------------------------------------------------------------------------------
17) Explain different types of attributes in ER model with suitable example for each.
----------------------------------------------------------------------------------------------------------------------------------------------
1. Simple Attribute An attribute that cannot be further subdivided into components is a simple attribute.
Example: The roll number of a student, the ID number of an employee, gender, and many more.

----------------------------------------------------------------------------------------------------------------------------------------------
[Link] Attribute An attribute that can be split into multiple sub-attributes is a composite attribute.
Example: The address can be further split into house number, street number, city, state, country, and pin
code, the name can also be split into first name middle name, and last name.

----------------------------------------------------------------------------------------------------------------------------------------------
3. Single-Valued Attribute The attribute which takes up only a single value for each entity instance is a
single-valued attribute.
Example: The age of a student, Aadhar card number.

----------------------------------------------------------------------------------------------------------------------------------------------
4. Multi-Valued Attribute The attribute which takes up more than a single value for each entity instance is a
multi-valued attribute. And it is represented by double oval shape. Example: Phone number of a student:
Landline and mobile.
5. Stored Attribute The stored attribute are those attribute which doesn’t require any type of further
update since they are stored in the database.
Example: DOB(Date of birth) is the stored attribute.

----------------------------------------------------------------------------------------------------------------------------------------------
6. Derived Attribute An attribute that can be derived from other attributes is derived attributes. And it is
represented by dotted oval shape.
Example: Total and average marks of a student, age of an employee that is derived from date of birth.

----------------------------------------------------------------------------------------------------------------------------------------------
7. Complex Attribute Those attributes, which can be formed by the nesting of composite and multi valued
attributes, are called “Complex Attributes“. These attributes are rarely used in DBMS(DataBase
Management System). That’s why they are not so popular.
Example: Address because address contain composite value like street, city, state, PIN code and also
multivalued because one people has more that one house address.

----------------------------------------------------------------------------------------------------------------------------------------------
8. Key attribute Key attributes are those attributes that can uniquely identify the entity in the entity set.
Example: Roll-No is the key attribute because it can uniquely identify the student.
----------------------------------------------------------------------------------------------------------------------------------------------
18) Explain the types of end users with examples.

----------------------------------------------------------------------------------------------------------------------------------------------
1. Casual End Users
• occasionally access the database, but they may need different information each time. They use a
sophisticated database query language to specify their requests and are typically middle- or high-level
managers or other occasional browsers.
• Example: A manager who occasionally queries the database to generate a sales report.
----------------------------------------------------------------------------------------------------------------------------------------------
2. Naive or Parametric End Users
• These users frequently use the database but have a limited understanding of its complexities. They often
interact with the system through pre-defined functions or transactions.
• Example:
1. Bank tellers check account balances and post withdrawals and deposits.
2. Reservation agents for airlines, hotels, and car rental companies check availability for a given request and
make reservations.
----------------------------------------------------------------------------------------------------------------------------------------------
3. Sophisticated End Users
• include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with
the facilities of the DBMS in order to implement their own applications to meet their complex
requirements.
• Example: A data analyst who writes SQL queries to extract, manipulate, and analyze large datasets for
business insights.
----------------------------------------------------------------------------------------------------------------------------------------------
4. Stand-alone Users
• These users maintain personal databases by using ready-made program packages that provide easy-to-
use menu-based or graphics-based interfaces.
• Example: A small business owner who uses Microsoft Access to manage customer information and sales
records.
----------------------------------------------------------------------------------------------------------------------------------------------
5) Define the following terms with example for each using ER notations:
Entity, attribute, composite attribute, multivalued attribute, participation role.

----------------------------------------------------------------------------------------------------------------------------------------------
Entity: An entity is a real-world object or thing represented in a database, depicted as a rectangle in ER
notation.
* Example: A "Student" entity, shown as a rectangle labeled "Student."

----------------------------------------------------------------------------------------------------------------------------------------------
Attribute: An attribute is a property or characteristic of an entity, represented as an oval connected to the
entity rectangle.
* Example: The "StudentID" attribute of the "Student" entity, shown as an oval labeled "StudentID" linked
to the "Student" rectangle.

----------------------------------------------------------------------------------------------------------------------------------------------
Composite Attribute: A composite attribute is an attribute made up of multiple sub-attributes, shown as
ovals connected to a parent oval.
* Example: The "Address" attribute of "Student" with sub-attributes "Street," "City," and "Zip," where
"Address" is an oval with connected ovals for its components.

----------------------------------------------------------------------------------------------------------------------------------------------
multivalued attribute: The attribute which takes up more than a single value for each entity instance is a
multi-valued attribute. And it is represented by double oval shape.
* Example: Phone number of a student: Landline and mobile.

----------------------------------------------------------------------------------------------------------------------------------------------
participation role:A participation role defines the function of an entity in a relationship, labeled on the line
connecting entities to a diamond-shaped relationship.
Example: In a relationship "Enrolls" between "Student" and "Course," the role "Enrollee" for "Student" is
labeled on the line from "Student" to the "Enrolls" diamond.
E1= Student ,R= Enrolls ,E2= Course
8) Define the following: 1) Database Administrator 2) Canned transaction
3) Weak entity 4) Meta data 5) Database Instance
6) Database 7) Schema 8) Entity
9) DDL 10) Degree of a relationship 11) Cardinality
12) Program data independence 13) DML 14) Value sets
15) Cardinality Ratio 16) Data Dictionary
----------------------------------------------------------------------------------------------------------------------------------------------
Database Administrator:
A database administrator (DBA) is a person responsible for managing and maintaining a database system,
ensuring its performance, security, and integrity. They handle tasks like user access control, backup,
recovery, and schema design. The DBA keep the database running smoothly.
----------------------------------------------------------------------------------------------------------------------------------------------
Canned Transaction:
A canned transaction is a pre-defined, standardized set of database operations executed as a single unit,
often used for routine tasks. For example, a bank withdrawal process that deducts money and updates the
account balance is a canned transaction
----------------------------------------------------------------------------------------------------------------------------------------------
weak entity
weak entity is an entity that cannot be uniquely identified by its own attributes alone. It relies on a
"strong" or "owner" entity and a relationship with that entity to ensure its uniqueness. It is represented with
a double rectangle in ER diagrams.
----------------------------------------------------------------------------------------------------------------------------------------------
Meta Data: Meta data is data about data, describing the structure, properties, and context of the database,
such as table names or data types. For example, a catalog storing information about a "Student" table’s
columns is meta data.
----------------------------------------------------------------------------------------------------------------------------------------------
Database Instance:
A database instance is the current state or snapshot of the database at a specific time, containing the
actual data stored. For example, a "Student" table with 50 records at 08:14 PM on July 05, 2025, is an
instance. It changes with data updates or transactions.
----------------------------------------------------------------------------------------------------------------------------------------------
database
A database is a structured collection of related data that can be easily accessed, managed, and updated. It
is designed to store, retrieve, and manipulate data efficiently.
Example: A student database that stores details like roll number, name, marks, etc.
----------------------------------------------------------------------------------------------------------------------------------------------
Schema
A schema is the logical structure or blueprint of a database, defining how data is organized, such as tables
and their relationships.

----------------------------------------------------------------------------------------------------------------------------------------------
Entity:
An entity is a real-world object or thing represented in a database, depicted as a rectangle in ER notation.
* Example: A "Student" entity, shown as a rectangle labeled "Student."

----------------------------------------------------------------------------------------------------------------------------------------------
DDL:
Data Definition Language (DDL) is a set of SQL commands used to define and modify the database
structure, like creating, droping or altering tables. For example, "CREATE TABLE Student (StudentID INT)" is
a DDL command. It helps design the schema.
----------------------------------------------------------------------------------------------------------------------------------------------
Degree of a Relationship:
The degree of a relationship is the number of entity types involved in a relationship, e.g., unary (one entity)
or binary (two entities). For instance, a "Manages" relationship between "Employee" and "Employee" is
unary with a degree of 1
----------------------------------------------------------------------------------------------------------------------------------------------
Cardinality: Cardinality indicates the number of instances of one entity that can be associated with each
instance of another entity in a relationship. For example, a "Student" can enroll in many "Courses,"
indicating a one-to-many cardinality
----------------------------------------------------------------------------------------------------------------------------------------------
Cardinality Ratio: Cardinality ratio specifies the maximum number of relationship instances an entity can
participate in, like 1:1 or 1:N.
----------------------------------------------------------------------------------------------------------------------------------------------
Program Data Independence: Program data independence allows application programs to remain
unaffected by changes in the database structure or storage details.
1. Logical Data Independence:
* Ability to change the logical schema (e.g., adding/removing attributes, entities) without affecting external
views or application programs.
2. Physical Data Independence:
* Ability to change the physical storage (e.g., indexing method, file organization) without affecting the
logical schema.
----------------------------------------------------------------------------------------------------------------------------------------------
DML: Data Manipulation Language (DML) is a set of SQL commands used to manipulate data, such as
inserting, updating, or deleting records. For example, "INSERT INTO Student VALUES (1, 'John')" is a DML
command
----------------------------------------------------------------------------------------------------------------------------------------------
Value sets
Each simple attribute of an entity is associates with a domain of values, or value set, which specifies the set
of values that may be assigned to that attribute for each entity.
For example, the "Grade" attribute of a "Student" might have a value set of {A, B, C, D, F}. It ensures data
validity.
----------------------------------------------------------------------------------------------------------------------------------------------
Data Dictionary: A data dictionary is a repository that stores meta data about the database, including table
definitions and constraints. For example, it might list all attributes of the "Student" table. It aids in database
management and access.
----------------------------------------------------------------------------------------------------------------------------------------------
27) Explain the categories of Data Models.
----------------------------------------------------------------------------------------------------------------------------------------------
High-level or conceptual data models provide concepts that are close to the way many users
perceive data, Conceptual data models use concepts such as entities, attributes, and relationships.
An entity represents a real-world object or concept, such as an employee or a project from the miniworld
that is described in the database.
An attribute represents some property of interest that further describes an entity, such as the employee’s
name or salary.
A relationship among two or more entities represents an association among the entities, for example, a
works-on relationship between an employee and a project.
----------------------------------------------------------------------------------------------------------------------------------------------
• Representational or implementation data models are used most frequently in traditional commercial
DBMSs. These include the widely used relational data model, as well as the so-called
legacy data models—the network and hierarchical models— Representational data models
represent data by using record structures and hence are sometimes called record-based data models.
----------------------------------------------------------------------------------------------------------------------------------------------
• low-level or physical data models describe how data is stored as files in the computer by representing
information such as record formats, record orderings, and access paths. An access path is a structure that
makes the search for particular database records efficient. An index is an example of an access path that
allows direct access to data using an index term or a keyword. Concepts provided by low-level data models
are generally meant for computer specialists, not for end users.

----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------

29) What are the responsibilities of DBA & database designers?

Responsibilities of Database Administrator (DBA)

• Database Management and Maintenance : The DBA is responsible for installing, configuring, and
upgrading the database system, ensuring its smooth operation, performance optimization, and
regular backups to prevent data loss. They monitor system health and apply patches or updates as
needed.
• Security and Access Control : The DBA manages user accounts, assigns privileges, and enforces
security policies to protect data from unauthorized access. They also handle recovery processes in
case of failures or breaches to maintain data integrity.
----------------------------------------------------------------------------------------------------------------------------------------------
Responsibilities of Database Designers
• Schema Design and Modeling Database designers create the logical structure of the database,
including tables, relationships, and constraints, using data models like ER diagrams. They ensure the
design meets the organization’s requirements and supports efficient data retrieval.
• Optimization and Testing They optimize the database design for performance, selecting appropriate
indexes and normalization techniques. They also test the design with sample data to identify and
resolve potential issues before implementation.
Module 2
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul --1
1) Discuss the update operations and dealing with constraint violations with suitable examples.
2) Illustrate the relational algebra operators with examples for select and project operation.
3) Discuss the characteristics of relations that make them different from ordinary table and files.
4) Perform
(i) Student ∪ Instructor (ii) Student ∩ Instructor
(iii) Student – Instructor (iv) Instructor − Student
on the following tables:

5) Consider the following relational database schema and write the queries in relational algebra
expressions:

EMP(Eno, Ename, Salary, Address, Phone, DNo)

DEPT(DNo, Dname, DLoc, MgrEno)
DEPENDENT(Eno, Dep_Name, Drelation, Dage)

(i) List all the employees who reside in 'Belagavi'.

(ii) List all the employees who earn salary between 30000 and 40000.
(iii) List all the employees who work for the 'Sales' department.
(iv) List all the employees who have at least one daughter.
(v) List the department names along with the names of the managers.

6) Consider the two tables T₁ and T₂ shown below:

Show the results of the following operations:

(i) T₁ ⨝_{T₁.P=T₂.A} T₂ (ii) T₁ ⨝_{T₁.Q=T₂.B} T₂
(iii) T₁ ⨝_{(T₁.P=T₂.A AND T₁.R=T₂.C)} T₂
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul – 2
----------------------------------------------------------------------------------------------------------------------------------------------
7) Briefly explain different types of update operation on relation database.
Show an example of violation of referential and entity integrity in each of update operation.
8) Consider following schema:
Suppliers (SID, SName, Address)
Parts (PID, PName, Colour)
Catalog (SID, PID, Price)

Write relational algebra expression for following queries:

i) Find the names of all red parts.
ii) Find all prices for parts that were red or green.
iii) Find the SID's of all suppliers who supply part that is red or green.
iv) Find the SID's of all suppliers who supply part that is red and green.
9) Describe the steps of ER to relational mapping with suitable examples and schema for each step.
10) Explain with example:
i) Division operation ii) Full outer join iii) Aggregate function
iv) Project operation v) Cartesian product
----------------------------------------------------------------------------------------------------------------------------------------------
Pya dec/jan
11) With suitable example, explain the entity integrity and referential integrity constraints. Why each is
considered important?
12) Discuss equijoin and natural join with suitable example using relational algebra notation.
13) Given the relational tables:

Write relational algebra expression for the following:

(i) Find the names and salaries of all employees in the ‘IT’ department.
(ii) Find the ID’s and names of employees who are in the ‘IT’ department and have a salary greater than
6000.
(iii) Find the ID’s and names of employees who are either in the ‘HR’ department or have a salary greater
than 6000.
(iv) Find the names of employees who are not in the ‘IT’ department.
(v) Find the names of employees along with their department names.

---
14) Explain any two operations that change the state of relation in a database. Provide suitable examples.
15) Discuss the aggregation functions and grouping in relational algebra with suitable examples.
16) Given the relational tables:

Write relational algebra expression for the following:

(i) Rename the student table to Learner and display it.

(ii) Find the students (learners) who are not enrolled in any project.
(iii) Find the students who are enrolled in all projects.
(iv) Find the students who are not enrolled in any project.
(v) Find the students who are enrolled in both the ‘Alpha’ and ‘Beta’ projects.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp – 1
17) Explain the different Relational Model constraints.
18) Demonstrate the concepts of Generalization & Specialization with examples.
19) Explain Entity Integrity Constraint & Referential Integrity Constraints? Why each of these is important in
a database.
20) Consider the Sailors-Boats-Reserves DB described:

s(sid, sname, rating, age)

b(bid, bname, color)
r(sid, bid, date)

Write each of the following queries in SQL:

1. Find the colors of boats reserved by Alber.
2. Find all sailor ids of sailors who have a rating of at least 8 or reserved boat 103.
3. Find the names of sailors who have not reserved a boat whose name contains the string “storm”. Order
the names in ascending order.
4. Find the sailor ids of sailors with age over 20 who have not reserved a boat whose name includes the
string “thunder”.

21) Discuss the Equijoin & Natural Join with suitable example.
22) Explain the relational algebra operation for set theory with examples.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp – 2
23) Explain the different types of update operations on relational database. How basic operation deals with
constraint violation.
24) Explain Unary relational operations with examples.
25) What is an Integrity Constraint? Explain the importance of Referential Integrity Constraint.
26) Explain the following relational algebra operation. JOIN, DIFFERENCE, SELECT, UNION
27) Discuss the E.R to Relational mapping algorithm with example for each step.
28) Explain the relational algebra operation for set theory with examples.
----------------------------------------------------------------------------------------------------------------------------------------------
1) Discuss the update operations and dealing with constraint violations with suitable examples
----------------------------------------------------------------------------------------------------------------------------------------------
Update operation includes:
1) Insert
2) Update
3) delete
----------------------------------------------------------------------------------------------------------------------------------------------
1) insert
* The Insert operation provides a list of attribute values for a new tuple t that is to be inserted into a
relation R.
* Insert can violate any of the four types of constraints.

a) Domain constraints Occurs when the inserted value does not match the specified data type or domain
range.
Ex: CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK
(Age BETWEEN 18 AND 30));
INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'John Doe', 15);

b) Key constraints Occurs when inserting a duplicate primary key or a non-unique value in a unique key
column.
Ex: INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'Alice', 22);
INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'Bob', 25);

c) Entity Integrity Occurs when a NULL value is inserted into a PRIMARY KEY column.
Ex: INSERT INTO Student (StudentID, Name, Age) VALUES (NULL, 'Charlie', 24);

d) Referential Integrity Occurs when an inserted foreign key value does not exist in the referenced table.
Ex: CREATE TABLE Course (CourseID INT PRIMARY KEY, CourseName VARCHAR(50));
CREATE TABLE Enrollment (StudentID INT, CourseID INT, FOREIGN KEY (CourseID) REFERENCES
Course(CourseID));
INSERT INTO Enrollment (StudentID, CourseID) VALUES (101, 500);
* attempts to add a record where CourseID = 500 does not exist in the Course table.
----------------------------------------------------------------------------------------------------------------------------------------------
2) UPDATE:
The Update operation is used to change the values of one or more attributes in a tuple (or tuples) of some
relation R.

a) Domain constraints Occurs when an updated value does not match the data type or domain range.

Ex: CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK
(Age BETWEEN 18 AND 30));
INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'Alice', 22);
UPDATE Student SET Age = 10 WHERE StudentID = 101;

b) Key constraints Occurs when updating a primary key to a duplicate value.

Ex: INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'Alice', 22);
INSERT INTO Student (StudentID, Name, Age) VALUES (102, 'bob', 25);
UPDATE Student SET StudentID = 102 WHERE StudentID = 101;
c) Entity Integrity Occurs when updating a PRIMARY KEY column to NULL.
Ex: UPDATE Student SET StudentID = NULL WHERE StudentID = 101;

d) Referential Integrity Occurs when updating a referenced foreign key to a non-existing value.
Ex: UPDATE Enrollment SET CourseID = 999 WHERE StudentID = 101;
* attempts to update a record where CourseID = 999 does not exist in the Course table.
----------------------------------------------------------------------------------------------------------------------------------------------
3) DELETE:
Delete is used to delete tuples. A DELETE operation can cause only referential integrity violations.
a) Referential Integrity Violation Occurs when attempting to delete a row that is referenced by a foreign
key.
Ex: DELETE FROM Course WHERE CourseID = 101;
* here KEY (CourseID) REFERENCES Course(CourseID))
----------------------------------------------------------------------------------------------------------------------------------------------
2) Illustrate the relational algebra operators with examples for select and project operation.

----------------------------------------------------------------------------------------------------------------------------------------------
The SELECT Operation.
* The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a selection
condition.
* The SELECT operation is visualized as a horizontal partition of the relation into two sets of tuples. Those
tuples that satisfy the condition are selected, and those
tuples that do not satisfy the condition are discarded.
* In general, the SELECT operation is denoted by σ <selection condition>(R)where the symbol σ (sigma) is
used to denote the SELECT operator and the selection condition is a Boolean expression (condition)
specified on the attributes of relation R.

Example 1: To select the EMPLOYEE tuples whose department is4, or those whose salary is greater than
$30,000, we can individually specify each of these two conditions with a SELECT operation as follows:
σDno=4(EMPLOYEE)
σSalary>3000(EMPLOYEE)

* The Boolean expression specified in <selection condition> is made up of a number of clauses of the form
<attribute name><comparison op><constant value>
or
<attribute name><comparison op><attribute name>
----------------------------------------------------------------------------------------------------------------------------------------------
The PROJECT Operation
* The SELECT operation chooses some of the rowsfrom the table while discarding other rows. The PROJECT
operation, on the otherhand, selects certain columns from the table and discards the other columns.
Example : To list each employee’s first and last name and salary, we can use the PROJECT operation as
follows:
πLname, Fname, Salary(EMPLOYEE)

* The general form of the PROJECT operation is π<attribute list>(R) where π (pi) is the symbol used to
represent the PROJECT operation, and <attributelist> is the desired sub list of attributes from the attributes
of relation R
* The PROJECT operation removes any duplicate tuples, so the result of the PROJECT operation is a set of
distinct tuples, and hence a valid relation. This is known as duplicate elimination.
----------------------------------------------------------------------------------------------------------------------------------------------
3) Discuss the characteristics of relations that make them different from ordinary table and files.

----------------------------------------------------------------------------------------------------------------------------------------------
1. Tabular Structure (Relations are Tables)
● A relation is represented as a table of rows and columns. Each row represents a tuple (or record), and
each column represents an attribute (orfield).
● Tuples in a relation are ordered conceptually but not physically, meaning the order of rows does not
affect the relation.
----------------------------------------------------------------------------------------------------------------------------------------------
2. Uniqueness of Tuples
● In a relational model, each tuple must be unique. No two rows can have identical values for all attributes.
This is enforced by the primary key, aunique identifier for each tuple.
----------------------------------------------------------------------------------------------------------------------------------------------
3. Attributes and Domains
● Each column (attribute) in a relation is associated with a domain, which defines the permissible values
the attribute can take. For example, a domain for a "Date of Birth" column might only accept valid date
values.
● Attributes must maintain atomicity
----------------------------------------------------------------------------------------------------------------------------------------------
4. No Duplicates
● Relations do not allow duplicate tuples. Each tuple must be distinct, as a relation is a set of tuples and
sets do not contain duplicate elements.
----------------------------------------------------------------------------------------------------------------------------------------------
5. Unordered Tuples and Attributes
● Tuples (rows) in a relation are not ordered. Unlike arrays or lists in programming, the order of tuples is
irrelevant, and the DBMS does not enforce a particular order.
● Attributes (columns) are also unordered, meaning the left-to-right order of columns does not affect the
definition or behavior of the relation.
----------------------------------------------------------------------------------------------------------------------------------------------
6. Integrity Constraints
● Relations are subject to various integrity constraints that ensure the correctness and consistency of the
data: example of constraints are ○ Domain constraints: ○ Key constraints:
--------------------------------------------------------------------------------------------------------------------------------------------
○ Referential integrity: ○ Entity integrity:
7. Relational Operations
● Relations support specific operations defined by relational algebra,including:
○ Selection: ○ Projection: ○ Join: ○ Union, Intersection, and Difference:
----------------------------------------------------------------------------------------------------------------------------------------------
8. Data Independence
● Relations are abstract representations of data, which provides data independence. Changes to the
physical storage of data do not affect the logical structure of relations, meaning users interact with data at a
higher level of abstraction.
----------------------------------------------------------------------------------------------------------------------------------------------
9. Set-Based Theory
10. Null Values
11. Normalization
----------------------------------------------------------------------------------------------------------------------------------------------
4) Perform
(i) Student ∪ Instructor (ii) Student ∩ Instructor
(iii) Student – Instructor (iv) Instructor − Student
on the following tables:
5) Consider the following relational database schema and write the queries in relational algebra
expressions:

EMP(Eno, Ename, Salary, Address, Phone, DNo)

DEPT(DNo, Dname, DLoc, MgrEno)
DEPENDENT(Eno, Dep_Name, Drelation, Dage)

(i) List all the employees who reside in 'Belagavi'.

(ii) List all the employees who earn salary between 30000 and 40000.
(iii) List all the employees who work for the 'Sales' department.
(iv) List all the employees who have at least one daughter.
(v) List the department names along with the names of the managers.
6) Consider the two tables T₁ and T₂ shown below:

Show the results of the following operations:

(i) T₁ ⨝_{T₁.P=T₂.A} T₂ (ii) T₁ ⨝_{T₁.Q=T₂.B} T₂
(iii) T₁ ⨝_{(T₁.P=T₂.A AND T₁.R=T₂.C)} T₂
8) Consider following schema:
Suppliers (SID, SName, Address)
Parts (PID, PName, Colour)
Catalog (SID, PID, Price)

Write relational algebra expression for following queries:

i) Find the names of all red parts.
ii) Find all prices for parts that were red or green.
iii) Find the SID's of all suppliers who supply part that is red or green.
iv) Find the SID's of all suppliers who supply part that is red and green.

i) π_PName(σ_Colour='red'(Parts))
ii) π_Price(σ_Colour='red'∨Colour='green'(Catalog ⨝ Parts))
or
π_Price((σ_Colour='red'∨Colour='green' (parts)) ⨝
[Link]=[Link] Catalog )
iii) π_SID(σ_Colour='red'∨Colour='green'(Catalog ⨝ Parts))
or
π_SID((σ_Colour='red'∨Colour='green' (parts)) ⨝
[Link]=[Link] Catalog )
iv) Reds := π_SID(σ_Colour='red'(Catalog ⨝ Parts))
Greens := π_SID(σ_Colour='green'(Catalog ⨝ Parts))
Answer := Reds ∩ Greens

10) Explain with example:

i) Division operation ii) Full outer join iii) Aggregate function
iv) Project operation v) Cartesian product
----------------------------------------------------------------------------------------------------------------------------------------------
i) Division Operation
* The division operation finds values in one relation that are associated with all values in another relation.
It is used to identify elements that have a complete relationship with another set.
Example:
Relation R (A, B):
 (1, x),
(1, y),
(2, x)
Relation S (B):
(x),
(y)
Division (R ÷ S): Finds A values in R that are associated with all B values in S. Here, A = 1 is associated
with both x and y, but A = 2 is only with x.
Result: (1)
----------------------------------------------------------------------------------------------------------------------------------------------
ii) Full Outer Join
A full outer join combines all rows from both relations, including matches and non-matches, with NULLs
where no match exists.
Example:
Relation R (A, B):
(1, x),
(2, y)
Relation S (B, C):
(x, 10),
(z, 20)
Full Outer Join (R ⨝ S): Matches on B, includes all rows.
Result:
A B C
1 x 10
2 y NULL
NULL z 20
----------------------------------------------------------------------------------------------------------------------------------------------
iii) Aggregate Function
Aggregate functions perform a calculation on a set of values (e.g., SUM, AVG, COUNT) and return a single
value.
Example:
Relation R (A, B):
(1, 10),
(2, 20),
(3, 30)
Aggregate (AVG(B)):
Calculates the average of B values.
Result: AVG(10, 20, 30) = 20.
----------------------------------------------------------------------------------------------------------------------------------------------
iv) Project Operation (π)
Used when: You want to select only specific columns.
Example:
Parts(PID, PName, Colour)

PID PName Colour

P1 Bolt Red
P2 Nut Blue
Query: Show only part names.
Relational Algebra: 𝜋𝑃𝑁𝑎𝑚𝑒(𝑃𝑎𝑟𝑡𝑠)
Result:
PName
Bolt
Nut
----------------------------------------------------------------------------------------------------------------------------------------------
v) Cartesian Product
The Cartesian product (×\times×) combines every row of one relation with every row of another, resulting
in all possible pairs.
Example:
Relation R (A):
(1),
(2)

Relation S
(B):
(x),
(y)
Cartesian Product (R ×\times× S): Pairs every A with every B. Result:
A B
1 x
1 y
2 x
2 y

11) With suitable example, explain the entity integrity and referential integrity constraints. Why each is
considered important?
----------------------------------------------------------------------------------------------------------------------------------------------
 (cascade delete) or that the foreign key should be set to null (set null) when a
customer record is deleted.

12) Discuss equijoin and natural join with suitable example using relational algebra notation.
1. Equijoin
An equijoin combines tuples from two relations where the values of specified attributes are equal, using
the ⋈ (theta join) operator with an equality condition. It retains all columns from both relations, including
the join attributes, which may appear multiple times.
Example:
Relation R (A, B):
A B
1 x
2 y

Relation S (B, C):

B C
x 10
y 20
z 30
Equijoin Expression: R ⋈R.B=S.B S
Condition: R.B=S.B (equality on attribute B).
Result:
A B B C
1 x x 10
2 y y 20
---------------------------------------------------------------------------------------------------------------------------------------------
2. Natural Join
A natural join is a special case of equijoin that combines tuples based on equality of all common attributes,
automatically eliminating duplicate columns. It is denoted by ⋈\bowtie⋈
Example:
Relation R (A, B) (same as above):
A B
1 x
2 y
Relation S (B, C) (same as above):
B C
x 10
y 20
z 30
Natural Join Expression: R⋈S R
Condition: Implicitly joins on the common attribute B, removing duplicate B columns.
Result:
A B C
1 x 10
2 y 20
13) Given the relational tables:
Write relational algebra expression for the following:
(i) Find the names and salaries of all employees in the ‘IT’ department.
(ii) Find the ID’s and names of employees who are in the ‘IT’ department and have a salary greater than
6000.
(iii) Find the ID’s and names of employees who are either in the ‘HR’ department or have a salary greater
than 6000.
(iv) Find the names of employees who are not in the ‘IT’ department.
(v) Find the names of employees along with their department names.

i) π Name, Salary (σ DeptName = ′ IT ′ (Employee ⋈ Department))

ii) π EID, Name (σ DeptName = ′ IT ′ ∧Salary>6000 (Employee ⋈ Department))

iii) πEID, Name (σ DeptName = ′HR′ ∨ Salary>6000(Employee ⋈ Department))

iv) π Name (σ DeptName =/= ′ IT ′ (Employee ⋈ Department))

v) π Name, DeptName (Employee ⋈ Department)

14) Explain any two operations that change the state of relation in a database. Provide suitable examples.
----------------------------------------------------------------------------------------------------------------------------------------------
In a relational database, operations that change the state of a relation (i.e., modify the data or structure of
a table) are typically part of the Data Manipulation Language (DML)
That are insert , update , delete ( WRITE ANY TWO )
----------------------------------------------------------------------------------------------------------------------------------------------
1. INSERT Operation The INSERT operation adds new rows (tuples) to a relation, thereby changing its
state by increasing the number of records.
Example: Before INSERT into Employee table:
EID Name DeptID Salary
1 Alice 10 5000
2 Bob 20 6000
SQL Operation:
INSERT INTO Employee VALUES (3, 'Eve', 20, 6500);
EID Name DeptID Salary
1 Alice 10 5000
2 Bob 20 6000
3 Eve 20 6500
----------------------------------------------------------------------------------------------------------------------------------------------
2. DELETE Operation
Purpose: Removes one or more tuples from a relation.
Effect on Relation State: Decreases the number of rows → removes data.
Example:
Before:
EID Name DeptID Salary
1 Alice 10 5000
2 Bob 20 6000
SQL:
DELETE FROM Employee WHERE EID = 1;
After:
EID Name DeptID Salary
2 Bob 20 6000
----------------------------------------------------------------------------------------------------------------------------------------------
3. UPDATE Operation
Purpose: Modifies the values of existing attributes in one or more tuples.
Effect on Relation State: Changes data values without adding or removing rows.
Example:
Before:
EID Name DeptID Salary
2 Bob 20 6000
SQL:
UPDATE Employee SET Salary = 7000 WHERE Name = 'Bob';
After:
EID Name DeptID Salary
2 Bob 20 7000
15) Discuss the aggregation functions and grouping in relational algebra with suitable examples.
----------------------------------------------------------------------------------------------------------------------------------------------
1. Aggregation Functions
Aggregation functions are used in relational algebra to perform calculations on sets of values in a column
(attribute). They return a single value from multiple tuples.
----------------------------------------------------------------------------------------------------------------------------------------------
Common Aggregation Functions:
Function Meaning
COUNT Counts the number of tuples
SUM Computes the total sum
AVG Computes the average
MAX Finds the maximum value
MIN Finds the minimum value
----------------------------------------------------------------------------------------------------------------------------------------------
Syntax (Extended Relational Algebra):
𝛾 grouping_attributes; aggregations (Relation)
----------------------------------------------------------------------------------------------------------------------------------------------
2. Grouping with Aggregation
Grouping is used when you want to apply an aggregation function within groups of tuples that share a
common value in one or more attributes.
----------------------------------------------------------------------------------------------------------------------------------------------
Example Schema:
Employee(EID, Name, DeptID, Salary)
EID Name DeptID Salary
1 Alice 10 5000
2 Bob 20 6000
3 Eve 20 6500
4 John 10 5500
----------------------------------------------------------------------------------------------------------------------------------------------
Example 1: Find total salary of all employees.
𝛾 SUM(Salary) (Employee)
Result:
SUM(Salary)
23000
----------------------------------------------------------------------------------------------------------------------------------------------
Example 2: Count number of employees in each department.
𝛾 DeptID; COUNT(EID) (Employee)
Result:
DeptID COUNT(EID)
10 2
20 2
----------------------------------------------------------------------------------------------------------------------------------------------
Example 3: Find average salary per department.
𝛾 DeptID; AVG(Salary) (Employee)
Result:
DeptID AVG(Salary)
10 5250
20 6250
16) Given the relational tables:
Write relational algebra expression for the following:

(i) Rename the student table to Learner and display it.

(ii) Find the students (learners) who are not enrolled in any project.
(iii) Find the students who are enrolled in all projects.
(iv) Find the students who are not enrolled in any project.
(v) Find the students who are enrolled in both the ‘Alpha’ and ‘Beta’ projects.
----------------------------------------------------------------------------------------------------------------------------------------------
i) ρ Learner (Student)

ii) π SID, Name (Student) – π SID, Name (Student ⋈ Enrollment)

iii) R = π SID ,PID (Enrollment)

S = π PID (Project)
Then: π SID (R÷S)

iv) π SID, Name (Student) – π SID, Name (Student ⋈ Enrollment)

v) π Name (( σ ProjectName = ′Alpha′ (Project) ⋈ Enrollment) ∩ ( σ ProjectName = ′Beta′ (Project) ⋈

Enrollment))

17) Explain the different Relational Model constraints

18) Demonstrate the concepts of Generalization & Specialization with examples.
----------------------------------------------------------------------------------------------------------------------------------------------
1. Generalization
* Generalization is the process of combining two or more lower-level entities into a single higher-level
entity based on common attributes.
* It’s a bottom-up approach in which shared features are abstracted into a superclass.
* Useful when multiple entity sets share common properties and you want to simplify the schema.
----------------------------------------------------------------------------------------------------------------------------------------------
Example:
Lower-level entities:
• Car(CarID, Model, EngineSize)
• Bike(BikeID, Model, EngineSize)
Generalized into:
• Vehicle(VehicleID, Model, EngineSize)

Now both Car and Bike become subsets of the Vehicle entity.
----------------------------------------------------------------------------------------------------------------------------------------------
ER Diagram Sketch
Vehicle
/ \
Car Bike

----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------

2. Specialization
* Specialization is the process of dividing a higher-level entity into two or more lower-level entities based
on distinguishing characteristics.
* It’s a top-down approach.
* Helps when a superclass has multiple distinct roles or behaviors that require specific attributes or
relationships.
----------------------------------------------------------------------------------------------------------------------------------------------
Example:
Higher-level entity:
• Employee(EID, Name, Salary)
Specialized into:
• Manager(EID, Bonus)
• Developer(EID, ProgrammingLanguage)

Now, Manager and Developer inherit from Employee but have additional attributes.
----------------------------------------------------------------------------------------------------------------------------------------------
ER Diagram Sketch
Employee
/ \
Manager Developer
22) Explain the relational algebra operation for set theory with examples.
----------------------------------------------------------------------------------------------------------------------------------------------
1. Union (∪)
Combines tuples from two relations, removing duplicates, provided they have the same schema
(compatible attributes).
Example:
o Relation R (A, B):
AB
1x
2y
o Relation S (A, B):
AB
2y
3z

o Expression: R∪S

o Result:
AB
1x
2y
3z
Explanation: Combines all unique tuples from R and S, removing the duplicate (2, y).
----------------------------------------------------------------------------------------------------------------------------------------------
2. Intersection (∩)
Returns tuples that appear in both relations, requiring compatible schemas.
Example:
o Relation R (A, B) (same as above):
AB
1x
2y
o Relation S (A, B) (same as above):
AB
2y
3z

o Expression: R∩S

o Result:
AB
2y
Explanation: Includes only the tuple (2, y) common to both R and S.
----------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------
3. Difference (−−)
Returns tuples that are in the first relation but not in the second, requiring compatible schemas.
Example:
o Relation R (A, B) (same as above):
AB
1x
2y
o Relation S (A, B) (same as above):
AB
2y
3z

o Expression: R−S

o Result:
AB
1x
Explanation: Includes only (1, x) from R that is not in S.
----------------------------------------------------------------------------------------------------------------------------------------------
4. Cartesian Product (×)
Combines every tuple of the first relation with every tuple of the second, creating all possible pairs. The
resulting relation has attributes from both relations.
Example:
o Relation R (A):
A
1
2
o Relation S (B):
B
x
y

o Expression: R×S

o Result:
AB
1x
1y
2x
2y
Explanation: Pairs each A value with each B value, resulting in 4 tuples.
----------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------
5. Rename (ρ)
Renames a relation or its attributes without changing the data, useful for clarity or reuse in expressions.

Syntax:
ρ NewRelationName (OldRelation)
or
ρ NewRelationName (A1,A2) (OldRelation )

Example:
If
Student(SID, Name)
Then
ρLearner(LID,LName)(Student)
→ Renames relation and attributes.
----------------------------------------------------------------------------------------------------------------------------------------------

26) Explain the following relational algebra operation. JOIN, DIFFERENCE, SELECT, UNION
----------------------------------------------------------------------------------------------------------------------------------------------
1. JOIN
The JOIN operation combines tuples from two relations based on a common attribute, creating a new
relation with matched rows. Common types include natural join (⋈\bowtie⋈) and theta join
(⋈θ\bowtie_{\theta}⋈θ).
Example:
o Relation R (A, B):
AB
1x
2y
o Relation S (B, C):
BC
x 10
y 20
z 30

o Expression: R⋈S R \bowtie S R⋈S (natural join on B)

o Result:
ABC
1 x 10
2 y 20
Explanation: Matches R and S on B, including only pairs where B values are equal, excluding z from S.
----------------------------------------------------------------------------------------------------------------------------------------------
2. DIFFERENCE (−-−)
The DIFFERENCE operation returns tuples that are in the first relation but not in the second, requiring
compatible schemas (same attributes).
Example:
o Relation R (A, B):
AB
1x
2y
o Relation S (A, B):
AB
2y
3z
o Expression: R−S R - S R−S
o Result:
AB
1x
Explanation: Includes only (1, x) from R that is not in S.
----------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------
3. SELECT (σ\sigmaσ)
The SELECT operation filters tuples from a single relation based on a condition, retaining rows that satisfy it.
Example:
o Relation R (A, B):
AB
1x
2y
3z

o Expression: σA>1(R)\sigma_{A > 1}(R)σA>1(R)

o Result:
AB
2y
3z
o Explanation: Selects tuples where A > 1, filtering out (1, x).
----------------------------------------------------------------------------------------------------------------------------------------------

4. UNION (∪\cup∪)
The UNION operation combines tuples from two relations, removing duplicates, provided they have the
same schema.
Example:
o Relation R (A, B):
AB
1x
2y
o Relation S (A, B):
AB
2y
3z

o Expression: R∪S R \cup S R∪S

o Result:
AB
1x
2y
3z
Explanation: Combines unique tuples from R and S, removing the duplicate (2, y).
----------------------------------------------------------------------------------------------------------------------------------------------

20) Consider the Sailors-Boats-Reserves DB described:

s(sid, sname, rating, age)
b(bid, bname, color)
r(sid, bid, date)

Write each of the following queries in SQL:

1. Find the colors of boats reserved by Alber.
2. Find all sailor ids of sailors who have a rating of at least 8 or reserved boat 103.
3. Find the names of sailors who have not reserved a boat whose name contains the string “storm”.
Order the names in ascending order.
4. Find the sailor ids of sailors with age over 20 who have not reserved a boat whose name includes the
string “thunder”.
----------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT [Link]
FROM s
JOIN r ON [Link] = [Link]
JOIN b ON [Link] = [Link]
WHERE [Link] = 'Alber';
----------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT sid
FROM s
WHERE rating >= 8
UNION
SELECT DISTINCT sid
FROM r
WHERE bid = 103;
----------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT [Link]
FROM s
WHERE [Link] NOT IN (
SELECT [Link]
FROM r
JOIN b ON [Link] = [Link]
WHERE [Link] LIKE '%storm%'
)
ORDER BY [Link] ASC;
----------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT [Link]
FROM s
WHERE [Link] > 20
AND [Link] NOT IN (
SELECT [Link]
FROM r
JOIN b ON [Link] = [Link]
WHERE [Link] LIKE '%thunder%'
);
----------------------------------------------------------------------------------------------------------------------------------------------
Module 3
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul – 1
1) Discuss the informal design guidelines for relation schema design.
2) Define 1NF, 2NF, and 3NF with examples.
3) Write the syntax for INSERT, UPDATE and DELETE statements in SQL and explain with suitable examples.
4) Discuss insertion, deletion and modification anomalies. Why are they considered bad? Illustrate with
examples.
5) Illustrate the following with suitable examples:
(i) Datatypes in SQL (ii) Substring Pattern Matching in SQL
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul -- 2
6) What is the need for normalization? Explain 2ⁿᵈ normal form. Consider the relation
EMP_PROJ = {SSn, Pnumber, Hours, Ename, Pname, Plocation}.
Assume {SSn, Pnumber} as a primary key. The dependencies are:
SSn, Pnumber → {Hours}
SSn → {Ename}
Pnumber → {Pname, Plocation}
Normalize the above relation into 2NF.
7) Illustrate the informal design guidelines for relation schemes with examples.
8) Write syntax with example in SQL for the DDL and DML SQL statements.
9) Consider the schema for college database:

Student (USN, Sname, Address, Phone, Gender)

SemSec (SSID, Sem, Sec)
Class (USN, SSID)
Subject (Subcode, Title, Sem, Credits)
IAMarks (USN, Subcode, SSID, Test1, Test2, Test3, Final IA)

Write SQL Query:

i) List all the students studying in 4ⁿᵗʰ sem ‘C’ section.
ii) Compute total number of male students in each semester.
iii) List Test1 marks of all students in all subjects.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq dec/jun
10) Explain Armstrong inference rules.
11) What is the need for normalization? Explain 1NF, 2NF and 3NF with examples.
12) What is functional dependency? Write an algorithm to find minimal cover for set of functional
dependencies. Construct minimal cover M for set of functional dependencies which are:
E = { B → A, D → A, AB → D }
13) Explain the types of update anomalies in SQL with an example.
14) Explain types of JDBC drivers.
15) Consider the schema R = ABCD, subjected to FDs F = { A → B, B → C }, and the non-binary partition D1 =
{ ACD, AB, BC }. State whether D1 is a lossless decomposition? [Give all steps in detail]
----------------------------------------------------------------------------------------------------------------------------------------------

Mqp –1
20) Explain the Cursor & its properties in embedded SQL with an example.
21) What is a Normalization? Explain the 1NF, 2NF & 3NF with examples.
22) Explain informal design guidelines for relational schema design.
23) What is Functional Dependency? Write algorithm to find minimal cover for set of Functional
Dependency. Construct the minimal cover m for set of functional dependency.
E = { B → A, D → A, AB → D }
25) Explain the types of update anomalies in SQL with an example.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp – 2
26) Illustrate insert, delete, update, alter & drop commands in SQL.
27) Explain informal design guidelines for relational schema design.
28) What is Functional dependency? Explain the inference rules for functional dependency with proof.
29) Consider two sets of functional dependency.
F = {A → C, AC → D, E → AD, E → H}
E = {A → CD, E → AH}. Are they Equivalent? (yes )
30) Explain the types of update anomalies in SQL with an example.
----------------------------------------------------------------------------------------------------------------------------------------------
1) Discuss the informal design guidelines for relation schema design.
* There are four informal guidelines that may be used as measures to determine the quality of relation
Schema design:
1) Making sure that the semantics of the attributes is clear in the Schema.
2) Reducing the redundant information in tuples.
3) Reducing the NULL values in tuples.
4) Disallowing the possibility of generating spurious tuples.
----------------------------------------------------------------------------------------------------------------------------------------------
1) Imparting clear semantics to attributes in a relation.
* When we group attributes to form a relation, the attributes must have a real world meaning and proper
interpretation associated with them.
* Consider the Schema below:
EMPLOYEE [Ename | ssn | bdate | address | dnumber]
* The easier to explain the semantics of the relation, the better the relation Schema design will be.
EMPLOYEE_DETAILS [Ename | ssn | bdate | address | dnumber | dept_pos]
Guideline 1:
* Design a relational Schema so that it is easy to explain its meaning. Do not combine attributes from
multiple entity types and relationship types into a single relation.
Violating guideline 1:
EMPLOYEE_DEPT [Ename | ssn | bdate | address | dnumber | Dname | mgr_ssn]
* This combines the attributes the two real world entities EMPLOYEE and DEPARTMENT, which violates
guideline 1.
----------------------------------------------------------------------------------------------------------------------------------------------
2) Reducing the redundant information in tuples:
* One goal of Schema design is to minimize the storage space used by the base relations (and hence the
corresponding files).
* Grouping attributes into relation schemas has a significant effect on storage space.
* Storing natural joins of base relations leads to an additional problem referred to as update anomalies.
* These can be classified into insertion anomalies, deletion anomalies, and modification anomalies.
----------------------------------------------------------------------------------------------------------------------------------------------
3) NULL Values in tuples:
* NULL values lead to problems with understanding the meaning of the attributes.
* Also, it causes confusion in COUNT and SUM operators.
* Also, if NULL value comes in comparison or JOIN operations, the result will be unpredictable.
Guideline 3:
* As far as possible, avoid placing attributes in a relation whose values may frequently be NULL unless they
do not apply to the majority of the tuples.
----------------------------------------------------------------------------------------------------------------------------------------------
4) Generation of Spurious tuples:
EMP_PROJ [Ssn | Pnumber | Hours | Ename | Pname | Location]
EMP_LOCS [Ename | Plocation]
Emp_PROJ1 [Ssn | Pnumber | Hours | Pname | Pnumber]
* The relation Emp_PROJ is decomposed into two relations Emp_LOCS and Emp_PROJ1. But if the two
tables are joined by JOIN operation, it will generate some unexpected spurious tuples. So the joined table
will not be same as EMP-PROJ.
Guideline 4
* Design relation schema so that they can be joined with equality conditions they can be and they are
appropriately deleted on attributes to guarantee that no spurious tuples will be generated.
2) Define 1NF, 2NF, and 3NF with examples.
----------------------------------------------------------------------------------------------------------------------------------------------
Normalization
Normalization is the process of organizing data in a database to:
* Eliminate redundancy (duplicate data)
* Ensure data dependencies make sense
* Improve data integrity and efficiency
----------------------------------------------------------------------------------------------------------------------------------------------
First Normal Form (1NF)
*Each column should contain atomic (indivisible) values.
* All entries in a column must be of the same data type.
* Each row should be unique (have a unique identifier/primary key).
* No repeating groups or arrays allowed.

----------------------------------------------------------------------------------------------------------------------------------------------
2. Second Normal Form (2NF) Definition:
A table is in Second Normal Form (2NF) if it is in 1NF and all non key attributes are fully functionally
dependent on the entire primary key. In other words, there should be no partial dependency of any column
on a subset of a composite primary key.
3. Third Normal Form (3NF)
A table is in Third Normal Form (3NF) if it is in 2NF and all the attributes are functionally dependent only on
the primary key, and not on any other non-key attributes. This means there should be no transitive
dependency.
12) What is functional dependency? Write an algorithm to find minimal cover for set of functional
dependencies. Construct minimal cover M for set of functional dependencies which are:
E = { B → A, D → A, AB → D }

A functional dependency is a relationship between attributes in a relational database where the value of
one set of attributes uniquely determines the value of another set of attributes

In a relation R, an attribute Y is functionally dependent on attribute X (written as X → Y) if, for any two
tuples t1 and t2,

t1[X] = t2[X] ⇒ t1[Y] = t2[Y]

This means the value of X uniquely determines the value of Y

Example:
Consider a relation:
Student(StudentID, Name, Dept, Email)

Here:
• StudentID → Name (Each student ID corresponds to exactly one student name)
• StudentID → Email (Each student ID has one email)
• But Name → StudentID may not hold if multiple students have the same name.

Algorithm to Find Minimal Cover (Canonical Cover)

The minimal cover (also called canonical cover) of a set of functional dependencies F is an equivalent set of
dependencies Fₘ that satisfies:
1. The right-hand side of each FD is a single attribute.
2. The left-hand side is minimal (no extra attributes).
3. No FD is redundant.

Steps:
1. Split RHS:
o Ensure each FD has a single attribute on the right-hand side.
o E.g., A → BC becomes A → B, A → C
o
2. Remove Extraneous Attributes from LHS:
o For each FD X → Y, check if any attribute in X can be removed without changing the closure.
o
3. Remove Redundant FDs:
o Temporarily remove an FD and compute the closure of remaining FDs.
o If the removed FD can be derived from the rest, it's redundant.
Apply the Algorithm to Given Set:
E = { B → A, D → A, AB → D }

Step 1: Split RHS

All FDs already have a single attribute on RHS, so no change.
• B→A
• D→A
• AB → D

Step 2: Remove Extraneous Attributes in LHS

Check AB → D: Is A or B extraneous?
• Test if B → D:
Given B → A and B → A, D → A, we cannot derive D, so B → D is not valid.
• Test if A → D:
No FD for A exists.
→ So, AB → D cannot be simplified. No extraneous attributes.

Step 3: Check for Redundant FDs

Check if any FD can be derived from the others:
• Is B → A redundant?
Remaining FDs: D → A, AB → D
From these, we can't get B → A. So not redundant.
• Is D → A redundant?
Remaining FDs: B → A, AB → D
Can’t derive D → A. So not redundant.
• Is AB → D redundant?
Remaining FDs: B → A, D → A
Can't derive AB → D. So not redundant.

Final Minimal Cover M:

M = { B → A, D → A, AB → D }
No change — the given set is already in minimal form.
28) What is Functional dependency? Explain the inference rules for functional dependency with proof.

A functional dependency is a relationship between attributes in a relational database where the value of
one set of attributes uniquely determines the value of another set of attributes

In a relation R, an attribute Y is functionally dependent on attribute X (written as X → Y) if, for any two
tuples t1 and t2,

t1[X] = t2[X] ⇒ t1[Y] = t2[Y]

This means the value of X uniquely determines the value of Y

Example:
Consider a relation:
Student(StudentID, Name, Dept, Email)

Here:
• StudentID → Name (Each student ID corresponds to exactly one student name)
• StudentID → Email (Each student ID has one email)
• But Name → StudentID may not hold if multiple students have the same name.

10) Explain Armstrong inference rules.

Inference Rules for Functional Dependencies (Armstrong’s Axioms)

Armstrong’s Axioms are a sound and complete set of rules to derive all possible functional dependencies
from a given set.

1. Reflexivity Rule
If Y is a subset of X, then:
X→Y
Proof:
If you know the values of X, you naturally know the values of any subset of X.
Example:
If X = {StudentID, Name} and Y = {Name},
then {StudentID, Name} → Name holds.

2. Augmentation Rule
If:
X→Y
then for any Z,
XZ → YZ
Proof:
If X determines Y, then adding the same attributes (Z) to both sides doesn’t change the dependency.
Example:
If StudentID → Name, then
StudentID, Dept → Name, Dept
 3. Transitivity Rule
If:
X → Y and Y → Z
then:
X→Z
Proof:
If X determines Y, and Y determines Z, then X indirectly determines Z.
Example:
StudentID → Name, and Name → Email
⇒ StudentID → Email

4. Union Rule
If:
X → Y and X → Z
then:
X → YZ
Proof:
From augmentation: X → Y ⇒ X → Y, X → Z ⇒ X → Z
By combining them, X → YZ

5. Decomposition Rule
If:
X → YZ
then:
X → Y and X → Z
Proof:
Since Y and Z are subsets of YZ, apply reflexivity.
4) Discuss insertion, deletion and modification anomalies. Why are they considered bad? Illustrate with
examples.
25) Explain the types of update anomalies in SQL with an example.

The three major types of anomalies are:

• Insertion Anomaly
• Deletion Anomaly
• Modification (Update) Anomaly

1. Insertion Anomaly
Insertion anomaly occurs when certain attributes cannot be inserted into the database without the
presence of some other, unnecessary data.

➤ Example:
Consider this table:
StudentID StudentName CourseID CourseName
101 Asha CS101 DBMS

Now, suppose a new course "AI" (CourseID: CS102) is introduced, but no student has enrolled yet.
You cannot insert this course into the table because it needs student details due to the structure. This
causes an insertion anomaly.

➤ Solution:
Separate into:
• Student(StudentID, StudentName)
• Course(CourseID, CourseName)
• Enrollment(StudentID, CourseID)
This allows inserting courses independently of students.

2. Deletion Anomaly
A deletion anomaly occurs when deleting a record unintentionally removes additional valuable data that
should have been kept.

➤ Example:
Using the same table:
StudentID StudentName CourseID CourseName
101 Asha CS101 DBMS
102 Raj CS102 Java

If student Raj drops out, we delete his row. But this deletion also removes information about the Java
course, even if it's still being offered.

➤ Solution:
Keep Course data in a separate table so that deleting a student doesn't affect course data.
3. Modification (Update) Anomaly
A modification anomaly occurs when a single piece of data exists in multiple places, and we must update all
of them consistently. If even one update is missed, it causes inconsistent or incorrect data.

➤ Example:
Suppose the instructor for "DBMS" changes from "Dr. Rao" to "Dr. Singh". If "DBMS" appears in multiple
rows (one for each student), we must update every occurrence.
StudentID StudentName CourseID CourseName Instructor
101 Asha CS101 DBMS Dr. Rao
102 Raj CS101 DBMS Dr. Rao

If we update only one row, the data becomes inconsistent.

➤ Solution:
Store instructor details separately in a Course table:
Course(CourseID, CourseName, Instructor)

Why are they considered bad?

Data Inconsistency
Update anomalies may lead to different values for the same data.
Loss of Information
Deletion anomalies may cause important data to be lost permanently.
Incomplete Data Entry
Insertion anomalies prevent the addition of certain data unless irrelevant data is also provided.
Poor Maintainability
More time and effort is needed to manage redundant data.
Violation of Data Integrity
Anomalies often result in logically incorrect or incomplete databases.
3) Write the syntax for INSERT, UPDATE and DELETE statements in SQL and explain with suitable
examples.

1. INSERT Command The insert statement is used To add new records into a table.

➤ Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

➤ Example:
-- Creating a table first
CREATE TABLE Student (
StudentID INT,
Name VARCHAR(50),
Age INT
);

-- Inserting values
INSERT INTO Student (StudentID, Name, Age)
VALUES (101, 'Asha', 20);

2. DELETE Command The delete statement is used To remove records from a table.

➤ Syntax:
DELETE FROM table_name
WHERE condition;

➤ Example:
-- Delete a specific student by ID
DELETE FROM Student
WHERE StudentID = 101;

-- Delete all records

DELETE FROM Student;

3. UPDATE Command The update statement is used To modify existing data in a table.

➤ Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

➤ Example:
-- Update name of a student
UPDATE Student
SET Name = 'Ashwini'
WHERE StudentID = 102;
5) Illustrate the following with suitable examples:
(i) Datatypes in SQL (ii) Substring Pattern Matching in SQL
----------------------------------------------------------------------------------------------------------------------------------------------
(i) Datatypes in SQL

SQL provides various data types to define the type of data that can be stored in a column of a table. Here
are some common SQL datatypes:
----------------------------------------------------------------------------------------------------------------------------------------------
● INT: Stores integer values (e.g., 100, -45).
CREATE TABLE Employees (
ID INT,
Name VARCHAR(50),
Age INT
);
----------------------------------------------------------------------------------------------------------------------------------------------
● VARCHAR(size): Stores variable-length string data (e.g., John, Database).
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100)
);
----------------------------------------------------------------------------------------------------------------------------------------------
● FLOAT: Stores floating-point numbers (e.g., 10.5, -2.75).
CREATE TABLE Products (
ProductID INT,
Price FLOAT
);
----------------------------------------------------------------------------------------------------------------------------------------------
● DATE: Stores date values in the format YYYY-MM-DD (e.g., 2023-09-20).
CREATE TABLE Orders (OrderID INT,
OrderDate DATE
);
----------------------------------------------------------------------------------------------------------------------------------------------
● BOOLEAN: Stores true/false values.
CREATE TABLE Flags (
FlagID INT,
IsActive BOOLEAN
);

----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------

(ii) Substring Pattern Matching in SQL

SQL provides the LIKE operator for pattern matching using wildcards, and it is commonly used to search for
substrings in string data. The two main wildcards used with LIKE are:

● %: Represents zero or more characters.

● _: Represents a single character.
Examples:
----------------------------------------------------------------------------------------------------------------------------------------------
● Find names that start with 'A':
SELECT Name
FROM Students
WHERE Name LIKE 'A%';
This query retrieves all names that begin with the letter 'A'.
----------------------------------------------------------------------------------------------------------------------------------------------
● Find names that end with 'son':
SELECT Name
FROM Employees
WHERE Name LIKE '%son';
----------------------------------------------------------------------------------------------------------------------------------------------
● Find names where the second letter is 'a':
SELECT Name
FROM Employees
WHERE Name LIKE '_a%';
----------------------------------------------------------------------------------------------------------------------------------------------
● Find names that contain 'dat':
SELECT Name
FROM Employees
WHERE Name LIKE '%dat%';
----------------------------------------------------------------------------------------------------------------------------------------------
6) What is the need for normalization? Explain 2ⁿᵈ normal form. Consider the relation
EMP_PROJ = {SSn, Pnumber, Hours, Ename, Pname, Plocation}.
Assume {SSn, Pnumber} as a primary key. The dependencies are:
SSn, Pnumber → {Hours}
SSn → {Ename}
Pnumber → {Pname, Plocation}

----------------------------------------------------------------------------------------------------------------------------------------------
Normalization
Normalization is the process of organizing data in a database to:
* Eliminate redundancy (duplicate data)
* Ensure data dependencies make sense
* Improve data integrity and efficiency
----------------------------------------------------------------------------------------------------------------------------------------------
Problems without normalization:
* Redundant data: Repeating the same information (e.g., employee name for each project)
* Update anomaly: Updating in one place but not others causes inconsistency
* Insertion anomaly: Cannot insert data unless some unrelated field is known
* Deletion anomaly: Deleting one record may delete useful data
* Improves data consistency and efficiency
----------------------------------------------------------------------------------------------------------------------------------------------
Second Normal Form (2NF) Definition:
A table is in Second Normal Form (2NF) if it is in 1NF and all non key attributes are fully functionally
dependent on the entire primary key. In other words, there should be no partial dependency of any column
on a subset of a composite primary key.
----------------------------------------------------------------------------------------------------------------------------------------------
Step 1: Identify Partial Dependencies

SSn → Ename
→ Partial dependency (only part of the key)

Pnumber → Pname, Plocation

→ Also a partial dependency
→ So the relation is not in 2NF.
----------------------------------------------------------------------------------------------------------------------------------------------
Step 2: Decompose the Relation
We'll split the table into 3 relations, each removing partial dependencies.
----------------------------------------------------------------------------------------------------------------------------------------------
Decomposed Relations in 2NF:

1) Emp_Proj_Hours
Stores hours worked on projects.

Emp_Proj_Hours(SSn, Pnumber, Hours)

Primary Key: (SSn, Pnumber)
----------------------------------------------------------------------------------------------------------------------------------------------
2) Employee
Stores employee information.

Employee(SSn, Ename)
Primary Key: SSn
----------------------------------------------------------------------------------------------------------------------------------------------
3) Project
Stores project information.

Project(Pnumber, Pname, Plocation)

Primary Key: Pnumber
----------------------------------------------------------------------------------------------------------------------------------------------
9) Consider the schema for college database:

Student (USN, Sname, Address, Phone, Gender)

SemSec (SSID, Sem, Sec)
Class (USN, SSID)
Subject (Subcode, Title, Sem, Credits)
IAMarks (USN, Subcode, SSID, Test1, Test2, Test3, Final IA)

Write SQL Query:

i) List all the students studying in 4ⁿᵗʰ sem ‘C’ section.
ii) Compute total number of male students in each semester.
iii) List Test1 marks of all students in all subjects.
----------------------------------------------------------------------------------------------------------------------------------------------
1)
SELECT DISTINCT [Link], [Link]
FROM Student s
JOIN Class c ON [Link] = [Link]
JOIN SemSec ss ON [Link] = [Link]
WHERE [Link] = 4 AND [Link] = 'C';
----------------------------------------------------------------------------------------------------------------------------------------------
2)
SELECT [Link], COUNT(DISTINCT [Link]) AS Male_Student_Count
FROM Student s
JOIN Class c ON [Link] = [Link]
JOIN SemSec ss ON [Link] = [Link]
WHERE [Link] = 'M'
GROUP BY [Link];
----------------------------------------------------------------------------------------------------------------------------------------------
3)
SELECT [Link], [Link], [Link], [Link], ia.Test1
FROM Student s
JOIN IAMarks ia ON [Link] = [Link]
JOIN Subject sub ON [Link] = [Link];
----------------------------------------------------------------------------------------------------------------------------------------------
8) Write syntax with example in SQL for the DDL and DML SQL statements

DDL (Data Definition Language)

DDL commands are used to define or modify the structure of database objects like tables, schemas, etc.
Common DDL Commands: CREATE ALTER DROP

1. CREATE – To create a new table

Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

Example:
CREATE TABLE Student (
USN VARCHAR(10) PRIMARY KEY,
Sname VARCHAR(50),
Address VARCHAR(100),
Phone BIGINT,
Gender CHAR(1)
);

4. ALTER Command
The ALTER statement is used To modify the structure of a table such as adding, deleting, or modifying
columns.

➤ Syntax:
• Add a column:
ALTER TABLE table_name
ADD column_name datatype;
➤ Example:
ALTER TABLE Student
ADD Email VARCHAR(100);

➤ Syntax:
• Modify a column:
ALTER TABLE table_name
MODIFY column_name new_datatype;
➤ Example:
ALTER TABLE Student
MODIFY Age VARCHAR(10);

5. DROP Command
The DROP statement is used To permanently delete a table or database from the system.
All data and structure will be lost. This action cannot be undone

➤ Syntax:
DROP TABLE table_name;
DROP DATABASE database_name;

➤ Example:
-- Drop a table
DROP TABLE Student;

-- Drop a database
DROP DATABASE CollegeDB;

DML (Data Manipulation Language)

DML commands are used to manipulate data stored in database tables.
Common DML Commands: INSERT UPDATE DELETE

1. INSERT Command The insert statement is used To add new records into a table.

➤ Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

➤ Example:
-- Creating a table first
CREATE TABLE Student (
StudentID INT,
Name VARCHAR(50),
Age INT
);

-- Inserting values
INSERT INTO Student (StudentID, Name, Age)
VALUES (101, 'Asha', 20);

2. DELETE Command The delete statement is used To remove records from a table.

➤ Syntax:
DELETE FROM table_name
WHERE condition;

➤ Example:
-- Delete a specific student by ID
DELETE FROM Student
WHERE StudentID = 101;

-- Delete all records

DELETE FROM Student;
3. UPDATE Command The update statement is used To modify existing data in a table.

➤ Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
➤ Example:
-- Update name of a student
UPDATE Student
SET Name = 'Ashwini'
WHERE StudentID = 102;

----------------------------------------------------------------------------------------------------------------------------------------------

Q5.a Explain the Cursor & its properties in embedded SQL with an example.
(ALSO ASKED IN M4 PYQ DEC)

A cursor is a database object used to retrieve and process rows from a query result set one at a
time.

Properties of Cursors
1. Declare: Define the cursor with a DECLARE statement, specifying the SQL query.
2. Open: Use OPEN to execute the query and create the result set.
3. Fetch: Use FETCH to retrieve individual rows from the result set.
4. Close: Use CLOSE to release resources associated with the cursor.
5. Deallocate: Optionally use DEALLOCATE to remove the cursor definition and free resources.

Example

-- Declare the cursor

DECLARE emp_cursor CURSOR FOR
SELECT emp_id, emp_name, salary FROM Employees;

-- Open the cursor

OPEN emp_cursor;

-- Fetch rows
FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name, @salary;

-- Close the cursor

CLOSE emp_cursor;

-- Deallocate the cursor

DEALLOCATE emp_cursor;
Module 4
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul -- 1
1) Consider the following relations:
Student(Snum, Sname, Branch, level, age)
Class(Cname, meet_at, room, fid)
Enrolled(Snum, Cname)
Faculty(fid, fname, deptid)
Write the following queries in SQL. No duplicates should be printed in any of the answers.
(i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach.
(ii) Find the names of all classes that either meet in room R128 or have five or more students enrolled.
(iii) For all levels except JR, print the level and the average age of students for that level.
(iv) For each faculty member that has taught classes only in room R128, print the faculty member’s name
and the total number of classes she or he has taught.
(v) Find the names of students not enrolled in any class.
2) What do you understand by correlated Nested Queries in SQL? Explain with suitable example.
3) Discuss the ACID properties of a database transaction.
4) What are the views in SQL? Explain with examples.
5) In SQL, write the usage of GROUP BY and HAVING clauses with suitable examples.
6) Discuss the types of problems that may encounter with transactions that run concurrently.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul -- 2
7) How are triggers and assertions defined in SQL? Explain with example.
8) Write the syntax and example of view in SQL. Explain efficient view implementation.
9) List the problems that occur during concurrency control and also explain them with supporting
transaction diagrams.
10) Explain the various DBMS-specific buffer replacement policies.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq dec/jan
11) Define transaction. Discuss ACID properties.
12) With a neat diagram, explain transition diagram of a transaction.
13) Demonstrate working of assertion and triggers in SQL with example.
14) Explain cursor and its properties in embedded SQL with suitable example.
15) Determine if the following schedule is serializable and explain your reasoning:
i) T1: R(X) W(X)
T2: R(X) W(X)
T1: COMMIT
T2: COMMIT

ii) T1: W(X) R(Y)

T2: R(X) W(Y)
T1: COMMIT
T2: COMMIT
----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp -- 1
16) Demonstrate the Database Transaction with transaction diagram.
17) Demonstrate working of Assertion & Triggers in SQL? Explain with an example.
18) Demonstrate the System Log in database transaction.
19) Demonstrate the ACID properties of database transaction.
20) Explain stored procedure language in SQL with an example.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp -- 2
21) Demonstrate transaction states & additional operations.
22) Demonstrate working of Assertion & Triggers in database? Explain with an example.
23) Demonstrate the System Log in database transaction.
24) Discuss the ACID properties of database transaction.
25) Explain stored procedure language in SQL with an example.
----------------------------------------------------------------------------------------------------------------------------------------------
11) Define transaction. Discuss ACID properties. of a database transaction.

* A transaction in a database is a logical unit of work that consists of one or more SQL operations (such as
INSERT, UPDATE, DELETE, or SELECT) executed as a single unit.
* A transaction must either complete entirely or not execute at all, ensuring data integrity in a multi-user
and concurrent environment.

→ Transactions should possess several properties, often called the ACID

----------------------------------------------------------------------------------------------------------------------------------------------
Atomicity
* Ensures that all operations within a transaction are treated as a single unit. Either all succeed, or none do.
If any part of the transaction fails, the entire transaction is rolled back
Example: If a bank transfer transaction debits one account but fails to credit another, the entire transaction
is reversed.
----------------------------------------------------------------------------------------------------------------------------------------------
Consistency preservation
* If transaction is completely executed from beginning to end without interference from other transactions.
It should take the database from one consistent state to another.
Example: If a transaction violates any constraints, such as a foreign key constraint, the database should be
rolled back.
----------------------------------------------------------------------------------------------------------------------------------------------
Isolation:
* Ensures that transactions are executed in isolation from one another. Intermediate results of a
transaction are not visible to other transactions until the transaction is committed.
Example: If two users try to update the same account balance simultaneously, isolation ensures that both
transactions are handled in a serializable manner.
----------------------------------------------------------------------------------------------------------------------------------------------
Durability or permanency
* Ensures that once a transaction is committed, it remains so, even in the event of a system crash. The
results of the transaction are permanently stored in the database.
Example: Once a bank transfer transaction is committed, it is reflected in the database even if the system
crashes immediately afterward..
17) Demonstrate working of Assertion & Triggers in SQL? Explain with an example.

1. ASSERTION in SQL
* An assertion is a condition or rule that must always be true for the database. It is used to enforce
constraints that involve multiple tables or complex conditions which can't be expressed using CHECK
constraints.
* If the condition in the assertion becomes false, the transaction is rolled back.

Syntax:
CREATE ASSERTION assertion_name
CHECK (condition);

Example:
Scenario: Ensure that no student is allowed with age less than 18.
CREATE ASSERTION age_check
CHECK (
NOT EXISTS (
SELECT * FROM Student
WHERE age < 18
)
);

* This assertion will prevent insertion or update of any student record with age < 18.

2. TRIGGERS in SQL
* A trigger is a special kind of stored procedure that automatically executes when a specific event (INSERT,
UPDATE, DELETE) occurs on a table.
* They can be set to execute BEFORE, AFTER, or INSTEAD OF the triggering event

Syntax (MySQL-style):
CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
-- trigger logic
END;

Example:
Scenario: Keep a log of all deleted students in a separate table.

Step 1: Create the log table

CREATE TABLE Deleted_Student_Log (
Snum INT,
Sname VARCHAR(50),
DeletedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create the trigger

CREATE TRIGGER log_student_delete

AFTER DELETE ON Student
FOR EACH ROW
BEGIN
INSERT INTO Deleted_Student_Log(Snum, Sname)
VALUES ([Link], [Link]);
END;

* This trigger will automatically store deleted student records in the log table.
18) Demonstrate the System Log in database transaction.

THE SYSTEM LOG

* The System Log also called the Transaction Log or Write-Ahead Log
* Log keeps track of all transaction operations that affect the values of database items. This information
may be needed to recovery from transaction failures.
* The log is kept on disk, so it is not affected by any type of failure except for disk
* one or more main memory buffers hold the last part of the log file, so that log entries are first added to
the main memory buffer
* When the log buffer is filled, or when certain other conditions occur, the log buffer is appended to the
end of the log file on disk.
* In addition, the log is periodically backed up to archival storage (tape) to guard against such failures
* The following are the types of entries called log records that are written to the log file
* In these entries, T refers to a unique transaction-id that is generated automatically by the system for each
transaction and that is used to identify each transaction:

➤ [Start-transaction, T]: Indicates that transaction T has started execution.

➤ [Write-item, T, X, Old_value, New_value]: Indicates that transaction T has changed the value of
database item X from old_value to new_value.

➤ [Read-item, T, X]: Indicates that transaction T has read the value of database item X.

➤ [Commit, T]: Indicates that transaction T has completed successfully and affirms that its effect
can be committed (recorded permanently) to the database.

➤ [Abort, T]: Indicates that transaction T has been aborted.

Example: System Log for a Transaction

Scenario: Transaction T1 updates the value of variable X from 50 to 70.

BEGIN TRANSACTION T1;

READ X; -- X = 50
X = X + 20; -- X = 70
WRITE X;
COMMIT;
16) Demonstrate the Database Transaction with transaction diagram. & additional operations.

A transaction is an atomic unit of work that should either be completed in its entirety or not done at all.
For recovery purposes, the system needs to keep track of when each transaction starts, terminates, and
commits or aborts. Therefore, the recovery manager of the DBMS needs to keep track of the following
operations:

BEGIN TRANSACTION: This marks the beginning of transaction execution.

READ or WRITE: These specify read or write operations on the database items that are executed as part of
a transaction.

END TRANSACTION: This specifies that READ and WRITE transaction operations have ended and marks the
end of transaction execution.

COMMIT TRANSACTION: This signals a successful end of the transaction so that any changes (updates)
executed by the transaction can be safely committed to the database and will not be undone.

ROLLBACK (or ABORT): This signals that the transaction has ended unsuccessfully, so that any changes or
effects that the transaction may have applied to the database must be undone.

* A transaction goes into an active state immediately after it starts execution, where it can execute its READ
and WRITE operations. When the transaction ends, it moves to the partially committed state.

* At this point, some recovery protocols need to ensure that a system failure will not result in an inability to
record the changes of the transaction permanently (usually by recording changes in the system log). Once
this check is successful, the transaction is said to have reached its commit point and enters the committed
state.

* When a transaction is committed, it has concluded its execution successfully and all its changes must be
recorded permanently in the database, even if a system failure occurs.

* However, a transaction can go to the failed state if one of the checks fails or if the transaction is aborted
during its active state.

* The transaction may then have to be rolled back to undo the effect of its WRITE operations on the
database.

* The terminated state corresponds to the transaction leaving the system. The transaction information that
is maintained in system tables while the transaction has been running is removed when the transaction
terminates. Failed or aborted transactions
4) What are the views in SQL? Explain with examples.
8) Write the syntax and example of view in SQL. Explain efficient view implementation.

views
Views in SQL are virtual tables based on the result of an SQL query. They do not store data themselves but
provide a way to look at and manipulate the result of aquery as if it were a table

* It does not store data physically, but provides a logical representation of one or more tables.

Syntax to Create a View ( EXPLAIN SYNATX IN 2 TO 3 LINE )

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:
Scenario: Consider a table named Student.

CREATE TABLE Student (

Snum INT,
Sname VARCHAR(50),
Branch VARCHAR(30),
Level VARCHAR(5),
Age INT
);

Create a View to Show Only Senior Students (Level = 'SR')

CREATE VIEW Senior_Students AS
SELECT Snum, Sname, Branch
FROM Student
WHERE Level = 'SR';

* Now, you can query the view just like a table:

SELECT * FROM Senior_Students;

* This will return only students who are at senior level.

* if view is not needed it can be removed by DROP view command

DROP VIEW Senior_Students;

Views are efficient for: ( EXPLAIN IN 1 TO 2 LINES )

* Simplifying complex queries
* Enforcing data security
* Hiding sensitive data
* Logical Independence
* Reusability
20) Explain stored procedure language in SQL with an example.

A stored procedure in SQL is a precompiled collection of one or more SQL statements that can be executed
as a unit. Stored procedures are used to encapsulate operations like inserting, updating, or retrieving data,
which can then be reused multiple times and maintained in the database.

Features of Stored Procedures:

* Reusable and modular * Improves performance (precompiled)
* Enhances security (can restrict direct access to data) * Supports parameters (IN, OUT, INOUT)

Syntax of a Stored Procedure

DELIMITER //

CREATE PROCEDURE procedure_name (parameters)

BEGIN
-- SQL statements
END //

DELIMITER ;

Example: A Simple Stored Procedure

* Let's say we have a table called Employees:

CREATE TABLE Employees (
ID INT,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);

* We can create a stored procedure to insert a new employee into the table.
DELIMITER //

CREATE PROCEDURE AddEmployee (

IN emp_id INT,
IN emp_name VARCHAR(50),
IN emp_salary DECIMAL(10,2)
)
BEGIN
INSERT INTO Employees (ID, Name, Salary)
VALUES (emp_id, emp_name, emp_salary);
END //

DELIMITER ;

Calling the Stored Procedure:

CALL AddEmployee(101, 'Alice', 55000.00);

* This will insert a new employee with ID 101, name 'Alice', and salary 55000 into the Employees table.
2) What do you understand by correlated Nested Queries in SQL? Explain with suitable example

Correlated Nested Query:

A correlated nested query is a subquery that uses values from the outer query. It is executed once for each
row of the outer query.

Example:
Problem: Find employees who earn more than the average salary of their own department. Employees

Table:
ID Name Department Salary
1 Alice HR 40000
2 Bob HR 50000
3 Carol IT 60000
4 David IT 55000

SELECT Name, Department, Salary

FROM Employees E1
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees E2
WHERE [Link] = [Link]
);

Explanation:
• The subquery uses [Link] from the outer query.
• So it runs once for each row, computing the average salary of that employee’s department.
5) In SQL, write the usage of GROUP BY and HAVING clauses with suitable examples.

1. GROUP BY Clause
* The GROUP BY clause is used to group rows that have the same values in specified columns.
* It is typically used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), or MIN().

Example:
Problem: Find the total salary paid in each department.

Employees Table:
ID Name Department Salary
1 Alice HR 40000
2 Bob HR 50000
3 Carol IT 60000
4 David IT 55000
5 Eve Finance 45000

SELECT Department, SUM(Salary) AS TotalSalary

FROM Employees
GROUP BY Department;

Output:
Department TotalSalary
HR 90000
IT 115000
Finance 45000

2. HAVING Clause
The HAVING clause is used to filter groups after they have been formed by the GROUP BY clause. Unlike
WHERE (which filters rows), HAVING filters aggregated group results.

Example:
Problem: Find departments where the total salary is more than 90000.

SELECT Department, SUM(Salary) AS TotalSalary

FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 90000;

Output:
Department TotalSalary
IT 115000
6) Discuss the types of problems that may encounter with transactions that run concurrently.

1. Lost Update Problem

Occurs when two transactions read the same data and update it, but one update overwrites the other, so
the first update is lost.

Initial X = 80
T1: READ(X) → 80
T2: READ(X) → 80
T1: X = X - 5 → 75
T1: WRITE(X)
T2: X = X + 4 → 84
T2: WRITE(X) ← overwrites T1’s update

Final X = 84 (incorrect)
Expected X = 79 (T1 subtracted 5, T2 added 4)

Problem: T1's update is lost because T2 overwrote the value

2. Temporary Update / Dirty Read Problem

Happens when one transaction updates a value but fails or rolls back, and another transaction reads this
temporary (incorrect) value.

Initial Balance = 1000

T1: UPDATE Balance = 5000
T2: READ(Balance) → 5000
T1: ROLLBACK → Balance = 1000

Problem: T2 read a dirty value (5000) that was never committed — leads to incorrect processing.

3. Incorrect Summary Problem

Occurs when one transaction calculates a total or average, while other transactions are modifying the data
being summarized.

Table: Orders
Rows: [100, 200, 300]

T1: SELECT SUM(amount)

T2: UPDATE Orders SET amount = amount + 100 WHERE amount = 200
T2: COMMIT
T1: Continues SUM...

Problem: T1 includes some updated and some original values, leading to an incorrect total.
4. Unrepeatable Read Problem
Happens when a transaction reads the same item twice and gets different values because another
transaction updated it in between.

Initial Stock = 50
T1: READ(Stock) → 50
T2: UPDATE Stock = 45
T2: COMMIT
T1: READ(Stock) → 45

* Transaction T1 expected that the value of stock stays the same while it is running.
* But it got two different values (50 and 45) for the same data.
1) Consider the following relations:
Student(Snum, Sname, Branch, level, age)
Class(Cname, meet_at, room, fid)
Enrolled(Snum, Cname)
Faculty(fid, fname, deptid)
Write the following queries in SQL. No duplicates should be printed in any of the answers.
(i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach.
(ii) Find the names of all classes that either meet in room R128 or have five or more students enrolled.
(iii) For all levels except JR, print the level and the average age of students for that level.
(iv) For each faculty member that has taught classes only in room R128, print the faculty member’s name
and the total number of classes she or he has taught.
(v) Find the names of students not enrolled in any class.

(i) Juniors enrolled in a class taught by I. Teach

SELECT DISTINCT [Link]
FROM Student S
JOIN Enrolled E ON [Link] = [Link]
JOIN Class C ON [Link] = [Link]
JOIN Faculty F ON [Link] = [Link]
WHERE [Link] = 'JR'
AND [Link] = 'I. Teach';

(ii) Classes that meet in room R128 or have ≥ 5 students enrolled

/* Classes meeting in R128 */
SELECT DISTINCT Cname
FROM Class
WHERE room = 'R128'

UNION -- removes duplicates automatically

/* Classes with five-or-more students */

SELECT Cname
FROM Enrolled
GROUP BY Cname
HAVING COUNT(DISTINCT Snum) >= 5;

(iii) Average age for every level except JR

SELECT [Link], AVG([Link]) AS Avg_Age
FROM Student S
WHERE [Link] <> 'JR'
GROUP BY [Link];
(iv) Faculty who have taught only in room R128 and how many classes they have taught
SELECT [Link], COUNT([Link]) AS Total_Classes
FROM Faculty F, Class C
WHERE [Link] = [Link]
AND [Link] = 'R128'
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = 1;

(v) Students not enrolled in any class

SELECT [Link]
FROM Student S
WHERE [Link] NOT IN (SELECT [Link] FROM Enrolled E);

15) Determine if the following schedule is serializable and explain your reasoning:
i) T1: R(X) W(X)
T2: R(X) W(X)
T1: COMMIT
T2: COMMIT

ii) T1: W(X) R(Y)

T2: R(X) W(Y)
T1: COMMIT
T2: COMMIT
 (i) Schedule:
T1: R(X) → W(X)
T2: R(X) → W(X)
T1: COMMIT
T2: COMMIT

Conflicting Operations:
• T1: W(X) and T2: R(X) → Write-Read conflict → T1 → T2
• T1: W(X) and T2: W(X) → Write-Write conflict → T1 → T2

Precedence Graph:
• Edge: T1 → T2
No cycle

Result: Conflict-Serializable
• Equivalent to serial schedule: T1 → T2

(ii) Schedule:
T1: W(X) → R(Y)
T2: R(X) → W(Y)
T1: COMMIT
T2: COMMIT

Conflicting Operations:
• T1: W(X) and T2: R(X) → Write-Read conflict → T1 → T2
• T1: R(Y) and T2: W(Y) → Read-Write conflict → T2 → T1

Precedence Graph:
• Edges:
o T1 → T2
o T2 → T1

Cycle Detected: T1 ↔ T2
Not serializable

Final Answers:
Schedule Serializable? Reason
No cycles in conflict graph (T1
(i) Yes
→ T2)
Cycle exists in conflict graph
(ii) No
(T1 → T2 → T1)
Module 5
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul -- 1
1) What is the two-phase locking protocol? How does it guarantee serializability?
2) Describe the wait-die and wound-wait protocols for deadlock prevention.
3) List and explain the four major categories of NoSQL system.
4) What is Multiple Granularity locking? How is it implemented using intention locks? Explain.
5) Discuss the following MongoDB CRUD operations with their formats: (i) Insert (ii) Delete (iii) Read
6) Briefly discuss about Neo4j data model.
----------------------------------------------------------------------------------------------------------------------------------------------
Pyq jun/jul -- 2
7) Demonstrate with example deadlock in transaction. Discuss deadlock prevention algorithm.
8) What are Binary locks? Explain with Lock and unlock operations with algorithm.
9 ) Write a short note on:
i) Properties of NoSQL system
ii) The CAP theorem
iii) Document based NoSQL system
iv) NoSQL Graph database
----------------------------------------------------------------------------------------------------------------------------------------------
pyq dec/jan
10) Explain the CAP theorem.
11) What is NoSQL graph database? Explain Neo4j.
12) Why concurrency control and recovery are needed in DBMS? Demonstrate with suitable examples
types of problems that may occur when two simple transactions run concurrently.
13) Explain basic operations CRUD in MongoDB.
14) Explain deadlock prevention protocols.
15) Briefly discuss the two-phase locking techniques for concurrency control.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp – 1
16) Demonstrate the Two phase locking protocol used for concurrency control.
17) Demonstrate the Concurrency control based on Timestamp ordering.
18) Why Concurrency control is needed? Demonstrate with an example.
19) What is NOSQL? Explain the CAP theorem.
20) What are document based NOSQL systems? Explain basic operations CRUD in MongoDB.
21) What is NOSQL Graph database? Explain Neo4j.
----------------------------------------------------------------------------------------------------------------------------------------------
Mqp – 2
22) Explain the Two phase locking protocol used for concurrency control.
23) Define Schedule? Illustrate with an example.
24) Why Concurrency control is needed? Demonstrate with an example.
25) What is NOSQL? Explain the CAP theorem.
26) What are document based NOSQL systems? basic operations CRUD in MongoDB.
27) What is NOSQL Graph database? Explain Neo4j.
----------------------------------------------------------------------------------------------------------------------------------------------
1) What is the two-phase locking protocol? How does it guarantee serializability?

Definition of Two-Phase Locking Protocol (2PL):

The Two-Phase Locking (2PL) protocol is a concurrency control method used in Database Management
Systems (DBMS) to ensure serializability of transactions. It divides the execution of a transaction into two
distinct phases:

Phases of 2PL
* Growing Phase: The transaction acquires all the locks it needs (e.g., shared or exclusive locks) without
releasing any locks.
* Shrinking Phase: The transaction releases all its locks without acquiring any new ones.

Types of Locks Used:

• Shared Lock (S-lock): for reading data.
• Exclusive Lock (X-lock): for writing/updating data.

Example
* Consider two transactions, T1 and T2, accessing data items A and B:
• T1: Read(A), Write(B)
• T2: Write(A), Read(B)

Without 2PL, their operations could interleave, causing conflicts (e.g., T2 writes A before T1 reads A,
leading to inconsistent results). With 2PL:

* T1 acquires a shared lock on A and an exclusive lock on B in its growing phase.

* T2 cannot access A (because T1 holds a lock) until T1 completes and releases its locks in the shrinking
phase.
* This ensures that either T1 executes completely before T2 or vice versa, guaranteeing a conflict-
serializable schedule.

How 2PL Guarantees Serializability (Expanded Short Answer)

Conflict Avoidance:
2PL ensures that a transaction acquires the required locks before accessing data.
This prevents other transactions from accessing the same data simultaneously, avoiding conflicts like dirty
reads or lost updates.

Locking Discipline:
Transactions follow a strict rule: acquire all locks before releasing any.
This creates a predictable access pattern, ensuring that transactions do not interfere with each other’s
critical sections.

Equivalence to Serial Schedule:

Because conflicting operations are separated by locks, their execution order mimics a serial transaction
order..

Conflict Serializability:
The locking mechanism of 2PL avoids cycles in the precedence graph of transactions.
15) Briefly discuss the two-phase locking techniques for concurrency control.

Definition of Two-Phase Locking Protocol (2PL):

The Two-Phase Locking (2PL) protocol is a concurrency control method used in Database Management
Systems (DBMS) to ensure serializability of transactions. It divides the execution of a transaction into two
distinct phases:

Phases of 2PL
* Growing Phase: The transaction acquires all the locks it needs (e.g., shared or exclusive locks) without
releasing any locks.
* Shrinking Phase: The transaction releases all its locks without acquiring any new ones.

Key Features
• Conflict Prevention: Locks prevent conflicting operations (e.g., simultaneous writes or read-write
conflicts) by ensuring exclusive access to data items.
• Serializability: 2PL guarantees conflict-serializable schedules, producing results equivalent to a serial
execution of transactions by enforcing a strict access order.
• Lock Types: Shared locks allow multiple transactions to read a data item, while exclusive locks
ensure sole access for writing.

Variants of 2PL

1. Basic 2PL: Follows the two-phase rule but may release locks before commit, risking cascading
rollbacks.

2. Strict 2PL: Locks are held until the transaction commits or aborts, ensuring recoverability and
preventing cascading rollbacks.

3. Rigorous 2PL:All locks (shared and exclusive) are held until the transaction ends. Ensures
serializability and recoverability.

4. Conservative C2PL: In Conservative 2PL, a transaction pre-declares all the locks it needs before it
starts. If all required locks are available, it proceeds; otherwise, it waits without locking anything,
avoiding deadlocks completely.
19) What is NOSQL? Explain the CAP theorem.

NOSQL
* NoSQL is a type of database management system (DBMS) that is designed to handle and store large
volumes of unstructured and semi-structured data.
* Unlike traditional relational databases that use tables with predefined schemas to store data, NoSQL
databases use flexible data models that can adapt to changes in data structures and are capable of scaling
horizontally to handle growing amounts of data.

CAP theorem
It is a fundamental principle in distributed systems that describes the trade-offs between three properties:
Consistency, Availability, and Partition Tolerance. It states that a distributed system can only guarantee
two of these three properties simultaneously under network partitions.

* Consistency ensuring all nodes in the system have the same view of the data at any given time
* Availability ensures every request receives a response, even if some nodes fail.
* Partition Tolerance means the system continues to operate despite network failures that partition the
system

This leads to three possible system designs:

* CA (Consistency + Availability):
Assumes no network partition. Prioritizes consistent and available responses. Fails during partition.
Example: MySQL (single-node).

* CP (Consistency + Partition Tolerance):

Maintains data accuracy even during partitions, but may reject requests (downtime).
Example: MongoDB (some configs), HBase.

* AP (Availability + Partition Tolerance):

Always responsive, even during partitions, but may return outdated data.
Example: Cassandra, DynamoDB.
11) What is NoSQL graph database? Explain Neo4j.

NoSQL graph database

* A NoSQL graph database is a type of non-relational (NoSQL) database designed to store, manage, and
query data as a graph, focusing on relationships between entities.
* graph databases use nodes, edges, and properties to represent and traverse complex relationships
efficiently.

Key Features
• Nodes: Represent entities (e.g., people, products, or places).
• Edges: Represent relationships between nodes (e.g., "friend of," "purchased," or "located in").
• Properties: Attributes of nodes or edges (e.g., a person's name or a relationship's timestamp).
• Flexible Schema: No rigid schema; nodes and edges can have varying properties.
• Real-time traversal (e.g., social networks, fraud detection, recommendation engines)

Neo4j
Neo4j is the most widely used NoSQL graph database. It uses the property graph model and supports the
Cypher query language for data operations.

Features of Neo4j:
• Stores data as nodes, relationships, and properties
• Highly optimized for graph traversa
• Scalability and Performancel
• ACID-compliant transactions
• Easy to query with Cypher (SQL-like syntax for graphs)
• Neo4j typically operates as a CP system (Consistency + Partition Tolerance)

Example: Social Network

If we have two users:
UserA -[:FRIENDS_WITH]-> UserB

Cypher Query Example:

MATCH (a:Person)-[:FRIENDS_WITH]->(b:Person)
RETURN [Link], [Link];

* It is widely used in applications requiring Social Networks: ecommendation Systems: Fraud Detection
Knowledge Graphs: Network Analysis:
20) What are document based NOSQL systems? Explain basic operations CRUD in MongoDB. ?

* Document-based NoSQL systems, also known as document stores, are a type of non-relational database
that store and manage data as semi-structured documents, typically in formats like JSON, BSON, or XML.

* Each document represents a record, and can contain nested structures like arrays and key-value pairs
(e.g., JSON-like { "name": "Alice", "age": 30 })

Key Features:
• Schema-less: Documents can have different fields and structures.
• Flexible: Easy to modify or update documents without altering schema.
• Indexable: Fields inside documents can be indexed for fast queries.
• Efficient for hierarchical or semi-structured data
• Most document-based NoSQL systems prioritize Availability and Partition Tolerance (AP)

Examples of Document-Based NoSQL Databases

MongoDB: CouchDB: Couchbase:

Use Cases
Content Management: E-commerce: Real-Time Analytics: IOT

Example of a Document:
{
"name": "Alice",
"age": 25,
"email": "alice@[Link]",
"skills": ["Java", "MongoDB"]
}
----------------------------------------------------------------------------------------------------------------------------------------------
CRUD stands for:
Create Read Update Delete
* These are the four basic operations used to manipulate data in a MongoDB database.

1. Create
Used to insert a new document into a collection.

Syntax:
[Link]({ key: "value", ... })

Example:
[Link]({
name: "Alice",
age: 21,
course: "Computer Science"
})
2. Read (Find)
Used to retrieve documents from a collection.

Syntax:
[Link]({ query })

Example:
[Link]({ name: "Alice" })

To get all documents:

[Link]({})

3. Update
Used to modify existing documents.

Syntax:
[Link]({ filter }, { $set: { updatedFields } })

Example:
[Link](
{ name: "Alice" },
{ $set: { age: 22 } }
)

4. Delete
Used to remove documents from a collection.

Syntax:
[Link]({ filter })

Example:
[Link]({ name: "Alice" })
3) List and explain the four major categories of NoSQL system.

1. Document-Based Databases
Document stores manage data as semi-structured documents, typically in formats like JSON, BSON, or XML.

• Structure: Key-value pairs with nested data

• Best For: Content management, Real-time analytics, E-commerce product
• Schema: Flexible (schema-less)
• Scalability: Easily scales horizontally across distributed systems
• CAP Theorem: often AP (Availability + Partition Tolerance)

Example: MongoDB, CouchDB, Firebase

Sample Document:
{
"name": "Alice",
"email": "alice@[Link]",
"skills": ["Java", "Python"]
}

2. Key-Value Stores
storing data as a collection of key-value pairs, where each key is unique and maps to a value. The value can
be a string, number, or complex object

• Structure: Simple dictionary/hashmap style

• Best For: Caching, session storage, real-time data
• Schema-less: No fixed structure; values can vary.
• Scalability: Easily scales horizontally across distributed systems
• CAP Theorem: Typically AP (Availability + Partition Tolerance)

Example: Redis, DynamoDB, Riak

Sample Entry:
Key: "user123"
Value: { "name": "Alice", "age": 25 }

3. Column-Based Databases
Column-Based Databases stores organize data into columns rather than rows, grouping related columns
into families. Each row can have different columns, optimized for analytical queries.

• Structure: Tables with column families

• Best For: Big data analytics, Time-series data, Event logging and fraud detection.
• Scalability: Easily scales horizontally across distributed systems
• CAP Theorem: Typically AP (Availability + Partition Tolerance)

Example: Apache Cassandra, HBase

Key Feature: Good for write-heavy operations and sparse data

 4. Graph-Based Databases
Graph databases store data as nodes (entities) and edges (relationships), optimized for querying complex
relationships and networks

• Structure: Graph structure

• Best For: Social networks, fraud detection, recommendation engines
• Flexible Schema: Nodes and edges can have varying properties

Example: Neo4j, ArangoDB, Amazon Neptune

Sample Relationship:
(Alice) -[:FRIENDS_WITH]-> (Bob)

----------------------------------------------------------------------------------------------------------------------------------------------

9 ) Write a short note on:

i) Properties of NoSQL system

Key Properties of NoSQL Systems:

1. Schema-less Structure
o No fixed table schema; each document/record can have a different structure.
o Makes it easy to evolve the data model over time.

2. Horizontal Scalability
o Supports distribution of data across multiple servers (sharding).
o Enables easy handling of big data and high-traffic applications.

3. High Performance
o Optimized for fast read/write operations.
o Efficient for large-scale, real-time processing.

4. Flexible Data Model

o Supports different data types like key-value, document, column, and graph.
o Suitable for diverse application needs.

5. Eventual Consistency (in many systems)

o Prioritizes availability and partition tolerance (as per CAP theorem).
o Some systems may not guarantee immediate consistency.

6. Built-in Caching
o Some NoSQL databases like Redis have in-memory caching features for speed.

7. Replication and Fault Tolerance

o Automatically replicates data for reliability and high availability.
24) Why Concurrency control is needed? Demonstrate with an example.

Concurrency control in DBMS ensures that multiple transactions can execute simultaneously without
interfering with each other, and without violating data consistency and integrity.

Key Reasons for Concurrency Control

1. Prevent Data Inconsistencies: Ensures that transactions do not interfere with each other, avoiding
problems like lost updates or reading uncommitted data.
2. Maintain Isolation: Ensures each transaction appears to execute in isolation, even when running
concurrently.
3. Ensure Serializability: Guarantees that the concurrent execution of transactions produces results
equivalent to a serial (one-at-a-time) execution.
4. Support Multi-User Access: Allows multiple users to access and modify the database
simultaneously without compromising data integrity.
5. Handle Conflicts: Manages conflicts when transactions access the same data (e.g., read-write or
write-write conflicts).
6. Maintain ACID properties

Problems Without Concurrency Control:

Example – Lost Update Problem

Initial Balance in Account A: ₹1000
Two transactions execute simultaneously:
• T1 wants to deposit ₹500
• T2 wants to withdraw ₹200

Without Concurrency Control:

T1: READ(A) → ₹1000
T2: READ(A) → ₹1000

T1: A = A + 500 → ₹1500

T2: A = A - 200 → ₹800

T1: WRITE(A) → ₹1500

T2: WRITE(A) → ₹800 (T1's update is lost)
Final Balance: ₹800 instead of ₹1300 → T1’s update is lost!

With Concurrency Control:

The DBMS ensures that one transaction waits or executes after the other, preserving correct results:
T1: READ(A) → ₹1000
T1: A = A + 500 → ₹1500
T1: WRITE(A)

T2: READ(A) → ₹1500

T2: A = A - 200 → ₹1300
T2: WRITE(A)
Final Balance: ₹1300 (Correct)
17) Demonstrate the Concurrency control based on Timestamp ordering.

Timestamp Ordering (TO) is a concurrency control protocol that uses timestamps to ensure serializability
(i.e., the result of concurrent transactions is the same as some serial execution order).
Each transaction is assigned a unique timestamp (TS) when it starts:
• Older transactions get smaller timestamps.
• Younger transactions get larger timestamps.

Timestamps Used in TO Protocol:

For each data item X, the system keeps:
1. read_TS(X) – Largest timestamp of any transaction that read X
2. write_TS(X) – Largest timestamp of any transaction that wrote X

Rules for Operations:

Let TS(Ti) be the timestamp of transaction Ti
Read(X) by Ti
• If TS(Ti) < write_TS(X) → Reject (Ti is too old, reading outdated value) → Ti is aborted
• Else → Allow, and set read_TS(X) = max(read_TS(X), TS(Ti))
Write(X) by Ti
• If TS(Ti) < read_TS(X) → Reject (Ti is overwriting a value read by a newer transaction) → Ti is
aborted
• If TS(Ti) < write_TS(X) → Reject (Ti is trying to write an outdated value) → Ti is aborted
• Else → Allow, and set write_TS(X) = TS(Ti)

Example:
Let’s say we have:
• TS(T1) = 5
• TS(T2) = 10
• Initially: read_TS(X) = 0, write_TS(X) = 0

Step-by-Step:
1. T1: Read(X)
o TS(T1) = 5 > write_TS(X) = 0 → Allowed
o Update: read_TS(X) = 5
2. T2: Write(X)
o TS(T2) = 10 > read_TS(X) = 5
o TS(T2) = 10 > write_TS(X) = 0 → Allowed
o Update: write_TS(X) = 10
3. T1: Write(X)
o TS(T1) = 5 < write_TS(X) = 10 → Reject
o T1 is aborted to maintain consistency

Advantages:
• Ensures serializability
• No need for locks (non-blocking)
• Prevents deadlocks
12) Why concurrency control and recovery are needed in DBMS? Demonstrate with suitable examples
types of problems that may occur when two simple transactions run concurrently.

Concurrency control in DBMS ensures that multiple transactions can execute simultaneously without
interfering with each other, and without violating data consistency and integrity.

Key Reasons for Concurrency Control

1. Prevent Data Inconsistencies: Ensures that transactions do not interfere with each other, avoiding
problems like lost updates or reading uncommitted data.
2. Maintain Isolation: Ensures each transaction appears to execute in isolation, even when running
concurrently.
3. Ensure Serializability: Guarantees that the concurrent execution of transactions produces results
equivalent to a serial (one-at-a-time) execution.
4. Support Multi-User Access: Allows multiple users to access and modify the database
simultaneously without compromising data integrity.
5. Handle Conflicts: Manages conflicts when transactions access the same data (e.g., read-write or
write-write conflicts).
6. Maintain ACID properties
7. Prevention of deadlock

2. Why is Recovery Needed?

* Recovery is needed to restore the database to a consistent state after: System crashes, Power failures,
Transaction failures
* Recovery ensures the Atomicity and Durability (from ACID properties) of transactions using techniques
like: Write-Ahead Logging (WAL), Checkpoints, Rollback and Rollforward

3. Problems from Concurrent Execution (with Examples)

Lost Update Problem

Initial Balance in A = ₹1000
• T1: Adds ₹500
• T2: Withdraws ₹200
Without concurrency control:
T1: READ(A) → 1000
T2: READ(A) → 1000

T1: A = A + 500 → 1500

T2: A = A - 200 → 800

T1: WRITE(A) → 1500

T2: WRITE(A) → 800 ← T1’s update is lost
Dirty Read
• T1: Updates salary to ₹60,000 and then crashes (rollback)
• T2: Reads the uncommitted salary
T1: WRITE(Salary = 60000)
T2: READ(Salary) → 60000 (Dirty read)
T1: ABORT (rollback Salary)
T2 reads invalid data.

C. Unrepeatable Read
• T1: Reads a record twice
• T2: Modifies the same record between T1’s reads
T1: READ(Product Price) → ₹500
T2: UPDATE(Product Price = ₹600)
T1: READ(Product Price again) → ₹600 (value changed)
7) Demonstrate with example deadlock in transaction. Discuss deadlock prevention algorithm.

deadlock
A deadlock in a database system occurs when two or more transactions are unable to proceed because
each is waiting for the other to release a lock on a resource, creating a cycle of dependencies

Deadlock Example (Demonstration)

Let’s consider two transactions T1 and T2 accessing two data items A and B:
Transaction T1:
LOCK(A)
LOCK(B)
UNLOCK(A)
UNLOCK(B)

Transaction T2:
LOCK(B)
LOCK(A)
UNLOCK(B)
UNLOCK(A)

Scenario (Deadlock Occurs):

1. T1 locks A
2. T2 locks B
3. T1 requests B → Blocked (T2 holds it)
4. T2 requests A → Blocked (T1 holds it)
Both are waiting on each other — this is a deadlock

1. Wait-Die Protocol
• Older transaction is allowed to wait
• Younger transaction is aborted (dies)
Rules:
• If T₁ (older) requests a resource held by T₂ (younger) → T₁ waits
• If T₂ (younger) requests a resource held by T₁ (older) → T₂ dies
Example:
• T1 (timestamp = 5), T2 (timestamp = 10)
• T1 requests a lock held by T2 → T1 waits
• T2 requests a lock held by T1 → T2 dies

2. Wound-Wait Protocol
• Older transaction wounds (forces abort) the younger one
• Younger transaction waits
Rules:
• If T₁ (older) requests a resource held by T₂ (younger) → T₂ is wounded (aborted)
• If T₂ (younger) requests a resource held by T₁ (older) → T₂ waits
Example:
• T1 (timestamp = 5), T2 (timestamp = 10)
• T1 requests a lock held by T2 → T2 is aborted (wounded)
• T2 requests a lock held by T1 → T2 waits
8) What are Binary Locks? Explain with Lock and Unlock Operations with Algorithm

Binary Lock?
A Binary Lock is the simplest locking mechanism used in concurrency control in DBMS.
Each data item can be in only one of two states:
• Locked (1) — The data item is currently in use and cannot be accessed by other transactions.
• Unlocked (0) — The data item is available for access.

Key Characteristics:
• Only one type of lock (no shared or exclusive modes).
• At most one transaction can hold the lock on a data item at a time.
• Used mainly to prevent conflicting operations.

Binary Locking Operations:

Lock(X):
• If X is already locked, the transaction waits.
• If X is unlocked, it is locked and the transaction continues.
Unlock(X):
• The lock on X is released, making it available to other transactions.

Binary Locking Algorithm:

LOCK(X)
{
if (LOCK_STATUS(X) == 1)
wait until X becomes unlocked;
else
LOCK_STATUS(X) = 1; // Lock X
}

UNLOCK(X)
{
LOCK_STATUS(X) = 0; // Unlock X
}

Example:
Assume Transaction T1 and T2 both want to access data item A.
1. T1: LOCK(A) → Success (A becomes locked)
2. T2: LOCK(A) → Waits (A already locked by T1)
3. T1: UNLOCK(A) → A becomes available
4. T2: Now LOCK(A) can proceed
23) Define Schedule? Illustrate with an example

* A Schedule in DBMS is the sequence of operations (read, write, commit, etc.) from one or more
transactions, executed in a specific order, maintaining the order of operations within each transaction.
* Schedules are used to determine how concurrent transactions interact with each other and whether the
execution maintains data consistency.

Types of Schedules:
• Serial Schedule – Transactions execute one after another without overlapping.
• Concurrent (Interleaved) Schedule – Operations of multiple transactions are mixed

Example: Consider two transactions on a Balance = $1000:

• T1: Transfer $200 (Read(Balance), Write(Balance - 200)).
• T2: Transfer $300 (Read(Balance), Write(Balance - 300)).
Concurrent Schedule:
1. T1: Read(Balance = $1000)
2. T2: Read(Balance = $1000)
3. T1: Write(Balance = $800)
4. T2: Write(Balance = $700)