Unit 5: Database Management System

DATABASE MANAGEMENT SYSTEM

UGC NET / JRF — Computer Science & Applications

High-Yield Study Notes & PYQ-Pattern Workbook (Unit 5)

Contents

Ch.1 — What is DBMS?

Ch.2 — Data Modelling

Ch.3 — Relational Data Model

Ch.4 — Normalization

Ch.5 — Transaction Processing

Ch.6 — Concurrency Control

Ch.7 — Big Data

Ch.8 — SQL

Ch.9 — NoSQL Database

Ch.10 — Data Warehouse, Data Mining

One-Shot Revision

Mini Mock Test (Answer Key)

Beginner → Concept → NET-level → JRF-level. Compact by design — exam value over page count.

Chapter 1 — What is DBMS?

1.1 DBMS vs File System

SimpleA DBMS (Database Management System) is software that stores, organizes and manages data — solving the problems of the older "file processing" approach (separate flat files per application).

Problem in File Systems	How DBMS Solves It
Data redundancy & inconsistency	Centralized data, single source of truth
Difficulty in accessing data	Query languages (SQL) for flexible retrieval
Data isolation	Unified format across applications
Integrity problems	Constraints enforced centrally (e.g., NOT NULL, CHECK)
Atomicity/concurrency issues	Transaction management (ACID)

1.2 Three-Schema Architecture & Data Independence

Level	Describes
External (View level)	How individual users see the data (custom views, may hide columns)
Conceptual (Logical level)	The overall community/logical structure of the whole database
Internal (Physical level)	How data is actually physically stored (files, indexes, storage structures)

JRF trap — logical vs physical data independenceLogical data independence = ability to change the CONCEPTUAL schema (e.g., add a new table/attribute) WITHOUT changing external schemas/application programs. Physical data independence = ability to change the INTERNAL (storage) schema (e.g., change indexing, file organization) WITHOUT changing the conceptual schema. Logical data independence is generally considered HARDER to achieve than physical — a frequently tested distinction.

1.3 Database Users & DBA Roles

• DBA (Database Administrator): manages schema definition, storage structure, authorization, backup/recovery.
• Application programmers: write programs that interact with the database (via embedded SQL, APIs).
• Sophisticated/casual/naive end users: interact via queries, applications, or fixed forms respectively.

1.4 Data Abstraction & Instances/Schema

IdeaThe SCHEMA is the overall design/structure of the database (relatively stable, changes rarely). An INSTANCE is the actual data stored in the database at a particular moment (changes constantly as data is inserted/updated/deleted).

TrapDon't confuse Schema (structure, like a blueprint) with Instance (actual data, like the furnished house) — a very basic but frequently tested NET distinction.

MUST REMEMBER — Chapter 1

• Three-schema architecture: External (user views) → Conceptual (logical structure) → Internal (physical storage).
• Logical data independence: change conceptual schema without affecting external schema (harder to achieve).
• Physical data independence: change internal/storage schema without affecting conceptual schema (easier).
• Schema = structure (stable); Instance = actual data at a point in time (changes constantly).

DON'T CONFUSE

• Logical data independence (conceptual↔external insulation) vs Physical data independence (internal↔conceptual insulation).
• Schema (design) vs Instance (data snapshot).

JRF CHALLENGE ZONE — Chapter 1

1. Changing the indexing structure of a table WITHOUT affecting the conceptual schema demonstrates: (a) Logical data independence (b) Physical data independence (c) View independence (d) None
Answer: (b)

2. Which is generally considered HARDER to achieve? (a) Physical data independence (b) Logical data independence (c) Both equally easy (d) Neither is achievable
Answer: (b)

Practice Questions — Chapter 1 (6)

1. Name three problems of traditional file-processing systems that DBMS solves.
   Ans: Any three of: data redundancy/inconsistency, difficulty accessing data, data isolation, integrity problems, atomicity/concurrency issues
2. List the three levels of the three-schema architecture.
   Ans: External (view), Conceptual (logical), Internal (physical)
3. Differentiate logical and physical data independence.
   Ans: Logical = changing the conceptual schema without affecting external schemas; Physical = changing the internal/storage schema without affecting the conceptual schema
4. Differentiate a database Schema and an Instance.
   Ans: Schema is the overall structure/design (stable); Instance is the actual stored data at a given moment (changes constantly)
5. What is the main role of a DBA?
   Ans: Managing schema definition, storage structure, authorization, and backup/recovery
6. Which data independence type is generally easier to achieve, and why?
   Ans: Physical — because storage-level changes (like indexing) rarely affect the logical structure applications depend on

---

Chapter 2 — Data Modelling

2.1 ER Model — Entities, Attributes, Relationships

Term	Meaning
Entity	A real-world object/thing distinguishable from others (e.g., a specific Student)
Entity Set	A collection of similar entities (e.g., all Students)
Attribute	A property describing an entity (e.g., Name, Age)
Relationship	An association between two or more entities (e.g., Student ENROLLS_IN Course)

2.2 Types of Attributes

Type	Meaning
Simple	Cannot be divided further (e.g., Age)
Composite	Can be divided into sub-parts (e.g., Name → First + Last)
Single-valued	Only one value per entity (e.g., DOB)
Multi-valued	Can have multiple values (e.g., Phone Numbers) — shown with a double oval
Derived	Computed from another attribute (e.g., Age derived from DOB) — shown with a dashed oval

2.3 Relationship Cardinality

Cardinality	Meaning
One-to-One (1:1)	Each entity in A relates to at most one in B, and vice versa
One-to-Many (1:N)	One entity in A can relate to many in B, but each B relates to only one A
Many-to-Many (M:N)	Entities in A can relate to many in B and vice versa

JRF trapWhen converting an ER diagram to relational tables: a 1:1 or 1:N relationship can often be represented by adding a FOREIGN KEY to one of the existing tables (the "many" side holds the FK in 1:N). But an M:N relationship ALWAYS requires a SEPARATE junction/bridge table containing the primary keys of both entities — you cannot represent M:N with just a foreign key in either table. This exact "when do you need a separate table" logic is heavily tested.

2.4 Keys in the ER Model

IdeaA key attribute uniquely identifies each entity in an entity set (shown underlined in ER diagrams). A WEAK entity has no key attribute of its own — it depends on a "strong"/owner entity's key plus a "partial key" (discriminator) to be uniquely identified (e.g., "Dependent" of an "Employee").

2.5 Extended ER — Generalization, Specialization, Aggregation

Concept	Idea
Generalization	BOTTOM-UP: combining multiple lower-level entities that share common attributes into one higher-level entity (e.g., Car + Truck → Vehicle)
Specialization	TOP-DOWN: dividing a higher-level entity into lower-level sub-entities with distinct attributes (e.g., Employee → Manager, Engineer)
Aggregation	Treats a RELATIONSHIP itself as a higher-level entity, so it can participate in further relationships

JRF trap — generalization vs specialization directionGeneralization works BOTTOM-UP (combining similar entities upward into a general one). Specialization works TOP-DOWN (breaking a general entity down into specialized subtypes). Students very commonly swap these two directions — a classic NET/JRF trap.

MUST REMEMBER — Chapter 2

• Multi-valued attributes → double oval; Derived attributes → dashed oval.
• M:N relationships always need a separate junction table when converting to relational tables; 1:1/1:N can use a foreign key instead.
• A weak entity has no key of its own — needs owner entity's key + a partial key (discriminator).
• Generalization = bottom-up (combine into general); Specialization = top-down (split into specific).
• Aggregation treats a relationship as a higher-level entity, allowing it to participate in further relationships.

DON'T CONFUSE

• Generalization (bottom-up) vs Specialization (top-down) — opposite directions.
• Weak entity (no own key) vs Strong entity (has its own key).

JRF CHALLENGE ZONE — Chapter 2

1. Combining "Car" and "Truck" entities (which share common attributes) into a single "Vehicle" entity is an example of: (a) Specialization (b) Generalization (c) Aggregation (d) Normalization
Answer: (b)

2. An M:N relationship between Student and Course, when converted to relational tables, requires: (a) A foreign key in Student only (b) A foreign key in Course only (c) A separate junction table with both PKs (d) No extra table needed
Answer: (c)

Practice Questions — Chapter 2 (7)

1. Differentiate an entity and an entity set.
   Ans: An entity is one specific real-world object; an entity set is the collection of similar entities
2. How are multi-valued and derived attributes shown in an ER diagram?
   Ans: Multi-valued: double oval; Derived: dashed oval
3. What is a weak entity, and what does it need to be uniquely identified?
   Ans: An entity with no key attribute of its own; needs its owner entity's key plus a partial key (discriminator)
4. Why does an M:N relationship require a separate junction table when converted to relational form?
   Ans: Because neither table alone can hold a single foreign key representing a many-to-many association; a bridge table with both primary keys is needed
5. Differentiate Generalization and Specialization.
   Ans: Generalization combines similar lower-level entities into one general entity (bottom-up); Specialization splits a general entity into specific sub-entities (top-down)
6. What does Aggregation allow in the ER model?
   Ans: Treating a relationship as a higher-level entity so it can itself participate in further relationships
7. Give an example each of a simple and a composite attribute.
   Ans: Simple: Age; Composite: Name (split into First Name + Last Name)

---

Chapter 3 — Relational Data Model

3.1 Keys — Candidate, Primary, Foreign, Super

Key Type	Meaning
Super Key	Any set of attributes that uniquely identifies a tuple (may have extra/unnecessary attributes)
Candidate Key	A MINIMAL super key — no attribute can be removed without losing uniqueness
Primary Key	The candidate key CHOSEN by the designer to uniquely identify tuples (cannot be NULL)
Foreign Key	An attribute referencing the primary key of another (or the same) table, enforcing referential integrity

JRF trapEvery Candidate Key is a Super Key, but not every Super Key is a Candidate Key (a super key can have redundant attributes). Every Primary Key is a Candidate Key, but a table can have MULTIPLE candidate keys and only ONE is chosen as primary — the others are called "Alternate Keys". This hierarchy (Super ⊇ Candidate ⊇ {Primary}) is frequently tested.

3.2 Integrity Constraints

Constraint	Meaning
Entity Integrity	Primary key attribute(s) can NEVER be NULL
Referential Integrity	A foreign key value must either be NULL or match an existing primary key value in the referenced table
Domain Constraint	Each attribute's value must come from its defined domain (data type/range)

3.3 Relational Algebra — Basic Operations

Operator	Symbol	Meaning
Select	σ (sigma)	Filters ROWS based on a condition
Project	π (pi)	Selects specific COLUMNS (removes duplicates)
Union	∪	Combines rows from two UNION-COMPATIBLE relations
Set Difference	−	Rows in R1 but NOT in R2
Cartesian Product	×	All possible combinations of rows from R1 and R2
Join (Natural)	⋈	Combines rows from two relations based on matching common attribute(s)

JRF trap — σ vs πStudents very commonly swap Select (σ, filters ROWS, e.g. σ(age>20)) and Project (π, filters COLUMNS, e.g. π(name,age)). Remember: σelnarrows down rows (like a WHERE clause); πicks columns (like the SELECT column-list). Also: Union requires the two relations to be UNION-COMPATIBLE (same number of attributes, matching domains) — mismatched relations cannot be unioned.

Worked example — relational algebra expressionFind names of employees earning >50000: π_name(σ_salary>50000(Employee)). Note the ORDER: Select (filter rows) happens INSIDE first, then Project (pick columns) is applied to that filtered result — reading relational algebra expressions inside-out is exactly how JRF numericals are evaluated.

3.4 Relational Calculus (Brief)

IdeaRelational Calculus is DECLARATIVE (says WHAT to retrieve, not HOW) — unlike Relational Algebra which is PROCEDURAL (specifies the sequence of operations). Tuple Relational Calculus (TRC) uses tuple variables; Domain Relational Calculus (DRC) uses domain variables (individual attribute values).

MUST REMEMBER — Chapter 3

• Key hierarchy: Super Key ⊇ Candidate Key ⊇ {Primary Key}; unchosen candidate keys = Alternate Keys.
• Entity Integrity: primary key can never be NULL. Referential Integrity: FK must be NULL or match an existing PK.
• σ (Select) = filters ROWS; π (Project) = selects COLUMNS.
• Union needs union-compatible relations (same attribute count/matching domains).
• Relational Algebra = procedural (how); Relational Calculus = declarative (what).

DON'T CONFUSE

• Select σ (rows) vs Project π (columns) — the most common relational-algebra mix-up.
• Super Key (may have extra attributes) vs Candidate Key (minimal).

JRF CHALLENGE ZONE — Chapter 3

1. Which operation removes duplicate rows automatically by definition? (a) σ (Select) (b) π (Project) (c) × (Cartesian product) (d) None
Answer: (b)

2. A table has candidate keys {RollNo} and {Email}. RollNo is chosen as primary key. Email is called: (a) Foreign key (b) Super key only (c) Alternate key (d) Not a key at all
Answer: (c)

Practice Questions — Chapter 3 (7)

1. Differentiate a Super Key and a Candidate Key.
   Ans: A super key may have redundant attributes; a candidate key is minimal (no attribute can be removed without losing uniqueness)
2. What is Entity Integrity?
   Ans: The rule that primary key attributes can never be NULL
3. What is Referential Integrity?
   Ans: A foreign key value must be NULL or match an existing primary key value in the referenced table
4. Differentiate the Select (σ) and Project (π) operations.
   Ans: Select filters rows based on a condition; Project selects specific columns (removing duplicates)
5. What condition must two relations satisfy to be union-compatible?
   Ans: Same number of attributes with matching domains
6. Differentiate Relational Algebra and Relational Calculus.
   Ans: Relational Algebra is procedural (specifies how to retrieve data); Relational Calculus is declarative (specifies what to retrieve)
7. What is an Alternate Key?
   Ans: A candidate key that was not chosen as the primary key

---

Chapter 4 — Normalization

4.1 Functional Dependency (FD)

SimpleA functional dependency X→Y means: if two tuples have the SAME value for X, they MUST have the same value for Y (X "determines" Y).

Closure & Armstrong's Axioms Reflexivity: if Y⊆X, then X→Y Augmentation: if X→Y, then XZ→YZ Transitivity: if X→Y and Y→Z, then X→Z

JRF-level numerical — closure computationGiven R(A,B,C,D,E) with FDs: A→B, B→C, C→D, D→E. Find (A)⁺ (closure of A). Start: {A}. A→B, add B: {A,B}. B→C, add C: {A,B,C}. C→D, add D: {A,B,C,D}. D→E, add E: {A,B,C,D,E}. (A)⁺ = {A,B,C,D,E} = all attributes → A is a CANDIDATE KEY (since its closure covers the whole relation). This "compute the closure to find keys" technique is THE fundamental JRF normalization numerical.

4.2 Normal Forms — 1NF to BCNF

Normal Form	Requirement
1NF	All attribute values are ATOMIC (no repeating groups/multi-valued attributes in a cell)
2NF	1NF + NO partial dependency (no non-key attribute depends on only PART of a composite primary key)
3NF	2NF + NO transitive dependency (no non-key attribute depends on another non-key attribute)
BCNF	For every FD X→Y, X must be a SUPER KEY (stricter than 3NF)

JRF-level numerical — identifying the highest normal formR(A,B,C) with primary key (A,B), and FD: C→B (C determines B, where C is a non-prime attribute and B is part of the key). This is a "non-prime → prime" dependency, which is fine for 3NF (3NF only forbids non-prime→non-prime, i.e., transitive dependency on the whole key) BUT it violates BCNF (since C is NOT a super key, yet C→B exists). So this relation is in 3NF but NOT BCNF — this exact "3NF-but-not-BCNF" scenario is a favourite JRF trap because it requires knowing BCNF's stricter "must be a super key" rule.

Trap — partial vs transitive dependencyPartial dependency = non-key attribute depends on only PART of a COMPOSITE primary key (violates 2NF; can't happen with a single-attribute key). Transitive dependency = non-key attribute depends on ANOTHER non-key attribute, not directly on the key (violates 3NF). Confusing these two dependency types is very common.

4.3 Decomposition — Lossless Join & Dependency Preservation

IdeaWhen decomposing a relation R into R1 and R2, TWO properties are desired: Lossless Join: joining R1 and R2 back together recovers EXACTLY the original R (no spurious/extra tuples) — guaranteed if (R1∩R2) is a super key of EITHER R1 or R2. Dependency Preservation: all original functional dependencies can still be checked/enforced using only R1 and R2, without needing to join them back.

JRF trapBCNF decomposition ALWAYS guarantees lossless join, but does NOT always guarantee dependency preservation (sometimes a dependency spans attributes now split across R1 and R2, and can't be checked in either alone). 3NF decomposition guarantees BOTH lossless join AND dependency preservation — this "3NF is always achievable with both properties, but BCNF might sacrifice dependency preservation" tradeoff is a very high-yield JRF fact.

MUST REMEMBER — Chapter 4

• Armstrong's Axioms: Reflexivity, Augmentation, Transitivity — used to compute FD closures.
• Compute attribute closure to test candidate keys: if (X)⁺ = all attributes, X is a candidate key (or superkey).
• 1NF=atomic values; 2NF=1NF+no partial dependency; 3NF=2NF+no transitive dependency; BCNF=every determinant is a super key (strictest).
• Partial dependency needs a COMPOSITE key; transitive dependency is non-key→non-key.
• 3NF decomposition: always lossless AND dependency-preserving. BCNF decomposition: always lossless, but MAY sacrifice dependency preservation.

DON'T CONFUSE

• Partial dependency (part of a composite key) vs Transitive dependency (non-key → non-key).
• 3NF (allows non-prime→prime) vs BCNF (every determinant must be a super key, no exceptions).
• Lossless join (no data loss on rejoining) vs Dependency preservation (FDs checkable without rejoining).

JRF CHALLENGE ZONE — Chapter 4

1. R(A,B,C,D) with FD: AB→C, AB→D. What is the closure (AB)⁺?
Answer: {A,B,C,D} — the full relation, so AB is a candidate key.

2. A relation is in 3NF but has a determinant that is not a super key. It is: (a) Also in BCNF (b) Not in BCNF (c) Not even in 1NF (d) Automatically invalid
Answer: (b)

3. Which decomposition ALWAYS preserves both lossless join AND dependency preservation? (a) BCNF (b) 3NF (c) 1NF (d) Neither guarantees anything
Answer: (b)

Practice Questions — Chapter 4 (8)

1. What does the functional dependency X→Y mean?
   Ans: If two tuples agree on X, they must also agree on Y
2. R(A,B,C,D) with FDs A→B, B→C, C→D. Find (A)⁺.
   Ans: {A,B,C,D} — the whole relation, so A is a candidate key
3. What does 1NF require?
   Ans: All attribute values must be atomic (no repeating groups or multi-valued cells)
4. What is a partial dependency, and which normal form forbids it?
   Ans: A non-key attribute depending on only part of a composite primary key; forbidden by 2NF
5. What is a transitive dependency, and which normal form forbids it?
   Ans: A non-key attribute depending on another non-key attribute; forbidden by 3NF
6. What extra requirement does BCNF add beyond 3NF?
   Ans: Every determinant (left side of any FD) must be a super key, with no exceptions
7. What is a lossless-join decomposition?
   Ans: A decomposition where rejoining the parts recovers exactly the original relation, with no spurious tuples
8. Does 3NF decomposition always preserve dependencies? Does BCNF?
   Ans: 3NF always does; BCNF is always lossless but may NOT always preserve dependencies

---

Chapter 5 — Transaction Processing

5.1 ACID Properties

Property	Meaning
Atomicity	A transaction executes COMPLETELY or NOT AT ALL ("all or nothing")
Consistency	A transaction takes the database from one valid state to another, preserving all defined rules/constraints
Isolation	Concurrent transactions don't interfere — each appears to execute as if it were alone
Durability	Once committed, changes survive even a subsequent system crash (permanently saved)

JRF trapAtomicity and Durability are typically enforced by the RECOVERY MANAGER (using logs — e.g., write-ahead logging). Isolation is enforced by the CONCURRENCY CONTROL manager (locking, timestamps). Knowing WHICH subsystem is responsible for which ACID property is a common JRF question.

5.2 Transaction States

Active → Partially Committed → Committed ↓ ↓ Failed → Aborted

IdeaActive (executing) → Partially Committed (finished executing, not yet permanently saved) → Committed (successfully, permanently saved) OR → Failed → Aborted (rolled back to before the transaction started).

5.3 Schedules — Serial vs Concurrent

IdeaA SERIAL schedule executes transactions one completely after another (no interleaving) — always consistent, but no concurrency benefit. A CONCURRENT (interleaved) schedule interleaves operations from multiple transactions for better performance — but might cause inconsistency if not controlled properly.

5.4 Serializability

Type	Meaning
Conflict Serializable	A schedule can be transformed into a SERIAL schedule by swapping non-conflicting (consecutive) operations
View Serializable	A schedule is equivalent to a serial schedule with the same "view" (same initial reads, same final writes) — a WEAKER/broader condition than conflict serializability

JRF trap — conflicting operationsTwo operations CONFLICT only if: (1) they belong to DIFFERENT transactions, (2) they access the SAME data item, AND (3) at least ONE of them is a WRITE. Two reads never conflict, even on the same data item, even from different transactions. This precise 3-condition definition is exactly what JRF numericals on precedence graphs test.

Worked example — precedence graph for conflict serializabilitySchedule: T1:Read(A), T2:Write(A), T1:Write(A), T2:Read(B), T1:Write(B). Conflicts: T1-Read(A) then T2-Write(A) → edge T1→T2. T2-Write(A) then T1-Write(A) → edge T2→T1. Since we get BOTH T1→T2 AND T2→T1, the precedence graph has a CYCLE → the schedule is NOT conflict serializable. "Draw the precedence graph, check for a cycle" is the standard JRF technique for testing conflict serializability.

MUST REMEMBER — Chapter 5

• ACID: Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (no interference), Durability (permanent after commit).
• Recovery manager enforces Atomicity+Durability; Concurrency control enforces Isolation.
• Transaction states: Active→Partially Committed→Committed, or →Failed→Aborted.
• Two operations conflict only if: different transactions + same data item + at least one is a write.
• Conflict serializable: no cycle in the precedence graph. View serializable is a weaker/broader condition.

DON'T CONFUSE

• Conflict serializability (precedence graph, no cycle) vs View serializability (weaker condition).
• Atomicity (all-or-nothing execution) vs Isolation (no interference between concurrent transactions).

JRF CHALLENGE ZONE — Chapter 5

1. Two Read operations on the same data item, from different transactions: (a) Always conflict (b) Never conflict (c) Conflict only if isolation level is low (d) Conflict only in serial schedules
Answer: (b)

2. A schedule's precedence graph contains a cycle. The schedule is: (a) Conflict serializable (b) NOT conflict serializable (c) Automatically a serial schedule (d) Guaranteed consistent
Answer: (b)

3. Which ACID property is enforced primarily by the concurrency control subsystem? (a) Atomicity (b) Durability (c) Isolation (d) Consistency
Answer: (c)

Practice Questions — Chapter 5 (7)

1. List the four ACID properties.
   Ans: Atomicity, Consistency, Isolation, Durability
2. Which subsystem primarily enforces Atomicity and Durability?
   Ans: The recovery manager (using logs, e.g., write-ahead logging)
3. List the transaction states in order for a successful transaction.
   Ans: Active → Partially Committed → Committed
4. What three conditions must hold for two operations to conflict?
   Ans: Different transactions, same data item, and at least one operation is a write
5. Do two Read operations on the same data item ever conflict?
   Ans: No, never
6. How do you test whether a schedule is conflict serializable?
   Ans: Build its precedence graph from conflicting operations; if there is no cycle, it is conflict serializable
7. Differentiate conflict serializability and view serializability.
   Ans: Conflict serializability requires equivalence via swapping non-conflicting operations; view serializability is a weaker condition based only on matching reads and final writes

---

Chapter 6 — Concurrency Control

6.1 Lock-Based Protocols

Lock Type	Meaning
Shared Lock (S)	Allows READING; multiple transactions can hold a shared lock on the same item simultaneously
Exclusive Lock (X)	Allows READING and WRITING; only ONE transaction can hold it, and no other lock (S or X) can coexist on that item

6.2 Two-Phase Locking (2PL)

Idea2PL has two phases: GROWING phase (a transaction can ACQUIRE locks, but not release any) and SHRINKING phase (can RELEASE locks, but not acquire any new ones). Once a transaction releases its FIRST lock, it enters the shrinking phase and can never acquire another lock.

NET pointBasic 2PL guarantees CONFLICT SERIALIZABILITY but does NOT prevent DEADLOCK (two transactions can each hold a lock the other needs) and does NOT prevent CASCADING ROLLBACK (if T1 fails after other transactions read its uncommitted writes, they must also roll back).

JRF trap — Strict 2PLStrict 2PL additionally requires that ALL exclusive locks be held until the transaction COMMITS or ABORTS (not released early even in the shrinking phase) — this specifically prevents CASCADING ROLLBACK, which basic 2PL does not prevent. Confusing "basic 2PL" (only guarantees serializability) with "strict 2PL" (also prevents cascading rollback) is a common JRF trap.

6.3 Timestamp Ordering Protocol

IdeaEach transaction gets a unique timestamp when it starts. Conflicting operations are ordered strictly according to timestamp order — an older transaction's operations always logically precede a younger one's, ensuring serializability equivalent to the timestamp order, WITHOUT using locks at all.

JRF trapTimestamp ordering is DEADLOCK-FREE by design (no waiting for locks — a transaction attempting an out-of-order operation is simply ROLLED BACK and restarted with a new timestamp), unlike lock-based 2PL which CAN deadlock. This "timestamp ordering avoids deadlock entirely" fact is a frequently tested advantage.

6.4 Deadlock Handling in DBMS

IdeaSimilar to OS deadlock handling: Prevention (e.g., Wait-Die and Wound-Wait schemes, using timestamps to decide whether an older transaction waits or the younger one is aborted), Detection (using a wait-for graph — a cycle means deadlock), and Timeout-based methods (abort a transaction if it waits too long).

MUST REMEMBER — Chapter 6

• Shared lock = read-only, multiple holders OK; Exclusive lock = read+write, only one holder, no coexistence with any other lock.
• 2PL: Growing phase (acquire only) then Shrinking phase (release only) — guarantees conflict serializability.
• Basic 2PL does NOT prevent deadlock or cascading rollback; Strict 2PL (locks held till commit/abort) DOES prevent cascading rollback.
• Timestamp ordering: no locks, deadlock-free by design; violating transactions are rolled back and restarted.
• DB deadlock handling mirrors OS: prevention (Wait-Die/Wound-Wait), detection (wait-for graph cycle), timeout.

DON'T CONFUSE

• Basic 2PL (serializable, but can deadlock/cascade) vs Strict 2PL (also prevents cascading rollback).
• Lock-based protocols (can deadlock) vs Timestamp ordering (deadlock-free by design).

JRF CHALLENGE ZONE — Chapter 6

1. Which protocol is deadlock-free BY DESIGN, without needing any lock-waiting? (a) Basic 2PL (b) Strict 2PL (c) Timestamp ordering (d) None are deadlock-free
Answer: (c)

2. Basic 2PL guarantees: (a) Conflict serializability only (b) Conflict serializability AND no cascading rollback (c) No deadlock (d) Nothing useful
Answer: (a) — cascading-rollback prevention needs STRICT 2PL specifically.

Practice Questions — Chapter 6 (6)

1. Differentiate a Shared lock and an Exclusive lock.
   Ans: Shared allows reading with multiple simultaneous holders; Exclusive allows reading and writing with only one holder, incompatible with any other lock
2. What are the two phases of Two-Phase Locking?
   Ans: Growing phase (acquire locks only) and Shrinking phase (release locks only)
3. Does basic 2PL prevent deadlock?
   Ans: No — it only guarantees conflict serializability, not deadlock freedom
4. What extra guarantee does Strict 2PL provide over basic 2PL?
   Ans: It prevents cascading rollback, by holding exclusive locks until commit/abort
5. Why is timestamp ordering deadlock-free?
   Ans: It doesn't use locks/waiting — an out-of-order transaction is simply rolled back and restarted with a new timestamp
6. Name two deadlock prevention schemes used in DBMS based on timestamps.
   Ans: Wait-Die and Wound-Wait

---

Chapter 7 — Big Data

7.1 Characteristics of Big Data (the V's)

V	Meaning
Volume	Massive scale of data (terabytes to petabytes+)
Velocity	Speed at which data is generated/must be processed (e.g., streaming data)
Variety	Different formats — structured, semi-structured, unstructured
Veracity	Uncertainty/trustworthiness/quality of the data
Value	The usefulness/insight that can be extracted from the data

7.2 Structured, Semi-structured, Unstructured Data

IdeaStructured = fits a fixed schema (relational tables). Semi-structured = has SOME organizational structure/tags but no fixed rigid schema (e.g., JSON, XML). Unstructured = no predefined structure at all (e.g., images, videos, free text).

7.3 Hadoop & MapReduce (Overview)

IdeaHadoop is an open-source framework for distributed storage (HDFS) and processing of big data across clusters of commodity hardware. MapReduce is its programming model: the MAP phase processes/transforms input data in parallel into key-value pairs; the REDUCE phase aggregates/summarizes those key-value pairs into the final result.

JRF trapHDFS (Hadoop Distributed File System) achieves fault tolerance through DATA REPLICATION (storing multiple copies of each data block across different nodes, typically 3 by default) — NOT through RAID or backup systems. If a node fails, data is still available from replicated copies elsewhere in the cluster.

MUST REMEMBER — Chapter 7

• 5 V's: Volume, Velocity, Variety, Veracity, Value.
• Structured=fixed schema; Semi-structured=some tags/organization but no rigid schema (JSON/XML); Unstructured=no defined structure.
• MapReduce: Map phase transforms data into key-value pairs (parallel); Reduce phase aggregates results.
• HDFS achieves fault tolerance via data replication (typically 3 copies), not RAID.

DON'T CONFUSE

• Semi-structured (JSON/XML, has tags but flexible) vs Unstructured (no organization at all, e.g. raw text/images).

JRF CHALLENGE ZONE — Chapter 7

1. XML and JSON data are best classified as: (a) Structured (b) Semi-structured (c) Unstructured (d) None of these
Answer: (b)

2. HDFS achieves fault tolerance primarily through: (a) RAID (b) Data replication across nodes (c) Backup tapes (d) Compression
Answer: (b)

Practice Questions — Chapter 7 (5)

1. List the 5 V's of Big Data.
   Ans: Volume, Velocity, Variety, Veracity, Value
2. Differentiate structured, semi-structured, and unstructured data.
   Ans: Structured fits a fixed schema; semi-structured has some organization/tags without a rigid schema; unstructured has no predefined structure
3. What do the Map and Reduce phases of MapReduce do?
   Ans: Map transforms input data in parallel into key-value pairs; Reduce aggregates those pairs into the final result
4. How does HDFS achieve fault tolerance?
   Ans: Through data replication — storing multiple copies of each block across different nodes
5. Give an example each of structured and unstructured data.
   Ans: Structured: a relational database table; Unstructured: a video file or free-form text

---

Chapter 8 — SQL (Structured Query Language)

8.1 SQL Command Categories

Category	Commands	Purpose
DDL (Data Definition)	CREATE, ALTER, DROP, TRUNCATE	Defines/modifies schema structure
DML (Data Manipulation)	SELECT, INSERT, UPDATE, DELETE	Manipulates actual data
DCL (Data Control)	GRANT, REVOKE	Controls access/permissions
TCL (Transaction Control)	COMMIT, ROLLBACK, SAVEPOINT	Manages transactions

JRF trap — DROP vs TRUNCATE vs DELETEDROP removes the entire table STRUCTURE (schema gone). TRUNCATE removes ALL rows but KEEPS the table structure (fast, usually can't be rolled back, resets auto-increment). DELETE removes rows (optionally with a WHERE clause for specific rows), CAN be rolled back (it's a DML/logged operation), and does NOT reset auto-increment counters. This exact three-way distinction is one of the most frequently tested SQL facts.

8.2 SQL Query Clause Order

Written order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

JRF trap — WHERE vs HAVINGWHERE filters ROWS BEFORE grouping (cannot use aggregate functions like COUNT/SUM directly in WHERE). HAVING filters GROUPS AFTER grouping/aggregation (used specifically to filter based on aggregate function results, e.g., HAVING COUNT(*)>5). Trying to use an aggregate function in WHERE is a syntax error — this exact "why can't I use COUNT in WHERE" question is extremely common.

8.3 Joins

Join Type	Result
INNER JOIN	Only matching rows from BOTH tables
LEFT (OUTER) JOIN	ALL rows from left table + matching rows from right (NULLs where no match)
RIGHT (OUTER) JOIN	ALL rows from right table + matching rows from left (NULLs where no match)
FULL (OUTER) JOIN	ALL rows from BOTH tables (NULLs where no match on either side)

8.4 Aggregate Functions & GROUP BY

Worked exampleTable Employee(EmpID, Dept, Salary). Query: SELECT Dept, COUNT(*), AVG(Salary) FROM Employee GROUP BY Dept HAVING COUNT(*) > 2; This groups employees by department, computes count and average salary PER GROUP, then keeps only departments having MORE than 2 employees. This exact GROUP BY + aggregate + HAVING pattern is THE most common SQL query style tested in NET/JRF.

NET pointEvery column in the SELECT list that is NOT inside an aggregate function MUST appear in the GROUP BY clause — otherwise it's ambiguous which row's value to show for that group. This is a strict SQL rule frequently tested via "which of these queries is INVALID" questions.

8.5 Subqueries

IdeaA subquery is a query nested inside another. Can appear in SELECT, FROM, or WHERE clauses.

JRF trap — correlated vs non-correlated subqueryA NON-correlated subquery can run INDEPENDENTLY of the outer query (executed once). A CORRELATED subquery references a column from the OUTER query, so it must be RE-EVALUATED for EACH row processed by the outer query (much less efficient) — e.g., SELECT * FROM Emp e WHERE Salary > (SELECT AVG(Salary) FROM Emp WHERE Dept=e.Dept) is correlated because it references e.Dept from the outer query.

8.6 Constraints

Constraint	Meaning
NOT NULL	Column cannot have a NULL value
UNIQUE	All values in the column must be distinct (NULLs allowed, typically only once)
PRIMARY KEY	NOT NULL + UNIQUE combined, uniquely identifies each row
FOREIGN KEY	Enforces referential integrity to another table's primary key
CHECK	Restricts values to satisfy a specific boolean condition

MUST REMEMBER — Chapter 8

• DDL=structure (CREATE/ALTER/DROP); DML=data (SELECT/INSERT/UPDATE/DELETE); DCL=permissions (GRANT/REVOKE); TCL=transactions (COMMIT/ROLLBACK).
• DROP=removes structure; TRUNCATE=removes all rows, keeps structure, resets identity, usually not rollback-able; DELETE=removes rows (optionally filtered), rollback-able.
• WHERE filters before grouping (no aggregates allowed); HAVING filters after grouping (for aggregate conditions).
• LEFT JOIN keeps all left rows; RIGHT JOIN keeps all right rows; FULL JOIN keeps all from both.
• Every non-aggregated SELECT column must appear in GROUP BY.
• Correlated subquery references the outer query's column, re-evaluated per row; non-correlated runs independently once.

DON'T CONFUSE

• DROP (removes structure) vs TRUNCATE (removes rows, keeps structure) vs DELETE (removes rows, filterable, rollback-able).
• WHERE (row filter, pre-aggregation) vs HAVING (group filter, post-aggregation).
• Correlated subquery (depends on outer row) vs Non-correlated subquery (independent).

JRF CHALLENGE ZONE — Chapter 8

1. Which command removes all rows but KEEPS the table structure, and typically cannot be rolled back? (a) DELETE (b) DROP (c) TRUNCATE (d) ALTER
Answer: (c)

2. Which clause would you use to filter groups based on COUNT(*) > 5? (a) WHERE (b) HAVING (c) GROUP BY alone (d) ORDER BY
Answer: (b)

3. A subquery references a column from its outer query. This subquery is: (a) Non-correlated (b) Correlated (c) Invalid syntax (d) A join, not a subquery
Answer: (b)

Practice Questions — Chapter 8 (10)

1. Classify CREATE, DROP, and ALTER — which SQL category do they belong to?
   Ans: DDL (Data Definition Language)
2. Differentiate DROP and TRUNCATE.
   Ans: DROP removes the entire table structure; TRUNCATE removes all rows but keeps the structure
3. Why can't you use COUNT(*) directly in a WHERE clause?
   Ans: WHERE filters rows before grouping/aggregation happens; aggregate results aren't available yet at that stage
4. Which JOIN returns all rows from the left table plus matching rows from the right?
   Ans: LEFT (OUTER) JOIN
5. What rule governs which columns can appear in the SELECT list with GROUP BY?
   Ans: Every selected column not wrapped in an aggregate function must appear in the GROUP BY clause
6. Differentiate a correlated and a non-correlated subquery.
   Ans: A correlated subquery references the outer query's column and is re-evaluated per row; a non-correlated subquery runs independently, once
7. What does the PRIMARY KEY constraint combine?
   Ans: NOT NULL and UNIQUE
8. Which TCL command permanently saves a transaction's changes?
   Ans: COMMIT
9. What is the logical execution order of SELECT, FROM, WHERE, GROUP BY, HAVING?
   Ans: FROM → WHERE → GROUP BY → HAVING → SELECT
10. Which join returns rows from both tables, with NULLs where there is no match on either side?
    Ans: FULL (OUTER) JOIN

---

Chapter 9 — NoSQL Database

9.1 Why NoSQL?

SimpleNoSQL ("Not Only SQL") databases were designed to handle large-scale, distributed, often unstructured/semi-structured data with high scalability — where rigid relational schemas and strict ACID guarantees can become a bottleneck.

9.2 Types of NoSQL Databases

Type	Idea	Example
Key-Value Store	Simplest — data stored as key-value pairs, like a giant dictionary	Redis, DynamoDB
Document Store	Stores semi-structured "documents" (usually JSON-like), each can have a different structure	MongoDB
Column-Family Store	Data organized by column families rather than rows — efficient for queries over specific columns across huge datasets	Cassandra, HBase
Graph Database	Data as nodes and edges — optimized for highly connected/relationship-heavy data	Neo4j

JRF trapDocument stores allow each "document" to have a DIFFERENT structure/set of fields (schema-flexible) — unlike a relational table where every row must follow the SAME fixed column structure. This "schema flexibility" is the core distinguishing feature between document NoSQL databases and relational tables.

9.3 CAP Theorem

IdeaIn a distributed system, you can only guarantee AT MOST TWO of these three simultaneously: Consistency (every read gets the most recent write), Availability (every request gets a response, even if not the latest data), Partition tolerance (system continues working despite network partitions/communication failures between nodes).

JRF trapSince network partitions ARE a real-world inevitability in distributed systems, Partition tolerance is generally considered NON-NEGOTIABLE — so the real practical choice is between CP (Consistency + Partition tolerance, sacrificing some Availability) and AP (Availability + Partition tolerance, sacrificing strict Consistency). Most NoSQL systems choose AP; traditional RDBMS-style distributed systems often lean CP. This "P is basically mandatory, so it's really a C-vs-A tradeoff" framing is the key JRF insight.

9.4 BASE vs ACID

	ACID (typical RDBMS)	BASE (typical NoSQL)
Stands for	Atomicity, Consistency, Isolation, Durability	Basically Available, Soft state, Eventual consistency
Consistency model	Strong/immediate consistency	Eventual consistency (may be briefly inconsistent, converges over time)
Priority	Correctness/reliability	Availability/scalability

MUST REMEMBER — Chapter 9

• 4 NoSQL types: Key-Value, Document, Column-Family, Graph.
• Document stores allow different structure per document (schema-flexible), unlike rigid relational rows.
• CAP theorem: can guarantee at most 2 of Consistency, Availability, Partition tolerance.
• Partition tolerance is essentially mandatory in distributed systems — the real tradeoff is CP vs AP.
• ACID = strong consistency (RDBMS); BASE = eventual consistency (NoSQL, favors availability).

DON'T CONFUSE

• Key-Value store (simplest, no structure) vs Document store (semi-structured, flexible schema per document).
• ACID (strong consistency) vs BASE (eventual consistency).

JRF CHALLENGE ZONE — Chapter 9

1. Which NoSQL type is best suited for highly connected, relationship-heavy data (e.g., social networks)? (a) Key-Value (b) Document (c) Column-family (d) Graph
Answer: (d)

2. Given that network partitions are practically unavoidable, the real-world CAP tradeoff is usually between: (a) C and A (b) C and P (c) A and P only, ignoring C entirely (d) There is no real tradeoff
Answer: (a)

Practice Questions — Chapter 9 (6)

1. What does NoSQL stand for, informally?
   Ans: "Not Only SQL"
2. Name the four types of NoSQL databases.
   Ans: Key-Value, Document, Column-Family, Graph
3. What key feature distinguishes a document store from a relational table?
   Ans: Each document can have a different structure/fields (schema flexibility), unlike a fixed relational row structure
4. State the CAP theorem.
   Ans: A distributed system can guarantee at most two of Consistency, Availability, and Partition tolerance simultaneously
5. Why is Partition tolerance usually considered non-negotiable?
   Ans: Because network partitions are a practical inevitability in real distributed systems
6. What does BASE stand for, and how does it differ from ACID's consistency model?
   Ans: Basically Available, Soft state, Eventual consistency; it allows temporary inconsistency that converges over time, unlike ACID's strong/immediate consistency

---

Chapter 10 — Data Warehouse, Data Mining

10.1 OLTP vs OLAP

	OLTP	OLAP
Full form	Online Transaction Processing	Online Analytical Processing
Purpose	Day-to-day operational transactions (fast INSERT/UPDATE)	Complex analysis/reporting over historical data
Data	Current, detailed, normalized	Historical, summarized, often denormalized
Query type	Simple, short, frequent	Complex, long-running, less frequent

JRF trapOLTP databases are typically HIGHLY NORMALIZED (to avoid update anomalies during frequent transactions), while Data Warehouses (OLAP) are typically DENORMALIZED (star/snowflake schemas) — because query SPEED for complex analytical reads matters more than update-anomaly avoidance in a warehouse (data is loaded periodically via ETL, not constantly updated).

10.2 Data Warehouse Schemas

Schema	Idea
Star Schema	One central FACT table connected directly to several DENORMALIZED dimension tables (simple, fast queries)
Snowflake Schema	Like star schema, but dimension tables are further NORMALIZED into sub-dimension tables (saves space, but more complex joins)

JRF trap — star vs snowflakeStar schema's dimension tables are DENORMALIZED (flat, may have redundant data) — fewer joins, faster queries. Snowflake schema NORMALIZES those dimension tables further — less redundancy/storage, but MORE joins needed, so typically SLOWER queries. "Which schema has more redundancy vs which has more joins" is the standard JRF distinguishing question.

10.3 ETL Process

IdeaETL = Extract (pull data from source systems), Transform (clean, reformat, aggregate the data to fit warehouse schema), Load (insert the transformed data into the data warehouse) — the standard pipeline for populating a data warehouse.

10.4 Data Mining Techniques

Technique	Idea
Classification	Assigns data into predefined categories/classes (SUPERVISED — uses labeled training data)
Clustering	Groups similar data points together WITHOUT predefined labels (UNSUPERVISED)
Association Rule Mining	Finds relationships/patterns between items (e.g., "market basket analysis" — customers who buy X also buy Y)
Regression	Predicts a CONTINUOUS numeric value based on other variables

JRF trap — classification vs clusteringClassification is SUPERVISED (training data already has known category labels, e.g., "spam" or "not spam" emails used to train the model). Clustering is UNSUPERVISED (no predefined labels — the algorithm discovers natural groupings on its own, e.g., customer segmentation). Mixing up "supervised with labels" (classification) vs "unsupervised without labels" (clustering) is a very common JRF trap.

MUST REMEMBER — Chapter 10

• OLTP = normalized, fast frequent transactions; OLAP = denormalized, complex analytical queries over historical data.
• Star schema = denormalized dimensions (fast, fewer joins); Snowflake = normalized dimensions (less redundancy, more joins, slower).
• ETL = Extract, Transform, Load — the standard data warehouse population pipeline.
• Classification = supervised (labeled data); Clustering = unsupervised (no labels, discovers groupings).
• Association rule mining finds "if X then Y" patterns (market basket analysis).

DON'T CONFUSE

• Star schema (denormalized, fewer joins) vs Snowflake schema (normalized, more joins).
• Classification (supervised) vs Clustering (unsupervised).
• OLTP (operational, normalized) vs OLAP (analytical, denormalized).

JRF CHALLENGE ZONE — Chapter 10

1. Which schema has dimension tables further normalized into sub-tables? (a) Star (b) Snowflake (c) Both equally (d) Neither
Answer: (b)

2. A model is trained on emails already labeled "spam"/"not spam" to classify new emails. This is: (a) Clustering (b) Classification (c) Association rule mining (d) Regression
Answer: (b)

3. Grouping customers into segments WITHOUT any predefined category labels is: (a) Classification (b) Clustering (c) Regression (d) ETL
Answer: (b)

Practice Questions — Chapter 10 (7)

1. Differentiate OLTP and OLAP in terms of purpose.
   Ans: OLTP handles fast day-to-day operational transactions; OLAP handles complex analytical queries over historical data
2. Are OLTP databases typically normalized or denormalized? What about data warehouses?
   Ans: OLTP: normalized; Data warehouses (OLAP): typically denormalized
3. Differentiate a Star schema and a Snowflake schema.
   Ans: Star schema has denormalized dimension tables (fewer joins); Snowflake normalizes dimensions further into sub-tables (more joins, less redundancy)
4. What do the three steps of ETL stand for?
   Ans: Extract, Transform, Load
5. Differentiate Classification and Clustering.
   Ans: Classification is supervised, using labeled training data; Clustering is unsupervised, discovering groupings without labels
6. What does Association Rule Mining typically find?
   Ans: Relationships/patterns between items, such as which items are frequently purchased together
7. What kind of output does Regression predict?
   Ans: A continuous numeric value

---

One-Shot Revision — Unit 5

Key facts across all chapters

• 3-schema: External→Conceptual→Internal. Logical data independence (conceptual↔external, harder); Physical (internal↔conceptual, easier).
• M:N relationship needs a separate junction table; 1:1/1:N can use a foreign key. Generalization=bottom-up; Specialization=top-down.
• Key hierarchy: Super Key ⊇ Candidate Key ⊇ {Primary Key}. σ=filter rows; π=select columns. Union needs union-compatibility.
• FD closure: compute (X)⁺; if it covers all attributes, X is a candidate key. 1NF=atomic; 2NF=no partial dep; 3NF=no transitive dep; BCNF=every determinant is a super key.
• 3NF decomposition: always lossless+dependency-preserving. BCNF: always lossless, MAY sacrifice dependency preservation.
• ACID: Atomicity+Durability→recovery manager; Isolation→concurrency control. Two ops conflict only if: different transactions+same item+≥1 write.
• Conflict serializable = no cycle in precedence graph. Basic 2PL=serializable only; Strict 2PL=+prevents cascading rollback. Timestamp ordering=deadlock-free by design.
• Big Data 5 V's: Volume, Velocity, Variety, Veracity, Value. HDFS fault tolerance = replication (not RAID).
• DROP=structure gone; TRUNCATE=rows gone,structure stays,no rollback; DELETE=rows gone (filterable), rollback-able. WHERE=pre-group; HAVING=post-group (aggregates).
• Correlated subquery=depends on outer row, re-evaluated each row; Non-correlated=runs once independently.
• NoSQL types: Key-Value, Document (schema-flexible), Column-family, Graph. CAP: pick 2 of 3; P is essentially mandatory → real tradeoff is C vs A.
• OLTP=normalized,fast transactions; OLAP=denormalized,analytical. Star=denormalized dims (fast); Snowflake=normalized dims (more joins).
• Classification=supervised (labeled); Clustering=unsupervised (no labels).

Potential future exam areasPotential high-value exam area based on syllabus importance and historical question patterns: functional-dependency closure and candidate-key-finding numericals; identifying the highest normal form (especially 3NF-vs-BCNF edge cases); precedence-graph conflict-serializability numericals; complex SQL queries combining JOIN+GROUP BY+HAVING; and CAP-theorem/BASE-vs-ACID scenario questions.

---

Unit 5 — UGC NET/JRF Mini Mock Test

50 questions across all 10 chapters. NTA/UGC NET-style question patterns — mixed NET/JRF difficulty, numerical, statement-based, matching and scenario-based. Answer key with brief explanations follows each question.

Q1. Changing an index structure without affecting the conceptual schema demonstrates: (a) Logical data independence (b) Physical data independence (c) View independence (d) None
Ans: (b) [Ch1 | NET]

Q2. Which schema level describes how individual users see the data?
Ans: External (view) level [Ch1 | NET]

Q3. A multi-valued attribute is shown in an ER diagram using: (a) Single oval (b) Double oval (c) Dashed oval (d) Rectangle
Ans: (b) [Ch2 | NET]

Q4. Combining "Savings Account" and "Current Account" entities into a general "Account" entity is: (a) Specialization (b) Generalization (c) Aggregation (d) Normalization
Ans: (b) [Ch2 | NET]

Q5. An M:N relationship, when converted to relational tables, requires: (a) A foreign key in either table (b) A separate junction table (c) No extra structure (d) A trigger
Ans: (b) [Ch2 | NET]

Q6. A candidate key that was NOT chosen as primary key is called: (a) Foreign key (b) Alternate key (c) Super key only (d) Composite key
Ans: (b) [Ch3 | NET]

Q7. Which relational algebra operator filters ROWS?
Ans: σ (Select) [Ch3 | NET]

Q8. Two relations can be UNION-ed only if they are: (a) Same size in bytes (b) Union-compatible (same attributes/domains) (c) Both empty (d) Sorted
Ans: (b) [Ch3 | NET]

Q9. R(A,B,C,D) with FDs A→B, B→C, C→D. Find (A)⁺.
Ans: {A,B,C,D} — A is a candidate key. [Ch4 | NET numerical]

Q10. A relation is in 3NF but a non-superkey determinant exists. It is: (a) In BCNF too (b) Not in BCNF (c) Not in 1NF (d) Invalid
Ans: (b) [Ch4 | JRF]

Q11. A non-key attribute depending on part of a composite primary key violates: (a) 1NF (b) 2NF (c) 3NF (d) BCNF
Ans: (b) [Ch4 | NET]

Q12. Which decomposition ALWAYS preserves both lossless join and dependencies? (a) BCNF (b) 3NF (c) 1NF (d) Neither
Ans: (b) [Ch4 | JRF]

Q13. Which ACID property is enforced mainly by the concurrency control subsystem?
Ans: Isolation [Ch5 | NET]

Q14. Two Read operations from different transactions on the same item: (a) Always conflict (b) Never conflict (c) Sometimes conflict (d) Cause deadlock
Ans: (b) [Ch5 | NET]

Q15. A schedule's precedence graph has a cycle. It is: (a) Conflict serializable (b) Not conflict serializable (c) A serial schedule (d) Always consistent
Ans: (b) [Ch5 | JRF]

Q16. Which lock type allows multiple simultaneous holders?
Ans: Shared lock [Ch6 | NET]

Q17. Basic 2PL guarantees: (a) Serializability only (b) Serializability and no cascading rollback (c) Deadlock freedom (d) Nothing
Ans: (a) [Ch6 | JRF]

Q18. Which concurrency protocol is deadlock-free by design (no locks/waiting)? (a) Basic 2PL (b) Strict 2PL (c) Timestamp ordering (d) None
Ans: (c) [Ch6 | NET]

Q19. Which V of Big Data refers to trustworthiness/quality of data?
Ans: Veracity [Ch7 | NET]

Q20. JSON and XML data are best classified as: (a) Structured (b) Semi-structured (c) Unstructured (d) None
Ans: (b) [Ch7 | NET]

Q21. HDFS achieves fault tolerance mainly via: (a) RAID (b) Replication (c) Compression (d) Backups only
Ans: (b) [Ch7 | NET]

Q22. Which command removes all rows but keeps the table structure? (a) DROP (b) DELETE (c) TRUNCATE (d) ALTER
Ans: (c) [Ch8 | NET]

Q23. Which clause filters GROUPS after aggregation? (a) WHERE (b) HAVING (c) ORDER BY (d) FROM
Ans: (b) [Ch8 | NET]

Q24. Which JOIN returns all rows from both tables?
Ans: FULL (OUTER) JOIN [Ch8 | NET]

Q25. A subquery referencing the outer query's column, re-evaluated per row, is called: (a) Non-correlated (b) Correlated (c) Nested join (d) Invalid
Ans: (b) [Ch8 | JRF]

Q26. Which SQL category does GRANT/REVOKE belong to? (a) DDL (b) DML (c) DCL (d) TCL
Ans: (c) [Ch8 | NET]

Q27. Which NoSQL type is best for highly connected, relationship-heavy data? (a) Key-Value (b) Document (c) Column-family (d) Graph
Ans: (d) [Ch9 | NET]

Q28. CAP theorem says a distributed system can guarantee at most how many of the three properties simultaneously?
Ans: Two [Ch9 | NET]

Q29. BASE stands for: (a) Basically Available, Soft state, Eventual consistency (b) Basic Atomicity, Strong Effect (c) Backup Available System Effect (d) None
Ans: (a) [Ch9 | NET]

Q30. Which schema has more normalized dimension tables (more joins)? (a) Star (b) Snowflake (c) Both equal (d) Neither
Ans: (b) [Ch10 | NET]

Q31. OLTP databases are typically: (a) Highly denormalized (b) Highly normalized (c) Unstructured (d) Schema-less
Ans: (b) [Ch10 | NET]

Q32. A model trained on labeled spam/not-spam emails to classify new emails uses: (a) Clustering (b) Classification (c) Association rules (d) ETL
Ans: (b) [Ch10 | NET]

Q33. What do the three ETL steps stand for?
Ans: Extract, Transform, Load [Ch10 | NET]

Q34. Which is TRUE about a weak entity? (a) Has its own unique key (b) Needs owner entity's key plus a partial key (c) Cannot participate in relationships (d) Is always the owner entity
Ans: (b) [Ch2 | JRF]

Q35. Which relational-algebra operator picks specific columns and removes duplicates?
Ans: π (Project) [Ch3 | NET]

Q36. R(A,B,C) with AB as primary key, FD: C→B (C non-prime). This relation is: (a) In BCNF (b) In 3NF but not BCNF (c) Not even in 1NF (d) In 2NF only
Ans: (b) [Ch4 | JRF]

Q37. Which transaction state comes immediately after "Active" on the success path?
Ans: Partially Committed [Ch5 | NET]

Q38. Strict 2PL specifically prevents: (a) Deadlock (b) Cascading rollback (c) Starvation (d) Nothing extra over basic 2PL
Ans: (b) [Ch6 | JRF]

Q39. Which V of Big Data refers to speed of data generation/processing?
Ans: Velocity [Ch7 | NET]

Q40. Which SQL clause comes logically FIRST in execution order? (a) SELECT (b) WHERE (c) FROM (d) HAVING
Ans: (c) [Ch8 | NET]

Q41. Document-store NoSQL databases allow: (a) Only fixed schema per collection (b) Different structure per document (c) No nested data (d) Only key-value pairs
Ans: (b) [Ch9 | NET]

Q42. Grouping customers into segments with no predefined labels is: (a) Classification (b) Clustering (c) Regression (d) ETL
Ans: (b) [Ch10 | NET]

Q43. Which key type may contain redundant/unnecessary attributes? (a) Candidate key (b) Super key (c) Primary key (d) Foreign key
Ans: (b) [Ch3 | NET]

Q44. A transitive dependency violates which normal form?
Ans: 3NF [Ch4 | NET]

Q45. Which subsystem primarily enforces Atomicity and Durability?
Ans: The recovery manager (via logs) [Ch5 | NET]

Q46. Which is TRUE about Star schema vs Snowflake schema? (a) Star has more joins (b) Snowflake has more joins due to normalized dimensions (c) They are identical (d) Snowflake has no fact table
Ans: (b) [Ch10 | NET]

Q47. Which lock is required for both reading AND writing, with only one holder allowed?
Ans: Exclusive lock [Ch6 | NET]

Q48. Referential integrity requires a foreign key to be: (a) Always NOT NULL (b) NULL or matching an existing PK value (c) Always unique (d) Always a string
Ans: (b) [Ch3 | NET]

Q49. Which is a supervised data mining technique?
Ans: Classification [Ch10 | NET]

Q50. Which of these is FALSE? (a) DELETE can be rolled back (b) TRUNCATE resets identity/auto-increment (c) DROP keeps the table structure intact (d) DELETE can use a WHERE clause
Ans: (c) — DROP removes the entire table structure, it does not keep it intact. [Ch8 | JRF]

— End of Mock Test — Cross-check your score, revisit the "Don't Confuse" and "JRF Challenge Zone" boxes for any topic you missed, then re-attempt after 48 hours. —

Share: