DBMS for GATE CSE 2027

A DBMS question may present a SQL query, a list of functional dependencies or a transaction schedule. Each of these requires an approach for SQL; you must handle rows and NULL values with care. Functional dependencies demand calculation of closures. Transaction schedules require a log of operations that conflict with each other.

This guide gathers all of those methods into one place. I find that preparing for DBMS can feel daunting. This guide helps. You can use it to organise your DBMS GATE notes then practise each topic by working through questions.

DBMS Study Checklist

Compare this topic map with the syllabus, for your examination year. Some of the supporting topics listed below give context beyond the main syllabus.

DBMS GATE weightage changes from one paper to another. It's better to look at years' questions to get a sense of the types of problems that appear instead of depending on a fixed number of marks.

What Is DBMS?

A Database Management System is a software tool that stores and manages data. It allows users to retrieve and update data in a controlled way. It also helps maintain data integrity by enforcing rules, manages access when multiple users work at the time and supports recovery in case of system failures.

DBMS vs File System

I see that these advantages come with costs, including administration, storage and processing overhead. I think a DBMS is useful when data relationships, shared access and consistency requirements justify that overhead.

Common database models include hierarchical, network and document models.

1. Architecture, Schema and Instance
2. The three-schema architecture separates:
3. External schemas: views required by particular users or applications.
4. Conceptual schema: the overall logical database structure.
5. Internal schema: how data is stored.

Physical data independence allows storage changes without changing the schema. Logical data independence is about changing the schema without requiring corresponding changes to external views or applications.

A schema describes structure. An instance is the data at a particular time.

ER Model in DBMS

An entity type describes a category, such as Student. An entity set contains the entities of that type at a time.

Attributes describe entities. They may be simple, composite, multivalued or derived. Relationships describe associations between entities, such as a student enrolling, in a course.

Generalization brings together entity types into a single more general type. Specialization does the opposite: it breaks down a type into smaller more specific subtypes. Aggregation lets a relationship be seen as an unit at a higher level in an ER model.

When converting from an ER model to a model, start by finding the keys of each entity. A, to-many relationship usually turns into its own table with the keys of the entities involved. A multivalued attribute also usually requires its separate table.

In the model a relation is made up of rows called tuples each defined by a set of attributes. In the version of the relational model every tuple is unique and the order of the tuples does not matter.

Minimal means you cannot remove any attribute and still keep uniqueness. Minimal does not mean that the candidate key has more attributes than every other candidate key.

Integrity constraints include domain constraints, key constraints and referential constraints. Referential integrity checks that foreign‑key values have matches in the referenced table while allowing NULL values when permitted. Integrity constraints are essential for data quality.

Codd’s rules describe the principles that guide database systems. Codd’s rules give background knowledge. It is far better to understand relations, keys and operations than to try to memorize a list of rules without seeing examples. Relational database systems rely on an understanding of these concepts.

Relational Algebra and Calculus

Relational algebra describes operations that produce relations

Projection removes duplicates under classical relational algebra. SQL, by contrast, generally retains duplicates unless an operation such as distinct removes them.

Tuple relational calculus uses tuple variables; domain relational calculus uses variables ranging over attribute values. Safe forms of relational calculus and relational algebra have equivalent expressive power for relationally complete queries.

For relational algebra GATE questions, write the intermediate result after each operation. This is especially helpful when a projection removes an attribute needed later

SQL, for GATE CSE

I use tables to test SQL queries by hand. Watch for duplicates, watch for join conditions, watch for grouping, watch for values.

Joins, Grouping and Subqueries

An inner join returns matching pairs. An outer join keeps rows from the preserved side putting NULLs in missing columns.

SELECT department, COUNT(*) AS student_count

FROM Student

WHERE marks >= 60

GROUP BY department

HAVING COUNT(*) >= 5;

Ordinary comparisons involving NULL usually evaluate to UNKNOWN. A Where clause retains rows for which its condition is TRUE.

A Check constraint rejects FALSE; UNKNOWN generally does not violate it use not Null separately when missing values must be prohibited.

In SQL query output questions, check whether an outer join is followed by a filter that removes its NULL-extended rows.

Functional Dependencies

A functional dependency XY means that tuples agreeing on X must also agree on Y.

Attribute closure finds all attributes determined by an attribute set.

Suppose:

R(A,B,C,D),F={AB, BC, ACD}

Starting with A:

1. AB adds B.
2. BC adds C.
3. With A and C, ACD adds D.

Thus:

A+={A,B,C,D}

Since A determines every attribute and has no removable attribute, it is a candidate key.

Armstrong’s Axioms and Minimal Cover

Armstrong’s axioms are reflexivity, augmentation and transitivity. Other inference rules can be derived from them.

To find a minimal cover, commonly:

• Split right-hand sides into individual attributes.
• Remove extraneous left-hand attributes.
• Remove redundant dependencies.

Terminology for minimal and canonical covers varies between texts. Follow the definition supplied in the question.

Normalization in DBMS

Normalization examines dependencies to reduce redundancy and update anomalies

A prime attribute belongs to at least one candidate key

3NF vs BCNF

BCNF is stricter than 3NF when it comes to functional dependencies. A relation might meet the requirements of 3NF if the attribute on the right-hand side of a dependency is a prime attribute.. It can still fail BCNF if the determinant is not a superkey.

Before you start checking forms you must find all the candidate keys. If you miss a key you might wrongly label an attribute as non-prime. That mistake can lead to conclusions about the normal form of the relationship.

Lossless and Dependency-Preserving Decomposition

1. A lossless decomposition means you can put the relation back together without adding any extra or spurious tuples. It’s important to keep the data accurate after splitting.
2. For a decomposition into R₁ and R₂ the decomposition is lossless with respect to functional dependencies if the common attributes between R₁ and R₂ determine all of R₁ or all of R₂. This ensures no data is lost during reconstruction.
3. Dependency preservation means that all the original functional dependencies can still be enforced using the dependencies that exist in the decomposed relations. You don’t have to join them to check the constraints.
4. These two properties. Lossless and dependency-preserving. They are different. You should test each one on its own.

Multivalued dependencies

Multivalued dependencies describe sets of values that're independent of each other but are tied to the same determinant. They are key to understanding 4NF.

Denormalization

Denormalization is the process of adding redundancy to a database. It's done to improve performance in read-heavy environments.. With redundancy comes the need for careful management to keep the data consistent.

File Organisation, Indexing and Hashing

Indexes give ways to find data quickly.

A dense index has entries for every search key value or record depending on how the data's organized. A sparse index has entries, for some search key values and usually depends on the right way the file is ordered.

In textbooks a primary index is made using an ordering key, a clustering index is made using an ordering non-key field and a secondary index is made using a non-ordering field. Different products might use words so always use the definitions given in the question.

For B+ tree GATE numericals, distinguish internal-node capacity from leaf capacity. If an internal node has p child pointers and p-1 keys:

pP+(p-1)KB

Here, P is pointer size, K is key size and B is usable node space. Account for headers if specified.

Disk-access cost depends on tree height, cached levels, matching records and file organisation. Do not assume every lookup has the same cost.

Hashing supports equality searches effectively under suitable conditions. Overflow and collisions affect the actual access cost.

Transactions and ACID Properties

A transaction groups operations into a logical unit

I have seen that transaction states commonly include Active, Partially Committed, Committed, Failed and Aborted.

When I talk about serializability and schedule types I notice that two operations conflict if they belong to transactions that touch the same data item and at least one of them writes.

To test conflict serializability I do the following:

1. Create a node for each transaction.
2. Add an edge for every ordering imposed by conflicting operations.
3. Check the precedence graph for cycles.

An acyclic graph means the schedule is conflict serializable.

View serializability keeps the reads the reads-, from relationships and the final writes unchanged. I know that every conflict-serializable schedule is also view serializable but a view serializable schedule does not always have to be conflict serializable.

Recoverability and serializability test different aspects of a schedule

Concurrency Control and Recovery

Two‑phase locking has a growing phase, during which locks can be taken. I notice that after that a shrinking phase follows, during which locks are let go. Once a transaction releases a lock that transaction cannot take another lock under two‑phase locking.

Basic two‑phase locking guarantees conflict serializability. It can allow deadlocks to happen. Strict two‑phase locking holds locks until a transaction commits or aborts. I suggest reading the convention that is stated because some texts use variants.

Timestamp ordering uses transaction timestamps to decide whether operations can proceed. I see that deadlock handling can involve detection, timeouts or prevention schemes such, as wait‑die and wound‑wait.

Database Recovery Techniques

Log‑based recovery logs the information that is needed to undo or redo changes.

Write‑ahead logging requires that the important log information reach storage before a changed data page is written there. Commit processing also requires that the needed log records be made durable.

Checkpoints reduce recovery work although the specific process depends on the checkpoint design. Shadow paging uses a version and a modified version to support recovery.

Whether recovery needs undo, redo or both depends on buffer‑management. Logging assumptions

DBMS GATE Preparation Strategy

Study the relational model and SQL first. Follow these with functional dependencies and normalization, then indexing, transactions, concurrency control and recovery.

Solve DBMS GATE previous-year questions after each topic. When using past year questions with solutions try solving them on your first. Do not look at the explanation away.

Keep a revision sheet. Include closure steps, normal-form conditions, index-capacity formulas and schedule properties. These are points that come up often.

After taking a DBMS GATE test go through every mistake. Write down the assumption or concept that led to the error. This list helps you understand where you went wrong.

Use this list to guide your revision session. It gives you direction. Focus on the areas you recorded. Repeat the process. This way you improve steadily.