Database Systems Technology

Full/PDF syllabus: https://www.upo.es/intl/multidisciplinary-studies/?lang=en#1682059528657-7ae5432b-d8cd

Course Description  

This course is an introduction to database systems. We will explain how to query database systems via languages such as SQL. Then, we will see how database systems work internally, how they store and index data, how they process and optimize queries, and how they process transactions while providing guarantees such as isolation, atomicity, and durability (ACID guarantees).  Different database systems such a distributed DBMS, In-Memory databases and NoSQL databases will also be introduced.  

Course Goals  

It is expected that students get to know how a database works under different situations and with different technologies. This knowledge should help students to understand and solve different practical problems that can happen in their future professional career.   In addition, students will be introduced to different database types to provide them with an overview about benefits and drawbacks of each one.  Finally, we will explain how data can be uploaded to and fetched from a database so it can be added to a regular calculation procedure in a programming language. 

Methodology  

This course consists of 25 face-to-face sessions where both theoretical knowledge and practical sessions will be taught.  

Students will be given both comprehensive documentation and slides for each session. It is recommended that students look at the documentation in advance.  

Class attendance and participation will be mandatory as, at the end of each unit, practical exercises will be proposed. Presentations or discussion among students will be requested so everyone can learn from other’s experience and conclusions.  

Technical Requirements  

This course will require students to bring their laptop for the classes that require running databases examples. (Databases will not be running on tablets or smartphones).  

Course Materials  

Textbooks are not required for this course as documentation will be provided in advance.

Complementary Bibliography

1. Fundamentals of database systems. Ramez Elmasri, Shamkant B. Navathe. Addison-Wesley

2. Database Systems. Thomas Connolly, Carolyn Begg. Addison-Wesley  

 

Course Contents

Unit 1. Introduction to databases

- Introduction

- History of Database Applications

- Databases vs File system

- Characteristic of the database approach

- Summary  

Unit 2. SQL review

- Entity-Relation model

- Data Definition Language (DDL)

- Data Manipulation Language (DML)

- Cardinality restrictions

- Summary

- Examples  

Unit 3. Transaction Management

- Introduction

- ACID concept

- Properties

- Concurrency of users

- Sequentially and interference

- Isolation level

- Failure recovery

- Roles on DBMS

- Summary and review questions  

Unit 4. Query Processing

- Translating SQL into linear algebra

- Algorithms for external sorting

- Algorithms for SELECT and JOIN operations

- Implementing Aggregate Operations and OUTER JOINs

- Pipelining

- Summary and review questions 

Unit 5. Query Optimization

- Heuristic query optimization

- Selectivity and Cost Estimates in query optimization

- Semantic query optimization

- Summary and Review questions  

Unit 6. Index structures

- Disk storage, basic file structure and hashing

- Single-Level ordered Indexes

- Multilevel indexes

- Dynamic multilevel Indexes using B/B*

-Trees and B+

-Trees

- Summary and review questions  

Unit 7. Distributed DBMS

- Differences between distributed database systems, distributed processing, and parallel database systems

- Advantages and disadvantages of distributed DBMS

- Problems of heterogeneity

- Basic networking concepts

- Summary  

Unit 8. In-Memory databases

- Description

- Advantages and disadvantages

- ACID support

- Hybrids with on-disk databases

- Summary

- SQL Lite exercises  

Unit 9. Alternative database models

- NoSQL vs SQL

- Architecture

- Advantages and disadvantages

- noSQL systems

- Document oriented database: MongoDB

- Columnar Database: MariaDB

- Graph oriented database: Neo4j

- Exercises 

Unit 10. Connection to programming languages

- JPA

- ADO

- SQLAlchemy

- Exercises