University Computing Flashcards: Databases
Computing — Databases Flashcards
20 interactive flashcards for university-level Databases. Press Space to flip, rate 1-4.
Additional Flashcard Topics
Relational Model: tables (relations) with rows (tuples) and columns (attributes). Primary keys uniquely identify rows; foreign keys reference other tables. The relational algebra provides operations (select, project, join, union).
SQL: SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY. SQL is declarative — you describe what you want, not how to get it. The query optimizer determines the execution plan.
Normal Forms: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF (every determinant is a candidate key). Normalisation eliminates redundancy.
ACID Properties: Atomicity (all or nothing), Consistency (valid state transitions), Isolation (concurrent transactions don’t interfere), Durability (committed data survives crashes).
Indexing: B-tree indexes support O(log n) lookups and range queries. Hash indexes support O(1) equality lookups. Indexes speed up reads but slow down writes.
NoSQL: document stores (MongoDB), key-value stores (Redis), column families (Cassandra), graph databases (Neo4j). Trade ACID for scalability and flexibility.
Intuition
Databases are about organising, storing, and retrieving data efficiently and reliably. The relational model provides a mathematical foundation (set theory and first-order logic) for data manipulation through SQL.
Normalisation intuition: Normalisation eliminates redundancy and prevents update anomalies by decomposing tables. Each normal form (1NF, 2NF, 3NF, BCNF) addresses a specific type of dependency. The goal is to ensure that every non-key attribute depends on “the key, the whole key, and nothing but the key.”
Transaction intuition: A transaction is a logical unit of work that must be atomic (all or nothing), consistent (valid state to valid state), isolated (concurrent transactions don’t interfere), and durable (committed changes survive crashes) — the ACID properties. Without transactions, concurrent access could corrupt data.
Indexing intuition: An index is like a book’s index — it allows you to find data without scanning every row. B-tree indexes are the most common: they maintain sorted order and support efficient range queries. The trade-off is that indexes speed up reads but slow down writes (because the index must be updated).
Key Concepts
| Concept | Why It Matters |
|---|---|
| ACID properties | Guarantee reliable transactions even with concurrent access and failures |
| Normalisation | Eliminates redundancy and prevents update anomalies through decomposition |
| B-tree index | Maintains sorted order for O(log n) lookups; supports efficient range queries |
| CAP theorem | Distributed databases can guarantee at most 2 of: Consistency, Availability, Partition tolerance |
| Query optimisation | The query planner chooses the best execution plan; understanding this helps write efficient queries |
Common Pitfalls
Over-normalising. Excessive normalisation creates many small tables requiring expensive joins. In read-heavy analytics workloads, denormalisation can improve performance by reducing join complexity.
Ignoring index maintenance cost. Indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE must update all affected indexes. A table with many indexes may be slower for write-heavy workloads than one with no indexes.
Confusing NULL with zero or empty string. NULL represents unknown or missing data. Comparisons with NULL using = or ≠ return NULL (not true or false). Use IS NULL or IS NOT NULL instead. NULLs in aggregate functions like COUNT(column) are excluded.
Forgetting that joins are expensive. Each join multiplies the number of rows being processed. A query with three JOINs on tables with 1000 rows each can produce up to 10⁹ rows. Always filter before joining when possible.
Assuming that normalisation is always beneficial. While normalisation eliminates redundancy, it can hurt read performance due to joins. Analytical (OLAP) workloads often benefit from denormalisation.
Cross-References
- Databases Practice: Auto-graded problems testing database concepts.
- Systems: Computer architecture that database systems build upon; I/O and memory management matter.
- Theory of Computation: Formal languages underlying query languages; relational algebra is formal.
- Operating Systems: OS provides file systems and concurrency primitives used by databases.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.
Advanced Content
This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.
Derivations and Proofs
Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.
Extended Examples
Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.
Research Connections
This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.
Prerequisites
Ensure you have mastered the prerequisite material before attempting this advanced content.