Corrigés des exercices
UML 2 pour les bases de données


UML 2 pour les bases de données

  
Chapitre 3

Exercice 3-1
Le script SQL2 (qui s'apparente à Oracle du fait des types DATE et NUMBER) est le suivant. 


CREATE TABLE E3 
	(d DATE, g CHAR(12),CONSTRAINT pk_E3 PRIMARY KEY(d));
CREATE TABLE E2
	(b NUMBER, c DATE, f CHAR(40),
	 CONSTRAINT pk_E2 PRIMARY KEY(b));
CREATE TABLE E1
	(a CHAR(12), b NUMBER, d DATE, h NUMBER,
	 CONSTRAINT pk_E1 PRIMARY KEY(a),
 	 CONSTRAINT fk_E1_E2 FOREIGN KEY(b) REFERENCES E2(b),
 	 CONSTRAINT fk_E1_E3 FOREIGN KEY(d) REFERENCES E3(d));
CREATE TABLE E4
	(a CHAR(12), i CHAR(40), j DATE,
	 CONSTRAINT pk_E4 PRIMARY KEY(a,i),
 	 CONSTRAINT fk_E4_E1 FOREIGN KEY(a) REFERENCES E1(a));
La suppression des tables suivra l'ordre inverse à savoir E4 puis E1, E2 et enfin E3.

Exercice 3-2
1/ Le script SQL2 qui décrit les affrètmements d'avions par les compagnies est le suivant. Selon le SGBD utilisé, si "dateVol" comprend les heures et minutes, les colonnes "heure" et "minute" sont inutiles. 


CREATE TABLE Caracteristiques
	(typeAvion CHAR(10), nomAvion CHAR(30), npMax NUMBER,
	 CONSTRAINT pk_Caracteristiques PRIMARY KEY(typeAvion));
CREATE TABLE Compagnie
	(comp CHAR(8), nomComp CHAR(30),
	 CONSTRAINT pk_Compagnie PRIMARY KEY(comp));
CREATE TABLE Avion
	(immatriculation CHAR(6), capacite NUMBER, comp CHAR(8), 
       typeAvion CHAR(10),
	 CONSTRAINT pk_Avion PRIMARY KEY(immatriculation),
 	 CONSTRAINT fk_Avi_Compa FOREIGN KEY(comp) REFERENCES Compagnie(comp),
	 CONSTRAINT ck_capacite CHECK (capacite BETWEEN 50 AND 500),
 	 CONSTRAINT fk_Avi_Carac FOREIGN KEY(typeAvion) 
			REFERENCES Caracteristiques(typeAvion));
CREATE TABLE Affreter
	(immatriculation CHAR(6), comp CHAR(8),
	CONSTRAINT pk_Affreter PRIMARY KEY(immatriculation,comp),
	CONSTRAINT fk_Aff_Avi FOREIGN KEY(comp) REFERENCES Avion(immatriculation),
	CONSTRAINT fk_Aff_Compa FOREIGN KEY(comp) REFERENCES Compagnie(comp));
CREATE TABLE Vol
	(immatriculation CHAR(6), comp CHAR(8), dateVol DATE, heure NUMBER(2), 
	minute NUMBER(2), nbPax NUMBER, cout NUMBER,
	CONSTRAINT pk_Vol PRIMARY KEY(immatriculation,comp,dateVol,heure,minute),
 	CONSTRAINT ck_nbPax CHECK (nbPax BETWEEN 50 AND 500),
	CONSTRAINT fk_Vol_Aff FOREIGN KEY(immatriculation,comp) 
			REFERENCES Affreter(immatriculation,comp));
2/ Le script SQL2 (testé avec Oracle) qui décrit Castanet Télécoms est le suivant. 

CREATE TABLE Portable
 	(numPortable CHAR(14), typeCombine CHAR(20),
	 CONSTRAINT pk_Portable PRIMARY KEY(numPortable));
CREATE TABLE Internet
	(formule CHAR(8), prixpMois NUMBER, heureSupp NUMBER, 
	 CONSTRAINT pk_Internet PRIMARY KEY(formule ));
CREATE TABLE Abonne
	(numAbonne CHAR(10), nomAbonne CHAR(30), adresse CHAR(40), 
	 consoLocale NUMBER, consoNationale NUMBER, 
   	 consoInternet NUMBER, consoMobiles NUMBER, consoAutres NUMBER, 
	 debitLigne NUMBER, numTel CHAR(14),  formule CHAR(8), 
	 numPortable CHAR(14), CONSTRAINT pk_Abonne PRIMARY KEY(numAbonne),
	 CONSTRAINT fk_Abonne_Internet FOREIGN KEY(formule)
	 	REFERENCES Internet(formule),
	 CONSTRAINT fk_Abonne_Portable FOREIGN KEY(numPortable)
	 	REFERENCES Portable(numPortable));
CREATE TABLE Fournisseur
	(URL CHAR(200), adresseFourni CHAR(40), responsable CHAR(30), 
	 CONSTRAINT pk_Fournisseur PRIMARY KEY(URL));
CREATE TABLE Historique
	(formule CHAR(8), URL CHAR(200), premierContact DATE, finContrat DATE, 
	 CONSTRAINT pk_Historique PRIMARY KEY(formule,URL),
	 CONSTRAINT fk_Historique_Internet FOREIGN KEY(formule)
	 	REFERENCES Internet(formule),
	 CONSTRAINT fk_Historique_Fournisseur FOREIGN KEY(URL)
	 	REFERENCES Fournisseur(URL));
CREATE TABLE Reduction
	(numAbonne CHAR(10), telPrefere CHAR(14),dureePrefere NUMBER, 
	 CONSTRAINT pk_Reduction PRIMARY KEY(numAbonne,telPrefere),
	 CONSTRAINT fk_Reduction_Abonne FOREIGN KEY(numAbonne)
	 	REFERENCES Abonne(numAbonne));
3/ Le script SQL2 (testé avec MySQL) qui décrit la gestion de la compétition de voltigé est le suivant. 

CREATE TABLE Club
 	(nClub CHAR(10), aerodrome CHAR(6), nomclub CHAR(30),
	 CONSTRAINT pk_Club PRIMARY KEY(nClub));
CREATE TABLE Competiteur
	(ncomp CHAR(7),nomComp CHAR(20), pays CHAR(15),	 nClub CHAR(10),
	 CONSTRAINT pk_competiteur PRIMARY KEY(ncomp),
	 CONSTRAINT fk_comp_club FOREIGN KEY(nClub)
	 	REFERENCES Club(nClub));
CREATE TABLE Avion
	(immat CHAR(7), typeavion CHAR(20), nClub CHAR(10),
	 CONSTRAINT pk_avion PRIMARY KEY(immat),
	 CONSTRAINT fk_avi_club FOREIGN KEY(nClub)
	 	REFERENCES Club(nClub));
CREATE TABLE Utilisation
	(nComp CHAR(7), immat CHAR(7),
	 CONSTRAINT pk_Utilisation PRIMARY KEY(ncomp,immat),
	 CONSTRAINT fk_util_comp FOREIGN KEY(ncomp)
	 	REFERENCES competiteur(ncomp),
	 CONSTRAINT fk_util_avi FOREIGN KEY(immat)
	 	REFERENCES avion(immat));
CREATE TABLE Vol
	(ncomp CHAR(7), immat CHAR(7), datevol DATE, typeprogramme CHAR(5),
	 CONSTRAINT pk_Vol PRIMARY KEY(ncomp,immat,datevol),
	 CONSTRAINT fk_vjour_uti FOREIGN KEY(ncomp,immat)
	 	REFERENCES Utilisation(ncomp,immat));
CREATE TABLE Inventeur
	(ninventeur CHAR(10), nominv CHAR(20), typepilote CHAR(5),
	 CONSTRAINT pk_inventeur PRIMARY KEY(ninventeur));
CREATE TABLE Figure
	(nFigure CHAR(7), nomFig CHAR(20), notemax INTEGER, datecreation DATE,
	 pagemanuel INTEGER, ninventeur CHAR(10),
	 CONSTRAINT pk_figure PRIMARY KEY(nFigure),
	 CONSTRAINT fk_fig_inv FOREIGN KEY(ninventeur)
	 	REFERENCES inventeur(ninventeur));
CREATE TABLE Notes
	(ncomp CHAR(7), immat CHAR(7), datevol DATE, nFigure CHAR(7), 
        note INTEGER,
	 CONSTRAINT pk_Notes PRIMARY KEY(ncomp,immat,datevol,nfigure),
	 CONSTRAINT fk_Notes_Vol FOREIGN KEY(ncomp,immat,datevol)
	 	REFERENCES Vol(ncomp,immat,datevol),
 	 CONSTRAINT fk_Notes_fig FOREIGN KEY(nfigure)
	 	REFERENCES figure(nfigure));
CREATE TABLE Liste
	(ncomp CHAR(7), immat CHAR(7), datevol DATE, nFigure CHAR(7), 
       numeroChrono INTEGER,
	 CONSTRAINT pk_Liste PRIMARY KEY(ncomp,immat,datevol,nfigure),
	 CONSTRAINT fk_Liste_Vol FOREIGN KEY(ncomp,immat,datevol)
	 	REFERENCES Vol(ncomp,immat,datevol),
 	 CONSTRAINT fk_Liste_fig FOREIGN KEY(nfigure)
	 	REFERENCES figure(nfigure));

Exercice 3-3
3/ Le script SQL2 (testé avec Oracle) qui décrit la gestion des transports des agents est le suivant. 


CREATE TABLE Agent
	(codea VARCHAR(5), noma VARCHAR(40), 
	 CONSTRAINT pk_Agent PRIMARY KEY(codea));
CREATE TABLE Chantier
	(numch NUMBER, adresse VARCHAR(20),
	 CONSTRAINT pk_chantier PRIMARY KEY(numch));
CREATE TABLE Vehicule
	(nimmat VARCHAR(10), totalkm NUMBER,
	 CONSTRAINT pk_vehi PRIMARY KEY(nimmat));
CREATE TABLE Mission
	(nimmat VARCHAR(10), datem DATE, km NUMBER, codea VARCHAR(5), 
       numch NUMBER, 
	 CONSTRAINT pk_Mission PRIMARY KEY(nimmat,datem),
	 CONSTRAINT fk_Mission_Chantier FOREIGN KEY(numch) 
			REFERENCES chantier(numch),
	 CONSTRAINT fk_Mission_Vehicule FOREIGN KEY(nimmat) 
			REFERENCES vehicule(nimmat),
	 CONSTRAINT fk_Mission_Agent FOREIGN KEY(codea) 
			REFERENCES Agent(codea));
CREATE TABLE Transporter
	(nimmat VARCHAR(10), datem DATE, codea VARCHAR(5),
	 CONSTRAINT pk_Transporter PRIMARY KEY(nimmat,datem,codea),
	 CONSTRAINT fk_Transporter_Mission FOREIGN KEY(nimmat,datem) 
			REFERENCES Mission(nimmat,datem),
	 CONSTRAINT fk_Transporter_Agent FOREIGN KEY(codea) 
			REFERENCES Agent(codea));