CREATE TABLE et ALTER — contraintes d’une bibliothèque

Tables d’un cabinet : patient (numero PK, Nom, …) et ordonnance (code PK, numero# → patient, Matricule# → medecin, …).

1. Écrire le CREATE TABLE de patient avec numero entier en clé primaire.
2. Écrire le CREATE TABLE d’ordonnance avec les deux clés étrangères.
3. Écrire l’ALTER qui ajoute adresse (chaîne) à patient.

Table Types (Type CHAR(1) PK, Designat VARCHAR(25)) déjà créée. Table Fichiers : CodeF PK chaîne 6, TypeF FK vers Types, Taille numérique, Datcreat date obligatoire.

1. Écrire le CREATE TABLE Fichiers en maintenant l’intégrité référentielle sur le type.
2. Que se passe-t-il si l’on insère un fichier avec TypeF = 'X' alors que 'X' est absent de Types ?

Un lycée enregistre les notes de ses élèves dans une base où les tables Eleve ( numInsc , nomEl , classe ) et Matiere ( codeMat , libMat , coef ) sont déjà créées. On veut créer la table Note ( numInsc# , codeMat# , trimestre , valeur ) à partir de la description suivante :

1. Écrire la requête SQL permettant de créer la table Note, en nommant pkNote la contrainte de clé primaire.
2. Expliquer pourquoi le couple (numInsc, codeMat) ne suffirait pas comme clé primaire.
3. L'élève 1203 et la matière « MATH » existent. On exécute dans l'ordre les trois requêtes ci-dessous : lesquelles sont acceptées ?

INSERT INTO Note
VALUES (1203, 'MATH', 2, 14.50);
INSERT INTO Note
VALUES (1203, 'MATH', 2, 16.00);
INSERT INTO Note
VALUES (1203, 'MATH', 3, 16.00);

Un lycée technique suit les stages d'été de ses élèves dans la table Stage ( numStage , entreprise , dateDebut , dateFin , nbHeures , mention ), décrite ainsi :

1. Écrire la requête SQL permettant de créer la table Stage, en nommant ckHeures, ckMention et ckDates les trois contraintes de domaine.
2. La table ayant été créée sans la contrainte ckDates, écrire la requête permettant de l'ajouter.
3. Dire si chacune des deux lignes suivantes serait acceptée par la table complète, et justifier.

Le club Forme et Santé de Hammamet gère ses activités avec les tables Activite ( codeAct , libAct , tarifMensuel ) et Seance ( numSeance , dateSeance , salle , codeAct# ). La table Activite a été créée avec une contrainte de domaine nommée ckLib, qui limite libAct aux valeurs « Musculation », « Cardio » et « Yoga ». Extraits :

Le club ouvre une piscine : il crée l'activité « A4 », de libellé « Natation », à 45 dinars par mois, et y affecte toutes les séances de la salle « Bassin ».

1. Écrire les requêtes SQL permettant :
   1. de supprimer la contrainte ckLib ;
   2. d'ajouter à Activite une contrainte nommée ckLib2 acceptant les quatre libellés ;
   3. d'insérer l'activité A4 ;
   4. d'affecter l'activité A4 aux séances de la salle « Bassin ».
2. Expliquer pourquoi l'insertion c) ne peut être exécutée qu'après a), et la mise à jour d) qu'après c).

Le parc automobile d'une municipalité est géré avec les tables Service ( codeServ , nomServ ) et Vehicule ( immat , marque , dateMiseCirc , codeServ ). Après leur création, l'administrateur constate qu'il a oublié de déclarer codeServ comme clé étrangère de Vehicule. Extraits :

1. Écrire la requête SQL permettant de définir la contrainte d'intégrité référentielle oubliée, nommée fkServ.
2. Pour suivre la consommation, on ajoute la table Carburant : idCarb (entier, clé primaire) et typeCarb (chaîne de 10 caractères au plus, obligatoire, limitée à « Essence », « Diesel » et « Electrique »). Écrire la requête de création.
3. Écrire les requêtes permettant d'ajouter à Vehicule le champ idCarb, puis d'en faire une clé étrangère nommée fkCarb vers Carburant.
4. Une fois fkServ définie, la requête suivante est refusée. Pourquoi ? INSERT INTO Vehicule (immat, marque, codeServ) VALUES ('215 TU 4410', 'Isuzu', 'S7');

Une association culturelle de Testour enregistre ses spectacles dans la table Evenement, créée par la requête suivante :

CREATE TABLE Evenement (
  idEv INT PRIMARY KEY,
  titre VARCHAR(40) NOT NULL,
  lieu VARCHAR(30),
  nbPlaces INT,
  prix DECIMAL(6,3)
);

Pour chacun des besoins suivants, mettre une croix devant la requête correcte (une seule par besoin) ; les requêtes retenues sont exécutées dans l'ordre a, b, c, d.

• a. Supprimer le champ « lieu ». ☐ ALTER TABLE Evenement DELETE lieu; ☐ ALTER TABLE Evenement DROP lieu; ☐ DELETE lieu FROM Evenement;
• b. Porter la taille du champ « titre » de 40 à 80 caractères. ☐ ALTER TABLE Evenement MODIFY titre VARCHAR(80) NOT NULL; ☐ UPDATE Evenement SET titre = VARCHAR(80); ☐ ALTER TABLE Evenement ADD titre VARCHAR(80) NOT NULL;
• c. Renommer le champ « prix » en « tarif ». ☐ UPDATE Evenement SET prix = tarif; ☐ ALTER TABLE Evenement MODIFY prix tarif; ☐ ALTER TABLE Evenement RENAME COLUMN prix TO tarif;
• d. Donner la valeur par défaut 100 au champ « nbPlaces ». ☐ UPDATE Evenement SET nbPlaces = DEFAULT 100; ☐ ALTER TABLE Evenement MODIFY nbPlaces INT DEFAULT 100; ☐ ALTER TABLE Evenement ADD DEFAULT 100;

1. Répondre aux besoins a à d.
2. Écrire la requête permettant ensuite d'ajouter le champ organisateur, chaîne de 30 caractères.
3. Donner la liste des champs de la table Evenement après ces cinq modifications.
4. Que contient le champ nbPlaces de la ligne enregistrée par la requête suivante ? INSERT INTO Evenement (idEv, titre, tarif) VALUES (12, 'Nuit du malouf', 15.000);