Maîtrise LEA – Année 2002/2003

Introduction aux Bases de Données

Maîtrise LEA Maîtrise LEA –– Année 2002/2003Année 2002/2003

Introduction aux Bases de DonnéesIntroduction aux Bases de Données

TRICHET Francky / ANDRE Pascal

[email protected]

[email protected]


PlanPlan

• Présentation générale

• Le modèle relationnel et son algèbre

• Les principes de normalisation


1. Présentation Générale1. Présentation Générale• Définition : Une Base de Données (BD) est un ensemble de

données mémorisées sur des supports accessibles par un ordinateur pour satisfaire simultanément plusieurs utilisateurs de façon sélective et en un temps très court.

• Outil d ’exploitation d’une BD : SGBD (Système de Gestion de Base de Données)

• gérer l ’insertion, la modification et la recherche efficace de données dans une grande masse d ’informations

• gérer l ’interface entre les utilisateurs et le support sur lequel est stockée la BD (chacun doit avoir l ’illusion qu’il est le seul à exploiter la BD)


Les 3 couches dLes 3 couches d ’un SGBD’un SGBD• Un SGBD est composé de 3 couches :

– Système de gestion de fichiers : gestion du stockage physique de l ’information (couche dépendante du matériel)

– SGBD interne : gestion logique des données (assemblage, placement, liens et accès)

– SGBD externe : gestion de l ’interface avec l ’utilisateur et le concepteur (langage de requêtes, outils de présentation)


Les points clés dLes points clés d ’une BD’une BD• Indépendance physique : un SGBD permet de

manipuler des données sans se soucier de la façon dont sont stockées physiquement ces données

• Indépendance logique : un SGBD permet de présenter sous différentes vues les mêmes données (groupes de travail hétérogènes)

• Manipulation par des non-informaticiens : un SGBD permet de décrire ce que l ’on souhaite sans décrire comment l ’obtenir (recherche déclarative)


Les points clés dLes points clés d ’une BD’une BD• Efficacité des accès aux données : un SGDB repose

sur des algorithmes (transparents pour l ’utilisateur) performants qui optimisent l ’accès aux données

• Centralisation des informations : un SGDB permet de centraliser les informations relatives à un domaine, ce qui :

– garantie la non-redondance– minimise la perte de place– facilite les mises à jour des données


Les points clés dLes points clés d ’une BD’une BD• Cohérence des données : un SGBD permet de la

vérification de contraintes d ’intégrité

– un salaire doit être compris entre 4000 et 10 000 000 F– l ’emprunteur d ’un livre doit être un abonné de la bibliothèque

• Partageabilité des données : un SGBD permet à plusieurs personnes d ’accéder simultanément aux données tout en offrant l ’illusion d ’être seul exploitant

• Sécurité des données : un SGBD permet de gérer différents niveaux d ’accessibilité aux données en fonction de profils d ’utilisateurs


Les 4 types de BDLes 4 types de BD• BD Hiérarchiques : les plus anciennes fondées sur une

modélisation arborescente des données

• BD Relationnelles : organisation des données sous forme de table et exploitation à l ’aide d ’un langage déclaratif– Exemples de SGBD Relationnels : Oracle, MySQL, 4D, Access, etc.

• BD Déductives : organisation des données sous forme de table et exploitation à l ’aide d’un langage logique

• BD Objets : organisation des données sous forme d ’instances de classes hiérarchisées (qui possèdent leur propres méthodes d ’exploitation)

– Exemple de SGBD Objets : O2


Démarche de conception dDémarche de conception d ’une BD’une BD

• Analyse du problème et modélisation de la BD

– représentation informationnelle de l ’organisation étudiée –phase supportée par des méthodes d ’analyse : AXIAL,

OMT ou MERISE (la plus connue qui sépare les données des traitements)

• Implantation logique de la BD

– transformation du modèle conceptuel des données en objets propres au type de SGBD utilisé

• Implantation physique de la BD : phase totalement prise en charge par le SGBD adopté


Modélisation des données en Merise (1)Modélisation des données en Merise (1)• MCD : Modèle Conceptuel des Données

– description statique du système d ’information visé à l ’aide d’entités, d’associations et de propriétés –Entité : un objet matériel ou immatériel ayant une existence

propre et conforme aux choix de gestion du système étudié : personne, voiture, client

–Association : un lien entre des entités (binaire, ternaire, n-aire)

–Propriété : une donnée élémentaire et indécomposable du système d ’information : couleur d ’une voiture, âge d ’une personne

• MCT : Modèle Conceptuel des Traitements (Hors Propos)


Modélisation des données en Merise (2)Modélisation des données en Merise (2)Représentation Graphique

Nom de l’Entité

Propriété 1

Propriété n

Nom de l’Association

Propriété

Nom de l’Entité

Propriété 1

Propriété n

Cardinalités(Mini,Maxi)

Cardinalités(Mini,Maxi)

Personne

Numéro INSEENom

Travailler

Service

Nom1,1 1,n

Prénom

Type d’entitéType d’association

Type de valeur


Exemple (1) Exemple (1) • Association unaire

0,n

0,n

ProduitNo_proNom_proLibellé_pro

nomenclatureqté

composant

composé


Exemple (2) Exemple (2) • Association binaire

1,1Etudiant

no_enom_eprénom_e

0,n

Adresseno_ruenom_ruecode_postalville

adressedate_ad


Exemple (3) Exemple (3) • Association ternaire

1,nEtudiant

no_enom_eprénom_e

0,n


0,n

adresse

Datejourmoisan


Exemple (4)Exemple (4)• Association quaternaire

0,n

0,n

1,n

0,n

Professeurnomfonctiongrade

Classeno_classenb_élèvesniveau

Salleno_sallenom_sallecapacité_salle

Datejourmoisan

coursheure


Modélisation des données en Merise (3)Modélisation des données en Merise (3)

• L ’identifiant d ’une entité : il est constitué d ’une ou plusieurs propriétés de l ’entité telles qu ’à chaque valeur de l ’identifiant corresponde une et une seule occurrence de l ’entité (numéro d ’INSEE d ’une personne)

–L ’identifiant d ’une association est l ’union des identifiants des entités qui participent à l ’association



• Les contraintes restreignent l’espace des solutions

– Appartenance à un type– Format et valeurs– Cardinalités– Contraintes d’intégrité fonctionnelles– Autres


Modélisation des données en Merise (4a)Modélisation des données en Merise (4a)

• Les cardinalités définissent des contraintes d ’intégrités sur l ’association

– borne minimale : nombre minimum de fois qu’une occurrence d ’une entité participe aux occurrences de l ’association (O, 1, n)

– borne maximale : nombre maximum de fois qu’une occurrence d ’une entité participe aux occurrences de l ’association (1, n)


Modélisation des données en Merise (4b)Modélisation des données en Merise (4b)

• Cardinalités

1,1Etudiant

no_enom_eprénom_e

0,n


adressedate_ad


Modélisation des données en Merise (4c)Modélisation des données en Merise (4c)

• Contrainte d’intégrité fonctionnelle

RG : à une date donnée, un étudiant habite à une seule adresse

1,nEtudiant

no_enom_eprénom_e

0,n


0,n

habite

Datejourmoisan

CIF


Modélisation des données en Merise (4d)Modélisation des données en Merise (4d)

Σ (NS) > Σ (AS) * coef Ensembliste

nouv_sal > ancien_salSimpleAspects

dynamiques

Σ salaires < nbEnsembliste

salaire > SMIGSimpleAspects

statiques

ExempleTypeContraintes sur




Décrire un SIDécrire un SI• Modéliser le réel perçu

• SI = SCD + CI + règles

• Démarche en 4 tâches

exposé démarche

• Exercice


EnoncéEnoncéLe CROUS gère des logements pour les étudiants. Deux

types de logements sont gérés par des services différents : les logements en cité universitaire et les logements en ville. Nous nous intéressons ici au second. Le service des logements en ville dispose de renseignements concernant des propriétaires de villas,appartements, garages, ...

• nom, prénom et adresse des propriétaires ;• nature et adresse du bien possédé par un propriétaire.a) Modéliser cette réalité à l'aide d'un modèle E-A-P ;


EnoncéEnoncéb) Nous voulons maintenant pouvoir représenter le fait

qu'un propriétaire peut vouloir louer ses biens. Nous ne souhaitons toutefois pas d'auto-location (de location dans lesquelles propriétaire et locataire sont une seule et même personne). La location se fait à partir d'une date donnée, pour une période donnée et moyennant un loyer mensuel précis. Modéliser cette réalité à l'aide d'un modèle E-A-P ;

c) Le CROUS envisage de percevoir sur les loyers un pourcentage, variable selon la nature du bien. Cela pourrait servir à alimenter un fond social d'aide aux étudiants en difficulté. Modifier le modèle précédent pour permettre la mémorisation de ce pourcentage.


PlanPlan





Le modèle relationnel (1)Le modèle relationnel (1)• Organisation des données en Table :

– des lignes (enregistrements ou n-uplets)– des colonnes (champs ou attributs)

• Concepts fondamentaux :

– Domaine : ensemble de valeurs distinctes que peut prendre une donnée élémentaire (D1={rouge,vert}, D2={3,2})

– Produit Cartésien d ’un ensemble de domaine D1, D2, Dn est l ’ensemble des n-uplets {<v1,v2,vn>} tels que vi ∈Di

– D1 * D2 = {<rouge,3>,<rouge,2>,<vert,3>,<vert,2>}


Le modèle relationnel (2)Le modèle relationnel (2)• Concepts fondamentaux :

– Relation (table relationnelle, table) : sous-ensemble du produit cartésien d ’une liste de domaines ; elle caractérisée par:–un nom–des champs (attributs) qui correspondent aux noms de

chacun des domaines mis en jeu (les colonnes)–des n-uplets (tuples) qui correspondent aux différentes

combinaisons valides du produit cartésien (les lignes)

Nom Prénom

TRICHET Francky

GILBERT Jacques

ANDRE Pascal

Personne



– Clé d’une relation : un ou plusieurs attributs permettant d ’identifier, de façon unique, chaque n-uplets de la relation

– Remarque : il peut éventuellement exister plusieurs clés pour une même relation ; dans ce cas, il faut (1) identifier les clés candidates puis (2) choisir une clé primaire parmi les clés candidates

– Exemple :

– ETUDIANT(matricule, nom, prénom, adresse, num-insee)

Clé primaire Clé candidate



– Schéma d ’une relation : ensemble des attributs (colonne) de la relation (table)

– Schéma d ’une BD : union des schémas des différentes relations

– Remarque : une relation peut être définie

– en intension (uniquement pour les BD déductives) : on identifie une formule qui va permettre d ’identifier automatiquement les n-uplets

• {(x,y,z) ∈ (N,Z,Q) tels que x+y>z}

– en extension : on spécifie un à un tous les n-uplets de la relation


Le modèle relationnel (5)Le modèle relationnel (5)Exemple de BD relationnelle

produits(pno,design,prix,poids,couleur)fournisseurs(fno,nom,adresse,ville)commandes(cno,fno,pno,qute)

et son schéma :


Du MCD Merise aux tables relationnelles Du MCD Merise aux tables relationnelles (1)(1)

• Traitement des Entités

– chaque entité devient une table– chaque propriété d ’une entité devient une colonne– l ’identifiant de l ’entité devient la clé primaire

• Traitement des Associations

– Cas 1 : association avec au moins une cardinalité maximale à 1–création de 2 tables (une par entité) avec migration de la clé

primaire de l ’entité de cardinalité (-,n) dans la table de l ’entité de cardinalité (-,1)

–si il existe des propriétés dans l ’association, elles migrent comme précédemment



Exemple de cas 1

Schéma Entités/Associations

Tables relationnelles correspondantes



• Traitement des Associations

– Cas 2 : association avec les deux cardinalités maximales à n

–création de 3 tables (une par entité + une pour l ’association)

avec migration des clés primaires des 2 entités dans la table

de l ’association

–si il existe des propriétés dans l ’association, elles migrent

dans la table de l ’association



Exemple de cas 2

Schéma Entités/Associations

Tables relationnelles correspondantes



Application sur l’exemple Produits-Fournisseurs-Commandes

Tables relationnelles + intégrité référentielleProduits(pno,design,prix,poids,couleur)

Commandes(fno,pno,qute)

Fournisseurs(fno,nom,adresse,ville)


LL ’algèbre relationnelle (1)’algèbre relationnelle (1)• L ’algèbre relationnelle

– a été introduite par Codd en 1970 dans le but de décrire des traitements à appliquer sur les différentes relations d ’une BD relationnelle

– repose sur deux types d ’opérations

–les opérations ensembliste (union, différence, intersection, produit cartésien)

– les opérations relationnelles (projection, sélection, jointure)


LL ’algèbre relationnelle (2)’algèbre relationnelle (2)Les opérations ensemblistes

• L ’union de deux relations R et S de même schéma est une relation T de même schéma contenant l ’ensemble des tuples appartenant à R et/ou à S

• La différence de deux relations R et S de même schéma (dans l ’ordre R-S) est une relation T de même schéma contenant les tuples appartenant à R et n ’appartenant pas à S


LL ’algèbre relationnelle (3)’algèbre relationnelle (3)

• L ’intersection de deux relations R et S de même schéma est une relation T de même schéma contenant les tuples appartenant à la fois à R et à S

• Le produit cartésien de deux relations R et S de schéma quelconque est une relation T ayant pour attributs la concaténation des attibuts de R et de S et dont les tuples sont constitués de toutes les concaténations d ’un tuple de R à un tuple de S

Les opérations ensemblistes


LL ’algèbre relationnelle (4)’algèbre relationnelle (4)Les opérations relationnelles

• La projection d ’une relation de schéma (A1,A2,…,An) sur les attributs Ai1,Ai2,…Aip (avec ij<>ik et p>n) est une relation R’ de schéma (Ai1,Ai2,…,Aip) dont les tuples sont obtenus par élimination des attributs de R n’appartenant pas à R’ et par suppression desdoublons

• La sélection de la relation R par une qualification Q est une relation R’ de même schéma dont les tuples sont ceux de R satisfaisant Q ; Q pouvant être exprimée à l ’aide de constantes, d ’opérateurs arithmétiques (>, <, etc.) ou logiques (et, ou, non)



• La jointure de deux relations R et S selon une qualification Q est l ’ensemble des tuples du produit cartésien R*S satisfaisant la qualification Q ; Q pouvant être exprimée à l ’aide de constantes, d ’opérateurs arithmétiques (>, <, etc.) ou logiques (et, ou, non)



• L ’équi-jointure est une jointure avec pour qualification l ’égalité entre deux colonnes

• La jointure naturelle est une équi-jointure de R et S sur tous les attributs de même nom suivie de la projection qui permet de conserver un seul de ces attributs égaux de même nom


LL ’algèbre relationnelle (7)’algèbre relationnelle (7)• Autre opérateur (de calcul)

– Compte est une opération qui permet de dénombrer les lignes d ’une relation qui ont une même valeur d ’attributs en commun. La relation résultante ne contient que les attributs de regroupement Xi choisis avec leur nombre d ’occurrences.


LL ’algèbre relationnelle (8)’algèbre relationnelle (8)• Exemples d’expressions de l’algèbre relationnelle

– Quels sont les numéros de fournisseurs des différents ‘ Dupont ’–projfno(selectnom=‘ Dupont ’(Fournisseurs))

– Quels sont les numéros de fournisseurs qui ont moins de trois commandes–projfno(selectcompte<3(comptefno(Commandes)))

Produits(pno,design,prix,poids,couleur)Fournisseurs(fno,nom,adresse,ville)Commandes(fno,pno,qute)


PlanPlan





La normalisationLa normalisation• L ’objectif de la normalisation est de définir un

schéma de relations permettant d ’éviter :

– la redondance de données– les incohérences lors des mises à jour– les anomalies lors d ’insertion et/ou suppression

• La normalisation repose sur l ’analyse des dépendances entre attributs– moyen : décomposer les relations sujettes à redondance tout en

conservant la possibilité de reconstituer les relations originelles par application de l’opération de jointure


La normalisation (2)La normalisation (2)

• Une entreprise de vente de bateaux qui souhaite constituer un système d ’information relatif à son activité

– Achats(IdClient, NomClient, PrénomClient, AdresseClient, ImmatriculationBateau, ModèleBateau, LongueurBateau, DateAchat, MontantAchat)

• Problèmes potentiels

– redondance de données : une personne qui achète plusieurs bateau

– incohérence suite à une MAJ : une personne qui change d ’adresse

– anomalies lors d ’insertion/suppression : un client potentiel ne peut être enregistré dans la BD s ’il n ’a pas encore acheté de bateaux ; lorsqu’un client vend son bateau, il est supprimé du système d ’information

Exemple


La normalisation (3)La normalisation (3)• Comment normaliser ?

– Décomposition des relations jusqu’à ce que toutes respectent les 3 Formes Normales suivantes

–Une relation est dite en Première Forme Normale si tous ses attributs sont atomiques ; aucun des attributs ne doit être une relation entre « sous-attributs » (pas de colonnes dans une colonne)


La normalisation (4)La normalisation (4)• Une relation est dite en Seconde Forme Normale lorsqu’elle est en

Première Forme Normale et tous les attributs n ’appartenant pas à la clé sont en dépendance fonctionnelle élémentaire avec la clé

– dès qu’un attribut non-clé dépend d’une partie de la clé, la relation n ’est pas en 2FN.

• Deux groupes d ’attributs X et Y de la relation R sont en Dépendance Fonctionnelle (on dit aussi X détermine Y) si dans R,chaque valeur de X détermine une et une seule valeur de Y

– une DF est une assertion sur toutes les extensions possibles d ’une relation et pas uniquement sur ses tuples actuels


La normalisation (5)La normalisation (5)Exemple d ’application de la 2FN


La normalisation (6)La normalisation (6)• Une relation est dite en Troisième Forme Normale lorsqu’elle est

en Seconde Forme Normale et qu’aucun de ses attributs non-clés dépend d ’un autre attribut non-clé ; il n ’existe pas de Dépendance Fonctionnelle entre 2 attributs non-clés


PlanPlan




• Manipulation d’un SGBD relationnel : Microsoft ACCESS ’97

– Documentation en ligne :http://www.er.uqam.ca/merlin/xw999998/access.htm


Les contraintes d’intégrité (1)Les contraintes d’intégrité (1)

• Contraintes de clé

– un SGBD gère et vérifie la définition de clé primaire pour chaque relation

• Contraintes de types de données

– à chaque attribut peut être associé des contraintes de types (entier, date, etc.) et de domaine (intervalle [1..100], date postérieure au 01/01/95, etc.)


Les contraintes d’intégrité (2)Les contraintes d’intégrité (2)


Les contraintes d’intégrité (3)Les contraintes d’intégrité (3)• Contraintes d’intégrité référentielle (CIR)

– Gestion de données référencées dans plusieurs tables : une CIR peut s’appliquer dès qu’une clé primaire d ’une table R est utilisée comme référence dans une autre table T


Les contraintes d’intégrité (4)Les contraintes d’intégrité (4)• Les CIR permettent au SGBD de vérifier

automatiquement la cohérence de la BD par rapport :

– Anomalie de suppression–lorsqu’on supprime un client (Table Client), il faut

supprimer toutes ses commandes (Table Commande)– Anomalie de modification

–lorsqu’on modifie le code d ’un produit (Table Produit), il faut répercuter ces modifications dans les commandes (Table Commande)

– Anomalie d ’ajout– lorsqu’on ajoute une commande, il faut s ’assurer que le

code client et le code produit existent

Documents

Maîtrise LEA – Année 2002/2003