Exercice 1 : Club de tennis - Création de la base de données

Chapitre B15 : Langage de définition de données
Exercice 1 : Club de tennis - Création de la base de données
@
Mémento SQL
Objectifs :
Éventuellement
#?dj[hfhƒj[hkdceZ„b[f^oi_gk[
#C[jjh[[d·klh[b[ih[gk…j[iZ[YhƒWj_edZ[jWXb[
1.1 - Quelle est la clé primaire de la table Adherent ? Quelles doivent être ses caractéristiques ?
La clé primaire de la table Adherents est numAdh qui doit être, pour chaque enregistrement, unique, stable et définie.
1.2 - Dans quelle table inscrit-on un licencié (un adhérent) à une leçon ?
Cela s’enregistre dans la table Inscription.
1.3 - Quelle est la clé primaire de la table Inscription ?
La clé primaire de la table Inscription est le couple (numAdh.numLecon)
1.4 - Que signifient les symboles 1 et d reliant les champs « numAdh » des tables « Inscription » et « Adherent » ?
Cela signifie qu’un adhérent peut s’inscrire à plusieurs leçons. C’est une contrainte d’intégrité multiple.
1.5 - Les leçons sont-elles toujours individuelles ?
Si les leçons étaient obligatoirement individuelles, la table Lecon serait directement en relation avec la table
Adherent par une clé étrangère numAdh.
1.6 - Le nombre de leçons suivies peut-il être :
- identique au nombre total des adhérents ?
C’est possible mais pas plus probable qu’un autre cas : cela signifierait que chaque adhérent a suivi en moyenne
une leçon.
- supérieur au nombre total des adhérents ?
Cela correspondrait au cas où les adhérents ont suivi en moyenne plus d’une leçon.
- inférieur au nombre total des adhérents ?
Cette hypothèse correspond à un nombre moyen de leçons suivies par les adhérents inférieur à un (certains adhérents ne prennent pas de leçons et cela n’est pas compensé par les leçons suivies par les autres).
1.7 - Quel est le type du champ « montantCot » de la table « Cotisation » ?
Il s’agit d’un champ réel (monétaire).
1.8 - Dans quel champ de quelle table peut-on vérifier qu’un adhérent a bien payé sa cotisation ?
Cette vérification est permise par le champ cotReglee qui est probablement booléen (il pourrait aussi contenir la date
de règlement qui serait donc non nulle en cas de règlement à jour).
117
1.9 - Présentez les requêtes de création des tables Adherent, Lecon, Cotisation et Inscription afin de les implanter sous MySQL. On
tiendra compte, pour faciliter la saisie, que 80 % des adhérents sont des hommes habitant Mennecy (91540).
CREATE TABLE Adherent ( numAdh integer Not Null AUTO-INCREMENT,
sexeAdh varchar(1) Not Null DEFAULT “H“,
civiliteAdh varchar(12) Not Null DEFAULT “Monsieur“,
nomAdh varchar(30) Not Null,
prenomAdh varchar(30) Not Null,
adrAdh varchar(100) Not Null,
CPAdh integer Not Null DEFAULT “91540“,
villeAdh varchar (30) Not Null,
PRIMARY KEY (numAdh));
CREATE TABLE Cotisation ( numCot integer Not Null AUTO-INCREMENT,
anneeCot smallint DEFAULT YEAR(CURRENT_DATE),
cotReglee decimal(3,2),
regltCot Boolean,
numAdh integer,
PRIMARY KEY (numCot),
FOREIGN KEY (numAdh) REFERENCES Adherent(numAdh));
CREATE TABLE Lecon ( numLecon smallint Not Null AUTO-INCREMENT,
niveauLecon varchar(10),
jourLecon Date Not Null,
debutLecon Time Not Null,
finLecon Time Not Null,
PRIMARY KEY (numLecon));
CREATE TABLE Inscription ( numAdh integer Not Null,
numLecon smallint,
PRIMARY KEY (numAdh, numLecon),
FOREIGN KEY (numAdh) REFERENCES Adherent(numAdh),
FOREIGN KEY (numLecon) REFERENCES Lecon(numLecon));
1.10 - Quel autre document aurait pu aider à la création des tables ?
Le dictionnaire des données recense l’ensemble des données de la base de données en précisant leur type.
1.11 - Le trésorier du club de tennis souhaite connaître les nom et prénom des licenciés n’ayant pas encore réglé leur cotisation. Écrivez
cette requête en SQL.
SELECT
FROM
WHERE
AND
nomAdh, prenomAdh
Adherent, Cotisation
Adherent.numAdh = Cotisation.numAdh
cotReglee = False ;
1.12 - On vous demande de déterminer l’effectif des cours (nombre de licenciés) par niveau de tennis (compétition, confirmé, intermédiaire et débutant). Écrivez cette requête en SQL.
SELECT
FROM
WHERE
GROUP BY
niveauLecon, COUNT(numAdh) AS effectif
Inscription, Lecon
Lecon.numLecon = Inscription.numLecon
niveauLecon ;
1.13 - Créez une vue, nommée Adh2010, des adhérents (sans leurs coordonnées personnelles), à jour de cotisation pour 2010, à
destination des moniteurs.
CREATE VIEW Adh2010
AS SELECT civiliteAdh, nomAdh, prenomAdh, sexeAdh, Adherent.numAdh
FROM Adherent, Cotisation
WHERE Adherent.numAdh = Cotisation.numAdh
AND cotReglee = True
AND anneeCot = 2010
118