JCL

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:lectures:05

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

teach:lbaw:lectures:05 [03/04/2020 11:59]
teach:lbaw:lectures:05 [12/03/2021 16:22] (current)
Correia Lopes created
Line 1: Line 1:
 +====== L: 15/03/2021 ======
  
 +**Master in Informatics and Computing Engineering\\
 +Database and Web Applications Laboratory\\
 +Instance: 2020/21**
 +\\
 +---
 +\\
 +
 +====== Lecture #5 :: 15/03/2021 ======
 +
 +===== 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 =====
 +
 +  - SQL integrity constraints (recap)
 +    * Key Constraints.
 +    * Foreign key constraints.
 +    * Attribute constraints.
 +    * Tuple constraints.
 +    * SQL-3 Triggers.
 +  - Constraints in PostgreSQL.
 +  - Triggers in PostgreSQL.
 +  - Transactions and recovery
 +    * Transactions.
 +    * ACID properties.
 +    * Atomicity of transactions.
 +    * PostgreSQL and Multiversion Concurrency Control.
 +  - 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 13.2 Documentation. 11th February 2021, online in http://www.postgresql.org/docs/13/static/
 +
 +===== Materials =====
 +
 +  * Illustrations presented in class: {{06-integrity.pdf|Integrity constraints, triggers, transactions and user functions (A6)}}
 +  * J. Correia Lopes, Sérgio Nunes. [[..:artefacts/index#ii_database_specification_ebd_20|Database Specification (EBD)]], March 2019
 +  * PostgreSQL. //Constraints// [[http://www.postgresql.org/docs/13/ddl-constraints.html|PostgreSQL 13.2 Documentation, Section 5.4]]
 +  * PostgreSQL. //Triggers// [[http://www.postgresql.org/docs/13/triggers.html|PostgreSQL 13.2 Documentation, Chapter 38]]
 +  * PostgreSQL. //Concurrency Control//. [[http://www.postgresql.org/docs/13/mvcc.html|PostgreSQL 13.2 Documentation, Chapter 13]]
 +  * PostgreSQL. //PL/pgSQL --- SQL Procedural Language//. [[http://www.postgresql.org/docs/13/plpgsql.html|PostgreSQL 13.2 Documentation, Chapter 42]]
 +  * PostgreSQL. //User-defined Functions//. [[http://www.postgresql.org/docs/13/xfunc.html|PostgreSQL 13.2 Documentation, Section 37.3]]
 +
 +===== Summary =====
 +
 +  * Integrity Constraints in SQL, assertions and triggers. Transactions, ACID properties, Concurrency Control. Database stored procedures.
 +
 + --- //LBAW, 2020/21//
 +
 +[[04|« Previous]] | [[index|Index]] | [[06|Next »]]