Databases : understand how to organize and to use data

Relational , Document-based and Graph-based databases

The big picture

Databases : Why?

Difference between databases and files
Performance, security and reusability concerns

Databases : Goals

Store and query data
Transparent implementation for user
Used accross several applications

Databases : 3 different languages

Model data : Data Definition Language (Data Structure)
Modifiy data : Data Manipulation Language (Data modification)
Control data : Data Control Language

Databases : 3 different languages

DDL represents the data (Entities and Relationships)
DML gives a simple way to modify data
DCL ensures that data are correctly modified and that integrity is preserved

Table Structure

The table represents an entity (equivalent of a UML Class), it is also called relation.
A relation (table) is constituted of attributes (columns), and rows of this table are called tuples.
Each rows represents one instance of the entity represented by this table

Table Structure (2)

Column types (most common ones)

Type Name	Description
CHAR(n)	Fixed length characters string
VARCHAR(n)	Variable length characters string
TEXT	Infinite length characters string
INT	Integer numeric value
FLOAT(n)	Floating point numeric value
DATE	Stores the date only
TIME	Stores the time only

Column types (Continued, specific to PostgreSQL)

Type Name	Description
TIMESTAMPTZ	Stores the time stamp (date and time) with time zone
ARRAY[n]	Stores an array of dimension n, the array can store any type
JSON	Stores a JSON document with ability to query

First contact with DDL, with PostgreSQL

Why PostgreSQL?
Demo Setting up PostgreSQL with CentOS and docker

Linking with other tables

It exists some possibility to declare links to other tables
It is part of the Control function of Databases
Primary keys to define uniqueness for tuples
Foreign keys to define external data linked to the current tuple

Linking with other tables (2)