Database Systems

The theoretical exposition precedes the practical part, providing the necessary structure for acquiring applied knowledge in the practical worksheets. All theoretical and practical material is available on the electronic platform (Inforestudante), along with the bibliography and links to examples and case studies from the adopted authors. Students will also have the opportunity to work with various online modeling tools, as well as locally installed databases, using native services or containerized environments. In this way, students will be autonomous and able to work from any location.

In class, students will have the opportunity to solve practical exercises and discuss the solutions with their peers and the instructor, initially employing an expository methodology, followed by an interrogative approach. Between the resolution of each exercise, students will have time to work individually or in groups, thus participating in the subsequent resolution. The practical worksheets cover topics such as SQL language, modeling through the entity-relationship model, normalization, and the identification of dependencies and keys. At the end, students will apply this knowledge in the construction and modeling of a database for a practical project, also creating an application using Rapid Application Development (RAD).

To complement the practical worksheets, students will be required to individually complete four mini-projects and collaboratively resolve a group project. There will be several follow-up classes during the modeling phase, in which the instructor will guide and assist students in completing their work. This way, independent study outside the classroom is encouraged.

The curricular unit aims to provide fundamental concepts about databases, empowering students to analyze, model, design, and develop these systems. The learning objectives are:

O1: Understand the historical importance of databases in technologies such as big data and machine learning;

O2: Know the capabilities and architecture of database management systems;

O3: Explore ACID and BASE properties, and the CAP theorem in distribution and scalability; O4: Understand the importance of transactions and apply concepts of the relational model and relational algebra;

O5: Model data with ER diagrams, defining attributes and assessing normalization up to the 3rd Normal Form (3NF);

O6: Identify functional dependencies and master concepts such as superkey and closure;

O7: Evaluate the advantages and disadvantages of NoSQL databases and perform basic operations in MongoDB;

O8: Execute operations in SQL, including views and stored procedures;

O9: Develop a database to meet a real need.

Theoretical topics:

T1. Introduction to Databases

T2. DBMS, Architecture, and Models:

T2.1 DBMS Requirements

T2.2 ACID, BASE, and CAP properties

T2.3 Importance of Transactions

T2.4 Client-Server Architecture and Microservices T2.5 DBMS Lifecycle

T3. Relational Model and Database Modeling:

T3.1 The Relational Model (Tables, relationships, keys, and constraints)

T3.2 Relational Algebra

T3.3 Entity-Relationship Diagrams

T4. Normalization Theory

T5. Functional Dependencies:

T5.1 Concepts of Key, Superkey, and Candidate Key

T5.2 Basic Properties of Functional Dependencies (FDs)

T5.3 Armstrong’s Axioms

T5.4 Closure of FDs and Attributes

T6. NoSQL Databases

Practical SQL content:

P1. Introduction to SQL:

P1.1 Tools

P1.2 Selection and Aggregation

P1.3 Transactions and Security

P1.4 Sorting in Databases

P1.5 Grouping Information

P1.6 Subqueries

P1.7 Data and Table Manipulation, Views, and Triggers

NAO