SabkaCode/Papers/Database Management System

MBA · Semester III · Examination 2025–26

Database Management System — Complete Question Paper Solution

Section-wise theory answers with definitions, detailed explanations, examples, comparison tables and flowcharts for exam preparation.

Section A

7 short answers · 14 marks

Section B

5 alternatives · attempt any 3

Section C

Questions 3–7 · both options

Section A · 14 marks

Short-answer questions

Original question labels 1(a)–1(g) are retained.

Section A · 1(a)2 marks · Exam-oriented answer

Define Data, Database and Database Management System (DBMS).

Data consists of raw facts, figures, symbols or observations that can be processed to obtain meaning. A database is an organized collection of related data stored so that it can be accessed, updated and managed efficiently. A Database Management System (DBMS) is software that enables users and applications to define, create, query, update and administer databases.

Main functions of a DBMS

A DBMS provides data definition, data manipulation, query processing, transaction management, concurrency control, security, integrity enforcement, backup and recovery. It also reduces unnecessary duplication and supports controlled sharing among multiple users. The database is the organized data; the DBMS is the software that manages it.

Suitable example

In a college database, student records, course details and marks are stored in tables. A DBMS such as MySQL or PostgreSQL allows authorized staff to add records, search students and generate results.

Section A · 1(b)2 marks · Exam-oriented answer

What do you mean by a data model?

A data model is a collection of concepts used to describe how data is structured, related, constrained and manipulated in a database. It provides a blueprint for database design and helps designers communicate the requirements of an information system before implementation.

Types of data models

The conceptual model describes entities and relationships at a high level, often through an Entity–Relationship (ER) model. The logical model describes structures such as relational tables, keys and constraints. The physical model specifies implementation details such as indexes, file organization and storage. Common database models include relational, hierarchical, network, object-oriented and document models.

Suitable example

A university model may define Student(StudentID, Name) and Course(CourseID, Title), with an Enrollment relationship connecting students and courses.

Section A · 1(c)2 marks · Exam-oriented answer

Define primary key with an example.

A primary key is a column or a set of columns selected to uniquely identify each row in a table. It must contain unique values and cannot contain NULL values. A table has one primary-key constraint, although that key may consist of multiple columns; such a key is called a composite primary key.

Importance

Primary keys prevent duplicate row identities and allow tables to be linked reliably through foreign keys. A good primary key should be stable, minimal and unique. The DBMS enforces its uniqueness and non-null requirements when the constraint is correctly defined.

Suitable example

In Student(StudentID, Name, Programme), StudentID can be the primary key because each student receives a unique identifier. Two students may share a name, but they must not share the same StudentID.

Section A · 1(d)2 marks · Exam-oriented answer

What is the syntax of the INSERT clause used in SQL?

The SQL INSERT statement adds one or more new rows to a table. Inserting with an explicit column list is generally safer because the values are mapped to named columns rather than relying on the table's physical column order.

Syntax

The basic form is INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);. The number and data types of values must be compatible with the specified columns. Character values are generally enclosed in single quotes, numeric values are written without quotes, and NULL is written without quotation marks when a null value is intended.

Suitable example

Example: INSERT INTO Student (StudentID, Name) VALUES (101, 'Asha'); This inserts a student with ID 101 and name Asha, assuming the table permits the other columns to be omitted.

Section A · 1(e)2 marks · Exam-oriented answer

Describe relational calculus.

Relational calculus is a non-procedural formal query language for relational databases. It specifies what data is required rather than the sequence of operations used to obtain it. It is based on predicate logic and forms an important theoretical foundation for relational query languages.

Types

Tuple Relational Calculus (TRC) uses tuple variables to represent rows, while Domain Relational Calculus (DRC) uses variables representing individual attribute values. A query consists of a logical condition that identifies the desired tuples or values. Safe expressions are important so that a query returns a finite result based on the database domain.

Suitable example

Conceptually, a query asking for the names of students whose marks exceed 80 specifies the condition Marks > 80 and returns matching names, without prescribing a step-by-step execution procedure.

Section A · 1(f)2 marks · Exam-oriented answer

What do you mean by a transaction in databases?

A database transaction is a logical unit of work consisting of one or more database operations that should be treated as a single unit. A transaction may read or update data and is normally completed by COMMIT or cancelled using ROLLBACK. Transaction management protects data consistency when operations succeed, fail or overlap with other users.

Transaction properties

Transactions are commonly explained through ACID properties: Atomicity, Consistency, Isolation and Durability. These properties help ensure that partial updates are not left behind, database rules remain satisfied, concurrent operations are controlled and committed results survive failures.

Suitable example

When money is transferred between two accounts, the debit from one account and credit to the other should succeed together. If either operation fails, the transaction should be rolled back to avoid losing money.

Section A · 1(g)2 marks · Exam-oriented answer

Discuss data mining.

Data mining is the process of discovering useful patterns, relationships, trends and anomalies from large datasets using statistical methods, machine learning and database techniques. It converts stored data into information that can support business planning, forecasting and decision-making.

Common techniques and applications

Classification predicts categories, clustering groups similar records, association-rule mining discovers items that frequently occur together, regression estimates numeric values, and anomaly detection identifies unusual cases. Applications include customer segmentation, fraud detection, market-basket analysis, demand forecasting and quality improvement. Results must be validated and interpreted in context.

Suitable example

A supermarket may analyse transaction data and discover that customers who buy pasta often also buy pasta sauce. It can use this pattern to plan product placement or promotions, while checking that the pattern is stable and commercially useful.

Section B · 21 marks

Detailed theory answers

All five options are included; the paper asks you to attempt any three.

Section B · 2(a)7 marks · Exam-oriented answer

Describe the evolution of database systems.

Database systems evolved as organizations needed more reliable, scalable and flexible ways to store and process growing amounts of information. Each stage addressed limitations of earlier approaches, although older systems continue to be used where they suit the workload.

1. File-based systems

Early applications stored data in separate files managed by individual programs. This approach was simple, but often caused data duplication, inconsistency, program–data dependence, weak sharing and difficult security or recovery. Changing a file format could require changes to several applications.

2. Hierarchical and network databases

Hierarchical databases organized records in a tree-like parent–child structure. Network databases allowed more complex links between records. These models could be efficient for known access paths, but application development and changes could become complex because relationships were closely tied to the navigational structure.

3. Relational databases

The relational model represents data as tables of rows and columns, with keys and constraints expressing relationships. SQL made querying and updating data more declarative. Normalization helped reduce redundancy, while transactions and integrity rules supported reliable multi-user applications. Relational systems became widely used in banking, administration, commerce and enterprise software.

4. Object-oriented and object-relational databases

Object-oriented databases were developed to store complex objects more directly, including identity and object relationships. Object-relational systems extend relational databases with selected object-oriented features, user-defined types and richer data types. These approaches can help with specialized applications, though the relational model remains common for business data.

5. Distributed and parallel databases

As organizations and users became geographically distributed, databases began operating across multiple sites or machines. Distributed systems can improve availability, locality and scalability, but must handle network failures, replication, consistency and coordination. Parallel database processing can divide work across processors to improve performance.

6. NoSQL and modern cloud databases

NoSQL systems include key–value, document, wide-column and graph databases. They support data models and scaling approaches suited to particular workloads, such as flexible documents, large event streams or relationship traversal. Cloud-managed databases reduce some infrastructure administration and can scale on demand, while introducing cost, governance, portability and shared-responsibility considerations.

7. New directions

Modern data architectures include in-memory databases, streaming systems, lakehouses, time-series databases and AI-supported data services. Selection depends on transaction requirements, data shape, latency, consistency, scale, skills, cost and regulatory obligations—not simply on which technology is newest.

Flowchart / Process

File-based processing

Hierarchical and network models

Relational DBMS and SQL

Object and distributed databases

NoSQL and cloud-managed databases

Modern analytics and specialized data platforms

Suitable example

A small shop may begin with spreadsheets, move to a relational database for orders and inventory, and later use a cloud data warehouse to analyse sales across many branches.

Conclusion

Database systems have progressed from isolated files to relational, distributed, NoSQL and cloud platforms. The evolution reflects changing requirements for data independence, integrity, availability, scalability and analytics.

Section B · 2(b)7 marks · Exam-oriented answer

Write down the basics of the relational model. How can E/R diagrams be converted into relations?

The relational model organizes data into relations, commonly represented as tables. Each relation has attributes (columns) and tuples (rows). A domain defines the permitted values of an attribute. Relational design uses keys and integrity constraints to identify rows and maintain valid relationships.

Core concepts of the relational model

A relation has a name and a set of attributes; each tuple contains one value for each attribute, subject to the model and DBMS rules. A candidate key uniquely identifies a tuple, a primary key is the selected candidate key, and a foreign key references a key in another relation. Entity integrity requires primary-key values to be non-null; referential integrity requires foreign-key values to match a referenced key or be NULL where permitted. Relational operations include selection, projection, union, difference, Cartesian product and join.

1. Strong entity

Create one relation for each strong entity. Include its simple attributes and choose its key as the primary key. Composite attributes are usually represented by their component attributes, while derived attributes are often calculated rather than stored unless there is a justified reason to materialize them.

2. Weak entity

Create a relation for a weak entity containing its own attributes and the primary key of its owner entity as a foreign key. The primary key usually combines the owner's key with the weak entity's partial key.

3. One-to-one relationship

For a 1:1 relationship, place one entity's primary key as a foreign key in the other relation, preferably on the side with total participation when that avoids unnecessary NULLs. Add a UNIQUE constraint to preserve the one-to-one cardinality. Relationship attributes can be stored with the relation containing the foreign key.

4. One-to-many relationship

For a 1:N relationship, place the primary key of the 'one' side as a foreign key in the relation on the 'many' side. For example, Department(DeptID, DeptName) and Employee(EmpID, Name, DeptID) represent one department having many employees.

5. Many-to-many relationship

For an M:N relationship, create a separate relation containing the primary keys of both participating entities as foreign keys. Their combination commonly forms the primary key, and any attributes belonging to the relationship are stored in this new relation. For example, Enrollment(StudentID, CourseID, EnrolDate) links students and courses.

6. Multivalued attributes and specialization

A multivalued attribute normally becomes a separate relation containing the owner's primary key and the attribute value. For specialization/generalization, a common mapping creates a relation for the superclass and separate relations for subclasses whose primary keys also reference the superclass. Other mappings are possible depending on disjointness, completeness and query requirements.

Flowchart / Process

Identify entities, attributes and keys in ER diagram

Create relations for strong entities

Map weak entities with owner keys

Map 1:1 and 1:N relationships using foreign keys

Create separate relation for M:N relationships

Map multivalued attributes and subclasses

Add keys, constraints and validate design

Suitable example

ER entities Student(StudentID, Name) and Course(CourseID, Title) have an M:N relationship because each student can take many courses and each course can have many students. Convert it into Student(StudentID, Name), Course(CourseID, Title) and Enrollment(StudentID, CourseID, Semester), where Enrollment has foreign keys to Student and Course.

Conclusion

The relational model represents data in tables linked through keys. ER-to-relational mapping converts entity types into relations and relationship cardinalities into foreign keys or separate relationship tables while preserving constraints.

Section B · 2(c)7 marks · Exam-oriented answer

Discuss the extended operators of relational algebra.

Relational algebra is a procedural query language that describes operations on relations and produces relations as results. Basic operators include selection, projection, union, set difference, Cartesian product and rename. Extended operators make complex queries more concise and expressive, especially when dealing with joins, aggregation and grouped results.

1. Join

A join combines tuples from two relations when a specified condition is satisfied. Theta join uses a general comparison condition; equijoin uses equality; natural join automatically matches attributes with the same names and removes duplicate copies of the matching columns. Outer joins preserve unmatched tuples by filling missing-side attributes with NULL-like values in systems that support them.

2. Intersection

Intersection returns tuples that occur in both union-compatible relations. If relation A contains students enrolled in Database and relation B contains students enrolled in Networking, A ∩ B returns students appearing in both sets.

3. Division

Division is useful for 'for all' queries. If R(Student, Course) records enrolments and S(Course) contains a set of required courses, R ÷ S returns students associated with every course in S, subject to the precise schemas and relational-algebra definition.

4. Assignment and rename

Assignment stores an intermediate relational expression under a temporary relation name, helping break a complex query into readable steps. Rename changes a relation name or attribute names and is useful when the same relation participates more than once in a query.

5. Aggregation and grouping

Extended relational algebra commonly includes aggregate functions such as COUNT, SUM, AVG, MIN and MAX, often combined with grouping. For example, grouping Employee records by Department and computing AVG(Salary) gives the average salary for each department. The exact notation differs among textbooks and implementations.

6. Outer join and generalized projection

Left outer join retains every tuple from the left relation even when no match exists on the right; right outer join does the reverse, and full outer join preserves unmatched tuples from both sides. Generalized projection allows expressions such as Salary × 1.10 or computed attributes to be included in the result.

Operator	Purpose	Example use
Join	Combine related tuples	Employees with department names
Intersection	Find common tuples	Students in both selected groups
Division	Express 'for every' conditions	Students taking all required courses
Aggregation	Calculate summaries	Average salary by department
Outer join	Keep unmatched rows	All departments including those with no employees

Suitable example

To find department-wise average salary, conceptually group Employee tuples by DeptID and apply AVG(Salary). To display departments even when no employee exists, use an appropriate outer join between Department and Employee.

Conclusion

Extended relational-algebra operators reduce the complexity of queries involving multiple relations, set comparisons, 'for all' conditions, computed attributes and summary calculations.

Section B · 2(d)7 marks · Exam-oriented answer

Explain the process of normalization up to BCNF with examples.

Normalization is the systematic process of organizing relational tables to reduce undesirable redundancy and prevent insertion, update and deletion anomalies. It uses functional dependencies and key analysis to decompose relations into better-structured tables. A good decomposition should preserve information through a lossless join; dependency preservation is also desirable where possible.

Functional dependency

A functional dependency X → Y means that if two rows agree on attributes X, they must also agree on attributes Y. Candidate keys are minimal attribute sets that determine all attributes in a relation. Normal forms use these dependencies to identify and remove specific kinds of redundancy.

First Normal Form (1NF)

A relation is in 1NF when each attribute value is atomic in the context of the chosen relational model and repeating groups are not stored as multiple values in one cell. For example, storing PhoneNumbers as '9876, 8765' in one field should be replaced by separate rows in a related StudentPhone table when multiple phone numbers must be represented.

Second Normal Form (2NF)

A relation is in 2NF when it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key; there must be no partial dependency of a non-prime attribute on a proper subset of a candidate key. If Enrollment(StudentID, CourseID, StudentName, CourseName, Grade) has key (StudentID, CourseID), StudentName depends only on StudentID and CourseName only on CourseID. Decompose into Student, Course and Enrollment relations.

Third Normal Form (3NF)

A relation is in 3NF when, for every non-trivial functional dependency X → A, either X is a superkey or A is a prime attribute (part of some candidate key). A commonly taught intuition is to remove transitive dependencies of non-key attributes on a key. For example, Employee(EmpID, DeptID, DeptName) with EmpID → DeptID and DeptID → DeptName can be decomposed into Employee(EmpID, DeptID) and Department(DeptID, DeptName).

Boyce–Codd Normal Form (BCNF)

A relation is in BCNF if, for every non-trivial functional dependency X → Y, X is a superkey. BCNF is stricter than 3NF. Consider Teaching(Student, Subject, Instructor), with dependencies (Student, Subject) → Instructor and Instructor → Subject, assuming each instructor teaches only one subject. Instructor is not a superkey, so Instructor → Subject violates BCNF. Decompose into InstructorSubject(Instructor, Subject) and StudentInstructor(Student, Instructor). Under these stated dependencies, the decomposition is lossless, though dependency preservation must be checked for any decomposition.

Benefits and cautions

Normalization improves consistency, reduces duplicate storage and makes constraints easier to enforce. Over-normalization can make reporting queries require more joins, and some systems intentionally denormalize for performance. Such decisions should be based on measured workload and must not compromise correctness.

Normal form	Main requirement	Typical issue addressed
1NF	Atomic attribute values; no repeating groups	Multiple values stored in one cell
2NF	1NF plus no partial dependency of non-prime attributes on candidate keys	Partial dependency on composite key
3NF	For X → A, X is a superkey or A is prime, for each non-trivial FD	Certain transitive/non-key dependencies
BCNF	Every determinant in a non-trivial FD is a superkey	Determinants that are not superkeys

Flowchart / Process

List attributes, candidate keys and functional dependencies

Remove repeating groups and ensure 1NF

Remove partial dependencies to reach 2NF

Check dependencies against 3NF condition

Decompose any BCNF-violating dependencies

Verify lossless join and dependency preservation

Suitable example

In StudentCourse(StudentID, StudentName, CourseID, CourseName, Grade), a composite key (StudentID, CourseID) determines Grade. StudentName depends only on StudentID and CourseName only on CourseID, so separate Student and Course tables and retain Grade in an Enrollment table.

Conclusion

Normalization proceeds from 1NF to 2NF, 3NF and BCNF by analysing functional dependencies and candidate keys. Decomposition should be checked for lossless joins and, where possible, dependency preservation.

Section B · 2(e)7 marks · Exam-oriented answer

Explain the Two-Phase Locking protocol and its variants.

Two-Phase Locking (2PL) is a concurrency-control protocol used by database systems to ensure conflict serializability. A transaction obtains locks before accessing data and follows two phases: a growing phase, in which it may acquire locks but cannot release them, and a shrinking phase, in which it may release locks but cannot acquire new ones. Shared locks generally permit compatible reads, while exclusive locks are needed for writes.

Growing and shrinking phases

During the growing phase, the transaction requests shared or exclusive locks as required. Once it releases its first lock, it enters the shrinking phase and cannot acquire additional locks. This restriction helps guarantee conflict-serializable schedules, but basic 2PL can still permit deadlocks and cascading rollback depending on the locking and release policy.

1. Basic 2PL

Basic 2PL follows the growing and shrinking rules. It ensures conflict serializability but does not itself guarantee deadlock freedom. A transaction may hold a lock while waiting for another, creating a cycle of dependencies among transactions.

2. Conservative or static 2PL

Conservative 2PL obtains all required locks before it starts. If every required lock is not available, the transaction waits without holding a partial set of locks. This prevents deadlocks caused by lock acquisition, but the transaction must know its lock requirements in advance and concurrency may be reduced.

3. Strict 2PL

Strict 2PL holds exclusive locks until commit or rollback. This prevents other transactions from reading or overwriting uncommitted values written by the transaction and avoids cascading aborts caused by such writes. Shared locks may be released earlier depending on the exact protocol variant.

4. Rigorous 2PL

Rigorous 2PL holds both shared and exclusive locks until commit or rollback. It is stricter than strict 2PL and simplifies reasoning about the order in which transactions complete, but it can reduce concurrency because read locks are retained longer.

Deadlock handling and lock granularity

Deadlocks may be managed by prevention, avoidance, detection and recovery, such as identifying cycles in a wait-for graph and aborting a selected victim. Locks may apply at row, page, table or other levels. Fine-grained locks can improve concurrency but require more lock-management overhead; coarse-grained locks are simpler but can block more work.

Flowchart / Process

Transaction requests required lock

Growing phase: acquire locks only

Lock point: last lock acquired

Shrinking phase: release locks only

Commit or rollback

Suitable example

Transaction T1 locks account A and later requests account B, while T2 locks B and requests A. Both wait, producing a deadlock. A DBMS can detect the cycle in its wait-for graph and roll back one transaction so the other can proceed.

Conclusion

2PL provides conflict serializability through controlled lock acquisition and release. Conservative, strict and rigorous variants offer different trade-offs involving deadlocks, recoverability, concurrency and lock duration.

Section C · Questions 3–7

Long-answer questions

Original question numbering and both alternatives are preserved for Questions 3 through 7.

Section C · 3(a)7 marks · Exam-oriented answer

Differentiate between a file-oriented system and a database system.

A file-oriented system stores data in separate files controlled by application-specific programs. A database system stores related data under the management of a DBMS, which provides shared definitions, queries, constraints, transaction control and recovery. The database approach is generally more suitable when multiple applications and users need consistent access to related information.

1. Data redundancy and consistency

In a file-oriented system, the same fact may be copied into several files. Updating one copy but not another can cause inconsistency. A database can centralize shared facts and use normalization and constraints to reduce unnecessary duplication, although poor database design can still create redundancy.

2. Data independence

File formats and application logic are often tightly coupled, so a file structure change may require multiple programs to be modified. A DBMS provides logical and physical data independence to a degree, allowing certain schema or storage changes without rewriting every application.

3. Sharing and concurrent access

Separate files make coordinated multi-user updates difficult. A DBMS provides concurrency control and transaction mechanisms so that simultaneous operations can be managed while preserving defined correctness properties.

4. Integrity and security

File-oriented applications may implement validation and access checks separately in each program. A DBMS can enforce primary keys, foreign keys, checks, roles and privileges centrally. Security still depends on correct configuration and application design.

5. Backup and recovery

File systems may require custom backup and recovery procedures for each application. A DBMS typically provides logging, checkpoints, backup tools and transaction recovery to restore a consistent state after failures.

6. Cost and suitability

Simple files may be inexpensive and sufficient for small, isolated tasks. A DBMS adds administration, infrastructure and skill requirements but is usually preferable for shared, structured, transactional or security-sensitive data.

Basis	File-oriented system	Database system
Management	Separate application files	DBMS manages shared data
Redundancy	Often higher and duplicated	Can be controlled through design
Concurrency	Application-specific handling	Transaction and concurrency controls
Integrity	Often embedded in programs	Declarative constraints plus application rules
Recovery	Often custom procedures	DBMS logging and recovery tools

Suitable example

In a file-based college system, the accounts and examination departments might maintain separate student names and addresses. In a shared database, both departments can use a common Student record with role-based access, reducing inconsistent updates.

Conclusion

File-oriented systems can suit simple isolated tasks, while DBMSs offer stronger sharing, integrity, concurrency and recovery features for complex multi-user applications.

Section C · 3(b)7 marks · Exam-oriented answer

What is an ER diagram? Draw a suitable ER diagram for a hospital management system.

An Entity–Relationship (ER) diagram is a conceptual database-design tool that represents entity types, their attributes and the relationships among them. Entities represent distinguishable objects such as Patient or Doctor; attributes describe their properties; relationships show how entities are associated. ER modelling helps clarify requirements before tables are created.

ER diagram symbols

In traditional ER notation, rectangles represent entities, ovals represent attributes, diamonds represent relationships, underlined attributes indicate key attributes, and lines connect the components. Cardinality labels such as 1:1, 1:N and M:N express how many instances may participate. Crow's-foot notation uses different symbols but expresses similar concepts.

Hospital entities

A basic hospital system can contain Patient(PatientID, Name, DOB, Phone), Doctor(DoctorID, Name, Specialization), Appointment(AppointmentID, DateTime, Status), Department(DepartmentID, DepartmentName), Admission(AdmissionID, AdmitDate, DischargeDate) and Room(RoomID, RoomType). Each entity has a primary key and attributes suitable to its purpose.

Relationships and cardinality

A patient can book many appointments, and a doctor can attend many appointments; Appointment therefore acts as an associative entity linking Patient and Doctor. A department may employ many doctors, while each doctor is assigned to one department in this simplified design. A patient may have multiple admissions over time. Each admission may be assigned a room, depending on the business rules and the period of stay.

Relational conversion

PatientID and DoctorID in Appointment are foreign keys referencing their respective tables. DepartmentID in Doctor references Department. Admission contains PatientID and a RoomID or a separate room-assignment table if room changes and occupancy periods must be tracked. Real hospital systems usually need additional entities for prescriptions, tests, billing, staff, insurance and medical records.

Flowchart / Process

PATIENT (1) ─── books ─── (N) APPOINTMENT

DOCTOR (1) ─── attends ─── (N) APPOINTMENT

DEPARTMENT (1) ─── employs ─── (N) DOCTOR

PATIENT (1) ─── has ─── (N) ADMISSION

ROOM (1) ─── assigned to ─── (N) ADMISSION*

Suitable example

A patient may have Appointment A101 with Doctor D12 on a specified date. Appointment stores PatientID and DoctorID as foreign keys, together with date, time and status. This design allows the hospital to find a patient's appointments or a doctor's daily schedule.

Conclusion

An ER diagram communicates entities, attributes, keys and cardinalities before implementation. A hospital model should represent patients, doctors, appointments, departments and admissions with appropriate keys and constraints.

Section C · 4(a)7 marks · Exam-oriented answer

Explain generalization, specialization and aggregation with examples.

Generalization, specialization and aggregation are concepts used in enhanced Entity–Relationship (EER) modelling to represent complex data requirements. Generalization combines similar lower-level entity types into a common superclass, specialization divides a superclass into more specific subclasses, and aggregation treats a relationship and its participating entities as a higher-level unit for modelling another relationship.

1. Generalization

Generalization is a bottom-up process. Similar entity types are combined into a higher-level superclass that contains their common attributes. For example, Car and Truck may be generalized into Vehicle with common attributes such as VehicleID, Manufacturer and RegistrationNumber. Car may retain NumberOfDoors, while Truck may retain LoadCapacity.

2. Specialization

Specialization is a top-down process. A superclass is divided into subclasses according to distinguishing properties or roles. For example, Employee may be specialized into TeachingStaff and AdministrativeStaff. Both inherit EmployeeID and Name, while TeachingStaff has SubjectArea and AdministrativeStaff has OfficeRole. Subclasses may be disjoint or overlapping, and specialization may be total or partial depending on the rules.

3. Aggregation

Aggregation is used when a relationship itself needs to participate in another relationship. For example, Employee WORKS_ON Project may represent an employee's assignment to a project. A Manager may monitor that specific assignment, so the WORKS_ON relationship is treated as a higher-level unit connected to Manager. Aggregation is different from a simple entity subtype hierarchy.

Constraints

Specialization constraints include disjointness—whether an entity may belong to more than one subclass—and completeness—whether every superclass entity must belong to a subclass. These constraints should reflect business rules and affect how the model is converted into tables.

Flowchart / Process

Generalization: Car + Truck → Vehicle

Specialization: Employee → TeachingStaff / AdministrativeStaff

Aggregation: Employee — WORKS_ON — Project

Manager monitors the aggregated assignment

Suitable example

In a university, Student and Employee may share some person-related attributes, allowing a general Person superclass. Person can then be specialized into Student and Employee. Separately, a project assignment relationship may be aggregated if a supervisor evaluates the assignment itself.

Conclusion

Generalization forms a common superclass from related entities, specialization creates subclasses from a superclass, and aggregation models a relationship as a higher-level unit when another relationship must refer to it.

Section C · 4(b)7 marks · Exam-oriented answer

What do you mean by ACID properties of a transaction? Discuss each.

ACID is a set of properties that help database transactions execute reliably. The four properties are Atomicity, Consistency, Isolation and Durability. They are especially important in systems such as banking, order processing and inventory management, where incomplete or conflicting updates can cause serious errors.

1. Atomicity

Atomicity means that a transaction is treated as an indivisible unit: either all of its required operations take effect or none do. If a failure occurs before completion, the DBMS uses rollback or recovery mechanisms to undo incomplete changes. Atomicity prevents a transaction from leaving only half of a multi-step operation applied.

2. Consistency

Consistency means that a transaction takes the database from one valid state to another, preserving defined integrity constraints and business rules. Examples include primary-key uniqueness, foreign-key validity and a rule that an account balance must not fall below a permitted limit. Consistency depends on correct constraints and application logic, not on the DBMS alone.

3. Isolation

Isolation controls the effects of concurrent transactions. Ideally, concurrent execution behaves as though transactions ran in a valid serial order, though practical isolation levels may permit certain anomalies in exchange for performance. Locking, multiversion concurrency control and other mechanisms help manage simultaneous reads and writes.

4. Durability

Durability means that once a transaction commits successfully, its changes survive subsequent failures covered by the system's durability guarantees. Databases commonly use transaction logs, stable storage, replication and recovery procedures to preserve committed work. Exact guarantees depend on configuration and the failure model.

Illustration through a bank transfer

Suppose ₹1,000 is transferred from Account A to Account B. Atomicity ensures both debit and credit happen together. Consistency preserves rules such as valid balances. Isolation prevents concurrent transactions from producing an invalid combined result. Durability ensures the committed transfer remains recorded after a crash.

Flowchart / Process

Begin transaction

Read and validate account balances

Debit Account A

Credit Account B

Commit if all operations succeed

Rollback if an operation fails

Suitable example

A bank transfer is a standard ACID example: the debit and credit must be atomic, account rules must remain consistent, concurrent transfers must be isolated appropriately, and a committed transfer must be durable.

Conclusion

ACID properties provide a foundation for dependable transaction processing. They reduce partial updates, invalid states, concurrency anomalies and loss of committed changes.

Section C · 5(a)7 marks · Exam-oriented answer

What are the various integrity constraints? Discuss each.

Integrity constraints are rules that keep database data accurate, valid and consistent. They restrict the values that can be stored and the relationships that can exist among records. Constraints can be declared in the database schema and checked by the DBMS whenever data is inserted, updated or deleted.

1. Domain constraint

A domain constraint restricts an attribute to an appropriate data type, range, format or set of permitted values. For example, Age may be a non-negative integer, and Status may be limited to values such as 'Active', 'Pending' or 'Closed'. SQL data types, CHECK constraints and suitable reference tables can enforce domain rules.

2. Key constraint

A key constraint requires candidate-key values to uniquely identify rows. A primary key is the selected candidate key and cannot contain NULL values. A UNIQUE constraint can enforce uniqueness for other attributes, subject to the DBMS's treatment of NULLs.

3. Entity integrity

Entity integrity requires every primary-key value to be non-null so each row can be identified. Without it, a row could not be reliably referenced through that key. A DBMS enforces this rule when a primary key is declared.

4. Referential integrity

Referential integrity requires a foreign-key value to match a referenced candidate or primary key, or to be NULL if the relationship permits it. For example, every non-null Employee.DeptID should refer to an existing Department.DeptID. On deletion or update, the schema may specify RESTRICT, CASCADE, SET NULL or another supported action.

5. NOT NULL and CHECK constraints

NOT NULL requires a value to be supplied for a column. CHECK enforces a condition on each inserted or updated row, such as Salary >= 0 or EndDate >= StartDate. CHECK expressions should be designed carefully because SQL's treatment of UNKNOWN and NULL can affect whether a row passes the constraint.

6. Semantic or business constraints

Business constraints express organization-specific rules that may involve multiple columns, rows or processes. Examples include ensuring that a course's enrolled count does not exceed capacity or that an employee is assigned to an authorized project. Some rules can be implemented with constraints; others require transactions, triggers or application logic. Enforcement should be centralized where practical to prevent inconsistent implementations.

Constraint	Purpose	Example
Domain	Restricts allowed values	Marks BETWEEN 0 AND 100
Key / UNIQUE	Prevents duplicate identifiers	Unique StudentID
Entity integrity	Primary key cannot be NULL	StudentID is NOT NULL
Referential integrity	Foreign key references valid row	Employee.DeptID references Department
CHECK / NOT NULL	Enforces conditions and required values	Salary >= 0; Name NOT NULL

Suitable example

In an Enrollment table, StudentID can be a foreign key to Student, CourseID a foreign key to Course, and the pair (StudentID, CourseID, Semester) may be the primary key. A CHECK constraint can ensure that Grade belongs to an allowed range or category.

Conclusion

Integrity constraints protect data quality by enforcing valid values, unique identities, valid references and business rules. They should be designed alongside keys and relationships rather than added only after errors occur.

Section C · 5(b)7 marks · Exam-oriented answer

Describe various types of join operations. Briefly explain their applications.

A join combines rows from two relations or tables based on a matching condition. Joins are fundamental to relational databases because related information is often stored in separate normalized tables. The join type determines which matching and non-matching rows appear in the result.

1. Inner join

An inner join returns only rows that satisfy the join condition in both tables. It is used when a result should include only records with a corresponding match, such as employees who have a valid matching department.

2. Equi join and theta join

An equi join uses equality between join attributes, such as Employee.DeptID = Department.DeptID. A theta join uses a general comparison condition such as less than, greater than or another supported predicate. Equi join is a common special case of theta join.

3. Natural join

A natural join automatically matches columns that have the same names and compatible domains, returning one copy of each matching column. It can be concise, but may be dangerous if unrelated columns share a name or schema changes introduce new common columns. Explicit join conditions are often clearer in production SQL.

4. Left, right and full outer joins

A left outer join returns all rows from the left table and matching rows from the right; unmatched right-side values appear as NULL. A right outer join preserves all rows from the right table. A full outer join preserves unmatched rows from both tables. These joins are useful for finding missing relationships, such as departments with no employees.

5. Cross join

A cross join returns the Cartesian product: every row from the first table is paired with every row from the second. It can be useful for generating combinations, such as every product with every date, but the result size can grow rapidly and should be intentional.

6. Self join

A self join joins a table to itself using aliases. It is useful for hierarchical or comparative relationships, such as displaying each employee alongside their manager when ManagerID references another row in Employee.

7. Semi join and anti join concepts

A semi join returns rows from one side for which a match exists on the other; SQL commonly expresses this with EXISTS. An anti join returns rows for which no match exists, often expressed with NOT EXISTS or an appropriate outer join filter. They are useful for existence checks and identifying missing related records.

Join type	Rows returned	Typical application
Inner	Only matching rows	Employees with departments
Left outer	All left rows plus matches	All departments, including empty ones
Right outer	All right rows plus matches	All right-side records with optional left data
Full outer	Matches and unmatched rows from both sides	Reconcile two lists
Cross	Every possible pair	Generate combinations
Self	Rows matched within same table	Employee-manager hierarchy

Suitable example

To list every department, including those without employees, use Department LEFT JOIN Employee ON Department.DeptID = Employee.DeptID. Departments without a match remain in the result, with Employee columns set to NULL.

Conclusion

Join operations combine related tables in different ways. Choosing the right join depends on whether the task needs only matches, all records from one side, all records from both sides or combinations within the same table.

Section C · 6(a)7 marks · Exam-oriented answer

What is deadlock? Explain deadlock detection and prevention techniques.

A deadlock is a situation in which two or more transactions wait indefinitely for resources locked by one another, so none can proceed. In a database, a transaction may hold a lock on one row while requesting another row held by a different transaction. Deadlocks are different from ordinary waiting: a circular dependency exists among the waiting transactions.

Necessary conditions

The classic deadlock conditions are mutual exclusion (a resource cannot be shared in the required mode), hold and wait (a transaction holds resources while requesting more), no pre-emption (resources are not forcibly taken away under the protocol), and circular wait (a cycle of transactions exists). Breaking at least one condition can prevent deadlock.

1. Wait-for graph detection

A DBMS can build a wait-for graph in which each node represents a transaction and an edge Ti → Tj means Ti is waiting for a resource held by Tj. A cycle indicates a deadlock under the usual single-instance lock-resource model. The DBMS can periodically or when needed inspect the graph to detect cycles.

2. Recovery after detection

Once a deadlock is detected, the system selects a victim transaction to abort or roll back, releasing its locks so other transactions can continue. Victim selection may consider work completed, locks held, rollback cost, transaction priority and how often a transaction has already been rolled back. The system should avoid repeatedly choosing the same transaction and causing starvation.

3. Prevention using resource ordering

Transactions can be required to acquire locks in a consistent global order, such as ascending account ID. This removes circular wait if all transactions follow the ordering rule. Another strategy is to require transactions to request all needed locks before execution, though this can reduce concurrency and may not be practical when requirements are not known in advance.

4. Timestamp-based prevention

Protocols such as wait-die and wound-wait use transaction timestamps to decide whether a transaction may wait or should be aborted. In wait-die, an older transaction may wait for a younger one, while a younger transaction requesting a resource held by an older one is aborted. In wound-wait, an older transaction can force a younger holder to roll back, while a younger requester may wait for an older holder. Exact implementation policies vary.

5. Timeout and practical precautions

A timeout aborts or reports a transaction that has waited too long. It is simple but can mistake a long legitimate wait for a deadlock and does not identify the cycle precisely. Short transactions, consistent lock ordering, suitable indexes, careful transaction boundaries and prompt commit or rollback reduce deadlock frequency.

Flowchart / Process

Transaction requests a locked resource

DBMS records waiting dependencies

Build or inspect wait-for graph

Cycle found? If no, continue monitoring

If yes, choose victim transaction

Rollback victim and release locks

Restart transaction when appropriate

Suitable example

T1 locks row A and requests row B, while T2 locks row B and requests row A. T1 waits for T2 and T2 waits for T1, creating a cycle. The DBMS can abort one transaction, release its locks and allow the other to complete.

Conclusion

Deadlocks result from circular resource waiting. Detection identifies cycles and rolls back a victim; prevention avoids the conditions that allow cycles to form. Consistent lock ordering and good transaction design are practical safeguards.

Section C · 6(b)7 marks · Exam-oriented answer

Explain indexing. Differentiate between primary, secondary and clustered indexes.

An index is an auxiliary data structure that helps a DBMS locate rows without scanning an entire table. It stores search-key values and references to rows or data pages. Indexes can greatly improve selection, join, ordering and grouping operations, but they require storage and must be maintained when data changes.

How indexing works

Common index structures include B-tree or B+ tree indexes, which support equality and range searches, and hash indexes, which are suited to equality lookup in systems that support them. The query optimizer estimates the cost of possible access paths and decides whether to use an index or scan the table. An index is not automatically beneficial for every query.

Primary index

In traditional file-organization terminology, a primary index is built on the ordering key of a data file that is physically ordered on that key. It may be sparse when one index entry represents a block. In commercial DBMSs, the phrase 'primary index' can also be used differently, so the exact meaning should be stated according to the textbook or system.

Secondary index

A secondary index provides an additional access path on an attribute that does not define the file's primary physical ordering. It may be built on a non-ordering field and can be unique or non-unique. Multiple secondary indexes can exist, but each adds storage and update overhead.

Clustered index

A clustered index determines or closely corresponds to the physical organization of table rows according to the index key in systems that implement clustered storage. It can make range scans and retrieval of nearby key values efficient. Because rows can have only one physical ordering, many systems permit only one clustered organization per table. Exact implementation differs by DBMS.

Choosing indexes

Useful indexes often cover columns frequently used in WHERE conditions, joins, sorting or grouping. Composite index column order matters. Too many indexes can slow inserts, updates and deletes; low-selectivity indexes may not help some queries. Query plans and real workload measurements should guide index design.

Basis	Primary index (traditional meaning)	Secondary index	Clustered index
Purpose	Access through file's ordering key	Additional search path	Organize rows by index key
Ordering	File ordered on primary search key	Not the file's ordering key	Rows stored in clustered key order or organization
Count	Depends on file organization	Multiple can exist	Typically one physical clustering order
Benefit	Efficient access by ordering key	Lookup using alternate attributes	Efficient range and locality-based access

Flowchart / Process

Query filters or joins on a column

Optimizer evaluates available access paths

Index locates matching keys or pages

DBMS fetches required rows

Measure plan and maintain index

Suitable example

An index on StudentID can speed up a query that searches for one student. A secondary index on Email may support login lookup. A clustered organization on OrderDate can help range queries that retrieve orders from a date interval, depending on the DBMS.

Conclusion

Indexes improve data retrieval by providing alternate access paths. Primary, secondary and clustered terminology depends partly on the database system, so definitions should be given clearly and index choices should be measured against workload and update costs.

Section C · 7(a)7 marks · Exam-oriented answer

Discuss data warehousing, OLAP and OLTP.

Data warehousing, Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP) serve related but different purposes. OLTP systems capture and manage day-to-day business transactions. A data warehouse integrates historical data from multiple sources for reporting and analysis, while OLAP provides analytical operations that help users explore summarized data from different perspectives.

1. OLTP

OLTP supports operational applications such as order entry, banking, reservations and inventory updates. It commonly handles many short transactions that insert, update or retrieve a small number of records. Important priorities include correctness, concurrency, low latency, availability and transaction integrity. Operational schemas are often normalized to reduce update anomalies.

2. Data warehouse

A data warehouse is a repository designed for analysis and reporting, often integrating data from different operational systems over time. Data is extracted, cleaned, transformed and loaded or otherwise ingested through ETL/ELT processes. Warehouses commonly organize information around business subjects and historical measures, and may use dimensional models with fact and dimension tables.

3. OLAP

OLAP supports multidimensional analysis. Users can roll up data to a higher level, drill down into detail, slice a subset, dice across dimensions and pivot the view. For example, sales can be analysed by product, region, store and month. OLAP may be implemented using relational, multidimensional or other analytical engines.

Architecture and relationship

Operational systems collect transactions; data integration pipelines extract and transform relevant information; the warehouse stores curated historical data; and BI tools or OLAP engines support analysis and dashboards. Modern systems may use near-real-time ingestion, lakehouses or direct analytical queries, so a single architecture is not mandatory.

Differences and design considerations

OLTP is optimized for frequent operational writes and current-state queries, while OLAP is optimized for complex scans, aggregations and analytical queries. Warehouses can support trend analysis but should not be assumed to replace operational transaction systems. Data freshness, quality, access controls, lineage and cost need to be managed across the pipeline.

Basis	OLTP	Data warehouse / OLAP
Purpose	Run daily operations	Analyse trends and support decisions
Data	Current, detailed operational records	Integrated, often historical and analytical data
Workload	Many short reads and writes	Complex queries, scans and aggregations
Example	Record a customer payment	Compare quarterly sales by region

Flowchart / Process

Operational sources / OLTP systems

Extract and ingest data

Clean, standardize and transform

Load into analytical warehouse

OLAP / BI queries and dashboards

Management insights and decisions

Suitable example

A retailer's OLTP system records each sale. A warehouse combines sales from all stores over several years. An OLAP dashboard lets managers compare revenue by month, product category and region to identify seasonal patterns.

Conclusion

OLTP runs daily transactions, a data warehouse integrates data for analysis, and OLAP enables multidimensional exploration. Together they support both operational execution and strategic decision-making.

Section C · 7(b)7 marks · Exam-oriented answer

Explain database security briefly.

Database security consists of policies, controls and technologies that protect data against unauthorized access, disclosure, modification, destruction and service disruption. It aims to preserve confidentiality, integrity and availability while allowing authorized users to perform their work.

1. Authentication and authorization

Authentication verifies a user's or service's identity, while authorization determines which operations that identity may perform. Strong passwords, multi-factor authentication, role-based access control and least privilege reduce the risk of unauthorized access. Shared accounts should be avoided because they weaken accountability.

2. Access control and separation of duties

Database roles should grant only the required permissions. Application accounts should not use administrator privileges unnecessarily, and sensitive actions may require approval or separation of duties. Privileged access should be reviewed regularly and removed when no longer needed.

3. Encryption and data masking

Encryption in transit protects data moving between applications and databases, while encryption at rest protects stored files or media. Keys must be managed securely. Masking or tokenization can reduce exposure in test, analytics or support environments, although these methods must be implemented carefully.

4. Integrity, auditing and monitoring

Constraints help prevent invalid data, while audit logs record important access and changes. Monitoring can identify unusual queries, failed logins, privilege changes and abnormal data exports. Logs should be protected against unauthorized alteration and retained according to policy.

5. Patching and secure configuration

Database software should be patched, unnecessary services disabled and default credentials removed. Network access should be restricted to trusted application servers and administrators. Secure configuration baselines and vulnerability assessments help reduce avoidable exposure.

6. Backup, recovery and incident response

Regular backups, transaction logs, replication where appropriate and tested restoration procedures support availability and recovery. Backups should be protected from ransomware and unauthorized deletion. An incident-response plan should define containment, investigation, recovery, communication and lessons learned.

7. Application-level protection

Applications should use parameterized queries or prepared statements to reduce SQL injection risk, validate inputs and handle errors without exposing sensitive details. Security also requires data minimization, privacy compliance, secure development and periodic access reviews.

Flowchart / Process

Identify sensitive data and threats

Authenticate users and services

Apply least-privilege permissions

Encrypt, patch and securely configure

Audit activity and monitor alerts

Back up, test recovery and respond to incidents

Suitable example

A hospital database can restrict doctors to records needed for treatment, give billing staff access only to billing data, encrypt connections, log access to patient records and test protected backups regularly.

Conclusion

Database security is a layered process involving identity, access control, encryption, integrity, auditing, patching and recovery. Technical controls must be supported by clear policies, monitoring and responsible data handling.

Exam-writing checklist

Start with a definition and explain each point under a clear heading.
Use examples and labelled flowcharts where they clarify the concept.
For comparison questions, draw a table with consistent comparison criteria.
Conclude long answers by summarizing the concept's importance and practical use.