Databases : understand how to organize and to use data

Relational , Document-based and Graph-based databases

Databases : Why?


Centralized Modeling
Centralized Modeling
Per program modeling
Per program modeling
Program 1
Program 1
Program 2
Program 2
File 1
File 1
File 2
File 2
Program 1
Program 1
Program 2
Program 2
DB
DB
V.S
V.S
Viewer does not support full SVG 1.1

Databases : Goals


Databases : 3 different languages


Databases : 3 different languages


Defining relation : the table

Table Structure


Table Structure (2)


ColumnĀ  (attribute)
ColumnĀ  (at...
Tuple
Tuple
1
1
France
France
FR
FR
2
2
Spain
Spain
ES
ES
3
3
Italy
Italy
IT
IT
Viewer does not support full SVG 1.1

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


Linking with other tables


Linking with other tables (2)


Primary Key
Primary Key
Foreign Key
Foreig...
1
1
France
France
FR
FR
2
2
Spain
Spain
ES
ES
3
3
Italy
Italy
IT
IT
1
1
Paris
Paris
1
1
2
2
Lyon
Lyon
1
1
3
3
Marseille
Marseille
1
1
Viewer does not support full SVG 1.1