Introduction à Access Algèbre relationnelle

Introduction à Access
Algèbre relationnelle
Hervé HOCQUARD
Université de BORDEAUX
LaBRI
Plan
Introduction à Access
Historique
Utilisation
Création d’une base de données avec Access
Création des tables
Création des relations
Algèbre relationnelle
2
Qu’est-ce qu’Access ?
Système de Gestion de Bases de Données
Relationnelles (SGBDR)
Fait partie de la famille de logiciels Microsoft
Office
Word : Traitement de texte
Excel : Tableur électronique
PowerPoint : Éditeur de présentations
Outlook : Agenda-Courriel
Access : Bases de données personnelles
3
Historique d’Access
1992 : Access 1.0
1993 : Access 2.0
Tournait sur Windows 3.0
Nécessitait 4 Mo de RAM et 8 Mo de disque dur !
1995 : Access 7 pour Windows95 (Office 95-Word 7)
1997 : Access 97 (Office 97)
1999 : Access 2000 (Office 2000)
2001 : Access 2001 (Office XP)
2003 : Access 2003 (2003 Microsoft Office System)
2007 : Microsoft Office Access 2007 (2007 Microsoft
Office System)
Nécessite 256 Mo de RAM et 1.5 Go de disque dur !
4
Compétiteurs d’Access
dBase – Années 1980-1990
5
Compétiteurs d’Access
Paradox
1987-…
Développé originalement par Borland
Fait maintenant partie de Corel Word Perfect Office
FileMaker Pro
1985-…
OpenOffice.org Base
2001-…
Fait partie de la suite bureautique OpenOffice.org
Logiciel Gratuit (GPL - General Public License)
6
Utilisation d’Access
Démarrage
Création d’une BD
Fichier vide
Modèle
Les modes d’ouverture d’une BD
Normal (mode par défaut): Consultation et Modification
de la BD, mono/multi utilisateur
Lecture seule : Consultation seulement
Exclusif : mono-utilisateur seulement, personne d’autre
ne peut ouvrir la BD en même temps
Lecture seule exclusif
7
Utilisation d’Access
La Barre de menus
Accueil
Créer
Données externes
Outils de bases de données
Conseil d’utilisation
Faites régulièrement des copies de sauvegarde !
8
Principaux éléments d’Access
Tables
Stockage des données
Relations
Liens entre les tables
Requêtes
Opérations sur les tables afin d’extraire de l’information
Formulaires
Interfaces-utilisateur d’entrée, de modification, d’affichage et
de traitement des données
États
Affichage de récapitulatifs (factures, commandes, rapports,
etc.), attrayants et imprimables
Macros
Liste de commandes pouvant être enregistrées et exécutées
par Access
9
Centre Sportif Peter inc. – Modèle
relationnel formel en FNBC
CLIENT (NoClient, Nom, Prenom, Statut)
REDUC_CLIENT (Statut, Reduction)
SALLE (NoSalle, Description)
RESERVATION (NoClient, NoSalle, DateDebut, DateFin)
COUT_RESERV (DateDebut, Cout)
AUTOR_SALLE (NoSalle, DateDebut, Autorisation)
FORFAIT (NoForfait, DateDebut, DateFin, Prix)
CARTE (NoClient, NoForfait)
ACTIVITÉ (NoActivité, Description, NoSalle)
ACTIVITÉ FORFAITAIRE (NoForfait, NoActivite)
ACT_EN_COURS (NoSalle, NoActivité)
GROUPE (NoGroupe, Description, DateDebut, DateFin, NoActivite)
EMPLOYE (NoEmploye, Nom, Prenom)
AFFECTATION (NoGroupe, NoEmploye)
COMPETENCE (NoEmploye, NoActivite, Fonction)
10
Création d’une BD avec Access
Création des tables
Création des relations
11
Création des tables
Champ
Unité de données la plus petite dans la BD
≈ Attribut ≈ Colonne ≈ Élément de données
Enregistrement
Ensemble des valeurs attribués aux champs d’une table
pour une occurrence donnée
≈ Ligne ≈ t-uplet ≈ Occurrence (du MCD)
Clé primaire
Champ (ou groupe de champs) qui identifie de façon
unique chaque enregistrement d’une table
≈ Identifiant (du MCD)
12
Création des tables
2 façons
Par entrée de données
En mode création (recommandé)
Entrée des données
En mode feuille de données
Assistant Liste de choix
13
Création des relations
Relation
Connexion entre 2 tables apparentées où 2 champs
partagent les mêmes données
Passe par les clés étrangères
Outil graphique de création de relation
Intégrité référentielle
Protège les données
Une clé étrangère doit toujours correspondre à une clé
primaire
Empêche de supprimer/modifier une clé primaire si des
clés étrangères y sont associées dans d’autres tables
Évite les enregistrements « orphelins »
14
Algèbre relationnelle
Chaque opération d’algèbre relationnelle prend
une/plusieurs tables et les transforme afin de
produire une nouvelle table
3 opérations principales
Sélection
Projection
Jointure
3 opérations secondaires
Union
Intersection
Différence
15
Tables des exemples - ÉTUDIANT
ÉTUDIANT
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Tremblay
Isabelle
1977
07
12
ULaval
Godin
Patrice
1972
02
18
UDM
Bouchard
Martin
1977
06
28
UDM
Girard
Julie
1980
10
22
UQAM
16
Tables des exemples - UNIVERSITÉS
UNIVERSITÉS
Sigle
NomInstitution
Ville
NbrÉtudiants
UQAC
Université du Québec à
Chicoutimi
Chicoutimi
4000
UQAM
Université du Québec à
Montréal
Montréal
12000
UDM
Université de Montréal
Montréal
11000
ULaval
Université Laval
Québec
8000
17
Tables des exemples - VILLES
VILLES
Nom
Région
Chicoutimi
Saguenay-Lac-StJean
Montréal
Montréal
Québec
Québec
18
Sélection ou Restriction
S’effectue sur une seule table
Produit une nouvelle table étant un sous-
ensemble de la table originale selon des
critères appliqués sur certains champs
Opérateur : σ
Format
σ {critères}(table source)
19
Sélection - Exemples
σ {Nom = "Girard "}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Girard
Julie
1980
10
22
UQAM
La liste des étudiants dont le nom de famille est "Girard"
σ {Université = "ULaval"}(ÉTUDIANT)
Nom
Prénom
AnnéeNais MoisNais
JourNais
Université
Tremblay
Isabelle
1977
12
ULaval
07
La liste des étudiants inscrits à l’Université Laval
20
Sélection - Exemples
Exemple de sélection donnant une table vide
σ {AnnéeNais = 1979}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
La liste des étudiants nés en 1979
21
Sélection – Exemples avec 2 critères
(OU et ET)
σ {AnnéeNais = 1977 ET Université = "UDM"}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Bouchard
Martin
1977
06
28
UDM
La liste des étudiants nés en 1977 et inscrits à l’Université Laval
σ {Université = "UQAC" OU Université = "UDM"}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Bouchard
Martin
1977
06
28
UDM
La liste des étudiants inscrits à l’UQAC ou à l’UDM
22
Sélection – Exemples avec 3 critères
(Les parenthèses sont importantes !)
σ {(Université = "UDM" OU Université = "ULaval" ) ET AnnéeNais = 1977}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Tremblay
Isabelle
1977
07
12
ULaval
Bouchard
Martin
1977
06
28
UDM
La liste des étudiants inscrits à l’UDM ou à ULaval et qui sont nés en 1977
σ {Université = "UDM" OU (Université = "ULaval" ET AnnéeNais = 1977)}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Tremblay
Isabelle
1977
07
12
ULaval
Godin
Patrice
1972
02
18
UDM
Bouchard
Martin
1977
06
28
UDM
La liste des étudiants inscrits à l’UDM ou ceux qui sont en même temps
inscrits à ULaval et nés en 1977
23
Sélection – Exemples basés sur un
critère d’inégalité et sur un critère partiel
σ {AnnéeNais < 1976}(ÉTUDIANT)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Godin
Patrice
1972
02
18
UDM
La liste des étudiants nés avant 1976
σ {Nom = "G* "}(ÉTUDIANT)
Nom
Prénom
AnnéeNais MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Godin
Patrice
1972
02
18
UDM
Girard
Julie
1980
10
22
UQAM
La liste des étudiants dont le nom de famille commence par G
24
Projection
S’effectue sur une seule table
Produit une nouvelle table qui est un sous-
ensemble de la table originale selon un ou
des champs sélectionnés
Sert à déterminer quelles données seront
présentées comme résultat de la requête
Opérateur : π
Format
π {champ} (table source)
25
Projection simple
π {Nom} (ÉTUDIANT)
Nom
Girard
Tremblay
Godin
Bouchard
La liste des noms de famille des étudiants
Note : les éléments identiques sont éliminés
Une table ne doit jamais contenir deux t-uplets identiques
26
Projection sur plusieurs champs
π {Nom, Prénom, Université} (ÉTUDIANT)
Nom
Prénom
Université
Girard
Martin
UQAC
Tremblay
Isabelle
ULaval
Godin
Patrice
UDM
Bouchard
Martin
UDM
Girard
Julie
UQAM
La liste des étudiants avec l’Université où ils sont inscrits
27
Jointure
Jointure naturelle
Opération s’effectuant sur 2 tables ayant au moins un
champ commun (de même domaine)
Produit une nouvelle table qui est une combinaison des
2 tables originales selon l’égalité des champs communs
Permet de créer de l’information qui ne se trouve pas
dans une seule table, mais par la combinaison de deux
tables
Opérateur : ⊗
Format
⊗ {table1.champ1 = table2.champ2}(table1,table2)
28
Jointure = Exemple
⊗ {ÉTUDIANT.Université=UNIVERSITÉS.Sigle}(ÉTUDIANT,UNIVERSITÉS)
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
NomInstitu
tion
Ville
NbrÉtudi
ants
Girard
Martin
1975
04
23
UQAC
Université
du Québec
à
Chicoutimi
Chicoutimi
4000
Tremblay
Isabelle
1977
07
12
ULaval
Université
Laval
Québec
8000
Godin
Patrice
1972
02
18
UDM
Université
de
Montréal
Montréal
11000
Bouchard
Martin
1977
06
28
UDM
Université
de
Montréal
Montréal
11000
Girard
Julie
1980
10
22
UQAM
Université
du Québec
à Montréal
Montréal
12000
Note : le nom du champ peut être le même ou différent, en autant que les domaines soient identiques
29
Notes sur les jointures
Si aucun champ n’est commun (de même
domaine/type) entre 2 tables, la jointure est
impossible
Si un champ est commun mais qu’il n’y a aucune
donnée commune, alors la jointure donne une table
vide
Jointure universelle
Opération s’effectuant sur 2 tables et qui fait la jointure
entre tous les t-uplets de 2 tables
Aucun attribut commun n’est requis
Opération aussi appelée « Produit cartésien »
Produit une suite de faux t-uplets, donc n’est pas utile, mais
permet de détecter des erreurs dans la BD (vu plus tard)
30
Combinaison de sélections
Revient au même que l’ajout d’un critère ET
σ {Université = "UDM" ET AnnéeNais < 1975}(ÉTUDIANT)
Ou
σ {AnnéeNais < 1975}(σ {Université = "UDM"}(ÉTUDIANT))
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Godin
Patrice
1972
02
18
UDM
31
Combinaison de jointures
Chaque jointure produit une table, on peut donc
en combiner plusieurs l’une après l’autre
R1=⊗{ÉTUDIANT.Université=UNIVERSITÉS.Sigle}(ÉTUDIANT,UNIVERSITÉS)
R=⊗{R1.Ville,VILLES.Nom}(R1,VILLES)
Nom
Prénom
Année
Nais
MoisNais
JourNais
Université
NomInstitution
Ville
NbrÉtudiants
Région
Girard
Martin
1975
04
23
UQAC
Université du
Québec à
Chicoutimi
Chicoutimi
4000
Saguenay
-Lac-StJean
Tremblay
Isabelle
1977
07
12
ULaval
Université Laval
Québec
8000
Québec
Godin
Patrice
1972
02
18
UDM
Université de
Montréal
Montréal
11000
Montréal
Bouchard
Martin
1977
06
28
UDM
Université de
Montréal
Montréal
11000
Montréal
Girard
Julie
1980
10
22
UQAM
Université du
Québec à Montréal
Montréal
12000
Montréal
32
Combinaison de sélection et projection
Permet d’afficher seulement les données qui
nous intéressent
La projection se fait généralement en dernier
π{Nom, Prénom} (σ {AnnéeNais = 1977})(ÉTUDIANT))
Nom
Prénom
Tremblay
Isabelle
Bouchard
Martin
La liste des noms des étudiants nés en 1977
33
Combinaison de jointure et projection
R1=⊗{ÉTUDIANT.Université=UNIVERSITÉS.Sigle}(ÉTUDIANT,UNIVERSITÉS)
R2= ⊗{R1.Ville,VILLES.Nom}(R1,VILLES)
R=π{Nom,Prénom,Région}(R2)
Nom
Prénom
Région
Girard
Martin
Saguenay-Lac-St-Jean
Tremblay
Isabelle
Québec
Godin
Patrice
Montréal
Bouchard
Martin
Montréal
Girard
Julie
Montréal
34
Combinaison de sélection et jointure
Dans certains cas, une sélection suivie d’une jointure peut être
équivalente à une jointure suivie d’une sélection
σ {NbrÉtudiants > 10 000}(⊗{ÉTUDIANT.Université=
UNIVERSITÉS.Sigle}(ÉTUDIANT,UNIVERSITÉS))
Ou
⊗{Université=Sigle}(ÉTUDIANT,σ {NbrÉtudiants >10 000}(UNIVERSITÉS))
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
NomInstitution
Ville
NbrÉtudiants
Godin
Patrice
1972
02
18
UDM
Université de
Montréal
Montréal
11000
Bouchard
Martin
1977
06
28
UDM
Université de
Montréal
Montréal
11000
Girard
Julie
1980
10
22
UQAM
Université du
Québec à Montréal
Montréal
12000
La 2e option est toutefois plus efficace parce que la jointure ne se
fait que sur la table résultante de la sélection sur la table
UNIVERSITÉS, donc de taille réduite, plutôt que sur la table au
complet
35
Combinaison de sélection, projection et
jointure
Permet d’effectuer des requêtes complexes
répondant à des besoins divers en information
π{Nom,Prénom,NomInstitution}(σ{NbrÉtudiants > 10 000}(⊗{ÉTUDIANT.Université=
UNIVERSITÉS.Sigle}(ÉTUDIANT,UNIVERSITÉS)))
Nom
Prénom
NomInstitution
Godin
Patrice
Université de
Montréal
Bouchard
Martin
Université de
Montréal
Girard
Julie
Université du
Québec à Montréal
36
Les opérations ensemblistes
Ne peuvent s’effectuer que sur des tables
ayant une structure identique
3 opérations
Union
Intersection
Différence
37
Tables des exemples
ÉTUDIANT
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Tremblay
Isabelle
1977
07
12
ULaval
Godin
Patrice
1972
02
18
UDM
Bouchard
Martin
1977
06
28
UDM
Girard
Julie
1980
10
22
UQAM
ÉTUDIANTCYCLE1
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Tremblay
Isabelle
1977
07
12
ULaval
Bouchard
Martin
1977
06
28
UDM
Nom
Prénom
AnnéeNais
MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Girard
Julie
1980
10
22
UQAM
FINISSANT
38
Union
Résultat : l’ensemble des t-uplets contenus
dans les deux tables
Opérateur : ∪
Format
Table 1 ∪ Table 2
39
Union
ÉTUDIANTCYCLE1 ∪ FINISSANT
Nom
Prénom
AnnéeNais MoisNais
JourNais
Université
Girard
Martin
1975
04
23
UQAC
Tremblay
Isabelle
1977
07
12
ULaval
Bouchard
Martin
1977
06
28
UDM
Girard
Julie
1980
10
22
UQAM
40
Intersection
Résultat : l’ensemble des t-uplets communs
aux deux tables
Opérateur : ∩
Format
Table 1 ∩ Table 2
41
Intersection
ÉTUDIANTCYCLE1 ∩ FINISSANT
Nom
Prénom
AnnéeNais MoisNais
JourNais
Université
Girard
Martin
1975
23
UQAC
04
42
Différence
Résultat : l’ensemble des t-uplets de la
première table qui ne sont pas présents dans
la deuxième table
Opérateur : \
Format
Table 1 \ Table 2
43
Différence
ÉTUDIANT \ ÉTUDIANTCYCLE1
Nom
Prénom
AnnéeNais
MoisNais
JourNais Université
Godin
Patrice
1972
02
18
UDM
Girard
Julie
1980
10
22
UQAM
44
Note sur l’algèbre relationnelle
Habituellement, la planification des requêtes
se fait sans connaître les données
emmagasinées à l’intérieur des tables
On utilise donc un modèle en mode formel
Dans la plupart des SGBD, l’utilisation de
l’algèbre relationnelle se fait par l’entremise
du langage SQL
45
Access : les requêtes
Access simplifie grandement les requêtes à
une base de données
Requêtes avec l’assistant
Requêtes en mode création
46
Liens vers des tutoriels Access en ligne
http://www.lecompagnon.info/access/index.html
http://cerig.efpg.inpg.fr/tutoriel/bases-de-donnees/sommaire.htm
http://www.ybet.be/access/formation_access.htm
http://perso.orange.fr/jeanmarc.stoeffler/access/IndexAccess.htm
http://enseignement.insset.u-picardie.fr/deug/ti/cours/access/
http://www.webprofesseur.com/access/da1.htm
http://mhubiche.developpez.com/Access/cours/bases/
http://mhubiche.developpez.com/Access/tutoJointures/
47
Des questions ?
48