un aperçu de sql par l’exemple -...

ChristinaKHNAISSER etLucLAVOIEDépartementd’informatiqueFacultédessciences

[email protected]://info.USherbrooke.ca/[email protected]://info.USherbrooke.ca/llavoie

UnaperçudeSQLparl’exemple

2018-09-16

BASES DE DONNÉES

BD100v121c

Départementd’inform

atique,Facultédessciences,UniversitédeSherbrooke,Québec2018-09-16

2

BD100:UnaperçudeSQLparl'exem

ple(v121c)—ChristinaKhnaisseretLucLavoie

PLAN¢Évaluation

� Rappels¢ Schémarelationnel¢ Exemplededonnées

� SchémaSQL¢ Itérations0,1,2,3

� Exempled’insertiondedonnées� Requêtes

¢Lescollesduprof





3

ÉVALUATION – RAPPELSSCHÉMA RELATIONNEL

¢ NousavonsdéjàutiliséleschémaÉvaluationlorsdelaprésentationdumoduleBD012pourillustrerlathéorierelationnelle:� L’activité desigle« sigle »etdetitre« titre »estofferteparl’UdeS.

� L’étudiant dontlematriculeest« matricule »,lenomest« nom »etl’adresseest« adresse »estinscritàl’UdeS.

� Letyped’évaluation decode« code »correspondantàladescription« description »estautoriséeàl’UdeS.

� Lerésultat « note »pourl’évaluation« TE »danslecadredel’activité« activité »autrimestre« trimestre »aétéobtenuparl’étudiant dontlematriculeest« matricule ».

Lesattributssoulignésformentlaclécandidateprimairedelarelation.





4

ÉVALUATION – EXEMPLE DE DONNÉES

sigle titre

IFT159 Analyseetprogrammation

IFT187 Élémentsdebasesdedonnées

IMN117 Acquisitiondesmédias numériques

IGE401 Gestiondeprojets

GMQ103 Géopositionnement

code description

IN Examenintra

FI Examenfinal

TP Travailpratique

PR Projet

Activité TypeÉvaluation

matricule TE activité trimestre note

15113150 TP IFT187 20133 80

15112354 FI IFT187 20123 78

15113150 TP IFT159 20133 75

15112354 FI GMQ103 20123 85

15110132 IN IMN117 20123 90

15110132 IN IFT187 20133 45

15112354 FI IFT159 20123 52

Résultatmatricule nom adresse

15113150 Paul ᐳᕕᕐᓂᑐᖅ

15112354 Éliane Blanc-Sablon

15113870 Mohamed Tadoussac

15110132 Sergeï Chandler

Étudiant


atique,Facultédessciences,UniversitédeSherbrooke,Québec

Activité{sigle :Texte;titre :Texte}

Étudiant{matricule :Texte;nom :Texte;adresse :Texte}

CREATE TABLE Activite (

sigle CHAR(6) NOT NULL,

titre VARCHAR(46) NOT NULL

);

CREATE TABLE Etudiant (

matricule CHAR(8) NOT NULL,

nom VARCHAR(60) NOT NULL,

adresse VARCHAR(60) NOT NULL

);

2018-09-16

5



ÉVALUATION – ITÉRATION 0SCHÉMA RELATIONNEL ET SCRIPT SQL (1/2)

Départem

entd’informatiq

ue,F

acultéd

essciences,U

niversitéd

eSherb

rooke,Q

uébec

TypeÉvaluation {

code :Texte;

description :Texte

}

Résultat{

matricule :Texte;

TE :Texte;

activité :Texte;

trimestre :Entier;

note :Entier[0..100]

}

CREATE TABLE TypeEvaluation (

code CHAR(2) NOT NULL,

description VARCHAR(60) NOT NULL

);

CREATE TABLE Resultat (

matricule CHAR(8) NOT NULL,

TE CHAR(2) NOT NULL,

activite CHAR(6) NOT NULL,

trimestre CHAR(5) NOT NULL,

note INTEGER NOT NULL

);

2018-09-16

6

BD100:Unaperçu

deSQ

Lparl'exem

ple(v1

21c)—

Christin

aKhnaisseretL

ucLavo

ie

ÉVALUATION – ITÉRATION 0SCHÉMA RELATIONNEL ET SCRIPT SQL (2/2)

Départem

entd’informatique,Facultédessciences,U

niversitédeSherbrooke,Québec

2018-09-16

7

BD100

:UnaperçudeSQ

Lparl'exemple(v121c)—

ChristinaKhnaisseretLucLavoie

ÉVALUATION – ITÉRATION 1AJOUTER DES CLÉS :LE DIAGRAMME RELATIONNEL





8

ÉVALUATION – ITÉRATION 1AJOUTER DES CLÉS :LE SCRIPT SQL(1/2)CREATE TABLE Activite (

sigle CHAR(6) NOT NULL,titre VARCHAR(46) NOT NULL,PRIMARY KEY (sigle)

);CREATE TABLE Etudiant (

matricule CHAR(8) NOT NULL,nom VARCHAR(60) NOT NULL,adresse VARCHAR(60) NOT NULL,PRIMARY KEY (matricule)

);CREATE TABLE TypeEvaluation (

code CHAR(2) NOT NULL,description VARCHAR(60) NOT NULL,PRIMARY KEY (code)

);





9

ÉVALUATION – ITÉRATION 1AJOUTER DES CLÉS :LE SCRIPT SQL(2/2)CREATE TABLE Resultat (

matricule CHAR(8) NOT NULL,TE CHAR(2) NOT NULL,activite CHAR(6) NOT NULL,trimestre CHAR(5) NOT NULL,note SMALLINT NOT NULL,PRIMARY KEY (matricule, activite, TE, trimestre),FOREIGN KEY (matricule)

REFERENCES Etudiant (matricule),FOREIGN KEY (activite)

REFERENCES Activite (sigle),FOREIGN KEY (TE)

REFERENCES TypeEvaluation (code));





10

ÉVALUATION – ITÉRATION 2AJOUTER DES IDENTIFIANTS AUX CLÉS (1/2)CREATE TABLE Activite (

sigle CHAR(6) NOT NULL,titre VARCHAR(46) NOT NULL,CONSTRAINT Activite_cc0 PRIMARY KEY (sigle)

);CREATE TABLE Etudiant (

matricule CHAR(8) NOT NULL,nom VARCHAR(60) NOT NULL,adresse VARCHAR(60) NOT NULL,CONSTRAINT Etudiant_cc0 PRIMARY KEY (matricule)

);CREATE TABLE TypeEvaluation (

code CHAR(2) NOT NULL,description VARCHAR(60) NOT NULL,CONSTRAINT TypeEvaluation_cc0 PRIMARY KEY (code)

);





11

ÉVALUATION – ITÉRATION 2AJOUTER DES IDENTIFIANTS AUX CLÉS (2/2)CREATE TABLE Resultat (

matricule CHAR(8) NOT NULL,TE CHAR(2) NOT NULL,activite CHAR(6) NOT NULL,trimestre CHAR(5) NOT NULL,note SMALLINT NOT NULL,CONSTRAINT Resultat_cc0

PRIMARY KEY (matricule, activite, TE, trimestre),CONSTRAINT Resultat_cr0 FOREIGN KEY (matricule)

REFERENCES Etudiant (matricule),CONSTRAINT Resultat_cr1 FOREIGN KEY (activite)

REFERENCES Activite (sigle),CONSTRAINT Resultat_cr2 FOREIGN KEY (TE)

REFERENCES TypeEvaluation (code));





12

ÉVALUATION – ITÉRATION 3AJOUTER DE CONTRAINTES GÉNÉRALES

¢Sigledecours:� troislettresmajusculessuiviesdetroischiffres.

¢Matricule:� huitchiffres.

¢Codedetyped’évaluation:� deuxlettres.

¢Trimestre:� l’annéesuivied’unchiffre:1 (hiver),2 (été)ou3 (automne).

¢Note:� entiercomprisen0et100.





13

ÉVALUATION – ITÉRATION 3AJOUTER DES CONTRAINTES GÉNÉRALES (1/4)CREATE TABLE Activite (

sigle CHAR(6) NOT NULL,titre VARCHAR(46) NOT NULL,CONSTRAINT Activite_cc0 PRIMARY KEY (sigle),

-- On s’assure que les trois premiers caractères-- du sigle sont des lettres latines majuscules et-- les trois derniers des chiffres.

CONSTRAINT Activite_sigle CHECK (sigle SIMILAR TO '[A-Z]{3}[0-9]{3}')

);





14

ÉVALUATION – ITÉRATION 3AJOUTER DES CONTRAINTES GÉNÉRALES (2/4)CREATE TABLE Etudiant (

matricule CHAR(8) NOT NULL,nom VARCHAR(60) NOT NULL,adresse VARCHAR(60) NOT NULL,CONSTRAINT Etudiant_cc0 PRIMARY KEY (matricule),

-- On s’assure que le matricule soit composé-- de huit chiffres.

CONSTRAINT Etudiant_matricule CHECK (matricule SIMILAR TO '[0-9]{8}')

);





15

ÉVALUATION – ITÉRATION 3AJOUTER DES CONTRAINTES GÉNÉRALES (3/4)CREATE TABLE TypeEvaluation (

code CHAR(2) NOT NULL,description VARCHAR(60) NOT NULL,CONSTRAINT TypeEvaluation_cc0 PRIMARY KEY (code),

-- On s’assure que le code soit composé de-- deux lettres latines.

CONSTRAINT TypeEvaluation_code CHECK (code SIMILAR TO '[A-Za-z]{2}')

);





16

ÉVALUATION – ITÉRATION 3AJOUTER DES CONTRAINTES GÉNÉRALES (4/4)

CREATE TABLE Resultat (matricule CHAR(8) NOT NULL,TE CHAR(2) NOT NULL,activite CHAR(6) NOT NULL,trimestre CHAR(5) NOT NULL,note SMALLINT NOT NULL,CONSTRAINT Resultat_cc0PRIMARY KEY (matricule, activite, TE, trimestre),

CONSTRAINT Resultat_cr0 FOREIGN KEY (matricule)REFERENCES Etudiant (matricule),

CONSTRAINT Resultat_cr1 FOREIGN KEY (activite)REFERENCES Activite (sigle),

CONSTRAINT Resultat_cr2 FOREIGN KEY (TE)REFERENCES TypeEvaluation (code),

CONSTRAINT Resultat_note CHECK (note BETWEEN 0 AND 100),-- Les trimestres sont encodés en suffixant le no du-- trimestre à l’année.

CONSTRAINT Resultat_trimestre CHECK(trimestre SIMILAR TO '[0-9]{4}[1-3]{1}’)

);





17

ÉVALUATIONEXEMPLE D’INSERTION DE DONNÉESINSERT INTO Activite (sigle, titre) VALUES

('IFT159', 'Analyse et programmation'),('IFT187', 'Éléments de bases de données'),('IMN117', 'Acquisition des médias numériques'),('IGE401', 'Gestion de projets'),('GMQ103', 'Géopositionnement');





18

ÉVALUATIONREQUÊTES

1. QuelssontlesétudiantsinscritsenIFT187?2. Quelssontlesétudiantsinscritsàuneactivité

d’informatiqueàl’automne2013?3. Quelsétaientétudiantsensituationd’échecau

finalàl’automne2012?4. Produirelerelevédenotesd’Éliane.5. Quelsétudiantsnesontinscritsàaucuneactivité?





19

ÉVALUATION – R1QUELS SONT LES ÉTUDIANTS INSCRITS EN IFT187?

¢Clarification� Laformulationutiliséepourraitindiquerqu’ons’intéresseauseultrimestrecourant.Lerequérantnousprécisecependantqu’ilvisetouteslesinscriptionsdepuislapremièreoffredel’activité.

¢Entête� InscritsIFT187{matricule :Texte}

¢Requête� (Résultatσ (activité=‘IFT187’))π{matricule}





20

ÉVALUATION – R1SCRIPT SQLSELECT DISTINCT matriculeFROM ResultatWHERE activite='IFT187’

¢Rappels� Larequêten’estcorrectequesi:

• matriculeestunecléréférentielledeRésultatversÉtudiant;

• « on »définitqu’uneinscriptionn’estenvigueurquesil’étudiantacomplétéunepremièreévaluationetquecelle-ciaétésaisie;

• Silapremièreconditionestnaturelle,ladeuxièmel’estmoinsetsoulèveuneinterrogationquantàlajustessedelamodélisation.





21

ÉVALUATION – R2QUELS SONT LES ÉTUDIANTS INSCRITS À UNE ACTIVITÉ D’INFORMATIQUE À L’AUTOMNE 2013?

¢Clarification� Uneactivitéd’informatiqueestdéfiniecommeétanttouteactivitédontlesigledébuteparlepréfixe‘IFT’.Noussupposonsqu’ilexisteunefonctionpréfixedéfiniesurleschainesdecaractères(letypeTexte).

� “Automne2013”doitêtrerecodésouslaformedel’entier20133.

¢Entête� InscritsIFT {matricule :Texte}

¢Requête� (Résultatσ (préfixe(activité,3)=‘IFT’)⋀trimestre=‘20133’)π{matricule}





22

ÉVALUATION – R2SCRIPT SQLSELECT matriculeFROM ResultatWHERE SUBSTRING(activite, 1, 3)='IFT'

AND trimestre = '20133';

SELECT DISTINCT matriculeFROM ResultatWHERE SUBSTRING(activite, 1, 3)='IFT'

AND trimestre = '20133';

matricule151131501511315015110132

matricule1511315015110132

Avecdoublons

Sansdoublons





23

ÉVALUATION – R3QUELS ÉTAIENT ÉTUDIANTS EN SITUATION D’ÉCHEC AU FINAL À L’AUTOMNE 2012?

¢Clarification� Unesituationd’échecestunenoteinférieureà60.� Unfinalestun‘Examenfinal’représentéparlecode‘FI’.� « Automne2012 »doitêtrerecodésouslaformedel’entier20123.

¢Entête� Échecs20123{matricule :Texte}

¢Requête� (Résultatσ (note<60⋀TE=‘FI’⋀trimestre=‘20123’))π{matricule}





24

ÉVALUATION – R3SCRIPT SQLSELECT DISTINCT matriculeFROM ResultatWHERE note < 60

AND TE = 'FI' AND trimestre = '20123';





25

ÉVALUATION – R4PRODUIRE LE RELEVÉ DE NOTES D’ÉLIANE.

¢Clarification� Lematriculeaétéintroduitpourdifférencierleshomonymes.Unrelevéproduitsurlaseulebasedunomestdoncsusceptibled’êtreinexact.Enconséquence,nousdemanderonsàÉliane sonmatricule(nousneconsultonspaslabasededonnées).

¢Entête� RelevéÉliane

{TE :TypeEval;activité :SigleCours;trimestre :Trimestre;note :Note}

¢Requête� ((Résultat⋈Étudiant)σ (matricule=‘15112354’))π

{TE,activité,trimestre,note}





26

ÉVALUATION – R4SCRIPT SQLSELECT DISTINCT TE, activite, trimestre, noteFROM ResultatWHERE matricule = ' 15112354';

Pourquoin’est-cepasnécessaire,maisnéanmoinspasinexact,despécifierDISTINCT?





27

ÉVALUATION – R4(BIS)PRODUIRE LE RELEVÉ DE NOTES D’ÉLIANE.

¢ Clarification� Ondemanded’ajouterletitredel’activitédanslerelevé.

¢ Entête� Relevé2Éliane{TE :Texte;sigle :Texte; titre :Texte;trimestre :Entier;note :Entier[0..100]}

¢ Requête� Remarquonsqu’ilestnécessairedejoindrelarelationActivitépourobtenirletitreetquel’attributdejointuren’yportepaslemêmenomquedanslarelationRésultat.

� ((((Résultat⋈Étudiant)σ (matricule=‘15112354’))ρ {activité→sigle})⋈Activité)π{TE, sigle,titre,trimestre,note}





28

ÉVALUATION – R4(BIS)SCRIPT SQLSELECT TE, sigle, titre, trimestre, noteFROM Resultat

JOIN Activite ON(activite = sigle)WHERE matricule = ' 15112354';





29

ÉVALUATION – R5QUELS ÉTUDIANTS NE SONT INSCRITS À AUCUNE ACTIVITÉ?¢ Clarification

� Encoreunefois,ilestnécessairedefairepréciserlapériodeàcouvrir,entermedetrimestres.Supposonsquecesoitlestroistrimestresdel’année2013,laquestiondevientdonc:¢ Quelsétudiantsnesontinscritsàaucuneactivitéen2013?

� Supposonségalementqu’ondésireavoirunmaximumd’informationsurcesétudiantsetpasseulementleurmatricule(àsavoirtouslesattributsdisponiblesdanslarelationÉtudiant).

¢ Entête� NonInscrits2013

{matricule :Texte;nom :Texte;adresse :Texte}





30

ÉVALUATION – R5(SUITE)QUELS ÉTUDIANTS NE SONT INSCRITS À AUCUNE ACTIVITÉ EN 2013?¢Requête

� Nousréalisonscetterequêteencalculantd’abordlesétudiantsinscritsàaumoinsuneactivitéen2013puisenlessoustrayantdel’ensembledesétudiants.

� Inscrits2013:(Résultatσ (‘20131’≤trimestre≤’20133’))π{matricule}

� NonInscrits2013:Étudiant– (Étudiant⋈Inscrits2013)

� ou,enextension:Étudiant–(Étudiant⋈((Résultatσ (‘20131’≤trimestre≤’20133’))π{matricule}))





31

ÉVALUATION – R5:SCRIPT SQLVERSION 2

SELECT matricule, nom, adresseFROM EtudiantEXCEPT

SELECT matricule, nom, adresseFROM Etudiant

JOIN (SELECT matricule FROM ResultatWHERE '20131' <= trimestre

AND trimestre <= '20133' ) AS Inscrit2013

USING(matricule);

matricule nom adresse15112354 Éliane Blanc-Sablon15113870 Mohamed Tadoussac

ÉlianePaul

Sergeï

Mohamed

Étudiants

Autres années2013





32

ÉVALUATION – R5(BIS)QUELS ÉTUDIANTS NE SONT INSCRITS À AUCUNE ACTIVITÉ EN 2013?¢ Justinremarquequ’ilestpossibledesimplifierl’expressionprécédentesionadmetqu’auseindumodèlelanégation delaproposition

(20131≤trimestre≤20133)s’exprimepar

(trimestre<20131⋁20133<trimestre)¢ Ilobtientalors(Étudiant⋈(Résultatσ (trimestre<'20131'⋁'20133'<trimestre)))π{matricule,nom,adresse}

¢ A-t-ilraison?� NON!





33

ÉVALUATION – R5(BIS):SCRIPT SQL

SELECT DISTINCT matricule, nom, adresseFROM Etudiant

JOIN (SELECT matricule FROM ResultatWHERE trimestre < '20131’

OR '20133’ > trimestre ) AS NonInscrit2013

USING(matricule);

matricule nom adresse15112354 Éliane Blanc-Sablon15110132 Sergeï Chandler

Paul Éliane

Sergeï

Mohamed

Étudiants

2013 Autres années





34

ÉVALUATION – R5(BIS)POURQUOI ?

¢Parcequ’unétudiantpeuts’inscrirelorsdeplusieurstrimestres,onnepeutpaslogiquementconclurequ’unétudiantinscriten2013nes’estinscritàaucuneautreannée:� Parexemple,Sergeï s’estinscriten2012eten2013.

¢Onnepeutpaslogiquementconclurequ’unétudiantquines’estpasinscriten2013s’estinscritlorsd’uneautreannée:� Parexemple,Mohamedestétudiant,maisnes’estencoreinscritàaucuneactivité.





35

ÉVALUATIONDU VRAI CODE!

¢Lesscriptscompletsdel’exemplesontdisponiblessurlesitewebdecoursExemples/Evaluation.

¢Lireducodedemeurelameilleurefaçond’apprendreàenécrireJ





36

LES COLLES DU PROF

¢ Quelleestladifférenceentreunschéma,undiagrammeetunscript?

¢ Ya-t-iluneseulefaçondetraduireuneexpressionrelationnelleenscriptSQL?

¢ Commentdécririez-vousl’instructionSELECT?¢ Est-ilpertinentdeformulerunerequêted’abordsousformed’expressionrelationnellepuisdelatraduireenscriptSQL?

¢ Est-ilpréférabledeformulerdirectementunerequêteenscriptSQL?

¢ ComparerlesprédicatsutilisésdansBD100avecceuxproposésparl’exempledéposédanslerépertoirepublicducours.Décrirelaportéedesdifférences,indiquerles"meilleures"formulationsetmotiverleschoix.

un aperçu de sql par l’exemple -...

Documents