1# Database Theory Examples
2
3This directory contains 10 Python examples demonstrating key database theory concepts using SQLite3 (built-in, no external dependencies).
4
5## Files Overview
6
7### 1. `01_relational_model.py` - Relational Model Fundamentals
8**Concepts:**
9- Relations (tables), tuples (rows), attributes (columns), domains
10- Keys: superkey, candidate key, primary key, foreign key
11- Integrity constraints: entity integrity, referential integrity
12- NULL handling and 3-valued logic (TRUE, FALSE, UNKNOWN)
13
14**Run:** `python 01_relational_model.py`
15
16---
17
18### 2. `02_relational_algebra.py` - Relational Algebra Operations
19**Concepts:**
20- Selection (ฯ), Projection (ฯ), Cartesian Product (ร)
21- Join operations (โ): natural join, theta join, outer join
22- Set operations: Union (โช), Intersection (โฉ), Difference (โ)
23- Division (รท), Rename (ฯ)
24
25**Run:** `python 02_relational_algebra.py`
26
27---
28
29### 3. `03_er_to_relational.py` - ER-to-Relational Mapping
30**Concepts:**
31- 7-step ER-to-relational mapping algorithm
32- Regular entities โ tables
33- Weak entities โ tables with composite keys
34- 1:1, 1:N, M:N relationships
35- Multivalued attributes โ separate tables
36
37**Run:** `python 03_er_to_relational.py`
38
39---
40
41### 4. `04_functional_dependencies.py` - Functional Dependencies
42**Concepts:**
43- Armstrong's axioms: reflexivity, augmentation, transitivity
44- Derived rules: union, decomposition, pseudotransitivity
45- Attribute closure (X+)
46- Finding candidate keys
47- Minimal/canonical cover of FDs
48
49**Run:** `python 04_functional_dependencies.py`
50
51---
52
53### 5. `05_normalization.py` - Normalization and Normal Forms
54**Concepts:**
55- First Normal Form (1NF): atomic values, no repeating groups
56- Second Normal Form (2NF): no partial dependencies
57- Third Normal Form (3NF): no transitive dependencies
58- Boyce-Codd Normal Form (BCNF): every determinant is a superkey
59- Lossless-join decomposition
60- Dependency-preserving decomposition
61
62**Run:** `python 05_normalization.py`
63
64---
65
66### 6. `06_indexing_btree.py` - B+Tree Indexing
67**Concepts:**
68- B+Tree data structure: insert, search, range queries
69- Performance comparison: with vs without index
70- Composite (multi-column) indexes
71- Leftmost prefix rule
72- When to use indexes
73
74**Run:** `python 06_indexing_btree.py`
75
76---
77
78### 7. `07_transactions_acid.py` - Transactions and ACID
79**Concepts:**
80- **Atomicity:** All-or-nothing execution (COMMIT/ROLLBACK)
81- **Consistency:** Maintaining database invariants (constraints, triggers)
82- **Isolation:** Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
83- **Durability:** Persistence via Write-Ahead Logging (WAL)
84
85**Run:** `python 07_transactions_acid.py`
86
87---
88
89### 8. `08_concurrency_mvcc.py` - Concurrency Control
90**Concepts:**
91- Multi-Version Concurrency Control (MVCC)
92- Two-Phase Locking (2PL): growing phase, shrinking phase
93- Lock types: shared (S), exclusive (X)
94- Deadlock detection and prevention
95- Read/write conflicts
96
97**Run:** `python 08_concurrency_mvcc.py`
98
99---
100
101### 9. `09_nosql_document_store.py` - NoSQL Document Store
102**Concepts:**
103- Schema-less storage with JSON documents
104- CRUD operations on JSON data
105- Nested queries and array operations
106- Indexing JSON fields
107- Relational vs document approach trade-offs
108
109**Run:** `python 09_nosql_document_store.py`
110
111---
112
113### 10. `10_query_optimizer.py` - Query Optimization
114**Concepts:**
115- Query execution plans (EXPLAIN QUERY PLAN)
116- Join ordering optimization
117- Index selection strategies
118- Query equivalence transformations
119- Statistics and cost estimation
120- Manual optimization techniques
121
122**Run:** `python 10_query_optimizer.py`
123
124---
125
126## Requirements
127
128- Python 3.10 or higher
129- SQLite3 (built-in with Python, no installation needed)
130
131## Usage
132
133Each file is self-contained and can be run independently:
134
135```bash
136# Run a specific example
137python 01_relational_model.py
138
139# Or run all examples sequentially
140for f in *.py; do echo "=== $f ==="; python "$f"; echo; done
141```
142
143## Learning Path
144
145Recommended order for learning:
146
1471. **Fundamentals:** 01 โ 02 โ 03
1482. **Design Theory:** 04 โ 05
1493. **Performance:** 06 โ 10
1504. **Transactions:** 07 โ 08
1515. **Alternative Models:** 09
152
153## Key Takeaways
154
155- **Relational model** provides strong theoretical foundation (algebra, normal forms)
156- **Normalization** eliminates redundancy but requires more joins
157- **Indexes** dramatically improve read performance (B+Tree)
158- **ACID properties** ensure data integrity in multi-user environments
159- **Concurrency control** (MVCC, 2PL) enables safe concurrent access
160- **NoSQL** trades consistency/integrity for flexibility and performance
161- **Query optimization** is critical for large-scale databases
162
163## Additional Resources
164
165- *Database System Concepts* by Silberschatz, Korth, Sudarshan
166- *Fundamentals of Database Systems* by Elmasri, Navathe
167- SQLite documentation: https://www.sqlite.org/docs.html
168- PostgreSQL documentation (for production systems): https://www.postgresql.org/docs/