Placement Preparation – Database Fundamentals (125

MCQs)

ER Diagrams, Keys & Constraints (25 MCQs)

Q1. A superkey is:
a) Any set of attributes that uniquely identifies a tuple
b) A minimal unique set
c) A non-unique set
d) Only the primary key
■ Answer: (a)

Q2. A candidate key is:

a) Any superkey
b) A minimal superkey
c) A foreign key
d) A composite attribute
■ Answer: (b)

Q3. An alternate key is:

a) A surrogate key
b) A non-chosen candidate key
c) A composite superkey
d) A partial key
■ Answer: (b)

Q4. Which statement about primary key is TRUE?

a) Can contain NULL
b) Can have duplicates
c) Uniquely identifies each row
d) Must be composite
■ Answer: (c)

Q5. A foreign key:

a) Uniquely identifies tuples within the same table
b) References a primary/unique key in another table
c) Is always composite
d) Must be nullable
■ Answer: (b)

Q6. ON DELETE CASCADE means:

a) Reject deletion if referenced
b) Set referencing to default
c) Delete referencing rows automatically
d) Ignore referential constraints
■ Answer: (c)

Q7. In ER modeling, a weak entity:

a) Has its own key
b) Is identified by a partial key and owner key
c) Has multivalued attributes only
d) Cannot have relationships
■ Answer: (b)

Q8. Total participation of an entity in a relationship means:

a) At least one entity may not participate
b) Every entity must participate
c) Only one entity participates
d) Optional participation
■ Answer: (b)
Q9. Cardinality 1:N from A to B means:
a) Each A relates to at most one B; each B to many A
b) Each A to many B; each B to at most one A
c) Each A to exactly one B
d) Many-to-many
■ Answer: (b)

Q10. Derived attribute example:

a) Age from DOB
b) Name
c) RollNo
d) Email
■ Answer: (a)

Q11. Composite attribute example:

a) PIN code
b) Full Name split into First/Middle/Last
c) Age
d) Salary
■ Answer: (b)

Q12. Which is NOT an integrity constraint?

a) NOT NULL
b) UNIQUE
c) CHECK
d) COMPUTED
■ Answer: (d)

Q13. Domain constraint restricts:

a) Number of rows
b) Valid values an attribute can take
c) Number of tables
d) Indexes
■ Answer: (b)

Q14. A relationship with an attribute is best represented as:

a) Binary relationship only
b) Ternary relationship
c) Associative (bridge) entity
d) Weak entity
■ Answer: (c)

Q15. Transforming an M:N relationship into relational schema requires:

a) Two tables only
b) One table only
c) Three tables including a junction table
d) Denormalization
■ Answer: (c)

Q16. Self-referencing foreign key is used to:

a) Store circular dependencies
b) Represent hierarchical (parent-child) within same table
c) Avoid joins
d) Speed up queries
■ Answer: (b)

Q17. CHECK constraint is used to:

a) Restrict allowed values by a boolean condition
b) Create indexes
c) Define foreign keys
d) Drop tables
■ Answer: (a)

Q18. A surrogate key is:

a) Natural attribute used as key
b) System-generated key (e.g., identity/UUID)
c) Always composite
d) Same as alternate key
■ Answer: (b)

Q19. A minimal key property implies:

a) Removing any attribute still keeps it unique
b) Removing any attribute destroys uniqueness
c) It must be composite
d) It must be natural
■ Answer: (b)

Q20. Participation constraint (min-max) specifies:

a) Attribute domain
b) Key uniqueness
c) Optionality and cardinality bounds
d) Transaction isolation
■ Answer: (c)

Q21. In ERD, a multivalued attribute is drawn as:

a) Double oval
b) Dashed oval
c) Rectangle
d) Diamond
■ Answer: (a)

Q22. Which combination enforces referential integrity?

a) PRIMARY KEY only
b) FOREIGN KEY referencing UNIQUE/PRIMARY KEY
c) UNIQUE only
d) CHECK only
■ Answer: (b)

Q23. ON UPDATE SET NULL requires:

a) Referenced column allows NULLs
b) Primary key can be NULL
c) Disabling constraints
d) A trigger
■ Answer: (a)

Q24. An identifying relationship connects:

a) Two strong entities
b) Weak entity to owner entity
c) Two weak entities
d) Entity to attribute
■ Answer: (b)

Q25. A ternary relationship between A, B, C becomes:

a) A single table with three FKs often plus relationship attributes
b) Three separate binary relationships always equivalent
c) Two tables only
d) No need for keys
■ Answer: (a)

SQL Basics: SELECT, JOINs, Aggregates (25 MCQs)

Q1. Which clause filters rows before grouping?
a) HAVING
b) WHERE
c) ORDER BY
d) GROUP BY
■ Answer: (b)

Q2. HAVING is applied:

a) Before WHERE
b) After GROUP BY on groups
c) After ORDER BY
d) Before FROM
■ Answer: (b)

Q3. COUNT(column) in SQL:

a) Counts NULLs also
b) Ignores NULLs
c) Counts only distinct by default
d) Same as COUNT(*)
■ Answer: (b)

Q4. Which JOIN returns only matching rows?

a) LEFT OUTER
b) RIGHT OUTER
c) FULL OUTER
d) INNER
■ Answer: (d)

Q5. LEFT OUTER JOIN returns:

a) Only matches
b) Left table all rows + matches/NULLs
c) Right table all rows + matches/NULLs
d) Cartesian product
■ Answer: (b)

Q6. CROSS JOIN produces:

a) Union
b) Intersection
c) Cartesian product
d) Difference
■ Answer: (c)

Q7. Which returns distinct values?

a) GROUP BY only
b) DISTINCT
c) ORDER BY
d) HAVING
■ Answer: (b)

Q8. BETWEEN a AND b is:

a) Exclusive
b) Inclusive of both ends
c) Inclusive of left only
d) Inclusive of right only
■ Answer: (b)

Q9. LIKE pattern '_' matches:

a) Any length
b) Exactly one character
c) Digits only
d) No characters
■ Answer: (b)
Q10. NULL comparisons in SQL:
a) Use '=' and '<>'
b) Use IS NULL/IS NOT NULL
c) NULL = NULL is TRUE
d) NULL behaves like empty string
■ Answer: (b)

Q11. COALESCE(expr, val) returns:

a) expr even if NULL
b) val when expr is NULL
c) sum of expr and val
d) counts non-NULLs
■ Answer: (b)

Q12. Which is TRUE about ORDER BY?

a) Default is DESC
b) Default is ASC
c) Cannot sort by multiple columns
d) Requires GROUP BY
■ Answer: (b)

Q13. Which removes duplicates while combining results?

a) UNION ALL
b) UNION
c) INTERSECT ALL
d) CROSS JOIN
■ Answer: (b)

Q14. A correlated subquery:

a) Does not reference outer query
b) References outer query and runs per row
c) Is same as join
d) Always faster
■ Answer: (b)

Q15. EXISTS generally checks:

a) If subquery returns any row
b) Count of rows
c) Distinct values
d) Full outer join condition
■ Answer: (a)

Q16. AVG(column) in SQL:

a) Includes NULLs as zeros
b) Ignores NULLs
c) Errors on NULLs
d) Counts only distinct
■ Answer: (b)

Q17. Non-joined filters should be placed in OUTER JOIN:

a) ON clause to preserve null-extended rows
b) WHERE clause always
c) SELECT clause
d) GROUP BY clause
■ Answer: (a)

Q18. To get top N rows in a portable way:

a) LIMIT only (all DBs)
b) FETCH FIRST N ROWS ONLY / LIMIT depending on DB
c) TOP N works everywhere
d) ROWNUM works everywhere
■ Answer: (b)
Q19. JOIN condition on multiple columns uses:
a) ON [Link]=[Link] AND [Link]=[Link]
b) ON [Link]=[Link] OR [Link]=[Link]
c) WHERE [Link]=[Link] only
d) USING always
■ Answer: (a)

Q20. IN vs EXISTS:
a) Behave identically with NULLs
b) EXISTS often preferred for correlated checks
c) IN always faster
d) EXISTS returns rows directly
■ Answer: (b)

Q21. GROUP BY requires:

a) All selected non-aggregates appear in GROUP BY
b) No aggregates allowed
c) ORDER BY columns in GROUP BY
d) HAVING before GROUP BY
■ Answer: (a)

Q22. To count rows per department and filter depts with count>5:
a) WHERE COUNT(*)>5
b) HAVING COUNT(*)>5
c) ORDER BY COUNT(*)>5
d) GROUP BY COUNT(*)>5
■ Answer: (b)

Q23. To avoid NULLs in concatenation across DBs, use:

a) COALESCE
b) NVL only everywhere
c) ISNULL everywhere
d) IFNULL everywhere
■ Answer: (a)

Q24. A NATURAL JOIN:

a) Joins on all columns with same names automatically
b) Requires explicit ON
c) Is always safe
d) Works only on PK-FK
■ Answer: (a)

Q25. To get rows where a value is NOT NULL:

a) WHERE col <> NULL
b) WHERE col IS NOT NULL
c) WHERE col != ''
d) WHERE NOT NULL(col)
■ Answer: (b)

Normalization (1NF, 2NF, 3NF, BCNF) (25 MCQs)

Q1. 1NF requires:
a) No NULLs
b) Atomic attribute values; no repeating groups
c) BCNF compliance
d) Only one candidate key
■ Answer: (b)

Q2. A partial dependency occurs when:

a) Non-key depends on entire composite key
b) Non-key depends on part of composite key
c) Key depends on non-key
d) Attributes depend transitively
■ Answer: (b)

Q3. 2NF removes:

a) Transitive dependencies
b) Partial dependencies
c) Multivalued dependencies
d) All redundancies
■ Answer: (b)

Q4. 3NF removes:

a) Only partial deps
b) Transitive dependencies (non-key → non-key)
c) All FDs
d) MVDs
■ Answer: (b)

Q5. BCNF states:

a) Every determinant is a candidate key
b) Every FD has single attribute RHS
c) No transitive dependencies
d) At most one key
■ Answer: (a)

Q6. Update anomalies are reduced by:

a) Denormalization
b) Normalization
c) Indexing only
d) Hashing
■ Answer: (b)

Q7. A lossless-join decomposition ensures:

a) No spurious tuples when joining back
b) All FDs preserved always
c) Minimal keys
d) No NULLs
■ Answer: (a)

Q8. Dependency preservation means:

a) All original FDs can be enforced without a join
b) All keys preserved
c) All tuples preserved
d) No redundancy
■ Answer: (a)

Q9. Prime attribute is:

a) Part of some candidate key
b) Primary key only
c) Foreign key
d) Derived attribute
■ Answer: (a)

Q10. Which normal form allows non-key determinant if dependent attribute is prime?
a) 1NF
b) 2NF
c) 3NF
d) BCNF
■ Answer: (c)

Q11. Boyce–Codd is stricter than 3NF because:

a) Disallows any non-key determinant
b) Allows more redundancy
c) Requires single key
d) Disallows NULLs
■ Answer: (a)

Q12. If R(A,B,C) with FDs A→B, B→A, A→C, then keys are:
a) A only
b) B only
c) A and B
d) C
■ Answer: (c)

Q13. Transitive dependency example:

a) Key → Non-key
b) Part-key → Non-key
c) Non-key → Non-key via another non-key
d) Key → Key
■ Answer: (c)

Q14. Repeating groups typically violate:

a) 1NF
b) 2NF
c) 3NF
d) BCNF
■ Answer: (a)

Q15. MVDs are addressed by:

a) 2NF
b) 3NF
c) BCNF
d) 4NF
■ Answer: (d)

Q16. A minimal cover of FDs is:

a) Any FD set
b) FDs with single-attribute RHS, minimal and non-redundant
c) Only keys
d) Only transitive FDs
■ Answer: (b)

Q17. Armstrong’s axioms are used to:

a) Compute minimal keys
b) Derive all implied FDs
c) Prove 4NF
d) Design indexes
■ Answer: (b)

Q18. If schema in 2NF still suffers update anomaly, likely due to:
a) Partial dep
b) Transitive dep
c) No indexes
d) No PK
■ Answer: (b)

Q19. Denormalization is often used to:

a) Improve write performance always
b) Improve read performance at cost of redundancy
c) Achieve BCNF
d) Remove NULLs
■ Answer: (b)
Q20. A table with attributes (OrderID, ProductID, Quantity, ProductName) violates 3NF because:
a) OrderID is not key
b) ProductName depends on ProductID, not on key (transitive)
c) Quantity not numeric
d) Too many columns
■ Answer: (b)

Q21. Lossless join test using common attributes requires:

a) Common attributes form a key in at least one relation
b) Common attributes are disjoint
c) No FDs
d) Index on common attributes
■ Answer: (a)

Q22. Which is TRUE?

a) BCNF always preserves dependencies
b) 3NF may preserve dependencies when BCNF may not
c) 1NF implies BCNF
d) 2NF implies BCNF
■ Answer: (b)

Q23. A schema in BCNF can still have:

a) Update anomalies due to MVDs
b) Partial dependencies
c) Transitive dependencies
d) Spurious tuples
■ Answer: (a)

Q24. Normalization primarily targets:

a) Eliminating redundancy and anomalies
b) Improving CPU usage
c) Improving network bandwidth
d) Adding indexes
■ Answer: (a)

Q25. A table with attributes (StudentID, CourseID, Instructor) where Instructor depends on CourseID indicates:
a) 1NF violation
b) 2NF violation if (StudentID,CourseID) is key
c) BCNF violation only
d) No issue
■ Answer: (b)

Transactions & ACID (25 MCQs)

Q1. ACID stands for:
a) Atomicity, Consistency, Isolation, Durability
b) Accuracy, Consistency, Integrity, Durability
c) Atomicity, Concurrency, Isolation, Data
d) Availability, Concurrency, Integrity, Durability
■ Answer: (a)

Q2. Atomicity ensures:

a) All or none of a transaction is applied
b) No concurrency issues
c) Data always normalized
d) Faster reads
■ Answer: (a)

Q3. Isolation ensures:

a) Only one user at a time
b) Concurrent transactions behave as if serialized
c) No disk failures
d) No NULLs
■ Answer: (b)

Q4. Durability ensures:

a) Data is cached
b) Committed data survives crashes
c) No deadlocks
d) Faster writes
■ Answer: (b)

Q5. Read Uncommitted allows:

a) Dirty reads
b) Non-repeatable reads only
c) Phantom reads only
d) No anomalies
■ Answer: (a)

Q6. Serializable level prevents:

a) Only dirty reads
b) Dirty and non-repeatable reads, and phantoms (strict serial order)
c) Only phantoms
d) Only lost updates
■ Answer: (b)

Q7. Two-Phase Locking (2PL) guarantees:

a) Deadlock freedom
b) Conflict serializability
c) No locking overhead
d) Read uncommitted
■ Answer: (b)

Q8. A shared lock (S) allows:

a) Other reads and writes
b) Other reads, blocks writes
c) Blocks reads, allows writes
d) Blocks all
■ Answer: (b)

Q9. An exclusive lock (X) allows:

a) Other reads only
b) Other writes only
c) No other locks on that item
d) Shared with S locks
■ Answer: (c)

Q10. Write-Ahead Logging (WAL) requires:

a) Write data before log
b) Write log record before data page
c) Disable fsync
d) Drop indexes first
■ Answer: (b)

Q11. A dirty read is:

a) Reading committed data
b) Reading uncommitted data from another transaction
c) Reading old snapshot
d) Reading with lock
■ Answer: (b)

Q12. Non-repeatable read occurs when:

a) Same row read twice sees different values due to another commit
b) New rows appear matching predicate
c) Uncommitted data read
d) Deadlock happens
■ Answer: (a)

Q13. Phantom read occurs when:

a) Same row changes
b) New/removed rows appear matching a WHERE between reads
c) Dirty data read
d) Index missing
■ Answer: (b)

Q14. MVCC primarily helps to:

a) Speed up writes only
b) Allow readers to see a consistent snapshot without blocking writers
c) Eliminate logging
d) Avoid normalization
■ Answer: (b)

Q15. Deadlock is:

a) A single transaction waiting
b) Mutual waiting cycle among transactions for resources
c) Always impossible with locks
d) Same as starvation
■ Answer: (b)

Q16. To handle deadlocks, systems usually:

a) Ignore them
b) Use time travel
c) Detect and abort one victim
d) Purge all transactions
■ Answer: (c)

Q17. A SAVEPOINT allows:

a) Committing partial work
b) Rolling back to a named point within a transaction
c) Auto-commit disable
d) Snapshot export
■ Answer: (b)

Q18. Autocommit ON means:

a) Every statement runs in its own transaction and commits automatically
b) No transactions used
c) All statements rollback
d) Only SELECTs commit
■ Answer: (a)

Q19. Idempotent operation means:

a) Multiple executions have same effect as one
b) Requires locks
c) Cannot be retried
d) Always commutative
■ Answer: (a)

Q20. Recoverability requires that:

a) Cascading aborts are fine
b) A transaction never reads uncommitted data that later aborts
c) No logs needed
d) Only serializable isolation
■ Answer: (b)
Q21. Strict 2PL additionally ensures:
a) Release of write locks only at commit/abort to avoid cascading aborts
b) No deadlocks ever
c) No read locks
d) Exclusive locks released early
■ Answer: (a)

Q22. Snapshot isolation prevents:

a) Dirty and non-repeatable reads
b) Write skew always
c) All anomalies
d) Deadlocks
■ Answer: (a)

Q23. BEGIN/START TRANSACTION is used to:

a) Declare schema
b) Start a transaction boundary
c) Flush logs
d) Block readers
■ Answer: (b)

Q24. ROLLBACK undo:

a) Only last statement
b) Entire transaction unless to SAVEPOINT
c) Cannot undo
d) Only committed rows
■ Answer: (b)

Q25. Commit latency often impacted by:

a) Disk fsync for log flush
b) Query optimizer
c) Normalization level
d) Number of columns
■ Answer: (a)

Indexing (25 MCQs)

Q1. A B-Tree index is best for:
a) Equality lookups only
b) Range queries and order-by
c) Full text search only
d) Graph traversals
■ Answer: (b)

Q2. A hash index is best for:

a) Range scans
b) Equality lookups
c) Sorting
d) Prefix matches
■ Answer: (b)

Q3. A clustered index:

a) Stores table rows in index order physically/logically
b) Is same as unique index
c) Prevents fragmentation
d) Exists in all RDBMS
■ Answer: (a)

Q4. Leftmost prefix rule for composite index means:

a) Order of columns irrelevant
b) Optimizer always uses all columns
c) Index usable if query predicates start from left-most columns
d) Only equality predicates allowed
■ Answer: (c)

Q5. A covering index:

a) Contains all columns needed by a query, avoiding table lookup
b) Covers only PK
c) Covers only joins
d) Means bitmap index
■ Answer: (a)

Q6. High-cardinality columns are:

a) Have many distinct values; good for indexing
b) Have few distinct values
c) Always bad for indexing
d) Only booleans
■ Answer: (a)

Q7. Index maintenance cost affects:

a) SELECT only
b) INSERT/UPDATE/DELETE performance
c) DDL only
d) Backups only
■ Answer: (b)

Q8. Unique index enforces:

a) No NULLs
b) Value uniqueness across rows (NULL handling is DB-specific)
c) Faster sorts
d) Foreign key
■ Answer: (b)

Q9. When NOT to index a column:

a) Heavily updated low-selectivity column
b) Frequently used in WHERE
c) Used in JOINs
d) Used in ORDER BY
■ Answer: (a)

Q10. Bitmap indexes are typically good for:

a) OLTP with frequent updates
b) Data warehouses with low-cardinality columns
c) Range queries only
d) Graph DBs
■ Answer: (b)

Q11. An index on expression (functional index) helps when:

a) Queries filter on computed expressions
b) Only equality on base col
c) Only joins
d) Never
■ Answer: (a)

Q12. Primary key creation typically:

a) Does nothing
b) Creates/uses a unique index under the hood
c) Drops other indexes
d) Requires bitmap index
■ Answer: (b)

Q13. Statistics on indexes help:

a) Transaction logging
b) Query optimizer choose best plan
c) Durability
d) Isolation
■ Answer: (b)

Q14. Full table scan is chosen when:

a) Table small or predicate not selective
b) Index always exists
c) Always faster
d) Indexes disabled
■ Answer: (a)

Q15. Composite index order should place first:

a) Any column
b) Lowest cardinality
c) Most selective/commonly filtered leftmost
d) Text columns only
■ Answer: (c)

Q16. An index scan reads:

a) Only index pages
b) Only table heap
c) Never uses cache
d) Is always slower
■ Answer: (a)

Q17. Rebuilding/reorganizing indexes aims to:

a) Change schema
b) Reduce fragmentation and improve locality
c) Change isolation level
d) Disable logging
■ Answer: (b)

Q18. Hash indexes typically do NOT support:

a) Equality
b) Range queries
c) Joins
d) Aggregations
■ Answer: (b)

Q19. Partial/filtered index stores:

a) A subset of rows matching a predicate
b) All rows
c) Only distinct values
d) Only NULLs
■ Answer: (a)

Q20. Index selectivity refers to:

a) Fraction of rows matched by predicate
b) Depth of B-Tree
c) Fill factor
d) Page size
■ Answer: (a)

Q21. Creating too many indexes can:

a) Improve writes
b) Degrade write performance and increase storage
c) Disable optimizer
d) Force serializable isolation
■ Answer: (b)
Q22. Covering indexes can speed up queries by:
a) Avoiding table lookups (index-only scans)
b) Avoiding parsing
c) Avoiding ACID
d) Avoiding logs
■ Answer: (a)

Q23. Clustered vs nonclustered index key difference:

a) Clustered defines row order; nonclustered stores pointers to rows
b) No difference
c) Clustered cannot be unique
d) Nonclustered stores rows physically
■ Answer: (a)

Q24. Prefix indexes (on left part of string) help when:

a) Searching by string prefix
b) Searching by suffix
c) Only numeric fields
d) Only equality on integers
■ Answer: (a)

Q25. To support ORDER BY col1, col2 efficiently, index should be:

a) (col2, col1)
b) (col1, col2) composite in that order
c) Hash on (col1,col2)
d) Bitmap on col2
■ Answer: (b)