G10 - La base de données relationnelle

DOSSIER G10 –
La base de données
Relationnelle
1
DOSSIER G10
Problématique
2
Problématique
• La société Locazur, située à Nice,
est spécialisée dans la location de véhicules
de prestiges.
• Pour la gestion des locations de ses véhicules,
Locazur utilise un SGBDR (Système de
Gestion de Base de Données Relationnelles).
• Toutefois elle rencontre certaines difficultés
dans l'utilisation de cette base de données.
3
DOSSIER G10
Activité 1 – Organiser
une base de données relationnelles
4
La base de données relationnelle
Modèle Relationnel des Données
Immatriculation
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation)
Clé primaire :
Clé étrangère :
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
La Table
"VEHICULES"
n'a pas de clé
primaire.
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
01. Quel champ de la table
"VEHICULE" peut
convenir pour être
la clé primaire ?
• L'attribut Immatriculation peut convenir comme clé
primaire, car il est unique (deux véhicules ne peuvent pas
avoir la même immatriculation).
5
La base de données relationnelle
Un extrait des tables vous est proposé ci-dessous :
Un tuple (ou enregistrement)
correspond à l'ensemble des
valeurs d'une ligne de la table.
02. Donnez des exemples
de tuple de la table
"VEHICULES".
• Le véhicule :
6
La base de données relationnelle
Un extrait des tables vous est proposé ci-dessous :
10 Tuples
(10 lignes d'enregistrement)
Un tuple (ou enregistrement)
correspond à l'ensemble des
valeurs d'une ligne de la table.
03. Combien y a-t-il de tuple
dans cet extrait de table ?
• Il y a dans cet extrait de table :
7
La base de données relationnelle
Modèle Relationnel des Données
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation)
Clé primaire :
Clé étrangère :
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
04. Pourquoi le champ
"Adresse" de la table
"CLIENT" n'est pas
bien structuré ?
• Car l'adresse doit être décomposée en 3 attributs
(champs) :
• la rue, la ville et le code postal.
8
La base de données relationnelle Un contrat
ne concerne
qu'un seul véhicule
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation)
Immatriculation
Clé primaire :
Immatriculation
et que ce dernier
Clé étrangère :
ne peut appartenir
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
qu'à une seul
Clé primaire :
CodeCategorie
catégorie.
Clé étrangère :
Modèle Relationnel des Données
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
05. Quels champs est-il
nécessaire d'ajouter
afin de réaliser les liens
entres les tables ?
Entre CONTRAT et VEHICULES :
Dans la table CONTRAT il faut ajouter
le champ Immatriculation
(Un contrat dépend fonctionnellement d'un véhicule).
9
La base de données relationnelle Un contrat
Modèle Relationnel des Données
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Clé étrangère :
ne concerne
qu'un seul véhicule
et que ce dernier
ne peut appartenir
qu'à une seul
catégorie.
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
05. Quels champs est-il
nécessaire d'ajouter
afin de réaliser les liens
entres les tables ?
Entre VEHICULES et CATEGORIE :
Dans la table VEHICULES il faut ajouter
le champ CodeCatégorie
(Un véhicule dépend fonctionnellement d'une catégorie).
10
La base de données relationnelle
Modèle Relationnel des Données
En essayant de lier les tables
"CONTRAT" et "CLIENT"
à l'aide du champ commun
"CodeClient" le message suivant
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
est apparu à l'écran.
Clé étrangère :
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie en référence à CodeCatégorie de CATEGORIE
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
06. Justifiez ce message
d'erreur et proposez une
modification
dans la table "CONTRAT"
afin de permettre le lien
entre cette table
et la table "CLIENT".
Le champ CodeClient est en format Texte dans la table
CLIENT et en format Numérique dans la table CONTRAT.
Pour être liés, les champs doivent avoir le même format.
11
DOSSIER G10
Activité 2 – Les règles d'intégrité
d'une base de données
12
Les contraintes d’intégrité
Une fois le modèle relationnel établi, la base de données traduisant
l’ensemble des relations est implantée dans un SGBDR.
Le modèle relationnel permet de déterminer les champs de chaque
table.
Les tables sont alors complétées par l’ensemble des valeurs de
chaque enregistrement.
Afin d’éviter certaines erreurs lors de la saisie des
enregistrements, les SGBDR contrôlent la cohérence de l’ensemble
des valeurs prises en vérifiant les trois contraintes d’intégrité
suivantes :
- la contrainte d’intégrité de domaine ;
- la contrainte d’intégrité de table (ou de relation) ;
- la contrainte d’intégrité référentielle.
13
La base de données relationnelle
La structure de la table est la suivante Vous
:
décidez de créer une nouvelle
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
catégorie en saisissant dans la table
Clé primaire :
Immatriculation
"CATEGORIE"
les éléments suivants :
Clé étrangère :
CodeCatégorie en référence à CodeCatégorie
de CATEGORIE
CodeCategorie : 4
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
NomCategorie : Old Cars
Clé étrangère :
Prixjourlocation : 990 euros
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Lorsque vous saisissez le prix par jour
Clé primaire :
NumeroContrat
(990 euros) le message suivant
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation
de VEHICULES
apparaît.
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
07. Justifiez ce message
d'erreur. Quelle valeur doit
être saisie dans l'attribut
"Prixjourlocation" ?
Le format du champ Prixjourlocation est numérique,
il ne peut accepter une saisie de format texte comme :
990 euros.
14
La contrainte d’intégrité de domaine
Si la valeur saisie n’est pas compatible avec le type de données
du champ (numérique, texte, monétaire, date, logique),
l’intégrité de domaine n’est pas respectée.
Un message d’erreur apparaît.
Exemple :
C'est le cas ici, dans la table CATEGORIE, le champ
Prixjourlocation et de type monétaire.
Si la valeur saisie par mégarde est de type texte
(le nom de la catégorie par exemple), la contrainte d’intégrité
de domaine n’est pas respectée.
Retour vers les liens
15
La base de données relationnelle
Laclient
structure
de la
tabledeest
la suivante
: véhicule (immatriculation :
Le
Maupart
décide
louer
un deuxième
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
986AZS06)
leImmatriculation
17/09/2008 pour une durée de 3 jours. Vous décidez d'ajouter
Clé primaire :
ce
au contrat
n°à162
en saisissant
de la façon suivante dans
Clénouveau
étrangère : véhicule
CodeCatégorie
en référence
CodeCatégorie
de CATEGORIE
la table "CONTRAT" :
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
08. Justifiez ce message
d'erreur.
La clé primaire de CONTRAT est le numéro du contrat,
on ne peut donc pas avoir deux tuples (enregistrements)
de la table CONTRAT avec le même numéro de contrat.
16
La contrainte d’intégrité de table
(ou de relation)
Si la valeur de la clé primaire n’est pas saisie ou s’il s’agit
d’une valeur déjà existante, la contrainte d’intégrité de table
(ou de relation) n’est pas respectée.
Un message d’erreur apparaît.
Exemple :
Dans cet exemple, lors de la saisie d’un enregistrement dans
la table CONTRAT, le numéro de contrat 162 (clé primaire),
existe déjà, l’enregistrement ne pourra être validé.
Retour vers les liens
17
La base de données relationnelle
La structure de la table est la suivante :
Vous devez saisir un nouveau contrat en
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur,
Immatriculation,
CodeCatégorie)
fonction
des éléments
suivants :
Clé primaire :
Immatriculation
Code contrat : 164
Clé étrangère :
CodeCatégorie en référence à CodeCatégorie de CATEGORIE
Date du contrat : 18/09/2009
Nombre de jours de location : 3
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Code client : 63 (il s'agit d'un nouveau client)
Clé étrangère :
Immatriculation : 158AZS06
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
Lors de la saisie dans la table "CONTRAT",
le message suivant apparaît à l'écran :
09. Justifiez ce message
d'erreur. Quelle saisie
préalable faut-il réaliser
pour enregistrer le contrat
du nouveau client ?
Dans la table "CONTRAT", l'attribut CodeClient est clé
étrangère. Il permet de faire le lien entre les tables
"CLIENT" et "CONTRAT".
La valeur "63" n'apparaît pas dans la table "CLIENT", par18
conséquent elle ne peut être acceptée dans la table "CONTRAT".
La contrainte d’intégrité référentielle
Si la valeur saisie pour un champ clé étrangère d’une table
ne fait pas partie des valeurs du même champ clé primaire
d’une autre table, la contrainte d’intégrité de référence
n’est pas respectée.
Un message d’erreur apparaît.
Les contraintes d’intégrité de domaine et de table s’appliquent
automatiquement.
La contrainte d’intégrité référentielle doit être définie lors
de la création des liens entre les tables.
Exemple :
Dans la table CONTRAT, lors de l’enregistrement d’un nouveau
contrat, numéro 164, la valeur saisie pour le code client est
« 63 ».
Or ce code client n’existe pas dans la table CLIENT
où le champ CodeClient est défini comme clé primaire.
La contrainte d’intégrité référentielle n’est pas respectée.
19
Retour vers les
liens
DOSSIER G10
Activité 3 – Exploiter la base de données
(les Opérateurs Relationnels)
20
Les opérateurs relationnels
Après modification le modèle relationnel se présente ainsi :
Modèle Relationnel des Données
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie en référence à CodeCatégorie de CATEGORIE
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
Clé primaire :
CodeCategorie
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
LocAzur désirerait réaliser des traitements sur sa base de données
afin d'extraire certaines informations.
- obtenir le nom de tous les clients ;
- les immatriculations des véhicules qui ont plus
de 10 000 km au compteur ;
- les immatriculations des véhicules de la catégorie
"Prestige".
21
Les opérateurs relationnels
Modèle Relationnel des Données
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie en référence
à CodeCatégorie
de CATEGORIE
Les
opérateurs relationnels
sont :
Projection
-La projection : sélectionner certaines colonnes d'une table ;
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
-La restriction : sélectionner certaines valeurs d'une table ;
Clé primaire :
CodeCategorie
-La jointure : lier deux tables par un champ commun.
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
CLIENT
Nom
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
LocAzur désirerait réaliser ce traitement sur sa base de données
afin d'extraire certaines informations.
Traitement : obtenir le nom de tous les clients
Champ(s) à afficher
Table(s) à utiliser
Opérateur(s) relationnel(s) à utiliser
22
Les opérateurs relationnels
Modèle Relationnel des Données
VEHICULES
Immatriculation
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie en référence
à CodeCatégorie
de CATEGORIE
Les
opérateurs relationnels
sont :
Projection
Restriction
-La projection : sélectionner certaines colonnes d'une table ;
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
-La restriction : sélectionner certaines valeurs d'une table ;
Clé primaire :
CodeCategorie
-La jointure : lier deux tables par un champ commun.
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
LocAzur désirerait réaliser ce traitement sur sa base de données
afin d'extraire certaines informations.
Traitement : les immatriculations des véhicules qui ont plus de 10 000 km
Champ(s) à afficher
Table(s) à utiliser
Opérateur(s) relationnel(s) à utiliser
23
Les opérateurs relationnels
Modèle Relationnel des Données
VEHICULES
Immatriculation
Table 1 : VEHICULES (Modele, Marque, DateAcquisition, KmCompteur, Immatriculation, CodeCatégorie)
Clé primaire :
Immatriculation
Clé étrangère :
CodeCatégorie en référence
à CodeCatégorie
de CATEGORIE
Les
opérateurs relationnels
sont :
CATEGORIES
Projection
Restriction
Jointure
-La projection : sélectionner certaines colonnes d'une table ;
Table 2 : CATEGORIE (CodeCategorie, NomCategorie, Prixjourlocation)
-La restriction : sélectionner certaines valeurs d'une table ;
Clé primaire :
CodeCategorie
-La jointure : lier deux tables par un champ commun.
Clé étrangère :
Table 3 : CONTRAT (NumeroContrat, DateContrat, Nbjourslocation, CodeClient, Immatriculation)
Clé primaire :
NumeroContrat
Clé étrangère :
CodeClient en référence à CodeClient de CLIENT
Immatriculation en référence à Immatriculation de VEHICULES
Table 4 : CLIENT (CodeClient, Nom, Adresse, Telephone)
Clé primaire :
CodeClient
Clé étrangère :
LocAzur désirerait réaliser ce traitement sur sa base de données
afin d'extraire certaines informations.
Traitement : les immatriculations des véhicules de la catégorie "Prestige"
Champ(s) à afficher
Table(s) à utiliser
Opérateur(s) relationnel(s) à utiliser
24
DOSSIER G10
Schéma conceptuel
25
Schéma conceptuel
Table
Table
Table
PROJECTION
Clés primaires et étrangères
RESTRICTION
JOINTURE
une(des) clé(s) étrangère(s)
Intégrité de relation
Intégrité de domaine
26
Première STG
Gestion
Diaporama adapté et automatisé
par M. Grard (enseignant)
Sources :
•Éditions Hachette Livre
Retour vers les liens
27