NSCT Databases & SQL Revision Guide

Databases is one of the most predictable NSCT subjects to revise. The same ideas keep returning: keys, joins, grouping, normal forms, transactions and indexes. The weightage published for the NSCT syllabus lists Databases at 10%; confirm the current figure, along with the question count, duration and marking scheme, in the official student guide on nsct.hec.gov.pk.

This guide condenses a semester of database theory into what MCQs actually test, with small tables you can trace by hand and worked questions that show how to reject wrong options. For the full topic list, see the Databases subject page and the NSCT syllabus page.

The Relational Model in Five Minutes

A relation is a table, a tuple is a row and an attribute is a column. The set of allowed values for an attribute is its domain. Two counting terms cause frequent mistakes:

• Degree is the number of attributes (columns).
• Cardinality is the number of tuples (rows).

Two integrity rules underpin everything else. Entity integrity says a primary key cannot be NULL. Referential integrity says a foreign key value must either match an existing primary key value in the referenced table or be NULL (if the column allows NULL).

In relational algebra, selection (σ) filters rows, projection (π) picks columns, and a join (⋈) combines tables on a condition. Mixing up selection and projection is one of the easiest marks to lose.

Keys

A quick counting fact: if relation R(A, B, C, D) has A as its only candidate key, every super key must contain A, and each of B, C and D can be in or out. That gives 2³ = 8 super keys.

SQL Joins with a Worked Example

Joins are the heart of the SQL MCQs. Use these two tables for everything in this section.

Students

Departments

INNER JOIN

SELECT s.name, d.dept_name
FROM Students s
INNER JOIN Departments d ON s.dept_id = d.dept_id;

Three rows. Hamza disappears because NULL never equals anything, not even another NULL. The AI department disappears because no student references it.

LEFT JOIN

SELECT s.name, d.dept_name
FROM Students s
LEFT JOIN Departments d ON s.dept_id = d.dept_id;

Four rows: every student appears, and Hamza gets NULL for the department columns. Row order is not guaranteed without ORDER BY.

For the same tables, a RIGHT JOIN returns four rows (the three matches plus AI with a NULL name). A FULL OUTER JOIN returns five (the three matches, Hamza and AI). A CROSS JOIN returns 4 × 3 = 12 rows.

GROUP BY, HAVING and Aggregates

The difference between WHERE and HAVING is simple once you know the logical order in which a query is evaluated:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE filters individual rows before grouping, so it cannot use aggregates like COUNT(*). HAVING filters groups after grouping, so it can.

Also remember that COUNT(*) counts rows, while COUNT(column) skips NULLs. In the Students table above, COUNT(*) is 4 but COUNT(dept_id) is 3.

Worked Example 1: WHERE vs HAVING

Employees

Question. How many rows does this query return?

SELECT dept, COUNT(*)
FROM Employees
WHERE salary > 60000
GROUP BY dept
HAVING COUNT(*) >= 2;

• A) 0
• B) 1
• C) 2
• D) 3

Reasoning. Apply the clauses in logical order.

1. WHERE salary > 60000 keeps Asad, Bilal, Fatima and Zara. Hina (exactly 60000) fails the strict inequality, and Usman (50000) fails too.
2. GROUP BY dept forms two groups: CS with 3 rows and SE with 1 row.
3. HAVING COUNT(*) >= 2 keeps only CS.

Answer: B. The single row is (CS, 3).

Why the distractors are wrong. C comes from skipping the WHERE clause: without it, CS has 3 and SE has 2, so both groups pass. D is the number of departments before any filtering. A would need every group to fail HAVING.

Normalization from 1NF to BCNF

Normalization removes redundancy caused by functional dependencies (FDs). An FD X → Y means that rows agreeing on X must also agree on Y. A prime attribute is one that belongs to some candidate key. Practise these rules in the normalization MCQs.

Every BCNF relation is in 3NF, but not the other way round. A decomposition into BCNF can always be made lossless, but it may not preserve every dependency. A 3NF decomposition can always be both lossless and dependency-preserving.

Worked Decomposition

Start with one wide table for course enrolments:

Enrolment(StudentID, CourseID, StudentName, CourseTitle, Instructor, InstructorOffice, Grade)

with these FDs:

• StudentID → StudentName
• CourseID → CourseTitle, Instructor
• Instructor → InstructorOffice
• {StudentID, CourseID} → Grade

The candidate key is {StudentID, CourseID}.

Step 1: 2NF. StudentName depends only on StudentID, and CourseTitle and Instructor depend only on CourseID. These are partial dependencies on part of the key. Split them out:

• Student(StudentID, StudentName)
• Course(CourseID, CourseTitle, Instructor, InstructorOffice)
• Result(StudentID, CourseID, Grade)

Step 2: 3NF. In Course, CourseID → Instructor → InstructorOffice is a transitive dependency: Instructor is not a super key and InstructorOffice is not prime. Split again:

• Course(CourseID, CourseTitle, Instructor)
• Instructor(Instructor, InstructorOffice)

All four resulting tables are now in 3NF, and each is also in BCNF, because every determinant is a key of its table.

Step 3: When 3NF is not BCNF. Consider Teaching(Student, Subject, Teacher) where {Student, Subject} → Teacher and Teacher → Subject (each teacher teaches one subject). The candidate keys are {Student, Subject} and {Student, Teacher}, so every attribute is prime and the relation is in 3NF. But Teacher → Subject has a determinant that is not a super key, so it violates BCNF. Decomposing into (Teacher, Subject) and (Student, Teacher) is lossless, but the dependency {Student, Subject} → Teacher can no longer be checked within a single table.

Worked Example 2: Highest Normal Form

Question. Relation R(A, B, C, D) has FDs A → B and B → C. What is the highest normal form R satisfies?

• A) 1NF
• B) 2NF
• C) 3NF
• D) BCNF

Reasoning. First find the key. A determines B, and B determines C, so A⁺ = {A, B, C}. Nothing determines D, so D must be in every key. The candidate key is {A, D}. Now B is non-prime and depends on A, which is only part of the key. That is a partial dependency, so R fails 2NF.

Answer: A.

Why the distractors are wrong. B (2NF) is what students pick when they assume A alone is the key and only notice the transitive dependency B → C. C and D need 2NF to hold first.

Transactions and ACID

A transaction is a unit of work that must behave as a single operation. The transaction management MCQs test the four ACID properties and what implements them.

• Atomicity: all of the transaction's changes happen, or none do. Implemented with undo information in the log.
• Consistency: a transaction takes the database from one valid state to another, respecting constraints.
• Isolation: concurrent transactions do not see each other's intermediate states beyond what the isolation level allows.
• Durability: once committed, changes survive crashes. Implemented with write-ahead logging and redo.

Two-phase locking (2PL) guarantees conflict-serializable schedules: a transaction acquires locks in a growing phase and releases them in a shrinking phase. Strict 2PL holds exclusive locks until commit, which avoids cascading aborts. 2PL does not prevent deadlocks.

Isolation Levels and Anomalies

• Dirty read: reading data another transaction has written but not committed.
• Non-repeatable read: reading the same row twice and getting different values because another transaction committed an update in between.
• Phantom read: re-running a query with a condition and getting a different set of rows because another transaction inserted or deleted matching rows.

This is the SQL standard's minimum guarantee; real systems can be stricter. The PostgreSQL transaction isolation documentation notes, for example, that PostgreSQL's Read Uncommitted behaves like Read Committed, and its Repeatable Read also prevents phantom reads. For an MCQ, answer with the standard table unless a specific DBMS is named.

Worked Example 3: Naming the Anomaly

Question. Transaction T1 reads an account balance of 1000. Transaction T2 then updates the balance to 500 and commits. T1 reads the same row again and sees 500. Which anomaly occurred, and what is the lowest standard isolation level that prevents it?

• A) Dirty read; Read Committed
• B) Non-repeatable read; Repeatable Read
• C) Phantom read; Serializable
• D) Lost update; Read Committed

Reasoning. T2 committed before T1's second read, so T1 never saw uncommitted data; it is not a dirty read. The same row returned a different value within one transaction, which is the definition of a non-repeatable read. From the table, Repeatable Read is the lowest level that prevents it.

Answer: B.

Why the distractors are wrong. A needs T1 to read T2's value before T2 commits. C concerns rows appearing or vanishing from a result set, not a changed value in an existing row. D describes one transaction overwriting another's update, but T1 never wrote anything here.

Indexing

An index speeds up reads at the cost of extra storage and slower inserts, updates and deletes, because every index must be maintained. Practise the details in the indexing MCQs.

• B+ tree indexes keep all data pointers in the leaf level, and the leaves are linked. This supports equality lookups, range queries and ORDER BY efficiently, with O(log n) search.
• Hash indexes support equality lookups well but do not help with range queries such as salary BETWEEN 50000 AND 80000.
• A clustered index determines the physical order of rows, so a table can have at most one. A non-clustered (secondary) index is a separate structure pointing to rows, and a table can have many.
• A composite index on (last_name, first_name) is most effective when the query constrains the leading column, last_name.
• Columns with very few distinct values (low selectivity) are usually poor candidates for a B+ tree index.

NoSQL Basics

NoSQL databases relax parts of the relational model to scale horizontally or to fit data that is not naturally tabular.

The CAP theorem states that when a network partition occurs, a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets a response). Many NoSQL systems favour availability with eventual consistency, described by the acronym BASE (basically available, soft state, eventually consistent), in contrast to ACID. See the NoSQL MCQs for practice.

Quick Revision Checklist

• Can you tell degree from cardinality, and selection from projection?
• Can you write the row count of INNER, LEFT, RIGHT, FULL and CROSS joins for two small tables?
• Do you know why WHERE cannot use COUNT(*)?
• Can you find a candidate key from a set of FDs using attribute closure?
• Can you explain why a relation can be in 3NF but not BCNF?
• Can you match each isolation level to the anomalies it allows?

Turning Revision into Practice

Database MCQs reward careful tracing more than memorisation. Rebuild the sample tables above on paper, change one value, and predict the new result before checking it in a real database. The official PostgreSQL documentation is a reliable place to confirm SQL behaviour. Then work through topic quizzes from the Databases subject page, where NSCT Prep's 33,808+ practice MCQs include SQL, normalization and transaction questions with explanations.