Question 1

When space is tight and records cannot span pages, does splitting one logical row into multiple physical records reduce wasted space?

Accepted Answer

Introduction / Context:Physical design sometimes must cope with large or wide rows relative to page size. If a single physical record cannot span a page, packing it whole may leave unusable slack space. Splitting can mitigate waste.Given Data / Assumptions:Pages have fixed sizes (for example, 4 KB, 8 KB, 16 KB).Rows may be wide because of many columns or long variable-length attributes.DBMS may disallow row-overflow across pages in some storage engines or treat it with special overflow areas.Concept / Approach:Vertical splitting (storing subsets of columns separately) or row-overflow mechanisms can improve page utilization when a monolithic record would waste space. While there is pointer and join overhead, the trade-off can favor reduced fragmentation and better cache use.Step-by-Step Solution:Assess row width relative to page size and average free space.Identify low-access or sparse columns to move into a secondary physical record.Store frequently accessed, compact columns together to maximize rows per page.Measure I/O impact due to additional lookups versus space saved.Verification / Alternative check:Benchmark with realistic workloads to confirm fewer page reads per query and improved packing density.Why Other Options Are Wrong:Claiming it “always increases waste” ignores scenarios where packing improves; limitations to clustered indexes, fixed-length rows, or columnar engines are unfounded.Common Pitfalls:Over-splitting that forces frequent reassembly joins; ignoring hot/cold column access patterns.Final Answer:Valid in general — vertical splitting can reduce page waste

Question 2

Are data-volume and frequency-of-use statistics critical inputs to physical database design decisions (indexes, partitions, page size, caching)?

Accepted Answer

Introduction / Context:Physical design translates logical schemas into storage structures. To choose indexes, partitioning, compression, and caching, designers must understand data volumes and access frequencies.Given Data / Assumptions:High-frequency queries benefit from appropriate indexes and clustering.Large tables may require partitioning and compression to remain performant.Skewed access patterns affect caching and hot/cold data separation.Concept / Approach:Without workload stats, physical design becomes guesswork. Statistics guide decisions that minimize I/O and latency while controlling storage costs.Step-by-Step Solution:Collect cardinalities, row counts, growth rates.Profile query patterns (filters, joins, groupings).Choose indexes/partitions aligned with the most frequent and costly operations.Validate choices with explain plans and performance tests.Verification / Alternative check:Index/advisor tools in major DBMSs rely on workload captures, reinforcing their criticality.Why Other Options Are Wrong:Limiting importance to logical modeling or OLTP only is incorrect; both OLTP and analytics rely on these stats. Optimizers use stats, but designers must use them too.Common Pitfalls:Designing once and never revisiting as volumes and patterns evolve; ignoring seasonality and growth.Final Answer:Valid — workload statistics are critical to physical design

Question 3

Partitioning terminology: is distributing the columns of a table into separate physical records called horizontal partitioning, or is that vertical partitioning?

Accepted Answer

Introduction / Context:Correct terminology matters in physical design. Horizontal partitioning splits a table by rows; vertical partitioning splits by columns.Given Data / Assumptions:Horizontal: ranges or lists on a partition key assign different row sets to partitions.Vertical: columns are separated into different physical structures, often joined by the primary key.Both techniques can improve performance and manageability in different scenarios.Concept / Approach:The statement in the prompt mislabels column-based splitting as “horizontal.” The accurate term is “vertical partitioning.”Step-by-Step Solution:Map “horizontal” to row subsets (by time, region, hash).Map “vertical” to column subsets (hot/cold columns, wide attributes).Compare with the statement; identify the mismatch.Conclude the statement is invalid.Verification / Alternative check:Standard definitions across DBMS documentation align with this distinction.Why Other Options Are Wrong:Distribution, key choice, or storage engine type does not rename the concept.Common Pitfalls:Using “sharding” as a catch-all and confusing it with vertical partitioning.Final Answer:Invalid — splitting columns is vertical partitioning, not horizontal

Question 4

Linking data physically: are the two basic constructs for connecting data items sequential placement and pointer-based references?

Accepted Answer

Introduction / Context:Physical data access relies on how records are arranged and connected. Two foundational mechanisms are sequential organization and pointer-based linking.Given Data / Assumptions:Sequential placement (clustering) keeps related records near each other to enable efficient scans.Pointers (record IDs, page/slot references, B-Tree child pointers) allow direct navigation between related items.Many structures combine both ideas (for example, clustered B-Trees with leaf-level sibling pointers).Concept / Approach:Sequential storage exploits spatial locality; pointers enable selective traversal. Together, they underpin heaps, clustered indexes, linked lists, and tree structures.Step-by-Step Solution:Identify sequential clustering benefits for range queries.Identify pointer-based navigation for joins and index traversals.Recognize coexistence in common index designs.Therefore, the statement is valid.Verification / Alternative check:Index diagrams show sibling and child pointers; storage guides emphasize clustering for scan workloads.Why Other Options Are Wrong:Limiting to pointers alone ignores sequential organizations; tying validity to heaps, hierarchy, or page size is unjustified.Common Pitfalls:Overlooking fragmentation that breaks sequential locality; ignoring pointer maintenance costs on updates.Final Answer:Valid — sequential storage and pointers are fundamental linking constructs

Question 5

Do denormalization and clustering help minimize data access time for small records (by reducing joins and improving locality)?

Accepted Answer

Introduction / Context:Performance tuning often weighs normalization against practical access patterns. For small records, denormalization (fewer joins) and clustering (co-locating related rows) can reduce I/O and latency.Given Data / Assumptions:Small rows allow many tuples per page; good clustering increases hit rate.Denormalization stores pre-joined attributes to avoid repeated join costs.Workloads include frequent lookups or range scans on co-accessed attributes.Concept / Approach:Denormalization trades write complexity and redundancy for faster reads. Clustering arranges data physically to exploit locality, benefiting small-row scenarios where more rows fit into fewer pages.Step-by-Step Solution:Identify read-heavy queries that currently require multiple joins.Evaluate denormalization to embed frequently joined attributes.Use clustered indexes or storage ordering to keep related rows adjacent.Measure I/O reductions and latency improvements.Verification / Alternative check:Explain plans and buffer cache statistics typically show fewer logical and physical reads after appropriate denormalization and clustering.Why Other Options Are Wrong:Benefits are not limited to large rows, columnar stores, index-disabled states, or exclusively OLTP workloads.Common Pitfalls:Excessive denormalization causing anomalies; clustering on a column with low correlation to access patterns.Final Answer:Valid — both can reduce access time for small rows

Question 6

In database storage architecture, an extent is defined as a contiguous section of disk space allocated to store data pages or segments. Does this definition accurately describe an extent?

Accepted Answer

Introduction / Context:An extent is a low-level physical storage concept used by many database engines (for example, SQL Server, Oracle, PostgreSQL tablespaces with segment-like behavior) to manage space efficiently. Understanding extents helps DBAs reason about I/O patterns, fragmentation, growth, and capacity planning. Given Data / Assumptions:We consider common DBMS implementations that allocate data in fixed-size pages/blocks.Extents group consecutive pages/blocks on disk.Goal is to reduce allocation overhead and improve sequential I/O. Concept / Approach:Instead of allocating individual pages each time a table or index grows, the DBMS requests a larger unit — an extent — consisting of a contiguous run of pages. Contiguity promotes sequential reads/writes, reduces fragmentation, and lowers metadata overhead because one allocation operation reserves multiple pages at once. Step-by-Step Solution:Recognize that a page/block is the fundamental I/O unit (e.g., 8 KB).Extents are made of multiple adjacent pages (e.g., 8 pages for 64 KB).When a table or index needs more space, the engine allocates another extent rather than a single page.Because pages in an extent are contiguous, scanning them incurs fewer random I/Os.This contiguous allocation defines what an “extent” is. Verification / Alternative check:Examine allocation maps or space usage DMVs for a given DBMS; you will observe that growth occurs by extent-sized chunks and that adjacent pages belong to the same extent identifier, confirming contiguity. Why Other Options Are Wrong:Noncontiguous by design (option b) contradicts the definition; while fragmentation may occur over time, each extent itself is contiguous. Log-only (option c) is incorrect; extents apply broadly to data and index allocations. Cloud-only (option d) is wrong; the concept predates cloud storage. Single-page (option e) misstates the unit; an extent is multi-page. Common Pitfalls:Confusing “contiguous extent” with “the whole object is contiguous.” Objects may span many extents; not all extents for one object remain adjacent. Also, page size and extent size are DBMS-specific; do not assume identical sizes across platforms. Final Answer:Applies — this definition is accurate

Question 7

In physical data management terminology, is a pointer a detailed coding scheme for representing organizational data, or is it simply a reference (address/locator) to another data item?

Accepted Answer

Introduction / Context:Pointers appear in file systems, hierarchical/network databases, indexes, and internal DBMS structures. They link one record, page, or node to another. Clarifying this term avoids confusion with “codes,” which are standardized value sets used to represent business attributes. Given Data / Assumptions:Pointers store locations (e.g., page number + slot, RID, OID, rowid, or memory address).“Coding schemes” (e.g., gender code, product category code) are domain values for business data.The statement claims a pointer is a detailed coding scheme. Concept / Approach:A pointer is metadata about where to find something, not what that something means. In DBMS internals, leaf nodes of B-tree indexes, heap row links, or overflow chains all use pointers to navigate storage structures efficiently. Step-by-Step Solution:Identify what a pointer stores: a location or identifier of a target.Compare to a code, which stores a semantic category (e.g., “US” for country).Note that pointers enable navigation; codes enable interpretation of business meaning.Therefore, equating pointers with coding schemes is incorrect. Verification / Alternative check:Inspect DBMS documentation for row identifiers (RID/rowid/OID): each is defined as a locator, not a business code. Likewise, file systems use inode pointers to point to blocks, not to describe the data’s meaning. Why Other Options Are Wrong:Option b contradicts standard definitions. Option c restricts pointers to hierarchical systems; modern RDBMS also use them internally. Option d mixes normalization with storage references. Option e confuses pointers with cryptographic keys. Common Pitfalls:Confusing surrogate keys or OIDs with business codes; assuming pointers persist across reorganizations without indirection; mistaking logical foreign keys (values) for physical pointers (locators). Final Answer:Does not apply — a pointer is a reference/locator, not a coding scheme

Question 8

In physical database design, minimizing user wait time for queries and transactions is not important. Does this claim apply, or should physical design prioritize performance for user interactions?

Accepted Answer

Introduction / Context:Physical database design translates logical structures into storage, indexing, partitioning, and file layout choices. The purpose is to meet service level objectives for performance, reliability, and scalability. User experience is directly tied to latency; therefore, minimizing time to interact with information systems is critical. Given Data / Assumptions:Users care about response time and throughput.Workloads can be OLTP, OLAP, or mixed.Physical design tools include indexes, partitioning, compression, caching, and I/O distribution. Concept / Approach:Physical design aligns data access paths with query patterns. Proper indexing reduces I/O. Partitioning improves pruning and parallelism. Filegroup/tablespace placement balances I/O across devices. These steps exist precisely to minimize time required by users to complete their tasks. Step-by-Step Solution:Profile workload: identify high-frequency and high-cost queries/transactions.Add or adjust indexes/materialized views matching access predicates.Partition large tables and align partitions with date or key ranges.Distribute storage to avoid hot spots; leverage caching and compression.Validate improvements with benchmarks and monitoring metrics. Verification / Alternative check:Measure response time and I/O before and after index/partition changes. Consistent latency reductions confirm that physical design directly improves user interaction time. Why Other Options Are Wrong:Claiming performance is unimportant (option b) ignores real-world SLAs. Archival-only (option c) is narrow; even archives have retrieval SLAs. Fast hardware (option d) does not negate the need for good design. Zero network latency (option e) is unrealistic and still does not address disk and CPU costs. Common Pitfalls:Over-indexing causing slow writes; misaligned partitions; ignoring statistics and fragmentation; treating physical design as a one-time task rather than an iterative process. Final Answer:Does not apply — physical design should minimize user wait time

Question 9

State the primary goal of physical database design: is it chiefly to achieve data processing efficiency (throughput and latency), or something else?

Accepted Answer

Introduction / Context:Physical database design implements logical models on storage media. Its “north star” is performance within constraints of cost, availability, and integrity. Efficiency encompasses both query latency and overall throughput for the target workload. Given Data / Assumptions:Workloads have SLAs around response time and concurrency.Resources (CPU, memory, I/O) are finite.Design levers: indexing, partitioning, compression, placement, caching, and file formats. Concept / Approach:By placing data to reduce random I/O, designing appropriate indexes, and aligning partitions with query predicates, DBAs achieve efficient execution plans. Efficiency supports business objectives: faster analytics, timely transactions, and reduced infrastructure costs. Step-by-Step Solution:Map logical entities to physical tables and files.Select indexes and materialized summaries to match access paths.Partition/cluster to exploit pruning and parallelism.Tune storage parameters (extent sizes, fillfactor, autogrowth policies).Validate via workload replay and monitoring. Verification / Alternative check:Benchmark representative queries/transactions; measure CPU, I/O, and elapsed time before/after design changes to verify efficiency gains. Why Other Options Are Wrong:“Aesthetics” (option b) is not an engineering objective. Maximizing redundancy (option c) hurts consistency and storage costs. Avoiding indexes (option d) ignores read performance. Storing spreadsheets in tables (option e) is irrelevant to physical design goals. Common Pitfalls:Overfitting design to current queries and neglecting future growth; ignoring maintenance (statistics, rebuilds); forgetting that write-heavy OLTP and read-heavy OLAP need different optimizations. Final Answer:Applies — the primary goal is data processing efficiency

Question 10

In data modeling and implementation, does a “field” (table column) represent each component of a composite attribute when that attribute is decomposed?

Accepted Answer

Introduction / Context:Composite attributes (for example, a full address) contain multiple subcomponents (street, city, state, postal_code). Good relational design favors representing each subcomponent as its own field to enable precise querying, indexing, validation, and integrity. Given Data / Assumptions:A composite attribute is identified during conceptual/logical modeling.Implementation happens in a relational table with columns (fields).Queries will often filter or join on subcomponents. Concept / Approach:Decomposing a composite attribute improves data quality and performance: constraints (e.g., postal code format), selective indexes (e.g., on city or postal_code), and more granular analytics. Storing the whole composite in one field makes filtering difficult and can require brittle string parsing. Step-by-Step Solution:Identify composite attributes (e.g., Address).Split into atomic components (street, city, state, postal_code).Create separate fields with appropriate datatypes and constraints.Index frequently searched components (e.g., postal_code).Use views to recompose for display if needed. Verification / Alternative check:Confirm that queries like “find all customers in state = CA and city = San Jose” run efficiently without full-text parsing, demonstrating the benefit of separate fields. Why Other Options Are Wrong:Single-field storage (option b) reduces flexibility. Numeric-only (option c) is incorrect; text components benefit heavily. Spreadsheets (option d) are not the target platform. Denormalization (option e) is unrelated; decomposition is a normalization step. Common Pitfalls:Cramming multiple values into one field; inconsistent parsing; ignoring internationalization (e.g., different address formats). Final Answer:Applies — each component of a composite attribute becomes its own field

Physical Database Design Questions

Questions

Practice smarter