UGC NET Computer Science Database Management Systems Syllabus

What is a database view, and what is a materialized view?

A view is a virtual table defined by a stored query, computed on demand. A materialized view physically stores the query result and must be refreshed to stay current.

Differentiate a stored procedure from a user-defined function in SQL.

A function must return a value and can be used inside SQL expressions/queries, typically without side effects; a stored procedure performs actions, may return zero or many results via output params, and is invoked with CALL/EXECUTE.

What is a database trigger and what are its main timing/event options?

A trigger is procedural code automatically executed in response to events (INSERT/UPDATE/DELETE), firing BEFORE or AFTER the event, at ROW or STATEMENT level, optionally with a WHEN condition.

What is SQL injection and a primary defense against it?

SQL injection is an attack where malicious SQL is inserted via unsanitized user input to alter a query. The primary defense is parameterized queries/prepared statements (plus input validation and least-privilege accounts).

Define a functional dependency X → Y.

X → Y holds if, for any two tuples that agree on the attribute set X, they must also agree on Y; i.e., X functionally determines Y (Y is dependent on X).

State Armstrong's three inference axioms for functional dependencies.

Reflexivity (if Y ⊆ X then X → Y), Augmentation (if X → Y then XZ → YZ), and Transitivity (if X → Y and Y → Z then X → Z).

Give the conditions for 1NF, 2NF, and 3NF.

1NF: all attribute values atomic (no repeating groups). 2NF: 1NF + no partial dependency of a non-prime attribute on part of a candidate key. 3NF: 2NF + no transitive dependency of a non-prime attribute on a candidate key.

State the condition for Boyce-Codd Normal Form (BCNF).

A relation is in BCNF if, for every nontrivial functional dependency X → Y, X is a superkey. BCNF is stricter than 3NF and removes anomalies 3NF may permit.

Name common query optimization strategies used by a DBMS.

Heuristic (rule-based) optimization — push selections/projections early; and cost-based optimization — estimate cost using statistics over alternative plans (access paths, join methods/orders) and choose the cheapest.

What are the main algorithms for implementing a join in query processing?

Nested-loop join, block nested-loop join, index nested-loop join, sort-merge join, and hash join.

State the ACID properties of a transaction.

Atomicity (all-or-nothing), Consistency (preserves integrity constraints), Isolation (concurrent transactions don't interfere), and Durability (committed changes survive failures).

What are the states in a transaction's life cycle?

Active, Partially Committed, Committed, Failed, and Aborted (terminated).

What is a schedule, and what makes it conflict-serializable?

A schedule is an interleaved order of operations of concurrent transactions. It is conflict-serializable if it can be transformed into a serial schedule by swapping non-conflicting adjacent operations — testable via an acyclic precedence (conflict) graph.

What is the two-phase locking (2PL) protocol?

A concurrency-control protocol where each transaction acquires all locks in a growing phase and releases locks only in a shrinking phase (no lock acquired after the first release), which guarantees conflict serializability.

What problems do concurrency-control techniques prevent?

The lost update problem, the dirty read (temporary/uncommitted dependency) problem, the unrepeatable read problem, and the incorrect summary (phantom) problem.

Compare deadlock prevention schemes wait-die and wound-wait.

Both use timestamps. Wait-die: an older transaction waits for a younger one, a younger one requesting an older's lock dies (non-preemptive). Wound-wait: an older transaction preempts (wounds) a younger one, a younger one waits (preemptive).

Differentiate deferred and immediate database update recovery techniques.

Deferred update: changes are written to the database only after commit (no UNDO needed, only REDO). Immediate update: changes may be written before commit, requiring both UNDO and REDO using the log.

What is the write-ahead logging (WAL) protocol?

Before any data page modification is written to disk, the corresponding log (UNDO) record must first be forced to stable storage, and the commit log record must be written before commit completes — ensuring atomicity/durability.

What is a checkpoint in database recovery?

A point at which the DBMS flushes buffers/log to disk and records a checkpoint marker, limiting the amount of log that must be scanned during recovery to transactions active at or after the checkpoint.

How do object-oriented databases differ from object-relational databases?

OODBs store objects natively with identity, inheritance, and methods using an object data model. ORDBs extend the relational model with object features (user-defined types, inheritance, references) while retaining SQL and tables.

What is the difference between authentication and authorization in database security?

Authentication verifies a user's identity (who you are); authorization (via GRANT/REVOKE privileges) determines what operations/data that identity is permitted to access.

Compare Discretionary (DAC) and Mandatory (MAC) access control in databases.

DAC grants/revokes privileges at the owner's discretion (GRANT/REVOKE). MAC enforces system-wide security classes/clearances (e.g., Bell-LaPadula) preventing flow from higher to lower classifications.

What are valid time and transaction time in a temporal database?

Valid time is the period during which a fact is true in the real world; transaction time is the period during which the fact was stored as current in the database. A bitemporal database records both.

What is a deductive database and which language is associated with it?

A deductive database can derive new facts (rules) from stored facts using logic. It typically uses Datalog (a Prolog-like declarative rule language) with facts and rules to infer intensional data.

What role does XML play in internet databases, and how is XML data queried?

XML provides a self-describing, hierarchical, platform-independent format for exchanging semi-structured data over the web; it is queried/navigated using XPath and XQuery (with XSLT for transformation).

What are the key characteristics (the Vs) of Big Data?

Volume (scale), Velocity (speed of generation/processing), Variety (structured, semi-structured, unstructured), Veracity (uncertainty/quality), and Value.

What are the two phases of the MapReduce programming model?

Map: processes input key-value pairs to produce intermediate key-value pairs. Reduce: aggregates all intermediate values grouped by key to produce the final output (with a shuffle/sort between the phases).

In HDFS, what are the roles of the NameNode and DataNode?

The NameNode is the master that manages the filesystem namespace and metadata (block locations); DataNodes are workers that store the actual data blocks and serve read/write requests. Blocks are replicated (default 3x) for fault tolerance.

Differentiate OLTP and OLAP systems.

OLTP: transaction-oriented, many short read/write operations, normalized schema, current data, day-to-day operations. OLAP: analysis-oriented, complex read-mostly queries, denormalized/star schema, historical aggregated data for decision support.

Differentiate star schema and snowflake schema in data warehouse modeling.

Star schema: a central fact table linked to denormalized dimension tables (simple, fast queries). Snowflake schema: dimension tables are normalized into sub-dimensions (less redundancy, more joins).

In association rule mining, define support, confidence, and lift.

Support(X) = fraction of transactions containing X. Confidence(X→Y) = support(X∪Y)/support(X). Lift(X→Y) = confidence/support(Y); lift > 1 indicates positive correlation. The Apriori algorithm uses these to find frequent itemsets.

Contrast supervised classification, regression, and unsupervised clustering in data mining.

Classification predicts a discrete categorical label from labeled data; regression predicts a continuous numeric value from labeled data; clustering groups unlabeled data into natural groups by similarity (no predefined labels).

How do the k-Nearest Neighbour and Support Vector Machine classifiers work?

kNN is a lazy, instance-based method classifying a point by the majority class of its k nearest neighbours. SVM finds the optimal separating hyperplane that maximizes the margin between classes, using kernels for non-linear boundaries.