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
This is not only a page about DBMS. It is a subject we teach in a classroom on Majitha Road, 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Three related tables with proper keys. Everything else in the practical file runs against these.
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);Join, aggregate, group with having, subquery, and a left join to find what is missing.
-- 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;The two objects the practical file usually ends on, and the ones interviews ask about.
-- 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;Not model answers to reproduce. What the examiner is checking for, and where the marks actually sit in each one.
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.
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.
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.
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.
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.