Week 20: CST 363 - Introduction to Database Systems

 

 WK04: Midpoint Reflections

I'm not new to databases and have some practical experience with SQL, but this course gave me the theoretical backbone for concepts I've used pragmatically. I connected everyday tasks - designing tables, choosing keys, writing joins, and skimming plans - to the relational model and formal normalization. Seeing why clustered vs. heap layouts affect access, how B-tree indexes support equality/range predicates, and where optimizers derive their strategies clarified earlier "rules of thumb." Overall, I gained vocabulary and structure to justify design choices.

Brief Summary of 5 Things Learned

I strengthened the relational model: entities/attributes, candidate vs. primary keys, and why foreign keys enforce meaning across tables. I practiced expressive SQL beyond basics - joins, subqueries, and aggregates - while reading execution plans to understand scans, seeks, and join algorithms. I deepened constraints and referential integrity to prevent bad data at the source. Normalization from 1NF through BCNF clarified partial/transitive dependencies and when selective denormalization or materialized views is warranted. Finally, I tied physical design to performance: table organization, single/multi-level indexes, and how index choice shapes access paths, plus clearer expectations for views' convenience vs. maintenance and performance trade-offs.

Questions I Still Have

I’m curious how the optimizer estimates row counts and costs in execution plans, and how to tell from a plan when an index would actually help versus when a scan is fine. I also want a clearer rule of thumb for balancing normalization with performance—when a view or a bit of denormalization is the better choice in practice. Finally, I’d like more guidance on index maintenance trade-offs: which columns to index for common filters and joins, how wide keys affect lookups, and how to avoid over-indexing that slows inserts and updates.