DBMS Module-1

1.What is a database? Give an example.

• A database is an organized collection of data for easy access, management, and retrieval.
• It stores information in tables, making data management efficient.
• Examples include MySQL, Oracle, and MongoDB.
• For instance, a library database maintains records of books, authors, and borrowers.
2. Discuss how DBMS characteristics enhance system efficiency.
• Data consistency is ensured by enforcing constraints.
• Redundant data is minimized through normalization.
• Concurrency control allows multiple users to access data simultaneously.
• Indexing and query optimization improve data retrieval speed.

3. Explain how DBMS minimizes redundancy compared to file systems.

• DBMS uses normalization to eliminate duplicate data.
• Centralized control ensures consistent data storage.
• Shared data is stored once and accessed by multiple applications.
• Referential integrity constraints prevent inconsistent data duplication.

4. What is Data Definition Language (DDL)?

• DDL is used to define database structures.
• It includes commands like CREATE, ALTER, DROP, and TRUNCATE.
• DDL statements are auto-committed, making changes permanent.
• For example, CREATE TABLE Student (ID INT, Name VARCHAR(50));

5. Define Data Manipulation Language (DML) and give an example.

• DML is used to manipulate data within tables.
• It includes commands like INSERT, UPDATE, DELETE, and SELECT.
• DML changes are not auto-committed; transactions can be rolled back.
• Example: INSERT INTO Student VALUES (1, 'John');

6. What is the purpose of Data Control Language (DCL)?

• DCL controls user access and permissions in databases.
• It includes commands like GRANT and REVOKE.
• GRANT assigns privileges, while REVOKE removes them.
• For example, GRANT SELECT ON Student TO user1;

7. Explain the use of Transaction Control Language (TCL) in databases.

• TCL manages database transactions to ensure consistency.
• Commands include COMMIT, ROLLBACK, SAVEPOINT, and SET
TRANSACTION.
• COMMIT saves changes permanently, while ROLLBACK undoes changes.
• For example, ROLLBACK TO SAVEPOINT point1;
 8. Define physical data independence.
• Physical data independence separates data storage from logical design.
• Changes in physical storage do not affect application programs.
• It allows data restructuring without modifying database schemas.
• This improves flexibility and scalability.

9. Define logical data independence.

• Logical data independence separates data structure from user views.
• Changes in the logical schema don't affect external applications.
• This ensures flexible database modifications.
• For example, adding a new column won't disrupt existing queries.

10. List two components of the overall system structure in databases.

• Storage Manager: Manages data storage, retrieval, and backup.
• Query Processor: Analyzes and optimizes user queries for efficient execution.
• Other components include transaction management and buffer management.
• Each ensures data integrity, security, and performance.

11. What is the purpose of a query processor in a database system?

• It translates high-level queries into low-level instructions.
• The query optimizer ensures efficient query execution.
• The query parser checks syntax and semantics.
• It improves data retrieval speed and accuracy.

12. What is a relationship in an ER model?

• A relationship defines associations between entities.
• Represented using diamonds in ER diagrams.
• Types include one-to-one, one-to-many, and many-to-many.
• Example: A 'Works_For' relationship links Employees and Departments.

5 marks

Q13: Enumerate five key advantages of using a DBMS over a file processing system.

1. Data Redundancy Control – DBMS minimizes duplication by storing data in a

centralized database.
2. Data Consistency – Changes made in one place are reflected everywhere due to
integration.
3. Improved Data Sharing – Multiple users can access and update data concurrently.
4. Data Security – Access control and authorization mechanisms restrict unauthorized
usage.
 5. Data Integrity – Constraints such as primary keys and foreign keys ensure valid data
entry.
6. Backup and Recovery – DBMS provides automatic backup and restore capabilities.
7. Concurrency Control – Ensures accurate results during simultaneous data access.
8. Efficient Query Processing – SQL enables complex queries for fast data retrieval.
9. Data Independence – Logical and physical data independence simplifies
modifications.
10. Centralized Management – The DBA manages the entire database from one point.

Q14: Define the three levels of database architecture. Describe each level briefly.

1. Internal Level (Physical Level):

a. Describes how data is stored physically (e.g., indexes, file structures).
b. Managed by DBMS for optimization and storage efficiency.
2. Conceptual Level:
a. Describes what data is stored and the relationships among data.
b. Hides physical details; defines entities, attributes, constraints.
3. External Level (View Level):
a. Represents different user views of the database.
b. Provides customized access without affecting the conceptual schema.

Diagram:

pgsql

External View 1 External View 2

\ /
Conceptual Schema
|
Internal Schema

Q15: Define database schema and instance. Explain their differences with an example.

Aspect Schema Instance

Structure of the database Actual data in the database at a given
Definition
(design) time
Change Frequency Rarely changes Changes frequently
Type Intension Extension
Table definition: Student(ID, Student(1, "Ravi"), Student(2,
Example
Name) "Asha")

• Schema Example: CREATE TABLE Student (ID INT, Name VARCHAR(30));

• Instance Example:

+----+------+
| ID | Name |
+----+------+
| 1 | Ravi |
| 2 | Asha |
+----+------+

Q16: Define data abstraction and its levels in a database system.

• Data Abstraction is the process of hiding the details of data storage and focusing on
the essential aspects from the user’s perspective.

Levels of Abstraction:

1. Physical Level – Details of how data is stored (files, blocks, etc.).

2. Logical Level – Describes data types, relationships, constraints.
3. View Level – User-specific representation of the data.

Diagram:

View Level (User Views)

|
Logical Level (Entities, Relationships)
|
Physical Level (Storage in Files, Blocks)
Benefits:

• Simplifies database design.

• Provides data independence.
• Enhances system usability and security.

Q17: Explain how application programmers interact with a database system.

1. Application programmers use database APIs like JDBC/ODBC to connect to DBMS.

2. They write SQL queries embedded in programs (e.g., Java, Python).
3. Use host languages to manipulate and process retrieved data.
4. Application code handles user inputs and communicates with the database.
5. They use Stored Procedures or functions for better modularity and performance.
6. Employ error handling for transactions and rollbacks.
7. Ensure data security via login/authentication mechanisms.
8. Interface through forms, GUIs, or web applications.
9. Focus on data manipulation, not storage implementation.
10. Debug and test using test datasets and schema simulators.

Q18: What tasks are performed by database administrators for managing users?

1. User Account Creation – Add new users with login credentials.

 2. Privilege Management – Assign specific access using GRANT and remove with
REVOKE.
3. Role Definition – Group users based on access levels (e.g., admin, analyst).
4. Password Policies – Enforce secure password practices.
5. Monitoring Usage – Track login activity and access patterns.
6. Backup & Recovery Access – Ensure users can recover their data if needed.
7. Revoking Access – Remove access of ex-employees or inactive users.
8. Auditing – Track unauthorized or suspicious actions.
9. Enforcing Constraints – Ensure users follow DB rules (e.g., no NULLs in critical
fields).
10. Training & Documentation – Guide users on how to interact with the DBMS securely.

Q19: List and explain five key responsibilities of a DBA.

1. Schema Definition – Create tables, constraints, relationships.

2. Storage Management – Allocate space efficiently using indexes and file organization.
3. Security & Authorization – Implement access control mechanisms.
4. Backup and Recovery – Set up regular backups and recovery strategies.
5. Performance Monitoring – Tune queries and optimize performance.
6. User Management – Add/remove users and manage roles.
7. Data Integrity – Enforce rules to maintain valid data.
8. Transaction Management – Ensure ACID compliance.
9. Software Updates – Patch and upgrade the DBMS regularly.
10. Documentation & Compliance – Maintain operational records for audit purposes.

Q20: Describe how the internal level handles the physical storage of data.

1. File Organization – Data is stored in structured formats (heap, clustered, hashed).

2. Record Formats – Defines how each tuple is laid out in memory.
3. Indexing – Index structures (e.g., B-trees) allow fast access.
4. Compression – Reduces storage footprint using encoding techniques.
5. Paging & Buffering – Data is loaded in memory pages to reduce disk I/O.
6. Data Blocks – Actual physical units on disk that hold records.
7. Access Paths – Defined ways to locate data quickly.
8. Pointer Mechanisms – Store references for linked access (e.g., pointers to next record).
9. Metadata Storage – Stores data dictionary about types, constraints, etc.
10. Data Clustering – Related data is stored together to reduce retrieval time.

Q22: What are the two types of schemas in databases? Explain with examples.

1. Logical Schema – Describes structure at the logical level.

a. Example: CREATE TABLE Employee (ID INT, Name VARCHAR(20));
2. Physical Schema – Describes how data is physically stored on disk.
a. Example: Describes indexes, compression, partitioning of Employee table.

Difference:
 Type Description Example
Logical Defines structure, attributes,
Table definitions
Schema relationships
Physical Indexes, partitions, access
Specifies storage method and format
Schema paths

Q23: Explain how a schema evolves over time in a database system.

1. Schema Evolution refers to the ability to change database structure without losing data.
2. Common operations:
a. ALTER TABLE to add/drop/modify columns.
b. RENAME to rename tables/columns.
c. Adding constraints dynamically.
3. Handled with minimal downtime using versioning tools.
4. View definitions can help maintain backward compatibility.
5. Schema evolution must consider:
a. Data migration
b. Query adaptation
c. Application layer updates

Q24: Discuss the significance of a physical schema in database design.

1. It defines how data is physically stored and accessed.

2. Crucial for performance optimization and storage efficiency.
3. Impacts data retrieval speed through access paths and indexes.
4. Determines how data is partitioned across storage devices.
5. Helps implement security at the file level.
6. Enables better load balancing in distributed databases.
7. Supports backup and recovery using physical file formats.
8. Affects buffer management and caching strategies.
9. Ensures minimal I/O operations for frequent queries.
10. Essential in large-scale DBMS for scalability and maintenance.

Module-2
37.How does conceptual modeling differ from physical modeling?
• Conceptual Modeling: Focuses on high-level data organization and structure.
• Physical Modeling: Describes actual database storage details like file paths and
indexes.
• Conceptual models use ER diagrams, while physical models use tables and keys.
• Conceptual changes are easier to implement than physical ones.

38.What is an ER model? Explain with an example.

 • An Entity-Relationship (ER) model is a visual representation of data and its
relationships.
• It uses entities (objects), attributes (properties), and relationships (associations).
• Example: In a school database, Student (entity) has Name and ID (attributes) and is
linked to Class (entity) by the "Enrolled_In" relationship.
• ER models simplify database design by showing clear data connections.

39.Define the term "entity" in the context of an ER model.

• An entity represents a real-world object or concept.
• Each entity has attributes that describe its properties.
• Entities are represented as rectangles in ER diagrams.
• Example: Student, Car, and Employee are entities.

40.Define a primary key in the context of an ER model.

• A primary key uniquely identifies each record in a table.
• It must contain unique and non-null values.
• Primary keys ensure data integrity and enable fast data retrieval.
• Example: Student_ID in a Student table.

41.Explain a weak entity type.

• A weak entity cannot be uniquely identified without a related strong entity.
• It relies on a foreign key from the strong entity for identification.
• Represented by a double rectangle in ER diagrams.
• Example: A Dependent entity relies on the Employee entity for identification.
42.Compare weak entity and strong entity.
• Strong Entity: Has a primary key to uniquely identify records.
• Weak Entity: Requires a foreign key plus its own key (partial key) for identification.
• Strong entities are represented by a single rectangle; weak entities by a double rectangle.
• Example: Employee (strong) and Dependent (weak).

43.What is an Extended Entity-Relationship (EER) model?

• The EER model extends the ER model with additional concepts.
• Includes features like specialization, generalization, and aggregation.
• Helps represent complex database designs more effectively.
• Example: Specializing a Person entity into Student and Teacher.

44.What does a relationship represent in an ER model?

• A relationship defines associations between entities.
• Represented by a diamond shape in ER diagrams.
• Can be one-to-one, one-to-many, or many-to-many.
• Example: Works_For links Employee and Department.
45.What is an entity type?
• An entity type is a collection of similar entities sharing attributes.
• Represented as rectangles in ER diagrams.
• Each instance of an entity type is called an entity instance.
• Example: Car as an entity type with Car_ID, Model, and Color.

47.What is a relationship type?

• A relationship type defines the set of associations between entity types.

• Described using relationship names and degree (binary, ternary, etc.).
• Represented by diamonds in ER diagrams.
• Example: Manages relationship type links Manager and Department.

48.What is the role of a primary key in a weak entity?

• A weak entity relies on the strong entity’s primary key for identification.
• The strong entity's primary key becomes a foreign key in the weak entity.
• This combination ensures each weak entity record is uniquely identified.
• Example: Dependent uses Employee_ID (primary key in Employee) as a foreign key.

5-Marks

49. Illustrate and design an ER model for an online shopping system,

including customers, products, and orders.

• Entities: Customer, Product, Order.

• Attributes:
o Customer: Customer_ID, Name, Address, Email.
o Product: Product_ID, Name, Price, Stock.
o Order: Order_ID, Order_Date, Total_Amount.
• Relationships:
o Places: Connects Customer and Order (1:M).
o Contains: Connects Order and Product (M:N).
• Primary Keys: Customer_ID, Product_ID, Order_ID.
• Foreign Keys: Customer_ID in Order, Order_ID in the Contains relationship.
• Diagram Elements: Use rectangles for entities, ovals for attributes, diamonds for
relationships, and arrows to indicate relationship types.
50. Convert the following business scenario into an ER diagram: A school
has teachers, students, and classes. Each student belongs to one class, and
each teacher teaches multiple classes.

• Entities: Student, Teacher, Class.

• Attributes:
o Student: Student_ID, Name, DOB.
o Teacher: Teacher_ID, Name, Subject.
o Class: Class_ID, Room_No, Time.
• Relationships:
o Belongs_To: Connects Student to Class (M:1).
o Teaches: Connects Teacher to Class (1:M).
• Primary Keys: Student_ID, Teacher_ID, Class_ID.
• Foreign Keys: Class_ID in Student, Teacher_ID in Class.

51. Differentiate between composite and derived attributes with examples.

Feature Composite Attribute Derived Attribute

An attribute that can be divided into An attribute derived from
Definition
smaller meaningful parts. other attributes.
Name can be divided into First_Name, Age is derived from
Example
Last_Name. Date_Of_Birth.
Not stored directly;
Storage Stored directly in the database.
calculated when needed.
Usage Provides detailed information in queries. Reduces data redundancy.
Diagram Shown as an oval connected to multiple Shown as an oval with a
Representation smaller ovals. dashed border.

52. Construct a relationship set for a university database connecting students

and courses.

• Entities: Student, Course.

• Attributes:
o Student: Student_ID, Name, Email.
o Course: Course_ID, Title, Credits.
• Relationship:
o Enrolled_In: Connects Student and Course (M:N).
• Attributes in Relationship: Enrollment_Date, Grade.
• Primary Keys: Student_ID, Course_ID.
• Foreign Keys: Student_ID and Course_ID in the Enrolled_In table.
53. Design an ER model for a university database that tracks students,
professors, and courses.

• Entities: Student, Professor, Course.

• Attributes:
o Student: Student_ID, Name, Email.
o Professor: Professor_ID, Name, Department.
o Course: Course_ID, Title, Credits.
• Relationships:
o Takes: Connects Student and Course (M:N).
o Teaches: Connects Professor and Course (1:M).
• Primary Keys: Student_ID, Professor_ID, Course_ID.

54. Represent an aggregation to connect suppliers, parts, and shipments in a

supply chain database.

• Entities: Supplier, Part, Shipment.

• Attributes:
o Supplier: Supplier_ID, Name, Location.
o Part: Part_ID, Name, Type.
o Shipment: Shipment_ID, Date, Quantity.
• Relationships:
o Supplies: Connects Supplier and Part.
o Shipped_By: Connects Supplier to Shipment.
• Aggregation: Combines the Supplies relationship with the Shipment entity.

55. Can an EER model manage staff, doctors, and patients in a hospital
database?

• Entities: Staff, Doctor, Patient.

• Attributes:
o Staff: Staff_ID, Name, Role.
o Doctor (Specialization): Doctor_ID, Department.
o Patient: Patient_ID, Name, Diagnosis.
• Relationships:
o Treats: Connects Doctor to Patient.
o Manages: Connects Staff to Patient.
• Generalization/Specialization: Doctor is a specialization of Staff.

56. Define conceptual modeling in databases. Explain its purpose with an

example.

• Conceptual modeling defines the logical structure of a database.

 • Focuses on high-level data organization, relationships, and constraints.
• Helps designers visualize database structure without technical details.
• Example: Designing an ER diagram for a library database showing Books, Members,
and Loans.

57. What are the key steps involved in conceptual database design?

1. Requirement Analysis: Understand user needs and system goals.

2. Identify Entities: Determine key objects to be represented.
3. Define Attributes: Specify relevant properties for each entity.
4. Establish Relationships: Define connections between entities.
5. Apply Constraints: Add rules like cardinality and participation.
6. Refine the Model: Improve structure by adding generalization, specialization, or
aggregation.
7. Validation: Ensure the model meets requirements.

58. Compare conceptual modeling with physical database design.

Feature Conceptual Design Physical Design

Data structure and
Focus Database storage, indexing, and performance.
relationships.
High-level, simpler
Complexity Technical and detailed design.
design.
ER diagrams, EER
Tools Used SQL, schemas, and indexing techniques.
models.
Changes Easier to modify. Complex and time-consuming to change.
Designing entities like Implementing tables with specific data types
Example
Student and Course. and constraints.
`

59. Develop a conceptual schema for a library system to manage books,

members, and loans.

• Entities: Book, Member, Loan.

• Attributes:
o Book: Book_ID, Title, Author.
o Member: Member_ID, Name, Phone_No.
o Loan: Loan_ID, Issue_Date, Return_Date.
• Relationships:
o Borrows: Connects Member and Book (M:N).
• Primary Keys: Book_ID, Member_ID, Loan_ID.
• Foreign Keys: Member_ID in Loan, Book_ID in Loan.
60. Why is the ER model widely used in database design?

• Visual Clarity: Easy to understand data structure using diagrams.

• Data Integrity: Ensures proper identification of primary and foreign keys.
• Flexibility: Supports complex relationships like M:N and 1:M.
• Efficiency: Reduces redundancy and improves data consistency.
• Documentation: Acts as a blueprint for database developers.
• Normalization: Helps organize data efficiently.
• Scalability: Can be expanded easily for larger databases.
• Conceptual Focus: Simplifies database design before implementation.
• Reduces Ambiguity: Clear representation of data relationships.
• Widely Adopted: Common standard in database design tools.

Q61: Discuss the advantages and limitations of using the ER model in database design.
Design an ER model for an online shopping platform that includes customers, products, and
orders. (10 marks)

Advantages:

1. Visual representation of database structure.

2. Helps in conceptual design before implementation.
3. Identifies relationships among data clearly.
4. Improves communication between stakeholders and developers.
5. Ensures data integrity through entity-relationship constraints.
6. Facilitates logical schema generation.
7. Enables early detection of design flaws.
8. Scalable and flexible for expanding system features.

Limitations:

9. Does not support complex data types (like multimedia).

10. Cannot model behavior or operations of the system.

11. Might become too abstract or large for complex systems.

12. Cannot enforce data integrity constraints by itself.

13. Less effective for real-time or temporal data modeling.

14. May lead to redundancy if not properly normalized.

ER Diagram for Online Shopping Platform:

Entities:
 • Customer (customer_id, name, email)
• Product (product_id, name, price)
• Order (order_id, date)

Relationships:

• Customer places Order (1:M)

• Order contains Product (M:N) → with associative entity OrderDetails (quantity)

ER Diagram:

Customer --(places)--> Order --(contains)--> Product

|
OrderDetails
(quantity, product_id)

15. Example Explanation:

A customer can place multiple orders. Each order can include multiple products. OrderDetails
maintains quantity.

Q62: Explain the importance of primary and foreign keys in maintaining data integrity. (10
marks)

1. Primary Key:

• Uniquely identifies each record.

• Cannot be NULL.
• Ensures entity integrity.

2. Foreign Key:

• Links one table to another.

• Maintains referential integrity.
• Can accept NULL or values from referenced primary key.

Importance in Data Integrity:

3. Prevents duplicate records (PK).

4. Prevents orphan records (FK).

5. Ensures valid relationships across tables.

6. Facilitates joins and data navigation.

7. Helps enforce business rules.

8. SQL Example:

CREATE TABLE Department (

dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

9. If you delete a department without ON DELETE CASCADE, its employees would

reference a non-existing dept → integrity loss.

10. Use in Normalization:

• Helps in designing 3NF and BCNF schemas.

• Avoids redundancy.

11. In ER modeling:

• Represented by underline (PK) and arrow (FK).

12. Enhances performance of joins.

13. Helps in data consistency checks.

14. Essential for setting up constraints and triggers.

15. Supports transaction control with consistency guarantees.

Q63: Differentiate between one-to-one, one-to-many, and many-to-many

relationships with examples. (10 marks)

Type Description Example Representation in ER

One entity instance related to One person has one Straight line with ‘1’ on
1:1
exactly one of another passport both sides
One entity instance related to A department has '1' near Department, 'N'
1:M
many of another many employees near Employee
Many entity instances related Students enroll in Resolved using
M:N
to many of another many courses associative entity
1. One-to-One (1:1):

• Example: Each person has one Aadhaar card.

CREATE TABLE Person (

person_id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE Aadhaar (

aadhaar_no BIGINT PRIMARY KEY,
person_id INT UNIQUE,
FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

2. One-to-Many (1:M):

• Example: One department has many employees.

CREATE TABLE Department (

dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

3. Many-to-Many (M:N):

• Example: Students enroll in courses.

CREATE TABLE Student (

student_id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE Course (

course_id INT PRIMARY KEY,
title VARCHAR(50)
);
CREATE TABLE Enrollment (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id),
PRIMARY KEY (student_id, course_id)
);

4. ER Diagram:

Student --< Enrollment >-- Course

5. M:N requires a junction table (associative entity) in relational model.

6. Clarifies cardinality and participation in relationships.

7. Helps in designing normalized schemas.

8. Used in real-world modeling (employees-projects, doctors-patients).

Q64: Define generalization and specialization. How are they represented in

ER models? (10 marks)

1. Generalization:

• Combines multiple lower-level entities into a higher-level entity.

• Bottom-up approach.

Example:

Entities – Car, Truck → Generalized as Vehicle.

2. Specialization:

• Divides a higher-level entity into lower-level subtypes.

• Top-down approach.

Example:

Entity – Employee → Specialized as Manager, Technician.

3. ER Representation:

• Use a triangle labeled “ISA”.

• Draw lines from sub-entities to the super-entity for generalization.
• Draw lines from super-entity to sub-entities for specialization.
 Vehicle Employee
/ \ / \
Car Truck Manager Technician
↑ ↑ ↑ ↑
Generalization Specialization

4. Used for inheritance and classification.

5. Supports attribute sharing via superclasses.

6. Allows constraints like disjoint or overlapping.

7. Helps model real-world hierarchy.

8. Can be implemented using foreign keys or class inheritance.

9. Specialization types:

• Disjoint/Overlapping
• Total/Partial

10. Example SQL:

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE Manager (

emp_id INT PRIMARY KEY,
department VARCHAR(50),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);

Q65: Explain the concept of aggregation in ER modeling with an example.

(10 marks)

1. Aggregation:

Used when we treat a relationship as an entity itself for further relationships.

2. Useful for modeling complex relationships.

3. Example Scenario:

• A professor guides a student on a project.

 • The project is a relationship between student and professor.
• The sponsor sponsors the project → Aggregation needed.

4. ER Diagram:

Student -----\
\ works_on
---------> Project
Professor ----/ |
|
sponsored_by
|
Sponsor

5. works_on is treated as an entity to relate with Sponsor.

6. SQL doesn’t directly support aggregation, but it’s simulated using junction tables.

7. Clarifies relationships involving actions or interactions.

8. Maintains semantic clarity in real-world models.

9. Enhances modularity and reusability of models.

10. Aggregation vs Composition:

• Aggregation is “has-a” relationship at relationship level.

Q66: Discuss the key features of the EER model. Provide examples for each
feature. (10 marks)

The Enhanced Entity-Relationship (EER) model extends the ER model with advanced
modeling concepts.

1. Specialization

• Divides entity into sub-entities.

• Example: Employee → Manager, Clerk.
2. Generalization

• Combines related entities into a higher one.

• Example: Car, Truck → Vehicle.

3. Aggregation

• A relationship treated as an entity.

• Example: Works_on (between Student and Project) is related to Sponsor.

4. Categorization (Union types)

• Combines multiple superclasses into a single subclass.

• Example: Owner is a union of Company and Person.

5. Inheritance

• Sub-entities inherit attributes/relationships from super-entity.

• Example: Student inherits name, id from Person.

6. Disjoint and Overlapping Constraints

• Disjoint: An entity belongs to only one subclass.

• Overlapping: Can belong to multiple subclasses.

7. Completeness Constraints

• Total: Must be in a subclass.

• Partial: May or may not be in a subclass.

Diagram Example:

Person
/ \
Student Teacher
(disjoint, total)

8. Better Real-World Modeling

• Captures hierarchies, roles, complex relationships.

9. Supports schema design for object-oriented databases.

10. Helps in accurate and flexible conceptual design.

Q67: Compare the ER and EER models. Discuss scenarios where the EER
model is more beneficial. (10 marks)

Feature ER Model EER Model

Entity, Attributes,
Basic Elements ER elements + advanced concepts
Relationships
Specialization/Generalizati
Not supported Fully supported
on
Aggregation Not supported Supported
Hierarchy No support Full support
Categorization No Yes
Complex modeling Limited Advanced real-world modeling
Enterprise apps (university,
Example Usage Simple apps (library, bank)
hospital)

Scenarios where EER is preferred:

1. University Database:
a. Person → Student, Faculty.
b. Teaches relationship has attributes (semester).
2. Hospital:
a. Staff → Doctor, Nurse.
b. Treats between Doctor & Patient, sponsored by Department (aggregation).
3. Inheritance Needed:
a. Vehicle superclass with Car, Bike subtypes.

EER enhances clarity, promotes reusability, and allows complex relationships.

Q68: Convert the following scenario into an ER model: A hospital has

doctors, patients, and appointments. Each doctor can treat multiple patients,
and each patient can have multiple appointments with different doctors. (10
marks)

Entities:

• Doctor (doc_id, name, specialty)

• Patient (patient_id, name, age)
• Appointment (appointment_id, date, time, doc_id, patient_id)
Relationships:

• Doctor–Patient: Many-to-Many via Appointment.

ER Diagram:

Doctor ------\
\
Appointment
/ \
Patient ------

M:N relationship modeled with Appointment

1. Entity Tables:

CREATE TABLE Doctor (

doc_id INT PRIMARY KEY,
name VARCHAR(50),
specialty VARCHAR(50)
);

CREATE TABLE Patient (

patient_id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

2. Relationship Table:

CREATE TABLE Appointment (

appointment_id INT PRIMARY KEY,
doc_id INT,
patient_id INT,
date DATE,
time TIME,
FOREIGN KEY (doc_id) REFERENCES Doctor(doc_id),
FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
);
3. Notes:

• Many-to-Many between doctors and patients is resolved using Appointment.

• Appointment is a relationship with attributes (date, time).
• Models real-world medical interactions.

Q69: Why is conceptual modeling essential in large-scale database projects?

Discuss with examples. (10 marks)

Conceptual Modeling:

It is the high-level design of a database that defines entities, relationships, and constraints,
independent of physical details.

Importance in Large Projects:

1. Foundation for Design

a. Helps capture requirements clearly.
b. Example: In a university DB, models students, courses, and enrollment.
2. Stakeholder Communication
a. Easy to understand by non-technical users.
b. Diagram-based visual modeling (ER diagrams).
3. Avoids Redundancy
a. Identifies duplicate data early.
4. Ensures Consistency
a. All teams (dev, admin) follow a uniform design.
5. Scalability
a. Supports growth in users or data.
6. Basis for Logical/Physical Design
a. Used to generate relational schema later.
7. Enables Early Error Detection
a. Fixing design issues is easier before implementation.
8. Documentation
a. Acts as a reference model for future updates.
9. Example:
a. Hospital database: Doctor, Patient, Appointment.
b. Conceptual model maps relationships clearly.
10. Tool Support
• Tools like ERwin, Lucidchart help design and refine.
Q70: Compare conceptual modeling and physical modeling. Provide suitable
examples for both. (10 marks)

Aspect Conceptual Modeling Physical Modeling

Focus What to store How to store
Elements Entities, relationships Tables, indexes, partitions
Audience Analysts, stakeholders DBAs, developers
Tools ER diagrams SQL, DBMS-specific tools
Example Student–Course–Enrollment Tables with data types, constraints
Independence Platform-independent DBMS-specific
Data Types Abstract (e.g., string) Concrete (e.g., VARCHAR(50))

Example:

• Conceptual:
o Student, Course, Enrollment (ER Diagram).
• Physical:

CREATE TABLE Enrollment (

student_id INT,
course_id INT,
date_enrolled DATE,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

Conclusion:

• Conceptual: "What is required?"

• Physical: "How is it implemented?"

Q71: Define the ER model. Describe its primary components in detail with
examples. (10 marks)

Entity-Relationship (ER) Model:

A high-level data model that defines data as entities, attributes, and relationships.

Primary Components:

1. Entities
 a. Real-world objects.
b. Example: Student, Course.
c. Types: Strong and Weak Entities.
2. Attributes
a. Properties of entities.
b. Example: name, age.
c. Types: Simple, Composite, Derived, Multivalued.
3. Entity Sets
a. Collection of similar entities.
b. Example: All students form the Student set.
4. Relationships
a. Association between entities.
b. Example: Enrolls (between Student and Course).
5. Relationship Sets
a. Collection of similar relationships.
6. Primary Key
a. Unique identifier for entity.
b. Example: student_id.
7. Cardinality
a. One-to-one, One-to-many, Many-to-many.
8. Participation
a. Total or partial.
9. Generalization/Specialization (EER)
a. Used in complex scenarios.

Diagram Example:

Student --(Enrolls)--> Course

Q72: Explain the role of relationships in the ER model. Illustrate your

answer with examples. (10 marks)

Role of Relationships:

They represent associations between two or more entities.

Types:

1. One-to-One
a. One employee has one ID card.
b. Employee -- IDCard
2. One-to-Many
a. One department has many employees.
b. Department -- Employee
 3. Many-to-Many
a. Students enroll in many courses, courses have many students.
b. Student -- Enrolls -- Course

Attributes in Relationships:

• Can store data specific to the relationship.

• Example: date_enrolled in Enrolls.

Participation:

• Total: Every entity must participate.

• Partial: Optional.

Cardinality Constraints:

• Define how many entities relate to others.

Diagram Example:

Student --(Enrolls [date])-- Course

Use in Database Design:

• Ensures data integrity.

• Defines how entities interact.
Module-3
73. Differentiate between DDL and DML commands with examples.

• DDL (Data Definition Language) defines, modifies, or deletes database structures (e.g.,
tables).
• DML (Data Manipulation Language) handles inserting, updating, and retrieving data.
• Examples of DDL commands: CREATE, ALTER, DROP.
• Examples of DML commands: INSERT, UPDATE, DELETE, SELECT.

74. Explain the purpose of the ALTER TABLE command with an example.

• ALTER TABLE modifies the structure of an existing table.

• It can add, modify, or delete columns or constraints.
• Example: ALTER TABLE Employee ADD PhoneNumber VARCHAR(15);
• Ensures database updates without recreating tables.
75. Define Data Manipulation Language (DML). Write the syntax of the
INSERT command.

• DML is used to manipulate data within database tables.

• It includes commands like INSERT, UPDATE, DELETE, and SELECT.
• Syntax for INSERT command: INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

• Example: INSERT INTO Employee (Name, Age) VALUES ('John', 30);

76. Explain the difference between the DELETE and DROP commands.

• DELETE removes specific rows from a table.

• DROP deletes the entire table structure permanently.
• DELETE can be rolled back; DROP cannot.
• Example:
o DELETE FROM Employee WHERE EmpID = 3;
o DROP TABLE Employee;

77. Write the syntax and purpose of the GRANT command in SQL.

• The GRANT command assigns user privileges to database objects.

• Syntax: GRANT privilege_name ON object_name TO user_name;

• Example: GRANT SELECT ON Employee TO user1;

• Enhances security by managing user roles.

78. Write an SQL query to implement a PRIMARY KEY constraint while

creating a table.

• A PRIMARY KEY uniquely identifies each row in a table.

• Example: CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

• Ensures data integrity by preventing duplicate values.

79. What is the purpose of a FOREIGN KEY constraint? How does it

maintain database integrity?

• A FOREIGN KEY establishes a relationship between tables.

 • It references the PRIMARY KEY of another table.
• Ensures only valid data is inserted in the referencing column.
• Prevents orphaned records by enforcing referential integrity.

80. How does the REVOKE command differ from the GRANT command?
Provide an example.

• GRANT assigns privileges; REVOKE removes them.

• Syntax for REVOKE: REVOKE privilege_name ON object_name FROM user_name;

• Example: REVOKE SELECT ON Employee FROM user1;

• REVOKE enhances security by restricting access.

81. Explain the concept of database constraints. List any three types of
constraints.

• Constraints enforce data rules for accuracy and consistency.

• Types of constraints:
o PRIMARY KEY: Ensures unique identification.
o FOREIGN KEY: Establishes table relationships.
o CHECK: Enforces a condition on data.

82. Explain the use of the GROUP BY clause in SQL. Provide an example
query.

• GROUP BY groups rows that have the same values in specified columns.
• It is often used with aggregate functions.
• Example: SELECT DepartmentID, AVG(Salary)
FROM Employee
GROUP BY DepartmentID;

• Useful for summarizing data like averages, totals, etc.

83. Differentiate between the GROUP BY and HAVING clauses in SQL with
an example.

• GROUP BY groups rows based on column values.

• HAVING filters grouped records using conditions.
• Example: SELECT DepartmentID, AVG(Salary)
FROM Employee
GROUP BY DepartmentID
 HAVING AVG(Salary) > 60000;

• WHERE filters before grouping, HAVING filters after.

84. Explain the UNION operator in SQL. How does it differ from the UNION
ALL operator?

• UNION combines results from multiple SELECT queries, removing duplicates.

• UNION ALL includes all records, even duplicates.
• Example: SELECT Name FROM Employees
UNION
SELECT Name FROM Managers;

• UNION improves result clarity by ensuring unique records.

5 MARKS

85. Demonstrate how to use INSERT INTO command to add data into the
Employee table.

• The INSERT INTO command is used to add data into a table. It can insert data into
specific columns or all columns in a table.
• Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• Example 1: Inserting one record

INSERT INTO Employee (EmpID, Name, Age, Salary)
VALUES (1, 'John Doe', 30, 55000);

• Example 2: Inserting multiple records

INSERT INTO Employee (EmpID, Name, Age, Salary)
VALUES
(2, 'Jane Smith', 28, 60000),
(3, 'Alice Brown', 35, 75000);

• Key Notes:
Ensure the data types match the column definitions.
When inserting all values for all columns, column names may be skipped:
INSERT INTO Employee VALUES (4, 'Bob White', 40, 70000);
86. Write an SQL query to retrieve all records from Employee where Salary
is greater than 50000.

• The SELECT statement is used to retrieve data from a database.

• Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Example Query:
SELECT * FROM Employee
WHERE Salary > 50000;

• Explanation:
The * selects all columns from the table.
The WHERE clause filters records where the Salary is greater than 50000.
This method efficiently retrieves specific data without displaying unnecessary rows.

87. Explain how to use the UPDATE statement to modify an existing Salary
for a given EmpID in the Employee table.

• The UPDATE statement modifies data in a table.

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

• Example Query:
UPDATE Employee
SET Salary = 65000
WHERE EmpID = 2;

• Explanation:
The SET keyword assigns a new value to the Salary column.
The WHERE clause ensures only the employee with EmpID = 2 is updated.
Without the WHERE clause, all records in the table would be updated.
88. What is the purpose of the ALTER TABLE command? Provide an
example using Employee table.

• The ALTER TABLE command is used to modify an existing table's structure. It can:

Add new columns

Modify column data types

Drop columns

Add or remove constraints

• Syntax:

ALTER TABLE table_name

ADD/MODIFY/DROP column_name data_type;

• Example 1: Adding a column

ALTER TABLE Employee
ADD Email VARCHAR(100);

• Example 2: Modifying a column data type

ALTER TABLE Employee
MODIFY Salary DECIMAL(10, 2);

• Example 3: Dropping a column

ALTER TABLE Employee
DROP COLUMN Email;

• Key Notes:
Always back up data before performing structural changes.
Use ALTER TABLE cautiously as irreversible changes can occur.

89. Define the purpose of the GROUP BY clause in SQL with an example
query on Employee to find the average Salary for each DepartmentID.

• The GROUP BY clause groups rows based on one or more columns and is commonly
used with aggregate functions like AVG(), SUM(), COUNT(), etc.
• Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

• Example Query:
SELECT DepartmentID, AVG(Salary) AS Avg_Salary
FROM Employee
GROUP BY DepartmentID;

• Explanation:
The AVG() function calculates the average salary.
The GROUP BY clause groups employees by their DepartmentID.

The AS keyword assigns a custom alias (Avg_Salary) for clarity. I'll continue with the
remaining answers in the same detailed format. Here's the next set:

90. Write an SQL query to use the AVG() function on the Salary column to
calculate the average salary of all employees.

• The AVG() function calculates the average value of a numeric column.

• Syntax:
SELECT AVG(column_name) AS alias_name
FROM table_name;

• Example Query:
SELECT AVG(Salary) AS Average_Salary
FROM Employee;

• Explanation:
The AVG() function calculates the average salary across all employee records.
The AS keyword assigns a clear alias name (Average_Salary) to the result.
No WHERE or GROUP BY clause is needed here since we're calculating for all records.

91. Explain the difference between INNER JOIN and LEFT JOIN. Provide
an SQL query that uses LEFT JOIN to retrieve employees along with their
department names.

• INNER JOIN: Returns only the records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table (e.g., Employee) and matching
records from the right table (e.g., Department). If no match is found, NULL values
are returned for columns from the right table.
 • Syntax for LEFT JOIN:

SELECT A.column1, B.column2

FROM table_A A
LEFT JOIN table_B B
ON A.common_column = B.common_column;

• Example Query:
SELECT E.EmpID, E.Name, D.DepartmentName
FROM Employee E
LEFT JOIN Department D
ON E.DepartmentID = D.DepartmentID;

• Explanation:
The LEFT JOIN retrieves all employee records even if some employees don't belong to
a department.
Non-matching records will show NULL in the DepartmentName column.
The alias E for Employee and D for Department keeps the query concise.

92. Write an SQL query to delete a record from Employee where EmpID =
3.

• The DELETE command removes specific records from a table.

• Syntax:
DELETE FROM table_name
WHERE condition;

• Example Query:
DELETE FROM Employee
WHERE EmpID = 3;

• Explanation:
The WHERE clause ensures that only the employee with EmpID = 3 is deleted.
Without the WHERE clause, all records would be deleted, so caution is necessary.

93. Write an SQL query to create a primary key constraint on the EmpID
column of the Employee table.

• A Primary Key uniquely identifies each record in a table and ensures that values in
that column are unique and non-null.
• Syntax (During Table Creation):
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2)
);

• Syntax (On Existing Table):

ALTER TABLE Employee
ADD CONSTRAINT PK_EmpID PRIMARY KEY (EmpID);

• Explanation:
The PRIMARY KEY constraint ensures each EmpID is unique and non-null.
The ALTER TABLE method is used to add a primary key constraint to an existing table.

95. Write an SQL query using HAVING clause to filter the average Salary
above 60000 for each DepartmentID after grouping by DepartmentID.

• The HAVING clause is used to filter grouped records based on aggregate conditions.
• Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name
GROUP BY column1
HAVING condition;

• Example Query:
SELECT DepartmentID, AVG(Salary) AS Avg_Salary
FROM Employee
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;

• Explanation:
The GROUP BY clause groups records by DepartmentID.
The AVG() function calculates the average salary for each department.
The HAVING clause filters results where the average salary is greater than 60000.
Unlike WHERE, the HAVING clause works after grouping.

96. Write an SQL query that uses DISTINCT to find all unique
DepartmentID values from the Employee table.

• The DISTINCT keyword eliminates duplicate values in the selected column.

• Syntax:
SELECT DISTINCT column_name
FROM table_name;

• Example Query:
SELECT DISTINCT DepartmentID
FROM Employee;

• Explanation:
The DISTINCT keyword ensures that each DepartmentID appears only once.
Useful when identifying unique values in large datasets.

Q97. Write a query to demonstrate a transaction using COMMIT and ROLLBACK. (10
Marks)

Explanation:

• A transaction is a set of SQL operations executed as a single unit.

• COMMIT saves the changes permanently to the database.
• ROLLBACK undoes changes made during the transaction.

Example:

BEGIN TRANSACTION;

INSERT INTO Accounts (account_id, holder_name, balance)

VALUES (101, 'John Doe', 5000);

UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 101;

-- If no error, commit changes

COMMIT;

-- If there’s an error, use:

ROLLBACK;

Q98. Define the UNION operator in SQL and explain its purpose with an example. (10
Marks)

Definition:

• The UNION operator combines the result of two SELECT statements.

• It removes duplicate rows from the result set.
• Each query must have the same number and type of columns.

Syntax:
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Example:

SELECT name FROM Employees

UNION
SELECT name FROM Managers;

Explanation:

• Returns a list of unique names from both Employees and Managers tables.

Q99. Write an SQL query to join two tables Orders and Customers using the INNER
JOIN clause. (10 Marks)

Query:

SELECT Customers.customer_name, Orders.order_id, Orders.order_date

FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Explanation:

• Combines rows from both tables where the customer_id matches.

• Only matched records are returned.

Q100. Explain any five string manipulation functions in SQL with suitable examples. (10
Marks)

1. UPPER() – Converts text to uppercase

SELECT UPPER('sql functions'); -- Result: SQL FUNCTIONS

2. LOWER() – Converts text to lowercase

SELECT LOWER('SQL FUNCTIONS'); -- Result: sql functions

3. LENGTH() – Returns length of a string

SELECT LENGTH('Database'); -- Result: 8

4. SUBSTRING() / SUBSTR() – Extracts part of a string

SELECT SUBSTRING('Relational', 1, 5); -- Result: Relat

5. CONCAT() – Combines two or more strings

SELECT CONCAT('Data', 'Base'); -- Result: DataBase

Use Cases: Formatting output, parsing input data, standardizing case, etc.

Q101. Write SQL commands to: (a) Insert a record into a table (b) Update a record (c)
Delete a record. Explain the significance of each operation. (10 Marks)

a) INSERT:INSERT INTO Students (student_id, name, course)

VALUES (101, 'Amit', 'DBMS');

Purpose: Adds new data to a table.

b) UPDATE:

UPDATE Students
SET course = 'AI'
WHERE student_id = 101;

Purpose: Modifies existing records.

c) DELETE:DELETE FROM Students

WHERE student_id = 101;

Purpose: Removes records.

Significance: These commands enable CRUD operations essential for database manipulation.

Q102. Explain the role of FOREIGN KEY in relational databases. Write an SQL query
to create a foreign key constraint. (10 Marks)

Definition:

• A FOREIGN KEY is a column that establishes a link between two tables.

• It ensures referential integrity by restricting values to those that exist in the referenced
table.

Example:

CREATE TABLE Orders (

order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Explanation:
 • Ensures that only valid customer IDs from the Customers table can appear in Orders.

Q103. Discuss the advantages and limitations of using SQL constraints. Provide scenarios
where constraints improve data integrity. (10 Marks)

Advantages:

1. Data Integrity: Prevents invalid data entry.

2. Consistency: Maintains uniform data across tables.
3. Automation: Reduces need for manual data checks.
4. Referential Integrity: Enforced by FOREIGN KEY constraints.
5. Improved Query Accuracy: Ensures data reliability.

Limitations:

1. Less Flexibility: Constraints may block valid but exceptional data.

2. Complexity: Can complicate table design.
3. Performance Overhead: May slow down insert/update operations.

Scenarios:

• NOT NULL for mandatory fields (e.g., email).

• CHECK to restrict age > 18.
• UNIQUE for email to avoid duplicates.
• FOREIGN KEY for relational integrity.

Q104. Discuss the differences between COMMIT and ROLLBACK commands in

transaction control. (10 Marks)

Feature COMMIT ROLLBACK

Saves changes
Function Undoes all uncommitted changes
permanently
After successful
Usage After error or cancel condition
transaction
Changes visible to
Data State Restores original state
other users
DCL (Transaction
Command Type DCL
Control)

Example:

BEGIN;
UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT; -- Changes saved
BEGIN;
UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
ROLLBACK; -- Changes undone

Conclusion: COMMIT finalizes changes, while ROLLBACK allows for correction.

Module-4

109. Types of Views in SQL

• Simple View: Based on a single table, no group functions.

• Complex View: Based on multiple tables or includes aggregation.
• Updatable View: Allows DML operations if certain conditions are met.
• Read-only View: Based on complex logic; cannot be updated directly.

110. Assertions in SQL

• Assertions define constraints that involve multiple tables or rows.

• Enforced using the CHECK clause within CREATE ASSERTION.
• Ensures data integrity across a database schema.
• Example:

CREATE ASSERTION check_salary CHECK (NOT EXISTS (

SELECT * FROM employee WHERE salary < 0));

111. Triggers in SQL

• A trigger automatically fires on specific table events.

• Can be BEFORE or AFTER INSERT, UPDATE, or DELETE.
• Used for auditing, enforcing rules, or automating tasks.
• Defined using CREATE TRIGGER.

112. Types of Privileges in SQL

• SELECT: Read data from a table/view.

• INSERT: Add new records to a table.
• UPDATE: Modify existing records.
• DELETE: Remove records from a table.

113. GRANT vs. REVOKE

• GRANT gives specified privileges to users.

• REVOKE removes those privileges.
 • Used to control access in multi-user environments.
• Syntax:

GRANT SELECT ON table TO user;

REVOKE SELECT ON table FROM user;

114. Tuple Relational Calculus

• Non-procedural language for querying.

• Specifies what to retrieve, not how.
• Uses variables, predicates, and logical connectives.
• Example:

{ t | t ∈ Employee ∧ t.salary > 50000 }

115. Role-Based Access Control in SQL

• Access is granted based on roles, not individual users.

• Roles are collections of privileges.
• Users assigned to roles automatically get those privileges.
• Easier to manage permissions for large user groups.

116. Nested Queries

• A query within a query, also called a subquery.

• Inner query runs first and passes its result to the outer query.
• Can be used with IN, ANY, ALL, EXISTS, etc.
• Helps with filtering, aggregations, and comparisons.

117. Correlated Subquery

• Inner query depends on outer query values.

• Executed once for each row of the outer query.
• Example:

SELECT name FROM employee e1

WHERE salary > (SELECT AVG(salary) FROM employee e2
WHERE e1.department_id = e2.department_id);
118. Cascading Deletes

• Automatically delete related records in child table when parent is deleted.

• Helps maintain referential integrity.
• Defined using ON DELETE CASCADE.
• Example:

FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE CASCADE;

119. Natural Join vs. Equi Join

• Natural Join: Joins tables using columns with the same name.
• Equi Join: Uses a specified equality condition (=) between any columns.
• Natural join eliminates duplicate columns.
• Example:SELECT * FROM A NATURAL JOIN B;
SELECT * FROM A JOIN B ON A.id = B.ref_id;

120. Materialized Views

• A stored snapshot of a query result.

• Improves performance for complex queries.
• Can be refreshed periodically or on demand.
• Used in data warehousing.

5-Marks

121. Query: Employees earning more than department average

SELECT e1.emp_id, e1.name, e1.department_id, e1.salary

FROM employees e1
WHERE salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);

• Uses correlated subquery.

• Computes average salary per department.
• Filters employees earning more than that average.
• Helps in performance reviews or bonus calculations.
 • Can be extended with joins for department names.

122. Trigger to prevent deletion if CGPA > 9.0

CREATE OR REPLACE TRIGGER prevent_high_cgpa_delete

BEFORE DELETE ON students
FOR EACH ROW
WHEN (OLD.CGPA > 9.0)
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Cannot delete student with CGPA above 9.0');
END;

• Prevents deletion of high-performing students.

• Uses BEFORE DELETE.
• RAISE_APPLICATION_ERROR generates a custom error.
• Protects critical academic data.
• Useful in academic databases.

123. Create View TopStudents

CREATE VIEW TopStudents AS

SELECT student_id, name, CGPA
FROM students
WHERE CGPA > 8.0;

• Creates a virtual table.

• Simplifies future queries on high scorers.
• View can be used with SELECT, JOIN, etc.
• Can be refreshed if the base data changes.
• Promotes data abstraction.

124. INNER JOIN: Customers with orders

SELECT c.name, o.order_id, o.order_date

FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

• Retrieves only customers with orders.

• Uses INNER JOIN to link customer_id.
• Filters out customers with no purchases.
• Useful for marketing and sales analysis.
• Can be extended to show order totals, etc.
125. AVG Salary Query Analysis

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• Selects names of employees earning above average salary.

• The subquery gives overall average salary.
• A classic example of filtering using aggregation.
• Encourages benchmarking of performance.

126. View Output Explanation

CREATE VIEW HighSalary AS

SELECT name, salary
FROM employees
WHERE salary > 50000;

SELECT * FROM HighSalary;

• View filters employees with salary > 50000.

• Second query fetches data from that view.
• Acts like a named filter.
• Makes frequent queries more readable.

127. INDEX and Query

CREATE INDEX idx_salary ON Employee(salary);

• Creates an index on salary column.

• Speeds up WHERE, ORDER BY, or GROUP BY on salary.
• Improves query performance.
• Useful for large datasets.
• Like an index in a book for faster lookup.

128. NULL DepartmentID Query

SELECT * FROM employees

WHERE DepartmentID IS NULL;

• Retrieves employees with no assigned department.

• Useful for data cleanup.
 • Highlights orphan records.
• Helps maintain data consistency.

129. Highest Salary Per Department

SELECT *
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);

• Finds top earners in each department.

• Uses a correlated subquery.
• Can be extended with JOIN for department name.
• Useful for managerial analysis.
• Highlights performance leaders.

130. Pivot for Department-Wise Count

(SQL Server syntax)

SELECT *
FROM (
SELECT department_id FROM employees
) AS SourceTable
PIVOT (
COUNT(department_id)
FOR department_id IN ([101], [102], [103])
) AS PivotTable;

• Converts rows to columns.

• Used for reporting and dashboards.
• Easy to compare department sizes side by side.
• Useful in BI tools.

131. MERGE Example

MERGE INTO employees e

USING new_employees ne
ON (e.emp_id = ne.emp_id)
WHEN MATCHED THEN
UPDATE SET e.salary = ne.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary) VALUES (ne.emp_id, ne.name, ne.salary);

• Combines insert and update operations.

• Prevents data duplication.
• Useful in data migration and syncing tables.
• Improves efficiency and cleaner logic.

132.)Comparison between UNION and UNION ALL

1. Purpose: Both combine results from multiple SELECT statements.

2. Duplicates:
a. UNION removes duplicates.
b. UNION ALL includes all duplicates.
3. Performance:
a. UNION is slower due to the duplicate-checking process.
b. UNION ALL is faster as it skips this step.
4. Syntax:
a. UNION: SELECT ... UNION SELECT ...
b. UNION ALL: SELECT ... UNION ALL SELECT ...
5. Column Matching: Both require the same number and order of columns with matching
data types.
6. Use Case:
a. Use UNION when only distinct results are needed.
b. Use UNION ALL when you need all occurrences, including duplicates.
7. Sorting Behavior:
a. UNION implicitly sorts results.
b. UNION ALL does not sort unless explicitly specified.
8. Memory Usage:
a. UNION uses more memory due to sorting and duplicate removal.
b. UNION ALL is memory-efficient.
9. Execution Time:
a. UNION has longer execution time due to overhead.
b. UNION ALL executes faster.
10. Example:

SELECT Name FROM A

UNION
SELECT Name FROM B;

SELECT Name FROM A

UNION ALL
SELECT Name FROM B;
10-Marks

Q134. Discuss the different types of Joins in SQL with suitable examples. (10 Marks)

Definition:

Joins in SQL are used to combine rows from two or more tables based on a related column
between them. They help retrieve meaningful data from normalized databases.

Types of Joins in SQL:

1. INNER JOIN:
a. Returns records that have matching values in both tables.
b. Excludes unmatched rows.

Example:

SELECT Employees.name, Departments.department_name

FROM Employees
INNER JOIN Departments ON Employees.department_id = Departments.department_id;

Explanation: Only employees who belong to a department are shown.

2. LEFT JOIN (or LEFT OUTER JOIN):

a. Returns all records from the left table, and matched records from the right table.
b. Unmatched right table columns return NULL.

Example:

SELECT Employees.name, Departments.department_name

FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.department_id;

Explanation: Shows all employees, including those not assigned to any department.

3. RIGHT JOIN (or RIGHT OUTER JOIN):

a. Returns all records from the right table, and matched records from the left table.
b. Unmatched left table columns return NULL.

Example:

SELECT Employees.name, Departments.department_name

FROM Employees
RIGHT JOIN Departments ON Employees.department_id = Departments.department_id;
Explanation: Shows all departments, including those without any employees.

4. FULL JOIN (or FULL OUTER JOIN):

a. Returns all records from both tables.
b. Unmatched records from each side will have NULLs.

Example:

SELECT Employees.name, Departments.department_name

FROM Employees
FULL JOIN Departments ON Employees.department_id = Departments.department_id;

Explanation: Includes all employees and departments, even if there is no match.

5. SELF JOIN:
a. A table is joined with itself to compare rows within the same table.

Example:SELECT A.name AS Employee, B.name AS Manager

FROM Employees A
JOIN Employees B ON A.manager_id = B.emp_id;

Explanation: Used to show manager-employee relationships.

6. CROSS JOIN:
a. Returns the Cartesian product of two tables.
b. Every row from the first table is joined with all rows from the second.

Example:

SELECT A.name, B.department_name

FROM Employees A
CROSS JOIN Departments B;

Explanation: Useful for generating combinations, but usually avoided in large datasets.

Conclusion:

Joins are essential for retrieving related data from multiple tables. Proper use of joins improves
data analysis, performance, and query clarity.

Q135. Given the following Customers and Orders tables:

Customers Table:

customer_id | name
------------|------
1 | Alice
2 | Bob
3 | Charlie

Orders Table:

order_id | customer_id | order_date

---------|--------------|------------
101 | 1 | 2024-01-01
102 | 2 | 2024-01-05
103 | 1 | 2024-01-10

Query:

SELECT Customers.name, Orders.order_id, Orders.order_date

FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Explanation:

• The INNER JOIN retrieves only the records where Customers.customer_id =

Orders.customer_id.
• It excludes customers with no orders.

Result:

name | order_id | order_date

--------|----------|------------
Alice | 101 | 2024-01-01
Bob | 102 | 2024-01-05
Alice | 103 | 2024-01-10

Q136. View for High Salary Employees

a) Create a view called HighEarningEmployees for employees earning more than 50,000:

CREATE VIEW HighEarningEmployees AS

SELECT emp_id, name, salary, department_id
FROM Employees
WHERE salary > 50000;

b) Retrieve all records from the view:

SELECT * FROM HighEarningEmployees;

Explanation:

• Views are virtual tables derived from SELECT queries.

• This view stores frequently accessed data for high salary employees to improve query
performance.

Q137. Discuss the importance of Database Security and Authorization in modern

applications.

1. Protection Against Unauthorized Access: Ensures only authorized users access

sensitive data.
2. Data Privacy: Maintains user confidentiality and adheres to data protection laws (e.g.,
GDPR).
3. Access Control: Role-based permissions (e.g., admin, user) help manage who can read
or modify data.
4. Preventing Data Manipulation: Protects against malicious actions like unauthorized
updates or deletions.
5. SQL Injection Protection: Proper security practices reduce the risk of SQL injection
attacks.
6. Audit Trails: Logs activities for accountability and tracking.
7. Data Integrity: Ensures data is consistent and accurate.
8. Encryption: Data is encrypted both at rest and in transit.
9. Authentication & Authorization: Secure login and role-based access control
mechanisms.
10. Backup & Recovery: Protects data during system failures or cyberattacks.
11. Security Policies: Define how data should be protected.
12. Firewalls & Access Logs: Prevent external threats.
13. Compliance: Meets industry standards and legal regulations.
14. Data Masking: Hides sensitive information for non-privileged users.
15. Secure APIs: Interfaces must be secured to avoid data breaches.

Conclusion: Database security and authorization are critical to maintaining the integrity,
confidentiality, and availability of modern applications.

Q138. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and
FULL JOIN in SQL with examples.

Join Type Description

INNER
Returns only matching rows from both tables.
JOIN
LEFT Returns all rows from the left table, matched rows from the right or
JOIN NULL.
RIGHT Returns all rows from the right table, matched rows from the left or
JOIN NULL.
 FULL
Returns all rows from both tables, matched or unmatched (with NULLs).
JOIN

Example Tables:

Customers:
customer_id | name
------------|------
1 | Alice
2 | Bob
3 | Charlie

Orders:
order_id | customer_id
---------|--------------
101 | 1
102 | 2

INNER JOIN Example:

SELECT name, order_id

FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Returns: Alice (101), Bob (102)

LEFT JOIN Example:

SELECT name, order_id

FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

Returns: Alice, Bob, Charlie (order_id NULL)

RIGHT JOIN Example:

SELECT name, order_id

FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

Returns: Alice, Bob (same as INNER JOIN here)

FULL JOIN Example:

SELECT name, order_id
FROM Customers
FULL JOIN Orders ON Customers.customer_id = Orders.customer_id;

Returns: Alice, Bob, Charlie (NULL), and any unmatched orders

Q139. Query to find students who scored above the average in ‘DBMS’ subject

SELECT s.student_id, s.student_name

FROM students s
JOIN marks m ON s.student_id = m.student_id
WHERE m.subject = 'DBMS'
AND m.score > (
SELECT AVG(score)
FROM marks
WHERE subject = 'DBMS'
);

Explanation:

• The subquery calculates the average marks for DBMS.

• The main query retrieves students scoring more than that average.

Q140. (Repeated) See Q137

Q141. How can privileges be granted to users in SQL? Explain with an example.

Theory:

• Privileges allow users to perform operations like SELECT, INSERT, UPDATE,

DELETE.
• The GRANT command is used.

Syntax:

GRANT privilege_list ON table_name TO user;

Example:

GRANT SELECT, INSERT ON Employees TO user1;

Explanation:

• This gives user1 permission to view and insert data in Employees.

With GRANT OPTION:

GRANT SELECT ON Employees TO user1 WITH GRANT OPTION;

• Allows user1 to grant the SELECT privilege to others.

Q142. What is the process of revoking authorization in SQL? Provide a detailed

explanation with an example.

Theory:

• The REVOKE command is used to take back previously granted privileges.

Syntax:

REVOKE privilege_list ON table_name FROM user;

Example:

REVOKE INSERT ON Employees FROM user1;

Explanation:

• Removes INSERT permission from user1 on the Employees table.

Cascading Effect:

• If user1 granted access to another user using WITH GRANT OPTION, revoking
user1’s access may also remove it from those users.

Q143. Define the term Relational Algebra. List its operations and explain each with
examples.

Definition:

• Relational algebra is a procedural query language that uses operations to manipulate

relations (tables).
• It forms the theoretical foundation of SQL.

Basic Operations:

1. SELECT (σ): Filters rows.

σ condition (Relation)

σ salary > 50000 (Employees)

 2. PROJECT (π): Selects columns.

π name, salary (Employees)

3. UNION (∪): Combines tuples from two relations.

A∪B

4. SET DIFFERENCE (−): Returns tuples in one relation but not the other.

A−B

5. CARTESIAN PRODUCT (×): Combines all tuples from two relations.

A×B

6. RENAME (ρ): Renames a relation or attributes.

ρ(Emp, Employees)

Additional Operations:

7. JOIN (⨝): Combines relations based on a condition.

A⨝B

8. INTERSECTION (∩): Returns common tuples.

A∩B
9. DIVISION (/): Used for queries like “Find students who took all courses.”

Example:

To get names of employees with salary > 50000:

π name (σ salary > 50000 (Employees))

Q144. What is Tuple Relational Calculus? How is it different from Domain Relational
Calculus?

Tuple Relational Calculus (TRC):

• Non-procedural query language.

• Specifies what to retrieve, not how.
• Uses tuple variables.

{t | t ∈ Employees AND t.salary > 50000}

Domain Relational Calculus (DRC):

• Also non-procedural.
• Uses domain (field) variables.

{<name, salary> | ∃ id ∃ dept (Employees(id, name, salary, dept) AND salary > 50000)}

Difference Table:

Feature Tuple Relational Calculus Domain Relational Calculus

Based On Tuple variables Field (domain) variables
Syntax {t condition(t)}
Expressiveness Same Same
Example Variable t.name, t.salary name, salary

Conclusion:

• Both are theoretical query languages used in database systems.

• TRC is tuple-oriented; DRC is attribute/domain-oriented.
• They form the basis for SQL.

Module-5
2-Marks Questions (4 Points Each)

145.What are insertion anomalies? Provide an example.

• Occur when certain attributes cannot be inserted into the database without the presence
of other attributes.
• Happens due to bad database design (e.g., storing customer data in loan table).
• Example: Cannot add a new branch without a loan.
• Violates normalization principles.

146.Explain deletion anomalies with an example.

• Deleting one record may unintentionally remove useful data.

• Example: Removing a loan entry may delete entire branch info.
• This is due to redundancy in a single table.
• Indicates poor relational schema design.

147.Describe update anomalies and their impact on databases.

• Occur when data is duplicated and a change in one instance requires changes in all
duplicates.
 • Failure leads to data inconsistency.
• Example: Changing branch address in one place, not others.
• Indicates poor normalization.

148.Identify the pitfalls in designing a database for an e-commerce platform.

• Redundant data storage.

• Difficulty in handling NULL values.
• Complicated updates due to data spread.
• Inability to represent all data (e.g., no product but need to store category).

149.What is meant by data inconsistency in database design?

• When the same data exists in multiple places but is not updated uniformly.
• Happens due to redundancy.
• Example: Two customer records with different addresses.
• Leads to reliability issues.

150.Why is normalization important in database design?

• Reduces redundancy.
• Ensures data consistency.
• Prevents anomalies (insert, delete, update).
• Makes database maintenance easier.

151.Define functional dependency with an example.

• Relationship where one attribute determines another.

• Notation: A → B (A determines B).
• Example: roll_no → name, dept_name.
• Ensures integrity and is used in normalization.

152.Define database anomalies in relational database design.

• Insert, delete, update issues due to poor schema.

• Caused by redundant and unstructured data.
• Affects integrity and consistency.
• Resolved using normalization.

5-Marks Questions (10 Points Each)

157.Explain the types of functional dependencies with examples.

• Trivial: A → A or A → subset of A (e.g., ABC → AB).

• Non-Trivial: A → B where B is not a subset of A.
 • Multivalued: X →→ Y, Y and Z are independent (e.g., color and year).
• Transitive: A → B and B → C implies A → C.
• Helps in normalization.
2. Analyze the impact of partial and transitive dependencies on database design.
• Partial: Non-prime attribute depends on part of a composite key. Causes redundancy.
• Transitive: Non-key depends indirectly on primary key via another attribute. Leads to
anomalies.
• Both violate normal forms (2NF and 3NF).
• Require decomposition to remove.
3. Convert a table to 1NF by removing multivalued attributes (assume example).
• Table before 1NF:

Student Subjects
John Math, Science

• After 1NF:

Student Subject
John Math
John Science

• Only atomic values per cell.

4. Provide a step-by-step method to convert a table into 1NF.
• Identify multivalued/composite attributes.
• Split them into separate rows.
• Ensure each field has atomic values.
• Ensure unique column names.
• Resulting table has no multivalued attributes.
5. Explain how partial dependencies are eliminated in 2NF.
• Identify composite primary key.
• Detect attributes dependent only on part of the key.
• Move those attributes to a new table.
• Retain foreign key relationship.
• Resulting tables have full functional dependency.
6. Analyze the benefits of achieving 2NF in database design.
• Eliminates partial dependencies.
• Reduces redundancy.
• Improves data integrity.
• Facilitates consistent updates.
• Enhances query performance.
7. Discuss scenarios where a table may violate 2NF.
• When non-key attribute depends only on part of composite key.
• Example: Course table with student_id and course_id.
 • If course_name depends only on course_id, it violates 2NF.
• Requires splitting the table.
8. Apply normalization to a given table to eliminate redundancy and ensure
consistency.
• Identify candidate keys.
• Apply 1NF: Remove multivalued attributes.
• Apply 2NF: Remove partial dependencies.
• Apply 3NF: Remove transitive dependencies.
• Resulting schema is clean and consistent.
9. Analyze the trade-offs between normalization and denormalization in database
design.
• Normalization: Reduces redundancy, ensures consistency, but may slow down joins.
• Denormalization: Combines tables for faster reads, but may introduce redundancy.
• Trade-off between query performance and update efficiency.
• Use case dependent.
10. Discuss the role of normalization in improving query performance with examples.
• Reduces redundant data scanned.
• Smaller tables = faster queries.
• E.g., separate customer and orders table reduces joins.
• Clear schema = optimized indexing and joins.
11. Define functional dependency. Provide an example to illustrate its concept.
• One attribute determines another.
• X → Y means Y is dependent on X.
• Example: roll_no → name.
• Ensures consistency and is key to normalization.

Module-6
2-Marks Questions (4 Points Each)

182.Define a transaction in a database system.

• A transaction is a sequence of operations.

• It performs a logical unit of work (e.g., transfer money).
• Ensures database consistency.
• Follows ACID properties.

183.What are the key characteristics of a transaction?

• Atomicity: All or nothing.

• Consistency: Valid state transition.
• Isolation: No interference.
• Durability: Changes are permanent.
184.Explain the importance of the "Committed" state in transaction management.

• Indicates successful transaction.

• Changes are made permanent.
• Cannot be rolled back.
• Ensures durability.

185.What is the role of the "Aborted" state in the transaction lifecycle?

• Indicates rollback after failure.

• Database returns to original state.
• Prevents inconsistency.
• Triggered after "failed" state.

186.differentiate between the "Failed" and "Partially Committed" states of a transaction.

• Failed: Error occurred, transaction can't proceed.

• Partially Committed: All operations done, awaiting final commit.
• Failed state goes to aborted.
• Partially committed can proceed to committed.

187.What happens in the "Active" state of a transaction?

• Transaction is executing.
• Performing read/write operations.
• Not yet committed or aborted.
• Normal working state.

188.Define serial execution of transactions.

• Transactions execute one after another.

• No interleaving.
• Maintains consistency.
• Reduces concurrency.

189. Difference between "Failed" and "Partially Committed" states of a transaction:

Aspect Failed State Partially Committed State

A transaction enters
this state when it A transaction reaches this state after executing its
Definition cannot complete final statement but before all changes are
successfully due to an permanently saved.
error.
 Caused by system
Indicates the transaction is almost done, pending
Reason crash, deadlock, or
final commit.
failed checks.
Transaction will be Transaction will either be committed or go to
Outcome
rolled back (undone). failed state if an error occurs at the end.
Changes are not yet durable; they will be after
Durability No changes are saved.
commit.

190. What happens in the "Active" state of a transaction? (4 points answer)

• The transaction is in progress and executing its operations.

• It performs read and write operations on the database.
• No changes are permanent yet.
• The transaction can either proceed to the next state (partially committed) or fail if an
error occurs.

191.Why is the transaction state diagram important in database systems?

• Visualizes transaction lifecycle.

• Helps understand transitions.
• Aids in debugging and recovery.
• Ensures proper ACID compliance.

192.Explain how a transaction transitions from the "Failed" state to the "Aborted" state.

• Upon detecting failure.

• Rolls back all operations.
• Moves to aborted state.
• Ensures data remains consistent.

193.Provide an example of a transaction transitioning through all its states.

• Active: Read/Write.
• Partially Committed: All steps done.
• Committed: Final commit.
• If error: Failed → Aborted.
• E.g., Fund transfer.

5-Marks Questions (10 Points Each)

194. What does ACID stand for in transaction management? Explain each
property briefly. (5 marks)

ACID is an acronym that defines key properties for reliable transaction processing in databases:
 1. Atomicity
a. Ensures a transaction is treated as a single unit.
b. Either all its operations are executed or none.
c. If a failure occurs, the system rolls back to its previous state.
2. Consistency
a. Ensures the database moves from one valid state to another.
b. All integrity constraints are preserved after the transaction.
3. Isolation
a. Ensures concurrent execution of transactions does not affect individual
transaction outcomes.
b. Transactions appear as though executed serially.
4. Durability
a. Once a transaction is committed, its changes are permanent.
b. Even system failures cannot undo the results of a committed transaction.
5. Importance
a. ACID guarantees the correctness, integrity, and reliability of the database.

195. How does a precedence graph help in checking conflict serializability?

Discuss with an example. (5 marks)

1. Definition:
a. A precedence graph (or serializability graph) helps determine if a schedule is
conflict-serializable.
2. Construction:
a. Nodes: Each transaction in the schedule.
b. Edges: From Ti to Tj if an operation in Ti conflicts with and precedes an
operation in Tj.
3. Conflict Types:
a. Read-Write (RW)
b. Write-Read (WR)
c. Write-Write (WW)
4. Serializability Check:
a. If the graph has no cycle, the schedule is conflict-serializable.
b. If there is a cycle, the schedule is not conflict-serializable.
5. Example:

Schedule:

T1: R(A) W(A)

T2: R(A) W(A)

Conflicts:

a. T1 W(A) before T2 R(A) → T1 → T2

b. T1 W(A) before T2 W(A) → T1 → T2
Graph: T1 → T2 (no cycle) → conflict-serializable

6. Use:
a. Used in concurrency control to validate transaction schedules.

196. Differentiate between shared and exclusive locks with examples. (5

marks)

Aspect Shared Lock (S-lock) Exclusive Lock (X-lock)

Allows reading and writing of a
Purpose Allows reading of a data item
data item
Multiple transactions can hold Only one transaction can hold an
Concurrency
shared locks concurrently exclusive lock
Compatible with other shared
Compatibility Not compatible with any other lock
locks
Can be upgraded to exclusive if
Lock Upgrade No upgrade required
needed
T1 acquires S-lock on item A to T2 acquires X-lock on item A to
Example Use
read its value update its value
Conflict High, since only one transaction
Rare, unless one tries to write
Possibility can write at a time
When modification or deletion is
Use Case When no modification is needed
intended
Deadlock Risk Lower than X-locks Higher due to blocking
Released after write or at
Lock Release Released after read
commit/abort
Implements concurrency without Ensures data integrity during
DBMS Usage
inconsistency updates

197. Explain the concept of two-phase locking protocol. Why is it important?

(5 marks)

1. Definition:
a. Two-Phase Locking (2PL) is a concurrency control method to ensure
serializability.
2. Phases:
a. Growing Phase: Transaction can acquire locks but not release them.
b. Shrinking Phase: Transaction can release locks but not acquire any new ones.
3. Locking Rules:
a. All locks must be acquired before any are released.
4. Importance:
a. Guarantees conflict-serializability of schedules.
b. Prevents lost updates and uncommitted data access.
5. Strict 2PL:
 a. All exclusive locks held until the transaction commits or aborts.
b. Used for recoverability and ensuring atomicity.
6. Drawbacks:
a. Can lead to deadlocks due to waiting for locks.
b. Requires deadlock detection or prevention mechanisms.
7. Example:
a. T1 locks A, then B (growing), performs actions, then unlocks A, B (shrinking).
8. Role in DBMS:
a. Widely used for maintaining concurrency control and consistency.

198. Discuss any one method for deadlock detection in database systems. (5
marks)

Wait-for Graph (WFG) Method:

1. Definition:
a. A directed graph where each node represents a transaction.
2. Edge Representation:
a. An edge from Ti → Tj indicates that Ti is waiting for a lock held by Tj.
3. Deadlock Detection Rule:
a. If the graph contains a cycle, a deadlock exists.
4. Construction Steps:
a. Track all transactions and their lock requests.
b. Add edges as transactions wait.
5. Example:
a. T1 waits for T2, and T2 waits for T1 → cycle → deadlock detected.
6. Detection Frequency:
a. Periodically or on lock request.
7. Resolution:
a. Abort one transaction in the cycle to break the deadlock.
8. Advantages:
a. Detects actual deadlocks instead of preventing all waits.
9. Disadvantages:
a. Overhead of graph maintenance and cycle detection.
10. Use in DBMS:
• Common in systems using lock-based concurrency control.

199. Classify the types of failures that occur in database systems. Provide
examples for each. (5 marks)

1. Transaction Failure
a. A single transaction fails due to logical or input errors.
b. Example: Division by zero in a transaction.
2. System Crash
a. The entire DBMS crashes due to power failure, OS crash, etc.
 b. Example: Sudden reboot while transactions are running.
3. Disk Failure
a. Physical damage to the disk where data is stored.
b. Example: Hard disk crash leads to loss of database files.
4. Media Failure
a. Irrecoverable storage medium damage.
b. Example: Fire damages the storage device permanently.
5. Application Software Failure
a. Errors or bugs in the application using the DB.
b. Example: App crash during transaction processing.
6. Operating System Failure
a. OS crash impacts database operation.
b. Example: Kernel panic causes DBMS to stop.
7. Concurrency-Related Failures
a. Conflicts due to improper synchronization.
b. Example: Lost updates or dirty reads.
8. Network Failures
a. Interruptions in communication affect distributed DBs.
b. Example: Connection timeout during transaction commit.
9. Buffer Failure
a. Issues in memory buffers due to corruption or overflow.
b. Example: Corrupted cache leads to wrong data writes.
10. Intentional Kill
• A transaction is aborted by the user or admin.
• Example: Admin kills a long-running unresponsive transaction.

200. What is log-based recovery? Explain its importance in database

recovery. (5 marks)

1. Definition:
a. Log-based recovery uses a transaction log that records all DB changes.
2. Transaction Log:
a. Contains <Transaction ID, Data Item, Old Value, New Value>
3. Types of Logs:
a. Undo log: Restores old values
b. Redo log: Reapplies new values
4. Importance:
a. Ensures atomicity and durability of transactions.
b. Helps in recovering committed transactions after crash.
5. Working:
a. On crash, DB checks log:
i. Undo uncommitted transactions.
ii. Redo committed ones.
6. Write-Ahead Logging (WAL):
 a. Log is written before actual DB changes are made.
7. Checkpoints:
a. Periodic markers to minimize recovery time.
8. Recovery Example:
a. Crash after T1 commits: Log helps redo T1.
b. Crash before T2 commits: Log helps undo T2.
9. Advantages:
a. Fast, reliable, and widely used.
10. Used In:
• All modern DBMSs like MySQL, Oracle, PostgreSQL.

201. Discuss any one method for deadlock detection in database systems.
(Duplicate of Q198)

Already answered above using Wait-for Graph (WFG) method.

Here are detailed answers from Q202 to Q217, formatted for 5-mark (~10 points) and 10-mark
(~15–16 points) expectations, based on your Module-6 notes.

202. Classify the types of failures that occur in database systems. Provide
examples for each. (Same as Q199 — 5 marks)

See Q199 answer above.

203. What is log-based recovery? Explain its importance in database

recovery. (Same as Q200 — 5 marks)

See Q200 answer above.

204. Explain the role of checkpoints in recovery. How do they enhance the
recovery process? (5 marks)

1. Definition:
a. A checkpoint is a point in the log where the DBMS writes all in-memory updates
to disk.
2. Purpose:
a. Reduces the amount of log that needs to be scanned during recovery.
3. Types of Checkpoints:
a. Fuzzy Checkpoints (allow ongoing transactions)
b. Sharp Checkpoints (transactions paused)
4. How They Work:
 a. Periodically, the system flushes all modified pages and logs “start checkpoint”
and “end checkpoint”.
5. Recovery Benefit:
a. Speeds up recovery by starting log analysis from the last checkpoint.
6. Minimizes Overhead:
a. Avoids scanning the entire log.
7. Used With WAL:
a. Works with write-ahead logging to ensure consistency.
8. Example:
a. If crash occurs after checkpoint, only logs after it are processed.
9. Frequency:
a. Set based on system workload.
10. Enhancement:
• Ensures efficient, faster, and reliable recovery process.

205. Describe the concept of shadow paging and its use in database recovery.
(5 marks)

1. Definition:
a. Shadow paging is a recovery method that avoids logging by maintaining
shadow pages.
2. Structure:
a. Uses a page table to map logical to physical pages.
3. Working:
a. On a write, changes go to a new page.
b. Old (shadow) page is untouched.
4. Commit:
a. On successful commit, page table is updated to new pages.
b. Old shadow page becomes obsolete.
5. Crash Recovery:
a. If crash occurs, use shadow page table to restore.
6. No Undo/Redo Needed:
a. Ensures atomicity without log.
7. Advantages:
a. Simpler recovery, no logs needed.
8. Drawbacks:
a. High overhead due to copying.
b. Poor performance in high-write systems.
9. Use Case:
a. Suitable for small or read-heavy systems.
10. Example:
• Page 5 updated → new page allocated → commit → update page table.
206. Define shared and exclusive locks. Explain their roles in managing
database concurrency. (10 marks)

1. Locking in DBMS: Prevents undesirable effects in concurrent transactions.

2. Shared Lock (S-lock):
a. Allows reading.
b. Multiple transactions can hold it concurrently.
c. Ensures read consistency.
d. Used when data is not modified.
3. Exclusive Lock (X-lock):
a. Allows both reading and writing.
b. Only one transaction can hold it at a time.
c. Prevents other reads/writes.
4. Compatibility Matrix:

Lock Type Shared Exclusive

Shared Yes No
Exclusive No No

5. Examples:
a. T1 reads A → S-lock.
b. T2 wants to write A → must wait if T1 holds S-lock.
6. Role in Concurrency:
a. Prevents dirty reads, lost updates.
b. Manages access in multi-user DB environments.
7. Lock Upgrade:
a. S-lock can be upgraded to X-lock.
8. Deadlock Possibility:
a. If improper lock acquisition order.
9. Unlocking:
a. Done at commit or rollback.
10. Integrated with 2PL:
• Works under two-phase locking for serializability.
11. Implementation in DBMS:
• SQL-based DBs use SELECT ... FOR SHARE or FOR UPDATE.
12. Read-Write Conflict Avoidance:
• Locks delay conflicting operations.
13. Blocking and Waiting:
• Managed via lock queues.
14. Ensures Isolation (ACID):
• Critical for transaction isolation level.
15. Performance Balance:
• Careful usage needed to avoid bottlenecks.
207. Discuss the concept of two-phase locking (2PL) and its impact on
ensuring serializability. (10 marks)

1. Definition:
a. A concurrency control protocol that ensures serializability.
2. Phases:
a. Growing Phase: Transaction acquires all required locks.
b. Shrinking Phase: Transaction releases locks; no new locks acquired.
3. Strict 2PL:
a. Locks held till commit/abort → ensures recoverability.
4. Role in Serializability:
a. Enforces conflict-serializability by avoiding conflicting interleavings.
5. Why It Works:
a. Transactions must finish locking before unlocking → linear order.
6. Example:
a. T1: Lock A, Lock B → operate → Unlock A, Unlock B.
7. Cascading Rollback Avoidance:
a. Strict 2PL avoids reading uncommitted data.
8. Drawbacks:
a. May cause deadlocks.
9. Deadlock Handling:
a. Requires timeout, wait-die, or wound-wait strategies.
10. Variants:
• Conservative 2PL (locks acquired before execution begins).
• Rigorous 2PL (all locks released at commit).
11. Locks Used:
• Shared and Exclusive.
12. Enforces Isolation:
• Vital for achieving isolation in ACID.
13. Comparison to Other Protocols:
• Simpler than timestamp ordering but can block more.
14. Widespread Use:
• Implemented in most commercial DBMS.
15. Visualization:
• Helps analyze using schedule timelines.

208. Describe any two deadlock prevention techniques used in database

concurrency control. (10 marks)

1. Wait-Die Scheme

• Old transaction waits for younger one if lock not available.

• Younger transaction dies (aborts) if it requests a lock held by older one.
• Prevents circular waits → avoids deadlocks.
2. Wound-Wait Scheme

• Older transaction wounds (forces abort) younger one holding the lock.
• Younger transaction waits if older is holding lock.
• Ensures progress of older transactions.

Supporting Points for Both:

3. Based on Timestamps:
a. Each transaction assigned a timestamp at start.
4. Prevents Cycles:
a. No circular waits possible → deadlocks avoided.
5. Fairness:
a. Older transactions prioritized.
6. Aborts Handled Gracefully:
a. Aborted transactions can restart with same timestamp.
7. Drawbacks:
a. Frequent aborts may reduce performance.
8. Used In:
a. Lock-based protocols, 2PL.
9. Example:
a. T1 (older) requests X on A held by T2 (younger):
i. Wait-Die: T1 waits.
ii. Wound-Wait: T2 aborted by T1.
10. Overhead:
• Timestamp maintenance and transaction restart management.
11. Simple Implementation:
• Suitable for systems avoiding complex detection.
12. Preemptive Strategy:
• Proactively avoids deadlocks rather than detecting.
13. Comparison to Detection:
• Prevention avoids overhead of wait-for graph checks.
14. Trade-offs:
• Reduces deadlocks at cost of transaction aborts.
15. Conclusion:
• Both are effective methods to manage concurrency safely.

209 & 214. What is log-based recovery? Discuss its role in ensuring atomicity
and durability. (10 marks — merge of Q209 and Q214)

1.Definition:
a. Log-based recovery uses a transaction log that records all DB changes.
2.Transaction Log:
b. Contains <Transaction ID, Data Item, Old Value, New Value>
3.Types of Logs:
 c. Undo log: Restores old values
d. Redo log: Reapplies new values
4.Importance:
e. Ensures atomicity and durability of transactions.
f. Helps in recovering committed transactions after crash.
5.Working:
g. On crash, DB checks log:
i. Undo uncommitted transactions.
ii. Redo committed ones.
6.Write-Ahead Logging (WAL):
h. Log is written before actual DB changes are made.
7.Checkpoints:
i. Periodic markers to minimize recovery time.
8.Recovery Example:
j. Crash after T1 commits: Log helps redo T1.
k. Crash before T2 commits: Log helps undo T2.
9.Advantages:
l. Fast, reliable, and widely used.
10.Used In:
• All modern DBMSs like MySQL, Oracle, PostgreSQL.
11. Ensures Atomicity:
• On failure, incomplete transactions can be undone via undo log.
12. Ensures Durability:
• Committed transaction logs used to redo after crash.
13. Crash Scenarios Handled:
• Power failure, system crash, transaction abort.
14. WAL Principle:
• Guarantees changes logged before actual DB change.
15. Atomic Commit Protocols:
• Integrates with protocols like two-phase commit in distributed DBs.

210. Discuss the concept of checkpoints in recovery. Explain their

importance with examples. (10 marks)

1. Definition:
a. A checkpoint is a point in the log where the DBMS writes all in-memory updates
to disk.

2. Purpose:

b. Reduces the amount of log that needs to be scanned during recovery.

3.Types of Checkpoints:
c. Fuzzy Checkpoints (allow ongoing transactions)
d. Sharp Checkpoints (transactions paused)
4.How They Work:
 e. Periodically, the system flushes all modified pages and logs “start checkpoint”
and “end checkpoint”.
5.Recovery Benefit:
f. Speeds up recovery by starting log analysis from the last checkpoint.
6.Minimizes Overhead:
g. Avoids scanning the entire log.
7.Used With WAL:
h. Works with write-ahead logging to ensure consistency.

8. Example:

i. If crash occurs after checkpoint, only logs after it are processed.

9. Frequency:
j. Set based on system workload.

10. Enhancement:

• Ensures efficient, faster, and reliable recovery process.

11. Minimal Redo Scope:
• Only process post-checkpoint logs for redo.
12. System Performance:
• Avoids long recovery delays.
13. Transaction States:
• Tracks active, committed, uncommitted transactions.
14. Multilevel Checkpoints:
• Used in distributed or large-scale systems.
15. Example Workflow:
• At checkpoint: T1 committed, T2 active.
• Crash → only T2 undone; T1 redo not needed

211 & 216. Define shadow paging. Compare its advantages and limitations
with log-based recovery. (10 marks)

1. Shadow Paging – Definition

• Recovery mechanism where changes are written to a new page instead of overwriting
existing data.
• Maintains two page tables: current and shadow.
• Shadow page table is not modified during transaction execution.

2. Working

• Any modified page is stored separately.

• At commit, the current page table replaces the shadow page table.
 • If crash happens before commit, shadow table provides original consistent state.

3. No Undo/Redo Required

• Atomicity is achieved without requiring logs.

4. Log-Free Operation

• Simpler implementation for systems with minimal concurrency.

Comparison with Log-Based Recovery

Feature Shadow Paging Log-Based Recovery

Logging Not required Mandatory (Undo/Redo logs)
Undo/Redo Not needed Essential
Write Overhead High (copies of pages made) Lower than shadow paging
Concurrency
Poor Better
Support
Crash Recovery
Fast if commit not completed Potentially slower (redo/undo)
Speed
Complexity Lower Higher due to log management
High due to multiple page Optimized with efficient
Disk Usage
versions logging
Suitability Small DB, low concurrency Large DB, high concurrency

Advantages of Shadow Paging

5. Simpler design
6. Atomicity ensured
7. No logs = lower logging overhead
8. Faster recovery in simple setups

Limitations

9. No support for fine-grained concurrency

10. Poor performance for frequent updates
11. Page copy overhead
12. Managing free page space is complex

Use Cases

13. Embedded databases

14. Read-heavy systems with low update rates
Conclusion

15. Shadow paging is simple and log-free but inefficient for large-scale DBs.
16. Log-based recovery remains the preferred choice in real-world systems.

212 & 217. Explain how recovery mechanisms like shadow paging and log-
based recovery ensure data consistency. (10 marks)

1. What is Data Consistency?

• Ensures that database constraints and transaction rules are not violated even during
failures.

2. Shadow Paging for Consistency

3. Changes are written to new pages → Original pages untouched

4. Crash during transaction → Rollback by using shadow page table
5. Commit → Update page table pointer atomically
6. Prevents partial writes → maintains integrity

3. Log-Based Recovery for Consistency

7. Uses logs to record all actions before executing (WAL rule)

8. Ensures atomicity by allowing undo of incomplete transactions
9. Ensures durability by redoing committed transactions
10. Logs maintain before-image and after-image

4. Undo/Redo Mechanisms

11. Undo restores DB to previous state (abort)

12. Redo reapplies committed changes after crash

5. Consistency with Checkpoints

13. Limits recovery to recent changes

14. Ensures clean state recovery with reduced log scan

6. ACID Compliance

15. Shadow paging and log-based recovery both support Atomicity and Durability,
preserving Consistency
16. These mechanisms guarantee database integrity even after system failures
213. Explain the types of failures in database systems. Provide examples for
each type. (10 marks)

1. Types of Failures:

1. Transaction Failure

• A transaction cannot complete due to logical error or internal checks.

• Example: Division by zero, constraint violation.

2. System Crash

• OS or DBMS crash causes in-memory data loss.

• Example: Power outage, software crash.

3. Media Failure

• Physical damage to the storage device.

• Example: Hard disk crash, sector corruption.

4. Application Failure

• Application bug or bad input causes abnormal termination.

• Example: Null pointer in client software.

5. Disk Failure

• Hardware-level fault; may lead to loss of part/all data.

• Example: Disk head crash.

6. Communication Failure

• Occurs in distributed systems due to network interruption.

• Example: Network cable unplugged during commit

7. Deadlocks or Timeouts

• Transactions wait indefinitely → system may abort one.

• Example: Two transactions waiting on each other’s lock.

8. Concurrency Anomalies

• Incorrect scheduling leads to inconsistencies.

• Example: Lost update or dirty read.

9. Natural Disasters
 • Rare but severe; floods or fires damaging data centers.

Recovery for Each

10. Transaction → Rollback

11. Crash → Log-based recovery
12. Media → Restore from backup
13. Application → Handle at app level
14. Disk → Replace hardware + restore backup