Primary and foreign keys, normalization forms, indexes, and constraints, each explained with a short example.
35 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a primary key in a database table?
- A column or group of columns that uniquely identifies each row in a table. No two rows can have the same primary key value.
- Why do we use primary keys?
- To ensure each row is uniquely identifiable, prevent duplicate records, and serve as the foundation for relationships between tables.
- What is the difference between a primary key and a unique constraint?
- A primary key uniquely identifies rows and cannot be null; a unique constraint enforces uniqueness but allows null values (except in some systems) and a table can have only one primary key but multiple unique constraints.
- What is a foreign key?
- A column or group of columns in one table that references the primary key of another table, creating a link between the two tables.
- What is the purpose of a foreign key?
- To maintain referential integrity by ensuring that values in the foreign key column exist as primary keys in the referenced table, preventing orphaned records.
- What is referential integrity?
- A database constraint that ensures every foreign key value either exists as a primary key in the referenced table or is null.
- What is a database index?
- A data structure that speeds up retrieval of rows from a table by maintaining a sorted copy of selected columns.
- What are the benefits of creating an index?
- Faster data retrieval for queries that filter or sort on indexed columns, improving SELECT query performance.
- What is the trade-off of creating an index?
- Indexes consume extra disk space and slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.
- What is a clustered index?
- An index that determines the physical order of rows in the table. A table has only one clustered index, typically the primary key.
- What is a non-clustered index?
- An index that does not affect the physical order of rows and points back to the actual row location. A table can have multiple non-clustered indexes.
- What is database normalization?
- The process of organizing table structure to eliminate redundancy and dependency anomalies, typically following progressive normal forms (1NF, 2NF, 3NF, BCNF).
- What does First Normal Form (1NF) require?
- All column values must be atomic (indivisible), meaning each cell contains only a single value, not lists or repeating groups.
- Describe a violation of 1NF and how to fix it.
- A cell containing comma-separated phone numbers like '555-1234, 555-5678' violates 1NF. Fix by creating a separate Phones table with one phone per row, linked by customer ID.
- What does Second Normal Form (2NF) require?
- The table must be in 1NF and every non-key column must depend on the entire primary key, not just part of it.