Database Systems

There are no prerequisites to attend this Course.

All knowledge transmitted will be based on a strong practical component, where students will be invited to use a set of BDs software development applications, namely, SQL Server, DBSchema, ERDPlus, Radzen, to practice their knowledge.

Several practical/hands-on activities will be proposed that will allow the student to acquire the experience of designing a Database, from the theoretical specification to full functioning using the DBMSs.

The objectives of the Databases Course are: to provide the student with the fundamental concepts of Databases so that he can analyze, model, design, and conceive a Databases system. Thus, in the data model phase, the student must be able to implement the relationship entity model and adapt it to different realities and respective restrictions. Know how to structure a database according to the relational model, as well as perform all the operations of relational algebra associated with this model. Design databases using the UML modeling language.

In a second phase, relational databases require three basic themes, namely: 1) SQL (Structured Query Language) as a reference for any database management system; 2) Aspects related to the integrity and security of databases, namely referential integrity, as well as aspects of protection and access to stored data. Finally, 3) the database design phase according to the standardization theory.

Concerning competencies, when completing this UC, the student must:

• Realize the usefulness and evolution of BDs;
• Know DBMS applications;
• Understand the functioning of a DBMS;
• Analyze, model, design, and conceive BDs
• Know and master the entity-relationship (E-R) model;
• Master the structuring of a database according to the relational model and understand operations of relational algebra;
• Design Databases;
• Master the SQL (Structured Query Language);
• Understand and master the concepts of integrity, referential integrity and security of databases;
• Know the concepts of competition, distribution, and management of transactions in databases;
• Understand contexts of use of Data Warehouses.

The Databases course will work as a course on Inforestudante/Nónio platform available to students enrolled in this course, with guaranteed accessibility and permanent availability of content and monitoring of teachers.
Discussion forums will also be created, whenever they prove to be opportune, where active participation of students is expected. All elements of the UC will be available in this area, which will also function as a distance learning community.

1. Introduction to Databases:

    1.1 Database Management Systems (DBMS)
    1.2 Types of Databases and their Applications
    1.3 Entity-Relationship Model
    1.4 Features of a DBMS
    1.5 Types of Users
    1.6 Historical Development
    1.7 Evolution of Technology

2. Information System Life Cycle:
    2.1 Planning, Analysis, Design, Implementation, Maintenance
    2.2 Database Life Cycle

3. Database Management System, Architecture, and Models:
    3.1 Requirements of a DBMS
    3.2 DBMS Services
    3.3 ACID Properties and Transactions
    3.4 Client-Server Architecture

4. Introduction to SQL Language:
    4.1 SQL
    4.2 The Relational Model
    4.3 Database Schemas
    4.4 Integrity Constraints
    4.5 Types of SQL Statements
    4.6 SQL Syntax Elements
    4.7 Identifiers and Data Types
    4.8 Variables, Operators, and Expressions
    4.9 Queries in Relational Algebra

5. SQL Language:
    5.1 SQL Part 1

• Representation and Creation of a Database
• Keys
• Types of SQL Statements
• SQL Syntax Elements
• Identifiers and Data Types
• Variables, Operators, and Expressions

    5.2 SQL Part 2

• Select
• Alias
• Concat
• From
• Distinct
• Where
• Operators

    5.3 Aggregation Functions
    5.4 Subqueries
    5.5 Advanced SQL: Views and Triggers

6. Relational Model and Database Modeling:
    6.1 The Relational Model
    6.2 Tables and Relationships
    6.3 Keys
    6.4 Integrity Constraints
    6.5 Relational Algebra
    6.6 Relational Algebra Operations
    6.7 Examples of Operations with Relax
    6.8 Entity-Relationship Diagrams

7. Normalization Theory:
    7.1 Redundant Data
    7.2 Functional Dependencies
    7.3 Normalization: 1NF, 2NF, and 3NF

8. Functional Dependencies:
    8.1 Concept of Superkey, Candidate Key
    8.2 Basic and Derived Properties
    8.3 Armstrong’s Axioms
    8.4 Trivial Functional Dependency
    8.5 Closure of a set of FDs and attributes
    8.6 Coverage and Equivalence

9. NoSQL Databases:
    9.1 Types of NoSQL Databases
    9.2 MongoDB
    9.3 Neo4J

NAO

Main References

• Database systems: design, implementation, and management, 13rd edition, Carlos Coronel, Steven Morris, CENGAGE Learning , 2019

• Fundamentals of Database Systems, Elmasri, Navathe, 7th edition, 2016, Pearson, ISBN-13: 978-0-13-397077-7

• SQL, Luís Damas, maio, 2015, FCA Editores

• Bases de dados: Fundamentos e aplicações, Feliz Gouveia, 2.ª edição aumentada, 2021, FCA Editores

• Bases de Dados Relacionais – Implementação com MySQL”, Orlando Belo, 2021, FCA editores

Additional references

• Database System Concepts, Silberschatz, Korth, Sudarshan, 2010, 6 th edition, McGraw-Hill, ISBN 978-0073523323 (Elementos online: http://codex.cs.yale.edu/avi/db-book/db6/slide-dir/ )

• Modern Database Management, Jeffrey A. Hoffer, V. Ramesh, Heikki Topi, 10 th edition, 2011, Pearson, ISBN 13: 978-0-13-608839-4

• Projecto, Desenvolvimento de aplicações & Administração de Banco de Dados, Michael V. Mannino, 2008, McGraw-Hill, ISBN 978-85-7726-020-1

• MySQL, Frederico Tavares, 2015, FCA, ISBN: 978-972-722-803-4

• Oracle 10g e 9i Para Profissionais, António Rodrigues, 2010, ISBN: 978-972-722-344-2

• SQL SERVER 2014 Curso Completo, Alberto Magalhães, 2015, FCA, ISBN: 978-972-722-746-4

• Kimball, Ralph and Ross, Margy. The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition). John Wiley & Sons, 2002