DBMS – 5 Marks Questions (30)

1. Define DBMS. Explain any three advantages over a file system.

ANS:

Definition of DBMS:

A Database Management System (DBMS) is a software system that allows users to create, store, organize,
retrieve, and manage data efficiently. It provides a systematic and secure way of handling large amounts of data
while ensuring data integrity, consistency, and controlled access.

Advantages of DBMS over File System (Any Three):

1. Reduced Data Redundancy

In file systems, the same data may be stored in multiple places, leading to duplication.
DBMS minimizes redundancy by using a centralized database and normalization techniques.

2. Improved Data Security

DBMS provides controlled access using authentication, authorization, and user privileges.
Sensitive data can be protected more effectively than in traditional file systems.

3. Data Consistency

Since DBMS reduces redundancy and enforces constraints (like primary key, foreign key, etc.),
the data remains accurate and consistent throughout the system.

2. What are the different types of data models in DBMS?

ANS:
In DBMS, a data model is a way to represent and organize data. The main types are:

1. Hierarchical Data Model – Represents data in a tree structure with parent–child relationships. Each
child has only one parent.
2. Network Data Model – Uses a graph structure allowing many-to-many (multiple parent and child)
relationships.
3. Relational Data Model – Stores data in tables (rows and columns). It is the most widely used model.
4. Entity-Relationship (ER) Model – A conceptual model using entities, attributes, and relationships,
mainly for designing databases.
5. Object-Oriented Data Model –Represents data as objects similar to OOP concepts like classes and
inheritance.
3. Explain the components of the three-schema architecture.
ANS:
The Three-Schema Architecture in DBMS separates the database into three levels to achieve data
independence. Its components are:

1. Internal Schema (Physical Level)

o Describes how data is physically stored in the database.
o Includes file structures, indexes, record formats, and access paths.
2. Conceptual Schema (Logical Level)
o Represents the overall logical structure of the entire database.
o Defines entities, attributes, relationships, constraints, and data types.
o Hides physical storage details and provides a unified view for all users.
3. External Schema (View Level)
o Provides different views of the database to different users or applications.
o Ensures security by showing only the required portion of data.
o Multiple external schemas can exist for the same database.

4. Differentiate between database administrator (DBA) and database

users.
ANS:

DATA ADMINISTRATOR DATABASE ADMINISTRATOR

Database admin is also known as database

Data admin is also known as data analyst.
coordinator or database programmer.

Data admin converts data into a

Database admin inputs data into the database.
convenient data model.

Data admin analyzes the database for Database admin optimizes and maintains the
relevant data. database.

Data admin monitors data flow across the

Database admin ensures database security.
organization.

Data admin handles issues concerning the Database admin handles issues with the
data. database.

Data admin requires excellent data

Database admin mostly require logical thought
analyzing, expression of ideas, and
process, troubleshooting and will to learn.
strategic thinking.
DATA ADMINISTRATOR DATABASE ADMINISTRATOR

Data admin is less of a technical role and Database admin is a wide role as it has multiple
more of a business role. responsibilities

Main tasks include data planning, Main tasks include database design,
definition, architecture and management construction, security, backup and recovery,
etc. performance tuning etc.

It set policies and standards, coordinates It enforces policies and procedures, choose and
and manages database design. maintains technology.

Generally it owns the data. Where as it owns the database.

It performs the high level function. It performs the technical function.

Data administration is DBMS independent. Database administration is DBMS specific.

5. What is an Entity? Describe its types with examples.

ANS:
An entity is a real-world object or concept that can be uniquely identified and about which data is stored in a
database. Entities represent things such as people, places, objects, or events that have attributes.

Types of Entities:

1. Strong Entity
o Exists independently and has its own primary key.
o Example: Student, identified by Student_ID.
2. Weak Entity
o Cannot exist without a strong entity and does not have a primary key of its own.
o Identified using a partial key + strong entity key.
o Example: Dependent of an Employee.
3. Tangible Entity
o Physical, real-world objects.
o Example: Car, Building, Book.
4. Intangible Entity
o Abstract concepts with no physical form.
o Example: Course, Account, Project.
6. Write short notes on (i) Keys (ii) Mapping constraints.
ANS: (i) Keys

Keys are attributes used to uniquely identify records in a table. They help maintain data integrity and support
relationships between tables.

Types of keys:

 Primary Key: Uniquely identifies each record.

 Candidate Key: Possible choices for primary key.
 Super Key: Any attribute set that uniquely identifies a record.
 Foreign Key: Establishes link between two tables.
 Composite Key: Key formed with multiple attributes.

Keys prevent duplicates and ensure reliable data retrieval.

(ii) Mapping Constraints

Mapping constraints specify how many entities from one set can be associated with entities from another in an
E-R model.

Types:

 One-to-One (1:1) – One entity relates to one entity.

 One-to-Many (1:M) – One entity relates to many entities.
 Many-to-One (M:1) – Many entities relate to one entity.
 Many-to-Many (M:N) – Many entities interact with many.

7. Explain weak entity sets with an example E-R diagram.

ANS:
Here’s a perfect 5-mark answer:

Explain weak entity sets with an example E-R diagram.

A weak entity set is an entity that cannot be uniquely identified by its own attributes alone. It depends on a
strong entity set for its identification. A weak entity does not have a primary key; instead, it has a partial key
and requires the primary key of the strong entity to form a unique identifier.

Characteristics of Weak Entity Sets:

 Exist only with the support of a strong entity.

 Represented with double rectangles in an E-R diagram.
 Their identifying relationship is shown using a double diamond.
 Have partial keys (dashed underline).
Example:

Consider Employee (strong entity) and Dependent (weak entity).

 Employee has attributes: Emp_ID, Name.

 Dependent has attributes: Dependent_Name, Age (partial key: Dependent_Name).
 Dependent depends on Employee for identification.

E-R Diagram (Text Representation):

8. Define Relational Algebra. Explain any two operations.

ANS:
Relational Algebra is a procedural query language used in DBMS to retrieve and manipulate data stored in
relations (tables). It uses a set of mathematical operations to produce new relations from existing ones. It forms
the theoretical foundation for SQL and helps in query optimization.

Two Operations:

1. Selection (σ)

 Used to choose rows (tuples) that satisfy a given condition.

 It filters data horizontally.
 Example:
σ salary > 50000 (Employee)
→ returns only those employees whose salary is above 50,000.
2. Projection (π)

 Used to choose specific columns (attributes) from a relation.

 It filters data vertically.
 Example:
π name, department (Employee)
→ returns only the name and department of employees.

9. What is a view? Mention its advantages.

ANS:
A view is a virtual table in a database that is created using a query on one or more base tables. It does not store
data physically; instead, it displays data dynamically from underlying tables whenever accessed.

Advantages of Views:

1. Improved Security:
Sensitive data can be hidden by giving users access only to specific columns through a view.
2. Simplifies Complex Queries:
Complicated joins and calculations can be stored as a view, making queries easier for users.
3. Provides Customized User Views:
Different users can see different representations of the same data.
4. Ensures Data Independence:
Changes in base tables do not affect the view definition as long as the structure is consistent.
5. Automatic Update of Data:
Since views reflect data from base tables, the displayed information is always up to date.

10. Briefly explain different types of E-R attributes.

ANS:
E-R attributes represent the properties or characteristics of an entity in an ER model. They help describe and
define an entity clearly.

Types of E-R Attributes:

1. Simple Attribute:
o Cannot be divided into smaller components.
o Example: Age, Emp_ID.
2. Composite Attribute:
o Can be broken into smaller sub-attributes.
o Example: Address → Street, City, Pincode.
3. Derived Attribute:
o Calculated from other stored attributes.
 o
Example: Age derived from Date_of_Birth.
4. Multivalued Attribute:
o Has multiple values for a single entity.
o Represented by double oval in ER diagram.
o Example: Phone Numbers, Skills.
5. Key Attribute:
o Uniquely identifies each entity in a set.
o Example: Roll_No, Employee_ID.

11. What is a graph database? State two differences from relational DB.
ANS:
A graph database is a type of NoSQL database that stores data using graph structures—nodes (entities), edges
(relationships), and properties. It is designed to efficiently handle highly connected data and complex
relationships through graph traversal.

Differences from Relational Database:

1. Data Representation:
o Graph DB: Uses nodes and edges to directly store relationships.
o Relational DB: Stores data in tables using rows and columns; relationships via foreign keys.
2. Query Performance:
o Graph DB: Faster for relationship-heavy queries (friend-of-friend, network paths).
o Relational DB: Slower for complex joins as relationships require multiple tables.

12. Define nodes, edges and properties in a graph model.

ANS:
In a graph database model, data is represented using three main components: nodes, edges, and properties,
which together form a flexible and connected structure.

1. Nodes:

 Represent entities or objects in the graph.

 Similar to rows in a relational table.
 Example: A node can represent a Person, Product, or City.

2. Edges:

 Represent relationships between nodes.

 Show how two entities are connected.
 Can be directed or undirected.
 Example: FRIENDS_WITH, PURCHASED, LOCATED_IN.
3. Properties:

 Key–value pairs that store information about nodes or edges.

 Add descriptive details.
 Example: Node property → name = "Ritu"; Edge property → since = 2020.

13. Write a short note on Neo4j and its applications.

ANS:
Neo4j is a widely used graph database that stores data as nodes, edges, and properties. It follows the property
graph model and uses Cypher Query Language (CQL) for pattern-matching queries. Neo4j is ACID-
compliant, highly efficient for relationship-based data, and supports fast graph traversal, making it suitable for
complex, interconnected datasets.

Applications of Neo4j:

1. Social Networks: Manages users, connections, and recommendations.

2. Fraud Detection: Identifies suspicious patterns by analyzing linked transactions.
3. Recommendation Systems: Suggests products or content using relationship patterns.
4. Knowledge Graphs: Organizes interconnected information for search and AI systems.
5. Network Management: Maps devices, dependencies, and system relationships.

14. Explain the role of CQL (Cypher Query Language).

ANS:
Cypher Query Language (CQL) is the declarative query language used in Neo4j for working with graph
databases. It is designed to express graph patterns in an intuitive, readable way using ASCII-art–like syntax.
CQL allows users to create, update, delete, and retrieve nodes and relationships efficiently.

Role of CQL:

1. Pattern Matching:
Helps to search for specific node–relationship patterns in the graph using simple syntax.
2. Data Manipulation:
Supports creating nodes, establishing relationships, and updating or deleting graph data.
3. Data Retrieval:
Extracts connected data through graph traversal, making complex queries easy.
4. Schema Management:
Allows creation of constraints, indexes, and labels for optimizing database performance.
5. User-Friendly Querying:
Its expressive, human-readable format makes graph queries easier compared to SQL joins.
15. Differentiate between DDL, DML and DCL with one example each.
ANS:
In SQL, commands are grouped into categories based on their purpose: DDL, DML, and DCL. Each serves a
different role in managing database structure, data, and user permissions.

1. DDL (Data Definition Language)

 Used to define or modify the structure of the database.

 Includes creating, altering, and deleting tables.
 Example:
CREATE TABLE Student (RollNo INT, Name VARCHAR(20));

2. DML (Data Manipulation Language)

 Used to manipulate data stored in tables.

 Includes inserting, updating, deleting, and retrieving records.
 Example:
INSERT INTO Student VALUES (1, 'Ritu');

3. DCL (Data Control Language)

 Used to control access and permissions for users.

 Helps manage security of the database.
 Example:
GRANT SELECT ON Student TO user1;

16. Explain referential integrity constraint with an example.

ANS:
Referential Integrity Constraint:

 It ensures that a foreign key in one table must either match a primary key in another table or be
NULL. This maintains consistency between related tables.
 It prevents ―orphan records,‖ i.e., foreign key values that do not correspond to any primary key in the
referenced table.

Example:

 Tables:
1. Student(StudentID, Name, ClassID)
2. Class(ClassID, ClassName)
 Constraint: [Link] is a foreign key referencing [Link].
 Implication: You cannot insert a Student record with ClassID = 10 if no Class with ClassID = 10
exists.
 Benefit: Maintains consistency between Student and Class tables.
17. What is a nested subquery? Provide one example.
ANS:
A nested subquery is a SQL query written inside another query. The inner query executes first and its result
is used by the outer query. It is used to solve complex queries in a single statement.

Example:

SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

 Here, the inner query (SELECT AVG(Salary) FROM Employees) calculates the average salary.
 The outer query retrieves employees earning more than this average.

18. Write a short note on triggers.

ANS:
A trigger is a special kind of stored procedure in a database that automatically executes when a specific event
occurs on a table or view. Triggers are used to enforce rules, maintain data integrity, or audit changes.

Key Points:

 Executed automatically on INSERT, UPDATE, or DELETE operations.

 Can be BEFORE (before the operation) or AFTER (after the operation).
 Helps in validation, logging, or cascading actions.

Example:

CREATE TRIGGER trg_salary_check

BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END;

 This trigger prevents inserting a negative salary in the Employees table.

19. Define aggregate functions with examples.

ANS:
Aggregate functions in SQL perform calculations on a set of values and return a single summary value. They
are commonly used with the GROUP BY clause.

Common Aggregate Functions with Examples:

1. SUM() – Calculates the total of a column.

SELECT SUM(Salary) AS TotalSalary FROM Employees;

2. AVG() – Calculates the average of a column.

SELECT AVG(Salary) AS AvgSalary FROM Employees;

3. COUNT() – Counts the number of rows.

SELECT COUNT(EmpID) AS EmployeeCount FROM Employees;

4. MAX() – Finds the maximum value.

SELECT MAX(Salary) AS HighestSalary FROM Employees;

5. MIN() – Finds the minimum value.

SELECT MIN(Salary) AS LowestSalary FROM Employees;

20. What is a functional dependency? Give one example.
ANS:
A functional dependency in a database exists when the value of one attribute (or a set of attributes) uniquely
determines the value of another attribute. It is denoted as:

X→YX \rightarrow YX→Y

Here, X determines Y, meaning for each value of X, there is exactly one corresponding value of Y.

Example:
In a table Students(StudentID, Name, DeptID):

 StudentID → Name
This means each StudentID uniquely determines a Name.

Key Point:
Functional dependencies are used in normalization to reduce redundancy and maintain data integrity.

21. Explain the concept of decomposition.

ANS:
Decomposition is the process of breaking a relation (table) into two or more smaller relations to remove
redundancy, avoid anomalies, and achieve normalization in a database.

Key Points:

 Ensures that data integrity is maintained.

 Helps in reducing update, insert, and delete anomalies.
 Can be lossless (no data is lost) or lossy (data may be lost).

Example:
Suppose we have a table StudentCourses(StudentID, StudentName, CourseID, CourseName).

 This table can be decomposed into:

1. Students(StudentID, StudentName)
2. Courses(CourseID, CourseName)
3. Enrollments(StudentID, CourseID)

This removes redundancy (e.g., storing StudentName multiple times) and preserves consistency.

22. What are anomalies? List and explain any two.

ANS:
An anomaly is an inconsistency or problem that arises in a database when data is redundant or poorly
structured. They usually occur in unnormalized tables.

Types of Anomalies (Any Two):

1. Insertion Anomaly:
o Occurs when certain data cannot be inserted into the table without the presence of other data.
o Example: In a table StudentCourses(StudentID, StudentName, CourseID, CourseName) ,
you cannot add a new course if no student is enrolled yet.
2. Deletion Anomaly:
o Occurs when deleting some data causes unintended loss of other important data.
o Example: Deleting the only student enrolled in a course also removes information about that
course.

Other anomalies include Update Anomaly, where changes in one place require multiple updates.

These anomalies are resolved by normalization and proper table design.

23. Differentiate between 3NF and BCNF.

ANS:
Feature 3NF (Third Normal Form) BCNF (Boyce-Codd Normal Form)
A relation is in 3NF if it is in 2NF and no non-
A relation is in BCNF if it is in 3NF and
Definition prime attribute is transitively dependent on the
every determinant is a candidate key.
primary key.
 Feature 3NF (Third Normal Form) BCNF (Boyce-Codd Normal Form)
Handling of Removes most update, insertion, and deletion Completely removes all redundancy
anomalies anomalies, but some cases may remain. caused by functional dependencies.
Dependency Non-prime attribute must not depend on another Every functional dependency X → Y
Condition non-prime attribute. must have X as a candidate key.
Stricter Form Less strict than BCNF. Stricter than 3NF.
Table with a transitive dependency is 3NF if non- Table violating BCNF (even if 3NF)
Example
prime attributes don’t violate 3NF rules. must be decomposed to satisfy BCNF.

24. Write short notes on multivalued dependencies.

ANS:
A multivalued dependency (MVD) occurs when, in a relation, one attribute determines multiple
independent values of another attribute, regardless of other attributes. It is denoted as:

X↠YX \twoheadrightarrow YX↠Y

This means for a single value of X, there can be multiple independent values of Y.

Key Points:

 MVDs often cause redundancy in tables.

 Used in Fourth Normal Form (4NF) to remove redundancy.
 Different from functional dependency because Y is independent of other attributes.

Example:
In a table StudentHobbiesLanguages(StudentID, Hobby, Language):

 StudentID →→ Hobby
 StudentID →→ Language
Here, hobbies and languages are independent but associated with the same student.

Solution: Decompose into two tables:

1. StudentHobbies(StudentID, Hobby)
2. StudentLanguages(StudentID, Language)

This removes redundancy and maintains data integrity.

25. What is a join algorithm? Briefly describe nested loop join.

ANS:
A join algorithm is a method used by a database system to combine rows from two or more tables based on a
related column. It determines how efficiently the join operation is executed.
Nested Loop Join:

 The simplest join algorithm.

 Works by taking each row of the first table (outer table) and comparing it with every row of the
second table (inner table) to find matching rows.
 Steps:
1. For each row in Table A (outer),
2. Scan all rows in Table B (inner),
3. Output rows that satisfy the join condition.
 Advantage: Simple and works for any join condition.
 Disadvantage: Slow for large tables (O(n × m) time complexity).

Example:
Joining Employees and Departments on DeptID:

SELECT [Link], [Link]

FROM Employees E, Departments D
WHERE [Link] = [Link];

Here, a nested loop join would compare each employee with each department to find matches.

26. What is two-phase locking?

ANS:
Two-Phase Locking (2PL) is a concurrency control protocol in DBMS that ensures serializability of
transactions by managing locks on data items.

Key Points:

 Each transaction goes through two phases:

1. Growing Phase: Transaction acquires all the locks it needs (read or write locks).
2. Shrinking Phase: Transaction releases locks and cannot acquire new locks.
 Ensures that transactions are executed safely without conflicts, preventing issues like lost updates or
dirty reads.

Example:
Transaction T1 wants to read and update AccountBalance:

1. Acquire read lock on AccountBalance.

2. Acquire write lock on AccountBalance.
3. Perform update.
4. Release all locks (shrinking phase).

Key Benefit: Guarantees conflict-serializable schedules in multi-transaction environments.

27. Define deadlock. Mention its prevention techniques.

ANS:
A deadlock occurs in a database when two or more transactions are waiting indefinitely for resources locked
by each other, and none can proceed.

Prevention Techniques:

1. Wait-Die Scheme:
o Older transaction waits for a younger one; younger transaction requesting a lock held by older is
aborted.
2. Wound-Wait Scheme:
o Older transaction forces younger transaction to abort if it requests a resource held by the older
one; younger waits otherwise.
3. Resource Ordering:
o Assign a fixed order to resources and require transactions to request locks in that order to avoid
circular waiting.
4. Timeout:
o If a transaction waits too long for a resource, it is rolled back to prevent deadlock.

Key Point: Deadlock prevention ensures smooth transaction execution and avoids indefinite waiting.

28. What is transaction? Explain ACID properties.

ANS:
A transaction is a unit of work in a database that is executed as a whole. It may involve one or more
operations like INSERT, UPDATE, DELETE, and must either complete fully or not at all.

ACID Properties:

1. Atomicity:
o The transaction is all or nothing. If any part fails, the whole transaction is rolled back.
2. Consistency:
o Ensures the database remains in a valid state before and after the transaction.
3. Isolation:
o Concurrent transactions do not interfere with each other; results are as if transactions executed
serially.
4. Durability:
o Once a transaction is committed, its changes are permanent, even in case of system failure.

Example:
Transferring money between two accounts:

 Debit from one account and credit to another must both succeed (Atomicity).
 Balance constraints must be maintained (Consistency).
 Other transactions cannot see intermediate states (Isolation).
 Committed changes are permanent (Durability).
29. Define primary index and secondary index.
ANS:
1. Primary Index:

 An index created on a primary key or a unique field of a table.

 Data file is usually sorted on the primary key.
 Each key value appears only once, so it is unique.
 Example: Index on EmpID in Employees table.

2. Secondary Index:

 An index created on a non-primary key column.

 Key values may repeat, so it is non-unique.
 Speeds up searches on columns other than the primary key.
 Example: Index on DeptID in Employees table.

Key Difference:

 Primary index → unique, based on sorted primary key.

 Secondary index → may be non-unique, based on other attributes.

30. What are multilevel indexes? Mention advantages.

ANS:
A multilevel index is an indexing technique where a large index is divided into multiple levels to reduce
search time.

 The first level (top) contains a summary of the second level, the second level contains a summary of
the third, and so on.
 It works like a tree structure, helping in faster access to records.

Advantages:

1. Faster search: Reduces the number of disk accesses compared to single-level indexes.
2. Efficient for large tables: Handles very large datasets effectively.
3. Scalable: Can add more levels as the database grows.
4. Reduces storage: Only key pointers are stored at higher levels.

Example:
For a table with 1 million records, a single-level index may require scanning 1000 entries, but a two-level index
may reduce this to just 50 entries.