Voici un exemple d'une petite base de données d'un site web de ventes ou échanges d'un produit. L'utilisateur s'inscrit et à partir de ce moment, il peut acheter ou vendre des produits ou une combinaison des deux. L'exemple est similaire à celle du cours 5, mais avec quelques valeurs de plus.
C'est un schéma spécifique pour SQLite
Création de la base de données
L'exercice est de créer la base de données avec les trois tables, mais d'utiliser les types de PostgreSQL au lieu des types de SQLite.
Voici quelques indications :
- Tous les champs textes ne permettent pas la chaîne de caractères vide.
- Tous les clés primaires doivent être générées automatiquement à l'insertion.
- Attention aux champs avec une chaîne de caractères (variables ou non). Plusieurs possibilités sont valides, mais seulement l'un des champs devrait être
TEXT. DateAchatdoit contenir seulement une date (donc, aucun temps)DerniereConnexiondoit supporter un format de ce genre2023-01-08 04:05:06- N'oubliez pas de tout transformer en nomenclature
snake_casepour améliorer la lecture
Commencez par faire le script pour la création de la base de données.
Solution
CREATE DATABASE app_troc WITH ENCODING = 'UTF8' LC_COLLATE = 'French_Canada.1252' LC_CTYPE = 'French_Canada.1252';
Maintenant, créer le script pour ajouter la table categories
Solution
CREATE TABLE categories( categorie_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, nom VARCHAR(100) NOT NULL CHECK (nom <> ''));
Créer le script pour ajouter la table utilisateurs
Solution
CREATE TABLE utilisateurs( utilisateur_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, prenom VARCHAR(100) NOT NULL CHECK (prenom <> ''), nom VARCHAR(100) NOT NULL CHECK (nom <> ''), courriel VARCHAR(255) NOT NULL UNIQUE CHECK (courriel <> ''), telephone VARCHAR(15) CHECK (telephone <> ''), est_actif BOOLEAN NOT NULL, derniere_connexion TIMESTAMP NOT NULL);
Généralement, un courriel contient au maximum 255 caractères. C'est souvent une bonne pratique de limiter certains champs pour éviter des abus comme pour Téléphone. Cependant, il ne faut pas exagérer et se retrouver avec des problèmes parce qu'on tronque des valeurs.
derniere_connexion est un cas typique d'utilisation d'un TIMESTAMP. C'est très similaire à une date de création ou de dernière modification.
Créer le script pour ajouter la table produits
Solution
CREATE TABLE produits( produit_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, titre VARCHAR(50) NOT NULL CHECK (titre <> ''), prix DECIMAL(9,2) CHECK (prix >= 0), date_achat DATE NULL, description TEXT NOT NULL CHECK (description <> ''), categorie_id INTEGER NOT NULL, vendeur_id INTEGER NOT NULL, acheteur_id INTEGER CHECK (acheteur_id <> vendeur_id), FOREIGN KEY (categorie_id) REFERENCES categories(categorie_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (vendeur_id) REFERENCES utilisateurs(utilisateur_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (acheteur_id) REFERENCES utilisateurs(utilisateur_id) ON DELETE RESTRICT ON UPDATE CASCADE);
Il n'y a pas vraiment de bonne réponse précise pour le prix. N'importe quoi qui supporte des nombres à virgule est valide. Description est le cas valide où l'on voudrait un vrai champs `TEXT libre.
Quelques insertions
Modifiez les insertions suivantes pour ajouter les colonnes manquantes pour l'utilisateur :
- est_actif
- derniere_connexion
INSERT INTO categories (nom)VALUES ('Jeux video'), ('Meuble'), ('Voiture'), ('Autres');INSERT INTO utilisateurs (prenom, nom, courriel, telephone)VALUES ('Bob', 'Gratton', 'bobgratton@cegeptest.org', '8885554321'), ('Gérard', 'D. Laflaque', 'gerardlaflaque@cegeptest.org', '8885551234'), ('Alain', 'Alain', 'alain_alain@cegeptest.org', NULL), ('Maryse', 'Boucher', 'mboucher@cegeptest.org', NULL), ('Sonia', 'Champoux', 'schampoux@cegeptest.org', NULL);
Consulter une des solutions possibles
INSERT INTO utilisateurs (prenom, nom, courriel, telephone, est_actif, derniere_connexion)VALUES ('Bob', 'Gratton', 'bobgratton@cegeptest.org', '8885554321', true, '2023-01-08 04:05:06'), ('Gérard', 'D. Laflaque', 'gerardlaflaque@cegeptest.org', '8885551234', true, '2023-10-13 08:05:06'), ('Alain', 'Alain', 'alain_alain@cegeptest.org', NULL, false, '1999-10-13 08:05:06'), ('Maryse', 'Boucher', 'mboucher@cegeptest.org', NULL, true, CURRENT_TIMESTAMP), ('Sonia', 'Champoux', 'schampoux@cegeptest.org', NULL, true, '2023-10-21');
Faites la même chose avec la table produits pour la colonne date_achat.
INSERT INTO produits (titre, prix, description, categorie_id, vendeur_id, acheteur_id)VALUES ('Zelda NES très propre!', 59.99, 'C''est vraiment le jeu de l''année... 1987', 1, 1, 2), ('Cyberpunk 2077', NULL, 'Je le passe au prochain!', 1, 1, NULL), ('Mon vieux divan', 100, 'Il a vécu beaucoup d''action, j''en ai acheté un nouveau', 2, 2, NULL), ('Willi Waller 2006', 19.99, 'Toujours dans la boite À ne pas manquer :p', 4, 2, 5), ('Honda Civic 99', 799.99, 'Pas trop bauché dans le temps. À qui la chance!', 3, 4, 2);
Consulter une des solutions possibles
INSERT INTO produits (titre, prix, description, categorie_id, vendeur_id, acheteur_id, date_achat)VALUES ('Zelda NES très propre!', 59.99, 'C''est vraiment le jeu de l''année... 1987', 1, 1, 2, '2023-10-17'), ('Cyberpunk 2077', NULL, 'Je le passe au prochain!', 1, 1, NULL, NULL), ('Mon vieux divan', 100, 'Il a vécu beaucoup d''action, j''en ai acheté un nouveau', 2, 2, NULL, NULL), ('Willi Waller 2006', 19.99, 'Toujours dans la boite À ne pas manquer :p', 4, 2, 5, '2023-06-01'), ('Honda Civic 99', 799.99, 'Pas trop bauché dans le temps. À qui la chance!', 3, 4, 2, '2004-08-10');
Pour tester les insertions
Faites une requête qui sort tous les utilisateurs qui sont inactifs
Consulter une des solutions possibles
SELECT *FROM utilisateursWHERE est_actif = false
Faites une requête qui retourne tous les produits non vendus
Consulter une des solutions possibles
SELECT *FROM produitsWHERE date_achat IS NULL-- OUSELECT *FROM produitsWHERE acheteur_id IS NULL
Si vous voulez aller plus loin, vous pouvez refaire le diagramme avec draw.io en utilisant les types de PostgreSQL.
Version originale par Pierre-Luc Boulanger