nicolas zozor 2012nzozor@gmail.com. retour sur les clés une clé primaire est une clé dune table...

Nicolas Zozor 2012nzozor@gmail.com

Retour sur les clés Une clé primaire est une clé d’une table qui est composée

d’un ou plusieurs (dit clé composée) attributs (colonnes) de la table et est utilisée pour identifier chaque ligne de manière unique. La clé primaire ne doit pas contenir de colonne valeur NULL.

Une clé unique possède les mêmes propriétés qu’une clé primaire à l’exception qu’une colonne définie comme clé unique peut être NULL. Elle contrôle l’unicité de la valeur

Une clé étrangère représente une relation entre les tables. Elle est composée d’une ou plusieurs attributs (colonnes) dont les valeurs dépendent de clés primaires (ou uniques) d’autres tables

On parle d’intégrité référentielle quand on veut signifier que les relations représentées par les clés sont maintenues. Elle assure la cohérence des données.

Entité et tables Lorsque l’on crée la base de donnée, l’entité devient une

table.

Requête Une requête est une question posée pour interroger une base

de donnée Permet d’extraire des données d’une ou plusieurs tables

Algèbre relationnelle

Outils de traitement permettant de formaliser les opérations pouvant être réalisées sur un schéma relationnel.

Opérateurs algébriques: (SELECTION,PROJECTION,JOINTURE)

Opérateurs ensemblistes:(UNION, INTERSECTION, DIFFERENCE, PRODUIT

CARTESIEN)

PROJECTION

Consiste à ne retenir que certains attributs(colonnes) d’une relation( 1 relation = 1 entité )

Le nombre de colonnes se trouve ainsi limité

PROJECTION Client(codeClient, nom, prénom, adresse,

codePostal, ville, telephone, mail) Materiels(codeMateriel, Désignation, Fabricant,

dateAchat, #codeClient) Interventions(numInterventions, descriptifPannne,

dateIntervention, temps_passé, #codeMateriel, #codeInterventions)

TypesInterventions(codeIntervention, designation, prixHoraire)

PiècesNeuves(refPièceNeuve, libellé, prix) Utiliser(#numInterventions,#refPièceNeuve, quantité)

PROJECTION

codeClient

nom prénom adresse codePostal

ville telephone

mail

230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@

231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@

232 Judor Eric Rue H 97200 FDF 05969 Judor@

Table Client : Mode feuille de données

Table d’origine EXEMPLE 1

PROJECTION

R1 = SELECT * FROM Client; * sélectionne tous les attributs de la table

EXEMPLE 1

PROJECTION

codeClient

nom prénom adresse codePostal

ville telephone

mail

230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@

231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@

232 Judor Eric Rue H 97200 FDF 05969 Judor@

Table Client : Mode feuille de données

Résultat de la requête: EXEMPLE 1

PROJECTION

codeClient

nom prénom adresse codePostal

ville telephone

mail

230 Vins Diesel Rue F1 V8769 Los Ang 08600 Vins@

231 Deep Johnny Pont 9 97233 Cap Est 07990 Deep@

232 Judor Eric Rue H 97200 FDF 05969 Judor@

Table Client : Mode feuille de données

Table d’origine EXEMPLE 2

PROJECTION

R1 = SELECT nom, prénom FROM Client; “Nom » et « prénom » sont 2 attributs de la table

client

EXEMPLE 2

PROJECTION

nom prénom

Vins Diesel

Deep Johnny

Judor Eric

Table Client : Mode feuille de données

Résultat de la requête: EXEMPLE 2

SELECTION

Consiste à n’afficher que certaines lignes d’une table en utilisant:

Opérateurs de comparaison: <, <=, >, >= Connecteurs logiques: AND, OR, NOT Opérateurs ensemblistes: IN, BETWEEN, LIKE La clause NULL (champs vide)

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origine EXEMPLE 1

SELECTION

R1 = SELECT * FROM Client WHERE nbFilms = 51; Sélectionner tous les attributs des clients ayant

joués 51 films

EXEMPLE 1

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

EXEMPLE 1

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 2

SELECTION

R1 = SELECT * FROM Client WHERE nbFilms = 5; Sélectionner tous les attributs des clients ayant

joués dans 5 films

EXEMPLE 2

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

232 Judor Eric Rue H 97200 FDF 5

EXEMPLE 2

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 3

SELECTION

R1 = SELECT * FROM Client WHERE nbFilms > 50; Sélectionner tous les attributs des clients ayant

joués dans 50 films et plus

EXEMPLE 3

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

EXEMPLE 3

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 4

SELECTION

R1 = SELECT nom, prénom FROM Client WHERE nbFilms > 50;

Sélectionner les noms et prénoms des clients ayant joués dans 50 films et plus

EXEMPLE 4

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

nom prénom

Vins Diesel

Deep Johnny

EXEMPLE 4

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 5

SELECTION

R1 = SELECT * FROM Client WHERE nbFilms BETWEEN 2 AND 52;

Sélectionner tous les attributs des clients ayant joués entre 2 et 52 films

EXEMPLE 5

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

232 Judor Eric Rue H 97200 FDF 5

EXEMPLE 5

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 FDF 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 6

SELECTION

R1 = SELECT * FROM Client WHERE nom LIKE ‘de*’ 

Sélectionner tous les attributs des clients ayant un nom commençant par ‘de’

EXEMPLE 6

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

231 Deep Johnny Pont 9 97233 Cap Est 55

EXEMPLE 6

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 NULL 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 7

SELECTION

R1 = SELECT * FROM Client WHERE ville IS NULL Sélectionner tous les attributs des clients ayant

l’attribut ville vide (null)

EXEMPLE 7

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

232 Judor Eric Rue H 97200 NULL 5

EXEMPLE 7

SELECTION

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

232 Judor Eric Rue H 97200 NULL 5

Table Client : Mode feuille de données

Table d’origineEXEMPLE 8

SELECTION

R1 = SELECT * FROM Client WHERE ville IS NOT NULL

Sélectionner tous les attributs des clients n’ayant pas l’attribut ville vide (null)

EXEMPLE 8

SELECTION

Table Client : Mode feuille de données

Résultat de la requête:

codeClient

nom prénom adresse codePostal

ville nbFilms

230 Vins Diesel Rue F1 V8769 Los Ang 51

231 Deep Johnny Pont 9 97233 Cap Est 55

EXEMPLE 8

DISTINCT

Il est possible supprimer les doublons en ajoutant la close DISTINCT

SELECT DISTINCT NomColonne FROM TABLE

JOINTURE

Une jointure Permet d’obtenir des données provenant de plusieurs tables

JOINTURE

Schéma relationnel AUTEURS(NumAuteur, NomAuteur, EmailAuteur,) ARTICLES(NumArti, TitreArti, TexteArti, DateArti, #NumAuteur)

C.I.F

EXEMPLE 1

JOINTUREEXEMPLE 1

NumAuteur NomAuteur PrenomAuteur EmailAuteur

1 Dubois Marcel m.dubois@email.com

2 Dupont Jacques jacques.dupont@email.com

3 Leroy Nicole Nicole.leroy@email.com

Auteurs

JOINTUREEXEMPLE 1

NumArti

TitreArti TextArti DateArti #NumAuteur

1 Wall Street xxxxxxxxxx 12/12/2011 2

2 La Fed blablabla 05/05/2007 1

3 La loi Hadop yyyyyyyyyy 03/03/2009 2

Articles

JOINTURE

Afficher tous les titres d’articles écrit part Dupont Jacques. Afficher également l’e-mail de l’auteur correspondant:

R1 = SELECT TitreArti, EmailAuteur

FROM ARTICLE,AUTEUR

WHERE ARTICLE.NumAuteur = Auteur.NumAuteur

AND AUTEUR.NumAuteur = 2

EXEMPLE 1

JOINTUREEXEMPLE 1

TitreArti EmailAuteur

Wall Street jacques.dupont@email.com

La loi Hadop jacques.dupont@email.com

Résultat de la requête

JOINTURE

Schéma relationnel FOURNISSEUR(NumFrs, NomFrs, AdrFrs, VilleFrs) ARTICLE(NumArt, LibArt, Stock, PrixUnitHT) ACHAT(#NumFrs,#NumArt, PrixAchat, Délai, EnCde)

C.I.M

EXEMPLE 2

JOINTURE

NumFrs NomFrs AdrFrs VilleFrs

230 Gcom Rue F1 FDF

231 Exnet Pont 9 Lamentin

232 Webplus Rue H FDF

Table Client : Mode feuille de données

Table FournisseurEXEMPLE 2

JOINTURE

NumArt LibArt Stock PrixUnitHT

100 PCportable 20 450

101 TelPortabl 50 70

102 clavierUsb 100 30

103 HautParleur

200 100

Table ARTICLE: Mode feuille de données

Table ArticleEXEMPLE 2

JOINTURE

#NumFrs,#NumArt PrixAchat Délai EnCde

230,100 350 6 validé

232,102 20 9 validé

231,101 50 2 validé

Table ACHAT: Mode feuille de données

Table AchatEXEMPLE 2

JOINTURE

Déterminer le Numéro et délai de livraison des articles pour lesquels le prix unitaire est compris entre 50€ et 500€

R1 = SELECT NumArt, Délai

FROM ACHAT,ARTICLE

WHERE ACHAT.NumArt = ARTICLE.NumArt

AND PrixUnitHt BETWEEN 50 AND 500

EXEMPLE 2

JOINTURE

NumFrs NumArt Délai

230 100 6

231 101 2

EXEMPLE 2

FONTIONS D’AGREGATION Permettent d’effectuer des calculs

statistiques COUNT() SUM() AVG() MAX() MIN() STDEV() VAR()

COUNT

Dénombre le nombre d’éléments de la colonne sélectionnée

COUNT

NumArt LibArt Stock PrixUnitHT

100 PCportable 20 450

101 TelPortabl 50 70

102 clavierUsb 100 30

103 HautParleur

200 100

Table ARTICLE: Mode feuille de données

Table ArticleEXEMPLE 1

COUNT

Compter le nombre d’articles de la table ARTICLES

R1 = SELECT COUNT(NumArt) AS [Nombre d’articles]

FROM ARTICLES

EXEMPLE 1

COUNT

Nombre d’articles

4

Résultat de la requêteEXEMPLE 1

COUNT

Compter le nombre de ligne dela table ARTICLES

R1 = SELECT COUNT(*) AS [Nombre d’articles]

FROM ARTICLES

EXEMPLE 2

COUNT

Nombre d’articles

4

Résultat de la requêteEXEMPLE 1

SUM

Additionne les valeurs d’un champ numérique

Calcule la somme d’une colonne Ignore les valeurs NULL (champs vides)

SUM

NumArt LibArt Stock PrixUnitHT

100 PCportable 20 450

101 TelPortabl 50 70

102 clavierUsb 100 30

103 HautParleur

200 100

Table ARTICLE: Mode feuille de données

Table ArticleEXEMPLE 1

SUM

Calculer le total d’articles en stock R1 = SELECT SUM(numArt) AS

[total stock] FROM ARTICLES

EXEMPLE 2

SUM

total stock

370

Résultat de la requêteEXEMPLE 1

FONTIONS D’AGREGATION Permettent d’effectuer des calculs

statistiques COUNT() SUM() AVG() Moyenne MAX() valeur maximum MIN() valeur minimum STDEV() écart type VAR() variance

TRI ET REGROUPEMENT

ORDER BY : résultat trié GROUP BY : forme des groupes de

lignes ayant une valeur commune

ORDER BY

NumArt LibArt Stock PrixUnitHT

100 PCportable 20 450

101 TelPortabl 50 70

102 clavierUsb 100 30

103 HautParleur 200 100

Table ARTICLE: Mode feuille de données

Table ArticleEXEMPLE 1

ORDER BY

ASC ordre croissant DESC: ordre décroissant

R1 = SELECT LibArt FROM ARTICLES ORDER BY LibArt ASC

Sélectionne tous les libellés de la table ARTICLE et tri par ordre alphabétique

EXEMPLE 1

ORDER BY

LibArt

clavierUsb

HautParleur

PCportable

TelPortabl

Table ARTICLE: Mode feuille de données

RésultatEXEMPLE 1

GROUP BY

Forme des groupes de lignes ayant une valeur commune

GROUP BY

Table contenant les achats et leurs clients respectifs

Table ACHATSEXEMPLE 1

GROUP BY

Somme totale des achats par groupe de clients

R1 = SELECT Customer, SUM(OrderPrice) FROM ACHATSGROUP BY Customer

EXEMPLE 1

GROUP BY

Somme par Clients

Résultat de la requêteEXEMPLE 1

GROUP BYEXEMPLE 2

GROUP BY

Somme totale Clients confondus

Résultat de la requêteEXEMPLE 2

HAVING

RESTRICTIONS (selection) sur les groupes crées avec GROUP BY

HAVING

Table contenant les achats et leurs clients respectifs

Table ACHATSEXEMPLE 1

HAVING

Somme totale des achats par groupe de clientsRESTRICTIONS sur cette Somme

R1 = SELECT Customer, SUM(OrderPrice) FROM ACHATSGROUP BY CustomerHAVING SUM(OrderPrice) < 2000

EXEMPLE 1

HAVING

Somme par Clients et Sommes inférieures à 2000 €

Résultat de la requêteEXEMPLE 1