bases de données - cri.ensmp.frboucheba/bd-atc1.pdf · un sgbd est un logiciel permettant d’...

Bases de Données – Sylvain Brandel – 1

Bases de Données

Université Louis-Pasteur de StrasbourgDépartement d'informatique

Youcef Bouchebaba

Pôle API, Boulevard Sébastien Brant67 400 ILLKIRCH

bureau a 253tél : 03 90 24 45 43

e-mail : [email protected]


Chapitre 1

Bases de Données et Systèmes de Gestion de Bases de Données

Bases de Données – Sylvain Brandel – 3Chapitre 1. BD et SGBD

Introduction

• Déf. Une BD est un ensemble de données modélisant les objets d’ une partie du monde réel et servant de support à une application informatique

• Ensemble– structuré– autorisant l’ extraction sélective d’ informations

• Exemple de BD : annuaire• Deux types d'opérations

– manipulation– définition

• Tout ensemble de données ne justifie pas forcément une BD exemple : CDs audio

• Déf. Un SGBD est un logiciel permettant d’ interagir avec une BD• Exemples de SGBD : Access, Oracle, mySQL


Exemple : CDs audio Exemple : École

Applications non coordonnées Applications « touffues »

Résultats différents des souhaits

Analyse

Chapitre 1. BD et SGBD

• Avant de penser BD, poser des questions, étudier, analyser les besoins

• Pourquoi MERISE ? Problèmes des grosses applications

• Principes de base :– méthode globale et intégrée– séparation données / traitements– 3 niveaux de modélisation


Niveau conceptuel : MCD

• Entité : collection d'objets ayant des propriétés analogues et possédant un identifiant

• Exemple : CLASSE < id-classe, nb-élèves, année >

• Association : Lien logique entre plusieurs entités



Niveau conceptuel : MCT

• Événement : compte-rendu fait au SI que quelque chose s'est produit dans l'univers extérieur ou dans le SI

• Les événements peuvent être :– internes– externes

• Opération : actions accomplies par le SI en réaction à un (ou des) événement(s)



Inconvénients de MERISE :- lourdeur- difficulté d'apprentissage

Niveaux organisationnel et technique

• MLD : MCD repensé• MOT : intégration de règles de gestion• Exemples de règles d'organisation :

– à chaque livraison, le magasinier contrôle la marchandise livrée– l'inventaire est effectué tous les ans les 29 et 30 décembre

• MPhD : implantation physique• MOpT : contraintes techniques



Bases de Données

• BD : Collection de représentation de la réalité sous forme de données inter-reliées

• Les précurseurs des BD : les systèmes de fichiers– informations communes – redondance non gérée

• Redondance :– volume de données important– temps de saisie et de mise à jour – manque de cohérence

• Cohérence :– être juste par rapport à une certaine réalité– respect des contraintes d'intégrité

• Centralisation :– BD : entité commune, centrale, à laquelle accèdent tous les utilisateurs



SGBD

• SGBD : ensemble de logiciels permettant aux utilisateurs de manipuler efficacement des données dans une grande masse d’ informations partagées avec d’ autres utilisateurs

• 1ère Génération : fin des 60's– modèles hiérarchique et réseau

• 2ème génération : fin des 70's– modèle relationnel

• 3ème génération : fin des 80's– Extensions objet des systèmes relationnels

• 4ème génération : 90's et actuellement– Internet et Web



1. Toutes les informations sur les données sont représentées au niveau logique et non physique (pas besoin d’ informations sur la façon dont sont enregistrées physiquement les données).

2. Les données sont accessibles uniquement par la combinaison du nom de la table, de la clé primaireet du nom de la colonne (pas de chemin à donner).

3. Une valeur spéciale doit représenter l’ absence de valeur (NULL).4. La description de la base de données doit être accessible comme les données ordinaires

(un dictionnaire des données est enregistré dans la base).5. Un langage doit permettre de définir les données, définir des vues (visions particulières de la base,

enregistrées comme des relations), manipuler les données, définir des contraintes d’ intégrité,des autorisations de gérer des transactions.

6. On peut faire des mises à jour par les vues lorsque c’ est logiquement possible.7. Le langage doit comporter des ordres effectuant l’ insertion, la mise à jour et la suppression des

données (un seul ordre pour effectuer chacune de ces fonctions)8. Indépendance des programmes vis-à-vis de l’ implantation physique des données.9. Indépendance des programmes vis-à-vis de l’ implantation logique des données

(si les informations manipulées par les programmes n’ ont pas été modifiées ou supprimées).10. Les contraintes d’ intégrité doivent pouvoir être définies dans le langage relationnel et enregistrées

dans le dictionnaire des données.11. Indépendance vis-à-vis de la répartition des données sur divers sites.12. On ne peut jamais contourner les contraintes (d’ intégrité ou de sécurité) imposées par le langage

du SGBD en utilisant un langage de plus bas niveau (par exemple le C).

Les 12 règles de Codd



Objectif : assurer - la sécurité des données- la confidentialité des données- l’ intégrité des données- de bonnes performances

Objectifs des SGBD

• Un SGBD doit permettre d’ écrire des applications indépendantes de l’ implantation physique des données

• Plusieurs utilisateurs peuvent accéder simultanément à la BD



• Une perte d'informations peut s'avérer fatale– la sécurité est primordiale

• Sécurité de fonctionnement

• Après un incident (matériel ou logiciel) :– redémarrer le système– remettre la BD dans un état cohérent

• Exemple de transaction : transfert d’ une somme d’ argent entre deux comptes d’ un client d’ une banque.– Validation : COMMIT– Annulation : ROLLBACK

Archivage

Journalisation

Sécurité



Mécanisme des verrous : interdire l'accès à une donnéependant un temps minimal

Risque : deadlock

Sécurité

• Gestion de la concurrence d'accès

• Exemple : éditer un même fichier partagé

• Exemple : Mises à jour perdues.

2 personnes veulent modifier une quantité q en stockTransaction :

- lire (q)- modifier (q) (valeur stockée dans un buffer)- écrire (q) (dans la BD)



• Lecture inconsistante ou lecture impropre

• Lecture non répétitive, ou non reproductible, ou incohérente?

- une transaction T2 lit une valeur donnée par - une autre transaction T1- ensuite la transaction T1 annule son affection de V

la valeur lue par T2 est fausse

Ne peut pas arriver si les modifications effectuées par unetransaction ne sont visibles par les autres qu’ aprèsvalidation de la transaction

- une transaction lit deux fois une même valeur etne trouve pas deux fois le même résultat

Blocage des données suffisamment longtemps pour éviterque les autres transactions ne puisse pas les modifier

Sécurité



- une sélection de lignes récupère des lignes qui sontmodifiées par une autre transaction et nevérifient plus le critère de la sélection

Blocage explicite des lignes en question

- Les SGBD gèrent automatiquement les accèsconcurrents de plusieurs utilisateurs sur lesmêmes lignes des tables

Oracle assure une lecture consistante

Sécurité

• Lignes fantômes

• Traitement des accès concurrents par le SGBD



• Contrôle de l’ accès à la base

• Contrôle de l’ accès aux données

Droits sur les objets

Gestion des droits d'accès

Gestion des privilèges

Vues

Confidentialité



Les commandes qui ne respectent pas ces contraintessont automatiquement annulées

- définition de la clé primaire d’ une table- clés étrangères dont les valeurs doivent exister

dans une autre table- unicité des valeurs d’ une rubrique- fourchette pour les valeurs acceptables- …

Intégrité

• Respect implicite des contraintes d'intégrité :

• Exemple : BD client / fournisseur / produit



• Problème des modèles relationnels : pas de stratégie d'accès aux données

• Solutions

index

clusters

performances dégradées

Performances



LMD Menu

Schéma externe A

Schéma externe B

Schéma conceptuel

Langage hôte

Schéma interne

SGBD

BD SE

Utilisateur

Niveaulogique

Niveauphysique

Architecture des SGBD



Différentes approches :- liaison : schémas externes / schéma conceptuel - schéma externe = dérivation du schéma conceptuel- schéma conceptuel = synthèse des schémas externes

Exemple : BD buveur / vin / cru

- deux utilisateurs peuvent appeler une même variablesous deux noms différents

- deux utilisateurs peuvent appeler deux variables différentessous un même nom

Les différents schémas

• Schéma conceptuel



Il existe autant de visions externes que d'utilisateurs

Mais les visions des différents utilisateurs vont se recouper(informations communes)

Exemple : BD buveur / vin / cru

Description de l'implantation physique

Pris en charge par le système d'exploitation

Les différents schémas

• Schémas externes

• Schéma interne



- bon fonctionnement- création de la BD- déclaration d'un utilisateur- droits d'accès- modification des schémas externes- modification des chemins d'accès aux données- maintien des performances d’ accès aux données- sauvegardes et reprises après pannes - …

- développement d'applications- droits sur ses applications

- utilisation des applications- accès limité

Les différents utilisateurs

• Administrateurs :

• Programmeur :

• Utilisateur final :


-


• LDD

• LMD

définition de : - schéma conceptuel- schémas externes- données- liens- contraintes d'intégrité

interrogation et mises à jour de la base de données instructions interactives :

- outils en ligne- interfaces avec des outils de bureautique ou avec le web

instructions insérées dans un langage de programmation :- langage hôte de 3ème génération- langage hôte de 4ème génération

menus formulaires

Les différents langages



Exemple de fonctionnement d'un SGBD

• Le programme d'application, accédant à la BD via le schéma externe B, demande la lecture d'une donnée de la base


LMD Menu

Schéma externe A

Schéma externe B

Schéma conceptuel

Langage hôte

Schéma interne

SGBD

BD SE

Utilisateur

Niveaulogique

Niveauphysique


Réparer les dommages occasionnés par des pannes

- logicielles- matérielles- réseau

Attitude différente suivant que les fichiers ont étéendommagés ou non

Résistance aux pannes

• Sauvegardes

• Types de pannes

• Reprise après accident



Redo logbuffer

Instance

SGA

LGWRDBWR

Control

files

Data files

Redo log

filesDatabase

Password

file

Parameter

file

Archived

log files

CKPT ARCH

Databasebuffercache

Exemple d'implantation d'un SGBD : Oracle

• Architecture globale



• Fichiers de la base

• Images "avant" et "après" Images "avant" : fichiers systèmes, ou emplacement spécial

de la base, contenant les informations nécessaires pourremettre la base dans un état antérieur à une modification

Image "après" : fichiers système contenant les informationsnécessaires pour refaire une modification à partir d'unétat antérieur de la base

Control

files

Data filesRedo log

filesDatabasePassword

file

Parameter

file

ALERT

file





• Images "avant" et "après"

DML Statement

New image

Table

Old image

Rollbacksegment



La place disque réservée aux images "avant" et "après" estlimitée : elle est recyclée pour enregistrer les dernièresimages

Il peut être intéressant d'archiver sur des médias stablesles images "après" pour les réutiliser en cas de panne

On peut ainsi dérouler toutes les dernières actions effectuéespar les utilisateurs depuis la dernière sauvegarde

Les différents SGBD peuvent avoir des implantationstrès différentes sur certains points, en particuliersur la façon de traiter les accès concurrents


• Archivage des images "après"




• Processus clients – serveurs

Oracle Server

Server process

User process

User

Client Server




• Écriture des données dans la base

DBWR

InstanceSGA

Databasebuffercache

Shared pool

ControlfilesData files

Redo logfilesDatabase



New image

Table

Image at statement commencement

SELECT * FROM table;


• Segments de rollback



member

member

member

member

member

member

Group 1 Group 2 Group 3

Disk 1

Disk 2


• Fichiers redo log



Chapitre 2

Le Modèle Relationnel


Relations

♦ Produit cartésien d'un ensemble de domaines D1, D2, … Dn :ensemble des vecteurs (d1, d2, … dn) où di est une valeurde Di (1 ≤ i ≤ n)

définition en "extension" : en donnant la liste des valeurs(ex : couleur-vins = {blanc, rouge, rosé} crus = {chablis, sylvaner} )

♦ Domaine : ensemble de valeurs caractérisées par un nom

définition en "intension" : en donnant une caractéristique(ex : caractères = chaînes de caractères de longueur

fixe ou variable )

ex : couleurs-vins × crus =

blanc chablisblanc sylvanerrouge chablisrouge sylvanerrosé chablisrosé sylvaner

Chapitre 2. Le Modèle Relationnel


Relations

♦ Colonne : sous-ensemble des valeurs d'un domaine

♦ Relation : sous-ensemble du produit cartésien d'une liste dede domaines, caractérisé par un nom

ex : un sous-ensemble decouleurs-vins × crus =

blanc chablisblanc sylvanerrouge chablisrosé chablis

ex : domaine = ensemble des entiers,une colonne =

12512

couleurs-crus



Relations

♦ Tuples : ligne d'une relation correspondant à un enregistrement(appelé aussi n-uplet ou ligne)

♦ Attribut : colonne d'une relation caractérisée par un nom (pas forcément le même que le nom de domaine)

ex : un tuple de couleurs-crus = (blanc, sylvaner)

signification sémantique d'une colonne

ex : attributs de couleurs-crus = couleur et cru

♦ Schéma de relation : nom de la relation suivi de la liste de sesattributs et de la définition de leurs domaines

ex : couleurs-crus (couleur : charvar, cru : charvar) vins (nv : integer, cru : charvar, degré : float)



Tables

♦ Propriétés d'une relation : nom unique dans l'ensemble des relations de la base nombre fixe de colonnes nombre variable de lignes

F# Nom Ville

F1F2F3F4F5

SmithMeyerMullerRyanMartin

LondresStrasbourgStrasbourgLondresParis

P#

P1P5P18

Nom

visécrou 6 pansboulon

F#

F1F1F3F1F5

P#

P1P18P5P18P18

ex : fournisseur pièce fournit

♦ Une BD relationnelle est un ensemble de relations : relations de l'utilisateur (relations de tables) relations de catalogues (relations systèmes)



Colonnes

♦ unicité du nom de l'attribut dans une relation(mais pas forcément dans la base)

colonne = champ = attribut

ex : fournisseur : F# fournit : F#

♦ L'attribut prend sa valeur dans un domaineex : ville ∈ { Strasbourg, Paris, Londres, New-York}

♦ L'ordre des attributs n'a pas d'importanceex : fournisseur (F#, Nom, Ville) = fournisseur (Nom, F#, Ville)

♦ Le nombre d'attributs définit le degré de la relation



Lignes

♦ Les lignes sont supposées toutes différentes

ligne = n-uplet = tuple

F# Nom Ville

F1F2F1F4F5

SmithMeyerSmithRyanMartin

LondresStrasbourgLondresLondresParis

ex : fournisseur INTERDIT

♦ L'ordre des lignes n'a pas d'importanceP#

P1P5P18

Nom


ex : pièce = pièce

P#

P1P18P5

Nom

visboulonécrou 6 pans

♦ Le nombre de n-uplets définit la cardinalité



Catalogue

♦ Tables créées et gérées par le système :

catalogue des relations

catalogue des constituants

catalogue des chemins d'accès

catalogue des utilisateurs

catalogue des droits d'accès

etc.

♦ Les utilisateurs y accèdent uniquement en lecture



Langages d'interrogation

♦ Trois théories mathématiques :

algèbre relationnelle langage SQL (Oracle, mySQL, …)

calcul relationnel des tuples langage QUEL (Ingres)

calcul relationnel des domaines langage QBE (IBM)

♦ L'algèbre relationnelle est de loin la théorie la plus utilisée



Algèbre relationnelle

♦ Opérations de base :

opérations ensemblistes :- union- différence- produit cartésien

opérations spécifiques :- projection- restriction- jointure

♦ Opérations dérivées :- intersection- division- complément- éclatement- jointure externe- semi-jointure



Union

♦ Opération binaire appliquée à deux relations de même schémaex : fournisseur1 (F#, Nom, Ville), fournisseur2 (F#, Nom, Ville)

F# Nom Ville

F1F2F3

SmithMeyerMuller

LondresStrasbourgStrasbourg

F# Nom Ville

F2F4

MeyerRyan

StrasbourgLondres

∪


F# Nom Ville

F1F2F3F4

SmithMeyerMullerRyan

LondresStrasbourgStrasbourgLondres


Différence


F# Nom Ville

F1F2F3

SmithMeyerMuller


F# Nom Ville

F2F4

MeyerRyan

StrasbourgLondres

−

♦ Opérateur non commutatif


F# Nom Ville

F1F3

SmithMuller

LondresStrasbourg


Produit cartésien

ex : R1(A, B), R2(C, D)

R1 × R2 = R(A, B, C, D)

ex : R (A, B), R (A, B)

problème de nom

en SQL on peut renommer les relations le tempsde la requête

♦ Opération binaire appliquée à deux relations deschémas quelconques



Produit cartésien

ex : fournisseur1 (F#, Nom, Ville), pièce (P#, Nom)

F# Nom Ville

F1F2F3

SmithMeyerMuller


×P#

P1P5P18

Nom



F# fournisseur.Nom Ville

F1F1F1F2F2F2F3F3F3

SmithSmithSmithMeyerMeyerMeyerMullerMullerMuller

LondresLondresLondresStrasbourgStrasbourgStrasbourgStrasbourgStrasbourgStrasbourg

P#

P5P1P18P5P1P18P5P1P18

pièce.Nom

visécrou 6 pansboulonvisécrou 6 pansboulonvisécrou 6 pansboulon


Projection

♦ Opération unaire : sélection d'une ou plusieurs colonnesd'une relation

F# Nom Ville

F1F2F3F4F5



ex : fournisseur

projVille(fournisseur) projNom, Ville(fournisseur)


Ville

LondresStrasbourgParis

Nom Ville




Restriction (ou sélection)

♦ Opération unaire : sélection d'une ou plusieurs lignesd'une relation

♦ R | E

E : <attribut> θ <valeur>

θ ∈ {=, ≠, <, ≤, >, ≥}

Formule atomique :<attribut> θ <attribut><attribut> θ <constante>

Les formules atomiques peuvent être reliées pardes connectivités - ET

- OU- NON



Restriction (ou sélection)

F# Nom Ville

F1F2F3F4F5



ex : fournisseur

fournisseur | ville = "Londres"

ex : stock(P#, Quantité-stock, Quantité-seuil) stock | quantité-stock < quantité-seuil


F# Nom Ville

F1F4

SmithRyan

LondresLondres


Jointure

♦ Opération binaire : composition de deux relations à l'aided'un critère de jointure

F# Nom Ville

F1F2F3F4F5



ex : fournisseur (F#, Nom, Ville), fournit(F#, P#)F#

F1F1F3F2F5

P#

P1P18P5P18P18

*

=


F# Nom Ville

F1F1F3F2F5

SmithSmithMeyerMullerMartin

LondresLondresStrasbourgStrasbourgParis

P#

P1P18P18P5P18


Jointure

♦ La jointure peut être obtenue par sélection et projection duproduit cartésien

ex : fournisseur * fournit =projF#, Nom, Ville, P# (fournisseur × fournit |

fournit.F# = fournisseur.F#)

♦ Le θ-produit est défini en remplaçant l'égalité par n'importequel opérateur de l'ensemble θ

le θ-produit est noté inéqui-jointure (la jointure est alors notée équi-jointure)

♦ Lorsque plusieurs attributs sont communs aux deux relations,la comparaison porte sur tous les attributs communs



Intersection


F# Nom Ville

F1F2F3

SmithMeyerMuller


F# Nom Ville

F2F4

MeyerRyan

StrasbourgLondres

∩

=

♦ Opération redondante : elle peut être obtenue par combinaisond'opérateurs de base

R1 ∩ R2 = R1 − (R1 − R2), ou bien R1 ∩ R2 = R2 − (R2 − R1).


F# Nom Ville

F2 Meyer Strasbourg


Division

♦ Opération binaire appliquée à deux relations deschémas quelconques

D1(A1,… Ap, Ap+1, … An) ÷ D2(Ap+1, … An)= Q(A1, … Ap) dont les tuples sont ceux qui concaténés à

tout tuple de D2 donnent un tuple de D1

ex :Nom Ville

MullerMullerMeyerMeyerMartin

StrasbourgStrasbourgStrasbourgStrasbourgParis

P#

P1P5P18P5P18

Ville

StrasbourgStrasbourg

P#

P18P5

÷

=


Nom

Meyer


Complément

♦ Opération unaireex : fournit (F#, P#)

F#

F1F1F3F2F5

P#

P1P18P5P18P18

¬ fournit


F# P#

F1F2F2F3F3F4F4F4F5F5

P5P1P5P1P18P1P5P18P1P5


Le langage SQL : interrogation

♦ Structured Query Language

♦ Requête :

SELECT ... (attributs sur lesquels on projette)FROM ... (relation, ou produit cartésien)[WHERE ...] (sélection, optionnel)




♦ Exemple fournisseur / pièce / fournit

fournisseur(F#, Nom, ville)pièce(P#, Nom)fournit(F#, P#)


projVille(fournisseur)

SELECT VilleFROM fournisseur ;

projNom, Ville(fournisseur)

SELECT Nom, VilleFROM fournisseur ;



fournisseur(F#, Nom, ville)pièce(P#, Nom)fournit(F#, P#)


fournisseur | Ville = "Londres"

SELECT F#, Nom, Ville (= SELECT *)FROM fournisseurWHERE Ville = 'Londres' ;

projP#(fournisseur × fournit | fournisseur.F# = fournit.F#et ville = Londres) SELECT P#

FROM fournisseur, fournitWHERE fournisseur.F# = fournit.F#

AND ville = 'Londres' ;



♦ Exemple joueur / rencontre / gain

joueur(nom, prénom, âge, pays)rencontre(nrencontre, gagnant, perdant, lieu-tournoi, date, score)gains(nom-joueur, lieu-tournoi, date, prime, sponsor)

1. Nom et âge des joueurs américains, autres que 'Connors',âgés de plus de 30 ans

2. Les pays des joueurs de 20 ans (sauf France)

3. Nom et âge des joueurs ayant participé à Roland Garros en 99

4. Nom et pays des joueurs ayant participé, en 95,aux tournois de Roland Garros et Wimbledon

5. Nom des joueurs ayant perdu toutes leurs rencontres



Le langage SQL : création de tableCREATE TABLE <nom-de-table> (<colonne> | <contrainte-de-relation>)+ <nom-de-table> : chaîne de caractères simple (ex. etudiant),

ou composé (un nom de schéma suivi d'un nom de table, ex. crous.etudiant) <colonne> = <nom-de-colonne> <type-de-données> [<défaut>] [<contrainte-de-colonne>]

<type-de-données> : - chaînes de caractères de longueur fixe : CHAR (size), où size est facultatif

(omis : taille = 1, max = 255) - chaînes de caractères de longueur variable :

- VAR CHAR 2 (size), où size est obligatoire (max = 2000)- LONG, sans préciser de taille (on peut y mettre des valeurs jusqu'à 2 Go), mais avec restrictions :

- un seul champ de type LONG par relation- contrainte d'intégrité : uniquement NULL ou NOT NULL pour ce champ (NULL = on ne peut rien y mettre)- index interdits- ne peut pas apparaître dans certaines parties d'une commande SQL (WHERE, GROUP BY, DISTINCT...)

- numérique (entier positif, négatif, virgule fixe ou flottante) : NUMBER(p,s), où :- p = précision : nombre total de chiffres (1 ≤ p ≤ 38), facultatif- s = scale : nombre de chiffres à droite de la virgule (s > 0), facultatif ; si s < 0, alors arrondi à gauche de la virgule- NUMBER(p) : s = 0 entiers- NUMBER : p = 38 et s indéfini nombres à virgule flottante- par compatibilité, on peut utiliser NUMERIC, DECIMAL, INTEGER, INT, SMALLINT, FLOAT, REAL, DOUBLE PRECISION

- dates : DATE, toutes les dates valides comprises entre le 1er janvier 4712 av. JC et le 31 décembre 4712 ap. JC. Format : centenaire + année + mois + jour + heure + min + sec Chapitre 2. Le Modèle Relationnel


Le langage SQL : création de table

<défaut> : DEFAULT val, où val est la valeur que l'on donne à l'attribut lorsque l'utilisateurn'en fournit pas

Restriction :- cohérence de type- ne peut pas contenir de référence à une autre colonne- ne peut pas contenir de date non complètement spécifiée

<contrainte-de-colonne> : permet de spécifier différentes CI portant sur un seul attribut,y compris les contraintes référentielles :

- valeur null impossible : NOT NULL - unicité de l'attribut : UNIQUE ou PRIMARY KEY - contrainte référentielle : REFERENCES <table-référencée> [<colonne-référencée>]

(le second argument est optionnel s'il est identique à la colonne référençante) - contrainte générale : CHECK <condition>, la condition pouvant être exprimée avec :

- <Attribut> θ <Valeur>, θ ∈ {=, <>, <, <=, >,>=}- prédicat d'intervalle : BETWEEN AND- prédicat de comparaison de texte : LIKE- test de nullité : NULL- test d'appartenance : IN

<contrainte-de-relation> : peuvent porter sur plusieurs attributs : - contrainte d'unicité : UNIQUE (<attribut>)+ - contrainte référentielle : [FOREIGN KEY (<colonne-référençante>)+]

REFERENCES <table-référencée> [(<colonne-référencée>)+] - contrainte générale : CHECK <condition>



Le langage SQL : création de table

Exemples :

CREATE TABLE prix (age NUMBER (2) PRIMARY KEY, tarif CHAR (2) DEFAULT 'T1', CHECK (age > 25) ) ;

CREATE TABLE etudiant (net NUMBER (2), nom VAR CHAR 2 (30), age NUMBER (2)

[ REFERENCES prix [ (age) ] ], adresse VAR CHAR 2 (60), PRIMARY KEY (net), [ FOREIGN KEY (age)

REFERENCES prix [ (age) ] ] ) ;



Le langage SQL : insertion de données

INSERT INTO <nom-de-table> [ ( <nom-de-colonne> )+ ]{ VALUES ( <constantes> )+ | <commande-de-recherche> }

INSERT INTO etudiant (net, nom, age, adresse)VALUES (1, Meyer, 28, Strasbourg) ;

Exemples :

INSERT INTO prixVALUES SELECT DISTINCT age FROM etudiant

WHERE... ;

Remarque : si toutes les colonnes ne sont pas précisées, les manquantes sont remplies avec NULL



Le langage SQL : suppression de données

DELETE FROM <nom-de-table>[ WHERE { <condition-de-recherche> |

CURRENT OF <nom-de-curseur> } ]

DELETE FROM etudiantWHERE nom LIKE Meyer OR age > 36 ;

Exemples :

DELETE FROM prix ;

Remarque : le second exemple élimine toute la table



Le langage SQL : mises à jour

UPDATE <nom-de-table>SET { <nom-de-colonne> =

{ <expression-de-valeur> | NULL } }+

WHERE { <condition-de-recherche> |CURRENT OF <nom-de-curseur> }

UPDATE prixSET tarif = 'T12'WHERE age = 30 ;

Exemples :

UPDATE etudiantSET age = age + 1WHERE nom = Meyer ;

Remarque : seuls les n-uplets vérifiant la condition (optionnelle) sont mis à jour



Dépendances fonctionnelles

♦ Contraintes d'intégrités très fortes

Soit R une relation dont les attributs sontU = {u1, u2, ..., un} et X ⊂ U, Y ⊂ U

∀ u ∈ R, ∀ v ∈ R,projX (R | u) = projX (R | v) ⇒ projY (R | u) = projY (R | v)

Y dépend fonctionnellement de X, ou X détermine Y, si :


u1

aa

u2

bb

u3 … … un

X = u1 et Y = u2 par ex.

Si deux lignes ont la même valeur sur X,alors ils ont la même valeur sur Y

dans ce cas, X → Y


Dépendances fonctionnelles : exemples

♦ n-ch

123456

type

ABAABC

nb-lits

212213

douche

ouiouinonnonouinon

♦ < Nom, Prénom, Ville, CP, Département >

DF ?

DF ?

♦ < Nom, Prénom, Moyenne, Âge, Enseignant > DF ?



Règles d'inférence


♦ Réflexivité

♦ Augmentation

X = {Nom, Catégorie}Y = Nom ou Y = {Catégorie} ou Y = {Nom, Catégorie}

Nom, Catégorie → NomNom, Catégorie → Catégorie

CP → Département CP, Ville → Département, Ville CP, Ville → Département

♦ Transitivité

CP → DépartementDépartement → Pays

CP → Pays


Règles d'inférence


♦ Union

♦ Pseudo-transitivité

Nom → CatégorieNom → NbEtoiles

Nom → Catégorie, NbEtoiles

♦ Décomposition

CP → DépartementDépartement, Langue → Pays

CP, Langue → Pays

Nom → Catégorie, NbEtoiles Nom → Catégorie


Ajout / Suppression d'attributs

Supposons G1, G2 → D1, D2 VRAIE


♦ Ajout d'attributs à gauche : X, G1, G2 → D1, D2

♦ Ajout d'attributs à droite : NON (à moins qu'une autre DF le permette)

♦ Suppressions d'attributs à gauche : NON (à moins qu'une autre DF le permette)

♦ Suppression d'attributs à droite : G1, G2 → D1


Choix des DF à retenir

♦ Ne pas trop simplifier par transitivitéX → YY → Z

il vaut mieux garder les deux premières

X → Z

♦ Autre simplificationG1 → X

il vaut mieux garder la première

G1, G2 → X



Fermeture

♦ Ensemble des DF obtenues à partir de toutes les décompositionspossibles des DF de F

F1 + DF (obtenue par application d'une règle) F2

F2 + DF (obtenue par application d'une règle) F3

…

Fn + DF (obtenue par application d'une règle) Fn+1

Lorsqu'il n'est plus possible de trouver de nouvelle DF F+ = Fn



Couverture

♦ Ensemble minimal de DF suffisant à retrouver F

Méthode : on enlève une DF et on essaye de la retrouver à partir des DF restantes

Exemple : Q → ST → RR, S → PT, Q → PP, R → S

F = {

}


Est-il possible de reconstituer F à partir de F' = { T → RR, S → PT, Q → PP, R → S

}

?

Même démarche avec toutes les DF de F


DF élémentaires, clés

♦ Y pleinement dépendant de X s'il n'existe pas de sous-ensemblestrict X' de X tel que X' → X

♦ Une clé est un ensemble minimal d'attributs qui déterminetous les autres



Passage DF – schéma relationnel

nom de l'attribut type descriptifnom chaîne nom de l'hôtel (unique dans une région donnée)catégorie entier nombre d'étoiles de l'hôtelnb-chambres entier nombre total de chambres pour un hôtel donnénum-chambre entier numéro de chambretype entier type de chambreprix réel prix d'une chambrenbre-lits entier nombre de litsdouche booléen vrai si la chambre comporte une douche privéebaignoire booléen vrai si la chambre comporte une baignoire privéeWC booléen vrai si la chambre comporte des WC privéslib-div chaîne libellé des divertissements proposés par l'hôtelgratuit booléen indique si un divertissement proposé par un hôtel est gratuit ou payantsaison {'H','B'} H : haute saison, B : basse saison

Le type de chambre détermine les commodités offertes : nombre de lits, présence de douche, debaignoire, de WC indépendants. Le prix d'une chambre dépend du type de la chambre. Ainsi, dans un hôtel donné, toutes les chambres de même type auront le même prix. Cette hypothèse n'est pas vraiepour deux hôtels différents. Le prix d'une chambre dépend également de la saison : haute ou basse



Passage DF – schéma relationnel

1. Les dépendances fonctionnelles suivantes sont-elles correctes ?

1. nom → catégorie, nb-chambres2. num-chambre → type3. type → prix4. type → nbre-lits5. type, prix → douche, baignoire, WC6. nom, catégorie → nb-chambres7. nom, num-chambre →type, douche, baignoire, WC8. nom, gratuit → lib-div9. catégorie, lib-div → gratuit10. nom, saison, type → nbre-lits

2. Déterminer la fermeture

3. Déterminer la couverture

4. Transformation en modèle relationnel



Normalisation

♦ Exemple : base étudiantsnet : numéro d'étudiantnomâgeadressetarif : tarif CROUS ∈ {T1, T2, T3} selon l'âge de l'étudiantmatièreenseignant : enseignant responsable de la matièremoyenne : moyenne dans une matière donnée

ETUDIANT < net, nom, âge, adresse, tarif, matière, enseignant, moyenne>

net → nom, âge, adressematière → enseignantnet, matière → moyenneâge → tarif

DF : clé ? vérifier qu'aucun attribut ne manque



Pourquoi normaliser

Redondance et anomalies :

Insertion, suppression Redondance Mise à jour Effet de bord

net nom âge adresse tarif matière enseignant moyenne

121240

MeyerMeyerMuller

252518

StrasbourgStrasbourgBrumath

T1

T1

T2

UV1UV2UV3

BrandelTellierAgnus

NULL1014

Pour y remédier formes normales



1ère Forme normale (1FN)

♦ Exemple :

net adresse

numéro rue ville CP

nom

INTERDIT

Soit mise à plat :

net numéro rue ville CP nom

Soit nouvelle relation :net adresse rue ville CPnom

12

id-adr

12

et




Si on a un ensemble (tous les éléments distincts), on duplique :

Si on a une liste (différent d'un ensemble car l'ordre a une importanceet on peut avoir plusieurs fois la même valeur), il faut rajouter unattribut :

net matière note

1 UV1 {12, 11, 16}

net matière note

111

UV1UV1UV1

121116

net note

1 UV1 {12, 11, 12}

net matière note

111

UV1UV1UV1

121112

ordre

123

matière




♦ Exemple : base étudiants


En 1FN ?

♦ Remarque : l'atomicité d'un domaine dépend du niveau dedécomposition



2ème Forme normale (2FN)

♦ Une relation est en 2FN si :- elle est en 1FN- aucun attribut n'est dépendant d'une partie seulement

de la clé

♦ Exemple typique de relation NON en 2FN :

X Y Z WR :

parce que Y → W on décompose en : R1 <Y, W>R2 <X, Y, Z>






En 1FN ? OUI

La clé est le couple net, matière

net, matière → nom pleine ? net, matière → âge pleine ? net, matière → adresse pleine ? net, matière → tarif pleine ? net, matière → enseignant pleine ? net, matière → moyenne pleine ?

En 2FN ? NON




on découpe d'après les DF :net → nom, âge, adresse, tarifmatière → enseignantnet, matière → moyenne

ETUDIANT<net, nom, âge, adresse, tarif>ENSEIGNEMENT<matière, enseignant>NOTE<net, matière, moyenne>

En 2FN ? OUI

net matière nom âge adresse tarif enseignant moyenne




♦ Remarque : si on supprime une moyenne, on ne perd plusd'étudiant

♦ Par contre, il faut fixer une Contrainte d'Intégrité référentielle(CI réf) exprimant que si on supprime un étudiant,il faut que la moyenne disparaisse aussi :

CI réf : projnet (NOTE) ⊂ projnet (ETUDIANT) projmatière (NOTE) ⊂ projmatière (ENSEIGNEMENT)




♦ Une relation est en 3FN si :- elle est en 2FN- tous les attributs n'appartenant pas à une clé sont directement

dépendants d'une clé

♦ Exemple typique de relation NON en 3FN :

X Y Z WR :

parce que Y → W on décompose en : R1 <Y, W>R2 <X, Y, Z>





ETUDIANT<net, nom, âge, adresse, tarif>ENSEIGNEMENT<matière, enseignant>NOTE<net, matière, moyenne>

En 2FN ? OUI

ETUDIANT<net, nom, âge, adresse, tarif> en 3FN ? NON parce que net → âge → tarif

on découpe en

ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>

ENSEIGNEMENT<matière, enseignant> en 3FN ? OUI

NOTE<net, matière, moyenne> en 3FN ? OUI




Le schéma devient alors :

ETUDIANT <net, nom, âge, adresse>PRIX <âge, tarif>ENSEIGNEMENT <matière, enseignant>NOTE <net, matière, moyenne>

En 3FN ? OUI

♦ CI réf à ajouter pour éviter des redondances en cas desuppression dans la relation ETUDIANT :

CI réf : projâge (PRIX) ⊂ projâge (ETUDIANT)



Forme normale de Boyce-Codd (FNBC)

♦ Une relation est en FNBC s'il n'y a pas d'autres DF que K → A, où- K est la clé- A est un attribut non clé

♦ Exemple typique de relation NON en FNBC :

X Y Z WR :

parce que W → X on décompose en : R1 <W, X>R2 <X, Y, Z>





En FNBC ? OUI


LOCALISATION <cru, pays, région, qualité>

♦ Exemple : base vins

cru, pays → régioncru, pays → qualitérégion → pays

DF :




En 3FN ? OUI parce que le cru ne détermine pas la région

En FNBC ?

cru pays région qualité

NON parce que région → pays

on décompose :

CRUS <cru, pays, qualité>REGION <région, pays>

cru, pays → qualitérégion → cru

DF :

La décomposition est sans perte malgré la perte d'une DF




♦ Exemplenet matière sport

11112

BDBDAlgoAlgoAlgo

escaladetennisescaladetennisVTT clé : net, cours, sport

Redondances, mais pas de DF permettant de lessupprimer

4FN : COURS <net, cours>SPORT <net, sport>




♦ Exemple

clé : buveur, cru, producteur

Redondances, mais pas de DF ni de DM permettant de lessupprimer

5FN :

buveur

MeyerMeyerMeyerBrandel

cru

EdelEdelGewurtzEdel

producteur

ClaudeAndréAndréAndré

buveur

MeyerMeyerBrandel

cru

EdelGewurtzEdel

producteur

ClaudeAndréAndré

buveur

MeyerMeyerBrandel

cru

EdelEdelGewurtz

producteur

ClaudeAndréAndré



Exemples de décomposition

R <A, B, C>

Quelle est la décomposition en 3FN dans les trois cas :

1. F = {A → B, B → C }2. F = {A → B, C}3. F = { }



Exemples de décomposition

Quelle est la décomposition en 3FN ?

GITES < nom, nb-chambres, num-chambre, type, prix, nbre-lits, douche, baignoire, WC, lib-div, gratuit, saison>

F0 = { nom → catégorie, nbre-chambres

nom, num-chambre → typetype → nb-lits, douche, baignoire, WCnom, lib-div → gratuitnom, type, saison → prix }



Insertion – suppression

♦ Emploi de n-uplets, sans précision sur son mode d'obtention(entrée au clavier + vérification)

♦ Couple pré-condition – post-condition :- pré-condition : doit être vérifiée pour avoir le droit

d'exécuter l'opération- post-condition : le résultat de l'opération doit la vérifier

♦ L'ordre d'insertion / suppression dans les relations est important

♦ Vérifier qu'il n'y a pas de situation d'interblocage :par exemple insérer avant un net peut permettred'insérer autre chose




DF = { net → nom, âge, adressematière → enseignantnet, matière → moyenneâge → tarif }

CI réf : projnet (NOTE) ⊆ projnet (ETUDIANT)projmatière (NOTE) ⊆ projmatière (ENSEIGNEMENT)projâge (ETUDIANT) ⊆ projâge (PRIX)

♦ Exemple : base étudiant


Attributs : net, nom, âge, adresse, tarif, matière, enseignant, moyenne




♦ Insertion dans PRIXOn cherche à insérer un n-uplet n dans PRIX

- pré-condition : n.âge ∉ projâge (PRIX)- post-condition : PRIX = PRIX ∪ {n}

♦ Suppression dans PRIXOn cherche à supprimer un n-uplet n de PRIX

- pré-condition : n.âge ∉ projâge (ETUDIANT) - post-condition : PRIX = PRIX - {n}

Suppression en cascade :- pré-condition : VRAI- post-condition : - PRIX = PRIX - {n} - ETUDIANT = ETUDIANT –

(ETUDIANT | âge = n.âge) - NOTE = NOTE –

(NOTE | net ∈ projnet (ETUDIANT | âge = n.âge))




♦ Insertion d'un n-uplet n dans ETUDIANT :- pré-condition : - n.net ∉ projnet (ETUDIANT) - n.âge ∈ projâge (PRIX)- post-condition : ETUDIANT = ETUDIANT ∪ {n}

♦ Suppression dans ETUDIANT :- pré-condition : n.net ∉ projnet (NOTE)- post-condition : ETUDIANT = ETUDIANT – {n}

Suppression en cascade :- pré-condition : VRAI- post-condition : - ETUDIANT = ETUDIANT – {n} - NOTE = NOTE - (NOTE | net = n.net)




♦ Insertion d'un n-uplet n dans NOTE :- pré-condition : - (n.net, n.matière) ∉ projnet, matière (NOTE)

(différent de (n.net ∉ projnet (NOTE) et n.matière) ∉ projmatière (NOTE))

- n.net ∈ projnet (ETUDIANT) - n.matière ∈ projmatière (ENSEIGNEMENT) - post-condition : NOTE = NOTE ∪ {n}♦ Suppression dans NOTE :

- pré-condition : VRAI (ne vient pas de suppression en cascade mais de l'absence de conditions)

- post-condition : NOTE = NOTE - {n}



Chapitre 3

Le Modèle Entités / Associations


Entité

♦ Entité = individu dans MERISE

♦ classe (ou type) d'entité : une classe regroupetoutes les entités de même nature

Exemple : M. Meyer, département de vente d'une entreprise... M. Meyer est UNE entité

♦ Entité = objet concret ou abstrait qui peut être distinctementidentifié dans l'esprit du concepteuret qui présente un intérêt particulier

Exemple : M. Meyer classe Personne(M. Meyer est une occurrence de la classe Personne)

Exemple : M. Meyer possède une 205

Chapitre 3. Le Modèle Entités / Associations

Personne Personne Voiture


Association

♦ Association = relation dans MERISE

♦ Classe (ou type) d'association =ensemble des associations ayant même sémantique,définies sur un même type d'entités

♦ Association = relation entre une ou plusieurs entités

♦ Un type d'association ne peut pas être directement reliéavec un autre type d'association :

INTERDIT (sauf extension agrégation)



Association

♦ Un type d'entité de peut être relié à un autre type d'entitéque par un lien d'association :

INTERDIT

♦ Il n'y a pas de restriction sur le nombre de typesd'associations entre types d'entités :


Personne VoiturePossède

Conduit

Habite Ville


Cardinalité et fonctionnalité

♦ Cardinalité min et max reportées sur la graphique :

Personne Possède Voiturea,b c,d

a = nombre min d'occurrences de B liées à l'occurrence de A par A - B (∈ {0,1})b = nombre max d'occurrences de B liées à l'occurrence de A par A - B (∈ {1,n})c = nombre min d'occurrences de A liées à l'occurrence de B par A - B (∈ {0,1})d = nombre max d'occurrences de A liées à l'occurrence de B par A - B (∈ {1,n})

Remarque : selon les auteurs, les couples (a,b) et (c,d)peuvent être représentés sur une liaison ou sur l'autre


Personne VoiturePossède

Conduit

Habite

0,n 1,1

0,n 0,n

0,n

0,n

1 × n

m × n

Ville



♦ cardinalité : couple (a,b) ou (c,d). 4 alternatives pour lesdeux couples (a,b) et (c,d) : 0,1 - 0,n - 1,1 - 1,nPas d'autre choix possible.

♦ fonctionnalité : couple (b,d). 3 alternatives : 1 × 1 - 1 × n - n × m

♦ Dimension (ou ordre ou degré) du type d'association =nombre de types d'entités distinctes qui interviennentdans le type d'association.

relation unaire (dimension = 1) :

femme

mari

Personn

e

marié à Personn

e

Personn

e

marié à




relation binaire (dimension = 2) :

relation ternaire (dimension = 3) :

…relation n-aire (dimension = n) :




Chateau

Salle

Emploi du temps

Enseignant

Horaire Groupe d'étudiants



♦ Problèmes quand il y a plus de deux types d'entités :

♦ Les cardinalités peuvent varier selon les questions que l'on se pose :


Produit Ligne produit

Client

Commande

0,n

1,1

mauvaise modélisation

Passée parProduit Ligne produit Commande1,n0,n 1,1 Client0,n


Attribut

♦ Attribut = propriété dans MERISE

♦ Attribut = propriété d'un type d'entité ou d'association

Personne

idnomâgeadresse

Autre notation :- idnom- âge- adresse

Personne

♦ Attribut :- valeurs simples, pas de structure, pas d'ensemble- valeurs facultatives ou obligatoires

♦ Attribut :- soit des identifiants : clés, soulignés

dans la représentation graphique - soit des descripteurs : tous les autres



Attribut

♦ Attributs du type d'association :clés de chacun des types d'entités de l'association

+ des attributs propres à l'association Produit

Id-fournNom

Ligne catalogue

Prix

0,n 0,nFournisseur

Id-produitDésignation

♦ Clé de l'association : a priori, elle est constituée de toutes les clés de l'ensemble des clés des entités qui interviennent dans l'association

Exemple : la clé de ligne catalogue est (Id-fourn, Id-produit)

En pratique, ce n'est pas toujours le cas :

Produit0,1 Le prix ne dépend que

du numéro de produitChapitre 3. Le Modèle Entités / Associations


Attribut

MatièreId-élèveNom…

InscritNote

0,n 0,nElève

Id-matièreLibellé…

inscrit < Id-élève, Id-matière, note > n'autorise pas d'avoirplusieurs notes pour un étudiant donné dans une matière donnée

analyser la cause de la répétitivitéet introduire un type d'entités pour la traduire

inscrit < Id-élève, Id-matière, Date, note >, on peut à présentavoir plusieurs notes par élève (une par date).


MatièreId-élèveNom…

InscritNote

0,n 0,nElève

Id-matièreLibellé…

DateDate

1,n


Attribut

Il existe des exemples où cela ne fonctionne pas :

Prison

Id-banditNom…

Séjour prison

Date débutDate fin

0,n 0,nBandit

Id-prison…

Date

Date

1,ndébut



Normalisation

♦ L'attribut est affecté au type d'entités ou d'associationsqu'il décrit le plus directement :

Voiture

Date de naissance

Possède

Date d'achat

Personne

Date de 1ère imat

Les trois dates sont très différentes

♦ Choix entité / attribut :

BureauId-empNom…

OccupeSurface

0,1 0,nEmployé

Id-bureauSurface

Id-empNomNo_bureauSurface

Employé

OU

Si plusieurs employés par bureau Si un seul employé par bureau

Le choix dépend de l'importance du bureau Chapitre 3. Le Modèle Entités / Associations


Normalisation

♦ Un type d'entité qui ne comporte qu'un identifiant peut être traitécomme un attribut, à condition qu'il n'intervienne pasdans un nouveau type d'association et qu'il ne génère pasd'attribut à valeur multiple :

Il faut toujours vérifier si les associations de degré supérieurà deux ne peuvent pas être rapportées à plusieursassociations de degré inférieur

Un type d'association peut être éliminé s'il est la compositionde deux types d'associations qui, par des cardinalités(0,1) ou (1,1), expriment la même réalité

Lieu

AffectationStation ski Situation Hôtel1,11,n 1,n

Chambre1,1

1,n 1,1

INTERDIT



Modèle E/A ⇔ modèle relationnel

Voiture

Id-persNom

Possède

Date d'achat

Personne

ImmatriculationType

0,n 0,1

♦ Exemple voitures


Plusieurs possibilités, selon les cardinalités :

Personne < Id-pers., nom, date, Immatriculation, type >

Personne < Id-pers., nom >Voiture < Immatriculation, type >

Possède < Id-pers., Immatriculation, date >Personne < Id-pers., nom >Voiture < Immatriculation, type, Id-pers., date >

Contrainte d'intégrité référentielle pour le dernier cas : projId-pers. (Voiture) ⊂ projId-pers. (Personne) ∪ { NULL }




♦ Exemple étudiants

Solution 1 :


Enseignement

netNomAdresse

Note0,n 0,n

Etudiant

MatièreEnseignant

1,1

Agé de

Age

Année

0,n Tarif

Détermine1,1 0,n

Id-tarif




Solution 2 :


Enseignement

netNomAdresse

Note0,n 0,n

Etudiant

MatièreEnseignant

1,1

Paie AgeTarif

Prix0,n



♦ Exemple employés SERVICE <nS, nomS>EMPLOYE <nE, nomE, nS>PROJET <nP, nomP, coût>PARTICIPE <nE, nP, nomR>


Service

nENomE

Travaille0,1 0,n

Employé

nSnomS

0,n

Participe nPnomPCoût

Projet0,n

nomR


Généralisation / spécialisation

♦ Généralisation : regrouper les différents types d'entitéen faisant abstraction de leurs différences

♦ Spécialisation : mise en évidence des particularités des sous-types

Personne

idnom

Homme

service militaire

Femme

nom jeune fille




♦ Il peut y avoir N niveaux d'abstraction :

Véhicule

Terrestre

Diesel

Aérien

Essence

Atmosphérique Compressé Carburation Injection

Diesel Essence




♦ Sur N critères :Personne

Homme Femme Majeur Mineur

♦ Association à tous les niveaux :Employé

Programmeur Chef de projet Secrétaire

Travaille

Dirige Dispose Voiture

Berline Monospace



Modèle étendu : MERISE 2

♦ Intégration d'informations supplémentaires dans le modèle E/A :- partition (+) : disjonction + couverture (l'un ou l'autre)- totalité (T) : ¬ disjonction + couverture

(l'un ou l'autre ou les deux, mais pas autre chose) - exclusion (×) : disjonction + ¬ couverture

(l'un ou l'autre ou autre chose, mais pas les deux)

♦ Fait appel aux notions de :- couverture : ensemble recouvrant tous les éléments- Disjonction : pas d'éléments commun à deux sous-ensembles

Partition : +

Totalité : T

Exclusion : ×

Représentation graphique :




Personne

Homme

Femme

Voiture

Diesel Turbo

Partition : Totalité :

Diesel EssenceVoiture

(GPL)

Exclusion :




+

PersonneId-persNom

ChanteurVoix

MusicienInstrument

♦ Exemple




une relationpersonne <Id-pers, nom, voix, instrument >

partition : voix ≠ NULL XOR instrument ≠ NULLtotalité : voix ≠ NULL OR instrument ≠ NULLexclusion :voix = NULL OU instrument = NULL

autre possibilité :

personne < id-pers, nom, type, voix, instrument >

partition : type ∈ {C,M}type = C ET voix ≠ NULL ET instrument = NULLtype = M ET voix = NULL ET instrument ≠ NULL

totalité : type ∈ {C,M,D}exclusion : type ∈ {C,M,NULL}

type = C ET voix ≠ NULL ET instrument = NULLtype = M ET voix = NULL ET instrument ≠ NULL

Expression des contraintes d'intégrité référentielles :




trois relations

partition : C3 (C1 et C2 non nécessaires)totalité : C4 (C1 et C2 non nécessaires)exclusion :C1 ET C2 ET C3

autre possibilité :

partition : type ∈ {C,M}projid-pers (chanteur) ⊂ projid-pers (personne | type = C)projid-pers (musicien) ⊂ projid-pers (personne | type = M)

totalité : type ∈ {C,M,D}exclusion : type ∈ {C,M,NULL}

projid-pers (chanteur) $ ⊂ projid-pers (personne | type ∈ {C,D})projid-pers (musicien) ⊂ projid-pers (personne | type ∈ {M,D})

personne < id-pers, nom >chanteur < id-pers, voix >musicien < id-pers, instrument >

C1 : projid-pers (chanteur) ⊂ projid-pers (personne)C2 : projid-pers (musicien) ⊂ projid-pers (personne)C3 : projid-pers (musicien) ∩ projid-pers (chanteur) = ∅C4 : projid-pers (musicien) ∪ projid-pers (chanteur) = projid-pers (personne)

personne < id-pers, nom, type >chanteur < id-pers, voix >musicien < id-pers, instrument >



Chapitre 4

Utilisation d'un SGBD :mySQLApachePHPPHPmyAdmin


Serveur HTTP robuste très couramment utilisé

Apache

SGBD robuste pauvre en fonctionnalités

mySQL Générateur de pages webdynamiques ("remplace" javascript) très couramment utilisé simple (syntaxe proche

du C)

PHP

Interface PHP pour utiliseret administrer mySQL très intuitif puissant

PHPmyAdmin

gratuit robuste multi plate-formes

Chapitre 4. Exemple de SGBD

Présentation


EasyPHP

• Le plus simple à installer

• Distribution Windows, testée pour :– Win9x / Me– NT / 2K (à voir pour XP)

• Télécharger le fichier : easyphp1-6_setup.exe (10,3 Mo)• Installer avec toutes les options par défaut• Par défaut, tout est installé dans

c:\Program Files\EasyPHP\

• Démarrer, configurer (services)...

24/04/02 :EasyPHP 1.6 (version 1.7 en vue, beta dispo):- apache 1.3.24- php 4.2.0- mysql 3.23.49- phpmyadmin 2.2.6

http://www.easyphp.org


Protection de mySQL

• Impératif de protéger l'accès à mySQL• Protection des répertoires fournie par Apache (alias, httpd.conf)• Créer un fichier .htaccess avec un éditeur :

• Créer un fichier .passwd avec l'utilitaire Apache htpasswd (dans une fenêtre invite de commande) :

• Placer les fichiers .htaccess et .passwd dans c:\Program Files\EasyPHP\phpmyadmin, éditer httpd.conf

AuthName "Acces BD"AuthType BasicAuthUserFile "c:\Program Files\EasyPHP\phpmyadmin\.passwd"AuthGroupFile Nonerequire valid-user

c:\>cd Program Files\EasyPHP\apache\binc:\Program Files\EasyPHP\apache\bin>htpasswd –c .passwd brandelAutomatically using MD5 format on Windows.New password: ********Re-type new password: ********Adding password for user brandel


Utilisation de Apache / PHP

• Le point d'entrée du site WEB est dans : c:\Program Files\EasyPHP\www

• Ce répertoire contient un fichier index.php exécuté lorsqu'on tape http://localhost dans la zone URL d'un navigateur (IE par ex.)

• Créer un fichiersaluer.php :

• Placer ce fichier dans c:\Program Files\EasyPHP\www

• Exécution par l'URL http://localhost/saluer.php

<html><head><title> un titre un vrai ! </title></head><body><?phpif ($prénom != "") { print("<h2>Bonjour, $prénom !</h2>"); exit;}?>

<FORM ACTION="http://localhost/saluer.php",METHOD="POST">

Votre prénom ?<INPUT TYPE="text" NAME="prénom" VALUE=""><INPUT TYPE="submit" VALUE="Cliquez ici"></FORM></body></html>


Utilisation de mySQL

• (au moins) 3 méthodes :– PHPmyAdmin– mysql– PHP

• Accessible avec un navigateur :– soit directement à l'URL http://localhost/mysql/– soit interactivement depuis http://localhost/home/

• Possibilités :– créer des bases, des objets, des utilisateurs…– manipuler des données– exporter des requêtes– …

PHPmyAdmin



• Interpréteur SQL en ligne (genre sql*plus) dans une fenêtre invite de commande

• L'utilitaire mysql se trouve dans c:\Program Files\EasyPHP\mysql\bin

• Préciser éventuellement un nom d’ utilisateur :

• Pour accéder aux objets d'une base :

mysql

mysql> create database disco;mysql> connect discomysql> CREATE TABLE disques( -> titre varchar(30),artiste varchar(30),date date);mysql> INSERT INTO `disques` (`titre`, `artiste`, `date`) -> VALUES ('aux armes', 'gainsbourg', '12/12/2000');

mysql –u root



• Langage de programmation servant à générer des pages HTML dynamiques (interfacées avec mySQL) ou statiques

• Exemple 1 :

PHP

<html><head><title> Test de mySQL </title></head><body>

<?php$link = @mysql_pconnect("localhost","root","") or exit();mysql_select_db("disco") or exit();$result = mysql_query("SELECT count(*) FROM disques") or exit();if ($row = mysql_fetch_array($result)) echo "<p> il y a ".$row[0]." ligne(s) dans la table disques";mysql_free_result($result);?></body></html>



• Exemple 2 :

PHP

<html><head><title> Test de mySQL </title></head><body><?php$link = @mysql_pconnect("localhost","root","") or exit();mysql_select_db("disco") or exit();$result = mysql_query("SELECT * FROM disques",$link);if ($result) { echo "<p>Contenu de la table disques :<br>"; while ($row = mysql_fetch_object($result)) echo "titre: $row->titre, artiste: $row->artiste, date: $row->date<br>\n";}else { echo "<b>erreur dans l'exécution de la requête</b><br>"; echo "<b>message d'erreur de mysql :</b> ".mysql_error($link);}?></body></html>


•

•*

bases de données - cri.ensmp.frboucheba/bd-atc1.pdf · un sgbd est un logiciel permettant d’...

Documents