DBS101 Unit 6
Before Learning
Before starting Unit 6, I had only worked with relational databases and had little idea about older models like the hierarchical and network models. I also didn’t fully understand the internal workings of SQL queries — how they are processed, optimized, and executed efficiently by the database engine.
I used to think that writing a query in SQL was enough — I didn’t realize there could be multiple ways to evaluate a query, and that performance can vary drastically based on how it is processed. These lessons opened my eyes to the internal mechanics that make modern databases powerful and efficient.
Key Takeaways from Unit 6
1. Hierarchical Database Model (Lesson 14)
- Organizes data in a tree structure: one parent per child.
- Easy to understand but rigid and difficult to adapt for complex relationships.
- Used in legacy systems like file systems and organizational charts.
2. Network Database Model (Lesson 15)
- Uses a graph-like structure: children can have multiple parents.
- Enables many-to-many relationships and is more flexible than hierarchical models.
- More powerful but also more complex and harder to manage.
3. Query Processing (Lesson 15)
- Involves parsing, translation, optimization, and execution.
- SQL queries are converted into relational algebra.
- Query evaluation plans determine the best execution strategy.
- Pipelining and materialization are two main ways queries are evaluated.
4. Query Optimization (Lesson 16)
- The process of choosing the most efficient evaluation plan for a query.
- Uses equivalence rules to transform relational algebra expressions into better forms.
- Chooses join order and join algorithms to reduce computation cost.
- Cost-based optimization considers statistics like data size and I/O costs.
- Uses dynamic programming and heuristics for efficient plan generation.
What I Learned and Why It Matters
Querying a database is not just about writing SQL — it’s about understanding how that query is processed and ensuring it runs efficiently.
Hierarchical and network models taught me that different data structures influence how relationships are managed.
Query processing steps showed me how SQL is internally converted to algebra and then to executable plans.
Optimization techniques such as join ordering, cost estimation, and pipelining made me realize how much performance can improve with smart design.
In large databases, even small changes in query structure or execution plan can lead to major performance gains — which matters for real-world systems.
Personal Growth and Reflection
This unit made me see the internal intelligence of database systems. I used to take for granted that SQL queries “just worked,” but now I appreciate the complexity involved in parsing, translating, optimizing, and executing every query efficiently.
I also realized that some database systems still use older models like hierarchical and network — and knowing how those work is important for understanding legacy systems or performing migrations.
Learning how databases estimate costs, choose plans, and optimize queries has made me more confident in writing performant queries and understanding what happens behind the scenes.
Including My Work from Class
Lesson 14 – Hierarchical Model
- Learned about tree-structured data modeling with strict parent-child relationships.
- Explored its limitations in terms of redundancy and rigidity.
- Modeled examples like organization hierarchies and file structures.
Lesson 15 – Network Model & Query Processing
- Studied network DBMS allowing multiple parent-child relationships.
- Understood query processing stages: parsing, translation, evaluation, and optimization.
- Practiced creating relational algebra expressions and evaluation plans.
- Learned about pipelining vs materialization, and their trade-offs in query execution.
Lesson 16 – Query Optimization
- Explored equivalence rules for transforming relational expressions.
- Practiced join ordering and choosing efficient join strategies.
- Learned how optimizers estimate cost using statistics and choose the best plan.
- Studied heuristics like applying selections early and using left-deep join trees.
- Implemented example transformations and compared plan efficiencies.