Relational database
Resources
Resources
Name | Tags |
---|---|
Elmasri, R. and Navathe, S. 2011. Fundamentals of database systems. Addison-Wesley. | |
Viescas, J. and Hernandez, M. 2008. SQL queries for mere mortals. Addison-Wesley. | |
Untitled |
Complement
Name | Tags |
---|---|
Karwin, B. and Carter, J. 2014. SQL antipatterns. Pragmatic Bookshelf. | |
Untitled | |
Database systems - Cornell Courses | |
Untitled | |
Untitled |
Doxography
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-:
Origins
- A relational model of data for large shared data banks. E. F. Codd: Relational algebra.
- Table from relation.
- Row -a record- from a tuple.
- Projection defines conditions on data you want to retrieve.
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
- Entity. A class in which we abstract domain or technical issues and we need to store his objects or instances by the requirements.
Good examples. Student, Settings.
Bad examples. People. Doesn't specific. It's preferable domain's word.
- Attribute. A property of an entity.
- Domain.
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
Definiendum | Definiens |
Column | |
Row | |
Table | |
Primary key | |
CRUD | An 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
https://www.postgresql.org/docs/
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
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.
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, ...
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
Requirements
The Agency currently rents out various types of domestic accommodation (flats and houses) to clients. The clients (e.g. students or groups of students) may require 1-year leases or (e.g. families) longer-term lets.
Details are kept on each property and in particular:
- Address (this must be searchable by postcode or partial postcode or by district)
- Owner details
- Lead tenant details
- Tenancy start
- Tenancy end
- Rent
- Type of property (flat, detached house, terrace house etc.)
- Furnished/Unfurnished (for Furnished, further details of the furnishings may be kept)
- Number of bedrooms
- Number of bathrooms
- Number of reception rooms
Optionally also:
- A textual description
- Photographs
A history of occupancy needs to be kept including periods were the property is unoccupied.
In order to make searching for appropriate properties easier, it has been decided that details of individual rooms within a property will also be stored. This will include:
- room type (bedroom/bathroom/kitchen etc.)
- room dimensions (in feet and meters)
- heating (e.g. radiator/fire)
- for kitchens: appliances
- for bathrooms: fittings
- any special features e.g. patio windows.
The tourist business has started to boom in the area and the Agency wishes to move into the business of holiday lettings. This will involve much shorter lets and a much greater requirement for up to date availability information.
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