1 Bases de Données Avancées: Bases de Données Relationnelles 12 novembre 2013 Dr. Rim Samia...

1

Bases de Données

Avancées:

Bases de Données Relationnelles

11 avril 2023

Dr. Rim Samia Kaabi

2

SQL

SQL

Un SGBD relationnel fournit un ensemble de services pour :

• définir et maintenir les schémas (LDD : langage de définition de données),

• créer, rechercher, mettre à jour les informations (LMD : langage de manipulation de données).

INTRODUCTION

SQL fournit aussi bien le LDD que le LMD

Création d'une relation (CREATE TABLE)

Description des relations avec:

SQL : langage de définition de données

• domaine de chaque attribut,

• définition de certaines contraintes d'intégrité sur les attributs.

Format général :

CREATE TABLE <relation>

( <attribut> <domaine> [ <contrainte d’attribut> ] ,...

[ <contrainte de relation> , ... ] )

• Domaines :

caractère(s) : CHARACTER[(n)], ou CHAR[(n)]

numérique exact : INTEGER ou INT, SMALLINT, NUMERIC(l,d), DECIMAL(l,d)

numérique non exact :

FLOAT (p), REAL,

DOUBLE PRECISION

temporels : DATE, TIME, TIMESTAMP (= date-heure)



• Contraintes d'attribut (pour spécifier des contraintes sur un seul attribut) :

valeur obligatoire : NOT NULL

unicité de l'attribut : UNIQUE , PRIMARY KEY

contrainte référentielle :

REFERENCES <relation> [(<clé référencée>)]

contrainte générale : CHECK (<expression logique>)



• Contrainte de relation (pour spécifier des contraintes sur plusieurs attributs):

contrainte d'unicité :

UNIQUE , PRIMARY KEY (<attributs>,...)

contrainte référentielle :

FOREIGN KEY ( <clé étrangère>,...)

REFERENCES <relation > [(clé référencée)]

contrainte générale :

CHECK (<expression logique>)



Exemples : création des relations suivantes :



• EMPLOYE(NoEmp, Nom, Année, NoDep)

• DEPART(NoDep, Intitulé, Taille, NoResp)

• PROJET(NoProj, NoChef)

• TRAVAILLE(NoEmp, NoProj, Temps)

CREATE TABLE EMPLOYE (

NoEmp INT NOT NULL, Nom CHAR(20),

Année NUMERIC(4), NoDep INT NOT NULL,

PRIMARY KEY (NoEmp),

FOREIGN KEY (NoDep) REFERENCES DEPART);

CREATE TABLE DEPART (

NoDep INT NOT NULL,

Intitulé CHAR(15), Taille INT, NoResp INT,

PRIMARY KEY (NoDep),

FOREIGN KEY (NoResp) REFERENCES EMPLOYE(NoEmp));

CREATE TABLE PROJET (

NoProj INT NOT NULL,

NoChef INT,

PRIMARY KEY (NoProj),

FOREIGN KEY (NoChef) REFERENCES EMPLOYE(NoEmp));


Création d'une relation (CREATE TABLE) (Exemples)

CREATE TABLE TRAVAILLE (

NoEmp INT NOT NULL,

NoProj INT NOT NULL,

Temps INT CHECK (Temps BETWEEN 1 AND 100),

PRIMARY KEY (NoEmp, NoProj) );

Remarques :

• La clause NOT NULL impose qu'une valeur soit donnée lors de la création de chaque tuple.

• La clause CHECK indique que le temps doit être compris entre 1 et 100 (c'est un pourcentage).



Modification d'une relation (ALTER TABLE)

Ajout, modification ou suppression d’attributs d'une table existante.


ALTER TABLE <table>

ADD < <attribut> <domaine> [<contrainte d'attribut>] > ,...

MODIFY < <attribut> [<domaine>] [<contrainte d'attribut>] > ,...

DROP <attribut>,...

Format général :

Modification d'une relation (ALTER TABLE)

Exemple : modification de la longueur de l'attribut Nom de la relation EMPLOYE.

ALTER TABLE EMPLOYE MODIFY Nom CHAR(30);

Remarque: Selon le type de modification, les valeurs existantes peuvent être conservées, modifiées (tronquées par exemple) ou perdues. Deux SGBD différents peuvent d'ailleurs avoir des actions différentes.


suppression d’une table:

Destruction d'une relation (DROP TABLE)


+ des données qu’elle contient,

+ de sa définition dans le dictionnaire,

+ des index associés.

Format général : DROP TABLE <table>

Exemple : destruction de la table TRAVAILLE

DROP TABLE TRAVAILLE

Avantages d'un index :

accélère certaines opérations : sélections ou tris (clauses WHERE et ORDER BY en SQL) , ou jointure.

Inconvénients d’un index :

traitements alourdis pour une table très petite, ou sur un attribut dont les mêmes valeurs se répètent beaucoup.

Création d'un index (CREATE INDEX)


Format général :

CREATE INDEX <index> ON <table> (<attribut> [,<attribut]...)

Exemple : CREATE INDEX NOMEMP ON EMPLOYE (Nom)

Certains SGBD créent eux-mêmes certains index, sur les clés primaires par exemple.

Format général :

DROP INDEX <nom d'index>

Normalement, dans une base donnée, deux index distincts doivent avoir des noms différents.

Exemple : DROP INDEX NOMEMP

Suppression d'un index (DROP INDEX)


Format simplifié :

INSERT INTO <table> [ ( <attribut>,... ) ]

VALUES ( <valeur>,...> );

Exemple : insertion de l'employé Dupont, en 1978, dans le département 03, avec le n° 1045.

INSERT INTO EMPLOYE VALUES (1045,'Dupont',1978,03);

Insertion de données (INSERT)

SQL : langage de manipulation de données

Interrogation synthèse de la base de donnée :

Interrogation des données (SELECT)


• sur 1 table,

• sur n tables,

• possibilité de réaliser la plupart des opérations de l'algèbre relationnelle : sélection, projection, jointure (et produit cartésien), union, intersection, différence.

résultat = un ensemble de tuples, éventuellement:

• ordonnés selon une clé (précisée dans le SELECT),

• regroupés selon certains critères ( " " ),

etc.

Format général :

SELECT [ALL | DISTINCT] <expression résultat>,...

FROM < <table> [ <alias-select>] > ,...

[ WHERE <condition>]

[ GROUP BY <attribut>,...]

[ HAVING <condition> ]

[ ORDER BY < <attribut> [ ASC | DESC] >,... ]



<expression résultat>:

expression où interviennent les attributs des tables utilisées. L'expression la plus simple, et la plus usuelle est la référence à un attribut.

Problème de la qualification: Si deux attributs de deux tables référencées ont le même nom, il est nécessaire de les préfixer avec les alias-select s'il en est défini, sinon avec les noms des tables : EMPLOYE.NoDep et DEPART.NoDep par exemple, désignent respectivement l'attribut NoDep des tables EMPLOYE et DEPART.



DISTINCT : élimination des tuples en double dans le résultat.

ALL : valeur par défaut.

opérateurs courants dans les expressions de résultat :



+ , - , * , / Opérateurs arithmétiques

< , > , = , <> , IS NULL Opérateurs relationnels

AND, OR, NOT Opérateurs logiques

|| Opérateur de concaténation (=mise bout à bout de deux chaînes)

fonctions utilisables dans les expressions de résultats. Seules les fonctions d'agrégat seront vues, avec l'option GROUP BY.

nom des colonnes du résultat : nom de l'attribut ou nom généré par le SGBD, par exemple SOMME DE ... si on a utilisé la fonction SUM(...). On peut aussi définir le titre de colonne avec une sous-option AS.



Exemple :

SELECT Nom, NoEmp AS 'N° Employé',

NoDep AS 'N° de son département'

FROM EMPLOYE ORDER BY Nom;

Remarque : Le caractère * représente l'ensemble des attributs intervenant dans le SELECT, par exemple SELECT * FROM EMPLOYE, donnera les valeurs de tous les attributs de tous les tuples de la table EMPLOYE.



FROM :

• introduit la liste des tables impliquées dans la commande SELECT.

• Chaque nom de table peut être suivi d'un alias-select qui n'est défini que le temps de l'exécution du SELECT.

• Lorsque FROM introduit plusieurs tables, la commande SELECT porte sur le produit cartésien de ces tables. C'est la condition introduite par la clause WHERE qui, éventuellement, transformera le SELECT en une jointure.



Exemple :

SELECT NoEmp, Nom FROM EMPLOYE;

SELECT EM.NoEmp, EM.Nom FROM EMPLOYE EM;

Exemple ou l'alias est plus utile (jointure) : liste des noms des employés, avec l'intitulé de leur département :

SELECT EM.Nom, DE.Intitule

FROM EMPLOYE EM, DEPART DE

WHERE EM.NoDep = DE.NoDep;



WHERE : introduit une condition de sélection, et/ou une condition de jointure selon les cas.

Exemple : liste des noms des employés engagés avant 1990, avec leur n° de département :

SELECT Nom, NoDep FROM EMPLOYE

WHERE Année < 1990;

Exemple : même question avec l'intitulé du département au lieu du n° :

SELECT EM.Nom, DE.Intitulé


WHERE EM.NoDep = DE.NoDep and Année < 1990;



Opérateurs utilisables dans les expressions suivant WHERE :



- opérateurs relationnels habituels : < , > , = , <> ,

- opérateur relationnel «composé» : BETWEEN,

- opérateurs logiques : AND, OR, NOT,

- opérateurs ensemblistes :

IN, NOT IN, ANY ou SOME, ALL, EXISTS,

- opérateur de recherche de chaîne de caractères:

LIKE,

Opérateur BETWEEN:

exemple : Temps BETWEEN 1 and 100

Opérateur LIKE : permet de tester la présence de chaîne de caractères précise dans un attribut de type chaîne de caractères.



_ (caractère "souligné") représente un seul caractère quelconque

% représente n'importe quelle chaîne de caractères, le caractère.

tout autre caractère se représente lui-même...

Exemples :

- Nom LIKE 'DU%' : tous les noms commençant par DU

(DUPONT, DURAND, DUX, DU, DULAC,...).

- Nom LIKE 'DUPON_' : noms de 6 c débutant par DUPON

(DUPOND, DUPONT, DUPONS,...)

Opérateurs IN et NOT IN : servent à introduire soit une liste de valeurs entre parenthèses, soit une sous-requête générant une liste de valeur.

Exemples :

NoDep IN (03, 05, 09) vrai si NoDep est un de ces numéros.

SELECT NoEmp, Nom sélectionne les employés consacrant FROM EMPLOYE plus de la moitié de leur temps sur un

WHERE NoEmp IN seul projet( SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50 );



Exemples (suite):

SELECT NoEmp, Nom (id. requête précédente)FROM EMPLOYE EM, TRAVAILLE TRWHERE EM.NoEmp = TR.NoEmp AND TR.Temps > 50;

SELECT NoEmp, Nom les employés qui ne consacrentFROM EMPLOYE pas plus de la moitié de leurWHERE NoEmp NOT IN temps sur chacun des projets

( SELECT NoEmp auxquels ils participent FROM TRAVAILLE

WHERE Temps > 50 );



Opérateurs ALL et ANY: l'élément placé à gauche est comparé (<, >, <>, =, >=, <=) à la liste située à droite. Comme avec IN, la liste peut être explicite, ou le résultat d'une sous-requête.

Avec ALL: la condition est vraie si la comparaison est vraie avec toutes les valeurs de la liste. Si la liste est vide, le résultat est vrai.

Avec ANY ou SOME: la condition est vraie si elle l'est pour au moins une valeur de la liste. Si la liste est vide, le résultat est faux.



Exemples (ALL, ANY):

SELECT * les employés qui ont uneFROM EMPLOYE ancienneté supérieureWHERE Année < ALL à chacun des employés

( SELECT Année du département n° 03. FROM EMPLOYE

WHERE NoDep = 03 );

SELECT * les employés qui ont uneFROM EMPLOYE ancienneté supérieure à au WHERE Année < ANY moins un des employés

( SELECT Année du département n° 03. FROM EMPLOYEWHERE NoDep = 03 );



32

Opérateur EXISTS : introduit une sous-requête. Le résultat est vrai si le résultat de cette sous-requête n'est pas vide, il est faux dans le cas contraire.

Exemple :

SELECT * les départements non vides!FROM DEPARTWHERE EXISTS

( SELECT * FROM EMPLOYEWHERE DEPART.NoDep = EMP.NoDep );

On peut utiliser aussi NOT EXITS.

Bien souvent, EXISTS permet de faire la même requête qu'avec une jointure, mais de façon plus compliquée.



reprendre toutes les requêtes SQL données en exemple pour l'instruction SELECT, jusqu'à ce point, pour les refaire dans le langage de requête algébrique.



Exercice

SELECT Nom, NoEmp AS 'N° Employé',

NoDep AS 'N° de son département'

FROM EMPLOYE ORDER BY Nom;



EMPLOYE

Nom,NoEmpTitres ?

Ordre ?

SELECT EM.Nom, DE.Intitule


WHERE EM.NoDep = DE.NoDep;



EMPLOYE DEPART

Nom,Intitulé

NoDep = NoDep

EMPLOYE

Nom,NoDep

Année< 1990



SELECT Nom, NoDep FROM EMPLOYE

WHERE Année < 1990;

SELECT EM.Nom, DE.Intitulé


WHERE EM.NoDep = DE.NoDep and Année < 1990;



EMPLOYE DEPART

Nom,Intitulé

NoDep = NoDepet

Année < 1990

SELECT NoEmp, NomFROM EMPLOYEWHERE NoEmp IN

( SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50 );



NoEmp,Nom

Temps > 50

TRAVAILLE

EMPLOYE

NoEmp = NoEmp

SELECT NoEmp, NomFROM EMPLOYEWHERE NoEmp NOT IN

( SELECT NoEmp FROM TRAVAILLE

WHERE Temps > 50 );



TRAVAILLE

EMPLOYE

NoEmp = NoEmp

NoEmp, Nom

-

EMPLOYE

NoEmpNoEmp

Temps > 50

SELECT * les employés qui ont uneFROM EMPLOYE ancienneté supérieureWHERE Année < ALL à chacun des employés

( SELECT Année du département n° 03. FROM EMPLOYE

WHERE NoDep = 03 );



EMPLOYE E1EMPLOYE E2

E1. Année > E2.Année

E1.NoEmp, E1.Nom,E1.Année, E1.NoDep

-

EMPLOYE

NoDep = 03

SELECT * les employés qui ont uneFROM EMPLOYE ancienneté supérieure à au WHERE Année < ANY moins un des employés

SELECT Année du département n° 03. FROM EMPLOYE

WHERE NoDep = 03 );

EMPLOYE E1 EMPLOYE E2

E1. Année < E2.Année

NoDep = 03

E1.NoEmp, E1.Nom,E1.Année, E1.NoDep



Autre solution SQL pour :«les employés qui ont une ancienneté supérieure à au moins un des employés du département n° 03.

SELECT distinct E1.* FROM EMPLOYE E1, EMPLOYE E2WHERE E2.NoDep = 03 and E1.Année < E2.Année;



SELECT * les départements non vides!FROM DEPARTWHERE EXISTS

( SELECT * FROM EMPLOYEWHERE DEPART.NoDep = EMP.NoDep );

EMPLOYE

DEPART

NoDep

NoDep = NoDepSELECT D.*

FROM DEPART D, EMPLOYE EWHERE D.NoDep = E.NoDep;



GROUP BY : indique sur quel(s) attribut(s) les tuples sélectionnés doivent être agglomérés. Ces attributs doivent impérativement apparaître dans la liste de résultats du SELECT, en plus des fonctions statistiques d'agrégats qui vont de paire avec la clause GROUP BY.

Les fonctions d’agrégat les plus courantes sont :



• SUM() : c'est la somme des valeurs de l'attribut donné en paramètre, qui apparaîtra pour chaque agrégat.

• AVG() : idem pour la moyenne.

• MIN() : idem pour le minimum.

• MAX() : idem pour le max.

• COUNT(*) : donne le nombre de tuples.

Remarque : Ces fonctions, utilisées seules dans un SELECT, sans la clause GROUP BY, fonctionnent sur la totalité des tuples sélectionnés, comme s'il n'y avait qu'un groupe.

Exemple: en supposant qu'il existe un attribut salaire dans la table EMPLOYE, donner pour chaque département sa masse salariale :

SELECT NoDep, SUM(salaire)

FROM EMPLOYE

GROUP BY NoDep;



GROUP BY(Suite):

HAVING :

- introduit une condition sur les groupes,

- lié à la clause GROUP BY,

- seuls les groupes pour lesquels la condition de la clause HAVING est vraie sont conservés dans le résultat.

Exemple : liste des projets ayant plus de 3 employés y travaillant, en indiquant le n° du projet, le n° du chef de projet, et le nombre d'employés qui y travaillent :



SELECT pr.NoProj, pr.NoChef, count(*)FROM PROJET PR., TRAVAILLE TRWHERE pr.NoProj = TR.NoProjGROUP BY pr.NoProjHAVING count(*) > 3;

ORDER BY : introduit la ou les colonnes du résultat sur lesquelles un tri doit être fait. Une colonne peut être désignée par son nom (attribut ou nom introduit par AS), ou par sa position dans la liste de résultats.

Exemple : liste des employés, par département, dans l'ordre des départements, et par ordre alphabétique dans chaque département.

SELECT NoDep, Nom, NoEmp, AnnéeFROM EMPLOYEORDER BY NoDep, Nom;



Remarque : on peut utiliser une sous-requête (SELECT) pour insérer plusieurs tuples dans une seule table, en une seule fois.

Exemple : insérer dans une table PRIMEPROJ, de même schéma que EMPLOYE, un tuple pour chaque chef de projet:

INSERT INTO PRIMEPROJSELECT DISTINCT PR.NoChef, EM.Nom, EM.Année, EM.NoDepFROM PROJET PR, EMPLOYE EMWHERE PR.NoChef = EM.NoDep;



Combinaisons ensemblistes de requêtes :

UNION : union ensembliste des résultats de 2 SELECT.

MINUS ou EXCEPT: différence.

INTERSECT : intersection.

Exemple : liste des n° des employés qui travaillent dans un service de plus de 10 personnes ou qui occupent plus de 50% de leur temps sur un seul projet :

SELECT NoEmp FROM EMPLOYE EM, DEPART DE

WHERE EM.NoDep = DE.NoDep AND DE.Taille>10

UNION

SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50;



Réalisation des opérations de l’algèbre relationnelle en SQL

Sélection : clause WHERE dans SELECT.


Table_X

ConditionSELECT * FROM TABLE_X

WHERE Condition;

Projection : liste-résultat de SELECT.



SELECT A1,A2,A3 FROM .... A1,A2,A3

....

Produit cartésien : naturellement, par la présence de plusieurs tables dans la clause FROM.



SELECT * FROM R1,R2;X

R1 R2

Jointure : produit cartésien puis sélection.

exemple de Jointure naturelle : (attribut A en commun)



SELECT * FROM R1,R2

WHERE R1.A = R2.A;

R1 R2

R1.A = R2.A

Opérations ensemblistes :



Union : UNION.

Différence : MINUS ou EXCEPT.

Intersection : INTERSECT.

Division : sans l'opérateur DIVIDE, qui est généralement absent de SQL, ce n'est pas très facile.

Attention aux schémas des opérandes

permet de modifier un ou plusieurs attributs dans un ou plusieurs tuples d'une table.

Format général :

UPDATE <table>

SET <attribut> = { <expression de valeur> | NULL },...

WHERE <condition>;

Mise à jour des données (UPDATE)


Exemple : corriger l'année d'embauche de M. Martin, en la mettant à 1980 :

Mise à jour des données (UPDATE)


UPDATE EMPLOYESET Année = 1980WHERE Nom = 'Martin';

Exemple : recalculer la taille de chaque département :

UPDATE DEPART deSET Taille =

( SELECT count(*) FROM EMPLOYE em

WHERE de.NoDep = em.NoDep );

permet de supprimer un ou plusieurs tuples.

Format général :

DELETE [FROM] <table>

WHERE <condition>;


Suppression de données (DELETE)

DELETE FROM PROJETWHERE NoProj NOT IN

( SELECT NoProj FROM TRAVAILLE );

Exemple : suppression des projets sur lesquels personne ne travaille :

58

Jointure par blocs emboîtés : IN ()

Nom et couleur des produits livrés par le fournisseur 1

Solution 1 : la jointure déclarativeSELECT nomp, couleur FROM P, PUFWHERE PUF.np = P.np AND nf = 1 ;

Solution 2 : la jointure procédurale (par emboîtement)

1. Ensemble des produits livrés par le fournisseur 1

2. Nom et couleur des produits du fournisseur 1

SELECT nomp, couleur FROM PWHERE np IN

( SELECT np FROM PUF WHERE nf =

1) ;

59

Composition de conditions

Nom des fournisseurs qui approvisionnent une usine de Londres ou de Paris en un produit rouge

SELECT nomf FROM FWHERE nf IN

(SELECT nf FROM PUF WHERE np IN

(SELECT np FROM P

WHERE couleur = 'rouge') AND nu IN

(SELECT nu FROM U WHERE ville = 'Londres' OR ville = 'Paris') ) ;

SELECT nomf

FROM PUF, P, F, U

WHERE couleur = 'rouge'

AND PUF.np = P.np

AND PUF.nf = F.nf

AND PUF.nu = U.nu

AND (U.ville = 'Londres'

OR U.ville = 'Paris');

60

Conditions sur des ensembles: EXISTS

• Test si l’ensemble n’est pas vide (E )• Noms des fournisseurs qui fournissent au moins un produit rouge

SELECT nomfFROM FWHERE EXISTS

( SELECT * FROM PUF, P

WHERE PUF.nf = F.nf AND PUF.np = P.np AND P.couleur = 'rouge' ) ;

61

Quantificateur universel en SQL (Division)

Noms des fournisseurs qui fournissent tous les produits rouges

SELECT nomfFROM FWHERE NOT EXISTS

( SELECT * FROM P WHERE P.couleur = 'rouge' AND NOT EXISTS

( SELECT * FROM PUF WHERE PUF.nf = F.nf AND PUF.np =

P.np )) ;

1 Bases de Données Avancées: Bases de Données Relationnelles 12 novembre 2013 Dr. Rim Samia...

Documents

Transcript of 1 Bases de Données Avancées: Bases de Données Relationnelles 12 novembre 2013 Dr. Rim Samia...