Les bases de données cours avancé
Base de données
Chapitre 1
Introduction Plan du cours
n Introduction n Modèle relationnel n SQL n Conception
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
n Petite base de données avec 1 seule table (appelons la professeur)
– Conserver les données
– Supporter des opérations sur les données
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
n Sélection
SELECT NOM, FONCTION
NOM | FONCTION |
Gilles | Prof_adj |
Myriam | Prof_agr |
Claudine | Prof_adj |
FROM PROFESSEUR
WHERE AN_ENT > 1992
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
n Insertion
INSERT INTO
PROFESSEUR (MAT, NOM, FONCTION, COURS, AN_ENT)
VALUES (66231, ‘Jian’, ‘Prof_adj’, ‘MRT2323’, 1996)
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
66231 | Jian | Prof_adj | MRT2323 | 1996 |
n Insertion
INSERT INTO
PROFESSEUR (MAT, NOM, FONCTION, COURS, AN_ENT)
VALUES (66231, ‘Jian’, ‘Prof_adj’, ‘MRT2323’, 1996)
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
66231 | Jian | Prof_adj | MRT2323 | 1996 |
n Mise à jour
UPDATE PROFESSEUR
SET COURS = ‘MRT2325’
WHERE MAT = 66231
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
66231 | Jian | Prof_adj | MRT2325 | 1996 |
n Mise à jour
UPDATE PROFESSEUR
SET COURS = ‘MRT2325’
WHERE MAT = 66231
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
66231 | Jian | Prof_adj | MRT2325 | 1996 |
n Destruction
DELETE FROM PROFESSEUR
WHERE MAT = 66231
Exemple
MAT | NOM | FONCTION | COURS | AN_ENT |
62945 | Gilles | Prof_adj | MRT1111 | 1997 |
34560 | Myriam | Prof_agr | MRT2221 | 1993 |
21539 | Claudine | Prof_adj | MRT3331 | 1999 |
80200 | Bernard | Prof_tit | MRT1112 | 1982 |
75902 | Yida | Prof_agr | MRT1664 | 1990 |
n Destruction
DELETE FROM PROFESSEUR
WHERE MAT = 66231
Système de gestion de bases de données
n Un système informatique de conservation de
l’information nécessite :
– Données
– Matériel
– Logiciel
– Utilisateurs
Base de données
n Définitions
– Données persistantes
• Durée de vie dépasse celle de l’exécution d’un programme
– Base de données
• Collection de données persistantes utilisées par des systèmes informatiques n Exemples
– Organisations
• banques, hôpitaux, université, compagnies, etc.
– Systèmes
• comptabilité, gestion du personnel, gestion de stock, etc.
n Information
– Entités
– Relations
– Propriétés
• Employé
– Mat
– Nom
– ...
n Acteurs
– Concepteur
– Administrateur de données
– Administrateur de la base de données
– Utilisateur final
n Une même personne peut occuper plusieurs rôles n Un même rôle peut être occupé par plusieurs personnes
n Avantages
– Réduit les redondances
• Un fichier du personnel pour la paye/comptabilité
• Un fichier du personnel pour les ressources humaines – Évite l’incohérence
• Ajout d’un nouvel employé
– Permet le partage des données
• Entre applications existantes et futures
– Permet l’application des normes
• ODMG, CORBA, etc.
n Avantages
– Garantie la sécurité
• Utilisateurs (politique)
• Opérations (consultation, destruction, insertion) – Assure l’intégrité
• Données exactes
– Permet d’établir des priorités
• Optimisation des accès pour certaines applications au détriment des autres.
– Assure l’indépendance des données
• Applications, représentation interne, etc.
Historique
n Génération 0 (années 60)
– fichiers reliés par des pointeurs
• IDS.I (Honeywell) et IMS.I (IBM)
• pour les programmes de la conquête spatiale (APOLLO) n 1ère génération (fin des années 60)
– modèles hiérarchique et réseaux (travaux de CODASYL) • TOTAL, IDMS, IDS.2 et IMS.2, etc.
n 2éme génération (depuis 1970)
– modèle relationnel
• SGBD commercialisés à partir de 1980 • ORACLE, SYSBASE, DB2, etc.
n 3ème génération (début des années 80)
– extension du relationnel (Oracle 10i, DB2 Universal Database, etc.)
– à objets (ObjectStore, GemStone, O2, etc.) n 4ème génération … Internet, les informations non structurées, le multimédia, la découverte des données, XML
Base de données
Chapitre 2
Le modèle relationnel Types de modèles
n Modèles conceptuels
– Entités-Relations
– UML
– …
n Modèles de bases de données
– Réseau
– Hiérarchique
– Relationnel
– À objets
– …
Plan du cours
n Introduction n Modèle relationnel
n SQL n Conception
Concepts de base
n Attribut
– Colonne d ’une relation caractérisée par un nom
n Relation
– Degré
• nombre d ’attributs
– Cardinalité
• nombre de n-uplets
Concepts de base
Concepts de base
n Relation vs. Table
– Relation : définition d ’un objet abstrait (voir plus haut)
– Table : représentation concrète de cet objet abstrait
– Souvent confondues
n Introduction
– A tout instant la base représente une partie du monde réel à travers une
configuration particulière des valeurs des données
– Certaines configurations des valeurs n ’ont pas de sens
• exemple age d ’une personne = -35
– Intégration de règles d ’intégrité pour informer le SGBD de certaines contraintes du monde réel
n Exemple de contraintes d ’intégrité
• Matricule doit être sous la forme nnnnn
• La valeur de GRADE doit appartenir à l ’ensemble {Prof_adj, Prof_agr, Prof_tit}
• L ’année de recrutement doit être supérieure à 1920 (ou pourquoi pas 1878)
• L ’année de recrutement doit être inférieure ou égale à l ’année courante
• Tous les professeurs recrutés avant 1994 doivent être au moins agrégés
n Contraintes spécifiques à une base de donnés particulière
n Propriétés d ’intégrité générale
– clés candidates et clés primaires
– clés étrangères n Clés candidates
– Ensemble minimal d ’attributs dont la connaissance des valeurs permet d ’identifier un n-uplet unique de la relation considérée
– Chaque relation contient au moins une clé candidate
n Clés candidates
– Utilité
• mécanisme d ’adressage au niveau des nuplets d ’une table
• Exemple
Professeur WHERE MAT = 34560, produit un seul n-uplet en résultat
Professeur WHERE AN_ENT = 1994, va produire un nombre imprévisible de n-uplets en résultat
n Clé primaire
– Une clé choisie parmi l ’ensemble des clés candidates
– Le choix est arbitraire
– Peux se faire selon des heuristiques
• Plus simple par exemple
– Exemple
• Clés candidates MAT et NOM - COURS
• Clé primaires MAT
n Clé primaire
– Exemple
Etudiant (Code_per, Nom, Prenom,
Adresse, Date_nais, Lieu_nais, NASS) Quelles sont les clés candidates ?
Quelle clé primaire choisir ?
n Clés étrangères
– Soir R2 une relation, un sous-ensemble d ’attributs FK dans R2 est dit clé étrangère dans le cas où
* il existe une relation R1 avec une clé candidate CK (R1 peut être R2)
* à tout instant, chaque valeur de FK dans
R2 est identique à une valeur de CK dans
R1
n Clés étrangères
– Propriétés
• Il n ’est pas nécessaire d ’avoir pour toute valeur de CK dans R1 une valeur identique de FK dans R2
• Une clé étrangère est composée ou simple
• Chaque attribut d ’une clé étrangère doit être défini sur le même domaine que son équivalent dans la clé candidate correspondante
n Clés étrangères
– Propriétés
• Un attribut d ’une clé étrangère peut faire partie d ’une clé candidate de sa relation
Employe (Mat, Nom, …)
Projet (Num, Designation, …) Role (Mat, Num, role)
• On dit que R2 référence
n Clés étrangères
– Diagramme référentiel
• représentation graphique des références
Employe Projet
Role
Dept
n Exemple
Employe (mat# : entier, nom : caracteres, adresse : caracteres, dept : entier, sup : entier)
Projet (num# : entier, designation : caracteres)
Departement (dept# : entier, dir : entier, nom :
caracteres)
Role (mat# : entier, num# : entier, role_emp : caracteres)
n Clés étrangères
– Intégrité référentielle
• La base de données ne doit pas contenir une valeurs de clé étrangère non « unifiable »
• Cette contrainte est dite référentielle
• Conséquence : Il faut créer et détruire les nuplets dans un ordre particulier
• Dans certain SGBD (version 92 de SQL), on peux différer la vérification d ’une contrainte d ’intégrité référentiel (restreinte vs. cascade)
Aperçu de déclaration de table en SQL
n Domaine de base
– Entier : INTEGER
– Décimal : DECIMAL(m,n)
– Réel flottant : FLOAT
– Chaîne de caractères : CHAR(n) – Date : DATE – ….
n Création d ’une table (forme simple)
CREATE TABLE ( , , , …)
Aperçu de définition de données en SQL
n Création d ’une table (forme simple)
– Exemple
CREATE TABLE VIN ( NV INTEGER,
CRU CHAR(10),
MIL INTEGER) n Contraintes d ’intégrité
• NOT NULL
• UNIQUE ou PRIMARY KEY
• FOREIGN KEY
• REFERENCES
• CHECK
Aperçu de définition de données en SQL
n Avec contraintes d ’intégrité
Exemple (SQL 86)
CREATE TABLE COMMANDE (
NC INTEGER UNIQUE NOT NULL,
NV INTEGER NOT NULL QUANTITE DECIMAL(6))
Exemple (SQL 89)
CREATE TABLE COMMANDE (
NC INTEGER PRIMARY KEY,
NV INTEGER NOT NULL REFERENCES VIN,
QUANTITE DECIMAL(6) CHECK(QUANTITE > 0))
---------------------------------------------------------------------------------------------------------------
NC INTEGER, PRIMARY KEY (NC),
NV INTEGER NOT NULL,SQL 92
FOREIGN KEY (NV) REFERENCES VIN,
en SQL
n Modification du schéma d ’une table
ALTER TABLE