Database Design
115 flashcards by MentisLibrary
Designing a schema that holds up: entities and relationships, keys and constraints, normal forms and when to denormalize, data types, indexing strategy, transactions and isolation, and the patterns that keep coming back.
- What is a data model?
- A description of the data a system stores, how the pieces relate, and the rules that constrain them. It is built in three levels of detail: conceptual, logical, and physical.
- Conceptual vs logical vs physical data model
- Conceptual: the entities and relationships in business terms. Logical: attributes, keys and normalized structure, still independent of any product. Physical: actual tables, column types, indexes and storage for one specific database.
- What is an entity in data modeling?
- A distinct thing the business needs to keep facts about, which becomes a table in a relational design. Each row is one instance of the entity.
- What is an attribute?
- A single fact about an entity, which becomes a column. Each attribute should hold one atomic value of one type.
- What is an entity-relationship (ER) diagram?
- A diagram that draws entities as boxes, their attributes inside, and relationships as lines annotated with cardinality. It is the standard picture of a conceptual or logical model.
- What does cardinality mean for a relationship?
- The number of instances of one entity that can relate to an instance of the other: one-to-one, one-to-many, or many-to-many.
- What does optionality (participation) mean in a relationship?
- Whether an instance must take part in the relationship (mandatory) or may exist without one (optional). It is drawn as the inner symbol on a crow's-foot line.
- What is crow's foot notation?
- An ER notation where the end of a relationship line shows cardinality: a three-pronged foot means many, a single bar means one, and a circle means zero (optional).
- How do you implement a one-to-one relationship?
- Put the foreign key in one of the two tables and add a UNIQUE constraint on it, or share the same primary key in both tables.
- How do you implement a one-to-many relationship?
- Put a foreign key column on the many side that references the primary key of the one side.
- How do you implement a many-to-many relationship?
- With a junction table that holds a foreign key to each side; its primary key is usually the pair of foreign keys.
- What is a junction (associative) table?
- A table whose purpose is to connect two other tables in a many-to-many relationship. It may also carry attributes that belong to the pair, such as a date or a role.
- What is a self-referencing relationship?
- A relationship from a table to itself, implemented with a foreign key that references the same table's primary key.
- What is a weak entity?
- An entity that cannot be identified by its own attributes alone and depends on a parent entity; its key includes the parent's key.
- When should a multi-valued attribute become its own table?
- Whenever an entity can have more than one value for the same fact. The values move to a child table with a foreign key back to the parent.
- What is a primary key?
- A column or set of columns whose values uniquely identify each row and can never be NULL. Every table should have one.
- Natural key vs surrogate key
- A natural key is a real-world identifier that already exists in the data (ISBN, country code). A surrogate key is an artificial value, such as an auto-increment integer or a UUID, generated only to identify the row.
- What is a candidate key?
- Any minimal set of columns that uniquely identifies a row. One candidate key is chosen as the primary key; the others become alternate keys enforced with UNIQUE constraints.
- What is a superkey?
- Any set of columns that uniquely identifies a row, whether or not it is minimal. A candidate key is a superkey with no redundant columns.
- What is a composite key?
- A primary key made of two or more columns whose combination is unique. It is common on junction tables and weak entities.
- What is a foreign key?
- A column (or columns) in one table whose values must match a primary or unique key in another table, linking the two.
- What is referential integrity?
- The rule that every foreign key value either matches an existing key in the referenced table or is NULL. The database enforces it on insert, update and delete.
- ON DELETE CASCADE vs RESTRICT vs SET NULL
- They define what happens to child rows when the parent row is deleted: CASCADE deletes the children too, RESTRICT (or NO ACTION) refuses the delete, SET NULL clears the foreign key.
- Auto-increment integer vs UUID as a surrogate key
- Auto-increment integers are small, sequential and index-friendly but predictable and generated by the database. UUIDs are generated anywhere, safe to expose, and merge cleanly across systems, but are 16 bytes and random ones scatter index writes.
- Why do time-ordered ids (UUIDv7, ULID) index better than random UUIDs?
- Because new values sort after existing ones, so inserts append to the end of a B-tree instead of landing on random pages. They keep the global uniqueness of a UUID with the locality of a sequence.
- What is an alternate key?
- A candidate key that was not chosen as the primary key. It is enforced with a UNIQUE constraint so the value still cannot repeat.
- What is a functional dependency?
- A rule that the value of one set of columns determines the value of another: if two rows agree on X they must agree on Y, written X -> Y.
- What is normalization?
- The process of organizing tables so each fact is stored once, by splitting tables according to their functional dependencies. It removes redundancy and the update, insert and delete anomalies that come with it.
- What is first normal form (1NF)?
- Every column holds a single atomic value of one type, there are no repeating groups, and each row is unique.
- What is second normal form (2NF)?
- 1NF plus no partial dependency: every non-key column depends on the whole primary key, not on part of a composite key.