BCAMCABSc ITB.Tech CSE

DBMS — syllabus, exam questions and lab programs

Your scheme may call it Database Management Systems.

DBMS is the paper with the widest gap between what the exam asks and what the job needs, and it is worth knowing which side of that gap you are on before you open the book. Normalisation up to BCNF, the relational algebra symbols, the ACID properties and the two-phase locking protocol are exam material — they appear in every paper and almost never in a working day. SQL, joins, indexes and knowing what a primary key is for are the opposite: barely worth ten marks and used constantly for the rest of your career.

So revise both, but do not confuse them. If you are short of time before the exam, learn normalisation and the transaction unit, because those are the questions that carry fifteen marks each. If you are short of time before an interview or a project, learn joins and subqueries, because that is all anybody will ask.

The good news is that this is the easiest paper in the degree to practise. Install MySQL or use SQLite, make yourself a three-table database — students, courses, enrolments — and answer every SQL question in the paper by actually running it. An hour of that beats a week of reading, and the practical file writes itself.

We teach this

DBMS is one of the papers we teach at WebPrims

This is not only a page about DBMS. It is a subject we teach in a classroom on Majitha Road — tuition for DBMS, if that is the word you were searching for — and a good share of every batch is college students taking it alongside their own semester. Bring your scheme and we cover what is on it.

What that means in practice: the whole syllabus gets covered rather than the parts that make a good demo, you write code on a machine instead of copying it into a file, and the lab work gets done here rather than the night before submission.

What we will not say is that this guarantees you marks or a job. We do not run placements and we do not promise results. Come and sit in a class, decide for yourself.

₹4,500 / month— one rate, any subjectMon–Sat, batches at 11, 1, 3 and 5Majitha Road, Amritsar

The syllabus, unit by unit

What each unit actually contains, and whether it is there because it matters or because it is on the paper. Unit order and numbering vary between GNDU, PTU and your batch’s scheme — check your own before you plan a revision week.

1

Introduction and database architecture

File system versus DBMS, data redundancy and inconsistency, data abstraction at the physical, logical and view levels, the three-schema architecture, data independence, and the roles of the DBA.

Definitions, and the three-schema diagram is worth learning to draw. The advantages-of-DBMS question is asked almost every year.

2

The ER model

Entities, attributes and their types, relationships and their degree, cardinality one-to-one, one-to-many and many-to-many, weak entities, generalisation and specialisation, and converting an ER diagram into tables.

You will be given a scenario — a library, a hospital, a bank — and asked to draw the diagram. Practise three scenarios and you have seen the pattern.

3

The relational model and relational algebra

Relations, tuples, domains and keys — super, candidate, primary, alternate and foreign; integrity constraints; and the algebra operators select, project, union, set difference, Cartesian product, rename and the joins.

Pure exam material. Learn the six Greek symbols and how to express a query in them; nobody uses this notation outside a classroom.

4

SQL

DDL, DML, DCL and TCL; create, alter, drop; insert, update, delete; select with where, order by, group by and having; the aggregate functions; inner, left, right and full joins; subqueries and correlated subqueries; views; and indexes.

The unit that matters after the degree. Every line of it is used in real work, and the interview questions come from here.

5

Normalisation

Functional dependency, full and partial and transitive dependency, anomalies on insert, update and delete, and the normal forms 1NF, 2NF, 3NF and BCNF, with 4NF and 5NF as ideas.

The heaviest question on most papers. Learn it as a procedure applied to a given table, not as four definitions.

6

Transactions and concurrency

What a transaction is, the ACID properties, states of a transaction, schedules and serialisability, concurrency problems — lost update, dirty read, unrepeatable read — locking, two-phase locking, deadlock and its handling.

Second-heaviest question. ACID is free marks; serialisability takes real practice.

7

Recovery and file organisation

Types of failure, log-based recovery with deferred and immediate update, checkpoints, shadow paging, and indexing with B-trees and hashing.

Usually the last unit, usually skipped by everyone, and usually the cheapest marks left on the paper.

What is worth keeping after the exam

Revise all of it — the marks are the marks. But it is worth knowing which half of this paper you will still be using in two years, and which half exists because it is on the paper.

Stays with you

  • +SQL, in full — this is the one part of the degree you will use nearly every working day
  • +Joins and what they cost, which separates people who can query a database from people who cannot
  • +Why a primary key and a foreign key exist, which stops you designing a schema you regret
  • +Indexes: what they speed up, and what they slow down
  • +The idea behind normalisation, even when you deliberately break it for speed

For the exam, and then gone

  • −Relational algebra notation — nobody writes sigma and pi outside an exam hall
  • −Shadow paging, which no production database uses
  • −4NF and 5NF, which you will not meet in ordinary work
  • −Reciting the two-phase locking protocol; the database does it for you

Questions that come up year after year

Not a guess paper, and not a promise about what will be set. These are the questions this subject keeps asking because they are the ones that test whether you understood it.

  1. 01Normalise the given table up to 3NF or BCNF, showing each step
  2. 02Draw an ER diagram for a library / hospital / bank management system
  3. 03Explain the ACID properties with an example
  4. 04Difference between DELETE, DROP and TRUNCATE
  5. 05Write SQL queries on the given tables (usually five short queries)
  6. 06Explain the types of join with examples
  7. 07What are the levels of data abstraction? Draw the three-schema architecture
  8. 08Explain concurrency control problems with examples

What students get wrong

From teaching this paper, not from a list somewhere. Each of these costs marks every year.

Wrong — Learning the definitions of 1NF, 2NF and 3NF without ever normalising a table.

Right — The question is always applied. Take a messy table, find the functional dependencies first, then apply the forms one at a time and show the tables after each. The dependencies are the step people skip, and without them the rest is guesswork.

Wrong — Saying DELETE and TRUNCATE are the same thing.

Right — DELETE is DML, removes selected rows, can be rolled back and fires triggers. TRUNCATE is DDL, removes every row, resets the identity and cannot be rolled back. DROP removes the table itself. This is asked constantly and answered wrongly constantly.

Wrong — Using WHERE to filter the result of a GROUP BY.

Right — WHERE filters rows before grouping; HAVING filters groups after. 'Departments with more than five employees' is HAVING COUNT(*) > 5. Getting this the wrong way round is the most common SQL error in the paper.

Wrong — Writing a join with a comma and no condition, then wondering why there are 4,000 rows.

Right — That is a Cartesian product — every row against every row. Always state the join condition, and prefer the explicit INNER JOIN ... ON form, which makes a missing condition impossible to hide.

Lab file programs

These compile and run as written — type them in, break them, and fix them. Copying a program into a file you never ran is how a practical viva goes badly.

Create the tables the rest of the file uses

Three related tables with proper keys. Everything else in the practical file runs against these.

sql
CREATE TABLE student (
    roll_no      INT PRIMARY KEY,
    name         VARCHAR(50) NOT NULL,
    city         VARCHAR(30),
    admission_yr INT
);

CREATE TABLE course (
    course_id   VARCHAR(10) PRIMARY KEY,
    title       VARCHAR(60) NOT NULL,
    credits     INT CHECK (credits BETWEEN 1 AND 6)
);

CREATE TABLE enrolment (
    roll_no   INT,
    course_id VARCHAR(10),
    marks     INT,
    PRIMARY KEY (roll_no, course_id),
    FOREIGN KEY (roll_no)   REFERENCES student(roll_no),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

INSERT INTO student VALUES
    (101, 'Simran Kaur',  'Amritsar', 2024),
    (102, 'Harjot Singh', 'Tarn Taran', 2024),
    (103, 'Navdeep Kaur', 'Amritsar', 2025);

INSERT INTO course VALUES
    ('BCA201', 'Data Structures', 4),
    ('BCA202', 'Database Management Systems', 4),
    ('BCA203', 'Operating Systems', 3);

INSERT INTO enrolment VALUES
    (101, 'BCA201', 78),
    (101, 'BCA202', 85),
    (102, 'BCA201', 61),
    (103, 'BCA202', 92);

The five queries that appear in every paper

Join, aggregate, group with having, subquery, and a left join to find what is missing.

sql
-- 1. Every student with the courses they are enrolled in
SELECT s.name, c.title, e.marks
FROM student s
JOIN enrolment e ON s.roll_no = e.roll_no
JOIN course   c ON c.course_id = e.course_id
ORDER BY s.name;

-- 2. Average marks per course
SELECT c.title, AVG(e.marks) AS average_marks
FROM course c
JOIN enrolment e ON c.course_id = e.course_id
GROUP BY c.title;

-- 3. Courses where more than one student scored above 70
SELECT c.title, COUNT(*) AS above_70
FROM course c
JOIN enrolment e ON c.course_id = e.course_id
WHERE e.marks > 70
GROUP BY c.title
HAVING COUNT(*) > 1;

-- 4. Students who scored above the overall average (subquery)
SELECT s.name, e.marks
FROM student s
JOIN enrolment e ON s.roll_no = e.roll_no
WHERE e.marks > (SELECT AVG(marks) FROM enrolment);

-- 5. Students enrolled in nothing at all (LEFT JOIN + IS NULL)
SELECT s.roll_no, s.name
FROM student s
LEFT JOIN enrolment e ON s.roll_no = e.roll_no
WHERE e.roll_no IS NULL;

A view and an index

The two objects the practical file usually ends on, and the ones interviews ask about.

sql
-- A view: a saved query that behaves like a table
CREATE VIEW student_report AS
SELECT s.roll_no, s.name, c.title, e.marks
FROM student s
JOIN enrolment e ON s.roll_no = e.roll_no
JOIN course   c ON c.course_id = e.course_id;

SELECT * FROM student_report WHERE marks >= 80;

-- An index: speeds up lookups on a column you filter by often
CREATE INDEX idx_student_city ON student(city);

-- Worth writing in the file: an index makes reads faster and
-- writes slower, because every insert has to update it too.
DROP INDEX idx_student_city ON student;

Long questions, answered the way they are marked

Not model answers to reproduce. What the examiner is checking for, and where the marks actually sit in each one.

Normalise the given table up to 3NF.

Work in four visible steps and you will not lose marks. One: list the functional dependencies you can see. Two: 1NF — remove repeating groups and multi-valued cells so every cell is atomic. Three: 2NF — the table must be in 1NF and have no partial dependency, so any attribute depending on only part of a composite key moves to its own table. Four: 3NF — no transitive dependency, so an attribute that depends on a non-key attribute moves out too. Show the tables after every step with their keys underlined. The dependencies at the start are what most answers miss, and they are what the examiner is checking.

Explain the ACID properties of a transaction.

Atomicity: all of the transaction happens or none of it does — the classic example is a bank transfer where the debit succeeds and the credit fails. Consistency: the database moves from one valid state to another, with every constraint still satisfied. Isolation: concurrent transactions do not see each other's half-finished work. Durability: once committed, it survives a crash, which is what the write-ahead log is for. Give the bank transfer example once and refer back to it for all four; that reads as understanding rather than recitation.

Differentiate between DELETE, TRUNCATE and DROP.

DELETE is DML, removes rows matching a WHERE clause, logs each row, can be rolled back and fires triggers. TRUNCATE is DDL, removes all rows, deallocates the pages, resets any auto-increment, cannot be rolled back in most systems and fires no row triggers. DROP is DDL and removes the table structure itself along with the data, its indexes and its constraints. A three-column table with the rows Type, Rolled back?, Speed answers this faster than a paragraph.

Explain the different types of join with examples.

Inner join returns only rows matching in both tables. Left outer join returns every row from the left table with nulls where the right has no match — this is how you find students enrolled in nothing. Right outer join is the mirror. Full outer join returns unmatched rows from both. Cross join is every combination, which is the Cartesian product. Self join joins a table to itself, used for hierarchies such as an employee and their manager. Use the same two small tables for all of them and show the result set for each; a result set is worth more than a sentence.

Difference between — the ones that get asked

Every one of these is a five-mark question somewhere on this syllabus, and the marks are rarely in the first line. They are in the consequences underneath it.

DBMS vs RDBMS

Every RDBMS is a DBMS. The R adds one idea — data is stored in tables that are related to each other by keys, and the software enforces those relationships for you.

 DBMSRDBMS
How data is storedAs files, or in a hierarchy or network of records. No requirement to be tabular.In tables of rows and columns, always.
RelationshipsNot enforced. If two files are meant to relate, your program keeps them in step.Enforced by the database through primary and foreign keys.
NormalisationNot supported as a concept.Supported, and the whole point of the normal forms.
ACID propertiesGenerally not guaranteed.Guaranteed — atomicity, consistency, isolation, durability.
Multi-user accessUsually single user, or no real concurrency control.Many users at once, with locking and isolation levels.
Data volumeSuited to small amounts.Scales to large amounts.
ExamplesMicrosoft Access in its file mode, XML stores, older file-based systems.MySQL, PostgreSQL, Oracle, SQL Server.

Where people lose the marks

The mistake in the exam is writing "RDBMS stores data in tables and DBMS does not" and stopping there. That earns one mark out of five. The marks are in the consequences of the tables — keys, normalisation, ACID and concurrency — because those are what the relational model buys you.

File system vs DBMS

A file system stores bytes and leaves every question of meaning, duplication and simultaneous access to the program that opens the file. A DBMS answers those questions itself.

 File systemDBMS
Data redundancyHigh. The same customer's address ends up in four files.Controlled, through normalisation and a single source of truth.
Data consistencyYour problem. Update three copies, miss one, and the data is now wrong.Maintained by constraints and transactions.
QueryingYou write code to read and filter the file yourself.You write a query and the engine works out how to answer it.
Concurrent accessTwo writers at once corrupt the file.Locking and isolation keep writers from colliding.
SecurityWhole-file permissions at best.Per-table, per-column, per-user privileges.
RecoveryWhatever backup you remembered to take.Logs and rollback — an interrupted transaction leaves no half-done state.

Where people lose the marks

This question is really asking why databases were invented at all. The strongest answer walks through one concrete example — the same address stored in four files, one of them updated — because every other difference on the list follows from that.

DDL vs DML

DDL changes the shape of the database. DML changes what is inside that shape.

 DDLDML
Full formData Definition LanguageData Manipulation Language
What it acts onThe structure — tables, columns, constraints, indexes.The rows inside the tables.
CommandsCREATE, ALTER, DROP, TRUNCATE, RENAMESELECT, INSERT, UPDATE, DELETE
Commit behaviourAuto-committed. The change is permanent as soon as it runs.Not auto-committed. You can ROLLBACK before you COMMIT.
Can it be rolled back?No.Yes, until committed.

Where people lose the marks

The one that catches people is TRUNCATE. It deletes every row, which feels like DML, but it is DDL — it drops and recreates the table structure, it is auto-committed, and you cannot roll it back. DELETE removes rows one at a time, is DML, and can be undone. That single distinction is a question on its own in most papers.

Primary key vs Foreign key

A primary key identifies a row in its own table. A foreign key points at a primary key in another table.

 Primary keyForeign key
PurposeUniquely identify each row of this table.Link this row to a row in another table.
DuplicatesNot allowed.Allowed — many orders can belong to one customer.
NULLNever.Permitted, unless the column is declared NOT NULL.
How many per tableExactly one.As many as there are relationships.
IndexCreated automatically.Not automatic in most engines — you usually want to add one.

Where people lose the marks

Draw it. Two boxes, an arrow from the foreign key to the primary key it references, and the whole thing becomes obvious. Students who memorise the table without drawing it lose the follow-up question about what happens on delete.

Multiple choice, with the reason

Universities set an objective section and campus written rounds are almost nothing else. The answer is marked, and every one of them says why — an answer key you can only memorise is worth nothing in a viva.

  1. 01

    Which normal form removes transitive dependency?

    • a1NF
    • b2NF
    • c3NF
    • dBCNF

    Why — 1NF removes repeating groups, 2NF removes partial dependency on part of a composite key, and 3NF removes dependency of a non-key attribute on another non-key attribute. BCNF is a stricter form of 3NF.

  2. 02

    Which SQL clause filters rows AFTER they have been grouped?

    • aWHERE
    • bHAVING
    • cGROUP BY
    • dORDER BY

    Why — WHERE filters individual rows before grouping; HAVING filters the groups themselves. 'Departments with more than five employees' needs HAVING, because the count does not exist until the grouping has happened.

  3. 03

    Which statement about DELETE and TRUNCATE is correct?

    • aBoth can be rolled back
    • bDELETE can be rolled back, TRUNCATE usually cannot
    • cTRUNCATE can have a WHERE clause
    • dBoth remove the table structure

    Why — DELETE is DML, logs each row and can be rolled back. TRUNCATE is DDL, deallocates pages, resets any auto-increment and in most systems cannot be undone. Neither removes the table itself — that is DROP.

  4. 04

    The 'I' in ACID stands for isolation, which guarantees that:

    • aEach transaction runs on its own server
    • bConcurrent transactions do not see each other's uncommitted work
    • cData is stored in isolated files
    • dA failed transaction is logged separately

    Why — Isolation is about visibility between concurrent transactions, not about physical separation. It is what prevents a dirty read — one transaction reading a change another has not yet committed.

  5. 05

    A foreign key in a table refers to:

    • aA primary key in another table
    • bAny column in the same table
    • cAn index on the same table
    • dA column that must be unique

    Why — It points at the primary key, or a unique key, of another relation, and that is how referential integrity is enforced — you cannot insert a child row whose parent does not exist.

  6. 06

    Which join returns rows from the left table even when there is no match on the right?

    • aINNER JOIN
    • bLEFT OUTER JOIN
    • cRIGHT OUTER JOIN
    • dCROSS JOIN

    Why — A LEFT JOIN keeps every left-hand row and fills the right-hand columns with nulls where nothing matched. Combined with IS NULL it is how you find records with nothing attached — students enrolled in no course, for instance.

  7. 07

    In the three-schema architecture, which level describes HOW data is stored?

    • aExternal
    • bConceptual
    • cInternal
    • dLogical

    Why — Internal is the physical storage level. Conceptual is the logical structure of the whole database, and external is the individual user's view of it. Physical data independence is the ability to change the internal level without touching the conceptual one.

  8. 08

    What does an index primarily trade away in return for faster reads?

    • aAccuracy
    • bWrite speed and storage
    • cReferential integrity
    • dTransaction isolation

    Why — Every insert, update and delete must also maintain the index, and the index occupies disk. That is why you index the columns you filter and join on, not every column.

Viva questions, and what a good answer sounds like

GNDU’s own scheme says the viva runs one-to-one beside the practical exam, about the code you are writing at that moment. These are the questions that get asked, and what the examiner is listening for in the reply.

“What is the difference between a primary key and a unique key?”

Both enforce uniqueness. A primary key cannot be null and there is exactly one per table; a unique key allows one null in most systems and a table can have several. The primary key is also what foreign keys normally reference.

“Your query is slow. Where do you look first?”

At what the query is filtering and joining on, and whether those columns are indexed. Then at whether a join condition is missing, which turns the result into a Cartesian product. Reading the execution plan is the honest next step.

“Why normalise at all, if joins cost time?”

To stop the same fact being stored in several places, which is what makes updates go wrong. Denormalising for speed is a deliberate decision taken afterwards, with the duplication accepted knowingly — not a reason to skip normalising in the first place.

“Show me a query that would return more rows than you expect.”

A join written with a comma and no condition, or an ON clause on the wrong column. Every row is paired with every row, so two hundred-row tables return ten thousand. Naming it as a Cartesian product is the answer being looked for.

“What is a view, and does it store data?”

A saved query that is used like a table. It stores no data of its own — it runs underneath each time it is selected from. A materialised view does store results, and then has to be refreshed.

“Explain the keys in the table you just created.”

Name which column identifies a row and why nothing else would do, which columns point at other tables, and what would happen if the foreign key were removed. This is the question that separates somebody who designed the schema from somebody who typed it.

Past the syllabus

Your paper stops somewhere, and a job interview does not. If you want the version of this subject that goes further than the scheme asks for, there is a full course for it.

CallWhatsAppFree demo