Conception, utilisation et gestion des bases de données relationnelles Phuong Le Hong [email protected] Fabienne Thomarat [email protected]

Conception, utilisation et gestion desbases de données relationnelles

Phuong Le Hong

[email protected]

Fabienne Thomarat

[email protected]

http://webloria.loria.fr/~thomarat/BD_FIMGP/

Introduction

Introduction

• Concept de systèmes de bases de données

• Architecture des SGBD

• Comment concevoir et utiliser une base de données ?

• Fonctions, langages et interfaces des SGBD

Introduction

• Concept de systèmes de bases de données BD : définition et intérêts SGBD et programmes d’application indépendance données – programmes




BD : définition et intérêtsConcept de systèmes de bases de données

NomPrénomSexeDate de naissanceAdresseProfession…

Nom sociétéAdresseCompte bancaire…

NomPrénomAdresseCompte bancaire…

BD : définition et intérêts

• définition d’une base de données : ensemble structuré de données possédant un lien entre elles.

Concept de systèmes de bases de données

• abréviations :

BD : Base de Données BDD : Base De Données

DB : Data Base

nécessite une modélisation du monde réel


• définition d’une base de données (informatisée) : ensemble structuré de données enregistrées sur des supports accessibles par l’ordinateur, représentant des informations du monde réel pouvant être interrogées et mises à jour par une communauté d’utilisateurs de façon sélective et en un temps opportun.



exemple : cabinet médical sans BD

NomPrénomPoidsMédecin référent…

NomPrénomDents traitées…

NomPrénomMédecinHonoraire…

Redondance

Risque d’incohérence

cardiologue dentiste





exemple : cabinet médical avec BD

BD

NomPrénomPoidsMédecin référentDents traitésMédecinHonoraire…



exemple : cabinet médical sans BD

NomPrénomPoidsMédecin référent…

NomPrénomDents traitées…

NomPrénomMédecinHonoraire…

cardiologue dentiste


Quels sont les patients ayant pour médecin

référent M. Dupont et qui portent un

appareil dentaire ?



exemple : cabinet médical avec BD

BD

NomPrénomPoidsMédecin référentDents traitésMédecinHonoraire…



Quels sont les patients ayant pour médecin

référent M. Dupont et qui portent un

appareil dentaire ?


partage des données par plusieurs utilisateurs avec des vues différentes

• intérêts de l’utilisation d’une BD :

interroger des données

stocker des données


organiser les données pour éviter la redondance et assurer la cohérence

SGBD et programmes d’application

= ensemble de programmes assurant les processus de :

définition de la BD

construction de la BD

manipulation de la BD partage de la BD

• SGBD (Système de Gestion de Bases de Données) :

• programme d’application (application) : programme informatique qui interagit avec la base de données en adressant une requête appropriée au SGBD


= logiciel qui assure la création et la gestion de bases de données.

SGBD et programmes d’application

Programme d’application BD

demande de prix

diminutiondu stock

envoi du prix

SGBD

exemple : achat au supermarché


Les systèmes de BD font partie de notre vie quotidienne !

système de BD = BD + SGBD + programmes d’application

commande

Indépendance données - programmes

Programme d’application BD

demande de prix

diminutiondu stock

envoi du prix

SGBD

exemple : achat au supermarché


indépendance données – programmes/traitements

commande

Introduction





Introduction





architecture dans la pratique et modèles des données

le niveau conceptuel : pourquoi modéliser ?

le niveau logique : les différents modèles logiques

le niveau externe : les vues

le niveau physique : la catalogue système ou dictionnaire de données

architecture théorique : indépendance des données

• architecture ANSI/SPARC (standardisée en 1975) : modèle théorique en trois couches (trois niveaux d’abstraction)

schémainterne

schéma conceptuel

schéma externe

schéma externe

schéma externe

BD

utilisateur 1 utilisateur 2 utilisateur n

Niveau externe

Niveau conceptuel

Niveau interne

…

Architecture théorique : indépendance des donnéesArchitecture des SGBD

Organisation physique des

données

• Trois niveaux :

niveau externe : la vue de la BD par les utilisateurs.

niveau conceptuel : la vue de l’ensemble de la BD.

niveau interne : la représentation physique de la BD.

façon dont les utilisateurs perçoivent les données

façon dont le SGBD perçoit les données

fournit la correspondance et l’indépendance entre le niveau externe et le niveau interne


• architecture ANSI/SPARC

schémainterne

schéma conceptuel

schéma externe

schéma externe

schéma externe

BD


Niveau externe

Niveau conceptuel

Niveau interne

…



données

vue des utilisateurs ou des programmes d’application

description du stockage des données

indépendance données - traitements

• indépendance de données :

indépendance logique des données : immunité des schémas externes par rapport aux modifications apportées au schéma conceptuel

indépendance physique des données : immunité du schéma conceptuel par rapport aux modifications apportées au schéma interne


• architecture ANSI/SPARC

schémainterne

schéma conceptuel

schéma externe

schéma externe

schéma externe

BD


Niveau externe

Niveau conceptuel

Niveau interne

…

indépendancelogique des données

indépendancephysique des données



données

• indépendance de données :

indépendance logique des données : immunité des schémas externes par rapport aux modifications apportées au schéma conceptuel

indépendance physique des données : immunité du schéma conceptuel par rapport aux modifications apportées au schéma interne


correspondance externe-conceptuelle

correspondance conceptuelle-interne

• conséquence de l’indépendance de données : diminution de l’efficacité

correspondance directe entre schéma interne et schémas externes dans beaucoup de SGBD !

• architecture ANSI/SPARC (standardisée en 1975) : modèle théorique en trois couches (trois niveaux d’abstraction)

schémainterne

schéma conceptuel

schéma externe

schéma externe

schéma externe

BD


Niveau externe

Niveau conceptuel

Niveau interne

…



données

modèles de données conceptuels (ou modèles de haut niveau)

• modèle de données : manière de décrire les données, les associations entre les données et les contraintes qui s’appliquent aux données.

modèles de données physiques (ou modèles de bas niveaux)

modèles de données logiques ou représentationnels (ou modèles d’implémentation)

Architecture dans la pratique et modèle de donnéesArchitecture des SGBD

• types de modèle de données :

3. implantation

vue vue

monderéel

schéma externe

schéma physique

schéma logiqueschéma conceptuel

1. modélisation

2.traduction5. définition des vues

BD

4. stockage

SBGD


vue vue

monderéel

schéma externe

schéma physique


1. modélisation

5. définition des vues

3. implantation

BD

4. stockage

SBGD

indépendant de toute implantation (donc du SGBD)


2.traduction

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

SBGD


2.traduction

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

SBGD

géré par le SGBD


2.traduction

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

SBGD


2.traduction

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

SBGD


2.traduction

3. implantation

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


BD

4. stockage

SBGD

le concepteur de BD

utili

sate

urs


vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage


vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

Architecture des SGBD

Le niveau conceptuel : pourquoi modéliser ?


• exemple : tableau de patients d’un cardiologue

Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956

Tristan Delerme 03.83.55.26.7512 avril 1987

Laurent Debrout 14-juillet-2007

Nom Date_naissance

Infarctus du myocarde

Hypertension artérielle

Épanchement péricardique

Diagnostic principal

Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent

Dupont Henri 31 décembre 1967

Dupont Henri 03.03.77.12.9431 décembre 1967

Christophe Ivolie 12/04/87

HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse

Carine Dupuis 1954/02/25

Martin Dominique 27-08-2004

Alexis Courrier 21 janvier 1990

Insuffisance cardiaque

Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir

Hélène Prigent 08/06/1975 Tachycardie Philippe Trisse

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.55.26.75

03.03.77.12.94


Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956



Nom Date_naissance





Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent




HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse





Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir


03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.55.26.75

03.03.77.12.94


Patients homonymes ou patient inséré plusieurs fois ?

quels sont les données qui permettent de caractériser de manière unique un patient ?

anomalie lors de l’insertion


Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956



Nom Date_naissance





Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent




HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse





Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir


03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.48.99.18

03.03.77.12.94


Médecins homonymes ou même médecin ?

comment éviter la redondance des données ?

anomalie lors de la modification


Quel est le numéro de téléphone correct ?

Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956



Nom Date_naissance





Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent




HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse





Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir


03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.55.26.75

03.03.77.12.94


Suppression d’un patient Perte des informations relatives à son médecin !

comment rendre indépendantes certaines données ?

anomalie lors de la suppression


Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956



Nom Date_naissance





Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent




HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse





Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir


03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.55.26.75

03.03.77.12.94


Quel est le prénom du patient « Martin Dominique » ?

comment choisir et structurer les données ?

problème pour rechercher et traiter les données


Tel_Medecin_Ref

Annie Dupreux

03.83.59.27.36

04/07/1956



Nom Date_naissance





Philippe Trisse

Grégoire Lenoir

Grégoire Lenoir

Medecin_Referent




HTA


Infarctus

Philippe Trisse

Pierre Boulois

Philippe Trisse





Anévrisme

Hypertension

Pierre Boulois

Odile Ténard

Grégoire Lenoir


03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.59.27.36

03.83.24.34.65

03.83.55.26.75

03.83.55.26.75

03.03.77.12.94


comment homogénéiser des données ?

problème pour rechercher et traiter les données

Combien de patients souffrent d’hypertension artérielle ?

Quel âge a un patient donné ?




Tel_Medecin_RefNom Date_naissance Diagnostic principal Medecin_Referent

Tel_Medecin_Ref

Nom

Date_naissance


Medecin_Referent




Numero_SS

Numero_Praticien

comment choisir des données ?





Tel_Medecin_Ref

Prénom

Date_naissance


Medecin_Referent




Numero_SS

Numero_Praticien


Nom





Tel_Medecin_Ref

Prénom

Date_naissance


Nom_Médecin




Numero_SS

Numero_Praticien


Nom

Prénom_Médecin


Patient Médecin_Référent



Tel_Medecin_Ref

Prénom

Date_naissance


Nom_Médecin

Numero_SS

Numero_Praticien

Nom

Prénom_Médecin

entité : représentation d’un objet

association : lien entre une ou plusieurs entités

modélisation :

attribut : caractéristique d’une entité ou d’une association

A pour médecin référent

attributs attributs

entités

association

modèle entité-association


vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage


Le niveau logique : les différents modèles logiques


modèle hiérarchique

• historique :

modèle hiérarchique : données représentées sous forme d’une arborescence descendante (arbre).


Le niveau logique : les différents modèles logiquesArchitecture des SGBD

Viton Delphine 03.83.56.36.81secrétaires

Dupreux Annie

Terrond Isabelle dentisteDelorme François cardiologue

Debrout Laurent

Dupont Henri

Dupont Henri

Martin Daniel

Courrier Alexis

médecins

patients


modèle hiérarchique

• historique :

modèle réseau : données représentées sous forme d’un graphe quelconque.

modèle réseau




Viton Delphine 03.83.56.36.81secrétaires

Dupreux Annie

Terrond Isabelle dentisteDelorme François cardiologue

Debrout LaurentDupont Henri

Martin Daniel

Courrier Alexis

médecins

patients

Visite 1 Visite 2 Visite 3

Les modèles de bases de données


• modèle de données (ou modèles de bases de données) : manière de structurer l’information dans une BD.

• historique :

1ère génération:1965-1970 modèle réseau : données représentées sous forme d’un

graphe quelconque.

modèle relationnel : données représentées sous forme de tables.

2ème génération:1970-1980



Viton Delphine 03.83.56.36.81

secrétaires

Dupreux Annie

Terrond Isabelle dentiste

Delorme François cardiologue

Debrout Laurent

Dupont Henri

Martin Daniel

Courrier Alexis

médecins

patients

Nom Prénom SpécialitéNom Prénom Num_tel

Nom Prénom

P001

P002

P003

P004

P005

Num_pat

M001

M002

Num_med

visites

P001

P004

P003

Num_pat

M002

05/09/2008

07/09/2008

Date

04/10/2008

M001

M002

Num_med

S001

Num_sec

S001

Num_sec

S001

Le modèles de bases de données


• modèle de données (ou modèles de bases de données) : manière de structurer l’information dans une BD.

• historique :

1ère génération:1965-1970 modèle réseau : données représentées sous forme d’un

graphe quelconque.

modèle relationnel : données représentées sous forme de tables.

2ème génération:1970-1980

modèle objet : données représentées sous forme d’objets3ème génération:

1980-2000 modèle relationnel-objet : hybridation entre relationnel et objet

Principal modèle utilisé aujourd’hui : le modèle relationnel


Le modèle relationnel

• introduit par E.F. Codd en 1970

• basé sur des concepts mathématiques (théorie des ensembles, logique du premier ordre)

• modélisation des données de la BD sous forme de tables ou relations (ensemble d’attributs) représentant un objet ou une association d’objets

Num_Médecin

DelormeDel01 François

TerrondTer01 Isabelle

Nom Prénom

34 square Lamartine

189 rue de la République

Adresse

Paris

Toulouse

Ville

attribut

enregistrementou n-uplet


• langage d’interrogation dédié aux BD relationnelles (SQL Structured Query Language) développé par Codd et normalisé

vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage


Le niveau externe : les vues

Le niveau externe : les vues

Date_nais.

Dupont 01/01/81Jean

Nom Prénom

1 rue Saint Jean

Adresse

Nancy

Ville

• exemple : employé d’une entreprise

54000

Code_pos. Sexe

M

Enfants

2

Indice

389

Poste

Ing.

Service

Dev.

Tel.

345

Dupont Jean

Nom Prénom Poste

Ing.

Service

Dev.

Tel.

345

Date_nais.

Dupont 01/01/81Jean

Nom Prénom

1 rue Saint Jean

Adresse

Nancy

Ville

54000

Code_pos. Enfants

2

Indice

389

Poste

Ing.

données disponibles

vue pour un autre employé

vue pour le service paye


vue vue

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage


Le niveau physique : le catalogue système ou dictionnaires des données

Le niveau physique : le catalogue système ou dictionnaires des données


le SGBD gère l’accès aux fichiers

• le SGBD possède un catalogue système ou dictionnaire des données (métadonnées) :

les noms, les types et les tailles des données

les contraintes d’intégrité sur les données

les schémas externes, logique et interne, ainsi que leurs correspondances

les informations sur l’emplacement de stockage des données et la façon d’y accéder

le SGBD gère les correspondances entre les schémas

le SGBD gère le schéma physique et le stockage de données

…

les noms des associations entre les données

Introduction





Introduction





Fonctions des SGBD

• fonction principale d’un SGBD :

Fonctions, langages et interfaces des SGBD

masquage des détails de l’implémentation physique interne aux yeux de l’utilisateur

accès efficace aux données

manipulation possible par des non-informaticiens

utilisation de langages non-procéduraux

stockage, obtention et mise à jour des données

• fonctions attendues des SGBD :

contrôle de la redondance des données intégrité des données

Fonctions des SGBDFonctions, langages et interfaces des SGBD

- contraintes sur les enregistrements

Dupreux Annie

Debrout Laurent

patients

Nom Prénom

exemple :

P001

P002

Num_pat

04/07/1956

12/04/1987

Date_nais

Menard Xavier P00315/10/2010



Dupreux Annie

Debrout Laurent

patients

Nom Prénom

exemple :

P001

P002

Num_pat

04/07/1956

12/04/1987

Date_nais

- contraintes sur les associations entre les enregistrements

M001

M002

Num_med

visites

P001

P002

Num_pat

05/09/2008

07/09/2008

Date

P001 M003 04/10/2008Terrond Isabelle dentiste


médecins

Nom Prénom Spécialité

M001

M002

Num_med




support de transaction


exemple : suppression d’une secrétaire et réaffectation des médecins


secrétaires



médecins


M001

M002

Num_med

S001

Num_sec

S001

Num_sec

S002

Lenoir Grégoire cardiologue

Trisse Philippe cardiologue M001

M002

S003

S003

Pinot Jean urologue

Darris Damien dentiste M001

M002

S002

S001

Foux Sophie 03.83.56.36.54 S002

Blanc Hélène 03.83.56.36.76 S003

S002







exemple : suppression d’une secrétaire et réaffectation des médecins


secrétaires



médecins


M001

M002

Num_med

S001

Num_sec

S001

Num_sec

S001

Lenoir Grégoire cardiologue

Trisse Philippe cardiologue M001

M002

S003

S003

Pinot Jean urologue

Darris Damien dentiste M001

M002

S002

S001

Foux Sophie 03.83.56.36.54 S002

Blanc Hélène 03.83.56.36.76 S003

S003










services de contrôle de concurrence

services d’autorisation

services de restauration

• fonctions réelles des SGBD : ces fonctions sont rarement toutes présentes dans un même SGBD

choix en fonction de la priorité donnée à certains critères :

efficacité d’accès

économie de disque

sécurité…

• langages et architecture trischématique des SGBD :

langage de définition de données (LDD)

Langages des SGBDFonctions, langages et interfaces des SGBD

schémainterne

schéma logique

schéma externe

schéma externe

schéma externe

BD


…

langage de stockagede données (LSD)

langage de définition de vues (LDV)

• langages des SGBD « classiques » :

langage de définition de données (LDD) : langage permettant de

Langages des SGBDFonctions, langages et interfaces des SGBD

- définir (ou modifier) le schéma conceptuel (logique), y compris les contraintes d’intégrité

- la création de vues catalogue système

langage de manipulation des données (LMD) : langage permettant de

- insérer de nouvelles données- modifier des données- extraire des données- supprimer des données

2 types de LMD :- LMD procéduraux- LMD non-procéduraux : SQL

• plusieurs types d’interfaces :

Interfaces des SGBDFonctions, langages et interfaces des SGBD

interfaces à menus

interfaces à formulaires

interfaces graphiques

interfaces en langage naturel

Introduction





Introduction





Conception de bases de données

Trois étapes :

• l’analyse du système du monde réel à modéliser :

analyse de l’organisation de l’entreprise

entretien avec le personnel

détermination de l’objectif du système de BD

identification : des objets à prendre en compte, de leurs caractéristiques, des liens qui existent entre eux des caractéristiques de ces liens des règles qui définissent le domaine de validité des caractéristiques

modèle entité-association


Trois étapes :


• la mise en forme du schéma pour l’intégrer dans un SGBD :

transformation du schéma conceptuel pour le rendre acceptable par un SGBD

modification éventuelle des objets et de leurs liens

vérification de la qualité de la base de données

modification éventuelle du schéma conceptuel

définition des vues du système de base de données

modèle relationnel


Trois étapes :


• la mise en forme du modèle pour l’intégrer dans un SGBD :

• la création effective dans le SGBD des structures et leur remplissage

création des relations (tables)

insertions des valeurs des caractéristiques (attributs)

création des vues

Plan, organisation et structure du cours

Plan du cours

Trois étapes :




Plan du cours

Trois étapes :

• le modèle entité-association



Plan du cours

Trois étapes :


• le modèle relationnel


Plan du cours

Trois étapes :



• le langage SQL

Plan du cours

Première partie du cours :



• le langage SQL

Première partie du cours : 5 séances

• utilisation du SGBD Access

Deuxième partie du cours : 5 séances

Objectif du cours

Première partie du cours :



• le langage SQL

Première partie du cours : 6 séances

• utilisation du SGBD Access

Deuxième partie du cours : 4 séances

savoir concevoir une base de données

maîtriser l’outil Access

vue vue

Architecture des SGBD : dans la pratique

monderéel

schéma externe

schéma physique


1. modélisation


3. implantation

BD

4. stockage

SBGD

le concepteur de BD

utili

sate

urs

Organisation du cours

• nombre de séances : 10

• en général : 1 séance = amphi (3/4 h) + TD (2h)

• mode d’évaluation :

- conception (modèle entité association + modèle relationnel)

- requêtes (langage SQL)

- utilisation d’un SGBD (Access)

mais aussi :

- travail au cours des TD

- petit test « surprise » en début de cours

tests en contrôle continu :

Documents

Conception, utilisation et gestion des bases de données relationnelles Phuong Le Hong [email protected] Fabienne Thomarat [email protected]