COCEPTION DES BASES DE DONNEES RELATIONNELLES

Les BD constituent le cœur du système d'information. La conception de ces bases est la tâche la plus ardue du processus de développement du système d'information.

Les méthodes de conception, telle Merise, préconisent une démarche en étapes et font appel à des modèles pour représenter les objets qui composent les systèmes d'information, les relations existantes entre ces objets ainsi que les règles sous-jacentes.

La modélisation se réalise en trois étapes principales qui correspondent aux trois niveaux d'abstraction différents.

Le premier niveau, ou schéma conceptuel, représente le contenu de la base en termes conceptuels, indépendamment de toute considération informatique.

Le second niveau, appelé niveau logique, résulte de la traduction du schéma conceptuel en un schèma propre à un type de BD.

Le troisième niveau, ou niveau physique, est utilisé pour décrire les méthodes d'organisation et d'accès aux données de la base. Nous allons étudier ici les SGBD relationnelles.

I. Modélisation conceptuel (premier niveau du processus d'abstraction)

La modélisation est une étape fondamentale de la conception de la BD dans la mesure où, d'une part, on y détermine le contenu de la BD et, d'autre part, on y définit la nature des relations entre les concepts principaux.

Les éléments de base du modèle ER (Entité-Relation, modèle de base).

Les concepts :

Entité : définit comme un objet pouvant être identifie distinctement.
Ex : un client, un livre, un compte bancaire, une commande, etc.

Il existe deux catégories d'entités :

Entités faibles : son existence dépend de l'existence d'une autre entité.
Ex : une entité LIGNE DE COMMANDE n'existe que si l'entité COMMANDE correspondante est présente.

L'entité faible aura un identifiant composé de l'identifiant de l'entité dont elle dépend et d'un autre attribut. Graphiquement on peut encadrer les entités faibles d'un double rectangle.

Entités régulières : son existence ne dépend pas de l'existence d'une autre entité.

Attribut : caractéristiques ou propriétés des objets.
Ex : le client a un code client, une raison sociale, une adresse, un montant de découvert, etc.

Identifiant : parmi tous les attributs de l'entité, l'identifiant est un attribut ou un ensemble
d'attributs permettant de déterminer une et une seule entité à l'intérieur de l'ensemble.
Ex : un code client identifie un seul client de l'entreprise.

Les relations : représentent les liens existantes entre les entités.
Ex : les clients sont reliés aux commandes.
Contrairement aux entités, les relations n'ont pas de relations propres. Les relations sont caractérisées, comme les entités, par un nom et éventuellement des attributs.

La dimension ou degré de la relation est le nombre d'entités impliquées dans une relation.

Les relations peuvent être de :

" Dimension I (ou réflexive) : elle ne concerne qu'une entité type dont elle relie deux éléments.
Ex : La relation Mariage relie deux éléments de l'entité PERSONNE

" Dimension II : c'est la plus fréquente.
Ex : la relation entre la commande et le client est une relation binaire ou de dimension 2.

" Dimension III : une relation est ternaire dans la mesure où elle ne peut être décomposée en deux ou trois relations binaires équivalentes.
Ex : une location de voiture représente une relation entre un véhicule, une personne et une date.

De manière générale, une relation peut être caractérisée par n dimensions.

Cardinalité : la description complète d'une relation nécessite la définition précise de la participation des entités. La cardinalité est le nombre de participation d'une entité à une relation.

Ex : la relation binaire Stock entre deux ensembles d'entités PRODUIT et DEPOT.

Cardinalité I-I : si et seulement si un produit ne peut être stocké que dans un seul dépôt et un dépôt ne contient qu'un seul type de produit.

Cardinalité I-N : un produit peut être stocké dans plusieurs dépôts mais où chaque dépôt ne contient qu'un type de produit, ou inversement.

Cardinalité M-N : un type de produit peut être stocké en plusieurs dépôts et un dépôt donné peut contenir plusieurs types de produits.

Pour les relations ternaires, les cardinalités possibles sont : I-I-1, I-I-N, I-M-N, et M-N-P.

Les cardinalités ci-dessus traduisent les contraintes propres aux entités et relations. Elles sont appelées cardinalités maximales dans la mesure où elles représentent le nombre maximum de participations d'une entité à une relation.

NB : Deux différentes notions de cardinalité :

- l'approche anglo-saxonne met l'accent sur le nombre de correspondant d'une entité au sein d'une relation ;

- l'approche française (Merise) définit la cardinalité comme le nombre de participations de l'entité à la relation.

Pour les relations binaires, cette différence de vision n'a pas de conséquence sur le modèle conceptuel obtenu, elle modifie simplement la convention de représentation de ces cardinalités. En revanche, pour les relations ternaires ou plus, il existe une réelle différence entre les approches.

Ex : une relation ternaire au sens de Merise peut, dans certains cas, se noter I-M-N dans la convention anglo-saxonne. L'approche anglo-saxonne a l'avantage de permettre la déduction automatique de la clé de la table relationnelle qui traduira la relation. Nous adoptons l'approche français.

Les éléments de base du modèle EER (modèle enrichi).

Cardinalité minimale : nombre minimal de participations d'une entité à une relation. La cardinalité minimale peut être 0 ou 1.

Ex : dans la relation Conduire entre des entités PERSONNE et VEHICULE, une personne peut ne pas conduire de véhicule, dans ce cas la cardinalité minimale est 0.

Généralisation : la généralisation d'entités permet de décrire un ensemble d'entités à différents niveaux d'abstraction.

Ex : un même ensemble de personnes travaillant dans une entreprise peut être divisé selon la qualification, le sexe, etc. Cette précision permet p.e. d'enrichir le schéma conceptuel en représentant, pour chaque relation, l'ensemble des entités impliquées dans la relation.

Représentation graphique d'un schéma ER

IMAGE

Une situation à modéliser peut avoir plusieurs schémas différents. Chaque modèle peut présenter des avantages et des inconvénients. Pour mesurer la qualité d'une modélisation ER ou EER il existent plusieurs critères à utiliser de manière combinée :

L'expressivité : traduit la richesse sémantique du schéma. Peut être caractérisée p.e. par le nombre de concepts et/ou contraintes exprimés dans le tableau ;

La minimalité : tend à privilégier les schémas avec un nombre de redondances minimales ;
La lisibilité : consiste à évaluer la représentation graphique proprement dite ;
La simplicité : privilégie les schémas contenant un nombre de concepts minimum. On peut la mesurer p.e. on calculant le nombre d'entités et d'associations présentes sur un schéma.

Démarche pour construire un schéma conceptuel

La construction d'un schéma conceptuel peut se réaliser de la manière suivante :

• 1. Déterminer la liste des entités.
  
  2. Pour chaque entité :
  a) établir la liste de ses attributs ;
  b) parmi ceux-ci, déterminer un identifiant.
  
  3. Déterminer les relations entre les entités.
  
  4. Pour chaque relation :
  a) dresser la liste des attributs propres à la relation ;
  b) vérifier la dimension (binaire, ternaire, etc.) ;
  c) définir les cardinalités (1-1, 1-N ou M-N).
  
  5. Vérifier le schéma obtenu, notamment :
  a) supprimer les transitivités ;
  b) s'assurer que le schéma est connexe ;
  c) s'assurer qu'il répons aux demandes.
  6. Valider avec les utilisateurs.

EXERCICE:

Cas pratiques
Gestion d'une bibliothèque

II. Conception logique relationnelle

La modélisation conceptuelle résulte en un schéma conceptuel qu'il faut rendre opérationnelle à l'aide d'une représentation logique dont le formalisme dépend du type de système de gestion de base de données visé.

Les concepts :

Le modèle relationnel allie la simplicité de la représentation des données au moyen des tables à une définition rigoureuse des concepts fondée sur la théorie mathématique des relations. Le modèle relation s'appui sur trois concepts fondamentaux :

Domaine : ensemble de valeurs défini en extension ou en intension. Un domaine peut être simple ou composé.

Domaine simple : si tous les éléments sont atomiques ou décomposables.
Ex : l'ensemble des grades du salarié peut être définit en extension par employé, agent de maîtrise, ou cadre.

Domaine composé : si les éléments peuvent être décomposés.
Ex : les dates sont décomposées d'un jour, un mois et une année.

Relation ou table : sous-ensemble du produit cartésien d'une liste de domaines. Autrement dit, une relation est un tableau à deux dimensions, chaque colonne appelée aussi attribut contient un ensemble des valeurs d'un domaine. Chaque ligne représente un tuple.

Schéma d'une relation : nom de la relation suivi de la liste des attributs, de la définition de leurs domaines et de l'ensemble des contraintes d'intégrité associées à cette table.

Schéma relationnel : ensemble des schémas de relation d'une base de données.

Contraintes d'intégrité : expression logique qui doit être vrai, à tout moment, dans une base de données. Ex : on ne peut pas avoir deux cours dans la même salle à la même heure.

Spécifier les contraintes d'intégrité permet d'en assurer la vérification systématique par le SGBD et ainsi de contrôler la cohérence des données.
Types de contraintes d'intégrité :

Contrainte de domaine : restriction de l'ensemble des valeurs possibles d'un attribut.

Contrainte de clé : définit un sous-ensemble minimal des colonnes tel que la table ne puisse contenir deux lignes ayant mêmes valeurs pour ces colonnes.

Contrainte obligatoire : précise qu'un attribut ou plusieurs attributs doivent toujours avoir une valeur.

Contrainte d'intégrité référentielle ou d'inclusion: lie deux colonnes ou deux ensembles de colonnes de deux tables différentes.

La théorie de la normalisation

La théorie de la normalisation est l'un des points forts du modèle relationnel. Elle permet de définir formellement la qualité des tables au regard du problème posé par la redondance des données. En effet, une mauvaise conception d'un schéma relationnel peut entraîner une redondance des données, voir une perte d'information. Cette redondance peut entraîner des anomalies lors d'opération de mise à jours des données.

La théorie de la normalisation s'appui sur la dépendance fonctionnelle rappelée ci-dessous.

Cod a définit un ensemble de formes normales caractérisant les tables relationnelles :
Première forme normale (IFN) : si elle ne contient que des attributs atomiques.
Deuxième forme normale (2FN) : si elle est en IFN et, si de plus, il n'existe pas de dépendance fonctionnelle entre une partie d'une clé et une colonne non clé de la table.
Troisième forme normale (3FN) : si elle est en 2FN et si, de plus, aucune dépendance fonctionnelle entre les colonnes non clé.

Ainsi, plus une table est normalisée moins elle comporte de redondances et donc de risques d'incohérence sémantiques dans les schémas relationnels.

Démarche pour la conception d'un schéma relationnel

Les règles principales de transformation d'un schéma conceptuel entité-relation en un schéma relationnel sont :

Règle I : Toute entité est traduite en une table relationnelle dont les caractéristiques sont les suivantes :
" le nom de la table et le nom de l'entité ;
" la clé de la table et l'identifiant de l'entité ;
" les autres attributs de la table forment les autres colonnes de la table.

Règle II : Toute relation binaire M-N est traduite en une table relationnelle dont les caractéristiques sont les suivantes :
" le nom de la table est le nom de la relation ;
" la clé de la table est formée par la concaténation des identifiants des entités participant à la relation ;
" les attributs spécifiques de la relation forment les autres colonnes de la table.

Une contrainte d'intégrité référentielle est générée entre chaque colonne clé de la nouvelle table et la table d'origine de cette clé.

Règle III : Toute relation binaire 1-N est traduite :

1. Soit par un report de clé : l'identifiant de l'entité participant à la relation côté N est ajoutée comme colonne supplémentaire à la table représentant l'autre entité. Cette colonne est parfois appelée clé étrangère. Le cas échéant, les attributs spécifiques à la relation sont eux aussi ajoutés à la même table ;

2. soit par une table spécifique dont les caractéristiques sont les suivantes :
" le nom de la table est le nom de la relation ;
" la clé de la table est l'identifiant de l'entité participent à la relation côté 1 ;
" les attributs spécifiques de la relation forment les autres colonnes de la table.

De plus une contrainte d'intégrité référentielle est générée.
Le choix du mode de traduction (report de clé ou table spécifique) peut être dicté par des considérations liées aux traitements futurs sur la base. Même si l'on dispose pas nécessairement d'une spécification détaillée des ces traitements, on peut partiellement les anticiper par l'analyse plus approfondie de cette relation. Un élément d'appréciation de ces traitements est la cardinalité minimale de l'entité côté 1. S cette cardinalité minimale est égale à 1, il est préférable d'opter pour le report de clé, dont l'avantage est de minimiser le nombre de tables. A noter que l'effet de cette minimisation n'est pas tant d'économiser l'espace disque que d'améliorer les performances de la base en réduisant le nombre de jointures entre tables.
Si la cardinalité minimale est égale à 0, nous proposons alors d'examiner la proportion d'entités participant effectivement à la relation. Si cette proportion est importante, on peut en déduire que la relation l'est aussi et opter encore pour le report de clé. En revanche, si cette proportion est relativement faible, il est préférable d'opter pour la table spécifique, dans la mesure où l'on peut penser que cette faible proportion exprime une importance relative de cette relation dans l'application.

EX2

Cas pratiques

Gestion d'une bibliothèque

Énoncé
À partir du schéma ER conçu pour la gestion de bibliothèque, produire le schéma relationnel correspondant. Dans chaque table, souligner la clé et préciser, le cas échéant, les clés candidates. Vérifier que le schéma obtenu est en troisième forme normale.

Corrigé
Par application de la règle I, on obtient les tables suivantes:

Par application de la règle 2, on obtient de plus les deux tables suivantes:

ÉCRIT(code_auteur, code_catalogue)
DÉCRIT(code_mot_clé, code_catalogue)

Et les contraintes d'intégrité référentielle suivantes:

ÉCRIT[code_auteur] - AUTEUR[code_auteur|
ÉCRIT[code_catalogue| - LiVRE|code_catalogue]
DÉCRIT|code_mot_clé] - MOT_CLÉ [code_mot_clé]
DÉCRIT|code_catalogue] - LIVRE[code_catalogue]

Le schéma ER comporte trois relations I-N. Toutes trois sont à cardinalité minimale 1 du côté où la cardinalité maximale est 1 :
•tout livre porte sur un et un seul thème;
•tout exemplaire se rapporte à un livre;
•tout exemplaire provient d'un éditeur.

Par application de la règle 3, en utilisant le report de clé, on complète de la façon suivante les deux tables LIVRE et EXEMPLAIRE:
LIVRE(code_catalogue, titre, code_thème)
EXEMPLAIRE(code_rayon, usure, date_acquisition, année_édition, code_éditeur,code_catalogue)

Et les contraintes d'intégrité référentielle suivantes:
EXEMPLAiRE[code_catalogue] - LiVRE[code_cataloguel]
LIVRE[code_thème] THEME [code_thème]
EXEMPLAiRE[code_éditeur] - ÉDiTEUR[code_éditeur]
Reste à traduire les deux relations ternaires Demande et Emprunt. Par application de la règle 5, on ajoute deux tables DEMANDE et EMPRUNT comportant, en plus des attributs propres (date_retour pour l'emprunt) les clés des entités participantes. On obtient ainsi la table DEMANDE:
DEMANDE(code_catalogue, matricule, date)
Et les contraintes d'intégrité référentielle suivantes:
DEMANDE[code_catalogue| - LIVRE[code_catalogue]
DEMANDE[matricule] - ABONNE[matricule]

La table EMPRUNT entre dans le cas particulier mentionné à la règle 5. En effet, sa clé peut être simplifiée dans la mesure où un même exemplaire à une même date ne peut être emprunté que par, au plus, un abonné. La clé, sous-ensemble minimum d'attributs, ne comprend donc pas le matricule de l'abonné:
EMPRUNT(code_ravon, date, matricule, date_retour)

Et les contraintes d'intégrité référentielle suivantes:
EMPRUNT[code_rayon] - EXEMPLAIRE[code_rayon]
EMPRUNT[matricule] - ABONNÉ [matricule]

La date, entité monoattribut, ne nécessite pas de traduction spécifique.
Le schéma relationnel obtenu est le suivant (les clés étrangères sont en italique):

ABONNÉ(matricule, nom, adresse, téléphone, date_adhésion, date_naissance, csp)
THÈME(code_thème, intitulé)
MOT_CLÉ(code_mot clé, mot_clé)
AUTEUR(code_auteur, nom_auteur, prénom_auteur)
ÉDITEUR(code_éditeur, nom_éditeur, adresse_éditeur)
ÉCRiT(code_auteur, code_catalogue)
DÉCRiT(code_mot_clé, code_catalogue)
LIVRE(code_catalogue, titre, code_thème)
EXEMPLAiRE(code_rayon, usure, date_acquisition, année_édition, code_éditeur, code _catalogue)
DEMANDE(code_catalogue, matricule, date)
EMPRUNT(code_rayon, date, matricule, date_retour)

Avec les contraintes d'intégrité référentielles suivantes:
ÉCRIT[code_auteur]- AUTEUR [code_auteur]
ÉCRIT[code_catalogue] - LIVRE[code_catalogue]
DÉCRIT[code_mot_clé] - MOT_CLÉ [code_mot_clé]
DÉCRIT[code_catalogue] - LivRE[code_catalogue]
EXEMPLAiRE[code_catalogue]- LiVRE[code_catalogue]
LIVRE[code_thème] -THÈME[code_thème]
EXEMPLAiRE[code_éditeur]- ÉDITEuR [code_éditeur]
DEMANDE [code_catalogue] -LIVRE[code_catalogue|
DEMANDE[matricule] - ABONNE [matricule]
EMPRUNT [code_rayon] - EXEMPLAiRE[code_rayon]
EMPRUNT[matricule] - ABONNÉ[matricule]

On vérifie le schéma relationnel obtenu en établissant la liste des dépendances fonctionnelles valides: dans la table ABONNE par exemple, les seules dépendances fonctionnelles sont constituées de MATRICULE en partie gauche. En effet, il n'y a pas de dépendance de nom vers adresse, ce qui signifierait que deux abonnés ne peuvent porter le même nom.
De même il n'y a pas de dépendance de téléphone vers nom, puisque deux personnes peuvent partager un numéro de téléphone sans toutefois porter le même nom.
De la même façon, on peut vérifier qu'aucune autre dépendance fonctionnelle n'existe dans la table ABONNE. Donc il n'y a pas, au sein de cette table, de dépendance fonctionnelle entre les colonnes non clé ou d'une partie de la clé vers un attribut non clé.
On en déduit donc que la table relationnelle ABONNE est en troisième forme normale. Il est aisé de vérifier qu'il en est de même pour les autres tables du schéma relationnel.
Cela confirme qu'un bon schéma conceptuel mène à un schéma relationnel normalisé.

SQL

Gestion d'une bibliothèque

Énoncé
À partir du schéma relationnel conçu pour la gestion de bibliothèque, définir à l'aide du langage SQL, l'ensemble des commandes de génération de ce schéma sur un noyau Oracle. Créer les index nécessaires.
Créer une vue permettant de rechercher toutes les caractéristiques d'un exemplaire: code rayon, code catalogue et titre du livre correspondant, thème.

Corrigé
Le schéma relationnel obtenu est le suivant (les clés étrangères sont en italiques):

ABONNÉ(matricule, nom, adresse, téléphone, date_adhésion, date_naissance, csp)
THÈME(code_thème, intitulé)
MOT_CLÉ(code_mot clé, mot_clé)
AUTEUR(code_auteur, nom_auteur, prénom_auteur)
ÉDITEUR(code_éditeur, nom_éditeur, adresse_éditeur)
ÉCRiT(code_auteur, code_catalogue)
DÉCRiT(code_mot_clé, code_catalogue)
LIVRE(code_catalogue, titre, code_thème)
EXEMPLAiRE(code_rayon, usure, date_acquisition, année_édition, code_éditeur, code _catalogue)
DEMANDE(code_catalogue, matricule, date)
EMPRUNT(code_rayon, date, matricule, date_retour)

La génération des tables suppose l'utilisation de la commande CREATE TABLE, ce qui produit l'ensemble d'instructions suivant:

CREATE TABLE abonné (matricule INTEGER PRIMARY KEY, nom CHAR (30),
adresse VARCHAR(5O),
téléphone CHAR(10),
date_naissance DATE,
date_adhésion DATE,
csp CHAR(5));

CREATE TABLE thème (code_thème INTEGER PRIMARY KEY, intitulé char(30));

CREATE TABLE motclé (code_mot_clé INTEGER PRIMARY KEY, mot_clé CHAR(40));

CREATE TABLE auteur (code_auteur CHAR(2) PRIMARY KEY,
nom_auteur CHAR(30),
prénom_auteur CHAR(30));

CREATE TABLE éditeur (code_éditeur CHAR(2) PRIMARY KEY,
nom_éditeur CHAR(30),
adresse_éditeur CHAR(50));

CREATE TABLE écrit(code_auteur char(3) NOT NULL,
code_catalogue CHAR(8) NOT NULL,
UNIQUE(code_auteur,code_catalogue));

CREATE TABLE décrit (code_mot_clé INTEGER NOT NULL, code catalogue CHAR(8) NOT NULL,
UNIQUE(code_mot_clé,code_catalogue));

CREATE TABLE livre(code_catalogue CHAR(8) PRIMARY KEY,
titre VARCHAR(50) NOT NULL,
code_thème INTEGER REFERENCES thème(code_thème)
CONSTRAINT RC-thème);

CREATE TABLE exemplaire (code rayon INTEGER PRIMARY KEY,
usure INTEGER,
date_acquisition DATE,
année_édition INTEGER,
code_éditeur CHARC(2) REFERENCES éditeur(code_éditeur)),
code__catalogue CHAR(8) REFERENCES livre(code_catalogue));

CREATE TABLE demande(code_catalogue CHAR(8) REFERENCES
livre(code_catalogue),
matricule INTEGER REFERENCES abonné(matricule),
date_demande DATE,
UNIQUE(code_catalogue,rnatricule,date_demande));

CREATE TABLE emprunt (code_rayon INTEGER REFERENCES exemplaire (code_rayon),
date_emprunt DATE,
matricule INTEGER REFERENCES abonné(matricule),
date_retour DATE,
UNIQUE(code_rayon,date_emprunt),
CHECK MONTHS_BETWEEN(date_emprunt,date_retour) <= 1);

Cet exemple permet d'illustrer quelques types de données parmi les plus courants: entier (INTEGER, SMALLINT), décimal (DECIMAL(m,n),NUMBER(m,n)), réel flottant (FLOAT, REAL), chaîne de caractères (CHAR(n) .VARCHAR(n)), date (DATE).
La contrainte PRIMARY KEY permet de définir une clé primaire pour chaque table. La contrainte NOT NULL assure l'obligation de saisir des valeurs dans les colonnes des tables DECRIT, ECRIT, LIVRE. Autrement dit, on ne peut insérer une ligne dans la table ECRIT sans y faire figurer nécessairement un code_auteur. La contrainte NOT NULL est implicite pour les colonnes clés primaires.
Pour les colonnes identifiantes non clé primaire, on définit une contrainte d'unicité, par exemple on ne peut avoir deux emprunts du même exemplaire à la même date: UNIQUE(code_rayon, date_emprunt). La contrainte matricule integer références abonné(matricule) impose que matricule soit la clé primaire de la table ABONNE. C'est un des exemples de contraintes référentielles. On peut nommer les contraintes à l'aide du mot-clé CONSTRAINT. L'intérêt principal de ce nom est d'autoriser dans l'avenir la suppression de ladite contrainte au moyen de la commande DROP CONSTRAINT. La contrainte sémantique CHECK MONTHS_BETWEEN(date_emprunt,date_retour) <= 1;

sera vérifiée chaque fois qu'une date d'emprunt et/ou une date de retour sera saisie ou modifiée. MONTHS_BETWEEN est l'une des nombreuses fonctions qu'offre Oracle pour manipuler les dates. Comme son nom l'indique, la fonction calcule le nombre de mois s'écoulant entre deux dates. Elle renvoie un nombre entier. À noter que les systèmes possèdent certains mots réservés, qui ne peuvent donc être utilisés pour désigner les constituants du schéma. Ainsi, nous avons dû renommer les dates d'emprunt et de demande car le mot DATE est réservé par SQL pour spécifier le type de données DATE.
De la même façon, on peut créer l'ensemble des index de la base Bibliothèque, à l'aide d'instructions du type: CREATE UNIQUE INDEX il ON Abonné (matricule);
Cet index accélérera les recherches d'abonné à partir du matricule ou les jointures entre la table ABONNE et les autres tables contenant le matricule.

La création de la vue permettant de rechercher toutes les caractéristiques d'un exemplaire s'obtient de la façon suivante:
CREATE VIEW vueexemplaire
AS SELECT code_rayon, code_catalogue, titre, intitulé
FROM exemplaire, livre, thème
WHERE exemplaire.code_catalogue=livre.code_catalogue
AND 1ivre.code_thème=thème.code_thème;

La clause SELECT contient la liste des colonnes que l'on veut faire figurer dans la vue. La clause FROM contient les trois tables nécessaires pour extraire ces colonnes, à savoir EXEMPLAIRE, LIVRE et THEME. La clause WHERE contient les deux conditions de jointure entre ces trois tables, à savoir l'égalité des codes catalogues entre le livre et l'exemplaire et l'égalité des codes thèmes entre le livre et le thème. Cette vue pourra être utilisée par un programme pour afficher sur un écran toutes les données utiles sur un exemplaire de livre.

EX2

Géographie

Énoncé
Pour faciliter l'apprentissage de la géographie, on a constitué une base de données relationnelles dont le schéma est le suivant:

PAYS(code pays, nom_pays, population, continent)
CAPITALE(ville, code_pays)
GRANDE_VILLE(ville, code_pays, population)
FRONTIÈRE(code_pays 1, code_pays2)

La table PAYS contient une codification de chaque pays, sa population approximative et le continent auquel ce pays appartient. On considère que chaque pays se trouve principalement rattaché à un seul continent. La table CAPITALE relie chaque pays à sa ville capitale. code_pays est une clé candidate de cette table. La table GRANDE_VILLE contient un grand nombre des villes importantes de ces pays.
Enfin, la table FRONTIERE contient les codes des pays qui ont une frontière comune (pays limitrophes). On suppose qu'il n'existe pas deux villes portan même nom. Utiliser SQL pour répondre aux questions suivantes:
1. Quels sont les pays d'Afrique dont la population est supérieure à 20 millions d'habitants?
2. Quelle est la capitale de la Finlande ?
3. À quel continent est rattachée l'Egypte ?
4. La Bulgarie et l'Albanie ont-elles une frontière commune ?
5. Quelle est la population de la capitale de la Finlande ?
6. Donner la liste des pays qui n'ont pas de frontière commune avec un autre pays.

Corrigé
1. SELECT nom_pays FROM pays
WHERE population > 20000000 AND continent='Afrique';

2. SELECT ville FROM capitale
WHERE code_pays IN
(SELECT code_pays FROM pays WHERE nom_pays='Finlande');

3. SELECT continent FROM pays
WHERE nom_pays='Egypte';

4. SELECT 'oui '
FROM pays
WHERE nom_pays='Bulgarie' AND code_pays IN
(SELECT code_paysl FROM frontière
WHERE code_pays2 IN
(SELECT code_pays FROM pays
WHERE nom_pays='Albanie')) OR code_pays IN
(SELECT code_pays2 FROM frontière
WHERE code_paysl IN
(SELECT codejays FROM pays WHERE nom_pays='Albanie')) ;

5. SELECT population FROM grande^ville
WHERE ville IN
(SELECT ville FROM capitale
WHERE code_pays IN (SELECT code_pays FROM pays WHERE nom_pays='Finlande'));

6. SELECT nom_pays FROM pays WHERE code_pays NOT IN
(SELECT code_paysl FROM frontière)
AND code_pays NOT IN
(SELECT code_pays2 FROM frontière);

Géographie
Énoncé
On reprend le schéma relationnel géographique du chapitre 3:

PAYS(code pays, nom_pays, population, pnb, continent)
CAPITALE(ville, code_pays)
GRANDE_VILLE(ville, code_pays, population)
FRONTIÈRE(code_pays 1, code_pays2)

On a ajouté la colonne pnb dans la table PAYS pour prendre en compte le produit
national brut de ce pays.

Utiliser SQL pour répondre aux questions suivantes:
1. Y a-t-il des pays limitrophes appartenant à des continents différents ?
2. Quels sont les pays dont la capitale n'est pas la ville la plus peuplée ?
3. Quels sont les pays ayant au moins une ville de plus d'un million d'habitants ?
4. Quel est le pays ayant le produit national brut le plus bas ?
5. Quel est le produit national brut moyen des pays d'Afrique ?
6. Quels sont les pays dont la capitale regroupe plus du tiers de leur population ?
7. Vérifier si la table FRONTIERE contient des doublons.

Corrigé
1. SELECT p1.nom_pays,p2.nom_pays
FROM pays p1, pays p2
WHERE EXISTS (SELECT *
FROM frontière
WHERE code_paysl=pl.code_pays
AND code_pays2=p2.code_pays)
AND p1.continent <> p2.continent ;

2. SELECT nom_pays
FROM pays
WHERE EXISTS
(SELECT *
FROM grande_ville
WHERE code_pays=pays.code_pays
AND population >
(SELECT population
FROM grande_ville
WHERE ville=
(SELECT ville
FROM capitale WHERE code_pays=pays.code_pays)));

3. SELECT nom_pays
FROM pays
WHERE code_pays IN
(SELECT code_pays
FROM grande_ville
WHERE population > 1000000);

4. SELECT nom_pays
FROM pays
WHERE pnb =
(SELECT MIN(pnb) FROM pays);

5. SELECT AVG(pnb)
FROM pays
WHERE continent - 'Afrique';

6. SELECT nom_pays
FROM pays
WHERE population <
(SELECT population *10/3 FROM
grande_ville
WHERE ville =
(SELECT ville
FROM capitale
WHERE code_pays=pays.code_pays));
7. SELECT FI.* FROM frontière FI
WHERE EXISTS
(SELECT F2.*
FROM frontière F2
WHERE F1.code_pays1=F2.code_pays2
AND F2.code_pays1=F1.code_pays2);

FINE