• ES Español
• EN English

5.28. Databases II (Mandatory)

5.28.1. Justification ↑ Back to top

This course is a continuation of Data Management I, focusing on the internal mechanisms of database management systems (DBMS) and modern database technologies. It covers query optimization, indexing structures, and transactional integrity in depth. Additionally, it introduces students to non-relational models (NoSQL) and distributed architectures, which are essential for handling large-scale data in contemporary cloud environments.

5.28.2. Generales Goals ↑ Back to top

1. Understand the internal storage structures and indexing techniques.
2. Analyze and optimize query execution plans.
3. Comprehend advanced transaction protocols and recovery mechanisms.
4. Evaluate and implement solutions using NoSQL database models.
5. Understand the principles of distributed and parallel databases.

5.28.3. Contribution to Outcomes ↑ Back to top

AG-C08) Problem Analysis: Identifies, formulates, and analyzes complex computing problems. (Usage)

AG-C12) Applies computer science theory and software development fundamentals to produce computer-based solutions. (Usage)

5.28.4. Content ↑ Back to top

5.28.4.1. Query Processing (12 hours) [Skills AG-C08,AG-C12] ↑ Back to top

Bibliography: (Silberschatz et al., 2019; Ramakrishnan and Gehrke, 2003)

Topics

1. Page structures
2. Index structures:
3. B+ trees
4. Hash indices: static and dynamic
5. Index creation in SQL enumerate
6. File structures:
7. Heap files
8. Hash files enumerate
9. Algorithms for query operators:
10. External Sorting
11. Selection
12. Projection; with and without duplicate elimination
13. Natural Joins: Nested loop, Sort-merge, Hash join
14. Analysis of algorithm efficiency enumerate
15. Query transformations
16. Query optimization:
17. Access paths
18. Query plan construction
19. Selectivity estimation
20. Index-only plans enumerate
21. Parallel Query Processing (e.g., parallel scan, parallel join, parallel aggregation)
22. Database tuning/performance:
23. Index selection
24. Impact of indices on query performance
25. Denormalization enumerate

Learning Outcomes

1. Describe the purpose and organization of both B+ tree and hash index structures [Describe]
2. Compose an SQL command to create an index (any kind) [Compose]
3. Specify the steps for the various query operator algorithms: external sorting, projection with duplicate elimination, sort-merge join, hash-join, block nested-loop join [Analyze]
4. Derive the run-time (in I/O requests) for each of the above algorithms [Analyze]
5. Transform a query in relational algebra to its equivalent appropriate for a left-deep, pipelined execution [Redesign]
6. Compute selectivity estimates for a given selection and/or join operation [Compute]
7. Describe how to modify an index structure to facilitate an index-only operation for a given relation [Describe]
8. For a given scenario decide on which indices to support for the efficient execution of a set of queries [Assess]
9. Describe how DBMSs leverage parallelism to speed up query processing by dividing the work across multiple processors or nodes [Describe]

5.28.4.2. DBMS Internals (12 hours) [Skills AG-C08,AG-C12] ↑ Back to top

Bibliography: (Silberschatz et al., 2019; Garcia-Molina et al., 2008)

Topics

1. DB Buffer Management
2. Transaction Management:
3. Isolation Levels
4. ACID
5. Serializability
6. Distributed Transactions enumerate
7. Concurrency Control:
8. 2-Phase Locking
9. Deadlocks handling strategies
10. Quorum-based consistency models enumerate
11. Recovery Manager:
12. Relation with Buffer Manager enumerate
13. Concurrency Control:
14. Optimistic concurrency control
15. Timestamp concurrency control enumerate
16. Recovery Manager:
17. Write-Ahead logging
18. ARIES recovery system (Analysis, REDO, UNDO) enumerate

Learning Outcomes

1. Describe how a DBMS manages its Buffer Pool [Describe]
2. Describe the four properties for a correct transaction manager [Describe]
3. Outline the principle of serializability [Outline]

5.28.4.3. Distributed Databases/Cloud Computing (12 hours) [Skills AG-C08,AG-C12] ↑ Back to top

Bibliography: (Silberschatz et al., 2019; Özsu and Valduriez, 2020)

Topics

1. Distributed DBMS:
2. Distributed data storage
3. Distributed query processing
4. Distributed transaction model
5. Homogeneous and heterogeneous solutions
6. Client-server distributed databases enumerate
7. Parallel DBMS:
8. Parallel DBMS architectures: shared memory, shared disk, shared nothing
9. Speedup and scale-up, e.g., use of the MapReduce processing model
10. Data replication and weak consistency models enumerate

Learning Outcomes

1. Describe the key components of a distributed DBMS, including distributed data storage, query processing, and transaction management [Describe]
2. Analyze the tradeoffs between parallel DBMS architectures: shared memory, shared disk, and shared nothing [Analyze]
3. Describe data replication strategies and weak consistency models in distributed database systems [Describe]

5.28.4.4. NoSQL Systems (12 hours) [Skills AG-C08,AG-C12] ↑ Back to top

Bibliography: (Sadalage and Fowler, 2012; Kleppmann, 2017a)

Topics

1. Why NoSQL? (e.g., Impedance mismatch between Application [CRUD] and RDBMS)
2. Key-Value and Document data model
3. Storage systems (e.g., Key-Value systems, Data Lakes)
4. Distribution Models (Sharding and Replication)
5. Graph Databases
6. Consistency Models (Update and Read, Quorum consistency, CAP theorem)
7. Processing model (e.g., Map-Reduce, multi-stage map-reduce, incremental map-reduce)
8. Case Studies: Cloud storage system (e.g., S3); Graph databases; "When not to use NoSQL"

Learning Outcomes

1. Develop a use case for the use of NoSQL over RDBMS [Create]
2. Describe the defining characteristics behind Key-Value and Document-based data models [Describe]

5.28.4.5. Semi-structured and Unstructured Databases (12 hours) [Skills AG-C08,AG-C12] ↑ Back to top

Bibliography: (Kleppmann, 2017a; Manning et al., 2008; Mikolov et al., 2013; Agrawal et al., 2023; MongoDB, Inc., 2025)

Topics

1. Vectorized unstructured data (text, video, audio, etc.) and vector storage:
2. TF-IDF Vectorizer with ngram
3. Word2Vec
4. Array database or array data type handling enumerate
5. Semi-structured databases (e.g., JSON):
6. Storage:
7. Encoding and compression of nested data types enumerate
8. Indexing:
9. Btree, skip index, Bloom filter
10. Inverted index and bitmap compression
11. Space filling curve indexing for semi-structured geo-data enumerate
12. Query processing for OLTP and OLAP use cases:
13. Insert, Select, update/delete tradeoffs
14. Case studies on Postgres/JSON, MongoDB, and Snowflake/JSON enumerate enumerate

Learning Outcomes

1. Describe vectorized representations of unstructured data (e.g., TF-IDF, Word2Vec) and their storage models [Describe]
2. Apply indexing techniques (e.g., B-tree, inverted index, Bloom filter) to semi-structured data stored in JSON format [Apply]
3. Analyze query processing tradeoffs for semi-structured databases in OLTP and OLAP use cases [Analyze]

5.28.5. Bibliography ↑ Back to top

Silberschatz, A., Korth, H. F., and Sudarshan, S. (2019). Database System Concepts. McGraw-Hill, 7th edition.

Ramakrishnan, R. and Gehrke, J. (2003). Database Management Systems. McGraw-Hill, 3rd edition.

Garcia-Molina, H., Ullman, J. D., and Widom, J. (2008). Database Systems: The Complete Book. Pearson, 2nd edition.

Özsu, M. T. and Valduriez, P. (2020). Principles of Distributed Database Systems. Springer, 4th edition.

Sadalage, P. J. and Fowler, M. (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Addison-Wesley.

Kleppmann, M. (2017a). Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. O'Reilly Media.

Manning, C. D., Raghavan, P., and Schütze, H. (2008). Introduction to Information Retrieval. Cambridge University Press.

Mikolov, T., Chen, K., Corrado, G., and Dean, J. (2013). Efficient estimation of word representations in vector space. arXiv preprint. arXiv:1301.3781.

Agrawal, D. et al. (2023). Toward a systems architecture for ai-enabled applications. Communications of the ACM, 66(6):54–63.

MongoDB, Inc. (2025). Mongodb manual. MongoDB, Inc. https://www.mongodb.com/docs/.