Création d'une base de données
Pour créer une nouvelle base de données, il faut le faire avec l'utilitaire sqlite3.exe.
Dans l'invite de commande, il faut ce positionner dans le dossier que la base de données doit exister.
Ensuite, il faut créer la base de données vide avec le paramètre VACUUM;
Voici un exemple pour créer la BD bd_cours5.sqlite dans le dossier C:\Projets\3N3\Cours5
C:\Projets\3N3\cours5> sqlite3 bd_cours5.sqlite "VACUUM;"
VS Code
Il est possible d'utiliser VS Code pour effectuer des requêtes et des commandes avec SQLite.
Cet outil est plus convivial pour la création de scripts que sqlite3 ou SQLite Studio.
Voici les liens de téléchargement si ce n'est pas déjà fait.
VS Code : https://code.visualstudio.com/
Assurez-vous d'ajouter l'extension SQLite : https://marketplace.visualstudio.com/items?itemName=alexcvzz.vscode-sqlite
Dossier de travail
Sélectionnez le menu Fichier -> Ouvrir le dossier...
Sélectionnez le dossier qui contient la base de données.
Fichier settings.json
Afin d'avoir en tout temps la vérification des contraintes de clés étrangères, il faut ajouter un fichier de configuration.
Cette étape se fait uniquement la première fois.
Créez le dossier .vscode à l'intérieur du dossier du projet.
Dans le dossier, créez le fichier settings.json.
{ "sqlite.setupDatabase": { "./bd_cours5.sqlite": { "sql": ["PRAGMA foreign_keys = ON;"] } }, "sqlite.logLevel": "ERROR"}
Il est important de remplacer le "./bd_cours5.sqlite" par le nom de votre base de données.
Ouverture de la base de données
Il faut ouvrir la base de données à partir de l'interface de commandes. Pour y accéder rapidement CTRL+MAJ+P.
Dans la zone de texte, inscrivez sqlite et sélectionnez SQLite: Open Database.
Sélectionnez l'élément qui correspond au fichier de votre base de données.
Dans la section Explorateur à gauche (CTRL+MAJ+E si non visible), ouvrez la section SQLITE EXPLORER
Effectuez un clic droit sur la base de données et sélectionnez l'option New Query pour avoir une fenêtre de requête.
Pour exécuter une requête ou une commande.
- Tout le document -> Clic-droit -> Run Query ou CTRL+MAJ+Q
- Uniquement la sélection -> Clic-droit sur la sélection -> Run Selected Query.
Le résultat sera dans la fenêtre de droite.
Création des tables - CREATE TABLE
Pour créer une table, SQL utilise la commande CREATE TABLE
Pour plus d'information : http://www.sqlite.org/lang_createtable.html
Convention pour les noms
Les conventions suivantes seront utilisées pour nommer les tables et les champs.
Il est important de respecter ces conventions, car elles seront utilisées lors des évaluations.
- Le nom des tables est au pluriel et commence par une majuscule (
Clientset nonClient) - Le nom des champs est au singulier et utilise la notation PascalCase.
- Le nom du champ doit être explicite. N'hésitez pas à utiliser un nom long plutôt qu'une abréviation qui peut porter à confusion.
- Il faut être consistant dans les noms. Par exemple, un champ CodePostal dans une table et CP dans une autre.
Ces règles sont pour ce cours. En entreprise ou dans les autres cours, il est possible que les conventions soient différentes.
Il est aussi fréquent pour de grosses bases de données d'utiliser un dictionnaire de données. Il est alors possible d'avoir des noms qui ne sont que des clés dans ce dictionnaire.
Création avec clé primaire - PRIMARY KEY
Voici un exemple pour créer une table qui va contenir l'information d'une voiture.
CREATE TABLE Voitures( VoitureId INTEGER PRIMARY KEY, Nom TEXT, Prix INTEGER, Couleur TEXT);
Il faut débuter avec le nom du champ, ensuite le type de données.
Lorsqu'il y a une seule clé primaire, il est possible de l'indiquer directement dans la déclaration du champ, après le type de données.
Lorsque la table utilise une clé primaire composée, il faut ajouter la contrainte PRIMARY KEY à la fin de la table et indiquer tous les champs qui correspondent à la clé primaire.
Dans la table ci-dessous, la clé primaire est composé des champs Annee, Marque et Modele.
CREATE TABLE Voitures2( Annee TEXT, Marque TEXT, Modele TEXT, Prix INTEGER, Couleur TEXT, PRIMARY KEY (Annee, Marque, Modele));
Champ obligatoire ou non obligatoire - NULL et NOT NULL
Dans l'exemple ci-dessous, les champs VoitureId et Couleur sont obligatoires. Les champs Nom et Prix ne sont pas obligatoires.
CREATE TABLE Voitures3( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NULL, Prix INTEGER, Couleur TEXT NOT NULL);
Il faut ajouter NOT NULL après le type lorsque c'est obligatoire.
Pour un champ non obligatoire, il est possible de mettre NULL après le type ou de ne rien mettre. L'important est d'être constant dans nos scripts de création de bases de données. L'exemple devrait être l'une des 2 options ci-dessous.
--ExpliciteCREATE TABLE Voitures4( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NULL, Prix INTEGER NULL, Couleur TEXT NOT NULL);--ImpliciteCREATE TABLE Voitures5( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT, Prix INTEGER, Couleur TEXT NOT NULL);
Valeur par défaut - DEFAULT
Pour indiquer une valeur par défaut, il faut mettre le mot-clé DEFAULT après la contrainte de champ obligatoire.
La valeur par défaut est inscrite après le mot-clé. Il est important d'inscrire la valeur dans le bon format.
Dans l'exemple ci-dessous, la valeur par défaut du champ Prix est 1000 et pour le champ Couleur est blanc. La chaine de caractère est entre des guillemets.
CREATE TABLE Voitures6( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NULL, Prix INTEGER NULL DEFAULT 1000, Couleur TEXT NOT NULL DEFAULT 'blanc');
Champ unique - UNIQUE
Il faut ajouter après la contrainte de champ obligatoire le mot-clé UNIQUE pour indiquer que le champ est unique.
Dans l'exemple ci-dessous, le champ NoPermis doit être unique.
CREATE TABLE Proprietaires( ProprietaireId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NOT NULL, Prenom TEXT NOT NULL, NoPermis TEXT NOT NULL UNIQUE);
Il est possible de créer une contrainte de champ unique composé. Le fonctionnement est similaire à une clé primaire composée. C'est la combinaison des champs qui doivent respecter l'unicité et non le champ individuel.
Dans l'exemple ci-dessous, il n'est pas possible de créer 2 fois François St-Hilaire, mais un François Morin et un Sylvain St-Hilaire sera possible.
CREATE TABLE Proprietaires2( ProprietaireId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NOT NULL, Prenom TEXT NOT NULL, NoPermis TEXT NOT NULL, UNIQUE (Nom, Prenom) );INSERT INTO Proprietaires2(ProprietaireId, Nom, Prenom, NoPermis)VALUES (1, 'St-Hilaire', 'François', 'STHF123'); -- FonctionneINSERT INTO Proprietaires2(ProprietaireId, Nom, Prenom, NoPermis)VALUES (2, 'Morin', 'François', 'STHF456'); -- FonctionneINSERT INTO Proprietaires2(ProprietaireId, Nom, Prenom, NoPermis)VALUES (3, 'St-Hilaire', 'Sylvain', 'STHF456'); -- FonctionneINSERT INTO Proprietaires2(ProprietaireId, Nom, Prenom, NoPermis)VALUES (4, 'St-Hilaire', 'François', 'STHF456'); -- Erreur
Contrainte de données stricte - STRICT
Dans le cours sur les commandes d'insertion, il a été démontré qu'il est possible d'insérer une chaine de caractères dans un champ de type INTERGER.
Voici un exemple avec la table Voitures.
INSERT INTO Voitures(VoitureId, Nom, Prix, Couleur)VALUES (1, 'Audi', 'Trop cher', 'bleu');
Le résultat du SELECT.
VoitureId Nom Prix Couleur --------- ---- --------- ------- 1 Audi Trop cher bleu
Par contre, ce n'est pas très pratique. Le mot-clé STRICT à la fin de la création de la table permet d'éviter ceci.
CREATE TABLE Voitures7( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NULL, Prix INTEGER NULL, Couleur TEXT NULL) STRICT;
Le message d'erreur sera celui-ci pour cette tentative d'insertion.
INSERT INTO Voitures7(VoitureId, Nom, Prix, Couleur)VALUES (1, 'Audi', 'Trop cher', 'bleu');-- Runtime error near line 4: cannot store TEXT value in INTEGER column Voitures7.Prix (19)
IMPORTANT : La contrainte
STRICTn'est pas reconnue par SQLiteStudio et la base de données n'est pas utilisable dans ce logiciel lorsqu'il y a une tableSTRICT.
Contraintes spécifiques sur les données
Il est possible d'ajouter une contrainte spécifique sur une colonne avec le mot clé CHECK. Il faut ajouter la condition booléenne entre parenthèse. Exemple CHECK (Nom <> ''). Cette contrainte est intéressante pour s'assurer que la chaîne de caractères vide ne soit pas possible. Car celle-ci est possible avec NOT NULL sans ajouter un CHECK.
Toutes les colonnes de la table sont accessibles. Ce qui permet de s'assurer que deux champs n'ont pas la même valeur pour le même enregistrement CHECK (Prenom <> Nom).
Une autre contrainte intéressante est de limiter un champs INTEGER à 0 ou 1 pour faire l'équivalent d'un booléen.
CHECK(EstSport IN(1,0)).
Au final CHECK permet de faire toute sorte de contraintes qui ne sont pas gérées par défaut, mais qui s'assure de l'intégrité des données.
Voici un exemple de CHECK dans un script de création de table.
CREATE TABLE Voitures19( VoitureId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NULL CHECK (Nom <> ''), Prix INTEGER NULL CHECK (Prix >= 0), Couleur TEXT NULL) STRICT;
Si l'on teste un insertion dans la table avec une valeur invalide, un message d'erreur apparraîtra.
INSERT INTO Voitures19 (Nom, Prix)VALUES (NULL, -3);--Erreur pendant l’exécution de la requête sur la base de données « cours3 » : CHECK constraint failed: Prix >= 0
Clé étrangère - FOREIGN KEY
Pour être en mesure d'indiquer les clés étrangères d'une table, il faut utiliser le mot-clé FOREIGN KEY.
L'ordre de création est important. Les tables parents doivent être créées avant les tables enfants.
Dans l'exemple ci-dessous, il faut indiquer le pays de provenance de l'étudiant. La table Pays doit être crée en premier, car elle est une table parent par rapport à la table Etudiants.
CREATE TABLE Pays( PaysId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NOT NULL) STRICT;CREATE TABLE Etudiants( EtudiantId INTEGER NOT NULL PRIMARY KEY, Prenom TEXT NOT NULL, Nom TEXT NOT NULL, DA TEXT NOT NULL UNIQUE, PaysId INTEGER NOT NULL, FOREIGN KEY (PaysId) REFERENCES Pays(PaysId)) STRICT;
Il est possible de mettre des règles comportementales sur les clés étrangères. Ce sujet sera présenté dans le prochain cours.
Mélange de contraintes
Voici une table qui a plus d'une contrainte par champ.
CREATE TABLE Voitures8( VoitureId INTEGER NOT NULL PRIMARY KEY DEFAULT 1, Nom TEXT NOT NULL UNIQUE DEFAULT 'Fiat', Prix INTEGER NOT NULL DEFAULT 1000 UNIQUE) STRICT;
Les champs Nom et Prix ont une contrainte d'unicité et de valeur par défaut. L'ordre de déclaration des contraintes n'a pas d'importance. Par contre, il est recommandé d'être uniforme.
La création ci-dessous serait plus appropriée.
CREATE TABLE Voitures9( VoitureId INTEGER NOT NULL PRIMARY KEY DEFAULT 1, Nom TEXT NOT NULL UNIQUE DEFAULT 'Fiat', Prix INTEGER NOT NULL UNIQUE DEFAULT 1000 ) STRICT;-- OUCREATE TABLE Voitures10( VoitureId INTEGER NOT NULL PRIMARY KEY DEFAULT 1, Nom TEXT NOT NULL DEFAULT 'Fiat' UNIQUE, Prix INTEGER NOT NULL DEFAULT 1000 UNIQUE) STRICT;
Exercices
Vous devez créer cette base de données et exécuter le script dans VS Code.
Voici le diagramme.
Solutions du script de création des tables pour cette base de données
CREATE TABLE Pays( Code TEXT NOT NULL PRIMARY KEY, Nom TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE Acteurs( ActeurId INTEGER NOT NULL PRIMARY KEY, Nom TEXT NOT NULL, Prenom TEXT NOT NULL, NationalitePaysCode TEXT NOT NULL, Biographie TEXT, UrlIMDB TEXT NOT NULL UNIQUE, FOREIGN KEY(NationalitePaysCode) REFERENCES Pays(Code) ) STRICT;CREATE TABLE Productions( ProductionId INTEGER NOT NULL PRIMARY KEY, NomCompagnie TEXT NOT NULL, AnneeFondation INTEGER, SiegeSocialPaysCode TEXT NOT NULL, FOREIGN KEY(SiegeSocialPaysCode) REFERENCES Pays(Code)) STRICT;CREATE TABLE Films( FilmId INTEGER NOT NULL PRIMARY KEY, Titre TEXT NOT NULL, Description TEXT NOT NULL DEFAULT 'Description non disponible', ProductionId INTEGER NOT NULL, DateSortie TEXT, UrlIMDB TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE FilmsActeurs( FilmId INTEGER NOT NULL, ActeurId INTEGER NOT NULL, NomPersonnage TEXT NOT NULL, PRIMARY KEY (FilmId, ActeurId), FOREIGN KEY(FilmId) REFERENCES Films(FilmId), FOREIGN KEY(ActeurId) REFERENCES Acteurs(ActeurId)) STRICT;
Suppression d'une table - DROP TABLE
La commande pour détruire une table est : DROP TABLE.
http://www.sqlite.org/lang_droptable.html
La clause IF EXISTS permet de ne pas afficher un message d'erreur si jamais on essaie d'effacer une table qui n'existe pas. Cette clause est très utile dans les scripts de création de bases de données.
L'ordre de suppression des tables est important, car les tables parents ne peuvent pas être supprimées s'il existe toujours des tables enfants qui les utilisent avec une clé étrangère.
DROP TABLE Voitures;
Diagramme d'entité-association
Le modèle entité-association (le terme entité-relation est une traduction erronée largement répandue), ou diagramme entité-association ou (en anglais entity-relationship diagram, abrégé en ERD), est un modèle de données ou diagramme pour des descriptions de haut niveau de modèles conceptuels de données.
Il fournit une description graphique pour représenter de tels modèles de données sous la forme de diagrammes contenant des entités et des associations. De tels modèles sont utilisés dans les phases amont de conception des systèmes informatiques. (http://fr.wikipedia.org/wiki/ModC3%A8le_entit%C3%A9-association)
NOTE : il existe plusieurs méthodes de représentation d'un DEA.
Voir http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model pour quelques exemples.
Diagrammes
Entités
Une entité est un objet, un événement, un lieu, une personne. Elle doit être identifiable sans ambiguïté.
Exemple : le film « Avatar », l'acteur « Woody Allen »...
Une classe d'entité est un regroupement d'entités de même nature.
Exemple : les films, les acteurs.
Une entité est donc une valeur particulière d'une classe d'entité.
Dans un DEA, ce sont les classes d'entités qui sont représentées, mais par convention, on parle d'entités.
Associations
Une association est un lien entre 2 ou plusieurs entités.
Exemple : Woody Allen a joué dans le film Woody et les robots
Notez qu'il y a souvent 2 façons de décrire la relation. On peut dire qu'un acteur a joué dans un film, ou qu'un film a des acteurs. Il arrive fréquemment qu'il soit difficile de mettre autre chose que "a" d'un côté de la relation, mais qu'il soit facile de trouver un verbe dans l'autre sens.
On favorise le verbe plus expressif. Mais dans certains cas, il est possible d'avoir un verbe des 2 sens: un étudiant est assis sur une chaise, et une chaise supporte un étudiant ;
ou encore un étudiant dort en écoutant écoute le professeur,
et le professeur** enseigne à** l'étudiant.
Propriétés/Attributs
Un attribut est une donnée élémentaire d'une entité.
Exemple : le nom, prénom, pour l'entité Acteur
Une relation peut aussi avoir des attributs.
Exemple : le rôle d'un acteur pour un film dans la relation « Joue ».
Identifiants (clé primaire et étrangère)
Il doit être possible d'identifier de façon unique chaque occurrence d'une entité. Pour se faire, l'entité doit avoir une clé primaire (PK = Primary Key) permettant de la distinguer des autres.
Cet identifiant peut être constitué d'une ou de plusieurs propriétés. On privilégie l'identifiant le plus court et le plus naturel. Si aucune propriété ne peut jouer ce rôle, on peut créer un identifiant artificiel (un id).
Pour une association, l'identifiant est obtenu en juxtaposant les identifiants des entités qui participent à la liaison.
Les attributs faisant partie de l'identifiant sont soulignés ou en gras ou un symbole les identifie (une clé, PK...).
Dans un DEA de niveau physique, lorsqu'une entité réfère à la clé primaire d'une autre entité, cette référence est nommée la clé étrangère (FK = Foreign Key)
Cardinalité des associations
Le rôle d'une association est défini par deux nombres : le nombre de fois minimum et maximum qu'une entité participe à une association (min, max).
Les valeurs possibles sont : (0,1), (1,1), (0,n), (1,n).
Il y a plusieurs techniques utilisées pour représenter cette cardinalité. Nous utiliserons la technique « crow's foot » (pattes d'oie, en référence à la forme du diagramme)
Lorsqu'on regarde la relation en entier, on regarde seulement le maximum à chaque bout. On parle de relation 1 à 1 (1:1), 1 à plusieurs (1:N), ou plusieurs à plusieurs (M:N)
Exemples :
Un étudiant est assis sur une chaise. Il ne peut y avoir 2 étudiants sur 1 chaise, et un étudiant ne peut être assis sur 2 chaises à la fois. Mais un étudiant peut ne pas être sur une chaise, et une chaise peut n'avoir personne d'assis sur elle. C'est donc une relation « 0 ou 1 » aux deux extrémités de la relation, elle sera donc 1:1.
Un homme peut avoir des enfants. Il peut avoir 0 ou plusieurs enfants, mais chaque enfant a assurément un père, en n'en a qu'un. C'est donc une relation «0 ou n » du côté de l'homme, et « 1 et seulement 1 » du côté enfant. C'est donc une relation 1:N.
Un professeur donne des cours. Il donne 0 ou plusieurs cours, et un cours est donné par 0 ou plusieurs professeurs. C'est donc une relation « 0 ou plusieurs » du côté du professeur, et la même chose du côté du cours. La relation est donc M:N (plusieurs à plusieurs).
Il est à noter qu'une telle relation devra être scindée en deux relations 1:M dans le modèle physique. Les outils graphiques ne permettront souvent pas d'avoir une M:N Elle sera scindée automatiquement selon les clés primaires des 2 entités.
DEA logique et physique
Un DEA logique affiche les entités et leurs relations sans nécessairement représenter les tables pivots (plusieurs à plusieurs).
Dans l'exemple ci-dessous, un étudiant assiste à des cours. Et un cours a des étudiants. Il s'agit d'une relation plusieurs à plusieurs, mais sans la table pivot.
Un DEA physique affiche toutes les entités, c'est-à-dire les tables, de la base de données.
Dans l'exemple ci-dessous, il s'agit de la relation entre les entités étudiant et cours, mais la table pivot pour représenter la relation plusieurs à plusieurs est présente. Dans la base de données, il y aura ces 3 tables.
Dans ce cours, le DEA physique sera utilisé.
Exemples d'une base de données
Exercices
Version originale par François St-Hilaire
Produit à partir des notes de Benoit Desrosiers
Mise à jour 2023 par Pierre-Luc Boulanger