Exemples relationnelsinfo.usherbrooke.ca/llavoie/enseignement/Modules/BD012-Exemples... · TP Travail pratique PR Projet ... 3. Quels étudiants étaient en situation d’échec au

ChristinaKHNAISSER etLucLAVOIEDépartementd’informatiqueFacultédessciences

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

ExemplesrelationnelsBASES DE DONNÉES

BD012v121b

2018-09-03

Départementd’inform

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

2

PLAN¢Exemple1:Évaluation

� Schéma(prédicats,entêtes,clés)� Exemplededonnées� Requêtes

¢Exemple2:GaspardetMadeleine� Schéma(prédicats,entêtes,clés)� Exemplededonnées� Requêtes

¢Conclusion¢Lescollesduprof

2018-09-03BD012

:Exemplesrelationnels(v121b)–ChristinaKhnaisseretLucLavoie

Départem

entd’informatiq

ue,F

acultéd

essciences,U

niversitéd

eSherb

rooke,Q

uébec

¢ Description

¢ Schéma(prédicats,entêtes,clés)

¢ Basededonnées

¢ Requêtes

3

ÉVALUATION2018-09-03

BD012:Exem

plesrelatio

nnels(v

121b)–Christin

aKhnaisseretL

ucLavo

ie

Départem

entd’informatique,Facultédessciences,U

niversitédeSherbrooke,Québec

4

ÉVALUATIONDESCRIPTION

¢L’UniversitédeSamarcande (UdeS)proposedifférentesactivitéspédagogiquesdansplusieursdomaines.Depuisdeuxans,lenombred’étudiantsnecessed’augmenter.Afindemieuxrépondreàleursbesoins,ilestnécessairedesuivreleurparcours.

¢Elledésireobtenirplusd’informationsur:� Lesactivitésproposées,

� Lesrésultats(notes)parcoursetpartypesd’évaluationsdechacundesétudiants.

2018-09-03BD012

:Exemplesrelationnels(v121b)–

ChristinaKhnaisseretLucLavoie

Départem



5

ÉVALUATIONQUELS SONT LES PRÉDICATS?

¢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 ».

2018-09-03BD012



Départem



2018-09-03BD012



6

ÉVALUATIONFAITS ET PROPOSITIONS – RAPPEL

¢Ensubstituantlesvaleursdesattributsd’untupleauxvariablescorrespondantesduprédicatdelarelation(lesidentifiantsentreguillemets)onobtientlaproposition(lefait).


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

7

BD012:Exem

plesrelationnels(v121b)–ChristinaKhnaisseretLucLavoie

ÉVALUATIONFAITS ET PROPOSITIONS – UN EXEMPLE

matricule nom adresse

15110132 Sergeï Chandler

L’étudiant dont le matricule est « matricule », le nom est « nom » et l’adresse est « adresse » est inscrit à l’UdeS.

L’étudiant dont le matricule est « 15110132 », le nom est « Sergeï» et l’adresse est « Chandler » est inscrit à l’UdeS.

+

=

Départem



8

ÉVALUATION - SCHÉMA (ITÉRATION 0):RELATION

Activité {sigle : Texte; titre : Texte}

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

TypeÉvaluation {code : Texte; description : Texte}

Résultat {matricule : Texte; TE : Texte; activité : Texte; trimestre : Texte; note : Entier}

2018-09-03BD012



Départem



9

ÉVALUATION - SCHÉMA (ITÉRATION 1):DOMAINES

SigleCours { x ∈ Texte | x = [A-Z]{3}[0-9]{3} }Activité {sigle: SigleCours; titre: Texte}

Matricule { x ∈ Texte | x = [0-9]{8} }Étudiant {matricule: Matricule; nom: Texte; adresse: Texte}

TypeEval { x ∈ Texte | x = {TP, PR, IN, FI} }TypeÉvaluation {code: TypeEval; description: Texte}

Note { x ∈ Entier | 0 <= x <= 100 }Trimestre { t ∈ Texte | t = [0-9]{4}[1-3] }Résultat {matricule: Matricule; TE: TypeEval;

activité: SigleCours; trimestre: Trimestre; note: Note}

2018-09-03BD012





10

ÉVALUATION - SCHÉMA (ITÉRATION 2):QUELLES SONT LES CLÉS?

¢Cléscandidates� Cle Activité{sigle}� Cle TypeEvaluation {code}� Cle Etudiant{matricule}� Cle Résultat{matricule,TE,activité,trimestre}

¢Clésréférentielles� Résultat.matricule →Étudiant.matricule� Résultat.TE →TypeÉvaluation.code� Résultat.activité →Activité.sigle

2018-09-03BD012



Départem



11

ÉVALUATIONDIAGRAMME RELATIONNEL

2018-09-03BD012



Départem



12

ÉVALUATION – EXEMPLE DE DONNÉES

sigle titre

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

code description

IN Examen intra

FI Examen final

TP Travail pratique

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


Étudiant

2018-09-03BD012



Départem



2018-09-03

13

BD012



IFT 187 BD012

GMQ 103 GéopositionnementIGE 401 Gestion de projetsIMN 117 Acquisition des médias numériqueIFT 187 Éléments de bases de données

Analyse et programmationtitre

IFT 159sigle

Christina Khnaisser Luc Lavoie

PR ProjetTP Travail pratiqueFI Examen final

Examen intradescription

INcode

15112354 5220123FI IFT 15920133IN 45IFT 18715110132

78

note

908575

80

2012320123

20133

trimestre2013320123

GMQ 103

IFT 187

IMN 117

activité

IFT 159IFT 187

15110132 IN

15112354 FI

15113150 TP

15112354 FI

TPTE

15113150

matricule

Tadoussac

ᐳᕕᕐᓂᑐᖅ

Chandler

adresse

Blanc-Sablon

15110132 Sergeï15113870 Mohamed15112354 Éliane

Paulnom

15113150

matricule

Activité TypeÉvaluation

ÉtudiantRésultat

Exemple de données Schéma Évaluation

BD012-Exemples-relationnels_AFF.graffle [Canvas 1.2] (2017-08-26)



14

É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é?

2018-09-03BD012





15

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

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

¢Entête� InscritsIFT187{matricule :Matricule}

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

2018-09-03BD012




16

ÉVALUATION – R1NOTES

¢Lasolutionprécédentesupposequ’unétudiantn’est(considéré)inscritàuneactivitéqu’àpartirdumomentoùilaétéévaluéaumoinsunefoisauseindecetteactivité.

¢Pourquoi?¢Sil’inscriptiondoitêtredissociéedel’évaluationetpouvoirêtreétablieavantlapremièreévaluation,commentdevrait-onchangerleschéma?

¢Cettedernièrequestionrelèvedelamodélisationetnondelaconception.

2018-09-03BD012




17

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

¢Clarification� Uneactivitéd’informatiqueestdéfiniecommeétanttouteactivitédontlesigledébuteparlepréfixe‘IFT’.Supposonsunefonctionpréfixe(texte,n)quiretourneuntexteàpartird’untexteetd’unnombredelettre.

� Letrimestre« Automne2013 »estcodépar« 20133 ».

¢Entête� InscritsIFT {matricule :Matricule}

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

π{matricule}

2018-09-03BD012




18

É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 »estcodépar« 20123 ».

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

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

π{matricule}

2018-09-03BD012:Exem


Départem



2018-09-03BD012



19

¢Clarification� Unesituationd’échecestunenoteinférieureà60.� Unfinalestun‘Examenfinal’représentéparlecode‘FI’.� « Automne2012 »estcodépar« 20123 ».

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

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

π{matricule}

Départem

entd’informatiq

ue,F

acultéd

essciences,U

niversitéd

eSherb

rooke,Q

uébec

2018-09-03

BD012:Exem

plesrelatio

nnels(v

121b)–Christin

aKhnaisseretL

ucLavoie

20

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

¢Clarification

� Lematriculeaétéintroduitpourdifférencierleshomonymes.Unrelevéproduitsurlaseulebasedunomestdoncsusceptibled’êtreinexact.Enconséquence,nousdemanderonsàÉliane sontmatricule(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}



21

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

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

¢ Entête� Relevé2Éliane

{TE :TypeEval;sigle :SigleCours;titre :Texte;trimestre :Trimestre;note :Note}

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

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

2018-09-03BD012


Départem



22

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

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

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

¢ Entête� NonInscrits2013

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

2018-09-03BD012



Départem



23

É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:Étudiant–(Étudiant⋈((Résultatσ ('20131'≤trimestre≤'20133'))π{matricule}))

2018-09-03BD012





24

ÉVALUATION – R5RÉSULTATS

Étudiant –(Étudiant ⋈((Résultat σ ('20131'≤trimestre≤'20133'))π{matricule}))

matricule nom adresse15112354 Éliane Blanc-Sablon

15113870 Mohamed Tadoussac

ÉlianePaul

Sergeï

Mohamed

Étudiants

Autres années2013

2018-09-03BD012


Départem



25

ÉVALUATION – R5(BIS)QUELS ÉTUDIANTS NE SONT INSCRITS À AUCUNE ACTIVITÉ EN 2013?

¢ Justinremarquequ’ilsemblepossibledesimplifierl’expressionprécédentesionadmetqu’auseindumodèlelanégationdelaproposition

('20131'≤trimestre≤'20133')s’exprimepar

(trimestre<'20131'⋁'20133'<trimestre)¢ Ilobtientalors

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

¢ A-t-il raison?� NON!

2018-09-03BD012





26

ÉVALUATION – R5(BIS)RÉSULTATS

(Étudiant⋈(Résultatσ (trimestre<'20131'⋁'20133'<trimestre)))

π{matricule,nom,adresse}

matricule nom adresse15112354 Éliane Blanc-Sablon


Paul Éliane

Sergeï

Mohamed

Étudiants

2013 Autres années

2018-09-03BD012




27

É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àuneactivité.

2018-09-03BD012





¢ Schéma(entêtes,prédicats,clés)¢ Basededonnées¢ Requêtes

28

GASPARD ET MADELEINE2018-09-03

BD012:Exem




29

GASPARD ET MADELEINECLARIFICATION,ENTÊTES,REQUÊTES

¢Voirsurlesitedecoursledocument� BD011-Gaspard-et-Madeleine_NDC

¢Àvousdejouer!� L’enseignantoffrevolontierssonsoutienàtoutétudiantquiprésenteunepreuvetangible(doncécrite)desontravail.

2018-09-03BD012


Départem



30

CONCLUSIONDÉMARCHE (1/3):DÉFINITION DU SCHÉMA

1. Lirel’énoncé.2. Comprendrelesbesoinsdesutilisateurs.

3. Clarifierlesconceptsdudomaine.4. Définirlesprédicats.

5. Identifierlescontraintes:o contraintesdedomaine,

o contraintescléscandidates,

o contraintesd’attribut,o contraintesderelation,

o contraintesréférentielles.

2018-09-03BD012





31

CONCLUSIONDÉMARCHE (2/3):DÉFINITION D’UNE REQUÊTE

1. Clarifierlaquestion.2. Expliciterleshypothèses.3. S’assurerquelaquestionaunsensauseindu

modèlesurlabasedesprédicatsdesrelationsduschéma.

4. Endéduirel’entêtedelarelationquiseralerésultatetenformulerleprédicat(quidevientalorslelibellédelarequête).

5. Élaborerdescasdetests.6. Diviserlarequêtepourentraiterplusfacilement

lesparties(diviserpourrégner).

2018-09-03BD012


Départem



32

DÉMARCHEDÉMARCHE (2/3):ÉLABORATION DES CAS DE TEST

¢Lescas� *:lecasgénéral� 1:lecasparticulier� 0:lecasinexistant� **:le« gros »cas

2018-09-03BD012



Départem



33

LA SUITE...LE LANGAGE SQL

Module Cat. ContenuBD100 SQL Un aperçu de SQL – reprise de l’exemple Évaluation de BD012BD101 LDD-01 Types élémentaires, tables, clés (create, alter, drop)BD102 LDD-02 Contraintes simples, conditions et expressionsBD103 LDD-03 Approfondissement des types et opérateurs prédéfinisBD104 LMD-01 Modification (insert, delete, update)BD105 LMD-02 Sélections simples (select from where ; select into)BD106 LMD-03 Sélections complexes (with, group)BD107 LMD-04 Sélections quantifiées et spécialisées (order, window...)BD108 LDD-04 Domaines et constantes (domain, set)BD109 LDD-05 Vues (view)BD110 LDD-06 Contraintes générales et automatismes (assertion, trigger)BD111 LDD-07 Fonctions et procédures (function, procedure)BD112 LDD-08 Types et classes (type)BD113 LDD-09 OrdonnancementetcatégorisationdesjeuxdecaractèresBD114 LDD-10 Clés primaires et artificielles

2018-09-03BD012





34

LES COLLES DU PROF

¢Pourquoil’étapedeclarificationest-elleessentielle ?

¢Pourquoiest-ilutile,sinonnécessaire,defixerl’entêtedurésultatapriori?

¢Unemêmerequêtepeut-elleêtreformuléededifférentesfaçons(parplusieursexpressionsdifférentes)?

¢Commentdécririezlerôledelajointureenregarddelaformulationdesrequêtes?

¢Quelestlelienentrelajointureetlacléréférentielle?

2018-09-03BD012


Départem

entd’informatiq

ue,F

acultéd

essciences,U

niversitéd

eSherb

rooke,Q

uébec

¢ Capacité� 5millionsdecaractèresde7bits(dontundeparité);

¢ Débit� unpeumoinsde9ko/s

¢ Dimensions(cabinet)� 1,52mx0,74mx1,72m

¢ Poids� environ1tonne

¢ Cout� 50000USD(1956),soitenv.� 450000USD(2015)

¢ Production� unmillierd’unités(1956-1961)

� findevie1969

2018-09-03

35

BD012:Exem

plesrelatio

nnels(v1

21b)–Christin

aKhnaisseretL

ucLavo

ie

LE DISQUE RAMAC305D’IBMUN LECTEUR MP3,VERSION 1956

http://www.bhmag.fr/actualites/nostalgie-premier-disque-dur-ibm-ramac-305-1-tonne-15324

Départem



2018-09-03BD012



36

LES COLLES DU PROF (BIS)

¢ Combiencouteundisquedomestiquede4To?� Quelestsondébit?� Quellessontsesdimensions?� Quelleestsaconsommationénergétique?

¢ Combiencouteundisquehauteperformancede4To?� Quelestsondébit?� Quellessontsesdimensions?� Quelleestsaconsommationénergétique?

¢ Quelssontlesratiosdecout(USD2015)parModecesdisquesparrapportauRAMAC305?

¢ Idempourlevolume(cm3/Mo).¢ Idempourlaconsommationd’énergie(watt/Mo).

¢ Combiencoutelestockagede100photosà12mégapixelssurchacundecessupports(utiliserunformatRAWà24bits/pixel)?

Documents

Exemples relationnelsinfo.usherbrooke.ca/llavoie/enseignement/Modules/BD012-Exemples... · TP Travail pratique PR Projet ... 3. Quels étudiants étaient en situation d’échec au