Td corrigé Système de Gestion de Bases de Données - R1 Web Site pdf

Système de Gestion de Bases de Données - R1 Web Site

PSY38X2 : Traitement de données en Psychologie - TD N°2 ... Après une étude des données qui interviennent, vous parvenez au MCD[2] ... table dans laquelle les données relatives aux intervenants, par exemple, .... Importez cette table depuis la base de données de référence W:PSY3TD ACCESSONG-CORRIGE.




part of the document



u artificiel ( Von Neuman )
1960 : Centralisation ( Travail par JOB )
1970 : Transactionnel ( Communication utilisateur / Processus )
1980 : Informatique personnel ( 1er pc : 1984 ) puis connexion ( 1er réseau )
2000 : Internet / coopération de BD distantes











Description

Liens entre données
Cohérence des données
Souplesse d’accès
Sécurité
Droit d’accès
Synchronisation
Sécurité de sauvegarde des données
Partage
Performance
Indépendance des données
Physique
Logique

Structures :

VEHICULE ( N° Véhicule, Marque, Type, Couleur )
PERSONNE ( N° SS, Nom, Prénom )
PROPRIETAIRE ( N° SS, N° Véhicule, date )

Vue :

PERSONNE ( N° SS, Prénom, Nom, N° Véhicule )


Fonctions du SGBD

Organisation
Langage de définition de données ( LDD ), ex : en SQL, CREATE TABLE

Manipulation
Langage d’interrogation, ex : SQL
Langage de programmation, ex : VBA, COBOL, C…
Pour faire les traitements autres que les 5 agrégats SQL ( Max, Min, Sum, Average, Count )

Gestionnaire























Modélisation











( Merise, UML,OMT,SADT,… )



Cycle en V




















Modèles précédents le modèle relationnel

Modèle hiérarchique











Modèle réseau










Modèle réseau sémantique



















Le modèle Entité / Association

Le modèle Entité / Association de base

Entité : concept (ex un livre ) concret ou abstrait que l’on reconnaît
comme individualisable











Classe d’association : niveau générique de toute association
présentant les mêmes caractéristiques



 Ou




Dimension ( degré, arité ) : nombre de classes d’entités associées


Remarque : Penser en choisissant un identificateur à ne pas choisir une propriété interdite par la CNIL ( données nominatives ), par exemple : N° sécu…, et encore moins la race, la religion, données médicales


Ex :











Cardinalité : ( Nb Min, Nb Max )





















































Le modèle Entité / Association étendu




























































Réification : transformer une association en entité















































Rem : avec ce genre de modèles :
pas de calcul, que des données élémentaires
pas de clé secondaire










Exercice : Achat d’un appartement en co-location




























































Exercice « Club Super Vidéo »

























































































































Conception de base de données
TD2

Exercice 1



























































Exercice 2


1)
























































2)



























































Exercice 3











































Solde Retard : Calculatoire
















Le modèle relationnel


1. Définition

Langage d’interrogation théorique

1970 CODD :
relationnel
normalisation
SGBD relationnel
SQL
Relation : sous ensemble du produit cartésien d’une liste de domaines

Domaine : ensembles de valeurs

Produit cartésien :
D1,D2…Di des domaines
D1xD2x..xDi
Est l’ensemble des tuples (V1,V2,…,Vi) tels que Vj appartient Dj pout tt j

Arité : c’est la relation de l’ensemble des n-uplets des éléments

Cardinalité : nombre de n-uplets

Relation :
Table

Voiture composant (champ) tuple (ligne)




Schéma d’une relation :

Nom relation ( : type…)

Voiture (N°immatriculation : string,
Couleur : string,
Année d’achat : entier)

E/A : identificateur
Relationnel : clé

Schéma d’une relation :
____(_____)
clé

Contraintes :

- Domaines (ensembles de valeurs possibles)

ex : N°immatriculation : chaîne de caractères
couleur : chaîne de caractères {rouge,bleu,noir}
Personne
N°SS  : entier 13
Nom : string 30
Prénom : string 30
Nom marital : string 30
Date naissance 
Jour : entier 2
Mois : entier 2
Année : entier 4
Sexe : booléen ( intention :
1 caractère N°SS : Sexe
1 : M
2 : F
extension :
2-3 caractères N°SS : Année de naissance, les 2 derniers caractères

4-5 caractères N°SS : Mois naissance

Nom marital : sexe )
Dépendances fonctionnelles

R ( A1,A2, …, An ) schéma de relation

G et D, 2 sous ensembles de { A1, A2, …, An }
On dit que D dépend fonctionnellement de G G(D

Si pour toute relation R de R (réels), les tuples u, v de R qui ont même composante dans G ont aussi même composante dans D

Voiture
N° immatriculation
Couleur
Type
Puissance

N° immatriculation Couleur
 Type

Puissance






















Langage d’interrogation théorique


Opérations ensemblistes

UNION : Union ( R1, R2 ) R1 R2
R1 U R2
U
R1 et R2 Même schéma






DIFFERENCE : Différence ( R1, R2 ) R1 R2
R1 - R2
-
R1 et R2 Même schéma






PRODUIT CARTESIEN : R1 R2
R1 X R2
X


?


L’INTERSECTION : R1 R2
R1- ( R1-R2 )








Opérations Spécifiques

SELECTION : Sélection ( R1, Condition )
 Condition ( R1 )
Condition
Même schéma, conserve les tuples
vérifiant les conditions


PROJECTION : Projection ( R1, a1, a2, & , ai )
À a1, a2, ai ( R1 )

Même schéma, conserve les tuples vérifiant les conditions



 a1, a2, ai Projection
JOINTURE : JOIN ( R1, R2, Condition )

R1 R2 R1 R2
Condition
 Condition




Passage E/A ( relationnel

MCD MLD MPD
(Logique) (Physique)

Toute entité devient une table
Quand vous avez une association entre 2 entité de type Maître / Esclave ( de cardinalité (_,n) avec (_,1)), vous ajoutez dans la table esclave une propriété de même type que la clef de la table maître



Maître Esclave











Clef primaire 2 : clef secondaire


A Partir de :













Dans le cas d’une association possédant des attributs ou une association de cardinalité (_,n),(_,n), alors on crée une table supplémentaire contenant des propriétés de même type que les clefs primaires des tables associées et les propriétés de l’association si nécessaire




































































Exercice : Algèbre relationnel


Ecrire le schéma relationnel






Gagnant



Perdant



Empoche





Paye









Question 1 : Gain



Nom sponsor = « Peugeot » et date entre 85 et 90





 Nom joueur, prime



Question 2 : Gain Joueur


Lieur Tournoi= ‘RG’
Date=89





 Nom joueur = Nom

 Nom, age

Question 3 : Gain Rencontre



Nom sponsor = ‘Peugeot’





 Joueur Nom joueur = Nom gagnant
et lieu tournoi = ‘RG’


 Nom Gagnant = Nom



 Nom, Nationalité



Question 4 : Gain Gain


Lieu tournoi =  RG Lieu tournoi =  W
Et date = 1985 et date = 1985



 Joueur Joueur


 Nom joueur = Nom Nom joueur = Nom







)"


Question 5 : Voir correction Prof

Question 6 :

Rencontre Rencontre Rencontre Rencontre


Lieu tournoi=’W’ Lieu tournoi=’W’ Lieu tournoi=’RG’ LT =’RG’


 Nom perdant Nom gagnant Nom gagnant Nom perdant

 - -
)"

Question 7 et 8 : Impossible puisque pas d opérateur calculatoire dans le langage
SQL «  Structured Query Language »


{ } : Obligatoire
[ ] : Facultatif

IBM system R
SQUARE


SQL : - table
- Colonne
- Ligne


Instruction de base

SELECT [ ALL / DISTINCT ] { liste < colonne / expression > / * }

FROM liste { < table > / < vue > }

WHERE condition

Visualisation de toutes les colonnes de la table T

SELECT * FROM T ;

Visualisation des colonnes C1, C2 et T

SELECT C1, C2 FROM T;

Visualisation d’une partie de T

SELECT * FROM T WHERE Condition ;


Opérateurs

Logiques : AND OR NOT
Conditionnels : < = > =
Prédicat :
Between ( ensemble fermé : bornes incluses )
IN ( )
LIKE _  : 1 et 1 seule occurrence dans le champ
LIKE % :

Ex : lieu LIKE ‘’_RI_’’
(PARIS

Lieu LIKE ‘’%ER’’ ( fin du mot
Lieu LIKE ‘’%ER%’’


Fonctions d’évaluation

Comptage
COUNT

SELECT COUNT ( DINSTINCT NATIONALITE ) FROM JOUEUR

AVG

SELECT AVG ( PRIME ) FROM GAIN

SUM

MIN

MAX Numérique et string


Requêtes imbriquées 1 seule colonne

SELECT _ FROM _
WHERE ( SELECT _ FROM _ WHERE _ )


Fonctions d’évaluation

SELECT _ FROM _ [ WHERE _ ]
GROUP BY liste colonne [Having < Condition >]


Tri

SELECT _ FROM _ [ WHERE _ ]
[ GROUP BY _ ]
ORDER BY { liste colonne / entier } [ ASC /DESC ]


Fusion

SELECT _
FROM _

UNION Même type (char, int, …) et même nombre de colonnes

SELECT _
FROM _


Vue

CREATE VIEW < VUE >
AS < Commande SELECT >


Modification de structure

Ajout attribut

ALTER TABLE < TABLE >
ADD ( liste < COLONNE > < TYPE > )

Modification attribut

ALTER TABLE < TABLE >
MODIFY ( Liste < COLONNE > < TYPE > )

Attention :
Réduction de taille : il faut que toutes les instances soient nulles
Modifier le type : idem
Renommer Table

RENAME < Ancien nom > TO < Nouveau nom >


Ajout n-uplet

INSERT INTO < TABLE > [ < liste colonne > ] VALUES < liste valeurs >


Mise à jour

UPDATE < TABLE >
SET < liste colonne > = < expression >
[ WHERE condition ]


Suppression de table

DROP TABLE < TABLE >


Suppression de données

DELETE FROM < TABLE > [ WHERE < condition > ]



























Exercice : Algèbre relationnel ( Manière différente )

SELECT NOM JOUEUR FROM GAIN WHERE LIEU = ‘RG’ AND PRIME >= 1MF AND NOMJOUEUR NOT IN ( SELECT NOMJOUEUR FROM GAIN WHERE LIEU = ‘RG’ AND PRIME < 1MF );

SELECT R1.NOMPERDANT FROM RENCONTRE R1 WHERE R1.NOMPERDANT NOT IN ( SELECT R2.NOMGAGNANT FROM RENCONTRE R2 );

OU

SELECT R1.NOMPERDANT FROM RENCONTRE R1 WHERE NOT EXIST ( SELECT * FROM RENCONTRE R2 WHERE R1.NOMPERDANT = R2.NOMGAGNANT );




TP Base de données : Le cirque


SELECT DISTINCT ( NATURE ) FROM NUMEROS ;

SELECT NOCAMION, VOLUME FROM ACCESSOIRES ORDER BY NOCAMION ASC, VOLUME DESC;

SELECT NOMACCESSOIRE FROM ACCESSOIRES WHERE COULEUR=’ROUGE’ AND VOLUME 1;

SELECT ACCESSOIRE FROM UTILISATION GROUP BY ACCESSOIRE HAVING COUNT ( DISTINCT ( TITRE DE NUMERO ) ) >1;

SELECT NOCAMION FROM ACCESSOIRES A, UTILISATION U, PERSONNEL P WHERE A.NOMACCESSOIRE = U.ACCESSOIRE AND U.UTILISATEUR = P.NOM AND P.ROLE = ‘JONGLEUR’;

SELECT COUNT ( DISTINCT ( TITRE DE NUMERO ) ) FROM NUMEROS;

SELECT COUNT ( DISTINCT ( TITRE DE NUMERO ) ) FROM UTILISATION WHERE ACCESSOIRE = ‘BALLE’ OR ACCESSOIRE = ‘BALLON’;


OU

WHERE ACCESSOIRE IN ( ‘BALLE’, ‘BALLON’ )

SELECT SUM ( VOLUME ), AVG ( VOLUME ) FROM ACCESSOIRES;

SELECT ACCESSOIRE FROM UTILISATION GROUP BY ACCESSOIRE HAVING COUNT ( DISTINCT ( TITRE DE NUMERO ) ) = ( SELECT COUNT ( * ) FROM NUMEROS );

SELECT NOMACCESSOIRES FROM ACCESSOIRES WHERE NOMACCESSOIRES NOT IN ( SELECT DISTINCT ACCESSOIRE FROM UTILISATION );












Examen Intermédiaire Bases de données
« Le département Photocopieurs »


AttributsTechnicienPhotocop.Catégorie Photocop.SociétéServiceContratType contratInterventionType interventionPièceN° TechnicienXNom TechnicienXAdresse TechnicienXN° Série Photocop.XCode catégorieXDate mise
en serviceXNom
sociétéXNom
serviceXN°
ContratXDate début contratXDate fin contratXCode type contratXLibellé
type
contratXPrix
contratXN° interventionXVille
sociétéXDate
arrivée (F)Heure arrivée (F)Temps présence
(F)Code type interventionXFrais intervention (F)N° pièceXQuantité pièce (LF)

(F) : appartient à Feuille
(LF) : appartient à Ligne Feuille


1)


























































Penser à rajouter les propriétés de chaque entité et chaque association

2)



















































3) SELECT P.N°SERIE FROM PHOTOCOPIEURS P WHERE P.N°CONTRAT = 001 ;

SELECT P.N°PIECES, SUM(P.QUANTITE) FROM PIECES P WHERE I.N°INTERVENTION = P.N°INTERVENTION
AND ( I.DATE BETWEEN 01/08/2000 AND 31/08/2000)
GROUP BY P.N°PIECE

SELECT N°PIECE, COUNT(DISTINCT N°INTERVENTION) FROM LIGNE
GROUP BY N°PIECE

Organisation Physique des données



Objectif : efficacité

Organisation :

Hachage
Séquentiel
B arbre


Modèle physique

Fichier
Disque / RAM
Bloc : quantité d’information minimale qui passe de le mémoire centrale à la mémoire vive
Pointeur : adresse


Organisation en TAS : enregistrements les uns derrières les autres
Stockage
R : Facteur de blocage : | Taille du bloc / Taille de l’enregistrement |

( : nombre d’enregistrements par bloc )

Coût de stockage de N enregistrement : | N / R | blocs

Recherche N’ : N’ / 2R statistiquement si l’enregistrement existe,

Sinon : N’ / R ( cas où il n’y est pas )

Insertion
Lecture (du dernier bloc)
Ecriture
2 accès

Suppression

Mise à jour

Hachage

Soit f(e) une fonction de hachage et e un élément tel que f(e) appartient à [ 0,n ]


Indice Table de hachage

 0


 n


Adresse d’un élément Liste d’éléments chaînés


Exemple :
- On veut stocker une liste de noms
- Pour chaque nom on fait la somme des indice des lettres des noms
- Cet indice modulo 10 (par exemple)
- Le reste constitue un indice de groupe
- Quand on cherche un nom, on calcule comme précédemment et on va directement à la liste d’indice égal au résultat du calcul
le nom se trouve dans cette chaîne

D U C H
4 / 21 / 3 / 8 : somme = 36 mod 10 = 6





TAS








PS: il faut que la table de hachage rentre en mémoire vive




Indexé ( ISAM en anglais ) : système d’arbre

e clé table d’index










S’il y a trop de clefs, on fait d’autre tables














B arbre : arbre de profondeur constante ( B = balanced = équilibré )

Toutes les feuilles sont à la même hauteur.
Protection des SGBD


Intégrité des données

- Cohérentes

Statique

Domaine : plage de types de la données

Ex: DB2 IBM

CREATE TABLE EMPLOYE (NOM INTEGER NOT NULL, DATE EMBAUCHE DATE NOT NULL WITH)

Clé primaire :

CREATE TABLE … (…) PRIMARY KEY ( Num))

CREATE UNIQUE INDEX I1 ON EMPLOYE (NOM)

Clé étrangère :

CREATE TABLE (…) FOREIGN KEY (DEPT) REFERENCES (DEPARTEMENT)



Option ON DELETE RESTRICT
SET NULL
CASCADE


Restrict : Supprime l’enregistrement de la table parent que si les tables qui dépendent d’elle ne la référencent pas

Set null : Si l’élément parent est supprimé alors la clé = Null

Cascade : Tous les enregistrements utilisant cette référence sont supprimés



Dynamique

Trigger


Concept de transaction

Transaction : une unité de traitement séquentiel exécutée pour le compte d’un usager, qui, appliquée à une BD cohérente restitue une cohérente






T1 T2 T1 et T2 cohérent : transaction


Problème :

Temps T1 Etat de la base T2

T1 Lire (X) X=100 -
T2 - - Lire(X)
T3 X=X+100 - -
T4 - - X=X+200
T5 Ecrire(X) 200 -
T6 - 300 Ecrire(X)

PB : X devrait être égal à 400


Autre problème :

Y=2U

Temps T1 X Y T2

T1 X=10 5 10 -
T2 Ecrire(X) 10 10 -
T3 - 10 10 X :=30
T4 - 30 10 Ecrire(X)
T5 - - - Y :=60
T6 Y :=20 30 60 Ecrire(Y)
T7 Ecrire(Y) 30 20 -


D’où la mise en place de différents mécanismes :
Verrouiller (T), Déverrouiller (T)
PB :
Interblocage
Famine



Sécurité

Identification ( droits d’accès par mot de passe et période de validité )

Physique ( cryptage )
SE – SQL
VUE ( l’intrus voit une vue et non la table )
Droit ( d’accès à certaine table, lecture écriture )


















































 PAGE 32/ NUMPAGES 35

Système de gestion de bases de données
CNAM 2000-2001 Michel SALA  HYPERLINK "mailto:sala@lirmm.fr" sala@lirmm.fr / 0680681843



Objet


DB2


E/A

CODD

Syst. R

IPS

IMS

Fichier séquentiel indexé

90

80

70

60

50

00

DataWare House
Data Mining

Macro

instruction

Requête

Utilisateur

Fichiers
et Dictionnaire

Gestionnaire de fichiers

Gestionnaire SGBD

Compilateur LDD

Code objet

Processeur de requêtes

SGBD

Physique

Exploitant

Administrateur

Utilisateur

Programme

Univers

Sous domaine

Modèle

Type de modèle

Analyse

Test de validation

MCD MCT

Test Intégration

30 %

Spécifications

Test Unitaire

Entre 10 et 20 %

Codage


ARBRE

Racine

PILOTE

Fils

AVION

VOL

VOL

AVION

PILOTE

VOL

AVION

PILOTE

Relationnel
( Problème : calculs réduits par le nombre d’agrégats )

Relation ( jointure )

Nom

Identificateur

Propriétés

Auteur

Livre

N° Auteur

N° ISBN

-
-
-


-
-
-


A écrit

Propriétés

(1,n)

(1,n)

Nb Livre

Nb Auteur

(1,1)

-
-
-




Fabricant

-
-
-




Véhicule



Fabrique

(0,n)

(0,1)



Est un (ISA)

(1,1)

Conducteur



Conduit

N° Auteur

(0,n)

(1,1)

-
-
-




Camion

-
-
-


Généralisation

(0,n)

(0,1)

Spécialisation

Subit

(0,n)

(0,n)

-
-
-




Test

-
-
-




Patient



(1,1)



Examine

(0,n)

-
-
-




Médecin

Date

Mariage

(0,n)

(0,n)

-
-
-




Femme

-
-
-




Homme

Date

Rencontre

Rencontre

Date

Mariage

Date

-
-
-




Femme

-
-
-




Femme

-
-
-




Homme

Date

Rencontre

-
-
-




Homme

Paiement charge



Achat

Montant

Montant charge

-
-
-




Propriété

-
-
-




Année

-
-
-




Semaine

-
-
-




Résidence

-
-
-




Type

Genre

N° genre






(0,n)

Client



Appartient

N° Client

- Nom
- Adresse
- Montant
Caution


(1,1)

(0,n)

- titre
- durée



N° film

Film

Emprunt

Date emprunt
Date retour

(0,n)

(0,n)

(0,n)



Contient

Boutique

N° Boutique

- Adresse





Détient

(1,1)

(0,n)



N° K7

K7

(0,1)

Date emprunt
Date retour

Emprunt

Détient

Contient

Appartient

(1,1)

(0,n)

(0,n)

(1,1)

(0,n)

(0,n)





(0,1)

- N° Série
- Date de MES
- Code catégorie
- Code contrat

(0,n)



(0,n)



N° K7

K7

- Adresse



N° Boutique

Boutique

- Nom
- Adresse
- Montant
Caution


N° Client

Client

- titre
- durée



N° film

Film






N° genre

Genre

Travaille sur



(1,n)

(1,n)

Possède

(1,1)

(1,n)

(0,1)



Salarié

(1,n)

Est affecté à













Service

- Nom division


Code Division

Division

(1,1)

Catégorie

(1,1)

- Nom projet
- Budjet projet



N° Projet

Projet

(1,1)

(1,n)



Est soumis à



Rataché à soumis à



Cinéma



Séance

Code Division

(1,n)

(0,n)



Joue

(1,n)

(0,n)

(1,n)

(1,1)







Horaire

- Nom division


Film

- Nom projet
- Budjet projet



N° Projet

Acteur







Salle

(0,n)

(0,n)







Calendrier





Film

(0,n)

(0,n)

- Nom
- Age
- Adresse
- Nationalité



(0,n)

Personne



Appartient



(0,n)

(0,n)



Met en scène

(0,n)

(0,n)





V.O.


Horaire

A créé



(1,1)

(1,1)

(1,1)



Traduit

- N°
- Date
- Montant Rappel
- N° Ordre



Rappel



Compo

Role

Acteur

(0,n)

(0,n)




(1,1)

- N°
- Date
- Date échéance
- Montant HT



Facture

(0,n)

Film

(0,n)

(0,n)





Client

- N°
- Date
- Mode règlement
- Montant total règlem.
- N° Règlement




Règlement

(1,n)

(1,1)

a1





a4

MT Regl Fact

Compense

(1,1)

(0,n)

(1,1)

(0,n)



Envoyé a



Destinée a



Fait par

a1

a4

- N° Film
- Durée


Prof

Film

- N° Casette
- N° Film


K7

- Nom prof





K7

- Code étudiant
- Nom étudiant

Etudiant

(0,n)

(1,n)

Suit

- Nom prof
- Code étudiant










(1,1)

(0,n)

- Nom prof


Prof

- Code étudiant
- Nom étudiant

Etudiant

(1,1)

(1,n)

(1,1)

(0,n)



Joueur



Rencontre

(0,n)

(1,1)



Gain



Sponsor

(1,1)

(0,n)

(1,1)

(0,n)

Utiliser la méthode :
1 ) Combien, de joueur gagnant par rencontre ?
2 ) Combien de rencontre par joueur ?

Technicien

Intervention

Feuille

Ligne Feuille

Photocopieur

Type intervention

Catégorie Photocop.

Pièce

Type contrat

Contrat

Société

Service

Est de type

Concerne

Appartient

Est de type


Concerne

Souscrit

Appartient

Lieu

(0,n)

(0,n)

(0,n)

(0,n)

(1,1)

(1,n)

(0,n)

(1,1)

(1,1)

(0,n)

(0,n)

(0,n)

(1,1)

(1,1)

(1,1)

(1,n)

(0,n)

(0,n)

(1,1)

(0,n)

(1,n)

- N° Pièce

- N° technicien
- Code intervention
- N° pièce
- Quantité

- Nom société

- Nom Service

- N° Intervention
- Code type intervention
- N° Série Photocopieur
- Nom service

- Code type intervention

(1,1)

- N° Intervention
- N° technicien
- Date arrivée
- Heure arrivée
- Temps présence
- Frais

- N° technicien
- Nom technicien
- Adresse

(1,1)

(0,n)

(1,1)

(O,n)

(1,1)

(1,n)

(1,1)

(1,1)

(1,1)

(1,1)

(0,n)

3

(0,n)

4

5

1

2

(1,1)

(1,1)

(0,n)

(1,1)

(0,n)

(0,n)

(0,n)

(0,n)

(0,n)

- Code catégorie

- Code type
- Libellé type

- N° Contrat
- Date début
- Date Fin
- Code Type Contrat

Feuille

Service

Société

Contrat

Type contrat

Catégorie Photocopieur

Photocopieur

Type intervention

Intervention

Technicien

Ligne

Pièce

6

7

8

9





DUCH

DUCH se trouve dans cette chaîne

clé

Adresse

Adresse

clé

Adresse

clé

Adresse

clé

Adresse

clé

Elément

100

500

1 500

100 000

100





0

500





101

Etat 1

Etat 2

T

0


 :