Université Ibn Zohr Département génie électrique
Ecole supérieure de technologie
TD4 SQL
Exercice 1.
Une entreprise désire gérer son parc informatique à l’aide d’une base de données. Le
bâtiment est composé de trois étages. Chaque étage possède son réseau (ou segment distinct)
Ethernet. Ces réseaux traversent des salles équipées de postes de travail. Un poste de travail
est une machine sur laquelle sont installés certains logiciels. Quatre catégories de postes de
travail sont recensées (stations Unix, terminaux X, PC Windows et PC NT).
Les noms et types des colonnes sont les suivants :
Colonne Commentaire Type
indIP trois premiers groupes IP (exemple : 130.120.80) VARCHAR(11)
nomSegment nom du segment VARCHAR(20)
etage étage du segment TINYINT(1)
nSalle numéro de la salle VARCHAR(7)
nomSalle nom de la salle VARCHAR(20)
nbPoste nombre de postes de travail dans la salle TINYINT(2)
nPoste code du poste de travail VARCHAR(7)
nomPoste nom du poste de travail VARCHAR(20)
ad dernier groupe de chiffres IP (exemple : 11) VARCHAR(3)
typePoste type du poste (UNIX, TX, PCWS, PCNT) VARCHAR(9)
A Création des tables
Écrire les requêtes SQL de création des tables avec leur clé primaire (en gras dans le
schéma suivant) et les contraintes suivantes :
• Les noms des segments, des salles et des postes sont non nuls.
• Le domaine de valeurs de la colonne ad s’étend de 0 à 255.
Remarque :
Il est recommandé de déclarer les contraintes NOT NULL en ligne, les autres
peuvent soit être déclarées en ligne, soit être nommées. Étudions à présent les types de
contraintes nommées (out-of-line).
Les quatre types de contraintes les plus utilisées sont les suivants :
CONSTRAINT nomContrainte UNIQUE (colonne1 [,colonne2]...)
PRIMARY KEY (colonne1 [,colonne2]...)
FOREIGN KEY (colonne1 [,colonne2]...)
REFERENCES nomTablePere [(colonne1 [,colonne2]...)]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
CHECK (condition)
La clause check exprime des contraintes portant soit sur un attribut, soit sur une
ligne. La condition elle-même peut être toute expression suivant la clause where dans
une requête SQL. Les contraintes les plus courantes sont celles consistant à restreindre
un attribut à un ensemble de valeurs.
Segment
indIP nomSegment etage
Salle
nSalle nomSalle nbPoste indIP
Poste
nPoste nomPoste indIP ad typePoste nSalle
B Destruction des tables
Écrire les requêtes SQL de destruction des tables
Exercice 2
A Insertion de données
Écrire les requêtes SQL pour insérer les données dans les tables suivantes :
Table Données
Segment INDIP NOMSEGMENT ETAGE
130.120.80 Brin RDC
130.120.81 Brin 1er étage
130.120.82 Brin 2e étage
Salle NSALLE NOMSALLE NBPOSTE INDIP
s01 Salle 1 3 130.120.80
s02 Salle 2 2 130.120.80
s11 Salle 11 2 130.120.81
s12 Salle 12 1 130.120.81
s21 Salle 21 2 130.120.82
s22 Salle 22 0 130.120.83
Poste NPOSTE NOMPOSTE INDIP AD TYPEPOSTE NSALLE
p1 Poste 1 130.120.80 01 TX s01
p2 Poste 2 130.120.80 02 UNIX s01
p3 Poste 3 130.120.80 03 TX s01
p4 Poste 4 130.120.80 04 PCWS s02
p5 Poste 5 130.120.80 05 PCWS s02
p8 Poste 8 130.120.81 01 UNIX s11
p9 Poste 9 130.120.81 02 TX s11
p10 Poste 10 130.120.81 03 UNIX s12
p11 Poste 11 130.120.82 01 PCNT s21
p12 Poste 12 130.120.82 02 PCWS s21
B Modification de données
Écrire la requête SQL qui permet de modifier (avec UPDATE) la colonne etage (pour
l’instant nulle) de la table Segment, afin d’affecter un numéro d’étage correct (0 pour le
segment 130.120.80, 1 pour le segment 130.120.81, 2 pour le segment 130.120.82).
Exercice 3
A Ajout de colonnes
Écrire la requête SQL pour ajouter les colonnes suivantes (avec ALTER TABLE).
Le contenu de ces colonnes sera modifié ultérieurement.
Table Nom, Type Signification des nouvelles colonnes
Segment nbSalle TINYINT(2) nombre de salles par défaut = 0.
nbPoste TINYINT(2) nombre de postes par défaut = 0.
Poste Passe varchar(10) mot de passe par défaut = 'Admin'.
B Modification de colonnes
Écrire la requête SQL pour :
• augmenter la taille dans la table Salle de la colonne nomSalle (passer à VARCHAR(30)) ;
• diminuer la taille dans la table Segment de la colonne nomSegment à VARCHAR(15) ;
Corrections
Exercice 1
A Création des tables
CREATE TABLE Segment
(indIP varchar(11),
nomSegment varchar(20) NOT NULL,
etage TINYINT(1),
CONSTRAINT pk_Segment PRIMARY KEY (indIP));
CREATE TABLE Salle
(nSalle varchar(7),
nomSalle varchar(20) NOT NULL,
nbPoste TINYINT(2),
indIP varchar(11),
CONSTRAINT pk_salle PRIMARY KEY (nSalle));
CREATE TABLE Poste
(nPoste varchar(7),
nomPoste varchar(20) NOT NULL,
indIP varchar(11),
ad varchar(3),
typePoste varchar(9),
nSalle varchar(7),
CONSTRAINT pk_Poste PRIMARY KEY (nPoste),
CONSTRAINT ck_ad CHECK (ad BETWEEN '000' AND '255'));
B Destruction des tables
DROP TABLE Poste;
DROP TABLE Salle;
DROP TABLE Segment;
Exercice 2
A Insertion de données
INSERT INTO Segment VALUES ('130.120.80','Brin RDC',NULL);
INSERT INTO Segment VALUES ('130.120.81','Brin 1er étage',NULL);
INSERT INTO Segment VALUES ('130.120.82','Brin 2ème étage',NULL);
INSERT INTO Salle VALUES ('s01','Salle 1',3,'130.120.80');
INSERT INTO Salle VALUES ('s02','Salle 2',2,'130.120.80');
INSERT INTO Salle VALUES ('s11','Salle 11',2,'130.120.81');
INSERT INTO Salle VALUES ('s12','Salle 12',1,'130.120.81');
INSERT INTO Salle VALUES ('s21','Salle 21',2,'130.120.82');
INSERT INTO Salle VALUES ('s22','Salle 22',0,'130.120.83');
INSERT INTO poste VALUES ('p1','Poste 1','130.120.80','01','TX','s01');
INSERT INTO poste VALUES ('p2','Poste 2','130.120.80','02','UNIX','s01');
INSERT INTO poste VALUES ('p3','Poste 3','130.120.80','03','TX','s01');
INSERT INTO poste VALUES ('p4','Poste 4','130.120.80','04','PCWS','s02');
INSERT INTO poste VALUES ('p5','Poste 5','130.120.80','05','PCWS','s02');
INSERT INTO poste VALUES ('p8','Poste 8','130.120.81','01','UNIX','s11');
INSERT INTO poste VALUES ('p9','Poste 9','130.120.81','02','TX','s11');
INSERT INTO poste VALUES ('p10','Poste 10','130.120.81','03','UNIX','s12');
INSERT INTO poste VALUES ('p11','Poste 11','130.120.82','01','PCNT','s21');
INSERT INTO poste VALUES ('p12','Poste 12','130.120.82','02','PCWS','s21');
B Modification de données
UPDATE Segment SET etage=0 WHERE indIP = '130.120.80';
UPDATE Segment SET etage=1 WHERE indIP = '130.120.81';
UPDATE Segment SET etage=2 WHERE indIP = '130.120.82';
Exercice 3
A Ajout de colonnes
ALTER TABLE Segment
ADD (nbSalle TINYINT(2) DEFAULT 0, nbPoste TINYINT(2) DEFAULT 0);
ALTER TABLE Poste ADD Passe varchar(10) DEFAULT 'Admin';
B Modification de colonnes
ALTER TABLE Salle MODIFY nomSalle VARCHAR(30);
ALTER TABLE Segment MODIFY nomSegment VARCHAR(15);
TD dictionnaire de données.
Une agence de voyage organise des circuits touristiques dans divers pays.
Les interviews effectuées auprès de la direction et des divers postes de travail ont permet de
recueillir les documents suivants.
CIRCUIT : Italie NORD Nombre de place : 20
Prix individuel : 6000F Accompagnateur : Durand Pierre
Liste des participants
Nom Acompte deuxième versement Remise Total
Dupont 3000 0 0 3000
Dubois 3000 2500 500 6000
Dupont M. 3000 3000 0 6000
Circuit N° 003 intitulé : Italie nord
Date départ Arrivée transport hôtel
Heure ville heure ville
20/03/88 2h paris 14h milan vol Af415 Palazzio
22/03/88 8h milan 1 5h bologne car
22/03/88 6h bologne 20h venisecar casa frolo
30/03/88 8h venise 11h paris vol AF754
Répertoire des villes par pays
Pays N° 02 Nom : Italie
Ville hôtel Adresse
Bologne Damartino piazza felice
Milan palazzio via palazzio
Venise casa floro giudecca
Fiche accompagnateur Fiche client
Nom : Durant Pierre Nom : Dupont
Adresse : 3 rue de belle ville 75020 paris Adresse : 143 rue Monge 75005 paris
CA : 5250
Etablir le dictionnaire des données.
Correction
Premier dictionnaire de données
Variable signification type longueur
NOCIR N° circuit N 3
NOMCIRC Nom circuit AN 30
PRIX Prix circuit N 4
NBPLACES NB de place N 2
NOACCOMP N° accompagnateur ? ?
NOMACCOMP Nom accompagnateur A 30
ADRACCOMP Adresse accompagnateur AN 60
RUEACCOMP Rue accompagnateur AN 30
VILLACCOMP Ville accompagnateur AN 30
DATE Date transport N 6
HEURE.D Heure départ N 2
TRANSPORT Inf. sur transport AN 30
VILL. Ville AN 30
NOM.H. Nom hôtel AN 30
ADR.H Adresse hôtel AN 30
HEURE.A Heure arrivée N 2
NOPYS N° pays N 2
NOMPAYS Nom pays A 30
NOCLL N° client ? ?
ADRCLI Adresse client AN 60
RUECLI Rue client AN 30
VILLECLI Ville client AN 30
[Link] CA client N 4
ACOMPTE compte versé N 4
VERSEMENT2 2e versement N 4
REMISE remise N 4
TOTAL total client pour un circuit N 4
Dictionnaire de données épuré
Variable signification type longueur
NOCIR N° circuit N 3
NOMCIRC Nom circuit AN 30
PRIX Prix circuit N 4
NBPLACES NB de place N 2
NOACCOMP N° accompagnateur ? ?
NOMACCOMP Nom accompagnateur A 30
ADRACCOMP Adresse accompagnateur AN 60
RUEACCOMP Rue accompagnateur AN 30
VILLACCOMP Ville accompagnateur AN 30
DATE Date transport N 6
HEURE.D Heure départ N 4
TRANSPORT Inf. sur transport AN 30
VILL.D Ville départ AN 30
NOM.H.D Nom hôtel départ AN 30
ADR.H D Adresse hôtel départ AN 30
VILLE.A Ville arrivée AN 30
NOM.H.A Nom hôtel arrivé AN 30
ADR.H.A Adresse hôtel arrivé AN 30
HEURE.A Heure arrivée N 4
NOPYS N° pays N 2
NOMPAYS Nom pays A 30
NOCLL N° client ? ?
ADRCLI Adresse client AN 60
RUECLI Rue client AN 30
VILLECLI Ville client AN 30
[Link] CA client N 4
ACOMPTE compte versé N 4
VERSEMENT2 2e versement N 4
REMISE remise N 4
TD MCD MLD
Une agence de location de maisons et d’appartements désire gérer sa liste de logements.
Vous disposé du dictionnaire de données suivant.
Donnez le MCD correspondant puis le MLD
Correction
MCD correspondant
MPD correspondant
Le MPD diffère du MLD puisque il tient compte de la valeur et de la longueur des données
utilisées.