Q1] Explain the various application of database system in modern industry.

Ans:-

DBMS applications range from banking to education, ensuring the effective management of
large amounts of data. Understanding the various applications of DBMS is crucial for
database professionals and students alike.
1. Railway Reservation System
In the rail route reservation framework, the information base is needed to store the record or
information of ticket appointments, status of train’s appearance, and flight. Additionally, if
trains get late, individuals become acquainted with it through the information base update.
2. Library Management System
There are many books in the library so; it is difficult to store the record of the relative
multitude of books in a register or duplicate. Along these lines, the data set administration
framework (DBMS) is utilized to keep up all the data identified with the name of the book,
issue date, accessibility of the book, and its writer.
3. Banking
Database the executive’s framework is utilized to store the exchange data of the client in the
information base.
4. Education Sector
Presently, assessments are led online by numerous schools and colleges. They deal with all
assessment information through the data set administration framework (DBMS). In spite of
that understudy’s enlistments subtleties, grades, courses, expense, participation, results, and so
forth all the data is put away in the information base.
5. Credit card exchanges
The database Management framework is utilized for buying on charge cards and age of month
to month proclamations.
Q2,19,35] Explain how DDL, DML, DCL used in management of DBMS.
Ans:-

1. Data Definition Language (DDL) in SQL

DDL or Data Definition Language actually consists of the SQL commands that can be used to
defining, altering, and deleting database structures such
as tables, indexes, and schemas. It simply deals with descriptions of the database schema and
is used to create and modify the structure of database objects in the database
Common DDL Commands

Command Description Syntax

CREATE Create database or its CREATE TABLE table_name

objects (table, index, (column1 data_type, column2
function, views, store data_type, ...);
procedure, and triggers)

DROP Delete objects from the DROP TABLE table_name;

database

ALTER Alter the structure of the ALTER TABLE table_name ADD

database COLUMN column_name
data_type;

TRUNCATE Remove all records from a TRUNCATE TABLE table_name;

table, including all spaces
allocated for the records are
removed
COMMENT Add comments to the data COMMENT 'comment_text' ON
dictionary TABLE table_name;

RENAME Rename an object existing RENAME TABLE old_table_name

in the database TO new_table_name;

Example of DDL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50), last_name
VARCHAR(50),
hire_date DATE
);

2. Data Manipulation Language (DML) in SQL

The SQL commands that deal with the manipulation of data present in the database belong to
DML or Data Manipulation Language and this includes most of the SQL statements. It is the
component of the SQL statement that controls access to data and to the database. Basically,
DCL statements are grouped with DML statements.
Common DML Commands
Command Description Syntax

INSERT Insert data into a INSERT INTO table_name (column1, column2,

table ...) VALUES (value1, value2, ...);

UPDATE Update existing data UPDATE table_name SET column1 = value1,

within a table column2 = value2 WHERE condition;
DELETE Delete records DELETE FROM table_name WHERE condition;
from a database
table

LOCK Table control LOCK TABLE table_name IN lock_mode;

concurrency

CALL Call a PL/SQL or CALL procedure_name(arguments);

JAVA subprogram

Command Description Syntax

EXPLAIN Describe the access EXPLAIN PLAN FOR SELECT * FROM

PLAN path to data table_name;

Example of DML
INSERT INTO employees (first_name, last_name, department) VALUES
('Jane', 'Smith', 'HR');
3. Data Control Language (DCL) in SQL
DCL (Data Control Language) includes commands such
as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls
of the database system. These commands are used to control access to data in the database by
granting or revoking permissions.
Common DCL Commands
Command Description Syntax

GRANT Assigns new privileges to a GRANT privilege_type

user account, allowing [(column_list)] ON [object_type]
access to specific database object_name TO user [WITH
objects, actions, or GRANT OPTION];
functions.

REVOKE Removes previously granted REVOKE [GRANT OPTION FOR]

privileges from a user privilege_type [(column_list)] ON
account, taking away their [object_type] object_name FROM
access to certain database user [CASCADE];
objects or actions.

Example of DCL
GRANT SELECT, UPDATE ON employees TO user_name;

Q3] What is relational database? Describe its basic structure including rows,
tables and columns with suitable example.
Ans:-

The relational model represents how data is stored in Relational Databases. A relational
database consists of a collection of tables each of which is assigned a unique name.
Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE,
and AGE shown in the table.
Basic Structure of a Relational Database
A relational database consists of the following components:
1. Table (Relation)
• A table is a collection of related data organized in rows and columns.
• Each table represents an entity (e.g., Students, Employees, Products).
2. Rows (Records or Tuples)
• Each row represents a unique record in the table.
• It contains actual data values for the entity.
3. Columns (Fields or Attributes)
• Columns define the properties of the entity.
• Each column has a unique name and a specific data type (e.g., INTEGER, VARCHAR).

Example of a Relational Database Table

Consider a Student table in a college database:

Student_ID Name Age Course Email

101 Raj Sharma 20 B.Sc. CS [email protected]

102 Priya Mehta 21 B.Com [email protected]

103 Amit Verma 19 B.Tech [email protected]

Explanation:
• Table Name: Student
• Columns: Student_ID, Name, Age, Course, Email
• Rows: Each row represents an individual student’s details.

Q4] Explain concept key in relational Database. Difference between primary,

foreign and candidate key.
Ans:-

In a Relational Database, a key is a column or a set of columns that helps in uniquely

identifying each record (row) in a table. Keys ensure data integrity and eliminate redundancy
by maintaining proper relationships between tables.
Types of Keys in a Relational Database:
1. Primary Key (PK)
2. Foreign Key (FK)
3. Candidate Key (CK)
1. Primary Key (PK)
• A Primary Key is a unique identifier for each record in a table.
• It ensures that no two rows have the same value in the primary key column.
• It cannot contain NULL values and must always have a unique value. 脥

Example (Student Table)

Student_ID (PK) Name Age Course

101 Raj Sharma 20 B.Sc. CS

102 Priya Mehta 21 B.Com 103 Amit Verma 19

B.Tech

꼡 In this table, Student_ID is the Primary Key, as it uniquely identifies each student.

2. Foreign Key (FK)

• A Foreign Key is a column that establishes a relationship between two tables.
• It refers to the Primary Key of another table.
• It allows only those values that exist in the referenced Primary Key column.

脥 Example (Student and Course Table Relationship)

1. Student Table
Student_ID (PK) Name Age Course_ID (FK)

101 Raj Sharma 20 C101

102 Priya Mehta 21 C102

103 Amit Verma 19 C103

2. Course Table

Course_ID (PK) Course_Name Duration

C101 B.Sc. CS 3 Years
C102 B.Com 3 Years
C103 B.Tech 4 Years
꼡 Here, Course_ID in the Course Table is a Primary Key, and the same column appears as a
Foreign Key in the Student Table, linking students to their respective courses.
3. Candidate Key (CK)
• A Candidate Key is a column (or a set of columns) that can uniquely identify a row.
• A table can have multiple Candidate Keys, but only one can be chosen as the Primary
Key.
• A Primary Key is selected from the Candidate Keys. 脥 Example (Employee Table)

Emp_ID (PK) Emp_Code (CK) Name Department

201 E1001 Rahul Verma IT

202 E1002 Neha Sharma HR

203 E1003 Amit Kumar Finance

꼡 Here, both Emp_ID and Emp_Code are Candidate Keys because they uniquely identify
each row. However, only one (e.g., Emp_ID) is chosen as the Primary Key.

Difference Between Primary Key, Foreign Key, and Candidate Key

Feature Primary Key (PK) Foreign Key (FK) Candidate Key
(CK)
A set of columns
Uniquely identifies Refers to the Primary Key of
that can be a
Definition each record in a table. another table.
Primary Key.

Uniqueness Always unique. Can have duplicate values. Always unique.

Cannot have NULL Can contain NULL values if no Cannot have

NULL Values values. relation exists. NULL values.

Number in
Can be multiple
Only one per table. Can be multiple in a table.
in a table.
Table
Establishes a
Identifies records in A potential
Usage relationship between candidate for the
its own table. tables. Primary Key.
Q5] Explain ER model and its key components.
Ans:-

The Entity-Relationship (ER) Model is a conceptual framework used in database design to

represent the structure of a database visually. It describes entities (objects), their attributes
(properties), and relationships (associations) between entities. The ER model helps in
designing a database before its actual implementation in Relational Databases (RDBMS).

Key Components of the ER Model

1. Entity
An entity is an object or thing in the real world that can be uniquely identified. It represents a
person, place, event, or concept that is stored in a database.

꼡 Types of Entities:

• Strong Entity: Has a Primary Key and exists independently (e.g., Student, Employee).
• Weak Entity: Depends on a Strong Entity and does not have a Primary Key (e.g.,
Dependent of Employee).

脥 Example:
In a university database, Student and Course are entities.

2. Attributes
Attributes define the properties of an entity.

꼡 Types of Attributes:

• Simple Attribute: Cannot be divided further (e.g., Name, Age).

• Composite Attribute: Can be broken down into smaller attributes (e.g., Name → First
Name, Last Name).
• Derived Attribute: Value derived from another attribute (e.g., Age from Date of Birth).
• Multivalued Attribute: Can have multiple values (e.g., Phone Numbers).
• Key Attribute: Uniquely identifies an entity (e.g., Student_ID).

脥 Example:
For a Student entity:
• Attributes: Student_ID (Key), Name, Age, Email, Phone_Number.
3. Relationship
A relationship defines how two or more entities are related to each other.

꼡 Types of Relationships:

• One-to-One (1:1): One entity is related to only one entity (e.g., One person → One
passport).
• One-to-Many (1:M): One entity is related to many entities (e.g., One teacher → Many
students).
• Many-to-Many (M:N): Many entities are related to many entities (e.g., Many students
→ Many courses).

脥 Example:
A Student enrolls in a Course → One-to-Many Relationship (1:M)

4. Primary Key (PK) and Foreign Key (FK)

• Primary Key: A unique identifier for each record in an entity (e.g., Student_ID in the
Student table).
• Foreign Key: A key in one entity that references a Primary Key in another entity,
forming a relationship.

脥 Example:

• Student_ID is the Primary Key in the Student table.

• Course_ID in the Student_Course Table is a Foreign Key linking it to the Course Table.

5. Cardinality
Cardinality defines the number of instances of one entity that can be associated with instances
of another entity.

꼡 Types of Cardinality:

• One-to-One (1:1) – One employee has one company car.

• One-to-Many (1:M) – One teacher teaches many students.
• Many-to-Many (M:N) – Many students enroll in many courses.

脥 Example:
A Library has many Books, but each Book belongs to one Library → One-to-Many (1:M)
Q6] Explain 5 features of DBMS.
Ans:-

A Database Management System (DBMS) is software that enables users to create, store,
retrieve, and manage data efficiently. It provides a structured way to handle large amounts of
data while ensuring security, consistency, and integrity.

1. Data Integrity and Consistency

• DBMS ensures accuracy, correctness, and consistency of data.
• Enforces constraints (Primary Key, Foreign Key, Unique, Not Null) to maintain data
integrity.
• Prevents inconsistent data updates by applying ACID (Atomicity, Consistency,
Isolation, Durability) properties.

脥 Example:
If a student enrolls in a course, DBMS ensures that the Student_ID exists in the Student table
before adding it to the Enrollment table (Foreign Key Constraint).

2. Data Security and Access Control

• DBMS provides user authentication and authorization to prevent unauthorized access.
• Allows role-based access control, where users are given different levels of permissions
(e.g., Admin, User, Guest).
• Supports encryption and backup mechanisms to protect sensitive data.

脥 Example:
• A bank database ensures that only tellers can view customer details, while managers
can approve transactions.

3. Data Redundancy Minimization

• Normalization in DBMS reduces data duplication by organizing data into multiple
tables.
• Ensures that the same data is not stored in multiple places, reducing storage space and
improving efficiency.

脥 Example:
• Instead of storing department details with every employee record, a separate
Department table is created, linked to the Employee table using Foreign Keys.

4. Multi-User Access and Concurrency Control

• Multiple users can access the database simultaneously without conflicts.
• Uses locking mechanisms to prevent data inconsistency when multiple transactions
occur at the same time.

脥 Example:
• In an online shopping website, multiple users can place orders at the same time without
affecting each other’s transactions.

5. Backup and Recovery Mechanism

• DBMS provides automatic backup and recovery features to prevent data loss due to
system failures, crashes, or cyberattacks.
• Supports transaction logs, which help restore the database to its last consistent state.

脥 Example:
• If a power failure occurs while updating customer information, DBMS rolls back
incomplete transactions to maintain data consistency.

Q7,21] Define normal forms with example.

Ans:-

Normalization is the process of organizing data in a relational database to minimize

redundancy and ensure data integrity. It involves dividing large tables into smaller, more
efficient tables and defining relationships among them.
Normalization is achieved through Normal Forms (NF), which are a set of guidelines to
design efficient databases.

Types of Normal Forms:

Normalization Forms in DBMS - Short Summary

First Form - No Repeating Groups

🔹 Rule: Each column must have atomic (indivisible) values, and each row must be unique.
🔹 Problem: Repeating groups or multiple values in a single cell.
🔹 Solution: Split multi-valued attributes into separate rows or tables.

✅ Example (Before - Violating Atomicity)

Student Courses

A Math, Physics

✅ After (Atomic Values Only)

Student Course

A Math

A Physics

Second Form - No Partial Dependency

🔹 Rule: Must be in First Form, and no partial dependency (i.e., no non-key column should
depend only on a part of the primary key).
🔹 Problem: A table with composite primary keys where some columns depend on only part
of the key.
🔹 Solution: Move partially dependent attributes to a separate table.

✅ Example (Before - Violating Partial Dependency)

Student Course Instructor Age

A Math Prof. X 20

A Physics Prof. Y 20

🔹 Problem: Age depends only on Student, not Course.

✅ After (Splitting into Two Tables)

Student Table

Student Age

A 20
Course Table

Student Course Instructor

A Math Prof. X

A Physics Prof. Y

Third Form - No Transitive Dependency

🔹 Rule: Must be in Second Form, and no transitive dependencies (i.e., non-key attributes
should not depend on other non-key attributes).
🔹 Problem: A column depends on another non-key column instead of the primary key.
🔹 Solution: Move dependent attributes to a separate table.

✅ Example (Before - Violating Transitive Dependency)

Student Department HOD

A CS Prof. X

B IT Prof. Y

🔹 Problem: HOD depends on Department, not directly on Student.

✅ After (Splitting into Two Tables)

Student Table

Student Department

A CS

B IT

Department Table

Department HOD

CS Prof. X

IT Prof. Y
4NF (Fourth Normal Form)

🔹 Definition: A table is in 4NF if it is in BCNF and has no multi-valued dependencies

(MVDs).

🔹 Problem: If one column uniquely determines multiple values of another column

independently of other attributes, it causes redundancy.

🔹 Solution: Split the table to remove multi-valued dependencies.

Example (Before 4NF - Violating MVD)

Student Course Hobby

A Math Chess

A Math Music

A Physics Chess

A Physics Music

Issue: Hobby is independent of Course, but both depend on Student, causing duplication.
After 4NF (Removing MVDs - Splitting Tables)

✅ Student-Course Table

Student Course

A Math

A Physics

✅ Student-Hobby Table

Student Hobby

A Chess

A Music

✔ No redundancy, no MVDs → Now in 4NF!

Q8] What is functional dependency? Explain its various types.
Ans:-
A functional dependency occurs when one attribute uniquely determines another attribute
within a relation. It is a constraint that describes how attributes in a table relate to each other.
If attribute A functionally determines attribute B we write this as the A→B.
Eg:-

roll_no name dept_name dept_building

42 abc CO A4

43 pqr IT A3

Types of Functional Dependencies in DBMS

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X
→ Y and Y is the subset of X, then it is called trivial functional dependency.
Symbolically: A→B is trivial functional dependency if B is a subset of A.
The following dependencies are also trivial: A→A & B→B Example 1 :
• ABC -> AB
• ABC -> A
• ABC -> ABC Example 2:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is
a subset of determinant set {roll_no, name}. Similarly, roll_no → roll_no is also an example
of trivial functional dependency.
2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.
Example 1 :
• Id -> Name
• Name -> DOB Example 2:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18
Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not
a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial
functional dependency, since age is not a subset of {roll_no, name}
3. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set are not dependent on each
other. i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is
called a multivalued functional dependency.
Example:

bike_model manuf_year color

tu1001 2007 Black

tu1001 2007 Red

tu2012 2008 Black

tu2012 2008 Red

tu2222 2009 Black

tu2222 2009 Red

In this table:
• X: bike_model
• Y: color
• Z: manuf_year
For each bike model (bike_model):
1. There is a group of colors (color) and a group of manufacturing years (manuf_year).
2. The colors do not depend on the manufacturing year, and the manufacturing year does
not depend on the colors. They are independent.
3. The sets of color and manuf_year are linked only to bike_model.
That’s what makes it a multivalued dependency.
In this case these two columns are said to be multivalued dependent on bike_model.
4. Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly dependent on determinant. i.e. If a
→ b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional
dependency.
Example:

enrol_no name dept building_no

42 abc CO 4

43 pqr EC 2

44 xyz IT 1
45 abc EC 2

Here, enrol_no → dept and dept → building_no. Hence, according to the axiom of transitivity,
enrol_no → building_no is a valid functional dependency. This is an indirect functional
dependency, hence called Transitive functional dependency.

Q9] Write transactional operation performed on DBMS.

Ans:-

A user can make different types of requests to access and modify the contents of a database.
So, we have different types of operations relating to a transaction. They are discussed as
follows:
1. Read(X)
A read operation retrieves the value of X from the database and stores it in the main memory
for processing. It is used when a user wants to view data without making changes.
Example: Checking an account balance.
2. Write(X)
A write operation updates the database with a new value from the main memory. Before
writing, a read operation is performed, modifications are made, and then the updated value is
stored.
Example: Withdrawing money from an account.
3. Commit
A commit operation permanently saves the changes made by a transaction. It ensures
data consistency and prevents loss due to failures. Example: Finalizing a bank transfer.
4. Rollback
A rollback operation restores the database to its last stable state if a transaction fails due to
power, hardware, or software issues. It prevents inconsistency. Example: Cancelling a failed
transaction.
Q10,29] Explain ACID properties.
Ans:-

1. Atomicity (A - All or Nothing)

• Ensures a transaction is fully completed or not executed at all.
• If any part of the transaction fails, all changes are rolled back to maintain data integrity.
• Example: If a bank transfer fails midway, the deducted amount is restored.
2. Consistency (C - Maintain Integrity)
• Guarantees that a transaction brings the database from one valid state to another.
• Ensures rules, constraints, and integrity are always maintained.
• Example: A transaction should not violate balance constraints in a banking system.
3. Isolation (I - No Interference)
• Transactions are executed independently without affecting each other.
• Ensures that intermediate changes are not visible to other transactions.
• Example: Two users withdrawing money from the same account won’t interfere with
each other’s transactions.
4. Durability (D - Permanent Changes)
• Ensures that once a transaction is committed, changes are permanently saved, even in
case of system failure.
• Data is stored safely in non-volatile memory.
• Example: After a successful money transfer, the updated balance remains saved despite
a power outage.
Q11] Explain Serializable and Non Serializable transactions.
Ans:- A serializable transaction is the highest level of isolation in database systems, ensuring
that the final outcome of concurrently executed transactions is the same as if they were
executed sequentially, one after another. This strict isolation prevents issues like dirty reads,
non-repeatable reads, and phantom reads, making it ideal for applications requiring high data
consistency, such as banking and finance. Serializable transactions are typically implemented
using methods like Two-Phase Locking (2PL), Timestamp Ordering, or Validation-Based
Scheduling, all of which help maintain order and prevent conflicts. However, this level of
control comes at the cost of reduced performance and concurrency, as transactions may need
to wait for locks or validation checks before proceeding.
On the other hand, non-serializable transactions allow greater concurrency by permitting
execution in a way that may not always produce the same results as a strictly serial execution.
These transactions operate at lower isolation levels, such as Read Committed or Read
Uncommitted, which can lead to anomalies like dirty reads, non-repeatable reads, and
phantom reads. While this approach improves performance and system throughput, it can
also compromise data integrity if not managed properly. Non-serializable transactions are
commonly used in scenarios where absolute consistency is not a priority, such as logging,
reporting, or applications where occasional inconsistencies are acceptable. The choice
between serializable and non-serializable transactions ultimately depends on the specific
requirements of the system, balancing between strict correctness and high concurrency.

Q12] Explain transactional as SQL statement with example.

Ans:-
A transaction in SQL is a sequence of one or more SQL statements executed as a single
unit of work. These statements could be performing operations like INSERT, UPDATE, or
DELETE. The main idea behind a transaction is that all changes made within it should
either be completely successful or fully undone in case of failure. In the case of an error, a
rollback ensures that no partial changes are saved.
The SQL transaction must adhere to four key properties known as ACID:
• Atomicity: The outcome of a transaction can either be completely successful or
completely unsuccessful. The whole transaction must be rolled back if one part of it
fails.
• Consistency: Transactions maintain integrity restrictions by moving the database from
one valid state to another.
• Isolation: Concurrent transactions are isolated from one another, assuring the accuracy
of the data.
• Durability: Once a transaction is committed, its modifications remain in effect even in
the event of a system failure.
The SQL Transaction Process
Transactions are controlled using specific SQL commands. The most common commands
used for managing transactions are:
1. BEGIN TRANSACTION: Starts a transaction.
2. COMMIT: Commits the transaction, saving all changes made.
3. ROLLBACK: Rolls back the transaction, undoing all changes made since the BEGIN
TRANSACTION.
4. SAVEPOINT: Marks a point within a transaction to which you can later roll back.
Example of SQL Transaction with a Bank Transfer Scenario
Let’s look at an example of a bank transfer between two accounts. This example demonstrates
the usage of multiple queries in a single transaction.
BEGIN TRANSACTION;

-- Deduct 150 from Account A

UPDATE Accounts
SET Balance = Balance - 150
WHERE AccountID = 'A';

-- Add 150 to Account B

UPDATE Accounts
SET Balance = Balance + 150
WHERE AccountID = 'B';

-- Commit the transaction if both operations succeed

COMMIT;
If any error occurs, such as an issue with the UPDATE query, you can use
ROLLBACK to undo all changes made during the transaction:
ROLLBACK;
This ensures that the system doesn’t end up in an inconsistent state, such as deducting money
from one account without adding it to another.
Q13] Hospital management system with ER diagram.
Ans:-

Q14] Various types of Engines

Ans:-

Database engines are MySQL components that can handle SQL operations like create, read,
update data from a database. There are two types of engines in MySQL: transactional and non-
transactional. InnoDB is the default engine for MySQL 5.5 and above versions. Major DBMS
uses an application programming interface(API) to enable the interaction of users with
database engines. It is very necessary to know about the engines for production databases and
it also impacts future development. To access the list of available MySQL engines we
run SHOW ENGINES; query.
There are 2 types of database engines :
• Transactional Databases: In this type, we can roll back the write operations on the
database if they are left incomplete. These operations are known as transactions.
Majorly, modern engines are transactional.
• Non-Transactional Databases: Unlike transactional databases, they don’t provide
Rollback/Commit. Instead, we need to manually code to perform the rollback
operations.

Q15] Describe relational query language.

Ans:-

Relational Query Language (RQL)

Relational Query Language (RQL) is used to interact with relational databases by retrieving,
inserting, updating, and deleting data stored in tables.

Types of Relational Query Languages:

1. Data Query Language (DQL) o Used to retrieve data
from the database.
o Example:
sql
SELECT * FROM Patients WHERE Age > 30;
2. Data Definition Language (DDL) o Defines the
database structure, including tables and schemas.
o Example:
sql
CREATE TABLE Doctor (
Doctor_ID INT PRIMARY KEY, Name VARCHAR(50),
Specialization VARCHAR(50)
);
3. Data Manipulation Language (DML) o Performs
operations like inserting, updating, and deleting data.
o Example:
sql
INSERT INTO Appointment (Appointment_ID, Patient_ID, Doctor_ID, Date, Time) VALUES
(101, 1, 5, '2025-03-05', '10:00 AM');
4. Data Control Language (DCL) o Manages
permissions and access control. o Example:

sql
GRANT SELECT ON Patients TO User1;
5. Transaction Control Language (TCL) o Manages
transactions in a database.
o Example:
sql
COMMIT;
ROLLBACK;

Q16] Database system various types of database model.

Ans:-

Q17] Role of indexing in dbms.

Ans:-
Role of Indexing in DBMS
Indexing in Database Management Systems (DBMS) is a technique used to improve the efficiency
of data retrieval operations. It acts like an index in a book, helping the database locate data quickly
without scanning the entire table. Below are the key roles of indexing in DBMS:

1. Improves Query Performance

• Indexes reduce the time complexity of search operations (e.g., SELECT queries).
• Instead of scanning the entire table (full table scan), the database can directly access the
relevant data.
2. Enhances Sorting and Searching
• Indexes speed up operations that involve ORDER BY and GROUP BY clauses.
• Searching for records using WHERE conditions is faster with indexes.
3. Supports Efficient Joins
• Indexes improve the performance of JOIN operations by reducing the time taken to match
records from different tables.
4. Reduces Disk I/O
• Since indexes store a subset of data in a structured format, fewer disk accesses are needed to
locate the data.
5. Helps in Uniqueness Enforcement
• Unique Indexes ensure that duplicate values are not inserted into a column (like enforcing
Primary Key and Unique Constraints).
6. Types of Indexing in DBMS
a) Primary Index
• Created on a Primary Key column.
• Helps in faster record retrieval.
b) Secondary Index
• Created on non-primary key columns to speed up queries on frequently searched attributes.
c) Clustered Index
• Sorts and stores table rows in the same order as the index.
• A table can have only one clustered index.
d) Non-Clustered Index
• Maintains a separate index structure from the actual data.
• A table can have multiple non-clustered indexes.
e) Composite Index
• Created on multiple columns to improve query performance on those specific columns.

Conclusion
Indexing is a crucial technique in DBMS that enhances query performance and data retrieval
efficiency. However, careful index selection and management are necessary to balance performance
and storage overhead.

Q18] How does indexing improve query performance?

Ans:-
Indexing improves query performance in a Database Management System (DBMS) by optimizing the way
data is retrieved. Here’s how it helps:
1. Faster Data Retrieval
• Without an index, the database must perform a full table scan, meaning it checks every row to find the
required data.
• With an index, the database can quickly locate data using a sorted structure (like a B-tree or hash
table), reducing search time significantly.
2. Reduced Disk I/O Operations
• Instead of scanning the entire table, the database fetches only relevant records.
• This reduces the number of disk reads and speeds up query execution.
3. Efficient Sorting and Filtering
• Indexes store data in an ordered format, making ORDER BY and GROUP BY queries much faster.
• Queries with WHERE conditions perform better as the index helps in quickly finding matching records.
4. Improved Join Performance
• Indexes on foreign keys speed up JOIN operations by quickly locating matching rows in related tables.
Example:
Consider a Students table:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
City VARCHAR(50)
);
If we frequently search for students by City, an index on City can help:
CREATE INDEX idx_city ON Students(City);
Now, queries like:
SELECT * FROM Students WHERE City = 'New York';
will run faster because the database can use the index instead of scanning the whole table.
Q20] What is a view in DBMS? Explain how views are created with an
example.
Ans:-
A view is a virtual table in a database that is based on the result of a SQL query. Unlike physical
tables, views do not store data themselves; they dynamically display data from one or more tables.

Types of views:
1 Internal View
• This view deals with physical storage, such as the location of the server and network
information for the server.
2 Conceptual View
• This view describes the logical structure and relationships of the data.
3 External View
• This view provides customized data access for individual users or groups.
4 Physical View
• This view displays the available physical resources and their location within the enterprise.
5 Logical View
• This view defines an information concept in the company data system.

How to Create a View in SQL?

Syntax:
sql
CopyEdit
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example of Simple View

We have a Students table:

Student_ID Name Age Department GPA

101 John 20 CS 3.8

102 Emma 22 IT 3.6

103 Alex 21 CS 3.9

Creating a View to Show Only CS Students:

sql
CopyEdit
CREATE VIEW CS_Students AS
SELECT Student_ID, Name, GPA
FROM Students
WHERE Department = 'CS';
Querying the View:
sql
CopyEdit
SELECT * FROM CS_Students;

✅ Output:

Student_ID Name GPA

101 John 3.8

103 Alex 3.9

Example of Complex View (Using Multiple Tables)

We have two tables:
Students Table:

Student_ID Name Dept_ID

101 John 1

102 Emma 2

Departments Table:

Dept_ID Department

1 CS

2 IT

Creating a View with JOIN:

sql
CopyEdit
CREATE VIEW Student_Department AS
SELECT Students.Student_ID, Students.Name, Departments.Department
FROM Students
JOIN Departments ON Students.Dept_ID = Departments.Dept_ID;
Dropping a View
To delete a view:
sql
CopyEdit
DROP VIEW CS_Students;

Q22] Different types of relationships in ER diagram with examples (1-1, 1-

M, M-N).
Ans:-
Different Types of Relationships in ER Diagram with Examples
In an Entity-Relationship (ER) diagram, relationships define how entities interact with each other.
There are three main types of relationships:
1. One-to-One (1:1) Relationship
o In this relationship, one entity is associated with only one other entity.
o Example: A passport is issued to only one person, and a person can have only one
passport.
2. One-to-Many (1:M) Relationship
o In this relationship, one entity is associated with multiple entities.
o Example: A teacher can teach multiple students, but each student is assigned to only
one teacher.
3. Many-to-Many (M:N) Relationship
o In this relationship, multiple entities from one set are associated with multiple entities
from another set.
o Example: Students can enroll in multiple courses, and each course can have multiple
students.
These relationships help in designing efficient database structures and maintaining data integrity. Let
me know if you need further clarification! 😊
Q23] What is data redundancy & how does normalization help in reducing
redundancy? (Explain all normal forms in short) (4-8 points).
Ans:-
Data redundancy occurs when the same piece of data is stored in multiple places within a database.
This leads to unnecessary data duplication, increased storage costs, and potential inconsistencies.
How Does Normalization Reduce Redundancy:
How Does Normalization Reduce Redundancy?
1. Eliminates Data Duplication – Ensures that the same data is not stored in multiple places,
reducing storage space.
2. Organizes Data Efficiently – Divides large tables into smaller related tables, making data
management easier.
3. Removes Partial Dependencies – Ensures that non-key attributes depend entirely on the
primary key (2NF).
4. Eliminates Transitive Dependencies – Prevents columns from depending on non-key
attributes, improving consistency (3NF).
5. Improves Data Integrity – Ensures accuracy by preventing anomalies and maintaining
consistency across tables.
6. Reduces Update Anomalies – Changes need to be made only in one place instead of multiple
occurrences.
7. Enhances Query Performance – Well-structured tables improve retrieval efficiency by
reducing redundant data scans.
8. Ensures Minimal Data Storage – Avoids unnecessary repetition, leading to optimized
database storage usage.

Q24] Explain the concept of referential integrity in databases.

Ans:-
Concept of Referential Integrity in Databases
Referential integrity is a fundamental principle in relational databases that ensures consistency and
accuracy in relationships between tables. It guarantees that a foreign key in one table always refers to a
valid primary key in another table, preventing orphaned records and maintaining data integrity.
Key Aspects of Referential Integrity:
1. Consistency: Foreign key values must match an existing primary key in the referenced table.
2. Prevention of Orphan Records: A record in a child table cannot reference a non-existent
record in the parent table.
3. Controlled Updates & Deletions: Changes to the primary key in the parent table should be
handled properly (restrict, cascade, set null/default).
4. Improved Data Reliability: Ensures that relationships remain valid over time, preventing
accidental data loss or inconsistencies.

Q25] How do foreign keys enforce referential integrity?

Ans:-
How Foreign Keys Enforce Referential Integrity?
A foreign key is a column or set of columns in a child table that establishes a link to the primary key
of a parent table. It enforces referential integrity by ensuring that relationships between tables remain
valid.
Ways Foreign Keys Enforce Referential Integrity:
1. Prevents Invalid References → A foreign key must reference an existing primary key in the
parent table.
2. Restricts Deletion (RESTRICT/NO ACTION) → Prevents deleting a parent record if child
records exist.
3. Cascade Delete (CASCADE) → Automatically deletes child records when the referenced
parent record is deleted.
4. Set NULL (SET NULL) → If the parent record is deleted, the foreign key in the child table is
set to NULL (if allowed).
5. Set Default (SET DEFAULT) → Assigns a default value to the foreign key when the parent
record is deleted.
6. Prevents Orphan Records → Ensures that every foreign key has a corresponding primary key,
avoiding broken relationships.
Q26] Define and explain different states of a transaction. How is a
transaction moved from one state to another? (Start, Resume, Pause,
Commit, Rollback diagram).
Ans:-
Different States of a Transaction in DBMS
A transaction in a database system goes through different states during its execution. The key states
are:
1. Active → The transaction starts and is being executed.
2. Partially Committed → All operations are successfully executed, but changes are not yet
saved permanently.
3. Committed → The transaction is successfully completed, and changes are permanently saved
in the database.
4. Failed → The transaction encounters an error or issue, preventing completion.
5. Aborted → The failed transaction is rolled back, and the database is restored to its previous
consistent state.
6. Terminated → The transaction has either committed or aborted and is removed from the
system.
State Transitions
• Start → Active: When a transaction begins execution.
• Active → Partially Committed: After successfully completing all operations.
• Partially Committed → Committed: When changes are permanently saved.
• Active → Failed: If an error occurs.
• Failed → Aborted: Transaction is rolled back.
• Aborted/Committed → Terminated: Transaction ends.
Q27] What is a deadlock in database transactions? Explain the techniques
used to prevent and resolve deadlocks.
Ans:-
What is a Deadlock in Database Transactions?
A deadlock occurs in a database when two or more transactions hold locks on resources and wait for
each other to release them, causing a circular wait situation where none can proceed. This results in an
indefinite wait, leading to system inefficiency.
Example of Deadlock:
• Transaction A locks Resource X and requests Resource Y.
• Transaction B locks Resource Y and requests Resource X.
• Both transactions wait indefinitely, causing a deadlock.

Techniques to Prevent Deadlocks

Deadlock prevention ensures that the system avoids deadlocks before they occur. Some methods
include:
1. Wait-Die Scheme:
o Older transactions can wait for a resource held by a younger transaction.
o Younger transactions requesting a locked resource are aborted (die) and restarted later.
2. Wound-Wait Scheme:
o Older transactions can preempt (wound) younger ones, forcing them to rollback.
o Younger transactions wait if an older transaction holds the resource.
3. Timeout-Based Approach:
o Transactions wait for a resource for a limited time. If not granted, they are aborted and
restarted.
4. Resource Ordering:
o Resources are assigned a predefined ordering, and transactions must request them in
that order, preventing circular waits.

Techniques to Resolve Deadlocks

If a deadlock occurs, the system detects and resolves it using these techniques:
1. Deadlock Detection:
o The system periodically checks for circular waits using a Wait-for Graph. If a cycle is
found, a deadlock exists.
2. Transaction Rollback:
o The system selects a transaction to abort (usually the one with the least progress) to
break the deadlock.
3. Process Termination:
o One or more transactions involved in the deadlock are forcibly terminated, releasing
locked resources.
4. Resource Preemption:
o Resources are forcibly taken from some transactions and reassigned to others, breaking
the circular wait condition.

Q28] Define the term concurrency control in the context of databases. What
are the different types of concurrency control methods?
Ans:-
Definition of Concurrency Control in Databases
Concurrency control in databases ensures that multiple transactions can execute simultaneously
without leading to inconsistencies, conflicts, or data loss. It maintains the ACID (Atomicity,
Consistency, Isolation, Durability) properties, ensuring that transactions do not interfere with each
other in multi-user environments.
For example, without concurrency control, two users modifying the same bank account balance
simultaneously could lead to incorrect updates.

Types of Concurrency Control Methods

1. Lock-Based Concurrency Control
o Uses locks to control access to data.
o Types:
▪ Shared Lock (S-lock): Allows multiple transactions to read but not write.
▪ Exclusive Lock (X-lock): Allows only one transaction to read and write.
o Two-Phase Locking (2PL): Ensures serializability by dividing transactions into
growing and shrinking phases.
2. Timestamp-Based Concurrency Control
o Assigns timestamps to transactions based on start time.
o Ensures that transactions execute in a chronological order, preventing conflicts.
3. Optimistic Concurrency Control (OCC)
o Transactions execute without locks and check for conflicts before committing.
o Best suited for systems with low contention.
o Uses three phases: Read, Validate, Write.
4. Multiversion Concurrency Control (MVCC)
o Instead of locking, it creates multiple versions of data.
o Transactions read the latest committed version, allowing concurrent reads and writes.
o Used in databases like PostgreSQL and Oracle.
5. Serializability-Based Concurrency Control
o Ensures that the final result of concurrent transactions is the same as if they had
executed sequentially.
Each method is used based on the database system’s requirements and level of concurrent
transactions.

Q30] Design a database for an online bookstore Include tables for customers,
books, orders, and payment. Provide an ER diagram and mention
relationships.
Ans:-

1. Customers Table

CustomerID Name Email

1 John Doe [email protected]

2 Jane Smith [email protected]

2. Books Table

BookID Title Price

101 The Great Gatsby 10.99

102 1984 12.50

3. Orders Table

OrderID CustomerID OrderDate

5001 1 2025-03-10

5002 2 2025-03-11
4. OrderDetails Table

OrderID BookID Quantity

5001 101 2

5002 102 1

5. Payments Table

PaymentID OrderID PaymentMethod

9001 5001 Credit Card

9002 5002 PayPal

Relationships:
1. Customers → Orders (One-to-Many)
2. Orders → OrderDetails (One-to-Many)
3. Books → OrderDetails (One-to-Many)
4. Orders → Payments (One-to-One)

Q31) Functional dependency how they are useful (advantages &

disadvantages) in database to improve data integrity.
Ans:-_
Functional Dependency in Databases
Definition:
Functional dependency means that one attribute uniquely determines another. Example:
StudentID → StudentName (Each StudentID has a unique StudentName).

Advantages:
1. Reduces Redundancy – Prevents duplicate data.
2. Improves Data Integrity – Maintains consistency.
3. Enhances Query Performance – Well-structured tables improve speed.
4. Prevents Anomalies – Avoids insertion, deletion, and update errors.
Disadvantages:
1. Complex Design – Too many tables make it complicated.
2. Slower Joins – More tables lead to slow queries.
3. Difficult Maintenance – Managing dependencies can be hard.

Q32) Explain various SQL Query commands with example.

Ans:-
1. DDL (Data Definition Language) Commands
DDL commands are used to define and manage the structure of a database.
a) CREATE – Creates a new database or table
sql
CopyEdit
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Grade VARCHAR(10)
);
b) ALTER – Modifies an existing table
sql
CopyEdit
ALTER TABLE Students ADD COLUMN Address VARCHAR(100);
c) DROP – Deletes a table or database
sql
CopyEdit
DROP TABLE Students;
d) TRUNCATE – Removes all records from a table but keeps the structure
sql
CopyEdit
TRUNCATE TABLE Students;
2. DML (Data Manipulation Language) Commands
DML commands handle data manipulation in the database.
a) INSERT – Adds new records
sql
CopyEdit
INSERT INTO Students (ID, Name, Age, Grade) VALUES (1, 'John Doe', 20, 'A');
b) UPDATE – Modifies existing records
sql
CopyEdit
UPDATE Students SET Age = 21 WHERE ID = 1;
c) DELETE – Removes records from a table
sql
CopyEdit
DELETE FROM Students WHERE ID = 1;

3. DQL (Data Query Language) Command

DQL is used to fetch data from a database.
SELECT – Retrieves data
sql
CopyEdit
SELECT * FROM Students;
sql
CopyEdit
SELECT Name, Age FROM Students WHERE Grade = 'A';

4. DCL (Data Control Language) Commands

DCL commands manage user privileges.
a) GRANT – Gives access rights to a user
sql
CopyEdit
GRANT SELECT, INSERT ON Students TO 'user1';
b) REVOKE – Removes access rights
sql
CopyEdit
REVOKE INSERT ON Students FROM 'user1';

5. TCL (Transaction Control Language) Commands

TCL commands manage transactions in a database.
a) COMMIT – Saves the transaction permanently
sql
CopyEdit
COMMIT;
b) ROLLBACK – Reverts changes made by a transaction
sql
CopyEdit
ROLLBACK;
c) SAVEPOINT – Creates a savepoint in a transaction
sql
CopyEdit
SAVEPOINT sp1;

Q33) Update, select, delete command with example.

Ans:-
1. UPDATE Command
The UPDATE command is used to modify existing records in a table. It changes the values of specific
columns for records that match a given condition.
Example:
Updating the age of a student with ID = 1 in the Students table:
sql
CopyEdit

UPDATE Students
SET Age = 21, Grade = 'A+'
WHERE ID = 1;

2. SELECT Command
The SELECT command is used to retrieve data from a table. It allows filtering, sorting, and
aggregation of records.
Example:
Retrieving all students who have grade 'A':
sql
CopyEdit
SELECT Name, Age FROM Students WHERE Grade = 'A';

3. DELETE Command
The DELETE command removes specific records from a table based on a condition.
Example:
Deleting a student with ID = 2:
sql
CopyEdit
DELETE FROM Students WHERE ID = 2;

Q34) purpose of Database System.

Ans:-
In DBMS, database systems provide a safe and effective platform to manage vast amounts of data.
Their role is to provide services like data organization, storage, and manipulation, as well as to
guarantee data integrity. A database system’s primary goal is to facilitate data retrieval and provide a
dependable storage platform for essential data.
• Efficient storage and retrieval are allowed by structured organization of data through database
systems utilizing predefined schemas and data models.
• DBMS maintains the reliability and accuracy of the information and returns it through
enforced constraints and rules defined in the database schema that eliminates data redundancy
and anomalies, respectively.
• Protecting confidential data is crucial and database systems successfully achieve this with their
safeguards against unauthorized access.
• Database systems prioritize the security of sensitive data with their solid mechanisms in place
to preserve data confidentiality.
• The inclusion of strong security measures in database systems ensures the protection of
sensitive data and upholds its confidentiality. Confidentiality and privacy of data are
maintained by utilizing resilient security measures within database systems.
• Collaboration made easy with DBMS. With the provision of a platform to access and
manipulate data, multiple users can now work together and ensure data consistency across
various applications. Data sharing and collaboration are now synonymous with the help of
DBMS.
• Data backups and transaction management are mechanisms provided by database systems to
ensure data durability. Safeguarding data against system crashes and failures is their main
priority.

Q35) Name any 2 database language.

Ans:- refer answer of question Q2)

Q36) What is relational database schema

Ans:-
Relation schema defines the design and structure of the relation or table in the database. It is the way
of representation of relation states in such a way that every relation database state fulfills the integrity
constraints set (Like Primary key, Foreign Key, Not null, Unique constraints) on a relational schema.
It consists of the relation name, set of attributes/field names/column names. every attribute would have
an associated domain. In this article, we will discuss relational schema in detail along with proper
examples.
Components of a Relation Schema
• Relation Name: Name of the table that is stored in the database. It should be unique and
related to the data that is stored in the table. For example- The name of the table can be
Employee store the data of the employee.
• Attributes Name:Attributes specify the name of each column within the table. Each attribute
has a specific data type.
• Domains: The set of possible values for each attribute. It specifies the type of data that can be
stored in each column or attribute, such as integer, string, or date.
• Primary Key: The primary key is the key that uniquely identifies each tuple. It should be
unique and not be null.
• Foreign Key: The foreign key is the key that is used to connect two tables. It refers to the
primary key of another table.
• Constraints: Rules that ensure the integrity and validity of the data. Common constraints
include NOT NULL, UNIQUE, CHECK, and DEFAULT.
The relation schema defines the structure of data stored in a database, specifying attributes and
relationships.
Example of Relation Schema
There is a student named Geeks, she is pursuing B.Tech, in the 4th year, and belongs to the IT
department (department no. 1) and has roll number 1601347 Mrs. S Mohanty proctors her. If we want
to represent this using databases we would have to create a student table with name, sex, degree, year,
department, department number, roll number, and proctor (adviser) as the attributes.
Student Table
student (rollNo, name, degree, year, sex, deptNo, advisor)