Relational database

Resources

Key questions

Why will we use PostgreSQL?

Because it’s commonly understood that all relational databases are the same at least in their foundations and PostgreSQL is more standard in the Open Source community.

Database relational

3.1 Modelo relacional
3.1.1 Estructura de una base de datos relacional
3.1.2 Operaciones relacionales: selección, proyección, reunión, unión e intersección
3.2 Lenguaje de consulta estructurada (SQL): creación, consulta, edición, borrado
3.3 Diseño de bases de datos relacionales
3.3.1. Modelo Entidad-Relación
3.3.2 Técnicas de normalización: 1ª. 2ª y 3ª forma normal
3.3.3 Técnicas de normalización multivaluada: 4ª y 5ª forma norma

https://github.com/sanchezcarlosjr/uabc/tree/main/src/relational-database

https://stackoverflow.com/questions/5363613/sample-database-for-postgresql

https://postgrespro.com/education/demodb

https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/

A database relational happens to be a database where his collections are relations -tables-, which have rows and columns and might have relationships with other tables. In this paradigm first, you design the schema, and then you enter data that conforms to the definition of that schema.

Example of tables -relations-:

https://sites.google.com/site/merasemester/dbm/relational-model

Origins

Database design

4.1 Introducción a los Sistemas Manejadores de Bases de Datos
4.2 Tipos de Manejadores de Bases de Datos y criterios de selección
4.3 Administración básica de un Manejador de Bases de Datos

5.1 Lenguajes de programación para bases de datos
5.2 Arquitecturas de Software (Frameworks) para implementación de bases de datos

6.1 Análisis y diseño de la aplicación
6.2 Implementación del diseño en un manejador específico: Oracle, Informix, MySQL, Postgres
6.3 Implementación del diseño de la aplicación mediante un lenguaje o Framework actual
6.4 Implementación de casos de prueba comunes en bases de datos

Approaches to developing a logical design of a database are Entity Modelling and Normalization.

Entity-relationship model

Logically structuring data

Normalization

Normalization is the process of database design concentrating on attributes and dependencies -relationships-, such that meets two requirements: There is no redundancy of data and all the data is stored in only one place.

Logic schema

UUID vs INT PRIMARY KEY

varchar vs char vs text

Wrap-up

DefiniendumDefiniens
Column
Row
Table
Primary key
CRUDAn acronym that refers to the four basic operations of persistent storage: Create, Read, Update, and Delete (CRUD)
SQL
JOIN
Index
B-tree

Bookmark the online PostgreSQL FAQ and documents

FAQ

https://www.postgresql.org/docs/

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

https://dba.stackexchange.com/questions/58894/differences-between-match-full-match-simple-and-match-partial

Select all the tables we created (and only those) from pg_class

SELECT relname FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = relnamespace WHERE nspname = 'public' AND relkind= 'r';

Physical design

Summary

Installation

💡
PostgreSQL's typical port is 5432 if it is available.

Docker

First of all, remember to install docker, at this moment I assume you have installed it. Thus when you'll need to share volumes.

docker run --rm -d \
    --name app \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v $(pwd)/backup:/backup \
    postgres

To connect psql -terminal to PostgreSQL-, we use exec

docker exec -it app psql postgres postgres

Otherwise, use pgAdmin, a user interface tool to PostgreSQL.

docker run --rm -d \
    --name app \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -p 5432:5432 \
    postgres

Now we're going to create a container to pgAdmin.

docker run --name pgadmin \
-e PGADMIN_DEFAULT_PASSWORRD=mysecretpassword \
-e [email protected] \
-e PGADMIN_LISTEN_ADDRESS=0.0.0.0\
-p 5000:80 \
-d dpage/pgadmin4

Docker compose

Alternatively, we could combine pgAdmin and PostgreSQL if we use docker-compose.

Simple example:

services:
  postgres:
    container_name: postgres_app
    image: postgres
    hostname: postgres_app
    ports:
      - 5432:5432
    environment:
       POSTGRES_USER: sanchezcarlosjr
       POSTGRES_PASSWORD: 123456
       POSTGRES_DB: app
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

  pgadmin:
    container_name: pgadmin_app
    image: dpage/pgadmin4
    depends_on:
      - postgres
    ports:
      - 5000:80
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: mysecretpassword

Download file in your project as docker-compose.yml and run

docker-compose up

Vagrant

Install vagrant, download this Vagrantfile, and run

vagrant up && vagrant ssh

IBM Cloud

PgAdmin

You will be able to visit http://localhost:5000/ after pgAdmin booting. To sign in write your credentials, in my case [email protected] and mysecretpassword since docker-compose.yml. At this point, you should be something as

Right-click over servers and click server.

From variables POSTGRES_USER: sanchezcarlosjr and POSTGRES_PASSWORD: 123456 with hostname postgres_app.

Ready! You have installed PostgreSQL and pgAdmin successfully.

Install extensions

We need tablefunc, dict_xsyn, fuzzystrmatch, pg_trgm, and cube.

How to import extensions

SQL

If you use docker, you can into a container:

docker exec -it app bash

Cheat Sheet

All of the databases relational check the SQL standard, but they might change functions, types, triggers, ...

SQL Cheat Sheet

MySql DevHint

PostreSQL

Relations, CRUD, and Joins

When you have installed Postgres, you can create a database from Bash with

createdb book

The command-line shell to connect with Postgres is psql.

psql book

You can find usage details about each SQL command in the following way:

\h CREATE INDEX

Creating a table consists of giving it a name and a list of columns.

The SQL to create a table looks like this:

CREATE TABLE countries (
	country_code char(2) PRIMARY KEY,
  country_name varchar(30) UNIQUE
); 

If you want to insert data:

INSERT INTO countries(country_code, country_name)
VALUES ('us', 'United States'), ('mx', 'Mexico'),
('au', 'Australia'),
('gb', 'United Kingdom'),
('de', 'Germany'),
('ll', 'Loompaland');

SQL checks unique key.

INSERT INTO countries(country_code, country_name)
VALUES ('us', 'United States');
ERROR:  duplicate key value violates unique constraint "countries_pkey"
DETAIL:  Key (country_code)=(us) already exists.

Read all data

SELECT * FROM countries;

Remove loop `ll`

DELETE FROM countries WHERE country_code='ll';

References

Viescas, J. & Hernandez M.J. (2014). SQL queries for mere mortals: a hands-on guide to data manipulation in SQL. Third Edition

Worked example: From conceptual design to implementation in PostgreSQL

Queries

Case study

Functions

Stored procedure vs language programming

Triggers

Backup

mysqldump DATABASE_NAME -p > FILE;

If you get next error

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

You need

mysqldump DATABASE_NAME --no-tablespaces >  BACKUP_NAME;

Restore backup

mysql -e "CREATE DATABASE al361075_db3" && mysql al361075_db3 < base.db

Seeding

Migrations

Optimizations

Materialized views

Triggers

Security

mysql --help | grep "Default options" -A 1

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Use ~/.my.cnf to authenticate app

[client]
user=""
password=""
database=""

Patterns

Save files

Testing

Wrap-Up

Strengths

Weaknesses

Worked example

Architecture

ORM

SQL Lite

Android Realm

Web Storage

Transform your database into consumible products

SQL to API REST

https://postgrest.org/en/stable/

https://marmelab.com/react-admin/Tutorial.html

SQL

References

Cushman, P. K. & Mata-Toledo, R. A. (2018). Fundamentals of
relational databases. Schaum´s Outlines.
Hernández, M. J. (2020). Database design for mere mortals:
25th anniversary edition (4th ed.). Adisson-Wesley
Professional.
Lemahieu, W., Vanden Broucke, S., & Baesens, B. (2018).
Principles of Database Management: The Practical
Guide to Storing, Managing and Analyzing Big and
Small Data. Cambridge: Cambridge University Press.
Romero, B. L. (2021). El libro práctico de bases de datos.
Publicación independiente.
Viescas, J. & Hernandez M.J. (2014). SQL queries for mere
mortals: a hands-on guide to data manipulation in SQL.
Third Edition. Adisson-Wesley. [Clásica]
Zea Ordóñez M. P., Molina Ríos J. R. y Redrován Castillo F. F.
(2017). Administracion de Bases de Datos con
PostgreSQL. Editorial Área de Innovación y Desarrollo,
S.L

https://www.3ciencias.com/wp-content/uploads/2017/04/
Administraci%C3%B3n-bases-de-datos.pd

Silberschatz A., Korth H. y Sudarshan S. (2017). Fundamentos de
bases de datos (6ta. ed.). McGraw-Hill Interamericana.
Elmasri, R., y Navathe, S. (2016). Fundamentals of database
systems. Londres: Pearson. [Clásica