DSSSB TGT / PGT CS
🔥 PYQ REPEATED FREQUENCY (2014–2024)
DBMS HIGH-WEIGHTAGE NOTES & TOPIC-WISE PYQ QUIZZES
Comprehensive exam revision: 3-Schema Architecture, Keys, 1NF–BCNF Normalization, SQL Execution Order & Concurrency Control.
🎯 EXAM WEIGHTAGE: 14–18 MARKS
IMPORTANT NOTICE
DSSSB Exam & AI Verification Advisory
THIS IS AN AI GENERATED QUIZ AND STUDY GUIDE BASED ON THE REAL PREVIOUS YEAR QUESTION PAPER. PLEASE VERIFY THESE ANSWERS WITH OFFICIAL DSSSB ANSWER KEYS BEFORE YOUR FINAL EXAM.
DSSSB TGT/PGT Computer Science
Exam Weightage: 14–18 Marks
DBMS Master Study Notes & Embedded Topic Quizzes
Comprehensive exam syllabus coverage with comparison tables, shortcut formulas, and interactive PYQs embedded after every topic.
Module 1: Introduction to DBMS & 3-Schema Architecture
Weightage: 2-3 Marks
1.1 DBMS vs. File Processing System
| Parameters | File Processing System | Database Management System (DBMS) |
|---|---|---|
| Data Redundancy | High; same data repeated across multiple files. | Controlled & minimized via normalization. |
| Data Independence | Absent; changing file format breaks program code. | High; provides Logical & Physical independence. |
| Concurrency & ACID | No transaction support; prone to race conditions. | Built-in locking, timestamps & ACID recovery. |
| Data Integrity | Difficult to enforce; hardcoded inside applications. | Enforced centrally via constraints (PK, FK, CHECK). |
1.2 ANSI/SPARC Three-Schema Architecture
Decouples user applications from physical storage mechanisms to achieve data independence:
External View Level (User Views & Virtual Windows)
↑↓ [Logical Data Independence]
Conceptual / Logical Level (Entities, Attributes & Relationships)
↑↓ [Physical Data Independence]
Internal / Physical Level (Storage Blocks, Indexes & B+ Trees on Disk)
DSSSB High-Yield Key Points:
- Physical Data Independence: Modifying internal physical structures (e.g., adding an index or moving storage blocks) without altering the conceptual schema. (Repeated 4 times in DSSSB).
- Logical Data Independence: Modifying the conceptual schema (e.g., adding an attribute/entity) without altering external user views. (Harder to achieve).
MODULE 1 PRACTICE QUIZ
Repeated 4 Times
Loading question...
Question 1 of 3
Module 2: Entity-Relationship (ER) Modeling & Relational Mapping
Weightage: 3-4 Marks
2.1 Standard Chen ER Notations
| ER Geometric Symbol | Database Element | Concrete Real-World Example |
|---|---|---|
| Single Rectangle | Strong Entity Set | STUDENT (has primary key Roll_No) |
| Double Rectangle | Weak Entity Set | DEPENDENT (depends on EMPLOYEE) |
| Double Diamond | Identifying Relationship | Links weak entity to owner entity |
| Single Ellipse | Simple / Single-valued Attribute | Gender, Salary |
| Double Ellipse | Multivalued Attribute | Phone_Number, Skills (person can have multiple) |
| Dashed Ellipse | Derived Attribute | Age (computed dynamically from Date_of_Birth) |
| Underlined Text in Ellipse | Primary / Candidate Key Attribute | Emp_ID, Admission_No |
| Dashed Underline in Ellipse | Partial Key (Discriminator) | Distinguishes weak entities sharing the same owner |
2.2 ER to Relational Table Conversion Rules
Minimal Table Rules for DSSSB:
- Many-to-Many (M:N): Requires a minimum of 3 tables (Table 1 for E1, Table 2 for E2, Table 3 for the relationship containing PKs of both).
- One-to-Many (1:N): Requires 2 tables. The Primary Key of the '1' side is placed as a Foreign Key on the 'N' side.
- One-to-One (1:1): Requires 2 tables (or 1 combined table if total participation on both sides). Place the FK on the side with total participation.
MODULE 2 PRACTICE QUIZ
Repeated 4 Times
Loading question...
Question 1 of 3
Module 3: Relational Model, Keys Hierarchy & Relational Algebra
Weightage: 4-5 Marks
3.1 Relational Keys Hierarchy
Super Key Set ⊇
Candidate Key Set (Minimal Super Keys) ⊇
Primary Key (1 Selected) +
Alternate Keys (Remaining CKs)
| Key Type | Formal Definition | Can it be NULL? |
|---|---|---|
| Super Key | Any set of attributes that uniquely distinguishes an entity. | Yes (non-identifying components) |
| Candidate Key | A minimal super key (no proper subset is a super key). | No attribute in PK can be NULL |
| Primary Key | Chosen candidate key to uniquely identify tuples. | NEVER NULL (Entity Integrity) |
| Foreign Key | Attribute referencing the candidate/primary key of another table. | YES (Unless restricted by NOT NULL) |
3.2 Relational Algebra Primitive Operators
- Selection (σ): Unary operator, filters tuples (rows) matching a condition.
- Projection (π): Unary operator, filters columns and removes duplicate rows.
- Union (∪): Binary operator, relations must be union-compatible (same degree and domain).
- Set Difference (-): Binary operator, returns tuples in R but not in S.
- Cartesian Product (×): If R has degree m and S has degree n, degree of R × S = m + n; cardinality = |R| × |S|.
- Rename (ρ): Renames relations or attributes.
* Note: Natural Join (⋈) and Division (÷) are derived operations, not primitive.
MODULE 3 PRACTICE QUIZ
Repeated 5 Times
Loading question...
Question 1 of 3
Module 4: Functional Dependencies & Normalization (1NF → 5NF)
Weightage: 4-5 Marks
4.1 Armstrong's Axioms & Closures
- Reflexivity: If Y ⊆ X, then X → Y holds.
- Augmentation: If X → Y, then XZ → YZ holds for any Z.
- Transitivity: If X → Y and Y → Z, then X → Z holds.
- Attribute Closure (X+): The set of all attributes functionally determined by X under F. If X+ contains all attributes of R, X is a Super Key.
4.2 Normalization Summary Matrix
| Normal Form | Condition to Satisfy | What It Strictly Eliminates |
|---|---|---|
| 1NF | All attribute values must be atomic (indivisible). | Repeating groups & multivalued columns. |
| 2NF | 1NF + No Non-prime attribute is partially dependent on any Candidate Key. | Partial Functional Dependencies. |
| 3NF | 2NF + For every non-trivial X → Y: Either X is a Super Key OR Y is a Prime Attribute. | Transitive Functional Dependencies. |
| BCNF | For every non-trivial functional dependency X → Y: X must strictly be a Super Key. | All functional dependency anomalies. |
| 4NF | BCNF + contains no non-trivial Multivalued Dependencies (MVDs). | Multivalued Dependencies (A →→ B). |
| 5NF (PJNF) | 4NF + cannot be losslessly decomposed without join dependencies. | Join Dependencies. |
Lossless Decomposition Test: Decomposition of R into R1 and R2 is lossless if and only if:
(R1 ∩ R2) → R1 OR (R1 ∩ R2) → R2 (The common attributes must form a superkey of at least one relation).
MODULE 4 PRACTICE QUIZ
Repeated 5 Times
Loading question...
Question 1 of 3
Module 5: Structured Query Language (SQL) & Execution Mechanics
Weightage: 3-4 Marks
5.1 SQL Command Subsets
| Subset | Commands | Transactional Behavior |
|---|---|---|
| DDL (Data Definition) | CREATE, ALTER, DROP, TRUNCATE, RENAME |
Auto-committed (Implicit Commit; cannot rollback in most RDBMS) |
| DML (Data Manipulation) | INSERT, UPDATE, DELETE |
Can be rolled back within an active transaction block |
| DCL (Data Control) | GRANT, REVOKE |
Manages security privileges and permissions |
| TCL (Transaction Control) | COMMIT, ROLLBACK, SAVEPOINT |
Controls transaction persistence and save boundaries |
5.2 TRUNCATE vs. DELETE vs. DROP
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Type | DML | DDL | DDL |
| WHERE Clause | Supported | Not Supported (Removes all rows) | Not Supported |
| Performance | Slower (Logs each row deletion) | Faster (Deallocates entire data pages) | Instant (Removes schema & data) |
| Structure | Retained | Retained | Completely Deleted |
True Logical SQL Execution Order:
FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
MODULE 5 PRACTICE QUIZ
Repeated 5 Times
Loading question...
Question 1 of 3
Module 6: Transaction Management & Concurrency Control
Weightage: 4-5 Marks
6.1 ACID Properties Breakdown
| Property | Meaning | DBMS Subsystem Responsible |
|---|---|---|
| Atomicity | All-or-Nothing execution | Recovery Manager (UNDO Logging) |
| Consistency | Preserves database invariants & constraints | Application Programmer & DBMS Integrity Subsystem |
| Isolation | Concurrent transactions appear serial | Concurrency Control Manager (Locking / 2PL) |
| Durability | Committed updates persist permanently | Recovery Manager (REDO Logging / WAL) |
6.2 Serializability & Two-Phase Locking (2PL)
- Conflict Operations: Belong to different transactions, access the exact same data item, and at least one is a WRITE operation.
- Conflict Serializability Test: Build a precedence graph. A schedule is conflict serializable if and only if the precedence graph is Acyclic (contains NO cycles).
- Basic 2PL: Growing phase (locks acquired, none released) → Shrinking phase (locks released, none acquired). Ensures serializability, but allows deadlocks and cascading rollbacks.
- Strict 2PL: Holds all Exclusive (X) locks until COMMIT or ROLLBACK. Guarantees avoidance of cascading rollbacks.
- Write-Ahead Logging (WAL): Log records must be written to non-volatile disk before the corresponding dirty database block is written to disk.
MODULE 6 PRACTICE QUIZ
Repeated 5 Times
Loading question...
Question 1 of 3
.png)