2020-10-06: Création
Source: Modélisez et implémentez une base de données relationnelle avec UML
Voici quelques notes sur le jargon de la base de données:
Les noms sont l'appellation des éléments constitutifs des systèmes complexes. Si les noms ne sont pas appropriés, le système peut rapidement être inintelligible.
Si les noms sont clairs, bien choisis et partagés par tous les membres de l'équipe projet, alors la communication sera bien plus efficace !
Il peut aussi critiquer (objectivement) le fait qu'un groupe est composé des mêmes musiciens tout au long d'une même tournée.
Posez des limites avec les multiplicités
En UML, la relation entre deux classes s'appelle une association.
Pour modéliser une association entre deux classes, on fait un trait entre elles.
Il faut que la clé primaire soit unique, exemple dans le modèle physique de données (MPD):
Attribut unique:
TABLE personne numeroSecuriteSocials: VARCHAR [PK] <== car numéro UNIQUE nom: VARCHAR prenom: VARCHAR
Clé primaire composite: 2 clés
TABLE commune code_postal: VARCHAR [PK] <== UNIQUE par ces 2 clés nom: VARCHAR [PK] <== superficie: INTEGER nbr_habitant: INTEGER
Clé primaire créée:
TABLE produit id: INTEGER [PK] <== clé primaire auto-incrémentée nom: VARCHAR prix_unitaire: DECIMAL
L'association un à un (one-to-one) est une association qui possède 1 comme cardinalité maximum de part et d'autre de celle-ci.
UML
L'association un à plusieurs (one-to-many) ou plusieurs à un (many-to-one) est une association qui possède 1 comme cardinalité maximum d'un côté et une cardinalité maximum supérieure à 1 (cardinalité multiple) de l'autre côté de celle-ci.
UML
MDP
On doit créer la clé étrangère dans la table correspondant à la cardinalité maximum supérieure à 1 (cardinalité multiple).
Il est ainsi possible de référencer plusieurs fois le même journal dans des articles différents (cardinalité *), et un article ne peut être lié qu'à un seul journal au maximum (cardinalité 0..1)
L'association plusieurs à plusieurs (many-to-many) est une association qui possède une cardinalité maximum supérieure à 1 (cardinalité multiple) de part et d'autre de celle-ci.
UML
MDP
Il faut ici créer une Table intermédiaire pour matérialiser les relations:
Tuples
En créant la classe d'association Concert, on peux ajouter, un attribut date qui spécifie la date du concert.
UML
Une classe d'association se traduit dans le MPD comme une association plusieurs à plusieurs (many-to-many) : En créant une table du nom de la classe d'association avec une clé primaire composée et contenant les attributs de la classe d'association.
MDP
Il y a de nombreux cas où les classes d'association sont utiles, par exemple enregistrer le numéro de siège dans une relation Passager/Vol, etc.
Pour être en première forme normale (1FN), il faut que chaque attribut soit atomique. En d'autres termes, aucun attribut ne doit être multivalué (liste de valeurs) ou composé (si on le décompose, on obtient des informations supplémentaires).
Exemple:
Une liste de contact avec un champ e-mail.
Cas où la 1ere forme normale ne serai pas respectée:
1ere solution (pas conseillée):
2ème solution (recomandée):
La deuxième forme normale ne concerne que les tables avec une clé primaire composite.
Pour être en deuxième forme normale:
Exemple:
On ajoute une information donnant la difficulté de la figure:
La difficulté d'une figure ne dépend que de la figure et non du skater.
L'attribut difficulte ne dépend que d'une partie de la clé primaire (figure) et non de la clé primaire complète (skater, figure) : La 2FN n'est pas respecté.
⇒ La solution consiste donc à isoler les attributs concernés dans des tables dédiées. On créé une table figure et déplacer la colonne difficulte dans cette nouvelle table :
Dans la table resultat, la colonne figure devient clé étrangère :
UML
MDP
La troisième forme normale ressemble à la deuxième (2FN) mais concerne la dépendance entre attributs non clés.
Pour être en troisième forme normale :
Exemple: La Table “personne”:
Cette table ne respecte pas la 3FN car l'attribut sexe peut être déduit de l'attribut non clé primaire civilite.
Afin de résoudre ce problème, comme pour la 2FN, il convient de créer une nouvelle table civilite et d'y déplacer l'attribut sexe :
MPD:
voici le nouveau contenu des tables personne et civilite :
Tuples:
Autre exmple qui ne respecte pas la 3FN:
La colonne total qui peut être calculée avec une opération mathématique simple à partir des colonnes quantite et prix_unitaire (total = quantite x prix_unitaire).
Sa valeur ne dépend pas de la clé primaire.
La solution consiste à simplement supprimer la colonne total.
L'agrégation, c'est une classe qui regroupe des objets.
Il est représenté en UML par un losange vide.
Exemple:
Une agrégation sera traduite dans le MPD comme une association classique de type un à plusieurs ou plusieurs à plusieurs. Il n'y a pas de formalisme particulier.
Une composition est une sorte d'agrégation plus « forte ».
Elle s'emploie lorsque :
Exemples de composition :
Une composition est matérialisée par un losange plein du côté de la classe jouant le rôle de composite.
De même que pour l'agrégation, la composition sera traduite dans le MPD comme une association classique de type un à plusieurs. Il n'y a pas de formalisme particulier.
En approche orientée objet, il est possible de généraliser des comportements grâce à l'héritage.
Exemple:
Le marteau et la clé à pipe sont des outils. On dit alors que les classes Marteau et CleAPipe héritent de la classe Outil.
Une instance de Marteau ou de CleAPipe sont indirectement des instances d'Outil :
Grâce à l'héritage, on va pouvoir :
Ainsi, il m'est possible de modéliser facilement le fait que :
Il est également possible de préciser que nous ne pouvons pas instancier directement un outil, mais seulement ses sous-classes, en rendant la classe Outil abstraite. Cela est représenté sur le diagramme de classes en mettant le nom de la classe en italique :
La notion d'héritage n'existe pas en tant que telle dans le modèle relationnel, mais il y a plusieurs moyens de traduire l'héritage dans le MPD.
Méthode conseillée:
Une association est dite réflexive quand les deux extrémités de celle-ci pointent sur la même classe.
On peut modéliser une arborescence de catégories comme celle-ci :
MPD:
Documentations complémentaires:
PostgreSQL
listen_addresses = 'localhost' port = 5432
## Permettre la connexion de tous les utilisateurs à toutes les bases ## via le réseau sur localhost ## en utilisant un mot de passe (le mot de passe est chiffré en MD5) # TYPE DATABASE USER ADDRESS METHOD host all all localhost md5
SQL Power Architect
SQuirreL SQL
Les index sont utilisé pour les recherche dans le base de données.
Un même index peut porter sur une ou plusieurs colonnes à la fois. Il est également possible de créer plusieurs index sur une même table.
En plus de servir de référencement, un index peut avoir des caractéristiques supplémentaires :
En général, les SGBD créent automatiquement des index pour les colonnes de clés primaire et certains le font pour les colonnes clés étrangères aussi.
Pour ajouter un index sur une table dans SQL Power Architect, il faut procéder ainsi :
Dans l'exemple ci-dessus, un index unique et clustered, nommé commune_idx a été ajouté à la table commune :
Les colonnes concernées par un index unique sont signalées par la notation [ AK ].
Exemple du MPD:
Pour générer le script:
Cliquer sur l'icône SQL
La documentation SQL: https://docs.postgresql.fr/9.6/sql-commands.html
CREATE TABLE public.historique_statut ( ticket_numero INTEGER NOT NULL, statut_id INTEGER NOT NULL, DATE TIMESTAMP NOT NULL, commentaire_id INTEGER, utilisateur_id INTEGER NOT NULL, CONSTRAINT historique_statut_pk PRIMARY KEY (ticket_numero, statut_id) );
Les clés étrangères sont ajoutées une fois que toutes les tables ont été créées.
ALTER TABLE public.commentaire ADD CONSTRAINT utilisateur_commentaire_fk FOREIGN KEY (utilisateur_id) REFERENCES public.utilisateur (id) ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE;
INSERT INTO public.utilisateur ( nom, prenom ) VALUES ( 'Dalton', 'Joe' );
SELECT * FROM public.projet;
Croisement de table: Clé étrangère
SELECT * FROM public.projet JOIN public.utilisateur ON projet.responsable_id = utilisateur.id;
UPDATE public.projet SET nom = 'System 6x7' WHERE id = 12;
UPDATE public.projet WHERE id = 12;
Il est possible de regrouper les instructions dans une transaction comme s'il s'agissait d'un seul bloc.
Une transaction est ouverte avec l'instruction BEGIN TRANSACTION; et elle est clôturée de deux manières :
Imaginez par exemple que vous vouliez supprimer un projet de la base. À cause des clés étrangères, vous allez aussi devoir supprimer les lignes des autres tables qui référencent ce projet (ticket, version) et ceci en cascade (tables bug, evolution, bug_version_affectee…).
BEGIN TRANSACTION; -- Ouverture de la transaction -- Projet à supprimer : projet.id = 18 DELETE FROM evolution WHERE EXISTS ( SELECT 1 FROM ticket WHERE ticket.numero = evolution.ticket_numero AND ticket.projet_id = 18 ); /* DELETE FROM bug_version_affectee ... DELETE FROM bug ... ... DELETE FROM ticket ... DELETE FROM version ... */ DELETE FROM projet WHERE id = 18; COMMIT; -- Validation de la transaction
owner = models.ForeignKey( User, default=1, on_delete=models.CASCADE)
Du coup, si l'on supprime le user cela impact owner (d'un article) qui sera supprimé en CASCADE.
INSERT INTO public.utilisateur -- INSERT INTO <schéma>.<table> ( id, nom, prenom -- <colonnes dont vous allez donner les valeurs> ) VALUES ( 4, 'Dalton', 'Joe' -- <valeurs des colonnes listées (dans le même ordre)> );
Ajout de plusieurs lignes en une requête SQL:
INSERT INTO public.utilisateur (id, nom, prenom) VALUES (4, 'Dalton', 'Joe'), -- ligne 1 (5, 'Dalton', 'William'), -- ligne 2 (6, 'Dalton', 'Jack'), -- ligne 3 (7, 'Dalton', 'Averell') -- ligne 4 ;
Exemple de définition de TABLE avec des valeurs par défaut:
CREATE TABLE tache ( id INTEGER NOT NULL DEFAULT NEXTVAL('tache_id_seq'), libelle VARCHAR(100) NOT NULL, priorite INTEGER NOT NULL DEFAULT 1, realisateur_id INTEGER, CONSTRAINT tache_pk PRIMARY KEY (id) );
SELECT currval('tache_id_seq'); -- Renvoie : 15
Le principe est de copier ou cloner une ligne existante : Ici je record 18.
INSERT INTO projet ( nom, date_creation, cloture, responsable_id ) SELECT nom, date_creation, cloture, responsable_id FROM projet WHERE id = 18;
Il est possible de modifier à la volée des données lors du clonage: Ici le nom.
INSERT INTO projet ( 'Dalton', date_creation, cloture, responsable_id ) SELECT nom, date_creation, cloture, responsable_id FROM projet WHERE id = 18;
Requête sur la même principe, avec un peu plus de complexité:
INSERT INTO projet ( nom, date_creation, cloture, responsable_id ) SELECT projet.nom || ' Perso ' || utilisateur.prenom, CURRENT_TIMESTAMP, FALSE, projet.responsable_id FROM projet JOIN utilisateur ON utilisateur.id = projet.responsable_id WHERE projet.id IN (18, 42);
Explications : Clonage des lignes 18 et 42
SELECT id, nom -- <liste des colonnes à afficher> FROM public.projet -- <schéma>.<table> ; /* Version allégée: */ SELECT * FROM utilisateur;
SELECT nom FROM utilisateur;
SELECT DISTINCT nom FROM utilisateur;
SELECT * FROM utilisateur WHERE nom = 'Dalton';
non gérés par Édouard Bracame, ET : clôturés OU créés entre le 10 et le 30 janvier 2017 (inclus)
SELECT * FROM projet WHERE (NOT responsable_id = 3) AND (cloture = TRUE OR (date_creation >= '2017-01-10' AND date_creation < '2017-01-31') ) ;
-- Afficher les informations des projets 18 et 42 SELECT * FROM projet WHERE id IN (18, 42); -- Afficher les informations des projets autres que 18 et 42 SELECT * FROM projet WHERE id NOT IN (18, 42);
SELECT nom || prenom FROM utilisateur WHERE LOWER(nom) LIKE `dal%`;
SELECT * FROM utilisateur WHERE LOWER(nom) LIKE '%a%' ORDER BY nom ASC, prenom DESC;
-- le croisement se fait sur projet.responsable_id = utilisateur.id SELECT * FROM projet WHERE responsable_id IN ( -- le croisement se fait ici SELECT utilisateur.id FROM utilisateur -- et là WHERE utilisateur.nom = 'Dalton' ) ;
-- le croisement se fait sur projet.responsable_id = utilisateur.id SELECT * FROM projet WHERE EXISTS ( SELECT 1 FROM utilisateur WHERE utilisateur.id = projet.responsable_id -- le croisement se fait ici AND utilisateur.nom = 'Dalton' ) ;
Afficher la liste des tickets qui ne sont pas de bugs.
SELECT * FROM ticket WHERE numero NOT IN ( -- j'utilise ici NOT IN SELECT ticket_numero FROM bug ) ;
SELECT * FROM projet WHERE NOT EXISTS ( -- j'utilise ici NOT EXISTS SELECT 1 FROM ticket WHERE ticket.projet_id = projet.id ) ;
Afficher la liste des projets dont le nom est le même que celui d'un autre projet.
SELECT * FROM projet AS p1 WHERE EXISTS ( SELECT 1 FROM projet AS p2 WHERE p2.nom = p1.nom AND p2.id <> p1.id );
Afficher la liste des noms de projets gérés par les « Dalton » avec les informations du responsable associé.
SELECT projet.nom AS "Nom du projet", utilisateur.* FROM projet INNER JOIN utilisateur ON utilisateur.id = projet.responsable_id WHERE utilisateur.nom = 'Dalton';
AS
est utilisé pour donner un nom plus parlant à une colonne.
La clause INNER JOIN
va réaliser un produit cartésien des tables. C'est-à-dire qu'à chaque ligne de la table projet
sera associée toutes les lignes de la table utilisateur
.
La clause ON
va permettre de ne conserver que les lignes qui remplissent les conditions définies après ON
. Ici, on ne conserve que les associations de lignes où la valeur de projet.responsable_id
correspond à celle de utilisateur.id
.
Sans le ON
, on aurait par exemple ceci :
Avec ON utilisateur.id = projet.responsable_id
, on obtient :
INNER
n'est pas obligatoire, vous n'êtes pas obligé·e de le mettre. Ces deux requêtes sont équivalentes :
SELECT * FROM projet INNER JOIN utilisateur ON utilisateur.id = projet.responsable_id; SELECT * FROM projet JOIN utilisateur ON utilisateur.id = projet.responsable_id;
table_a LEFT OUTER JOIN table_b ON
… : table_b
table_a RIGHT OUTER JOIN table_b ON
… : table_a
table_a FULL OUTER JOIN table_b ON
… table_a
où il n'y a aucune correspondance dans table_b
table_b
où il n'y a aucune correspondance dans table_a
SELECT projet.id, projet.nom, COUNT(ticket.numero) AS "Nombre ticket" -- comptage des tickets par groupe FROM projet JOIN ticket ON ticket.projet_id = projet.id WHERE projet.cloture = FALSE GROUP BY projet.id, projet.nom; -- grouper par projet.id, projet.nom
ajouter un filtre se basant sur le résultat d'un calcul sur le groupe :
SELECT projet.id, projet.nom, COUNT(ticket.numero) AS "Nombre ticket" FROM projet JOIN ticket ON ticket.projet_id = projet.id WHERE projet.cloture = FALSE GROUP BY projet.id, projet.nom HAVING COUNT(ticket.numero) > 10; -- Ne garder que les lignes avec count(...) > 10
Cette requête n'affiche dans le résultat que les projets qui ont plus de 10 tickets :
Modifier le titre du ticket numéro 64 en « Ajouter le nom du commercial sur l'affichage d'un contrat ».
UPDATE ticket SET titre = 'Ajouter le nom du commercial sur l''affichage d''un contrat' WHERE numero = 64;
On peut aller plus loin en se basant sur les données déjà présentes.
UPDATE evolution SET priorite = priorite + 1 WHERE priorite >= 2;
Passer en majuscules le nom et en minuscules le prénom des utilisateurs.
UPDATE utilisateur SET nom = UPPER(nom), prenom = LOWER(prenom) ;
On peut aussi utiliser une clause WHERE
plus complexe.
UPDATE evolution SET priorite = priorite + 2 WHERE EXISTS ( SELECT 1 FROM ticket WHERE ticket.numero = evolution.ticket_numero AND ticket.projet_id = 18 ) AND priorite >= 3;
La suppression de lignes se fait à l'aide d'une requête de type DELETE
.
Supprimer les lignes de la tablehistorique_statutconcernant
le projet d'id 18.
DELETE FROM historique_statut WHERE EXISTS ( SELECT 1 FROM ticket WHERE ticket.numero = historique_statut.ticket_numero AND ticket.projet_id = 18 );