====== Lab #5 :: Week of 22/11/2021 ======

{{page>..:header&nofooter}}

===== Goals =====

By the end of this class, the student should be able to:
  * Identify and create constraints needed to verify the business rules
  * Estimate the expected database workload
  * Develop the database physical schema
  * Identify the database transactions
  * Create and populate a PostgreSQL database

===== Tasks =====

  * Present the artefact A5 (Relational Schema, Validation and Schema Refinement)
  * Identify and create constraints needed to verify the business rules, including triggers
  * Estimate the expected database workload
  * Identify and justify the creation of indexes
  * Identify and characterize the necessary database transactions
  * Create the database using PostgreSQL (locally and at the production server - db.fe.up.pt)
  * Populate the database with plausible data and with sufficient number of tuples for testing purposes

===== Materials =====

  * Database Specification Component
    * [[..:artefacts:index#ii_database_specification_ebd_25|EBD Component]] | [[https://git.fe.up.pt/lbaw/components/-/wikis/ebd|EBD Template (GitLab Wiki)]]
    * [[..:artefacts:a05|A5. Relational Schema, Validation and Schema Refinement]]
    * [[..:artefacts:a06|A6. Indexes, Triggers, Transactions and Database Population]]

  * MediaLibrary Example
    * [[https://docs.google.com/document/d/e/2PACX-1vTDVn_kO0Um8mJCgihvcCr8e6UuIHAUQIQjszZt7eIVcrMuRp6XWAgPSBICviYpOgfWZacSFEKDojXR/pub|A5: Relational Schema, Validation and Schema Refinement ]]
    * [[https://docs.google.com/document/d/e/2PACX-1vRm244bZU5hRBQlPW09VjYSHNP2yDOOmwBVzWVbplMXxKk-qkJRjTqUPPyFl8PWi-dxB5axfyEFJ-c5/pub|A6. Indexes, Triggers, Transactions and Database Population]]

  * Generate data to populate the database:
    * [[http://www.generatedata.com/|generatedata.com]]
    * [[http://spawner.sourceforge.net/|Spawner Data Generator]]

  * [[..:tools|LBAW Computational requirements]]
  * [[https://web.fe.up.pt/~ssn/wiki/teach/lbaw/202122/tools#postgresql_for_data_persistence|Computational Resources > PostgreSQL for Data Persistence]]
  * [[https://git.fe.up.pt/lbaw/template-postgresql|Template PostgreSQL]]

===== Summary =====

  * Integrity constraints and triggers.
  * Database indexes.
  * Database transactions.
  * Create and populate the database.

[[04|« Previous]] | [[index|Index]] | [[06|Next »]]