Le modèle relationnel introduit à la séquence précédente est un modèle mathématique permettant de raisonner sur des données tabulées. Il est mis en œuvre par un logiciel particulier, le Système de Gestion
de Bases de Données (SGBD en abrégé).
Un SGBD relationnel est un SGBD utilisant le modèle relationnel pour la représentation des données!. L'écrasante majorité des SGBD relationnels utilisent le langage SQL (Séructured Query Language, langage de requête structuré). Ce dernier permet d'envoyer
des ordres au SGDB.
Les ordres peuvent être de deux natures. Les mises à
jour permettent la création de relations, l’ajout d’entité dans ces dernières, leur modification et leur suppression.
Les requêtes permettent de récupérer les données répondant à des critères particuliers.
Directement inspiré du modèle relationnel introduit par E. Codd, le langage SQL permet la définition de relations ou tables dans une base de données relationnelle. Ce langage est standardisé par PISO sous la référence ISO/IEC 9075.
La dernière version du standard date de 2016.
Le langage SQL permet de créer des tables en spécifiant leur nom, leurs attributs, les types de ces derniers et les contraintes associées à la table.
Vous allez utiliserer comme SGBD pour travailler avec le langage SQL :
MariaDB avec l'application phpmyadmin
Pour cela, il faut aller sur le serveur privé (Vps) à l'adresse suivante:
![](\"https://sciencesappliquees.com/images/nsi/sql1.png\")
et tapez les paramètres données par votre professeur.
![](\"https://sciencesappliquees.com/images/nsi/sql2.png\")
Vous sélectionnez la base de donnée et cliquez sur l'onglet SQL pour exécuter vos expressions SQL.
Maintenant vous allez créer les tables correspondant à la modélisation finale de la médiathèque obtenue à la fin de la séquence précédente.
On saisit les ordres suivants :
CREATE TABLE usager (nom VARCHAR(90) NOT NULL, prenom VARCHAR(90) NOT NULL,
adresse VARCHAR(300) NOT NULL, cp VARCHAR(5) NOT NULL,
ville VARCHAR(60) NOT NULL,
email VARCHAR(60) NOT NULL,
code_barre CHAR(15) PRIMARY KEY);
CREATE TABLE livre (titre VARCHAR(300) NOT NULL,
editeur VARCHAR(90) NOT NULL,
annee INT NOT NULL,
isbn CHAR(14) PRIMARY KEY);
CREATE TABLE auteur (a_id INT PRIMARY KEY,
nom VARCHAR(90) NOT NULL,
prenom VARCHAR(90) NOT NULL);
CREATE TABLE auteur_de (a_id INT REFERENCES Auteur(a_id),
isbn CHAR(14)
REFERENCES Livre(isbn),
PRIMARY KEY (a_id, isbn));
CREATE TABLE emprunt (code_barre CHAR(15)
REFERENCES Usager(code_barre),
isbn CHAR(14) PRIMARY KEY
REFERENCES Livre(isbn),
retour DATE NOT NULL);
Vous devez obtenir ceci :
![](\"https://sciencesappliquees.com/images/nsi/sql3.png\")
Vous voyez que l’interaction avec un SGBD se fait par l'envoi d’une suite d'ordres SQL.
Un ordre peut s'étendre sur plusieurs lignes.
Les blancs et l’indentation ne sont pas significatifs mais améliorent la lisibilité dans certains cas.
Un ordre se termine par un « ; ».
Les cinq ordres donnés ci-dessus créent les cinq
tables composant notre base de données.
Une première remarque de syntaxe est que SQL est insensible à la casse. On aurait ainsi pu écrire « create
table livre ... » ou encore « CReATE TablE liVrE ... ». Nous faisons le choix d'utiliser des capitales pour les mots clés du langage SQL (CREATE, TABLE, PRIMARY, etc.) et des minuscules pour les noms d’attributs (a_id,
prenom, etc.) et de tables.
Ces derniers ne pouvant pas contenir d’espace,
nous utilisons le caractère « _» comme séparateur de mots.
Enfin, nous utilisons la convention, généralement considérée comme une bonne pratique, d'utiliser des noms de tables au singulier.
Une autre remarque de syntaxe est que le langage SQL peut sembler « verbeux », à l'inverse d’un langage
de programmation comme Python.
Les ordres ressemblent à du langage naturel (en anglais). La notation est aussi semblable aux schémas du modèle relationnel.
Mettre le résultat ici.
La syntaxe générale d'un ordre CREATE TABLE est la suivante :
CREATE TABLE nom_table (att1 dom1 contri1? ,
.......... ,
attn domn contrin? ,
contr_glob1,
......... ,
contr_globn ) ;
On donne, à la suite des mots clés CREATE TABLE, un nom de table, suivi de la liste des définitions d’attributs entre parenthèses et séparées par des virgules.
Une définition d’attribut consiste en un nom d'attribut, un type d’attribut (ou domaine) qui sont tous les deux obligatoires et optionnellement des contraintes sur cet attribut, Si des contraintes portent sur plusieurs attributs à la fois (par exemple pour spécifier que plusieurs attributs forment une clé primaire) on peut placer ces contraintes en fin de liste, avant la parenthèse fermante.
Attention : Le système lèvera une erreur si la table existe déjà.
Strictement parlant, une relation du modèle relationnel et une table SQL ne sont pas des concepts équivalents. En particulier, une table peut contenir des doublons. En effet, il n'est pas obligatoire de spécifier une clé primaire lors de la création d’une table SQL. Sans clé primaire, une table peut contenir plusieurs copies du même n-uplet, sans que cela pose problème, chose qui n’est a priori pas autorisé pour les ensembles du modèle relationnel (car ce sont des ensembles).
Nous ignorerons ces différences dans le cadre du programme de terminale et utiliserons les termes «tables» et «relations» indistinctement.
De façon analogue, en SQL les attributs d’une relation sont appelées des colonnes et les entités des lignes.
Mettre le résultat ici.
Les domaines abstraits du modèle relationnel correspondent à des types de données du langage SQL.
Nous avons comme type :
Types numériques.
| nom du type | exact ou approché | description |
| SMALLINT | exact | entier 16 bits signé |
| INTEGER | exact | entier 32 bits signé |
| INT | exact | alias pour INTEGER |
| BIGINT | exact | entier 64 bits signé |
| DECIMAL(t, f) | exact | décimal signé de t chiffres dont f après la virgule |
| REAL | approché | flottant 32 bits |
| DOUBLE PRECISION | approché | flottant 64 bits |
Le standard SQL définit plusieurs types numériques.
Ces derniers sont soit des types numériques exacts, soit des types numériques approchés.
La table ci-dessous détaille les différents types numériques. La plupart de ces types représentent fidèlement les entiers où flottants «machine» manipulables directement par le processeur. Une exception notable est le type DECIMAL(E, f) qui permet de représenter de manière exacte un nombre à virgule d'une taille donnée. Ce type est particulièremnent important, car il permet par exemple de représenter des sommes d'argent sans erreurs d'arrondis. Ainsi, le type
DECIMAL(5,2) permet de stocker des valeurs décimales de 5 chiffres, dont deux après la virgule, soit des valeurs entre -999, 99 et 999,99. Une utilisation de ce type permettra de réaliser une contrainte de domaine
sur un attribut numérique. Le standard ne supporte que les nombres en base 10, sans autoriser des notations hexadécimales ou octales comme dans les
autres langages.
Mettre le résultat ici (code et figure).
Types textes
| nom du type | description |
| CHAR(n) | Chaîne d’exactement n caractères. Les caractères manquant sont complétés par des espaces. |
| TEXT Chaîne de taille quelconque. | Chaîne d’au plus n caractères. |
| TEXT | Chaîne de taille quelconque. |
Le standard SQL définit plusieurs types permettant de stocker des chaînes de caractères. Malheureusement, ces derniers sont supportés
de manière inégale dans les divers SGBD. On se limitera aux plus communs.
Le type CHAR(n) permet de définir des chaînes de caractères de taille exactement n. La taille maximale acceptée pour n dépend des différents systèmes, mais ils supportent tous au moins 8000 caractères. La taille minimale est 1. Ce type est approprié lorsque l’on veut stocker des exactement 14 caractères, de la forme *XXX-XXXXXXXXXX’ (trois chiffres, un tiret, dix chiffres). Attention, si l’on stocke une chaîne de taille inférieure à n, cette dernière est complétée par la droite avec des espaces. Ainsi, la chaîne \"hello’ stockée dans une colonne de type CHAR(10) sera convertie en ’hello_____' (où le caractère « _ » représente un espace).
Le type VARCHAR(n) permet quant à lui de définir des chaînes de taille au plus n. La valeur de n suit les mêmes règles que pour le type CHAR.
Enfin, le type TEXT permet de stocker des chaînes de caractères de taille variable, sans fixer de taille maximale à priori. En pratique, il est équivalent à VARCHAR(n) pour la plus grande valeur de n supportée par le système. Les chaînes de caractères littérales sont délimitées par des guillemets simples « ' ».
Le caractère guillemet peut être échappé en le doublant,
Par exemple, la chaîne « c'est moi » s’écrira ’c' 'est moi'.
Les autres caractères n’ont pas besoin
d'être échappés. Une chaîne peut en particulier contenir un retour chariot (et donc être écrite sur plusieurs lignes).
Type booléen
Le type BOULEAN est inégalement supporté par les différents systèmes, qu’ils soient commerciaux ou libres. Cela est dû au fait que le standard SQL laisse ce type comme optionnel.
Les SGBD sont donc libres de ne pas le proposer. Une alternative possible est d'utiliser CHAR(1) et de se
servir de deux caractères distincts (par exemple 'T et 'F') pour représenter des booléens.
Une autre alternative est d'utiliser un type numérique exact et de considérer la valeur 0 comme fausse et les autres valeurs comme vraies.
","title":""},{"edit":"Mettre le résultat ici (code et figure).
Type des dates, durées et instants
| nom du type | description |
| DATE | une date au format 'AAAA-MM-JJ' |
| TIME | une heure au format ’hh:mm:ss' |
| TIMESTAMP | un instant (date et heure) au format 'AAAA-MM-JJ hh:mm:ss’ |
À première vue anodine, la gestion des dates et du temps est un problème excessivement complexe, source de nombreux bugs. Le standard SQL propose donc de nombreux types temporels permettant de représenter des dates, des heures et des durées.
Quelques types sont donnés dans la table ci-dessus. Les valeurs de ces types s’écrivent comme de simples chaînes de caractères. Une fonctionnalité intéressante est la possibilité d'utiliser l'addition pour ajouter des jours à une valeur de type DATE.
Si d est une expression de type DATE, alors d + 10 représente la date + 10 jours après d. Cette opération produit une valeur de type DATE et donc prend correctement en compte les changements de mois, année et les années bissextiles.
Nous ne détaillons pas plus ces types de données très
complexes (qui savent par exemple prendre en compte les fuseaux horaires).
Mettre le résultat ici (code et figure).
Valeur NULL
Une valeur notée NULL existe en SQL. Elle représente une absence de valeur et peut donc être utilisée à la place de n’importe quelle autre valeur, quel que soit le type attendu.
Son utilisation est similaire à la constante None du langage Python, mais son comportement est complexe et peut être source d’erreurs.
Il est déconseillé de l’utiliser dans le cadre d’une initiation aux bases de données. En particulier, SQL interdit l’utilisation de NULL comme valeur pour une clé primaire. En revanche, elle est autorisée pour les clés étrangères. La valeur NULL permet donc de violer la contrainte de référence.
La seule chose que l’on fera donc avec des attributs potentiellement NULL est de les tester au moyen des expression
- e IS NULL
- ou e IS NOT NULL.
Attention cependant, le test e = NULL ne produit pas le résultat booléen comme s’y attendrait mais renvoie toujours NULL quelle que soit la valeur de e .
Mettre le résultat ici (code et figure).
Les contraintes d’intégrité jouant un rôle fondamental dans le modèle relationnel, il est naturel de pouvoir les spécifier en SQL.
Nous montrons ici comment définir les quatre types de contraintes d’intégrité étudiés au précédent :
Clé primaire. Les mots clés PRIMARY KEY permettent d'indiquer qu'un attribut est une clé primaire.
Voici un exemple :
CREATE TABLE personne (id INT PRIMARY KEY,
nom VARCHAR(99),
prenom VARCHAR (99));
Si l’on souhaite utiliser plusieurs attributs comme clé primaire, on peut spécifier la contrainte après les attributs :
CREATE TABLE point (x INT,
y INT,
couleur VARCHAR(30),
PRIMARY KEY (x. y)):
Mettre le résultat ici (code et figure).
Clé étrangère. Un attribut peut être qualifié de clé étrangère en utilisant le mot clé REFERENCES suivi de la table où se trouve la clé primaire et de son nom.
CREATE TABLE employe (emp INT REFERENCES personne(id),
dept VARCHAR (90),
suph INT REFERENCES personne(id));
La table employe associe un employé (attribut emp}, qui est une personne, à son département dans l’entreprise (ressources humaines, support informatique, comptabilité, etc.) et à son supérieur hiérarchique (attribut suph), qui est aussi une personne. Ce lien est matérialisé par le fait que emp et suph sont des clés étrangères. Il est à noter que la plupart des SGBD ne supportent pas l’utilisation de clés étrangères composites.
L'utilité des clés primaires composites se trouve donc amoindrie en pratique.
","title":""},{"edit":"Mettre le résultat ici (code et figure).
Unicité, non nullité.
Il peut être intéressant de spécifier qu'un groupe d’attributs est unique, sans pour autant en faire une clé primaire. Cette information permet au SGBD plus de vérifications sur les données (cohérence
des données) et parfois de traiter ces dernières de façon plus efficace.
Cela peut être spécifié au moyen du mot clé UNIQUE.
Une autre bonne pratique consiste à déclarer qu’un attribut ne peut pas être NULL. Cette valeur spéciale ne pourra donc jamais être utilisée pour remplir des valeurs de la colonne correspondante.
Cela peut être fait au moyen du mot clé NOT NULL.
Notons que PRIMARY KEY implique obligatoirement NOT NULL.
En reprenant l'exemple des utilisateurs de la bibliothèque, on pourrait raffiner notre définition de table de la manière suivante :
CREATE TABLE usager (nom VARCHAR(90) NOT NULL,
prenom VARCHAR(90) NOT NULL,
adresse VARCHAR(300) NOT NULL,
cp VARCHAR(S5) NOT NULL,
ville VARCHAR(60) NOT NULL,
email VARCHAR(60) NOT NULL UNIQUE,
code_barre CHAR(15) PRIMARY KEY);
On spécifie de cette façon qu'aucun des attributs n’est optionnel et de plus que email doit être UNIQUE dans la table (même s’il n’est pas une clé primaire).
","title":""},{"edit":"Mettre le résultat ici (code et figure).
Contraintes utilisateur.
Il est possible de spécifier des contraintes arbitraires sur les attributs d’une même ligne au moyen du mot clé CHECK, suivi d'une formule booléenne. Cette contrainte est placée obligatoirement en fn de déclaration avant la parenthèse fermante (et non pas au niveau d’un attribut).
Nous donnons ici quelques exemples et reviendrons dessus dans la séquence suivante sur la syntaxe des expressions.NETS
CREATE TABLE produit (id INT PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
quantite INT NOT NULL,
prix DECIMAL(10,2) NOT NULL,
CHECK (quantite >= 0 AND prix >= 0));
Nous définissons ici une table des produits vendus dans un magasin. Ces derniers ont un identifiant (qui est la clé primaire), un nom, une quantité et un prix.
Ceux-ci ne peuvent jamais être négatifs (ce qui n’est pas exprimable uniquement au moyen des types INT ou DECIMAL). On ajoute donc une contrainte CHECK.
Mettre le résultat ici (code et figure).
Une fois qu’une table est créée, il n'est pas possible d'en créer une autre avec le même nom. Si on souhaite recréer la table, par exemple avec un schéma différent, il faut d'abord supprimer celle portant le même nom.
C’est le but de l'instruction DROP TABLE :
DROP TABLE auteur_de;
Cette dernière supprime la table et donc toutes les données qui y sont stockées.
Un point important est qu'il n'est pas possible de supprimer une table si elle sert de référence pour une clé étrangère d’une autre table, car cela violerait une contrainte de référence :
# DROP TABLE usager;
ERROR: cannot drop table utilisateurs because other objects
depend on it
DETAIL: constraint emprunt code barre_fkey on table
emprunt depends on table usager
Nos reproduisons ci-dessus une interaction avec le SGBD PostgreSQL, mais tous les autres systèmes relationnels auront un comportement semblable.
Le système indique que la table usager est mentionnée par une contrainte (ici celle de la table emprunt). Le système refuse donc de supprimer la table et renvoie une erreur.
Il convient alors de supprimer les tables dans le bon ordre, c'est-à-dire d'abord les tables contenant les clés étrangères, avant les tables contenant les clés primaires référencées.
DROP TABLE emprunt;
DROP TABLE auteur_de;
DROP TABLE auteur;
DROP TABLE livre;
Certains SGBD permettent de spécifier que la suppression d’une table doit détruire automatiquement toutes les tables qui dépendent d’elle. Cette fonctionnalité est commode mais dangereuse et de toute façon inégalement supportée. Il est donc conseillé de spécifier explicitement les commandes de
suppression, dans l’ordre adéquat.
Le standard SQL en pratique. Bien que très volumineux {le standard ISO/IEC 9075 qui définit la norme SQL se décompose en 14 parties distinctes et fait plus de 5000 pages). Le standard SQL comporte beaucoup de parties optionnelles. En pratique, même si la plupart des SGBD commerciaux et libres en implémentent une partie, leur support est inégal.
Il n’est donc pas rare de devoir écrire du code SQL « propriétaire », c'est-à-dire utilisant des extensions propres à SGBD particulier, dès que l’on souhaite faire des traitements un tant soit peu complexes. Cette
pratique nuit malheureusement à la portabilité.
Mettre le résultat ici (code et figure).
Nous pouvons enfin aborder l'insertion de nouvelles valeurs dans une table.
Cette action s'effectue au moyen de l’ordre INSERT INTO
INSERT INTO auteur VALUES (97, 'Ritchie','Dennis');
On spécifie le nom de la table (ici auteur) suivi d'une suite de n-uplets, chacun entre parenthèses.
Chaque n-uplet représente une nouvelle ligne de
la table. Les valeurs des attributs sont supposés être dans le même ordre que lors du « CREATE TABLE ». Si on souhaite les passer dans un ordre différent, on peut spécifier l’ordre des attributs avant le mot clé VALUES
INSERT INTO auteur (prenom, a_id, nom)
VALUES (’Jean-Jacques’, 200, ’Rousseau’);
Un point important est que les contraintes d’intégrités sont vérifiées au moment de l'insertion.
Une instruction INSERT violant ces contraintes conduira donc à une erreur et les données correspondantes ne seront pas ajoutées à la table.
# INSERT INTO auteur (97, ’Dumas’, ’Alexandre”’);
ERROR: duplicate key value violates unique constraint
“auteur _pkey\"
TDETATT Vaner Pa SANLÉOTN Smandiu nvicte
Ici, on essaye d’ajouter un auteur avec la même clé primaire qu’un auteur
déjà existant.
# INSERT INTO produit VALUES (1, ’ordinateur’, 10, -200);
ERROR: new row for relation \"produit\" violates check
coustraint \"produit _check\"
DETAIL: Failing row contains (1, ordinateur, 10, -200.00).
Ici, l'insertion viole la contrainte CHECK de la table produit définie plus haut.
Mettre le résultat ici (code et figure).
Regrouper ensemble les termes synonymes : colonne, entité, domaine, attribut, ligne, schéma, base de données, type, column, row.
","title":"Exercice"},{"edit":"Mettre le résultat ici (code et figure).
(colonne, attribut, column), (ligne, entité, row),
(domaine, type), (schéma), (base de données).
Nous avons le modéliser l'annuaire de la manière suivante :
Annuaire(nom String, prénom String, tel String)
Les contraintes sur le modèle sont les suivantes :
- le numéro de téléphone est unique;
- le numéro de téléphone est une clé primaire.
- les attributs nom et le prenom ne peuvent pas être vides.
Donner un ordre SQL permettant de créer la table correspondante,
avec un maximum de contrainte d'intégrité.
","title":"Exercice"},{"edit":"Mettre le résultat ici (code et figure).
CREATE TABLE annuaire( nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
tel VARCHAR(20) PRIMARY KEY);
Nous avons le modèle relationnel suivant :
Eleve(nom String, prénom String, num String)
Matiere(intitule String, m_id INT)
Note(num String, m_id INT, note Float)
Contrainte : attribut note compris entre 0 et 20.
Donner les ordres SQL permettant de créer les tables correspondantes, avec un maximum de contrainte d'intégrité.
Donner les ordres SQL permettant de supprimer ces tables une fois qu’elles existent.
Mettre le résultat ici (code et figure).
CREATE TABLE eleve (nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
num VARCHAR(20) PRIMARY KEY);
CREATE TABLE matiere ( intitule VARCHAR(100) NOT NULL,
m_id INT PRIMARY KEY);
CREATE TABLE note ( num VARCHAR(20) REFERENCES eleve(num),
m_id INT REFERENCES matiere(m_id),
note DECIMAL(4,2) NOT NULL,
PRIMARY KEY (num, m_id),
CHECK (note >= O AND note <= 20));
DROP TABLE note;
DROP TABLE matiere;
DROP TABLE eleve;
Pour chacune des séquences d'ordres SQL suivantes, dire quelle instruction provoque une erreur.
On suppose que la base de données ne contient aucune table au début de chaque séquence,
1. DROP TABLE client;
CREATE TABLE client (cid INT PRIMARY KEY,
nom VARCHAR (100),
prenom VARCHAR (100),
points _fidelite INT NOT NULL,
CHECK (points_fidelite >= 0));
2. CREATE TABLE client (cid INT PRIMARY KEY,
nom VARCHAR (100),
prenom VARCHAR(100),
points_fidelite INT NOT NULL,
CHECK(points_fidelite >= 0));
CREATE TABLE commande (cid INT REFERENCES client (cid),
pid INT REFERENCES produit (pid),
date DATE NOT NULL) ;
CREATE TABLE produit (pid INT PRIMARY KEY,
nom VARCHAR (100),
prix DECIMAL(10,2));
3.
CREATE TABLE client (cid INT PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
points _fidelite INT NOT NULL,
CHECK (points_fidelite >= 0));
CREATE TABLE produit (pid INT PRIMARY KEY,
nom VARCHAR (100),
prix DECIMAL(10,2));
CREATE TABLE commande (cid INT REFERENCES client(cid),
nomp VARCHAR(100)
REFERENCES produit (nom),
date DATE NOT NULL) ;
4.
CREATE TABLE client (cid INT PRIMARY KEY,
nom VARCHAR (100),
prenom VARCHAR(100),
points_fidelite INT NOT NULL,
CHECK (points _fidelite >= 0));
CREATE TABLE produit (pid INT PRIMARY KEY,
nom VARCHAR (100),
prix DECIMAL(10,2));
CREATE TABLE commande (cid INT REFERENCES client(cid),
pid INT REFERENCES produit (pid),
date DATE NOT NULL);
INSERT INTO commande VALUES (0, 0, 2020-03-02);
Mettre le résultat ici (code et figure).
1. L'ordre DROP échoue car la table client n'existe pas.
2. L'ordre CREATE TABLE commande ... échoue car la table produits n'existe pas encore.
3. L'ordre CREATE TABLE commande ... échoue car la table nomp ne référence pas une clé primaire.
4. L'ordre INSERT échoue, les valeurs 0 et O ne font pas référence à des clés primaires.
On considère les deux tables suivantes :
CREATE TABLE joueur (jid INT PRIMARY KEY,
nom VARCHAR(100) NOT NULL);
CREATE TABLE partie (j1 INT REFERENCES joueur(jid),
j2 INT REFERENCES joueur(jid),
score1 INT NOT NULL,
score2 INT NOT NULL,
CHECK ((j1 <> j2));
Ces tables stockent des résultats de parties entre des joueurs. Lister toutes les contraintes d’intégrité et pour chacune donner des ordres SQL violant ces contraintes.
","title":"Exercice"},{"edit":"Mettre le résultat ici (code et figure).
On donne la liste des contraintes et à chaque fois
une suite d’ordres incompatibles avec celle-ci.
1. jid est une clé primaire (contrainte d’entité)
INSERT INTO joueur VALUES (1, 'TOTO’);
INSERT INTO joueur VALUES (1, 'TOTO’);
2. nom doit être non NULL (contrainte de domaine)
INSERT INTO joueur VALUES (1, NULL);
Requêtes similaires pour score1 et score2.
3. j1 et j2 sont des clé étrangère (contraintes de référence)
INSERT INTO partie VALUES (42, 43, 10, 10);
(on suppose qu'aucun jid n’a la valeur 42 ou 43)
4. j1et j2 doivent avoir des valeurs distinctes (car une personne ne peut pas jouer contre elle-même, contrainte utilisateur).
INSERT INTO joueur VALUES (1, ’Toto”);
INSERT INTO partie VALUES (1, 1, 10, 10);
5. Toutes les contraintes de domaine peuvent être violées en insérant une valeur d’un type différent de celui attendu.
Nous avons créé les tables suivantes ;
CREATE TABLE joueur (jid INT PRIMARY KEY,
nom VARCHAR(100) NOT NULL);
CREATE TABLE partie (j1 INT REFERENCES joueur(jid),
j2 INT REFERENCES joueur(jid),
score1 INT NOT NULL,
score2 INT NOT NULL,
CHECK ((j1 <> j2));
Modifier les ordres de création des tables en prenant en compte les modifications suivantes :
- La table partie contient en plus une colonne jour non nulle, indiquant la date à laquelle la partie à eu lieu.
- Les scores ne peuvent pas être négatifs.
- Deux joueurs ne peuvent pas jouer plusieurs fois le même jour.
","title":"Exercice"},{"edit":"Mettre le résultat ici (code et figure).
La table joueur est inchangée. On modifie la table partie.
CREATE TABLE partie (j1 INT REFERENCES joueur(jid),
j2 INT REFERENCES joueur(jid),
score1 INT NOT NULL,
score2 INT NOT NULL,
jour DATE NOT NULL,
UNIQUE (ji, j2, jour),
CHECK ((j1 < j2) AND
(score1 >= 0) AND
(score2 >= 0)));
En particulier, pour interdire de saisir pour le même jour une partie entre les joueurs 1 et 2 et une autre entre les joueurs 2 et 1, on force le jid du premier joueur à être strictement inférieur. La contrainte UNIQUE permet
ensuite de s'assurer que un triplet (j1, j2, jour) n’apparaît qu’une seule fois.
Écrire un programme Python qui lit un fichier CSV infos.csv au format suivant :
- les champs sont séparés par des « ; »
- le fichier contient 4 colonnes nom, prenom, annee naissance, taille, représentant le nom, prénom, l'année de naissance et la taille (en cm) de personnes.
Le programme doit écrire sur sa sortie standard un script SQL (i.e. un ensemble d'ordres) qui crée une table permettant de stocker ces informations ainsi qu'un identifiant unique (entier) servant de clé primaire et remplit la table avec les données du fichier CSV.
","title":"Exercice"},{"edit":"Mettre le résultat ici (code et figure).
import csv
print (\"\"\"CREATE TABLE personne
(pid INT PRIMARY KEY,
nom VARCHAR (100),
prenom VARCHAR (100),
annee_naiss INT,
taille INT,
CHECK (taille >= O AND annee_naissance >= 0));\"\"\")
def q(s):
return s.replace(\"'\", \"''\")
with open (\"infos.csv\") as f:
pid = 0
for l in csv.DictReader(f, delimiter=\";\"):
print (\"\"\"INSERT INTO personne
VALUES (%d, '%s', '%s', %d, %d);\"\"\" % \\
(pid, q(l[\"nom\"]), q(l[\"prenom\"]), \\
int(l[\"annee_naissance\"]), int(l[\"taille\"])))
pid = pid +1
La fonction auxiliaire q prend en argument une chaîne de caractères et échappe le caractère \" s’il est présent en le doublant ''(règle d'échappement pour les chaînes SQL).
SOLUTIONSSELECT titre FROM livre;