Obtention du schéma relationnel

Clés étrangères

Pour chaque entité, on commence par rechercher les références à des clés d'autres entités, indiquées par des dièses #.

Exemple :
clients(numéro_client, nom_client, prénom_client, adresse_client)
commandes(numéro_comd, date_comd, #numéro_client (non vide))

Quelques contraintes peuvent être spécifiées sur les attributs, comme "unique" ou "not null" (non vide ci-dessus).

Remarques

• Une même table peut avoir plusieurs clés étrangères, mais une seule clé primaire (éventuellement composée).
• Une colonne clé étrangère peut aussi être primaire.
• Une clé étrangère peut être composée (c'est le cas si la clé primaire référencée est composée).
• Implicitement, chaque colonne qui compose une clé primaire ne peut pas recevoir la valeur vide (NULL interdit). Mais,
• Si une colonne clé étrangère ne doit pas recevoir la valeur vide, alors il faut le préciser dans la description des colonnes.

Intégrité référentielle : Le SGBD vérifie à chaque opération que les clés étrangères ne prennent que des valeurs autorisées.

Cas des associations

Notations : on dit qu'une association binaire (entre deux entités ou réflexive) est de type :

• 1 : 1 (one-to-one) si aucune des cardinalités maximales n'est n ;
• 1 : n (one-to-many) si une des cardinalités maximales est n ;
• n : m (many-to-many) si les deux cardinalités maximales sont n.

Remarque :
Un schéma relationnel ne peut faire la différence entre 0,n et 1,n. Par contre, il peut la faire entre 0,1 et 1,1 (cf. règles 2 et 4 à suivre).

Traduction E/A $\rightarrow$ rel. : règles 1 et 2

Règle 1 : toute entité devient une table dans laquelle les attributs deviennent les colonnes. L'identifiant de l'entité constitue alors la clé primaire de la table.

Règle 2 : une association binaire de type 1 : n disparaît, au profit d'une clé étrangère dans la table côté 0,1 ou 1,1 qui référence la clé primaire de l'autre table. Cette clé étrangère ne peut pas recevoir la valeur vide si la cardinalité est 1,1.

fournisseurs( num_four, nom_contact, num_tel_contact)
livraisons( num_livr, date_livr, nom_livr, #num_four (non vide))

Traduction E/A $\rightarrow$ rel. : règle 3

Règle 3 : une association binaire de type n : m devient une table supplémentaire (table de jointure, table d'association ...) dont la clé primaire est composée de deux clés étrangères (qui référencent les deux clés primaires des deux tables en association). Les attributs de l'association deviennent des colonnes de cette nouvelle table.

Traduction E/A $\rightarrow$ rel. : règle 4

Règle 4 : une association binaire de type 1 : 1 est traduite comme une association binaire de type 1 : n, sauf que la clé étrangère se voit imposer une contrainte d'unicité en plus d'une éventuelle contrainte de non vacuité (cette contrainte d'unicité impose à la colonne correspondante de ne prendre que des valeurs distinctes).

Traduction E/A $\rightarrow$ rel. : règle 5

Règle 5 : une association non binaire est traduite par une table supplémentaire dont la clé primaire est composée d'autant de clés étrangères que d'entités en association. Les attributs de l'association deviennent des colonnes de cette nouvelle table.

Formes normales, le retour

Introduction

Objectifs :

• éviter la redondance des données ;
• éviter les anomalies transactionnelles ;

Il existe six formes normales graduelles, visant chacune une robustesse accrue et des économies de mémoire ; en pratique on se contente souvent des trois premières.

En général plus une table est normalisée, moins les performances (en temps d'exécution) sont bonnes. C'est pourquoi on préfère parfois dénormaliser a posteriori certaines parties d'une base pour améliorer ces performances.

Première forme normale

Une relation (schéma de table) est en 1NF si aucun de ses attributs ne peut prendre de valeur qui soit elle-même un ensemble.

Cependant, PostGreSQL (et d'autres SGBDR) permet la création de types composés, pouvant être utilisés comme attributs.

Exemples :

Seconde forme normale - exemple

Troisième forme normale - exemple

CREATE TABLE marins(nom, date_de_naissance, PRIMARY KEY(nom));
CREATE TABLE embarquements(marin, date_embarquement, bateau, tonnage)
PRIMARY KEY(marin, date_embarquement);

Est-ce en 2NF ? En 3NF ? Si non, quel est le problème ?

CREATE TABLE marins(nom, date_de_naissance, PRIMARY KEY(nom));
CREATE TABLE embarquements(marin, date_embarquement, bateau)
PRIMARY KEY(marin, date_embarquement);
CREATE TABLE bateaux(bateau_ID, tonnage, PRIMARY KEY(bateau_ID));

BCNF - définition équivalente

Une relation $R$ est en forme normale de Boyce-Codd (BCNF) si et seulement si pour chaque dépendance fonctionnelle non triviale $A \rightarrow B$ qui doit être vérifiée par $R$, $A$ est une surclé de $R$.

DF triviale : $A \rightarrow B$ avec $B \subseteq A$
Surclé : ensemble d'attributs noté $X$ tel que $\forall Y \in R, X \rightarrow Y$

Une relation en BCNF peut comporter des redondances

• Pas de DF
• Clé = {Course, Book, Lecturer}
• 3NF, et BCNF

Quatrième forme normale (4NF)

Une relation est en 4NF si les seules DM (dépendances multivariées) sont celles dans lesquelles une clé multidétermine un attribut.

Définition : $X$ "multidétermine" $Y$ si pour chaque valeur (n-uplet) de $X$ l'ensemble des valeurs (n-uplets) possibles pour $Y$ est restreint, et que cet ensemble est indépendant des autres attributs.

Remarque : une DF est un cas particulier de DM.

Cinquième forme normale (5NF)

Une dépendance de jointure (DJ) sur une relation R est la donnée d'un n-uplet $X_1,..., X_n$ de sous-ensembles d'attributs de R vérifiant $$R = \pi_{X_1}(R) \bowtie \pi_{X_2}(R) \bowtie \dots \bowtie \pi_{X_n}(R)$$

Définition : une relation R est en 5NF si toutes les DJ sont impliquées par les clés.

Passage au modèle physique

Définition

Un modèle physique de données est l'implémentation particulière du modèle logique de données par un logiciel.

La traduction d'un MLD conduit à un MPD qui précise notamment le stockage de chaque donnée à travers son type et sa taille (en octets).

Si nécessaire, cette traduction est l'occasion d'un certain nombre de libertés prises par rapport aux règles de normalisation afin d'optimiser les performances du système.

Optimisations

Possibilités :

• ajout de colonnes calculées ou de certaines redondances pour éviter des jointures coûteuses ;
• suppression des contraintes d'unicité, de non vacuité ou encore de clé étrangère (attention : doit alors être géré autrement).

Normaliser or not normaliser ?

Exemple clients/achats/produits (association n : m).

Obtenir la liste des achats avec les nom des clients et produits nécessite deux jointures : opération coûteuse.

Attention cependant : la mise à jour des noms demandera plus de travail sous cette forme (maintenance plus complexe).

Bilan

Un schéma E/A se traduit quasi automatiquement en un schéma relationnel, décrivant directement la structure des tables.

Il peut être nécessaire de dénormaliser certaines parties de la base si l'on constate des problèmes de performance.

Note 1 : un certain nombre de situations ne sont pas modélisables par la méthode décrite jusqu'alors. Des informations complémentaires intéressantes se trouvent par exemple sur ce fil, dans la FAQ Merise MCD ou encore dans la FAQ Merise MLD.

Note 2 : face à une base de données existante mais mal conçue, on peut suivre les étapes inverses (normalisation, obtention et re-conception du schéma E/A).

Avant d'interroger une base : création

DDL : définir la structure des tables

Création et suppression de tables

CREATE TABLE my_first_table (
	first_column text,
	second_column integer );

DROP TABLE my_first_table;

CREATE TABLE Livres (
	Auteur varchar(64),
	Titre text,
	Date date,
	ISBN integer );
DROP TABLE Livres;

Modification des tables

column-def :

Exemple :

ALTER TABLE Livres RENAME TO Bouquins;
ALTER TABLE Bouquins ADD COLUMN nb_pages integer;

Attention: pas de "DROP COLUMN"

Workaround (from sqlite3 FAQ)

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t_backup(Auteur,Titre,ISBN);
INSERT INTO t_backup SELECT Auteur,Titre,ISBN FROM Bouquins;
DROP TABLE Bouquins;
CREATE TABLE Livres(Auteur,Titre,ISBN DEFAULT 0);
INSERT INTO Livres SELECT * FROM t_backup;
DROP TABLE t_backup;
COMMIT;

Types de données dans SQLite

Most SQL database engines uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite,
the datatype of a value is associated with the value itself,
not with its container.

INSERT INTO Livres (ISBN) VALUES (42); -- Ok : type integer
INSERT INTO Livres (ISBN) VALUES ("13"); -- Ok : converti
INSERT INTO Livres (ISBN) VALUES ("hello world!"); -- Ok : text

Storage Classes and Datatypes

Each stored value has one of the following storage classes:

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer.
• REAL. The value is a floating point value.
• TEXT. The value is a text string.
• BLOB. The value is a (binary) blob of data.

A storage class represents several datatypes

DML : altérer le contenu des tables

Insert : exemples

-- Indiquer seulement certains champs
INSERT INTO Livres (Auteur) VALUES ('Fred Vargas');
INSERT INTO Livres (Auteur,Titre) VALUES
	('Alain Damasio', 'La Horde du Contrevent');

-- Plusieurs lignes en une commande
INSERT INTO Livres (Auteur, Titre) VALUES
	('Haruki Murakami','La Course au mouton sauvage')
	('Haruki Murakami','La Fin des temps')
	('Haruki Murakami','La Ballade de l''impossible')

SELECT * FROM Livres;
Fred Vargas||
Alain Damasio|La Horde du Contrevent|0
Haruki Murakami|La Course au mouton sauvage|0
Haruki Murakami|La Fin des temps|0
Haruki Murakami|La Ballade de l'impossible|0

Importer depuis un fichier CSV

.mode csv
.import my_file.csv my_table
-- Vérification
.schema my_table
SELECT * FROM my_table;

Note : If the table already exists, the sqlite3 tool uses all the rows, including the first row, in the CSV file as the actual data to import. Therefore, you should delete the first row of the CSV file.

Modifier des lignes

Exemples

UPDATE Livres SET ISBN = 32 WHERE ISBN IS NULL;
UPDATE Livres SET ISBN = 42 WHERE Auteur = 'Alain Damasio';

SELECT * FROM Livres;
Fred Vargas||32
Alain Damasio|La Horde du Contrevent|42
...

Supprimer des lignes

Exemples

DELETE FROM Livres WHERE ISBN = 32;
DELETE FROM Livres WHERE Titre = 'La Fin des temps';

SELECT * FROM Livres;
Alain Damasio|La Horde du Contrevent|42
Haruki Murakami|La Course au mouton sauvage|0
Haruki Murakami|La Ballade de l'impossible|0

Note : DELETE FROM Livres; supprime les données mais pas la table

Propriétés ACID

Un SGBD est dit transactionnel s'il vérifie les propriétés

• d'Atomicité : une transaction se fait complètement ou pas du tout ;
• de Cohérence : une transaction amène le système d'un état valide à un autre ;
• d'Isolation : une transaction s'exécute comme si elle était seule sur le système ;
• de Durabilité : une fois confirmée, une transaction est enregistrée de façon permanente.

(Contre-)Exemple imaginaire avec la table

CREATE TABLE acidtest (
	A INTEGER, B INTEGER CHECK (A + B = 100));

$T_1$ transfert 10 de A à B, et $T_2$ transfert 5 de B à A.