J. Correia Lopes

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:lectures:06

T: 22/03/2019

Master in Informatics and Computing Engineering
Database and Web Applications Laboratory
Instance: 2018/2019


Lecture #6 :: 22/03/2019

Goals

By the end of this class, the student should be able to:

  • Describe how to implement data business rules using SQL.
  • Describe how to maintain consistency of the database in the presence of concurrent accesses.
  • Write database user-defined functions.

Content

  1. SQL integrity constraints (recap)
    • Key Constraints.
    • Foreign key constraints.
    • Attribute constraints.
    • Tuple constraints.
    • SQL-3 Triggers.
  2. Constraints in PostgreSQL.
  3. Triggers in PostgreSQL.
  4. Transactions and recovery
    • Transactions.
    • ACID properties.
    • Atomicity of transactions.
    • PostgreSQL and Multiversion Concurrency Control.
  5. Database Store procedures
    • User-defined functions in PostgreSQL.

Bibliography

  • R. Ramakrishnan, J. Gehrke. Database Management Systems. McGRAW-Hill International Editions, 3rd Edition, 2003, ISBN=0-07-246563-8
  • The PostgreSQL Global Development Group. PostgreSQL 9.4 Documentation. Online in http://www.postgresql.org/docs/9.4/static/, last accessed on March 2018

Materials

Summary

  • Integrity Constraints in SQL, assertions and triggers. Transactions, ACID properties, Concurrency Control. Database stored procedures.1)

LBAW, 2018/19

« Previous | Index | Next »

1)
Implementação de Restrições de integridade em SQL: de chave, referenciais, de domínio, baseadas em atributos e baseadas em tuplos; asserções; gatilhos. Transações, propriedades ACID, Controlo de concorrência. Procedimentos na base de dados.
teach/lbaw/lectures/06.txt · Last modified: 16/03/2019 09:49 by Correia Lopes