Physical Data Modeling Flashcards

What is schema-on-read, and how does it differ from schema-on-write?

Answer: Schema-on-read applies structure at query time; schema-on-write enforces structure when data is stored

Schema-on-write (traditional RDBMS) enforces a defined schema when data is stored, while schema-on-read (data lakes) applies interpretation at query time, allowing raw data ingestion.

Which physical modeling concept involves grouping related tables into named containers within a database?

Answer: Schema (namespace)

A schema (namespace) groups related database objects — tables, views, procedures — into a logical container, enabling organization, access control, and naming isolation.

What is the role of a tablespace in physical database design?

Answer: A storage container that maps database objects to physical disk files or storage devices

A tablespace is a physical storage unit that maps database objects to one or more underlying data files on disk, enabling storage management and I/O optimization.

What does DDL stand for and what does it contain?

Answer: Data Definition Language — SQL statements that create, alter, and drop database objects

DDL (Data Definition Language) is the subset of SQL containing CREATE, ALTER, DROP, TRUNCATE, and RENAME statements that define and modify database structure.

In physical data modeling, when is it appropriate to choose a CHAR data type over VARCHAR?

Answer: When the column stores fixed-length values that are always the same length, such as a 2-character state code

CHAR is appropriate for truly fixed-length data (state codes, ISO country codes) because it stores exactly N characters without overhead, while VARCHAR varies in storage.

What is a composite primary key, and what should you consider when using one?

Answer: A primary key made of two or more columns that together uniquely identify a row

A composite primary key uses multiple columns together to uniquely identify rows, which is common in associative entities but adds complexity to foreign key relationships.