Unit – 1 Overview of Database Systems

1) What is database system?

Answer:
Database System
A database system is a collection of related data that is stored and managed in
an organized manner. It helps users to store, access, update, and manage large
amounts of data efficiently. Database systems are widely used in schools,
banks, hospitals, and businesses.
Components of a Database System
A database system includes the following components:
• Database – Collection of related data
• DBMS – Software to manage the database
• Users – End users, programmers, and database administrators
• Hardware – Computers and storage devices
• Application Programs – Programs used to access the database
Functions of a Database System
The main functions of a database system are:
• Stores large amounts of data in an organized manner
• Allows quick retrieval of required data
• Enables insertion, deletion, and updating of data
• Maintains data consistency and accuracy
• Provides data security by restricting unauthorized access
• Supports multiple users to access data at the same time
• Performs backup and recovery to prevent data loss
• Maintains data integrity by enforcing rules and constraints
Advantages of Database System
The advantages of a database system include:
 • Reduces duplication of data
• Improves data consistency and reliability
• Provides better data security and privacy
• Allows data sharing among multiple users and applications
• Saves time by providing fast access to data
• Reduces data maintenance cost
• Ensures data backup and easy recovery
• Improves decision-making by providing accurate information

2) Explain database vs file system?

Answer:
Before databases existed, data was stored using a file system (simple files like
text files).
Drawbacks of File System:

File System
Meaning Example
Problem

Employee details stored in

Same data is stored multiple
Data redundancy HR, payroll, and attendance
times
files

Data Different values for the same HR file shows salary = 20,000,
inconsistency data payroll shows 22,000

No security Anyone can access files Staff can open [Link]

Difficult to Requires long programs for Finding employees earning >

access data searching/updating 30,000 needs complex code

No concurrency Multiple users cannot safely Two users editing a file may
control access data corrupt it
File System
Meaning Example
Problem

Poor data No rules to ensure correct Age = −5, phone number

integrity data with letters

Advantages of Database System over File System:

DBMS Feature Meaning Example

Reduced data DBMS stores each piece of Employee details stored only
redundancy data only once in Employee table

Same data is maintained Salary update is reflected

Data consistency
everywhere automatically

Controls who can access Only manager can view

Data security
data salaries

Rules ensure correct data

Data integrity Age > 0, Salary > 0
values

Database changes do not Table structure change does

Data independence
affect applications not break application

Protects data from system Oracle recovers data after

Backup & recovery
failures crash

Concurrency Allows multiple users at ATM and online banking

control the same time systems

3) Explain about Database users?

Answer:
Types of Database Users
1. Database Administrator (DBA)
The Database Administrator is responsible for the overall management of the
database system.
Functions:
• Installation and configuration of the database
• Managing user access and security
• Backup and recovery of data
• Monitoring and tuning database performance
2. Database Designers
Database designers define the structure of the database.
Functions:
• Designing database schema
• Defining tables, relationships, and constraints
• Ensuring data consistency and integrity
3. Application Programmers
Application programmers develop software that interacts with the database.
Functions:
• Writing SQL queries and programs
• Developing database-driven applications
• Implementing business logic
4. End Users
End users access the database to perform daily operations.
Types of End Users:
• Naive users: Use predefined applications (e.g., ATM users)
• Casual users: Access data occasionally using queries
• Sophisticated users: Use advanced database tools
• Standalone users: Maintain personal databases
4) Discuss the advantages of database systems.
Answer:
Advantages of Database Systems
1. Reduced Data Redundancy
The same data is not stored repeatedly in different places.
Example:
In a college database, student details are stored once and used by fees, exams,
and attendance systems.

Advantages:
• Saves storage space
• Avoids confusion
2. Improved Data Consistency
The same data has the same value everywhere in the database.

Example:
If a student’s phone number is updated, it changes in all related tables.

Advantages:

• Accurate and reliable data

3. Data Security
Only authorized users can access or modify data.

Example:

• Teachers can view marks

• Only admin can change marks

Advantages:

• Protects sensitive information

4. Data Integrity
Rules ensure correct and valid data is stored.

Example:

• Age must be greater than 0

• Marks must be between 0 and 100

Advantages:

• Maintains data accuracy

5. Backup and Recovery

DBMS automatically saves copies of data and recovers data after failure.

Example:
If a system crashes, Oracle restores data from backup.

Advantages:

• Prevents data loss

6. Concurrency Control
DBMS manages multiple users accessing data simultaneously without conflict.

Example:
Two people booking tickets at the same time.

Advantages:

• Prevents data corruption

7. Data Integration
All data is stored in a centralized database.
Example:
Employee details, salary, and attendance stored in one system.

Advantages:

• Easy management and reporting

5) Explain about Database Applications.

Answer:

A database application is a software application that uses a database to store,

retrieve, update, and manage data.

Database application = Application program + Database

Applications:

1. Banking

For customer information, accounts, loans and banking transactions.

2. Universities

For student information, course registrations, results and grades.

3. Credit Card Transactions

For purchases on credit cards and generation of monthly statements.

4. Telecommunication

For keeping records of calls made, generating monthly bills, prepaids and about
communication networks.

5. Human Resources

For information about employees, salaries and payroll taxes.

6. E-Commerce Applications

Product details, Customer information, orders and payments.

7. Healthcare Applications

Patient details, Medical history, Lab reports and Billing

8. Social Media Apps

User profiles, Posts and comments, Likes and messages

6) Explain about data models.

Answer:

Data Model: A data model is used to describe the structure of a database.

Defines:

What data is stored?

How data is stored

How data is related

Rules and constraints on data

Basic Building Blocks:

1) Entities

2) Attributes

3) Relationships

4) Constraints

1) Entity: Something we store information about.

Example: Student, Teacher, Employee

 2) Attribute: Details of an Entity

Example: sid, sname, age, marks

3) Relationship: An association between two or more entities

➢ 1:1 : One entity is related to only one entity, and vice versa.

Example

• Person ↔ Passport

• One person has one passport

• One passport belongs to one person

➢ 1 : M: One entity is related to many entities.

Example

• Teacher → Students

• One teacher teaches many students

• Each student is taught by one teacher

➢ M : 1: Many entities are related to one entity.

Example

• Employees → Department

• Many employees work in one department

• Each employee belongs to one department

➢ M : M: Many entities are related to many entities.

Example

• Students ↔ Courses
 • One student enrolls in many courses

• One course has many students

7) Explain concepts of schema in dbms

Answer:
Concept of Schema in DBMS
A schema is the logical structure or blueprint of a database.

Definition of Schema
A schema is a formal description of the database structure, including tables,
attributes (columns), data types, relationships, and constraints.

It does not contain actual data, but it defines how data is stored.

Types of Schema in DBMS

DBMS mainly follows a three-schema architecture, which includes:

1. Physical Schema

The physical schema describes how data is actually stored in the system.

It includes:

• Storage details
• Indexing methods
• File organization

2. Logical Schema

The logical schema describes the overall structure of the database.

It includes:

• Tables
• Columns
• Data types
• Relationships
• Constraints
3. View Schema (External Schema)

The view schema defines how different users see the data.

It provides:

• Customized views of the database

• Data security by restricting access

Conclusion
Schema is an essential concept in DBMS that defines the structure and
organization of data.

8) What is Instance and Data Independence in dbms.

Answer:

1. Instance in DBMS

Definition

An instance refers to the actual data stored in the database at a particular

moment in time. It represents the current state of the database.

In simple words, schema is the structure of the database, while instance is the
data inside that structure.

Example
If a table Student has columns: Roll No, Name, and Marks,
then the actual records stored in the table (like 101, Ravi, 85) are called the
instance.

2. Data Independence in DBMS

Definition

Data independence means the ability to modify the database structure without
affecting data.

It allows changes in one level of the database without disturbing other levels.

Types of Data Independence

1. Logical Data Independence

It is the ability to change the logical schema without affecting the external
schema or application programs.

Example:
Adding a new column to a table without changing existing programs.

2. Physical Data Independence

It is the ability to change the physical storage of data without affecting the
logical schema.

Example:
Changing storage devices without changing the table structure.

Conclusion

An instance represents the actual data stored in a database at a specific time,

while data independence allows changes in database structure without
affecting other levels.

9) Explain Three tier schema architecture for data independence in dbms.

Answer:

Three-Tier Schema Architecture for Data Independence in DBMS

The Three-Tier Schema Architecture, helps in achieving data independence,

meaning changes at one level do not affect other levels.

The Three Levels of Architecture

1. External Level (View Level)

The external level is the highest level of the database architecture. It describes
how different users view the database.

Features:

• It Hides unnecessary details from users.

• Enhances data security
Each user may have a different external view.

2. Logical Level (Conceptual Level)

The conceptual level describes the overall logical structure of the database.

Includes:

• Tables
• Attributes (columns)
• Relationships
• Constraints

It defines what data is stored and how it is organized.

3. Physical Level

The physical level describes how data is physically stored in the system.

Includes:

• File organization
• Storage methods
• Indexing techniques

It focuses on how the data is stored in memory or disk.

Data Independence in Three-Tier Architecture

The separation of these three levels provides two types of data independence:

1. Logical Data Independence

Changes in the logical level do not affect the external level.

Example:
Adding a new attribute in a table without changing user views.

2. Physical Data Independence

Changes in the physical level do not affect the logical level.

Example:
Changing storage devices without affecting the table structure.
10) Explain database system structure and environment

Answer:

Database System Structure and Environment

1. Database System Structure

The database system mainly consists of the following components:

Database

A database is an organized collection of data stored electronically. It contains

tables, records, relationships, and constraints.

Database Management System (DBMS)

A DBMS is software that allows users to create, manage, manipulate and

retrieve the database.

Query Processor

The query processor interprets and executes SQL queries. It converts user
queries into low-level instructions for the database.

Storage Manager

The storage manager handles the physical storage of data in files and manages
indexing and buffering.

Database Users

Different users interact with the database such as:

• Database Administrator (DBA)

• Application programmers
• End users

2. Database System Environment

The database environment includes all the components required for the
database system to function properly.

Hardware
Includes computers, storage devices, and networking equipment where the
database is stored and accessed.

Software

Includes the DBMS software, operating system, and application programs.

Data

The actual stored information in the database.

Procedures

Rules and instructions for using and maintaining the database system.

People

Users who manage and use the database system.

11) Explain the Centralized and client server architecture for the database.

Answer:

Centralized and Client–Server Architecture for Database

1. Centralized Database Architecture

Definition

In a centralized database architecture, the entire database system is located at

a single central location (server), and all users access it from different
terminals.

Structure

• One central computer stores the database

• Users connect to the central system through terminals
• All processing is done at the central server

Advantages

• Easy to maintain and manage

• Strong data security
• Simple backup and recovery
Disadvantages

• System failure affects all users

• Heavy load on the central server
• Limited scalability

2. Client–Server Database Architecture

Definition

In client–server architecture, the database is stored on a server, and multiple

clients (users) connect to it through a network. The work is divided between
clients and server.

Structure

• Client: Sends requests to the server

• Server: Processes requests and manages the database

Types of Client – Server Architecture

1. Two-Tier Architecture

• Client directly communicates with the database server

• Used in small-scale applications

2. Three-Tier Architecture

• Client → Application Server → Database Server

• Commonly used in web applications

Advantages

• Better performance
• Supports multiple users
• Easy scalability
• Reduced server workload

Disadvantages

• Network dependency
• Higher setup cost
• Requires proper maintenance
 UNIT – II – RELATIONAL MODEL
1) Explain the concept of Relational Model
Answer:
Introduction to Relational Model
Introduction
The Relational Model is a widely used data model in Database Management
Systems (DBMS). It was proposed by E. F. Codd in 1970. In this model, data is
organized in the form of tables, also called relations.
In the relational model:
 Data is stored in tables (relations)
 Each table consists of rows (tuples)
 Each row contains columns (attributes)

Important Terminologies

1. Relation

A relation is a table with rows and columns.

2. Tuple

A tuple is a single row in a table.

3. Attribute

An attribute is a column in a table.

4. Domain

A domain is the set of valid values that an attribute can have.

Keys in Relational Model

1. Primary Key
A primary key uniquely identifies each record in a table.
2. Foreign Key
A foreign key is an attribute that links one table to another.
Characteristics of Relational Model
 Data is organized in tabular form
 Each table has a unique name
 No duplicate rows

Conclusion

The Relational Model organizes data into tables with rows and columns. It
provides a simple and efficient way to manage data.

2) Explain codd's rules.

Answer:

Codd’s Rules in DBMS

Introduction

Codd’s Rules were proposed by Dr. E. F. Codd in 1985 to define what makes a
database management system a true Relational Database Management
System (RDBMS). There are 13 rules (Rule 0 to Rule 12) that describe the
standards an RDBMS should follow.

Rule 0: Foundation Rule

A system must be able to manage the database entirely through its relational
capabilities.

Rule 1: Information Rule

All data must be stored in the form of tables (relations), consisting of rows and
columns.

Rule 2: Guaranteed Access Rule

Each data value must be accessible using a combination of table name, primary
key, and column name.

Rule 3: Systematic Treatment of Null Values

The system must support NULL values to represent missing or unknown data.

Rule 4: Active Online Catalog

The database structure (metadata) must be stored as tables and accessible like
regular data.

Rule 5: Comprehensive Data Sub-language Rule

The system must support a relational language (such as SQL) for data
definition, manipulation, and control.

Rule 6: View Updating Rule

All views that are theoretically updatable must be updatable by the system.

Rule 7: High-Level Insert, Update, and Delete

The system must support set-level operations for insert, update, and delete,
not just single records.

Rule 8: Physical Data Independence

Changes in physical storage should not affect application programs.

Rule 9: Logical Data Independence

Changes in logical structure should not affect application programs.

Rule 10: Integrity Independence

Integrity constraints must be stored in the database and not in application

programs.

Rule 11: Distribution Independence

The system should work properly whether the database is distributed or

centralized.

Rule 12: Non-Subversion Rule

Low-level access methods should not bypass security and integrity rules.

Conclusion
Codd’s Rules define the standards of a true relational database system.

3) Explain the concepts of domain, attribute, tuple and relation in dbms.

Answer:

Concepts of Domain, Attribute, Tuple, and Relation in DBMS

Introduction

In the Relational Model of a Database Management System (DBMS), data is

organized in the form of tables. To understand this model properly, it is
important to know the basic terms: Domain, Attribute, Tuple, and Relation.

1. Domain

Definition

A domain is the set of valid values that an attribute can have.

In simple words, it defines the type and range of values allowed in a column.

Example

If an attribute Age is defined as an integer between 18 and 60, then 18–60 is

the domain of that attribute.

2. Attribute

Definition

An attribute is a column in a table. It represents a property or characteristic of

an entity.

Example

In a student table, attributes can be:

 Roll No
 Name
 Age
 Course

Each attribute has a specific domain.

3. Tuple

Definition

A tuple is a single row in a table. It represents one record in the relation.

Example

In the Student table,

(101, Ravi, 20, BCA)
is a tuple.

Each tuple contains values for all attributes.

4. Relation

Definition

A relation is a table in a relational database. It consists of attributes (columns)

and tuples (rows).

Example

A Student table containing multiple records is called a relation.

Conclusion

In DBMS, a domain defines valid values, an attribute represents a column, a

tuple represents a row, and a relation represents the complete table. These
concepts form the foundation of the relational database model.

4) Explain the constraints (domain, key, integrity) and their importance in

dbms.

Answer:

Constraints in DBMS and Their Importance

Introduction

In a Database Management System (DBMS), constraints are rules applied to

data in order to maintain accuracy, consistency, and integrity.

The main types of constraints are:

  Domain Constraints
 Key Constraints
 Integrity Constraints

1. Domain Constraint

Definition

A domain constraint ensures that the values stored in a column belong to a

specific domain (valid range or data type).

Example

If the attribute Age is defined as an integer between 18 and 60, then entering
70 or a text value will not be allowed.

Importance

 Ensures valid data entry

 Prevents incorrect data types
 Maintains data accuracy

2. Key Constraint

Definition

A key constraint ensures that each record in a table is uniquely identified.

Types of Keys

 Primary Key: Uniquely identifies each record and cannot be NULL.

 Foreign Key: Links one table with another.

Example

In a Student table, Roll Number can be a primary key because it uniquely

identifies each student.

Importance

 Prevents duplicate records

 Maintains uniqueness
 Establishes relationships between tables
3. Integrity Constraints

Integrity constraints ensure correctness and consistency of data in the

database.

Types of Integrity Constraints

1. Entity Integrity

Ensures that the primary key cannot be NULL.

2. Referential Integrity

Ensures that a foreign key value must match a primary key value in another
table.

Example

If a Student table has a foreign key (department) referencing a Department

table, the department must exist in the Department table.

Importance

 Maintains relationship consistency

 Prevents invalid references
 Protects database reliability

5) Explain the concept of keys (super key, candidate key, surrogate key,
primary key, foreign key) in dbms.

Answer:

Concept of Keys in DBMS

Introduction

In a Database Management System (DBMS), keys are attributes or sets of

attributes used to uniquely identify records in a table and establish
relationships between tables.

1. Super Key

Definition
A super key is a set of one or more attributes that uniquely identifies a record
in a table.

Example

In a Student table with attributes

(RollNo, Name, Email):

Roll No Name Email

1 abc abc@[Link]
2 def def@[Link]

Super Keys: {Roll No, Email, Roll No + Name, Email + Name}

All the above combinations can uniquely identify a record, so they are super
keys.

2. Candidate Key
Definition

A candidate key is a minimal super key. But combination of unnecessary

attributes or fields cannot be consider as candidate keys.

Example

From the above table, if Roll No and Email both uniquely identify a student,
then both are candidate keys. And we did not consider the combination of
unnecessary field “Name” here as a candidate key. There can be multiple
candidate keys in a table.

Candidate Keys: {Roll No, Email}

3. Surrogate Key
Definition

A surrogate key is an artificially created key added to a table to uniquely

identify records.

It is usually auto-generated and has no real-world meaning.

Example

An auto-incremented ID number assigned to each record.

4. Primary Key
Definition

A primary key is one of the candidate keys chosen to uniquely identify records
in a table. There should be only one primary key in a table.

Characteristics

 Must be unique
 Cannot contain NULL values

Example

From the above “Student” table Roll No is selected as the primary key in the
Student table.

5. Foreign Key
Definition

A foreign key is an attribute in one table that refers to the primary key of
another table.

Purpose

It creates a relationship between two tables.

Example

Student
Roll No Name Email Department Id
1 abc abc@[Link] d1
2 def def@[Link] d2
Department
Department Id Name
d1 Science
d2 Arts

If a Department Id in the Student table refers to Department Id in the

Department table, then it is a foreign key.

6) Explain Relational Algebra and Relational Calculus.

Answer:

Relational Algebra and Relational Calculus

Introduction

Relational Algebra and Relational Calculus are two formal query languages
used in the Relational Model of DBMS.

1. Relational Algebra
Definition

Relational Algebra is a procedural query language. It consists of a set of

operations that take one or two relations as input and produce a new relation
as output.

Basic Operations in Relational Algebra

1. Selection (σ) – Selects rows based on a condition.

2. Projection (π) – Selects specific columns from a table.
3. Union (∪) – Combines tuples from two relations.
4. Set Difference (−) – Returns tuples in one relation but not in another.
5. Cartesian product (×) – Combines tuples of two relations.
6. Rename (ρ) – Renames relation or attributes.

Example

To select students with marks greater than 80 :

σ (Marks > 80) (Student)
2. Relational Calculus
Definition

Relational Calculus is a non-procedural query language. It is based on

mathematical logic.

Types of Relational Calculus

1. Tuple Relational Calculus (TRC)

Uses tuple variables to specify conditions.

Example:
{ t | t ∈ Student AND [Link] > 80 }

2. Domain Relational Calculus (DRC)

Uses domain variables (attribute values).

Example:
{ <Name> | ∃ RollNo, Marks (Student(RollNo, Name, Marks) AND Marks > 80) }

Here,

∈ : Belongs to

∃ : There exists

| : Such that

^ : AND

V : OR

7) Explain the purpose of normalization or schema refinement.

Answer:

Purpose of Normalization (Schema Refinement) in DBMS

Introduction

Normalization, also known as schema refinement, is the process of organizing

data in a database to reduce data redundancy and improve data integrity. It
involves dividing large tables into smaller tables and defining relationships
between them.

1. Reduce Data Redundancy

Normalization removes duplicate data from tables.

Example

Instead of storing department details repeatedly for every student,

department information is stored in a separate table.

Purpose:

 Saves storage space

 Avoids unnecessary repetition

2. Prevent Data Anomalies

Normalization helps avoid different types of anomalies:

1. Insertion Anomaly

Difficulty in adding new data without other related data.

2. Deletion Anomaly

Loss of important data when deleting a record.

3. Update Anomaly

Inconsistency caused by updating data in multiple places.

3. Improve Data Integrity

Normalization ensures that relationships between tables are properly

maintained using primary keys and foreign keys.
Conclusion

Normalization (schema refinement) is an important process in DBMS that

improves database design by reducing redundancy, preventing anomalies, and
ensuring data integrity.

8) Explain the concept of Functional Dependency

Answer:

Functional Dependency in DBMS

Introduction

Functional Dependency (FD) is an important concept in the relational database

model. It describes the relationship between attributes in a table. Functional
dependency is mainly used in the process of normalization to remove
redundancy and improve database design.

Definition

A functional dependency occurs when one attribute (primary key) determines

another attribute (non – primary key) in a relation.

It is denoted as:

A→B

This means attribute A determines B.

Example

Consider a Student table:

RollNo Name Department

101 Ravi CSE

102 Anu ECE

Here:

RollNo → Name, Department

This means RollNo uniquely determines Name and Department.
 UNIT – III – ENTITY RELATIONSHIP MODEL
1) Explain the Entity Relationship (ER) Model in detail.
Answer:

Introduction

The Entity Relationship (ER) Model is a high-level conceptual data model used
in database design. The ER model is a graphical representation of real-world
objects and their relationships using an ER diagram.

Components of ER Model

1. Entity

An entity is a real-world object or concept that can be uniquely identified.

Example: Student, Employee, Course

2. Attribute

An attribute is a property or characteristic of an entity.

Example: Student → Name, Roll Number

3. Relationship

A relationship represents association between entities.

Example: Student enrolls in Course

ER Diagram
An ER diagram is a visual representation of entities, attributes, and
relationships using symbols like:

 Rectangle → Entity
 Ellipse → Attribute
 Diamond → Relationship

Advantages

 Easy to understand and design

 Helps in database planning
 Reduces redundancy
 Improves data organization

2) Explain Entities, Entity Set, and their Representation.

Answer:

Entity

An entity is any object that exists independently and has attributes.

Example: Student, Teacher

Representation

In ER diagram, an entity is represented by a rectangle.

Types of Entities

Strong Entity

A strong entity has a primary key and exists independently.

Example: Student (Roll No)

Weak Entity

A weak entity depends on another entity and does not have a primary key.
Example: Dependent of Employee

Entity Set

An entity set is a collection of similar entities.

Example: All students in a college form a Student entity set.

3. Explain Attributes and their Types in ER Model.

Answer:

Attribute:

An attribute is a property or characteristic of an entity.

Types of Attributes
1. Simple Attribute

A simple attribute is an attribute which cannot be divided further.

Example: Age

2. Composite Attribute

A Composite attribute is an attribute which Can be divided into smaller parts.

Example: Address: street, city, state, country

3. Single-Valued Attribute

Single – valued attribute has only one value for each entity.
Example: Roll Number

4. Multi-Valued Attribute

Multi – valued attribute has multiple values.

Example: Phone Numbers

5. Derived Attribute

Derived attribute can be calculated from other attributes.

Example: Age from Date of Birth
6. Key Attribute

Key attribute uniquely identifies an entity.

Example: ID

4. Explain Relationship and Relationship Set in ER Model.

Answer:

Relationship

A relationship is an association between two or more entities.

Example: Student enrolls in Course

Representation

Relationships are represented by a diamond shape.

Relationship Set

A relationship set is a collection of similar relationships.

Degree of relationship:

The no of different entity sets participating in a relationship set is called the

degree of a relationship set.

1. Unary/Recursive Relationship: When there is only ONE entity set

participating in a relation, the relationship is called a unary relationship.
2. Binary Relationship: When there are TWO entities set participating in a
relationship, the relationship is called a binary relationship.

3. Ternary Relationship: When there are three entity sets participating in a

relationship, the relationship is called a ternary relationship.

4. N-ary Relationship: When there are n entities set participating in a

relationship, the relationship is called an n-ary relationship.

Cardinality of Relationship:
1:1 : One entity is related to only one entity, and vice versa.

1 : M: One entity is related to many entities.

M : 1: Many entities are related to one entity.

M : M: Many entities are related to many entities.

5. Explain Subclass, Superclass, and Inheritance.

Introduction

Subclass and superclass concepts are used for hierarchical data modeling in ER
diagrams.

Superclass
A superclass is a general entity that contains common attributes.
Example: Person

Subclass

A subclass is a specialized entity derived from superclass.

Example: Student, Teacher

Inheritance

Inheritance means subclasses inherit attributes and relationships from

superclass.

Features

 Promotes reusability
 Reduces redundancy
 Organizes data hierarchy

9. Explain ER Diagram with Specialization, Generalization, and Inheritance.

Specialization:

It is the process of dividing a higher-level entity into lower-level sub-entities

based on characteristics.

Example

Employee → Tester, Developer

Generalization:

It is a bottom-up approach where multiple entities are combined into one

general entity.

Example

Faculty + Student → Person

10) Explain Database Schema and Data Types in SQL.

Answer:

Introduction

A database is used to store organized data, and SQL (Structured Query

Language) is used to manage and manipulate the database. The structure of
the database is defined using a database schema, and the type of data stored
is defined using data types.

Database Schema

A database schema is the logical structure of a database. It defines:

 Tables
 Columns
 Data types
 Relationships between tables
 Constraints
In simple words, schema is the design or blueprint of the database.

Example Schema

Student Table:

 RollNo – Integer
 Name – Varchar
 Age – Integer
 Marks – Float

Data Types in SQL

Data types specify the type of data that can be stored in a column.

Common SQL Data Types

Data Type Description

INT Stores integers

VARCHAR(n) Stores text

CHAR(n) Fixed length text

FLOAT Decimal numbers

DATE Stores date

BOOLEAN True/False

Importance of Data Types

 Ensures correct data storage

 Improves memory usage
 Maintains data integrity

Conclusion

Database schema defines the structure of the database, and data types define
the type of data stored in the database.
11) Explain DDL Operations: CREATE, ALTER, DROP, RENAME.

Answer:

Introduction

DDL stands for Data Definition Language. DDL commands are used to define
and modify the structure of database objects like tables.

DDL Commands

1. CREATE

The CREATE command is used to create a new table.

Syntax:

CREATE TABLE Student (

RollNo INT,

Name VARCHAR(50),

Age INT

);

2. ALTER

The ALTER command is used to modify an existing table.

Syntax:

ALTER TABLE Student ADD Marks INT;

ALTER TABLE Student MODIFY Name VARCHAR(100);

3. DROP

The DROP command is used to delete a table completely.

Syntax:

DROP TABLE Student;

4. RENAME

The RENAME command is used to rename a table.

Syntax:

RENAME TABLE Student TO Student Details;

12) Explain DML Operations: INSERT, DELETE, UPDATE.

Answer:

Introduction

DML stands for Data Manipulation Language. These commands are used to
manipulate data stored in tables.

DML Commands

1. INSERT

Used to insert new records into a table.

INSERT INTO Student VALUES (1, 'Ravi', 20);

2. UPDATE

Used to modify existing data.

UPDATE Student

SET Age = 21

WHERE RollNo = 1;

3. DELETE

Used to delete records from a table.

DELETE FROM Student

WHERE RollNo = 1;

13) Explain Basic SQL Querying (SELECT and PROJECT) with WHERE Clause.
Answer:

Introduction

The SELECT statement is used to retrieve data from a database. It is the most
commonly used SQL command.

SELECT Statement

Used to select data from a table.

SELECT * FROM Student;

Project Operation

Project means selecting specific columns.

SELECT Name, Age FROM Student;

WHERE Clause

The WHERE clause is used to filter records based on a condition.

SELECT * FROM Student

WHERE Age > 18;

Conditions Used in WHERE

Operator Meaning

= Equal

> Greater than

< Less than

>= Greater than equal

<= Less than equal

!= Not equal
14) Explain Arithmetic and Logical Operations in SQL.

Answer:

Introduction

SQL supports arithmetic and logical operators to perform calculations and

apply conditions.

Arithmetic Operators

Operator Operation

+ Addition

- Subtraction

* Multiplication

/ Division

Example:

SELECT Marks + 10 FROM Student;

Logical Operators

Operator Meaning

AND Both conditions true

OR At least one condition true

NOT Reverse condition

Example:

SELECT * FROM Student

WHERE Age > 18 AND Marks > 50;

15) Explain Aggregation, Grouping, and Ordering in SQL.

Answer:

Introduction

Aggregation functions are used to perform calculations on multiple rows of

data.

Aggregate Functions

Function Description

COUNT() Counts rows

SUM() Total value

AVG() Average

MAX() Maximum

MIN() Minimum

Example:

SELECT AVG(Marks) FROM Student;

GROUP BY

GROUP BY is used to group rows with same values.

SELECT Dept, COUNT(*)

FROM Student

GROUP BY Dept;

ORDER BY

ORDER BY is used to sort the result.

SELECT * FROM Student

ORDER BY Marks DESC;

ASC → Ascending

DESC → Descending
 Unit – IV: SQL
1) Explain Nested Queries (Sub Queries) in SQL.
Answer:

Introduction

A Nested Query or Sub Query is a query inside another SQL query. The inner
query executes first, and its result is used by the outer query.

Definition

A subquery is a SELECT statement written inside another SQL statement such

as SELECT, INSERT, UPDATE, or DELETE.

Types of Subqueries

1. Single Row Subquery

Returns only one row.

SELECT Name
FROM Student
WHERE Marks = (SELECT MAX(Marks) FROM Student);

2. Multiple Row Subquery

Returns multiple rows.

SELECT Name
FROM Student
WHERE Dept IN (SELECT Dept FROM Student WHERE Marks > 80);

3. Correlated Subquery

Inner query depends on outer query.

Advantages

 Simplifies complex queries

  Improves query readability
 Used for comparisons and filtering

Conclusion

Subqueries are used to perform complex queries by embedding one query

inside another.

2. Explain Different Types of Joins in SQL.

Answer:

Introduction

Joins are used to combine data from two or more tables based on a related
column.

Types of Joins

1. INNER JOIN

Returns matching records from both tables.

SELECT [Link], [Link]

FROM Student
INNER JOIN Dept
ON [Link] = [Link];

2. LEFT JOIN

Returns all records from left table and matching records from right table.

SELECT [Link], [Link]

FROM Student
LEFT JOIN Department
ON [Link] = [Link];

3. RIGHT JOIN

Returns all records from right table and matching records from left table.
SELECT [Link], [Link]

FROM Student

RIGHT JOIN Department

ON [Link] = [Link];

4. FULL JOIN

Returns all records when there is a match in either table.

SELECT [Link], [Link]

FROM Student
FULL JOIN Department
ON [Link] = [Link];

5. EQUI JOIN

EQUI JOIN is a join where tables are joined using equality (=) condition. It is
usually an INNER JOIN with = operator.

SELECT [Link], [Link]

FROM Student, Department

WHERE [Link] = [Link];

3. Explain SQL Functions (Date, Numeric, String, Conversion Functions).

Answer:

Introduction

SQL functions are built-in functions used to perform operations on data.

Types of SQL Functions

1. Date Functions
Function Description

SYSDATE Current date

NOW() Current date and time

DATE() Extract date

2. Numeric Functions

Function Description

ROUND() Rounds number

SQRT() Square root

MOD() Remainder

3. String Functions

Function Description

UPPER() Convert to uppercase

LOWER() Convert to lowercase

LENGTH() String length

SUBSTR() Extract substring

4. Conversion Functions

Function Description

TO_CHAR() Convert number to string

TO_NUMBER() Convert string to number

TO_DATE() Convert string to date

Conclusion
SQL functions help in data manipulation, formatting, and calculations.

4. Explain Creating Tables with Relationship.

Answer:

Introduction

Tables in a relational database are related using keys, especially primary key
and foreign key.

Primary Key

A primary key is a unique identifier for each record.

Foreign Key

A foreign key is a column that refers to the primary key of another table.

Example

CREATE TABLE Department (

DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Importance

 Maintains relationship between tables

 Ensures referential integrity
Conclusion

Tables are connected using primary key and foreign key relationships.

5. Explain Keys and Integrity Constraints.

Answer:

Introduction

Keys and constraints are used to maintain accuracy and consistency of data.

Types of Keys

Key Description

Primary Key Unique and not null

Foreign Key References primary key

Candidate Key Possible primary key

Super Key Uniquely identifies row

Composite Key Combination of columns

Integrity Constraints

Entity Integrity

Primary key cannot be NULL.

Referential Integrity

Foreign key must match primary key.

Domain Constraint

Values must be in a valid range.

Conclusion

Keys and integrity constraints ensure data accuracy and database consistency.
6. Explain Views in SQL.

Answer:

Introduction

A view is a virtual table created from a SELECT query.

Definition

A view does not store data physically; it displays data from one or more tables.

Syntax

CREATE VIEW Student_View AS

SELECT Name, Marks FROM Student;

Advantages

 Improves security
 Simplifies complex queries
 Provides data abstraction

Conclusion

Views are used to create virtual tables for security and simplicity.

7. Explain Relational Set Operations in SQL.

Answer:

Introduction

Set operations are used to combine results of two SELECT queries.

Types of Set Operations

Operation Description

UNION Combines results, removes duplicates

UNION ALL Combines results, keeps duplicates

Operation Description

INTERSECT Common rows

MINUS Rows in first but not second

Union Example

SELECT Name FROM Student

UNION
SELECT Name FROM Alumni;

UNION ALL

Definition

UNION ALL is used to combine results of two SELECT queries and includes
duplicates.

SELECT Name FROM Student

UNION ALL
SELECT Name FROM Alumni;

INTERSECT

Definition

INTERSECT returns only common rows from both SELECT queries.

SELECT Name FROM Student

INTERSECT
SELECT Name FROM Alumni;

MINUS

Definition

MINUS returns rows from first SELECT query that are not present in second
SELECT query.
SELECT Name FROM Student
MINUS
SELECT Name FROM Alumni;

8. Explain Transaction Control Language (TCL).

Answer:

Introduction

TCL commands are used to manage transactions in a database.

TCL Commands

COMMIT

Saves changes permanently.

ROLLBACK

Undo changes.

SAVEPOINT

Creates a point to rollback.

SAVEPOINT A;
ROLLBACK TO A;

Conclusion

TCL is used to control transactions and maintain database consistency.

9. Explain Data Control Language (DCL).

Answer:

Introduction

DCL commands are used to control access and permissions in a database.

DCL Commands
GRANT

Gives permission to user.

GRANT SELECT ON Student TO User1;

REVOKE

Removes permission.

REVOKE SELECT ON Student FROM User1;

Conclusion

DCL is used for database security and access control.

 UNIT – V: PL/SQL
1) Briefly explain PL/SQL

Answer:

Introduction

PL/SQL stands for Procedural Language/Structured Query Language. It is an

extension of SQL developed by Oracle. PL/SQL allows us to write procedural
programs that include SQL statements.

Need for PL/SQL

SQL is a non-procedural language, but PL/SQL is a procedural language,

meaning it supports:

 Variables
 Loops
 Conditions
 Functions
 Procedures

Features of PL/SQL

 Block structured language

 Supports exception handling
 Supports cursors
 Improves performance
 Supports modular programming

Advantages

 Reduces network traffic

 Increases performance
 Code reusability
 Better error handling

Conclusion

PL/SQL is used to write complete programs using SQL and procedural

concepts.
2. Structure of PL/SQL Block

Answer:

Introduction

PL/SQL program is written in blocks, and each block has three sections.

Structure of PL/SQL Block

DECLARE
-- Variable declaration
BEGIN
-- Executable statements
EXCEPTION
-- Error handling
END;

Sections of PL/SQL Block

1. DECLARE Section

Used to declare variables, constants, and cursors.

2. BEGIN Section

Contains SQL statements and program logic.

3. EXCEPTION Section

Handles runtime errors.

Example

Sections of PL/SQL Block

1. DECLARE Section

Used to declare variables, constants, and cursors.

2. BEGIN Section

Contains SQL statements and program logic.

3. EXCEPTION Section

Handles runtime errors.

Example

DECLARE
num1 NUMBER := 10;
num2 NUMBER := 20;
sum NUMBER;
BEGIN
sum := num1 + num2;
DBMS_OUTPUT.PUT_LINE(sum);
END;

Conclusion

PL/SQL block structure helps in organizing program into logical sections.

3. Control Structures in PL/SQL

Answer:

Introduction

Control structures are used to control the flow of execution in a PL/SQL

program.

Types of Control Structures

1. Conditional Statements

IF Statement

IF condition THEN
 statement;
END IF;
IF-ELSE Statement
IF condition THEN
statement1;
ELSE
statement2;
END IF;
2. Loop Statements

WHILE LOOP

WHILE condition LOOP

statement;
END LOOP;

FOR LOOP

FOR i IN 1..5 LOOP

statement;
END LOOP;

Conclusion

Control structures help in decision making and repetition of statements.

4. Cursors in PL/SQL

Answer:
Introduction

A cursor is a pointer to a result set returned by a SQL query.

Types of Cursors

1. Implicit Cursor

Automatically created by Oracle.

2. Explicit Cursor

Created by the programmer.

Steps to Use Cursor

1. Declare cursor
2. Open cursor
3. Fetch data
4. Close cursor

DECLARE
CURSOR c1 IS SELECT Name FROM Student;
sname [Link]%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO sname;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sname);
END LOOP;
CLOSE c1;
END;

5. Procedure in PL/SQL
Answer:

Introduction

A procedure is a named PL/SQL block that performs a specific task.

Syntax

CREATE OR REPLACE PROCEDURE proc_name IS

BEGIN
statements;
END;
Example
CREATE OR REPLACE PROCEDURE display_msg IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END;

Advantages

 Code reusability
 Modular programming
 Easy maintenance

Conclusion

Procedures are used to perform specific operations repeatedly.

6. Function in PL/SQL

Answer:

Introduction

A function is a PL/SQL block that returns a value.

Syntax
CREATE OR REPLACE FUNCTION func_name RETURN datatype IS
BEGIN
RETURN value;
END;
Example
CREATE OR REPLACE FUNCTION add_num(a NUMBER, b NUMBER)
RETURN NUMBER IS
BEGIN
RETURN a + b;
END;

Difference Between Procedure and Function

Procedure Function

Does not return value Returns value

Called as statement Called in expression

Conclusion

Functions are used when a value needs to be returned.

7. Packages in PL/SQL

Answer:

Introduction

A package is a collection of procedures, functions, and variables stored

together.

Parts of Package

Package Specification

Declares procedures and functions.

Package Body

Contains implementation.

Advantages

 Modular programming
 Code reusability
 Improves performance

Conclusion

Packages help in organizing large programs into modules.

8. Exception Handling in PL/SQL

Answer:

Introduction

Exception handling is used to handle runtime errors in PL/SQL.

Types of Exceptions

 Predefined exceptions
 User-defined exceptions

Example

BEGIN
num := 10/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;

Advantages

 Prevents program crash

 Improves reliability
Conclusion

Exception handling is used to handle errors and maintain normal program

flow.

9. Triggers in PL/SQL

Answer:

Introduction

A trigger is a stored procedure that automatically executes when an event

occurs.

Types of Triggers

 BEFORE Trigger
 AFTER Trigger
 INSERT Trigger
 UPDATE Trigger
 DELETE Trigger

Example

CREATE OR REPLACE TRIGGER trg1

BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Record inserted');
END;

Advantages

 Automatic execution
 Maintains data integrity
 Used for auditing

Conclusion
Triggers are used to automatically perform actions when database events
occur.

10. Explain Transaction State in DBMS.

Answer:

Introduction

A transaction is a sequence of database operations that performs a logical unit

of work. A transaction must follow ACID properties (Atomicity, Consistency,
Isolation and Durability).

Transaction States

During execution, a transaction goes through different states.

1. Active State

The transaction is executing read and write operations.

2. Partially Committed State

The transaction has completed all operations but not yet committed.

3. Committed State

The transaction is successfully completed and changes are saved

permanently.

4. Failed State

The transaction cannot proceed due to error or system failure.

5. Aborted State

The transaction is rolled back and database is restored to previous state.

6. Terminated State

The transaction finishes execution after commit or abort.

Transaction State Diagram

Conclusion

Transaction states describe the life cycle of a transaction from start to

completion.

11. Explain Implementation of Atomicity and Durability.

Answer:

Introduction

Atomicity and Durability are two important ACID properties of DBMS.

Atomicity

Atomicity means all operations of a transaction are completed or none are

completed.

Implementation of Atomicity

Atomicity is implemented using:

 Log file
 Undo operation
 Rollback

If a transaction fails, undo operations are performed using log file.

Durability
Durability means once a transaction is committed, the changes are
permanent even if system crashes.

Implementation of Durability

Durability is implemented using:

 Log file
 Redo operation
 Stable storage

Conclusion

Atomicity is implemented using undo, and durability is implemented using

redo operations and stable storage.

12. Explain Concurrent Execution of Transactions.

Answer:

Introduction

Concurrent execution means multiple transactions are executed at the same

time.

Advantages

 Improves CPU utilization

 Increases system throughput
 Reduces waiting time

Problems of Concurrent Execution

 Lost Update Problem

 Dirty Read Problem
 Uncommitted Data Problem
 Inconsistent Retrieval Problem

Conclusion

Concurrent execution improves performance but needs concurrency control

to maintain consistency.
13. Explain Serializability in DBMS.

Answer:

Introduction

Serializability is a concept used to ensure correctness of concurrent

transactions.

Definition

A schedule is serializable if the result of concurrent execution is same as serial

execution.

Types of Serializability

1. Conflict Serializability

Conflict serializability is a database concept that ensures a concurrent schedule

(multiple transactions running simultaneously)
2. View Serializability

Based on final result of transactions.

Conclusion

Serializability ensures database consistency during concurrent execution.

14. Explain Recoverability in DBMS.

Answer:

Introduction

Recoverability ensures that a transaction commits only after the transaction

from which it reads commits.

Types of Recoverable Schedules

 Recoverable Schedule
 Cascading Rollback
 Cascadeless Schedule
 Strict Schedule
Conclusion

Recoverability ensures database can be recovered without data

inconsistency.

15. Explain Implementation of Isolation.

Answer:

Introduction

Isolation ensures that transactions execute independently without

interference.

Isolation Levels

Level Description
Read Uncommitted Can read uncommitted data
Read Committed Reads only committed data
Repeatable Read Same data read multiple times
Serializable Highest isolation

Implementation Methods

 Locking
 Timestamp
 Validation

Conclusion

Isolation is implemented using locking and concurrency control techniques.

16. Explain Testing for Serializability.

Answer:

Introduction

Serializability is tested using a precedence graph.

Steps
 1. Create nodes for each transaction
2. Draw edge if conflict occurs
3. Check for cycle

 If cycle present → Not serializable

 If no cycle → Serializable

Testing for serializability in a DBMS ensures interleaved transaction schedules

maintain database consistency, matching a serial execution. The primary
method is constructing a precedence graph (conflict graph) to check for cycles.
If the graph is acyclic (no cycles), the schedule is conflict serializable.

 Rule 1: Write-Read (W - R) − If Ti writes a value, and Tj reads that value

afterward, draw an edge Ti→Tj.
 Rule 2: Read-Write (R - W) − If Ti reads a value, and Tj writes that value
afterward. Then draw an edge Ti→Tj.
 Rule 3: Write-Write (W - W) − If Ti writes a value, and Tj writes the same value
later. Then draw an edge Ti→Tj.

Conclusion

Serializability testing ensures correct concurrent execution.

17. Explain Failure Classification.

Answer:

Types of Failures
1. Transaction Failure

Logical error, system error.

2. System Failure

Power failure, OS crash.

3. Disk Failure

Hard disk crash.

Conclusion

Failure classification helps in database recovery planning.

18. Explain Recovery Algorithm.

Answer:

Algorithm for Recovery and Isolation Exploiting Semantics (ARIES)

ARIES is a robust recovery algorithm based on the Write-Ahead Logging

(WAL) protocol.

Types of Log Records in ARIES

Every update operation creates a log record, which can be of the following
types:
 Undo-only Log Record: Stores only the before image (original data). Used to
undo changes.
 Redo-only Log Record: Stores only the after image (new data). Used to redo
changes.
 Undo-Redo Log Record: Stores both before and after images. Used for both
undo and redo operations.
Log Sequence Numbers (LSN)
 Every log record is assigned a unique, monotonically increasing LSN.
 Every data page has a pageLSN indicating the LSN of the last update applied
to it.
Write-Ahead Logging (WAL) Rules
 A log record must reach stable storage before the corresponding data page
is written to disk.
 Log writes are buffered in a memory area called the log tail.
 The log tail is flushed to disk when full.
 A transaction is not considered committed until its commit record is flushed
to disk.
Checkpoints
Periodically, the system writes a checkpoint record to the log.
A checkpoint contains:
 Transaction Table
 Dirty Page Table
A master log record, stored in stable storage, holds the LSN of the most recent
checkpoint.
Recovery Phases in ARIES
When the system restarts after a crash, ARIES performs three recovery phases:
1. Analysis Phase
 Starts from the latest checkpoint.
 Reconstructs the state of active transactions and dirty pages at the time of
the crash.
2. Redo Phase
 Replays operations forward from the smallest LSN of dirty pages.
 Reapplies necessary changes to bring the database to the state before
crash.
3. Undo Phase
 Scans the log backward.
 Rolls back the effects of uncommitted (loser) transactions.