Full-Stack Development Database Design & Management 2 — Questions and Answers
Question 1: What is an ORM (Object-Relational Mapper) used for in full-stack development?
- Compressing database backups
- Mapping database tables to programming language objects to simplify data access (Correct answer)
- Optimizing SQL query execution plans
- Replicating data across multiple database servers
Correct answer: Mapping database tables to programming language objects to simplify data access
An ORM abstracts database interactions by mapping tables to classes and rows to objects, allowing developers to query databases using their programming language instead of raw SQL.
Question 2: Which PostgreSQL feature allows you to store and query JSON data natively?
- TEXT column with manual parsing
- JSONB column type (Correct answer)
- BLOB column type
- XML column with XPATH queries
Correct answer: JSONB column type
PostgreSQL's JSONB type stores JSON in a binary format that supports indexing and efficient querying, making it ideal for semi-structured data.
Question 3: What is database sharding?
- Creating read-only replicas of a database
- Encrypting database tables for security
- Horizontally partitioning data across multiple database instances (Correct answer)
- Compressing old data to archive storage
Correct answer: Horizontally partitioning data across multiple database instances
Sharding splits a large database horizontally across multiple servers (shards), each holding a subset of the data, to improve scalability and performance.
Question 4: In a relational database, what does a composite primary key consist of?
- A single auto-incrementing integer column
- Two or more columns whose combined values uniquely identify a row (Correct answer)
- A UUID generated at insert time
- A hash of all column values in the row
Correct answer: Two or more columns whose combined values uniquely identify a row
A composite primary key uses multiple columns together to uniquely identify each row, commonly used in junction tables representing many-to-many relationships.
Question 5: What is the primary difference between a clustered and a non-clustered index?
- Clustered indexes are stored in RAM; non-clustered are on disk
- A clustered index determines the physical order of data in the table; non-clustered does not (Correct answer)
- Non-clustered indexes are faster for all query types
- Clustered indexes can only be created on primary keys
Correct answer: A clustered index determines the physical order of data in the table; non-clustered does not
A clustered index physically reorders table rows to match the index order, while a non-clustered index is a separate structure that points to the actual row data.
Question 6: Which database isolation level prevents dirty reads but still allows non-repeatable reads?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct answer: Read Committed
Read Committed ensures a transaction only reads committed data (no dirty reads), but another transaction can modify data between reads within the same transaction.
Question 7: What is the CAP theorem in distributed databases?
- A formula for calculating optimal cache size
- The principle that a distributed system can only guarantee two of: Consistency, Availability, Partition tolerance (Correct answer)
- A rule for normalizing database schemas
- A protocol for encrypting database connections
Correct answer: The principle that a distributed system can only guarantee two of: Consistency, Availability, Partition tolerance
The CAP theorem states that in a distributed data store, you can only simultaneously guarantee two out of three properties: Consistency, Availability, and Partition tolerance.
Full-Stack Development Database Design & Management 2
What is an ORM (Object-Relational Mapper) used for in full-stack development?