Formation Base de données relationnelles

Participez au vote ☆☆☆☆☆★★★★★

Table des matières

Les BD sont nées à la fin des années 1960 pour combler les lacunes des systèmes de fichiers et faciliter la gestion qualitative et quantitative des données informatiques. Les SGBD sont des applications informatiques permettant de créer et de gérer des BD (comme Oracle ou PostgreSQL par exemple)

Les BD relationnelles, issues de la recherche de Codd, sont celles qui ont connu le plus grand essor depuis les années, et qui reste encore aujourd'hui les plus utilisées. On utilise des SGBDR pour les implémenter. Le langage SQL est le langage commun à tous les SGBDR, ce qui permet de concevoir des BD relativement indépendamment des systèmes utilisés.

Les usages de BD se sont aujourd'hui généralisés pour entrer dans tous les secteurs de l'entreprise, depuis les petites bases utilisées par quelques personnes dans un service pour des besoins de gestion de données locales, jusqu'aux bases qui gèrent de façon centralisée des données partagées par tous les acteurs de l'entreprise.

L'accroissement de l'utilisation du numérique comme outil de manipulation de toutes données (bureautique, informatique applicative, etc.) et comme outil d'extension des moyens de communication (réseaux) ainsi que les évolutions technologiques (puissance des PC, Internet, etc.) ont rendu indispensable, mais aussi complexifié la problématique des BD.

Les conséquences de cette généralisation et de cette diversification des usages se retrouvent dans l'émergence de solutions conceptuelles et technologiques nouvelles, les bases de données du mouvement NoSQL particulièrement utilisées par les grands acteurs du web.

a) Base de données

Logiciel et données

Un logiciel informatique est composé de programmes, c'est à dire d'instructions données à l'ordinateur, et de données auxquelles s'appliquent ces instructions.

Par exemple un logiciel de traitement de texte est composé de fonctions - ouvrir, copier, coller, insérer une image, changer la police, enregistrer - et de fichiers sur lesquels elles s'appliquent. Dans ce cas les fichiers de traitement de texte sont les données.

Définition : Définition lâche de base de données : un ensemble de données

On appelle parfois base de données tout ensemble de données stocké numériquement et pouvant servir à un ou plusieurs programme. De ce point de vue des fichiers sur un disque dur, un fichier de tableur, voire un fichier de traitement de texte peuvent constituer des bases de données.

Définition : Définition restreinte de base de données : un ensemble de données structuré

On appellera base de données un ensemble de données numériques qui possède une structure ; c'est à dire dont l'organisation répond à une logique systématique.

On parlera de modèle logique de données pour décrire cette structure.

Exemtple : Base de données relationnelle

Une base de données relationnelle permet d'organiser les données en tableaux (appelés relations).

espèce	eucaryote	multicellulaire	propriété
bactéries	false	false
archées	false	false
protistes	true	false
champignons	true	true	décompose
végétaux	true	true	photosynthétise
animaux	true	true	ingère

Tableau 1 Base de données de classification classique des espèces animales

Fondamental: Fonctions d'une base de données

Une base de données est structurée afin de pouvoir mieux répondre à des fonctions fondamentales en informatique, telles que :

 Stocker l'information de façon fiable (c'est à dire être capable de restituer l'information entrée dans le système)

 Traiter de grands volumes de données (massification)

 Traiter rapidement les données (optimisation)

 Sécuriser les accès aux données (gérer les autorisations selon les utilisateurs)

 Contrôler la qualité des données (par exemple la cohérence par rapport à un modèle pré-établi)

 Partager les données (entre plusieurs applications dédiées à plusieurs métiers)

 Rendre accessible les données en réseau (gérer la concurrence des accès parallèles) 

b) Système de gestion de bases de données (SGBD)

Définition : Système de Gestion de Bases de Données

Un SGBD est un logiciel qui prend en charge la structuration, le stockage, la mise à jour et la maintenance d'une base de données. Il est l'unique interface entre les informaticiens et les données (définition des schémas, programmation des applications), ainsi qu'entre les utilisateurs et les données (consultation et mise à jour).

Exemtple : Exemtples de SGBD

 Oracle est un SGBD relationnel et relationnel-objet très utilisé pour les applications professionnelles.

 PostgreSQL est un SGBD relationnel puissant qui offre une alternative libre (licence BSD) aux solutions commerciales comme Oracle ou IBM.

 Access est un SGBD relationnel Microsoft, qui offre une interface graphique permettant de concevoir rapidement des applications de petite envergure ou de réaliser des prototypes.

 MongoDb est un SGBD non-relationnel libre (licence Apache) orienté document. Il permet de gérer facilement de très grandes quantités de données - dans un format arborescent JSON - réparties sur de nombreux ordinateurs.

Comtplément : SGBD relationnel et non-relationnel

Les SGBR relationnels (SGBDR) sont les plus courants des SGBD ; jusqu'au début des années 2000, la plupart des bases de données étaient relationnelles.

Mais avec l'arrivée des géants du web, ces entreprises qui gèrent des quantités énormes de données comme Google, Amazon ou Facebook, s'est développé un mouvement important de développement de bases de données nonrelationnelles, également appelées NoSQL.

c) Application de base de données

Une base de données seule n'est pas directement utilisable par un utilisateur humain ; elle n'est utilisable que par les informaticiens qui connaissent son langage de programmation et par les applications qui ont été programmées pour s'en servir.

Définition

On appelle application de base de données un logiciel informatique permettant à un utilisateur final de manipuler (lire ou écrire) les données d'une base de données.

Exemtple : Atptplication web

Une application web est composée d'interfaces en HTML qui permettent d'écrire et de lire des données dans une base de données, via un langage applicatif, comme par exemple PHP.

Exemtple : Twiter

L'application Twitter est composée d'interfaces web permettant d'entrer des données (saisir son profil, twitter, retwitter, ) et de sortir des données (consulter un fil twitter, faire une recherche sur un hashtag ) d'une base de données (Twitter utilise une base de données NoSQL Apache Cassandra).

Cette base de données est stockée sur les serveurs de Twitter et elle contient tous les profils de tous les utilisateurs, tous les tweets, tous les hashtags

Exemtple : Comtpagnie aérienne

Une base de données de gestion de l'activité d'une compagnie aérienne concerne les voyageurs, les vols, les avions, le personnel, les réservations

Une application à partir d'une telle base de données pourra permettre la gestion des réservations, des disponibilités des avions en fonction des vols à effectuer, des affectations des personnels volants

Exemtple : Atptplication de bureau Access

Avec un logiciel comme Access on peut réaliser à la fois une base de données et une application permettant de manipuler cette base de données pour des besoins bureautiques simples.

d) Donnée (en relationnel) : table, objet, propriété, domaine, atomicité

Ratptpel : Base de données relationnelle

Une base de données relationnelle permet d'organiser les données en tables (appelés relations). Chaque case de la table contient une information atomique.

Définition : Objet (ligne)

Chaque ligne de la table correspond à un objet que l'on veut gérer dans la base de données : une voiture, une personne, une espèce

Fondamental

Toutes les lignes d'une même table correspondent à des objets du même type, donc dans une table, on met soit des voitures, soit des personnes, mais on ne mélange pas les deux.

Définition : Protpriété et domaine (colonne)

Chaque colonne de la table correspond à une propriété des objets qui se trouvent dans la table ; tous les objets de la table partagent donc les mêmes propriétés.

Fondamental: Domaine

Chaque colonne de la table est associée à un domaine de valeur fixéa priori, par exemple : entier, texte, booléen

Définition : Donnée en relationnel (cellule)

Une donnée en relationnel, c'est une cellule d'une table, qui correspond à la propriété d'un objet.

propriété 1 domaine : d1	propriété 2 domaine : d2
objet1, donnée 1	objet1, donnée 2
objet2, donnée 1	objet2, donnée 2

Tableau 2 Une table ou relation ( en relationnel)

Exemtple

	espèce domaine : texte	eucaryote domaine : booléen
	bactéries	false
	archées	false

Tableau 3 Exemple de relation instanciée

Atention : Atomicité

Pour que la base de données fonctionne correctement on veille à ne mettre qu'une seule donnée par case, c'est le principe d'atomicité en relationnel.

espèce, domaine : texte

bactéries : procaryotes unicellulaires

archées : procaryotes unicellulaires

protistes : eucaryotes unicellulaires

champignons : eucaryotes multicellulaires qui décomposent

végétaux : eucaryotes multicellulaires qui photosynthétisent

animaux : eucaryotes multicellulaires qui ingèrent

Tableau 4 Un mauvais exemple de relation : les données ne sont pas atomiques (il y a

plusieurs données par case de la table)

e) Langage de données : l'exemple du langage SQL

Définition : Langage de données

Un langage de données est un langage informatique permettant de décrire et de manipuler les schémas et les données d'une BD.

Synonymes : Langage orienté données

Fondamental: SQL

SQL est le langage consacré aux SGBD relationnels et relationnels-objet.

Il permet de :

 créer des tables, en définissant le domaine de chaque colonne ;

 insérer des lignes dans les tables

 lire les données entrées dans la base de données

Exemtple : Création de table en SQL (définition du schéma de données)

CREATE TABLE Etudiant ( NumEtu : integer PRIMARY KEY,

Nom : varchar,

Ville : varchar)

Cette instruction permet de créer une relation "Etudiant" comportant les propriétés "NumEtu", "Nom" et "Ville" de domaines, respectivement, entier, texte et texte.

Exemtple : Insertion de ligne en SQL (création de données)

INSERT INTO Etudiant (NumEtu, Nom, Ville) VALUES (1, 'Holmes', 'Londres')

Cette instruction permet de créer l'étudiant numéro 1, de nom Holmes qui habite la ville de Londres.

Exemtple : Manitpulation de données en SQL (extploitation des données)

SELECT Nom

FROM Etudiant

WHERE Ville = 'Compiègne'

Cette instruction permet de rechercher les noms de tous les étudiants habitant la ville de Compiègne.

Comtplément : Autres langages de données

 XQuery est un langage de données mobilisé dans les bases de données arborescentes XML.

 Les bases NoSQL proposent des langages de données spécifiques, souvent inspirés du

SQL. Par exemple le langage de MongoDB permet de manipuler une base de contenus JSON.

a) Exercice : Étapes de la conception d'une base de données relationnelle

Mettre dans l'ordre les étapes de conception suivantes.

1. Création du code SQL pour un SGBDR

2. Modélisation conceptuelle en UML ou E-A

3. Élaboration du modèle logique en relationnel

4. Analyse de la situation existante et des besoins

Réponse : ___ ___ ___ ___

b) Méthodologie générale de conception d'une base de données

Méthode : Étatpes de la concetption d'une base de données

1. Analyse de la situation existante et des besoins (clarification)

2. Création d'un modèle conceptuel qui permet de représenter tous les aspects importants du problème

3. Traduction du modèle conceptuel en modèle logique (et normalisation de ce modèle logique)

4. Implémentation d'une base de données dans un SGBD, à partir du modèle logique (et optimisation)

Graphique 1 Processus de conception d'une base de données

On distingue quatre étapes dans la conception d'une base de données :

 L'analyse

Elle consiste à étudier le problème et à consigner dans un document, la note de clarification, les besoins, les choix, les contraintes.

 La modélisation conceptuelle

Elle permet de décrire le problème posé, de façon non-formelle (en générale graphique), en prenant des hypothèses de simplification. Ce n'est pas une description du réel, mais une représentation simplifiée d'une réalité.

 La modélisation logique

Ele permet de décrire une solution, en prenant une orientation informatique générale (type de SGBD typiquement), formelle, mais indépendamment de choix d'implémentation spécifiques.

 L'implémentation

Elle correspond aux choix techniques, en terme de SGBD choisi et à leur mise en œuvre (programmation, optimisation ).

Fondamental

 Bien analyserle problème posé en amont

 Bien modéliserle problème au niveau conceptuel avant de passer au niveau logique et à l'implémentation

Conseil : L'imtportance de l'étatpe d'analyse

La première étape de la conception repose sur l'analyse de l'existant et des besoins. De la qualité de la réalisation de cette première étape dépendra ensuite la pertinence de la base de données par rapports aux usages. Cette première étape est donc essentielle et doit être menée avec soins.

Si la première étape est fondamentale dans le processus de conception, elle est aussi la plus délicate. En effet, tandis que des formalismes puissants existent pour la modélisation conceptuelle puis pour la modélisation logique, la perception de l'existant et des besoins reste une étape qui repose essentiellement sur l'expertise d'analyse de l'ingénieur.

Conseil : L'imtportance de l'étatpe de modélisation concetptuelle

Étant donnée une analyse des besoins correctement réalisée, la seconde étape consiste à la traduire selon un modèle conceptuel. Le modèle conceptuel étant formel, il va permettre de passer d'une spécification en langage naturel, et donc soumise à interprétation, à une spécification non ambigüe. Le recours aux formalismes de modélisation tels que E-A ou UML est donc une aide fondamentale pour parvenir à une représentation qui ne sera plus liée à l'interprétation du lecteur.

La traduction d'un cahier des charges spécifiant l'existant et les besoins en modèle conceptuel reste néanmoins une étape délicate, qui va conditionner ensuite l'ensemble de l'implémentation informatique. En effet les étape suivantes sont plus mécaniques, dans la mesure où un modèle logique est déduit de façon systématique du modèle conceptuel et que l'implémentation logicielle est également réalisée par traduction directe du modèle logique.

Remarque: Les étatpes de traduction logique et d'imtplémentation

Des logiciels spécialisés (par exemple Objecteering [w_objecteering]) sont capables à partir d'un modèle conceptuel d'appliquer des algorithmes de traduction qui permettent d'obtenir directement le modèle logique, puis les instructions pour la création de la base de données dans un langage orienté données tel que SQL. L'existence de tels algorithmes de traduction montre que les étapes de traduction logique et d'implémentation sont moins complexes que les précédentes, car plus systématiques.

Néanmoins ces étapes exigent tout de même des compétences techniques pour optimiser les modèles logiques (normalisation), puis les implémentations en fonction d'un contexte de mise en œuvre matériel, logiciel et humain.

c) Qatre étapes pour réduire la complexité

La réalisation d'une base de données est une tâche complexe, le découpage en quatre étapes permet de gérer cette complexité.

Clarification ModélisationConceptuelle ModélisationLogique Im plém entation

Conception en quatre étapes

L'idée générale est de ne pas mélanger la nature des tâches : typiquement, on ne veut pas en même temps se poser des questions générales relatives aux besoins (ce que je veux faire) et des questions techniques très spécifiques (comment représenter telle information).

Méthode

L'approche est donc :

 de "regarder à gauche" : on regarde le monde quand on clarifie, on regarde la note de clarification quand on fait le MCD, on regarde le MCD quand on fait le MLD, on regarde le MLD quand on implémente ;

 et de ne "pas regarder trop loin" : on anticipe déjà le MCD pendant la clarification, mais on ne se préoccupe pas de questions d'implémentation ; quand on fait le MLD, on ne se pose plus de question sur le monde, la clarification et le MCD ont dû déjà répondre ; quand on implémente on ne se pose plus de question de modélisation, on s'occupe des programmes, de la machine.

Fondamental

On peut toujours revenir sur une étape, la conception est itérative, mais on ne doit pas tout le temps se poser tous les problèmes en même temps.

d) Éléments pour l'analyse de l'existant et des besoins

La phase d'analyse de l'existant et des besoins est une phase essentielle et complexe. Elle doit aboutir à des spécifications générales qui décrivent en langage naturel les données manipulées, et les traitements à effectuer sur ces données.

On se propose de donner une liste non exhaustive d'actions à mener pour rédiger de telles spécifications.

Méthode : L'analyse de documents existants

La conception d'une base de données s'inscrit généralement au sein d'usages existants. Ces usages sont généralement, au moins en partie, instrumentés à travers des documents électroniques ou non (papier typiquement). Il est fondamental d'analyser ces documents et de recenser les données qu'ils manipulent.

Exemtple : Exemtples de document existants à analyser

 Fichiers papiers de stockage des données (personnel, produits, etc.)

 Formulaires papiers d'enregistrement des données (fiche d'identification d'un salarié, fiche de description d'un produit, bon de commande, etc.)

 Documents électroniques de type traitement de texte (lettres, mailing, procédures, etc.)

 Documents électroniques de type tableurs (bilans, statistiques, calculs, etc.)

 Bases de données existantes, à remplacer ou avec lesquelles s'accorder (gestion des salaires, de la production, etc.)

 Intranet d'entreprise (information, téléchargement de documents, etc.)

 etc.

Méthode : Le recueil d'extpertise métier

Les données que la base va devoir manipuler sont toujours relatives aux métiers de l'entreprise, et il existe des experts qui pratiquent ces métiers. Le dialogue avec ces experts est une source importante d'informations. Il permet également de fixer la terminologie du domaine.

Exemtple : Exemtples d'extperts à consulter

 Praticiens (secrétaires, ouvrier, contrôleurs, etc.)

 Cadres (responsables de service, contre-maîtres, etc.)

 Experts externes (clients, fournisseurs, etc.)

 etc.

Méthode : Le dialogue avec les usagers

La base de données concerne des utilisateurs cibles, c'est à dire ceux qui produiront et consommeront effectivement les données de la base. Il est nécessaire de dialoguer avec ces utilisateurs, qui sont les détenteurs des connaissances relatives aux besoins réels, liés à leur réalité actuelle (aspects de l'organisation fonctionnant correctement ou défaillants) et à la réalité souhaitée (évolutions, lacunes, etc.).

Exemtple : Exemtples d'utilisateurs avec qui dialoguer

 Personnes qui vont effectuer les saisies d'information (à partir de quelles sources ? Quelle est leur responsabilité ? etc.)

 Personnes qui vont consulter les informations saisies (pour quel usage ? pour quel destinataire ? etc.)

 Personnes qui vont mettre à jour les informations (pour quelles raisons ? comment le processus est enclenché ? etc.)

 etc.

Méthode : L'étude des autres systèmes informatiques existants

la base de données va généralement (et en fait quasi systématiquement aujourd'hui) s'insérer parmi un ensemble d'autres logiciels informatiques travaillant sur les données de l'entreprise. Il est important d'analyser ces systèmes, afin de mieux comprendre les mécanismes existants, leurs forces et leurs lacunes, et de préparer l'intégration de la base avec ces autres systèmes. Une partie de ces systèmes seront d'ailleurs souvent également des utilisateurs de la base de données, tandis que la base de données sera elle même utilisatrice d'autre systèmes.

Exemtple : Exemtples d'autres systèmes coexistants à étudier

 Autres bases de données (les données sont elle disjointes ou partiellement communes avec celles de la base à concevoir ? quelles sont les technologies logicielles sur lesquelles reposent ces BD ? etc.)

 Systèmes de fichiers classiques (certains fichiers ont-ils vocations à être supplantés par la base ? à être générés par la base ? à alimenter la base ? etc.)

 Applications (ces applications ont elles besoins de données de la base ? peuvent-elles lui en fournir ? etc.)

 etc.

Comtplément : Méthodes d'analyse

Il existe des outils et méthodes d'analyse en ingénierie, comme l'analyse fonctionnelle (AF), qui dépassent le cadre de la conception des bases de données, mais sont tout à fait complémentaires.

e) Modélisation conceptuelle de données

L'objection du modèle conceptuel est de représenter le problème à l'aide de représentations graphiques et partiellement formelles.

Les principales caractéristiques du modèle conceptuel sont :

 Une représentation graphique simple

 Une puissance d'expression élevée pour un nombre de symboles raisonnables

 Une lecture accessible à tous et donc un bon outil de dialogue entre les acteurs techniques et non techniques

 Une formalisation peu ambiguë et donc un bon outil de spécification détaillée

Remarque

Le modèle n'est pas encore formel, donc certaines représentations peuvent être équivoques, même si on a levé de très nombreuses ambiguïtés.

E-A

La modélisation conceptuelle en bases de données relationnelle était à l'origine faite avec le formalisme E-A de la méthode MERISE.

Modèle E-A "gestion de projets"

UML

UML est un autre langage de modélisation, plus récent que E-A et couvrant un spectre plus large que les bases de données. En tant que standard de l'OMG et en tant que outil très utilisé pour la programmation orientée objet, il a supplanté la modélisation E-A.

Remarque

En BD on utilise uniquement le diagramme de classe d'UML pour modéliser conceptuellement les données.

Modèle UML "gestion de projets"

f) Modélisation logique de données

Définition : Modèle logique de données

Un modèle logique de données est une description, au moyen d'un langage formel, d'un ensemble de données.

Un schéma permet de décrire la structure d'une base de données, en décrivant l'ensemble des types de données de la base. Une instance de base de données est constituée d'un ensemble de données qui respectent le schéma de la base.

Synonyme : schéma de données, schéma

Exemtple : Modèle logique de données relationnel

Un modèle logique de données relationnel permet de représenter une base de données relationnelles, c'est à dire : des tables, des propriétés, des domaines

Exemtple : Schéma d'une relation

Espece(nom:chaîne, eucaryote:booléen, multicellulaire:booléen, propriété:chaîne)

Exemtple : Schéma d'une base de données avec tplusieurs relations

Etudiant (num:entier, nom:chaîne, ville:chaîne)

Module(num:entier, titre:chaîne)

Inscription(numetu:entier, nummod:entier, année:entier(4))

: Instance de base de données

Etudiant Module

172	Dupont	Lille
173	Durand	Paris
174	Martin	Isabelle

1	SGBD
2	OS

Inscription

172	1	2016
172	2	2016
173	1	2015
174	2	2017

Comtplément : Exemtple de formalismes de modélisation logique

 Le modèle CODASYL, antérieur au modèle relationnel est un modèle hiérarchique (Tardieu, 1983 [Tardieu83]).

 Le modèle relationnel (tabulaire) est le modèle dominant à la base des SGBDR.

 Le modèle relationnel-objet (adaptation des modèles relationnels et objets au cadre des SGBD) est actuellement en croissance.

 D'autres modèles (document, graphe, ) se développent dans le cadre du mouvement NoSQL.

Comtplément : Voir aussi

Brève introduction aux bases de données NoSQL - p.98

g) Synthèse : Les trois niveaux de conception

 Niveau Conceptuel

Modèle conceptuel graphique - Exemples  E-A

 UML

 Niveau Logique

Schéma logique indépendant d'un SGBD - Exemples

 Relationnel

 Objet

 Relationnel-Objet

 Graphe

 Document

 Niveau Informatique

Implémentation pour un SGBD particulier - Exemples

 Oracle

 MySQL

 PostgreSQL

 DB2

 Access

 SQLServer

 MongoDB

 Cassandra

Modélisation Modélisation Im plém entation Clarification Conceptuelle Logique

AF UML Relationnel SQL Conception en quatre étapes : exemple de formalismes

Cette série d'exercices est destinée à faire expérimenter un SGBDR, afin de se familiariser avec les concepts classiques des bases de données relationnelles.

Pour la réalisation de cet exercice, se connecter sur le site , et conserver la fenêtre du navigateur ouverte.

 Db DiscoÓ

a) Notion de table

Créer sa première table

Une base de données relationnelle est principalement constituée de tables (ou « relations » d'où le nom de relationnel). Une table est basiquement un élément d'organisation de l'information constitué de colonnes (ou attributs) et de lignes (ou enregistrements).

Nous allons dans un premier temps créer le schéma d'une table, c'est à dire définir ses colonnes. Pour cela nous utiliserons l'instruction SQL LDD « CREATE ».

Question 1

Exécuter l'instruction suivante et décrire ce qu'elle fait.

CREATE TABLE tEtu (

pk_numSecu CHAR(13) PRIMARY KEY, k_numEtu VARCHAR(20) UNIQUE NOT NULL, nom VARCHAR(50), prenom VARCHAR(50));

Alimenter la table

Une fois les colonnes de la table définies, nous pouvons en déclarer les lignes. Nous utilisons pour cela l'instruction SQL LMD « INSERT ».

Question 2

Exécuter les deux instructions suivantes et décrire ce qu'elles font.

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('1800675001066', 'AB3937098X', 'Dupont', 'Pierre');

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('2820475001124', 'XGB67668', 'Durand', 'Anne');

Interroger la table

Une fois une table créée, il est possible à tout moment d'en inspecter le contenu. Nous utilisons pour cela l'instruction SQL LMD « SELECT ».

Question 3

Exécuter l'instruction suivante et décrire ce qu'elle fait.

SELECT pk_numSecu, k_numEtu, nom, prenom

FROM tEtu;

Question 4

Exécuter l'instruction suivante et décrire ce qu'elle fait.

SELECT nom, prenom

FROM tEtu

WHERE pk_numSecu='2820475001124';

b) Notion de contraintes

Contrainte de domaine

Lorsque l'on définit une table, on définit également des contraintes sur cette table, qui serviront à contrôler son intégrité, par rapport à des règles que l'on aura fixées.

C'est notamment le cas des contraintes de domaine, qui permettent de vérifier qu'une colonne prend ses valeurs parmi un ensemble déterminé (les chaînes de 10 caractères au plus, les entier de 1 à 1000, etc.).

Question 1

Exécuter l'instruction suivante et expliquer pourquoi le système renvoie une erreur.

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('XXXXXXXXXXXXXXX', 'XXXXXX', 'Dupont', 'Pierre');

Question 2

Donner un exemple de contrainte qui n'est pas formulée dans la définition de la table tEtu et que l'on aurait pu souhaiter.

Indice :

Pour indiquer qu'un élément est obligatoire, on ajoute la clauseNOT NULLaprès la définition de son domaine dans l'instructionCREATE TABLE.

Contraintes de clé

Les contraintes de clé se composent de contraintes d'unicité et de contraintes de non nullité. Elles permettent d'assurer que toutes les valeurs d'une colonne seront différentes pour chaque ligne.

Question 3

Exécuter les trois instructions suivantes (les unes après les autres) et expliquer ce qui se passe.

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('1800675001066', 'HGYT67655Y', 'Dupont', 'Pierre');

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('2810592012232', 'XGB67668', 'Durand', 'Anne');

INSERT INTO tEtu (pk_numSecu, k_numEtu, nom, prenom)

VALUES ('2810592012232', 'HGYT67655Y', 'Duchemin', 'Aline');

Question 4

Explorer le contenu de votre table en exécutant l'instruction suivante, et vérifier vos explications précédentes.

SELECT *

FROM tEtu;

Question 5

Pourrait-on insérer dans la table une seconde personne qui aurait le prénom "Aline" et le nom "Duchemin" ? Pourquoi ?

c) Notion de références

Clé étrangère

Une base de données est en général constituée de plusieurs tables. Ces tables se référencent entre elles en utilisant une clé étrangère : c'est à dire qu'une des colonnes de la table est utilisée pour faire référence à la colonne d'une autre table.

On va à présent créer une seconde table, qui permettra d'associer des Unités de Valeurs (UVs) aux étudiants, puis insérer des valeurs dans cette table.

CREATE TABLE tUv ( pk_code CHAR(4) NOT NULL, fk_etu CHAR(13) NOT NULL,

PRIMARY KEY (pk_code, fk_etu),

FOREIGN KEY (fk_etu) REFERENCES tEtu(pk_numSecu));

INSERT INTO tUV (pk_code, fk_etu)

VALUES ('NF17', '1800675001066');

INSERT INTO tUV (pk_code, fk_etu)

VALUES ('NF26', '1800675001066');

INSERT INTO tUV (pk_code, fk_etu)

VALUES ('NF29', '1800675001066');

Question 1

Expliciter ce qu'exprime le contenu de la table tUv .

Contraintes d'intégrité référentielle

Lorsque nous avons défini la table tUv, nous avons défini une contrainte supplémentaire, dite d'intégrité référentielle : contrainte de type FOREIGN KEY.

Question 2

En exécutant les instructions suivantes, expliquer quel est le rôle d'une contrainte d'intégrité référentielle.

INSERT INTO tUV (pk_code, fk_etu)

VALUES ('NF17', '2810592012232');

INSERT INTO tUV (pk_code, fk_etu)

VALUES ('NF17', '1700792001278');

d) Projection, restriction et jointure

L'instruction SELECT du langage SQL LMD nous donne de larges possibilités pour interroger les tables d'une base de données. Cette instruction se fonde notamment sur les opérations mathématiques de l'algèbre relationnelle, dont les principales sont la projection, la restriction, le produit et la jointure.

Question 1

Exécuter l'instruction suivante et expliquer pourquoi c'est une projection.

SELECT nom, prenom

FROM tEtu;

Question 2

Exécuter l'instruction suivante et expliquer pourquoi c'est une restriction.

SELECT *

FROM tEtu

WHERE nom='Dupont';

Question 3

Exécuter l'instruction suivante et expliquer pourquoi c'est un produit (cartésien).

SELECT *

FROM tEtu,tUv;

Question 4

Exécuter l'instruction suivante et expliquer pourquoi c'est une jointure.

SELECT *

FROM tEtu JOIN tUv ON pk_numSecu=fk_etu;

Question 5

Exécuter l'instruction suivante et montrer qu'une jointure est la composition d'un produit et d'une restriction.

SELECT *

FROM tEtu,tUv

WHERE pk_numSecu=fk_etu;

e) Fonctions et agrégats

L'instruction SELECT permet également d'effectuer des calculs qui portent sur plusieurs lignes, ce que l'on appelle des agrégats.

Question 1

Exécuter la requête SQL suivante et expliquer le résultat obtenu.

SELECT COUNT(pk_code)

FROM tUv

WHERE fk_etu='1800675001066';

Question 2

Exécuter la requête SQL suivante et expliquer le résultat obtenu.

SELECT fk_etu, COUNT(pk_code)

FROM tUv

GROUP BY fk_etu;

Question 3

Compléter la requête SQL suivante afin qu'elle renvoie, pour chaque UV, le nombre d'étudiants inscrits.

SELECT _______, COUNT(______)

FROM tUv

GROUP BY _______

* *

À l'issue de cette série d'exercices, vous devez savoir définir les termes suivants :

 table ou relation

 schéma relationnel

 domaine

 clé

 clé étrangère

 opérations de projection, restriction, jointure, produit

Description du problème

[30 min]

Un laboratoire souhaite gérer les médicaments qu'il conçoit.

Un médicament est décrit par un nom, qui permet de l'identifier. En effet il n'existe pas deux médicaments avec le même nom. Un médicament comporte une description courte en français, ainsi qu'une description longue en latin. On gère aussi le conditionnement du médicament, c'est à dire le nombre de pilules par boîte (qui est un nombre entier).

Ce problème est un exemple très simple que l'on pourra modéliser avec une base de données relationnelle à une seule table.

Vous pouvez tester vos instructions SQL ici : Db DiscoÓ

Question 1

Proposer une clarification du problème (par exemple sous la forme d'une liste des propriétés de la relation visée).

Question 2

Proposer un exemple de données.

Question 3

Dessiner un modèle conceptuel de données en UML. Il ne contient qu'une seule classe.

Indice :

Modélisation conceptuelle de données

Question 4

Proposer un modèle logique de données sous forme de schéma relationnel. Il ne contient qu'une seule relation.

Indice :

Modélisation logique de données

Question 5

Proposer une implémentation en SQL standard de votre modèle relationnel. Il ne contient qu'une seule instruction CREATE TABLE.

Indice :

Langage de données : l'exemple du langage SQL

Question 6

Écrivez les instructions SQL permettant d'insérer vos données de test dans votre base de données.

Indice :

Langage de données : l'exemple du langage SQL

La modélisation conceptuelle est l'étape fondatrice du processus de conception de BD. Elle consiste à abstraire le problème réel posé pour en faire une reformulation qui trouvera une solution dans le cadre technologique d'un

SGBD.

Si le modèle dominant en conception de bases de données a longtemps été le modèle E-A, le modèle UML se généralise de plus en plus. Nous proposons ici une introduction au diagramme de classes à travers la représentation de classes et d'associations simples (il existe d'autres diagrammes UML, par exemple le diagramme de cas, et d'autres primitives de représentation dans le diagramme de classe, par exemple l'héritage).

a) Exercice : Centre médical

[5 min]

Soit le modèle conceptuel suivant représentant des visites dans un centre médical. Quelles sont les assertions vraies selon ce schéma ?

Image 1

	Un patient peut effectuer plusieurs visites.
	Tous les patients ont effectué au moins une consultation.
	Un médecin peut recevoir plusieurs patients pendant la même consultation.
	Un médecin peut prescrire plusieurs médicaments lors d'une même consultation.
	Deux médecins différents peuvent prescrire le même médicament.

b) Q'est ce qu'un modèle ?

Définition : Modèle

« Modeling, in the broadest sense, is the cost-effective use of something in place of something else for some cognitive purpose. It allows us to use something that is simpler, safer or cheaper than reality instead of reality for some purpose. A model represents reality for the given purpose; the model is an abstraction of reality in the sense that it cannot represent all aspects of reality. » (Rothenberg, 1989 [Rothenberg et al., 1989], cité par Arribe, 2014 [Arribe, 2014])

« Système physique, mathématique ou logique représentant les structures essentielles d'une réalité et capable à son niveau d'en expliquer ou d'en reproduire dynamiquement le fonctionnement. » (TLFi)

Fondamental: Modèle

Un modèle est une représentation simplifiée de la réalité en vue de réaliser quelque chose.

c) Q'est ce qu'un modèle en informatique ?

Définition : Modèle informatique

Un modèle informatique est une représentation simplifiée de la réalité en vue de réaliser un traitement avec un ordinateur.

Comtplément : Numérisation et abstraction : Toute information numérique a été codée selon un modèle donné

« Tout numérisation est une représentation de la réalité sous la forme d'une modélisation numérique. Cette modélisation procède d'une abstraction au sens où c'est une séparation d'avec le réel, au sens où c'est une construction destinée à la manipulation (algorithmique en l'occurrence) et au sens où c'est une simplification de la réalité. »

d) Q'est ce qu'un bon modèle ?

Atention

Un modèle est une abstraction, une simplification de la réalité, ce n'est pas la réalité, il n'est jamais complètement fidèle par construction.

Le seul modèle complètement fidèle à la réalité est la réalité elle-même, et ce n'est donc pas un modèle.

Exemtple : La carte et le territoire

Une carte est un modèle d'un territoire. Elle est une représentation simplifiée destiné à un usage particulier :

 randonner à pied, en vélo ;

 se diriger en voiture sur des grands axes, sur des axes secondaires ;

 voler en avion de tourisme, en avion de ligne ;

 naviguer sur fleuve, sur mer ;

 étudier les frontières d'une région, d'un pays, de la terre ;

 étudier la démographie, l'économie ; 

Fondamental

À partir de cet exemple on notera que :

1. Un modèle est orienté par un usage.

Chacune de ces cartes est très différente selon ce que l'on veut faire.

2. Un modèle ne cherche pas à être proche de la réalité.

Chacune de ces cartes est très différente de la réalité qu'elle représente.

3. Un modèle adresse un niveau d'information qui existe mais qui n'est pas accessible dans la réalité.

Chacune de ces cartes permet quelque chose que ne permet pas l'accès direct à la réalité.

1 -

Méthode : Le rasoir d'Ockham : Entre deux modèles donnés le meilleur modèle est-il toujours le tplus fourni ?

La méthode de raisonnement connue sous le nom de rasoir d'Ockham (du nom du philosophe éponyme) consiste à préférer les solutions les plus simples aux plus complexes, lorsqu'elles semblent permettre également de résoudre un problème donné ; entre deux théories équivalentes, toujours préférer la plus simple.

Ce principe s'applique très bien à la modélisation : étant donné un objectif et plusieurs modèles possibles, il ne faut pas choisir a priori celui qui représente le plus de choses, mais préférer le plus simple dès qu'il couvre le besoin.

C'est un principe d'économie (il coûte moins cher à produire) et d'efficacité (car les éléments inutiles du modèle plus fourni nuiront à l'efficacité de la tâche).

Exemtple

Ainsi, pour naviguer en voiture, il est plus simple de ne pas avoir sur la carte les chemins de randonnées qui ne sont pas praticables en voiture.

a) Lab I

Description du problème

[15 min]

Un laboratoire souhaite gérer les médicaments qu'il conçoit.

 Un médicament est décrit par un nom, qui permet de l'identifier. En effet il n'existe pas deux médicaments avec le même nom. Un médicament comporte une description courte en français, ainsi qu'une description longue en latin. On gère aussi le conditionnement du médicament, c'est à dire le nombre de pilules par boîte (qui est un nombre entier).

 À chaque médicament on associe une liste de contre-indications, généralement plusieurs, parfois aucune. Une contre-indication comporte un code unique qui l’identifie, ainsi qu'une description. Une contre-indication est toujours associée à un et un seul médicament. Exemple de données

Afin de matérialiser notre base de données, nous obtenons les descriptions suivantes :

 Le Chourix a pour description courte « Médicament contre la chute des choux » et pour description longue « Vivamus fermentum semper porta. Nunc diam velit, adipiscing ut tristique vitae, sagittis vel odio. Maecenas convallis ullamcorper ultricies. Curabitur ornare. ». Il est conditionné en boîte de 13. Ses contre-indications sont :

- CI1 : Ne jamais prendre après minuit.

- CI2 : Ne jamais mettre en contact avec de l'eau.

 Le Tropas a pour description courte « Médicament contre les dysfonctionnements intellectuels » et pour description longue « Suspendisse lectus leo, consectetur in tempor sit amet, placerat quis neque. Etiam luctus porttitor lorem, sed suscipit est rutrum non. ». Il est conditionné en boîte de 42. Ses contre-indications sont :

- CI3 : Garder à l'abri de la lumière du soleil

Question 1

Réaliser le modèle conceptuel de données en UML du problème.

Question 2

Étendre le modèle conceptuel UML afin d'ajouter la gestion des composants. Un composant est identifié par un code unique et possède un intitulé. Tout médicament possède au moins un composant, souvent plusieurs. Tout composant peut intervenir dans la fabrication de plusieurs médicaments. Il existe des composants qui ne sont pas utilisés pour fabriquer des médicaments et que l'on veut quand même gérer.

b) Présentation d'UML

UML est un langage de représentation destiné en particulier à la modélisation objet. UML est devenu une norme OMG en 1997.

UML propose un formalisme qui impose de "penser objet" et permet de rester indépendant d'un langage de programmation donné. Pour ce faire, UML normalise les concepts de l'objet (énumération et définition exhaustive des concepts) ainsi que leur notation graphique. Il peut donc être utilisé comme un moyen de communication entre les étapes de spécification conceptuelle et les étapes de spécifications techniques.

Fondamental: Diagramme de classe

Le diagramme de classes est un sous ensemble d'UML qui s'attache à la description statique d'un modèle de données représentées par des classes d'objets.

Remarque

Dans le domaine des bases de données, UML peut être utilisé à la place du modèle E-A pour modéliser le domaine. De la même façon, un schéma conceptuel UML peut alors être traduit en schéma logique (relationnel ou relationnel-objet typiquement).

c) Classes

Définition : Classe

Une classe est un type abstrait caractérisé par des propriétés (attributs et méthodes) communes à un ensemble d'objets et permettant de créer des instances de ces objets, ayant ces propriétés.

Image 2 Représentation UML d'une classe

Exemtple : La classe Voiture

L'objet V1 est une instance de la classe Voiture.

V1 : Voiture

 Marque : 'Citroën'

 Type : 'ZX'

 Portes : 5

 Puissance : 6

 Kilométrage : 300000

Comtplément

La modélisation sous forme de diagramme de classes est une modélisation statique, qui met en exergue la structure d'un modèle, mais ne rend pas compte de son évolution temporelle. UML propose d'autres types de diagrammes pour traiter, notamment, de ces aspects.

d) Atributs

Définition : Atribut

Un attribut est une information élémentaire qui caractérise une classe et dont la valeur dépend de l'objet instancié.

Un attribut est typé : Le domaine des valeurs que peut prendre l'attribut est fixé a priori.

 Un attribut peut être multivalué : Il peut prendre plusieurs valeurs distinctes dans son domaine.

 Un attribut peut être dérivé : Sa valeur alors est une fonction sur d'autres attributs de la classe

 Un attribut peut être composé (ou composite) : Il joue alors le rôle d'un groupe d'attributs (par exemple une adresse peut être un attribut composé des attributs numéro, type de voie, nom de la voie). Cette notion renvoie à la notion de variable de type Record dans les langages de programmation classiques.

Atention : On utilise peu les atributs dérivés et composés en UML

 En UML on préfère l'usage de méthodes aux attributs dérivés. On utilisera toujours des méthodes dès que la valeur de l'attribut dérivé dépend d'autres attributs extérieurs à sa classe.

 En UML on préfère l'usage de compositions aux attributs composés. On utilisera toujours des compositions pour les attributs composés et multivalués.

Syntaxe

attribut:type

attribut_multivalué[nbMinValeurs..nbMaxValeurs]:type

/attribut_dérivé:type attribut_composé

- sous-attribut1:type

- sous-attribut2:type

Image 4 Représentation d'attributs en UML

Dans cet exemple, les attributs Nom, Prénom sont de type string, l'un de 20 caractères et l'autre de 10, tandis que DateNaissance est de type date et Age de type integer. Prénom est un attribut multivalué, ici une personne peut avoir de 1 à 3 prénoms. Age est un attribut dérivé, il est calculé par une fonction sur DateNaissance.

Comtplément : Voir aussi

Méthodes

Composition - p.99

e) Repérage des clés

Un attribut ou un groupe d'attributs peut être annoté comme étant clé s'il permet d'identifier de façon unique un objet de la classe.

On ajoute le symbole {key} à côté du ou des attributs concernés.

Clé en UML

Clé composée de deux attributs

Méthode

Le repérage des clés n'est pas systématique en UML (la définition des clés se fera essentiellement au niveau logique). On cherchera néanmoins à repérer les clés rendues évidentes par la phase de clarification.

Atention

On n'ajoutera jamais de clé artificielle§ au niveau du MCD. Si aucune clé n'est évidente, on laisse la classe sans clé. Atention : Atribut souligné et #

On trouvera dans ce cours des exemples d'attributs soulignés ou précédés de # pour exprimer l'unicité. Ce n'est pas une pratique standard et la notation{key}devrait lui être substituée.

 Un attribut souligné est normalement un attribut de classe, oustatic, en UML,  Un attribut précédé de # est normalement un attribut protégé en UML.

Mais les concepts d'attribut de classe et d'attribut protégé ne sont pas utilisés dans le cadre des bases de données.

f) Méthodes

Définition : Méthode

Une méthode (ou opération) est une fonction associée à une classe d'objet qui permet d'agir sur les objets de la classe ou qui permet à ces objets de renvoyer des valeurs (calculées en fonction de paramètres).

Syntaxe

methode(paramètres):type

Remarque: Méthodes et modélisation de BD

Pour la modélisation des bases de données, les méthodes sont surtout utilisées pour représenter des données calculées (à l'instar des attributs dérivées) ou pour mettre en exergue des fonctions importantes du système cible. Seules les méthodes les plus importantes sont représentées, l'approche est moins systématique qu'en modélisation objet par exemple.

Remarque: Méthodes, relationnel, relationnel-objet

Lors de la transformation du modèle conceptuel UML en modèle logique relationnel, les méthodes ne seront généralement pas implémentées. Leur repérage au niveau conceptuel sert donc surtout d'aide-mémoire pour l'implémentation au niveau applicatif.

Au contraire, un modèle logique relationnel-objet permettra l'implémentation de méthodes directement associées à des tables. Leur repérage au niveau conceptuel est donc encore plus important.

Comtplément

Transformation des méthodes par des vues - p.98

g) Associations

Définition : Association

Une association est une relation logique entre deux classes (association binaire) ou plus (association n-aire) qui définit un ensemble de liens entre les objets de ces classes.

Une association est nommée, généralement par un verbe. Une association peut avoir des propriétés (à l'instar d'une classe). Une association définit le nombre minimum et maximum d'instances autorisée dans la relation (on parle de cardinalité).

Syntaxe

Image 5 Notation de l'association en UML

Atention

Le nom de l'association (verbe qui la décrit) est obligatoire, au même titre que le nom d'une classe ou d'un attribut.

Remarque

Une association est généralement bidirectionnelle (c'est à dire qu'elle peut se lire dans les deux sens). Les associations qui ne respectent pas cette propriété sont dites unidirectionnelles ou à navigation restreinte.

Image 6 Représentation d'association en UML

L'association Conduit entre les classes Conducteur et Voiture exprime que les conducteurs conduisent des voitures.

Comtplément : Voir aussi

 Cardinalité

 Explicitation des associations - p.100

 Associations ternaires - p.101

 Contraintes sur les associations - p.101

h) Cardinalité

Définition : Cardinalité d'une association

La cardinalité d'une association permet de représenter le nombre minimum et maximum d'instances qui sont autorisées à participer à la relation. La cardinalité est définie pour les deux sens de la relation.

Syntaxe

Si min_a (resp. max_a) est le nombre minimum (resp. maximum) d'instances de la classe A autorisées à participer à l'association, on note sur la relation, à côté de la classe A : min_a..max_a.

Si le nombre maximum est indéterminé, on note n ou *.

Atention

La notation de la cardinalité en UML est opposée à celle adoptée en E-A. En UML on note à gauche (resp. à droite) le nombre d'instances de la classe de gauche (resp. de droite) autorisées dans l'association. En E-A, on note à gauche (resp. à droite) le nombre d'instances de la classe de droite (resp. de gauche) autorisées dans l'association.

Remarque

Les cardinalités les plus courantes sont :

 0..1 (optionnel)

 1..1 ou 1 (un)

 0..n ou 0..* ou * (plusieurs)

Ici un conducteur peut posséder plusieurs voitures (y compris aucune) et une voiture n'est possédée que par un seul conducteur.

i) Classe d'association

Définition : Classe d'association

On utilise la notation des classes d'association lorsque l'on souhaite ajouter des propriétés à une association.

Syntaxe : Notation d'une classe d'association en UML

Image 8 Notation d'une classe d'association en UML

Image 9 Emplois

[15 min]

Tennis

Le schéma suivant représente les rencontres lors d'un tournoi de tennis. Quelles sont les assertions vraies selon ce schéma ?

Image 10

	On peut jouer des matchs de double.
	Un joueur peut gagner un match sans y avoir participé.
	Il peut y avoir deux matchs sur le même terrain à la même heure.
	Connaissant un joueur, on peut savoir sur quels terrains il a joué.

Journal

Voici le schéma conceptuel du système d'information (très simplifié) d'un quotidien. Quelles sont les assertions vraies selon ce schéma ?

Image 11

	Un article peut être rédigé par plusieurs journalistes.
	Un article peut être publié plusieurs fois dans le même journal.
	Un article peut être publié dans un journal par un journaliste qui ne travaille pas pour ce journal.
	Il peut y avoir plusieurs articles sur le même sujet.
	Un journaliste peut interviewer une personnalité sans faire d'article à ce propos.

Logistique

Une société de transport routier veut installer un système d'information pour rendre plus efficace sa logistique. Embauché au service informatique de cette compagnie, vous êtes donc chargé de reprendre le travail déjà effectué (c'est à dire le schéma suivant).

Quelles sont les assertions vraies selon ce schéma ?

Image 12

	Un conducteur peut conduire plusieurs camions.
	Un conducteur peut conduire un camion sans y être autorisé.
	Il peut y avoir plusieurs conducteurs pour le même camion.
	Un conducteur peut livrer sa propre ville

[20 minutes]

Cet exercice a été inspiré par Bases de données : objet et relationnel [Gardarin99].

On considère une base "Coopérative" qui possède les caractéristiques suivantes :

 Un vin est caractérisé par un numéro entier unique nv, un cru, une année de production et un degré.

 Un viticulteur est caractérisé par un numéro entier unique nvt, un nom et une ville.

 Un viticulteur produit un ou plusieurs vins et réciproquement, un vin est produit par un ou plusieurs producteurs (éventuellement aucun).

 Les buveurs sont caractérisés par un numéro de buveur nb, un nom, prénom et une adresse (limitée à la ville pour simplifier).

 Un buveur consomme des vins et peut passer des commandes pour acheter des vins.

Question 1

Lister tous les types d'objet à considérer, les attributs associés et les domaines de valeurs de ces attributs. Repérer les éventuelles clés.

Question 2

Lister toutes les associations à considérer et indiquer leurs cardinalités.

Question 3

Donner le diagramme UML de cette situation.

[20 min]

On souhaite réaliser une base de données pour gérer les cours dispensés dans une école d'ingénieur, ainsi que les personnes qui interviennent dans ces cours.

Chaque cours est identifié par une année et un numéro. Chaque cours a donc un numéro unique localement à chaque année. Un cours possède un titre et un type ('C' pour Cours, 'TD' ou 'TP'). Un cours possède également une date de début, et une date de fin, qui est toujours de 5 jours après la date de début.

Chaque intervenant est identifié par son nom (deux intervenants ne peuvent pas avoir le même nom). Il a un prénom, un bureau, un ou plusieurs numéros de téléphones (jusqu'à trois numéros) et des spécialités. Un bureau est défini par un centre ('R' pour Royallieu, 'BF' pour Benjamin Franklin et 'PG' pour Pierre Guillaumat), un bâtiment (une lettre de A à Z), et un numéro (inférieur à 1000). Les spécialités sont des couples de chaînes de caractères désignant un domaine (par exemple 'BD') et une spécialité (par exemple 'SGBDRO').

Chaque cours est donné par un unique intervenant.

Voici un exemple : Le cours 'Machines universelles', n°21 de l'année 2014 est donné par Alan Turing entre le 05/01/2014 et le 10/01/2014. C'est un cours de type 'C'. Alan Turing a le bureau 666 au bâtiment X de PG. Il a les numéros de téléphone 0666666666 et 0766666666. Il possède les spécialités suivantes :

 Domaine : Mathématique ; Spécialité : Cryptographie

 Domaine : Informatique ; Spécialité : Algorithmie

 Domaine : Informatique ; Spécialité : Intelligence Artificielle

Question

Réaliser le modèle UML de la base de données. Préciser les clés et les types des attributs.

[30 minutes]

Le service de gestion du personnel d'une entreprise désire s'équiper d'un outil lui permettant de gérer informatiquement ses employés, leurs salaires et leurs congés. Les spécifications suivantes ont pu être mises en exergue par une analyse des besoins réalisée auprès des utilisateurs du service du personnel.

Généralités :

 tout employé est identifié par un nom, un prénom et une date de naissance ;

 tout employé remplit une fonction et appartient à un service ;

 pour chaque employé on gère la date d'embauche et la quotité (c'est à dire le pourcentage de temps travaillé par rapport au temps plein, en cas de travail à temps partiel).

Gestion des salaires :

 pour chaque employé on gère l'historique de ses salaires dans l'entreprise, chaque salaire étant affecté à une période de temps ;

 un salaire est composé d'un salaire brut, de charges patronales et de charges salariales ;

 on cherchera à partir des ces données à obtenir également le salaire chargé (brut + charges patronales), et le salaire net (brut - charges salariales), et ce en particulier pour le salaire en cours (celui que touche actuellement le salarié).

Gestion des congés :

 pour chaque employé, on mémorise chaque congé pris (posant qu'un congé concerne toujours une ou plusieurs journées entières) ;

 chaque employé a le droit aux jours de congés suivants :

- 25 jours (pour une quotité de 1) et 25 x quotité sinon,

- chaque fonction ouvre les droits à un certain nombre de jours de RTT,

- chaque service ouvre les droits à un certain nombre de jours de RTT,

- chaque tranche de 5 ans passés dans l'entreprise donne droit à 1 jour supplémentaire,

- les employés de plus de 40 ans ont un jour supplémentaire, et ceux de plus de 50 ans deux.

 pour chaque employé on cherchera à connaître le nombre total de jours de congés autorisés, le nombre de jours pris et le nombre de jours restants sur l'année en cours.

Question

Réaliser le diagramme de classes permettant de modéliser ce problème.

Le modèle relationnel est aux fondements des SGBDR. Il a été, et continue d'être, le modèle théorique dominant pour la représentation logique des base de données, même si le mouvement NoSQL propose des alternatives.

Le modèle relationnel permet de reformuler le modèle conceptuel dans un formalisme - le tableau - beaucoup plus proche de l'implémentation informatique, bien qu'encore indépendant d'une solution technologique particulière.

a) Niveau logique

Le niveau logique est le lien entre le niveau conceptuel et l'implémentation effective de l'application. Le modèle conceptuel étant un modèle formel, le modèle logique a pour vocation d'être également un modèle formel, mais spécifiant non plus la réalité existante ou recherchée comme le modèle conceptuel, mais les données telles qu'elles vont exister dans l'application informatique.

Pour assumer cette fonction, le modèle relationnel [Codd70] s'est imposé en réaction aux insuffisances des modèles antérieurs, les modèles hiérarchique et réseau, et de part la puissance de ses fondements mathématiques. Encore aujourd'hui dominant le modèle relationnel est un fondement indispensable à la conception de bases de données.

Ratptpel

Méthodologie générale de conception d'une base de données

b) Définition du modèle relationnel

Introduction

Le modèle relationnel a été introduit par Codd [Codd70], en 1970 au laboratoire de recherche d'IBM de San José. Il s'agit d'un modèle simple et puissant à la base de la majorité des bases de données, encore aujourd'hui.

Les objectifs du modèle relationnel, formulés par Codd, sont les suivants :

 Assurer l'indépendance des applications et de la représentation interne des données

 Gérer les problèmes de cohérence et de redondance des données

 Utiliser des langages de données basés sur des théories solides

Définition : Modèle relationnel

On appelle modèle relationnel un ensemble de concepts permettant de formaliser logiquement la description d'articles de fichiers plats, indépendamment de la façon dont ils sont physiquement stockés dans une mémoire numérique.

Le modèle relationnel inclut des concepts pour la description de données, ainsi que des concepts pour la manipulation de données.

Fondamental: Représenter le monde en tables

Le modèle relationnel permet de représenter les données que l'on va gérer à l'aide d'un très petit nombre de concepts très simples :

 Les relations ou tables : des lignes et des colonnes

 Les domaines de valeurs : chaque case d'une table prend une unique valeur dans un domaine pré-défini

 Les clés : il existe des cases dont les valeurs doivent être uniques et non nulles

 Les clés étrangères : il existe des cases qui doivent prendre une valeur existante dans les cases d'une autre table

Comtplément : Extension du modèle relationnel

Le modèle relationnel est un standard, normalisé par l'ISO à travers son langage, le SQL. Il se veut néanmoins dès l'origine extensible, pour permettre de gérer des données plus complexes que les données tabulaires. Le modèle relationnel-objet est né de cette extension.

a) Domaine

Définition : Domaine

Ensemble, caractérisé par un nom, dans lequel des données peuvent prendre leurs valeurs.

Remarque

Un domaine peut-être défini en intension§ (c'est à dire en définissant les propriétés caractéristiques des valeurs du domaine, on parle aussi de compréhension) ou en extension§ (c'est à dire en énumérant toutes les valeurs du domaine)

Exemtple : Domaines définis en intension

 Tous les entiers

 Les réels inférieur à 5

 Les booléen (vrai ou faux)

 Toutes les chaînes de 1 à 255 caractères

 Les valeurs monétaires, définie comme des décimaux avec deux chiffres après la virgule

 Les dates, définies comme des chaînes de 10 caractères comprenant des chiffres et des tirets selon le patron "00-00-0000"

 Les salaires, définis comme des valeurs monétaires compris entre 15.000 et 100.000

Exemtple : Domaines définis en extension

 Couleur : {Bleu, Vert, Rouge, Jaune, Blanc, Noir}

 SGBD : {Hiérarchique, Réseau, Relationnel, Objet, Relationnel-Objet}

b) Exercice

Indiquez quelle définition et quel exemple correspondent respectivement aux motsintensionetextension.

1 - Le domaine des couleurs

2 - Énonciation exhaustive de l'ensemble des objets du domaine

3 - {bleu, rouge, vert}

4 - Explicitation d'un domaine par la description de ses caractéristiques (en vue de sa compréhension abstraite, générale).

Intension Extension

c) Atribut et enregistrement

Définition : Atribut

On appelle attribut d'une relation, une colonne de cette relation. Un attribut est caractérisé par un nom et un domaine dans lequel il prend ses valeurs.

Synonymes : Champs, Propriété, Colonne

Définition : Enregistrement

On appelle enregistrement d'une relation, une ligne de cette relation. Un enregistrement prend une valeur pour chaque attribut de la relation.

Synonymes : Tuple, N-uplet, Vecteur, Ligne

Exemtple

	A		B
1		1
1		2
2		2

Tableau 5 Relation R

La relation R comporte les deux attributs A et B et les trois enregistrements <1,1>, <1,2> et <2,2>

Remarque: Atribut, domaine, ordre

Un attribut se distingue d'un domaine car il peut ne comporter que certaines valeurs de ce domaine.

Les colonnes de la relation ne sont pas ordonnées et elles ne sont donc repérées que par le nom de l'attribut.

Remarque: Valeur nulle

Un enregistrement peut ne pas avoir de valeur pour certains attributs de la relation, parce que cette valeur est inconnue ou inapplicable, sa valeur est alors "null".

d) Exemple : La relation "Vol"

Exemtple

Numero	Compagnie	Avion	Départ	Arrivée	Date
AF3245	Air France	747	Paris	Oulan Bator	01082002
AF6767	Air France	A320	Paris	Toulouse	30072002
KLM234	KML	727	Paris	Amsterdam	31072002

Tableau 6 Relation Vol

Objectifs

Connaître les notions de clés candidates, naturelles, artificielles, primaire, étrangère

Aborder le principe d'éclatement des relations et de nonredondance.

a) Clé

Définition : Clé

Une clé est un groupe d'attributs minimum qui permet d'identifier de façon univoque un tuple dans une relation.

Fondamental

Toute relation doit comporter au moins une clé, ce qui implique qu'une relation ne peut pas contenir deux tuples identiques.

Atention : Atributs de clés unique et non null

Afin d'être déterminants pour l'identification d'un enregistrement, tous les attributs d'une clé doivent être valués, c'est-à-dire qu'aucun ne peut avoir de valeurnull. Dire qu'un groupe d'attribut est une clé équivaut à dire qu'il est unique et nonnull.

Exemtple : Numéro d'étudiant

 Le numéro d'étudiant d'une relation Etudiant est une bonne clé car il y aura systématiquement une valeur non nulle.

 Le groupe d'attributs (nom, prénom) d'une relation Etudiant est en général une mauvaise clé, car les homonymes existent.

b) Déterminer les clés

Détermination d'une clé

Définir un groupe d'attributs comme étant une clé nécessite une réflexion sémantique sur les données composant ces attributs, afin de s'assurer de leur unicité.

Fondamental

La définition des clés est un acte demodélisation, elle ne renvoie pas donc pas à une vérité intangible, mais à la réalité telle qu'elle est représentée dans le modèle que l'on élabore.

Exemtple

L'attribut numéro de sécurité sociale d'une relation personne peut paraître une bonne clé a priori car son unicité est assurée. Mais tout le monde n'en dispose pas forcément (les enfants, des étrangers), donc ce n'est une clé que si l'on considère des personnes affiliées à la sécurité sociale.

c) Clé primaire et clés candidates

Définition : Clé tprimaire

Si plusieurs clés existent dans une relation, on en choisit une parmi celles-ci. Cette clé est appelée clé primaire.

La clé primaire est généralement choisie de façon à ce qu'elle soit la plus simple, c'est à dire portant sur le moins d'attributs et sur les attributs de domaine les plus basiques (entiers ou chaînes courtes typiquement).

Définition : Clés candidates

On appelle clés candidates l'ensemble des clés d'une relation qui n'ont pas été choisies comme clé primaire (elles étaient candidates à cette fonction).

d) Clé artificielle

Définition : Clé artificielle

S'il est impossible de trouver une clé primaire, ou que les clés candidates sont trop complexes, il est possible de faire appel à une clé artificielle. Une clé artificielle est un attribut supplémentaire ajouté au schéma de la relation, qui n'est lié à aucune signification, et qui sert uniquement à identifier de façon unique les enregistrements et/ou à simplifier les références de clés étrangères.

Définition : Clé signifiante

Une clé est signifiante si elle n'est pas artificielle.

Synonyme : Clé naturelle

Atention : Clé artificielle et niveau logique

Au niveau du modèle logique, il faut éviter la simplicité consistant à identifier toutes les relations avec des clés artificielles, et ne réserver cet usage qu'aux cas particuliers.

Conseil

1. Si au moins une clé naturelle composée d'un seul attribut existe en choisir une parmi celles-ci comme clé primaire

2. Sinon, choisir une clé naturelle composée de plusieurs attributs si elle ne pose pas de problème identifié

3. Toujours justifier l'emploi d'une clé artificielle (au niveau logique uniquement pour des raisons de complexité du modèle, les questions de performance sont étudiées au niveau physique)

Remarque: Clé artificielle et niveau tphysique, évolutivité, maintenance et tperformance

Au niveau de l'implémentation physique par contre, il est courant que des clés artificielles soient utilisées de façon systématique.

 Du point de vue de l'évolutivité de la BD, il existe toujours un risque qu'une clé nonartificielle perde sa propriété d'unicité ou de non-nullité.

 Du point de vue de la maintenance de la BD, il existe toujours un risque qu'une clé non-artificielle voit sa valeur modifiée et dans ce cas, la répercution de ce changement pour mettre à jour toutes les références peut poser problème.

 Du point de vue de la performance de la BD, les clés non-artificielles ne sont pas en général optimisées en terme de type et de taille, et donc peuvent limiter les performances dans le cadre des jointures. Précisons néanmoins qu'inversement les clés artificielles ont pour conséquence de systématiser des jointures qui auraient pu être évitées avec des clés primaires signifiantes.

Exemtple : Problème d'évolutivité tposé tpar une clé signifiante

Soit le numéro de sécurité sociale la clé primaire d'une table d'une BD française, elle ne permettra pas d'entrer un individu non-français issu d'un pays ne disposant pas d'un tel numéro.

Exemtple : Problème de maintenance tposé tpar une clé signifiante

Soit le numéro de sécurité sociale la clé primaire d'une table d'une BD centrale dont les données sont exploitées par d'autres tables d'autres BD qui viennent "piocher" dans cette BD pour leurs propres usages, sans que la BD centrale ne connaisse ses "clients". Soit une erreur dans la saisie d'un numéro de sécurité sociale dans la BD centrale, si ce numéro est corrigé, il faudrait (ce qui n'est pas possible dans notre cas) impérativement en avertir toutes les bases utilisatrices pour qu'elles mettent à jour leurs références.

Exemtple : Problème de tperformance tposé tpar une clé signifiante

Soit le numéro de sécurité sociale la clé primaire d'une table comptant un million d'enregistrements, ce numéro est généralement un nombre à 13 chiffres ou une chaîne à 13 caractères, ce qui dans les deux cas est supérieur au nombre à 7 chiffres suffisant pour identifier tous les individus de la BD. Les performances seront donc toujours moins bonnes, lors des jointures, si une clé prend deux fois plus de place en mémoire que son optimum. Mais ajoutons que cette perte de performance n'a pas toujours de conséquence sur la réalité perceptible par les utilisateurs de la BD.

Inversement, soit une clé artificielle la clé primaire d'une table T1, par ailleurs référencée par une autre table T2. Soit le numéro de sécurité sociale un attribut clé de T1. Si l'on veut par requête disposer des informations de T2 ainsi que du numéro de sécurité sociale de T1, alors il faudra faire une jointure, tandis que si ce numéro signifiant avait été choisi comme clé primaire, cela n'aurait pas été nécessaire.

e) Clé étrangère

Définition : Clé étrangère

Une clé étrangère est un attribut ou un groupe d'attributs d'une relation R1 devant apparaître comme clé primaire dans une relation R2 afin de matérialiser une référence entre les tuples de R1 et les tuples de R2. Une clé étrangère d'un tuple référence une clé primaire d'un autre tuple.

Atention

Seule une clé primaire peut être référencée par une clé étrangère, c'est même le seule fonction de la clé primaire : être la clé qui peut être référencée par les clés étrangères.

Définition : Contrainte d'intégrité référentielle

Une clé étrangère respecte la contrainte d'intégrité référentielle si sa valeur est effectivement existante dans la clé primaire d'un tuple de la relation référencée, ou si sa valeur est null.

Une clé étrangère qui ne respecte pas la contrainte d'intégrité référentielle exprime un lien vers un tuple qui n'existe pas et donc n'est pas cohérente.

f) Référence entre relations

Le modèle relationnel a pour objectif la structuration de données selon des relations. L'enjeu est de parvenir à traduire un modèle conceptuel en modèle logique relationnel. Or, il n'y a pas de notion d'association en relationnel, donc il faudra pouvoir traduire les associations avec les concepts dont on dispose : relation, clé, clé étrangère.

Afin de représenter des références entre relations dans un modèle relationnel, la seule solution est de stocker l'information dans une relation, et donc que certains attributs d'une relation servent à pointer sur d'autres relations.

Atention

Il n'y a pas vraiment de référence ou de lien en relationnel, puisque nous ne disposons que de tables, de clés, de clés étrangère et de valeurs.

On va donc devoir se servir de ces outils pour matérialiser une notion de référence.

Méthode : Référence

La référence entre deux tuples T1 et T2 de deux relations différentes est exprimable par une valeur identique entre une clé étrangère du tuple T1 et la clé primaire de l'autre tuple T2. Synonyme : Lien Exemtple

R1 R2

#a1 a2=>R2 #b1 b2

Image 13

L'attribut a2 de la relation R1 référence l'attribut b1 de la relation R2 car a2 est une clé étrangère de R1 vers R2 (b1 est la clé primaire de R2).

Ici on a donc par exemple les tuples identifiés par B et C de R1 qui référencent le tuple identifié par 1 dans R2.

g) Schéma relationnel

Définition : Schéma d'une relation

Le schéma d'une relation définit cette relation en intension. Il est composé :

 du nom de la relation,

 de la liste de ses attributs avec les domaines respectifs dans lesquels ils prennent leurs valeurs,

 de la clé primaire,

 des clés étrangères,  des clés candidates.

Définition : Schéma relationnel d'une base de donnée

Le schéma relationnel d'une BD est la définition en intension de cette BD (par opposition à l'instance de la BD qui est une extension de la BD). Il est composé de l'ensemble des schémas de chaque relation de la BD.

Syntaxe : Relation

Relation (Attribut1:Domaine1, Attribut2:Domaine2, , AttributN:DomaineN)

La relation "Relation" contient N attributs chacun défini sur son domaine.

Syntaxe : Clé tprimaire

Relation (#Attribut1:Domaine1, , #AttributM:DomaineM, ,

AttributN:DomaineN)

La clé de la relation "Relation" est composée des attributs "Attribut1" à "AttributM" (attribut précédés de # ou bien soulignés)

En général on note la clé primaire en premier dans la relation.

Syntaxe : Clé étrangère

Relation1 ( , AttributM=>Relation2, , AttributN=>Relation2)

La relation "Relation1" comporte une clé étrangère (composée des attributs "AttributM" à "AttributN") référençant la clé primaire de "Relation2". Bien sûr il peut exister plusieurs clés étrangères vers plusieurs relations distinctes. Une clé étrangère et sa clé primaire référencée sont toujours composées du même nombre d'attributs. Il n'est pas nécessaire de préciser les domaines des attributs appartenant à la clé étrangère car ce sont forcément les mêmes que ceux de la clé primaire référencée. Il n'est pas non plus en général nécessaire de préciser dans le schéma relationnel quels attributs de la clé étrangère référencent quels attributs de la clé primaire (cela est généralement évident) mais il est possible de la faire on notant "Attribut=>Relation.Attribut".

En général on note les clés étrangères en dernier dans la relation, sauf pour les clés étrangères qui font partie de la clé primaire (clés identifiantes).

Syntaxe : Clé candidates

Relation1 ( AttributM:DomaineM, ) avec AttributM clé

Les clés candidates doivent être notées sur le schéma relationnel :

 S'il n'y a qu'une ou deux clés candidates, les noter directement après la définition de la relation

 S'il y a beaucoup de clés, pour ne pas trop alourdir la notation, renvoyer à un tableau à part

Atention : Clés candidates et clé primaire

La notationR(#a,#b)signifie toujours queRa comme clé primaire(a,b), et non queRaurait deux clésaetb(dont on ne saurait pas laquelle est primaire).

La notationR(#a,b) avec b clésignifie bien queaetbsont deux clés deR, et queaest primaire.

Il ne faut pas confondreune clé composée de deux attributsavecdeux clés.

h) Exemple de schéma relationnel pour la géographie

Exemtple

Personne (#Numero:Entier, Nom:Chaîne, Prénom:Chaîne, LieuNaissance=>Ville)

Pays (#Nom:Chaîne, Population:Entier, Superficie:Réel, Dirigeant=>Personne)

Région (#Pays=>Pays, #Nom:Chaîne, Superficie, Dirigeant=>Personne)

Ville (#CodePostal:CP, Nom:Chaîne, Pays=>Ré, Région=>Ré,

Dirigeant=>Personne)

Exemtple : Exemtple d'instance de la base de données

Personne

# Num ero Nom Prenom LieuNaissance

1 Durand Pierre 60200

2 Dupont Marie 60200

Pays
# Nom	Population	Superficie	Dirig eant

France 60 500001,01 2

Allemagne 80 600000,23564 2

Espagne 40 350000,1 1

Région

# Pays # Nom Superficie Dirig eant

France Picardie 50 1

Espagne Picardie 40 1

France Normandie 30 2

Ville

# CodePostal Nom Pays Rég ion Dirig eant F60200 Compiègne France Picardie 1 F60300 Senlis France Picardie 2

F60301 Senlis France Picardie 2

E8000 Senlis Espagne Picardie 2

Le schéma relationnel précédent décrit :

 Des personnes

Elles sont identifiées par un numéro qui est en fait une clé artificielle. En effet, même une clé composée de tous les attributs (Nom, Prénom, LieuNaissance) laisse une possibilité de doublons (homonymes nés dans la même ville).

La clé étrangère LieuNaissance fait référence à la relation Ville, et plus précisément à sa clé primaire CodePostal, ce qui est est laissé implicite car évident.

 Des pays

Ils sont identifiés par leur nom, puisque deux pays ne peuvent avoir le même nom. Les pays sont dirigés par des personnes, et ce lien est matérialisé par la clé étrangère Dirigeant.

 Des régions

Elles font partie d'un pays et ont un nom. Deux régions de pays différents pouvant avoir le même nom, il faut utiliser une clé primaire composée à la fois du nom de la région et du nom du pays, qui est une clé étrangère (le nom est appelé clé locale car il n'est pas suffisant pour identifier un tuple de la relation Région, et la clé étrangère vers la relation Pays est appelée clé identifiante).

 Des villes

Elles sont identifié par un code postal qui est unique dans le monde (en utilisant le préfixe de pays de type "F-60200"). Ce code postal a pour domaine CP qui est une chaîne composée d'une ou deux lettres, d'un tiret, puis d'une série de chiffres.

Le lien d'appartenance entre une ville et une région est matérialisé par la clé étrangère composée des deux attributs Pays et Région. Cette clé référence la clé primaire de la relation Région, également composée de deux attributs. Pour clairement expliciter les références (bien que sémantiquement la dénomination des attributs ne laisse pas de place au doute) on utilise la syntaxe Ré et Ré.

a) Synthèse : Schéma relationnel

Schéma relationnel

Un schéma relationnel permet une formalisation d'un modèle logique.

 Relation ou table

Sous-ensemble d'un produit cartésien - Attribut ou colonne

Prend ses valeurs dans un domaine

- Enregistrement ou ligne

Pose une valeur (y compris la valeur "null") pour chaque attribut

 Clé

Groupe d'attributs ayant un rôle d'identification au sein d'un enregistrement

- Clé candidate

Identifie de façon unique un enregistrement

- Clé primaire

Clé candidate choisie pour représenter un enregistrement pour sa facilité d'usage

- Clé étrangère

Référence la clé primaire d'un tuple d'une autre relation pour exprimer un lien

b) Bibliographie commentée sur le modèle relationnel

Comtplément : Synthèses

SQL2 SQL3, applications à Oracle [Delmal01]

Une définition synthétique et efficace du domaine relationnel : relation, domaine, attribut, clé, intégrité, opérateurs (Premier chapitre).

a) Produit cartésien

Définition : Produit cartésien

Le produit cartésien, noté "X", des domaines D1, D2, , Dn, noté "D1 X D2 X X Dn" est l'ensemble des tuples (ou n-uplets ou vecteurs) <V1,V2, ,Vn> tel que Vi est une valeur de Di et tel que toutes les combinaisons de valeurs possibles sont exprimées.

Exemtple

D1 = {A, B, C}

D2 = {1, 2, 3}

D1 X D2 = {<A,1>, <A,2>, <A,3>, <B,1>, <B,2>, <B,3>, <C,1>, <C,2>, <C,3>,}

b) Relation

Définition : Relation

Une relation sur les domaines D1, D2, , Dn est un sous-ensemble du produit cartésien "D1 X D2 X X Dn". Une relation est caractérisée par un nom. Synonymes : Table, tableau

Syntaxe

On peut représenter la relation R sur les domaine D1, , Dn par une table comportant une colonne pour chaque domaine et une ligne pour chaque tuple de la relation.

D1		Dn
V1	Vn

V1	Vn

Tableau 7 Relation R

Remarque

Une relation est définie en extension, par l'énumération des tuples la composant.

Description du problème

[20 min]

Un laboratoire souhaite gérer les médicaments qu'il conçoit.

Afin de matérialiser notre base de données, nous obtenons les descriptions suivantes :

- CI1 : Ne jamais prendre après minuit.

- CI2 : Ne jamais mettre en contact avec de l'eau.

- CI3 : Garder à l'abri de la lumière du soleil

Question 1

Dessiner des relations instanciées (en extension donc) remplies avec les données fournies en exemple.

Question 2

Écrivez le schéma relationnel (définition en intension donc) permettant de représenter une base de données relationnelle pour le laboratoire.

Afin de pouvoir implémenter une base de données, il faut pouvoir traduire le modèle conceptuel en modèle logique. Cela signifie qu'il faut pouvoir convertir un modèle UML en modèle relationnel. Les modèles conceptuels sont suffisamment formels pour que ce passage soit systématisé dans la plupart des cas.

Objectifs

Savoir faire le passage d'un schéma conceptuel UML à un schéma relationnel pour les cas simples.

a) Transformation des classes

Méthode : Classe

Pour chaque classe non abstraite,

 on crée une relation dont le schéma est celui de la classe ;

 la clé primaire de cette relation est une des clés de la classe.

Classe1

Graphique 2 Classe

Classe1( )

Remarque

Les classes abstraites sont ignorées à ce stade, et n'étant pas instanciables, ne donnent généralement pas lieu à la création de relation.

b) Transformation des atributs

Méthode : Atributs simtples

Pour chaque attribut élémentaire et monovalué d'une classe,  on crée un attribut correspondant.

Classe1

a {key} b

Graphique 3 Attribut

Classe1(#a,b)

Méthode : Atributs comtposites

Pour chaque attribut composite comprenant N sous-attributs d'une classe,

 on crée N attributs correspondants,

 dont les noms sont la concaténation du nom de l'attribut composite avec celui du sous attribut.

Classe1

a {key} b

-b1

-b2

Graphique 4 Attribut composé

Classe1(#a,b_b1,b_b2)

Méthode : Atributs multivalués

Pour chaque attribut multivalué b d'une classe C,

 on crée une nouvelle relation RB,

 qui comprend un attribut monovalué correspondant à b,

 plus la clé de la relation représentant C ;

 la clé de RB est la concaténation des deux attributs.

Classe1

a {key} b[1..10]

Graphique 5 Attribut multivalué

Classe1(#a)

RB(#b,#a=>Classe1)

Méthode : Atributs multivalués (méthode alternative)

Dans le cas où le nombre maximum de b est fini, et petit, on peut également adopter la transformation suivante :

Classe1(#a,b1,b2,b3,b4,b5,b6,b7,b8,b9,b10).

Si le nombre d'attributs est infini (b[1..*]) c'est impossible, s'il est trop grand ce n'est pas souhaitable.

Méthode : Atributs comtposés multivalués

On combine les règles énoncées pour les attributs composés et pour les attributs multivalués.

Classe1

a {key} b [0..N] -b1

-b2

Graphique 6 Attribut composé multivalué

Classe1(#a)

RB(#b_b1,#b_b2,#a=>Classe1)

Ratptpel : Voir aussi

Transformation des compositions - p.102

c) Transformation des atributs dérivés et méthodes

Méthode : Atributs dérivés et méthodes

On ne représente pas en général les attributs dérivés ni les méthodes dans le modèle relationnel, ils seront calculés dynamiquement soit par des procédures internes à la BD (procédures stockées), soit par des procédures au niveau applicatif.

Classe1

a {key} \b

m()

Graphique 7 Attribut dérivé et méthodes

Classe1(#a)

Comtplément : Atribut dérivé stockés

On peut décider (pour des raisons de performance essentiellement) de représenter l'attribut dérivé ou la méthode comme s'il s'agissait d'un attribut simple, mais il sera nécessaire dans ce cas d'ajouter des mécanismes de validation de contraintes dynamiques (avec des triggers par exemple) pour assurer que la valeur stockée évolue en même temps que les attributs sur lesquels le calcul dérivé porte.

Notons qu'introduire un attribut dérivé ou un résultat de méthode dans le modèle relationnel équivaut à introduire de la redondance, ce qui est en général déconseillé, et ce qui doit être dans tous les cas contrôlé.

Objectifs

Savoir faire le passage d'un schéma conceptuel UML à un schéma relationnel pour les cas simples.

a) Transformation des associations 1:N

Méthode

Pour chaque association binaire de type 1:N :

 on ajoute à la relation côté N une clé étrangère vers la relation côté 1.

Classe1	0..1	association	0..N	Classe2
a {key} b				c {key} d

Graphique 8 Association 1:N

Classe1(#a,b)

Classe2(#c,d,a=>Classe1)

Comtplément

Contrainte de cardinalité minimale 1 dans les associations 1:N - p.103

b) Transformation des associations N:M

Méthode

Pour chaque association binaire de type M:N :

 on crée une nouvelle relation,

 composée de clés étrangères vers chaque relation associée,

 et dont la clé primaire est la concaténation de ces clés étrangères.

Graphique 9 Association N:M

Classe1(#a,b)

Classe2(#c,d)

Assoc(#a=>Classe1,#c=>Classe2)

Comtplément

Contrainte de cardinalité minimale 1 dans les associations N:M - p.104

c) Transformation des associations 1:1 (approche simplifiée)

Méthode

La solution la plus simple et la plus générale pour transformer une association 1:1 consiste à traiter cette association 1:1 comme une association 1:N, puis à ajouter une contrainte UNIQUE sur la clé étrangère pour limiter la cardinalité maximale à 1.

Classe1	1..1	association	1..1	Classe2
a {key} b				c {key} d

Graphique 10 Association 1:1

Classe1(#a,b,c=>Classe2) avec c UNIQUE

Classe2(#c,d) ou

Classe1(#a,b)

Classe2(#c,d,a=>Classe1) avec a UNIQUE

Remarque

Il existe toujours deux solutions selon que l'on choisit une ou l'autre relation pour accueillir la clé étrangère. Selon la cardinalité minimale, un des deux choix peut être plus pertinent.

Comtplément

Il est parfois possible de choisir de fusionner les deux classes au sein d'une seule relation plutôt que d'opter pour une clé étrangère.

Comtplément

Transformation des associations 1:1 (approche générale) - p.105

d) Transformation des classes d'association

Méthode : Classe d'association N:M

Graphique 11 Classe assocation (N:M)

Classe1(#a,b)

Classe2(#c,d)

Assoc(#a=>Classe1,#c=>Classe2,e,f)

Comtplément : Classe d'association 1:N

Les attributs de la classe d'association sont ajoutés à la relation issue de la classe côté N.

Comtplément : Classe d'association 1:1

Les attributs de la classe d'association sont ajoutés à la relation qui a été choisie pour recevoir la clé étrangère. Si les deux classes ont été fusionnées en une seule relation, les attributs sont ajoutés à celle-ci.

Description du problème

[30 min]

Un laboratoire souhaite gérer les médicaments qu'il conçoit.

Afin de matérialiser notre base de données, nous obtenons les descriptions suivantes :

- CI1 : Ne jamais prendre après minuit.

- CI2 : Ne jamais mettre en contact avec de l'eau.

- CI3 : Garder à l'abri de la lumière du soleil

Question 1

Réaliser le modèle conceptuel de données en UML du problème.

Question 2

Question 3

En mobilisant les règles adéquates, proposer un modèle logique de données correspondant en relationnel. Le repérage des domaines et des clés est obligatoire.

Question 4

Dessiner des tableaux remplis avec les données fournies en exemple, afin de montrer que le modèle fonctionne selon le besoin exprimé initialement. On pourra mettre le premier mot seulement des descriptions pour gagner du temps.

[30 minutes]

Une usine cherche à modéliser sa production de véhicules et de moteurs :

 Les véhicules sont identifiés par un numéro d'immatriculation alphanumérique et caractérisés par une couleur, dont la dénomination est une chaîne de caractères. Chaque véhicule peut comporter un unique moteur et/ou un nombre quelconque de pneus.

 Chaque moteur est monté sur un et un seul véhicule et est identifié par un numéro de série. Un moteur est caractérisé par une puissance, en chevaux.

 Tout pneu est monté sur un unique véhicule et est identifié par un numéro de série. Sa position est définie localement sur ce véhicule et par rapport à l'essieu : Dn pour les pneus situé sur la droite de l'essieu et Gn pour les pneus situés à gauche ; n représentant le numéro de l'essieu (1 pour celui situé devant, 2 pour la deuxième rangée, etc.). Un pneu est caractérisé par un diamètre et une largeur en pouces.

 Les moteurs, les pneus et les véhicules sont fabriqués sous une marque. Les mêmes marques peuvent fabriquer indifféremment des moteurs, des pneus et/ou des véhicules, et un véhicule d'une certaine marque peut comporter un moteur et/ou des pneus de marque différente.

Question 1

Réaliser le modèle UML de ce problème en faisant apparaître les domaines et les clés.

Question 2

Réaliser le passage au modèle relationnel, en faisant apparaître les clés primaires, candidates et étrangères.

Question 3

Dessiner les tableaux correspondant aux relations du modèle. Instancier au minimum deux véhicules et quatre marques.

Question 4

Donner quatre exemples d'enregistrements qui seront refusés - étant données les données déjà insérées - pour quatre raisons différentes :

 contrainte de clé sur une clé primaire

 contrainte de clé sur une clé candidate

 contrainte d'intégrité référentielle  contrainte de non nullité

Définition : SQL

SQL (pour langage de requêtes structuré) est un langage déclaratif destiné à la manipulation de bases de données au sein des SGBD et plus particulièrement des SGBDR.

SQL : LDD, LCD, LMD, LCT

SQL est un langage déclaratif, il n'est donc pas a proprement parlé un langage de programmation, mais plutôt une interface standard pour accéder aux bases de données.

Il est composé de quatre sous ensembles :

 Le Langage de Définition de Données (LDD, ou en anglais DDL, Data Definition

Language) pour créer et supprimer des objets dans la base de données (tables, contraintes d'intégrité, vues, etc.).

Exemple de commandes : CREATE DROP ALTER

 Le Langage de Contrôle de Données (LCD, ou en anglais DCL, Data Control Language) pour gérer les droits sur les objets de la base (création des utilisateurs et affectation de leurs droits).

Exemple de commandes : GRANT REVOKE

 Le Langage de Manipulation de Données (LMD, ou en anglais DML, Data Manipulation

Language) pour la recherche, l'insertion, la mise à jour et la suppression de données. Le LMD est basé sur les opérateurs relationnels, auxquels sont ajoutés des fonctions de calcul d'agrégats et des instructions pour réaliser les opérations d'insertion, mise à jour et suppression.

Exemple de commandes : INSERT UPDATE DELETE SELECT

 Le Langage de Contrôle de Transaction (LCT, ou en anglais TCL, Transaction Control Language) pour la gestion des transactions (validation ou annulation de modifications de données dans la BD)

Exemple de commandes : COMMIT ROLLBACK

Fondamental: Référence SQL : SQL-99 complete, really

Gulutzan and Pelzer, 1999 [Gulutzan and Pelzer, 1999]

Comtplément : Origine du SQL

Le modèle relationnel a été inventé par E.F. Codd (Directeur de recherche du centre IBM de San José) en 1970, suite à quoi de nombreux langages ont fait leur apparition :

 IBM Sequel (Structured English Query Language) en 1977

 IBM Sequel/2

 IBM System/R

 IBM DB2

Ce sont ces langages qui ont donné naissance au standard SQL, normalisé en 1986 au États-Unis par l'ANSI pour donner SQL/86 (puis au niveau international par l'ISO en 1987).

Comtplément : Versions de SQL

 SQL-86 (ou SQL-87) : Version d'origine

 SQL-89 (ou SQL-1) : Améliorations mineures

 SQL-92 (ou SQL-2) : Extensions fonctionnelles majeures (types de données, opérations relationnelles, instruction LDD, transactions, etc.

 SQL-99 (ou SQL-3) : Introduction du PSM (couche procédurale sous forme de procédure stockées) et du RO

 SQL-2003 : Extensions XML

 SQL-2006 : Améliorations mineures (pour XML notamment)

 SQL-2008 : Améliorations mineures (pour le RO notamment)

Remarque: Version SQL et imtplémentations SGBD

Selon leur niveau d'implémentation de SQL, les SGBD acceptent ou non certaines fonctions.

Certains SGBD ayant entamé certaines implémentations avant leur standardisation définitive, ces implémentations peuvent différer de la norme.

Le LDD est la partie du langage SQL qui permet de créer de façon déclarative les objets composant une BD. Il permet notamment la définition des schémas, des relations, des contraintes d'intégrité, des vues. Rappel : Le code SQL peut être testé avec Db DiscoÓ

2 -

a) Lab I++

Description du problème

[20 min]

Un laboratoire souhaite gérer les médicaments qu'il conçoit.

Afin de matérialiser notre base de données, nous obtenons les descriptions suivantes :

- CI1 : Ne jamais prendre après minuit.

- CI2 : Ne jamais mettre en contact avec de l'eau.

- CI3 : Garder à l'abri de la lumière du soleil

Question 1

Réaliser le modèle conceptuel de données en UML du problème.

Question 2

En mobilisant les règles adéquates, proposer un modèle logique de données correspondant en relationnel. Le repérage des domaines et des clés est obligatoire.

Question 3

Créer une base de données en SQL correspondant au modèle relationnel.

Question 4

Insérer les données fournies en exemple dans la base de données.

b) Création de tables

Ratptpel

Qu'est ce que le SQL ?

Introduction

La création de table est le fondement de la création d'une base de données en SQL.

Définition : Création de table

La création de table est la définition d'un schéma de relation en intension§, par la spécification de tous les attributs le composant avec leurs domaines respectifs.

Syntaxe

CREATE TABLE nom_table (

nom_colonne1 domaine1,

nom_colonne2 domaine2,

nom_colonneN domaineN

);

Exemtple

CREATE TABLE Personne (

Nom VARCHAR(25),

Prenom VARCHAR(25),

Age NUMERIC(3)

);

Atention : Contrainte d'intégrité

La définition des types n'est pas suffisante pour définir un schéma relationnel, il faut lui adjoindre la définition decontraintes d'intégrité, qui permette de poser les notions de clé, d'intégrité référentielle, de restriction de domaines, etc.

c) Domaines de données

Introduction

Un attribut d'une relation est défini pour un certain domaine ou type. Les types de données disponibles en SQL varient d'un SGBD à l'autre, on peut néanmoins citer un certain nombre de types standards que l'on retrouve dans tous les SGBD.

Fondamental: Les types standard

 INTEGER ou INT, SMALLINT

 NUMERIC(X)

 DECIMAL(X,Y) ou NUMERIC(X,Y)

 FLOAT(X), REAL

 CHAR(X)

 VARCHAR(X)

 DATE (AAAA-MM-JJ)

 DATETIME (AAAA-MM-JJ HH:MM:SS)

Les tytpes numériques standard

 Les nombres entiers

INTEGER (ou INT) et SMALLINT, permettent de coder des entiers sur 4 octets (2.147.483.648 à 2.147.483.647) ou 2 octets (-32.768 à 32.767).

 Les nombres entiers

NUMERIC(X) désigne un entier de X chiffres au maximum.  Les nombres décimaux

DECIMAL(X,Y), où X et Y sont optionnels et désignent respectivement le nombre de chiffres maximum pouvant composer le nombre, et le nombre de chiffres après la virgule.

NUMERIC(X,Y) est un synonyme standard.

 Les nombres à virgule flottante

FLOAT(X), avec X définissant la précision (nombre de bits de codage de la mantisse).

REAL est un synonyme standard de FLOAT(24).

Conseil : FLOAT versus DECIMAL

Il est conseillé d'utiliser DECIMAL qui est un nombre exact, plutôt que FLOAT qui est un nombre approximatif, si la précision requise est suffisante. FLOAT sera réservé typiquement à des calculs scientifiques nécessitant un degré de précision supérieur.

Les tytpes chaîne de caractères standard

On distingue principalement les types CHAR(X) et VARCHAR(X), où X est obligatoire et désigne la longueur de la chaîne.

 CHAR définit des chaînes de longueur fixe (complétée à droites par des espaces, si la longueur est inférieure à X) ;

 et VARCHAR des chaînes de longueurs variables.

CHAR et VARCHAR sont généralement limités à 255 caractères. La plupart des SGBD proposent des types, tels que TEXT ou CLOB (Character Long Object), pour représenter des chaînes de caractères longues, jusqu'à 65000 caractères par exemple.

Les tytpes date standard

Les types date dont introduits avec la norme SQL2. On distingue :

 DATE qui représente une date selon un format de type "AAAA-MM-JJ" ;

 et DATETIME qui représente une date plus une heure, dans un format tel que "AAAAMM-JJ HH:MM:SS".

Comtplément : Les autres tytpes

En fonction du SGBD, il peut exister de nombreux autres types. On peut citer par exemple :

 MONEY pour représenter des décimaux associés à une monnaie,

 BOOLEAN pour représenter des booléens,

 BLOB (pour Binary Long Oject) pour représenter des données binaires tels que des documents multimédia (images bitmap, vidéo, etc.)



d) La valeur NULL

L'absence de valeur, représentée par la valeur NULL, est une information fondamentale en SQL, qu'il ne faut pas confondre avec la chaîne espace de caractère où bien la valeur 0. Il ne s'agit pas d'un type à proprement parler, mais d'une valeur possible dans tous les types.

Fondamental

Par défaut en SQLNULLfait partie du domaine, il faut l'exclure explicitement par la clauseNOT NULLaprès la définition de type, si on ne le souhaite pas.Syntaxe

CREATE TABLE nom de table (

CREATE TABLE nom_table (

nom_colonne1 domaine1 NOT NULL, nom_colonne2 domaine2, nom_colonneN domaineN NOT NULL

);

e) Contraintes d'intégrité

Fondamental

 PRIMARY KEY ()

 UNIQUE ()

 FOREIGN KEY () REFERENCES ()

 CHECK ()

Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données de la

BD.

Il existe deux types de contraintes :

 sur une colonne unique,

 ou sur une table lorsque la contrainte porte sur une ou plusieurs colonnes.

Les contraintes sont définies au moment de la création des tables.

Définition : Contraintes d'intégrité sur une colonne

Les contraintes d'intégrité sur une colonne sont :

 PRIMARY KEY : définit l'attribut comme la clé primaire

 UNIQUE : interdit que deux tuples de la relation aient la même valeur pour l'attribut.

 REFERENCES () : contrôle l'intégrité référentielle entre l'attribut et la table et ses colonnes spécifiées

 CHECK () : contrôle la validité de la valeur de l'attribut spécifié dans la condition dans le cadre d'une restriction de domaine

Définition : Contraintes d'intégrité sur une table

Les contraintes d'intégrité sur une table sont :

 PRIMARY KEY () : définit les attributs de la liste comme la clé primaire

 UNIQUE () : interdit que deux tuples de la relation aient les mêmes valeurs pour l'ensemble des attributs de la liste.

 FOREIGN KEY () REFERENCES () : contrôle l'intégrité référentielle entre les attributs de la liste et la table et ses colonnes spécifiées

 CHECK () : contrôle la validité de la valeur des attributs spécifiés dans la condition dans le cadre d'une restriction de domaine Syntaxe

CREATE TABLE nom de table (

CREATE TABLE nom_table (

nom_colonne1 domaine1 ,

nom_colonne2 domaine2 ,

nom_colonneN domaineN ,

);

Exemtple

1
2
3
4
5
6
7
8

CREATE TABLE Personne (

N°SS CHAR(13) PRIMARY KEY,

Nom VARCHAR(25) NOT NULL,

Prenom VARCHAR(25) NOT NULL,

Age INTEGER(3) CHECK (Age BETWEEN 18 AND 65),

Mariage CHAR(13) REFERENCES Personne(N°SS), UNIQUE (Nom, Prenom)

);

Remarque: Clé candidate

La clause UNIQUE NOT NULL sur un attribut ou un groupe d'attributs définit une clé candidate non primaire.

Remarque

Les contraintes sur une colonne et sur une table peuvent être combinées dans la définition d'un même schéma de relation.

Remarque

Une contrainte sur une colonne peut toujours être remplacée par une contrainte sur une table.

f) Exemple de contraintes d'intégrité

Exemtple

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE Personne (

N°SS CHAR(13) PRIMARY KEY,

Nom VARCHAR(25) NOT NULL,

Prenom VARCHAR(25) NOT NULL,

Age INTEGER(3) CHECK (Age BETWEEN 18 AND 65),

Mariage CHAR(13) REFERENCES Personne(N°SS),

Codepostal INTEGER(5),

Pays VARCHAR(50),

UNIQUE (Nom, Prenom),

FOREIGN KEY (Codepostal, Pays) REFERENCES Adresse (CP, Pays)

);

CREATE TABLE Adresse (

CP INTEGER(5) NOT NULL,

Pays VARCHAR(50) NOT NULL,

Initiale CHAR(1) CHECK (Initiale = LEFT(Pays, 1)),

PRIMARY KEY (CP, Pays)

);

Dans la définition de schéma précédente on a posé les contraintes suivantes :

 La clé primaire de Personne est N°SS et la clé primaire de Adresse est (CP, Pays).

 Nom, Prénom ne peuvent pas être null et (Nom, Prénom) est une clé.

 Age doit être compris entre 18 et 65 et Initiale doit être la première lettre de Pays (avec la fonction LEFT qui renvoie la sous chaîne à gauche de la chaîne passée en premier argument, sur le nombre de caractères passés en second argument)

 Mariage est clé étrangère vers Personne et (Codepostal, Pays) est une clé étrangère vers Adresse.

Exemtple : Réécriture avec uniquement des contraintes de table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE Personne (

N°SS CHAR(13) ,

Nom VARCHAR(25) NOT NULL,

Prenom VARCHAR(25) NOT NULL,

Age INTEGER(3) ,

Mariage CHAR(13),

Codepostal INTEGER(5),

Pays VARCHAR(50),

PRIMARY KEY (N°SS),

UNIQUE (Nom, Prenom),

CHECK (Age BETWEEN 18 AND 65),

FOREIGN KEY (Mariage) REFERENCES Personne(N°SS),

FOREIGN KEY (Codepostal, Pays) REFERENCES Adresse (CP, Pays)

);

CREATE TABLE Adresse (

CP INTEGER(5) NOT NULL,

Pays VARCHAR(50) NOT NULL,

Initiale CHAR(1),

PRIMARY KEY (CP, Pays), CHECK (Initiale = LEFT(Pays, 1))

);

Ce schéma est strictement le même que le précédent, simplement les contraintes ont toutes été réécrites comme des contraintes de table.

g) Exercice

Les instructions SQL ci-après ont-elles pu permettre de créer le schéma des trois relations instanciées ci-dessous ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE A (

A1 CHAR(255),

A2 CHAR(255),

A3 CHAR(255),

A4 CHAR(255)

);

CREATE TABLE B (

B1 CHAR(255),

B2 CHAR(255),

B3 CHAR(255),

B4 CHAR(255),

B5 CHAR(255),

B6 CHAR(255),

B7 CHAR(255)

);

CREATE TABLE C (

C1 CHAR(255),

C2 CHAR(255)

);

Oui

Non

Objectifs

Maîtriser les bases du SQL pour entrer, modifier et effacer des données dans les tables.

a) Exercice

Quelle valeur renvoie la dernière instruction SQL de la liste ci-dessous :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE t (

a integer,

b integer,

c integer);

INSERT INTO t

VALUES (0, 0, 0);

INSERT INTO t

VALUES (1, 0, 0);

INSERT INTO t

SELECT * FROM t;

SELECT sum(a) + count(b)

FROM t;

b) Insertion de données

Le langage SQL fournit des instructions pour ajouter des nouveaux tuples à une relation. Il offre ainsi une interface standard pour ajouter des information dans une base de données.

Il existe deux moyens d'ajouter des données, soit par fourniture directe des valeurs des propriétés du tuple à ajouter, soit par sélection des tuples à ajouter dans une autre relation.

Syntaxe : Insertion directe de valeurs

INSERT INTO () VALUES ()

Exemtple : Insertion directe de valeurs

INSERT INTO Virement (Date, Montant, Objet)

VALUES (14-07-1975, 1000, 'Prime de naissance');

Syntaxe : Insertion de valeurs tpar l'intermédiaire d'une sélection

INSERT INTO () SELECT

L'instruction SELECT projetant un nombre de propriétés identiques aux propriétés à valoriser.

Exemtple : Insertion de valeurs tpar l'intermédiaire d'une sélection

INSERT INTO Credit (Date, Montant, Objet)

SELECT Date, Montant, 'Annulation de débit'

FROM Debit

WHERE = 25-12-2001;

Dans cet exemple tous les débits effectués le 25 décembre 2001, sont re-crédités pour le même montant (et à la même date), avec la mention annulation dans l'objet du crédit. Ceci pourrait typiquement réalisé en cas de débits erronés ce jour là.

Remarque

 Les propriétés non valorisées sont affectées à la valeur NULL.

 Il est possible de ne pas spécifier les propriétés à valoriser, dans ce cas, toutes les propriétés de la relation seront considérées, dans leur ordre de définition dans la relation (à n'utiliser que dans les cas les plus simples).

c) Mise à jour de données

Le langage SQL fournit une instruction pour modifier des tuples existants dans une relation.

Syntaxe : Mise à jour directe de valeurs

1	UPDATE
2	SET
3	WHERE

1	UPDATE r
2	SET a=1, b='x'
3	WHERE c=0

Exemtple : Mise à jour directe de valeurs

UPDATE Compte

SET Monnaie='Euro'

WHERE Monnaie='Franc'

Exemtple : Mise à jour tpar calcul sur l'ancienne valeur

UPDATE Compte

SET Total=Total * 6,55957

WHERE Monnaie='Euro'

d) Suppression de données

Le langage SQL fournit une instruction pour supprimer des tuples existants dans une relation.

Syntaxe

DELETE FROM

WHERE

Exemtple : Sutptpression de tous les tutples d'une relation

DELETE FROM FaussesFactures

Exemtple : Sutptpression sélective

DELETE FROM FaussesFactures

WHERE Auteur='Moi'

Le LDD permet de créer les objets composant une BD de façon déclarative. Il permet notamment la définition des schémas des relations, la définition des contraintes d'intégrité, la définition de vues relationnelles.

a) Suppression d'objets

Il est possible de supprimer des objets de la BD, tels que les tables ou les vues.

Syntaxe

DROP

Exemtple

DROP TABLE Personne;

DROP VIEW Employe;

b) Modification de tables

Introduction

L'instruction ALTER TABLE permet de modifier la définition d'une table (colonnes ou contraintes) préalablement créée.

Cette commande absente de SQL-89 est normalisée dans SQL-92

Syntaxe : Ajout de colonne

ALTER TABLE

ADD <définition de colonne>

Syntaxe : Sutptpression de colonne

ALTER TABLE

DROP

Syntaxe : Ajout de contrainte

ALTER TABLE

ADD <définition de contrainte de table>

Remarque: Modification de table sans donnée sans la commande ALTER

Pour modifier une table ne contenant pas encore de donnée, la commande ALTER n'est pas indispensable, l'on peut supprimer la table à modifier (DROP) et la recréer telle qu'on la souhaite. Notons néanmoins que si la table est référencée par des clauses FOREIGN KEY, cette suppression sera plus compliquée, car il faudra également supprimer et recréer les tables référençantes (ce qui ce complique encore si ces dernières contiennent des données).

Remarque: Modification de table avec données sans la commande ALTER

Pour modifier une table contenant des données, la commande ALTER n'est pas indispensable. On peut en effet :

1. Copier les données dans une table temporaire de même schéma que la table à modifier

2. Supprimer et recréer la table à modifier avec le nouveau schéma

3. Copier les données depuis la table temporaire vers la table modifiée

c) Exemple de modifications de tables

Table initiale

Soit une table initiale telle que définie ci-après.

CREATE TABLE Personne ( pk_n NUMERIC(4), nom VARCHAR(50), prenom VARCHAR(50), PRIMARY KEY (pk_n)

);

Modifications

On décide d'apporter les aménagements suivants à la table : on définit "nom" comme UNIQUE et on supprime le champ "prenom".

ALTER TABLE Personne

ADD UNIQUE (nom);

ALTER TABLE Personne

DROP prenom;

Table finale

La table obtenue après modification est identique à la table qui aurait été définie directement telle que ci-après.

CREATE TABLE Personne ( pk_n NUMERIC(4), nom VARCHAR(50),

PRIMARY KEY (pk_n), UNIQUE (nom)

);

[30 minutes]

Soit le schéma relationnel suivant décrivant un système de réservations de places de spectacles :

SPECTACLE (#nospectacle:int, nom:str, durée:int, type:{théâtre|danse|concert})

SALLE (#nosalle:int, nbplaces:int)

REPRESENTATION (#date:date, #nospectacle=>SPECTACLE, #nosalle=>SALLE, prix:decimal)

En faisant les suppositions suivantes :

 On gère un espace de spectacles ayant un ensemble de salles (décrit par la relation SALLE).

 On suppose que pour un jour donné et une salle donnée, il n'y a qu'un seul spectacle représenté.

Question 1

Retro-concevoir le MCD en UML.

Question 2

Proposer des contraintes d'intégrité réalistes pour ce schéma (en français).

Question 3

Proposer une définition du schéma en SQL qui prenne en compte certaines de ces contraintes.

Question 4

Insérer des données réalistes dans votre schéma afin de vérifier son bon fonctionnement.

[30 min]

Soit le modèle relationnel suivant :

Producteur(#raison_sociale:chaîne(25), ville:chaîne(255)) Consommateur(#login:chaîne(10), #email:chaîne(50), nom:chaîne(50),prenom:chaîne(50), ville:chaîne(255))

Produit(#id:entier, description:chaîne(100), produit-par=>Producteur, consommepar-login=>Consommateur, consomme-par-email=>Consommateur)

On ajoute que :

 (nom,prenom,ville) est une clé candidate de Consommateur

 Tous les produits sont produits

 Tous les produits ne sont pas consommés

Question 1

Rétro-concevez le modèle conceptuel sous-jacent à ce modèle relationnel.

Question 2

Établissez le code LDD standard permettant d'implémenter ce modèle en SQL.

Question 3

Insérez les données dans votre base de données correspondant aux assertions suivantes :

 L'entreprise de Compiègne "Pommes Picardes SARL" a produit 4 lots de pommes, et 2 lots de cidre.

 Il existe trois utilisateurs consommateurs dans la base, donc les adresses mails sont : - -

Ce sont des employés de la ville de Compiègne qui habitent cette ville. Leur mail est construit sur le modèle . Leur login est leur prénom.

Question 4

Modifiez les données de votre base de données pour intégrer les assertions suivantes :

 1 lots de pommes a été consommés par Al Un.

 2 lots de pomme ont été consommé par Bob Deux.

 Tous les lots de cidre ont été consommés par Al Un.

Question 5

Charlie Trois n'ayant rien consommé, modifiez votre base de données afin de le supprimer de la base.

Objectifs

Connaître et savoir utiliser les opérateurs relationnels de projection, restriction, produit et jointure.

a) Introduction

La représentation d'information sous forme relationnelle est intéressante car les fondements mathématiques du relationnel, outre qu'ils permettent une modélisation logique simple et puissante, fournissent également un ensemble de concepts pour manipuler formellement l'information ainsi modélisée.

Ainsi une algèbre relationnelle, sous forme d'un ensemble d'opérations formelles, permet d'exprimer des questions, ou requêtes, posées à une représentation relationnelle, sous forme d'expressions algébriques.

L'algèbre relationnelle est composée par les cinq opérateurs de base et les trois opérateurs additionnels suivants :

 Opérateurs de base

- Union

- Différence

- Projection

- Restriction

- Produit cartésien

 Opérateurs additionels

- Intersection

- Jointure

- Division

Fondamental: Algèbre relationnelle et SQL

Les questions formulées en algèbre relationnelle sont la base des questions formulées en SQL pour interroger une base de données relationnelle.

b) Employés et départements

[30 minutes]

Soit les deux relations EMP et DEPT ci-après.

EMP (#ENO, ENOM, PROF, SAL, COMM, DNO=>DEPT(DNO))

DEPT (#DNO, DNOM, DIR=>EMP(ENO), VILLE)

 ENO : numéro d'employé, clé

 ENOM : nom de l'employé

 PROF : profession (directeur n'est pas une profession)

 SAL : salaire

 COMM : commission (un employé peut ne pas avoir de commission)

 DNO : numéro de département auquel appartient l'employé

 DNO : numéro de département, clé

 DNOM : nom du département

 DIR : numéro d'employé du directeur du département

 VILLE : lieu du département (ville)

Écrire en algèbre relationnelle les requêtes permettant d'obtenir les informations suivantes.

Question 1

Lister les employés ayant des revenus supérieurs à 10.000 euros.

Question 2

Trouver le nom et la profession de l'employé numéro 10.

Question 3

Lister les noms des employés qui travaillent à Paris.

Question 4

Trouver le nom du directeur du département Commercial.

Question 5

Trouver les professions des directeurs des départements.

Question 6

Trouver le nom des directeurs de département ayant comme profession Ingénieur.

c) Projection

Définition : Projection

La projection est une opération unaire (c'est à dire portant sur une seule relation). La projection de R1 sur une partie de ses attributs {A1, A2, } produit une relation R2 dont le schéma est restreint aux attributs mentionnés en opérande, comportant les mêmes tuples que R1, et dont les doublons sont éliminés.

Remarque: Élimination des doublons

Après suppression d'une partie des attributs du schéma, la relation peut comporter des doublons. Étant donné que l'on ne pourrait plus identifier ces doublons les uns par rapport aux autres, la seule solution sensée est donc de considérer que deux doublons sont équivalents, et donc de n'en garder qu'un seul dans la relation résultante.

Exemtple

Soit la relation suivante :

Personne (#Nom, Prénom, Age)

Dupont

Pierre

Durand

Jean

Tableau 8 Personne

Soit l'opération suivante :

R = Projection (Personne, Nom, Age)

On obtient alors la relation R composée des tuples suivants :

Dupont	20
Durand	30

Tableau 9 R

d) Restriction

Définition : Restriction

La restriction est une opération unaire (c'est à dire portant sur une seule relation). La restriction de R1, étant donnée une condition C, produit une relation R2 de même schéma que R1 et dont les tuples sont les tuples de R1 vérifiant la condition C.

Exemtple

Soit la relation suivante :

Personne (#Nom, Prénom, Age)

Soit les tuples suivants :

Dupont	Pierre	20
Durand	Jean	30

Tableau 10 Personne

Soit l'opération suivante :

R = Restriction (Personne, Age>25)

On obtient alors la relation R composée de l'unique tuple restant suivant :

Durand

Jean

Tableau 11 R

e) Produit

Définition : Produit cartésien

Le produit cartésien est une opération binaire (c'est à dire portant sur deux relations). Le produit de R1 par R2 (équivalent au produit de R2 par R1) produit une relation R3 ayant pour schéma la juxtaposition de ceux des relations R1 et R2 et pour tuples l'ensemble des combinaisons possibles entre les tuples de R1 et ceux de R2. Synonymes : Produit

Remarque

Le nombre de tuples résultant du produit de R1 par R2 est égal au nombre de tuples de R1 fois le nombre de tuples de R2.

Remarque

Le nombre de colonne du produit de R1 par R2 est égal au nombre de colonne de R1 plus le nombre de colonnes de R2.

Exemtple

Soit les deux relations suivantes :

Homme (#Nom, Prénom, Age)

Voiture (#Type, #Marque)

Soit les tuples suivants pour ces deux relations respectivement :

Dupont	Pierre	20
Durand	Jean	30

Tableau 12 Homme

Tesla	Model X
Citroën	2 CV

Tableau 13 Voiture

Soit l'opération suivante :

R = Produit (Homme, Voiture)

On obtient alors la relation R composée des tuples suivants :

Dupont	Pierre	20	Tesla	Model X
Dupont	Pierre	20	Citroën	2 CV
Durand	Jean	30	Tesla	Model X
Durand	Jean	30	Citroën	2 CV

Tableau 14 R

Remarque

Le produit cartésien est rarement utilisé seul, mais il est à la base de la jointure.

f) Jointure

Définition : Jointure

La jointure est une opération binaire (c'est à dire portant sur deux relations). La jointure de R1 et R2, étant donné une condition C portant sur des attributs de R1 et de R2, de même domaine, produit une relation R3 ayant pour schéma la juxtaposition de ceux des relations R1 et R2 et pour tuples l'ensemble de ceux obtenus par concaténation des tuples de R1 et de R2, et qui vérifient la condition C.

Exemtple

Soit les deux relations suivantes :

Homme (#Nom, Prénom, Age)

Voiture (#Type, #Marque, Propriétaire)

Soit les tuples suivants pour ces deux relations respectivement :

Dupont	Pierre	20
Durand	Jean	30

Tableau 15 Homme

Tesla	Model X	Dupont
Citroën	2 CV	Durand
Citroën	3 CV	Dupont

Tableau 16 Voiture

Soit l'opération suivante :

R = Jointure (Homme, Voiture, =Voiture.Propriétaire)

On obtient alors la relation R composée des tuples suivants :

Dupont	Pierre	20	Tesla	Model X	Dupont
Dupont	Pierre	20	Citroën	3 CV	Dupont
Durand	Jean	30	Citroën	2 CV	Durand

Tableau 17 R

(Dupont, Pierre, 20, Dupont, Georges, 1)

(Dupont, Pierre, 20, Dupont, Jacques, 3)

Fondamental

La jointure est l'opération qui permet de rassembler les informations séparées entre plusieurs tables et référencées par des clés étrangères.

Remarque: Otpération additionnelle

La jointure n'est pas une opération de base, elle peut être réécrite en combinant le produit et la restriction.

g) Exercice

Quelles sont les expressions relationnelles équivalentes à :

Projection ( Jointure (R1, R2, R1.A1=R2.A1), R1.A1, R2.A2)

Jointure ( Projection(R1, A1), Projection(R2, A2), R1.A1=R2.A1)

Projection ( Jointure (R2, R1, R2.A1=R1.A1), R1.A1, R2.A2)

Projection ( Restriction ( Produit(R1, R2), R1.A1=R2.A1), R1.A1, R2.A2)

Produit (R1, R2, R1.A1=R2.A1, R1.A1, R2.A2)

a) Jointure naturelle

Définition : Jointure naturelle

La jointure naturelle entre R1 et R2 est une jointure pour laquelle la condition est l'égalité entre les attributs de même nom de R1 et de R2. Il est donc inutile de spécifier la condition dans une jointure naturelle, elle reste toujours implicite.

Exemtple

Soit deux relations R1 (A, B, C) et R2 (A, D), l'opération Jointure(R1,R2,R1.A=R2.A) est équivalente à l'opération JointureNaturelle(R1,R2).

Remarque

Pour appliquer une jointure naturelle, il faut que les deux relations opérandes aient au moins un attribut ayant le même nom en commun.

b) Jointure externe

Introduction

La jointure est une opération qui entraîne la perte de certains tuples : ceux qui appartiennent à une des deux relations opérandes et qui n'ont pas de correspondance dans l'autre relation. Il est nécessaire dans certains cas de palier cette lacune, et l'on introduit pour cela la notion de jointure externe.

Définition : Jointure externe

La jointure externe entre R1 et R2 est une jointure qui produit une relation R3 à laquelle on ajoute les tuples de R1 et de R2 exclus par la jointure, en complétant avec des valeurs nulles pour les attributs de l'autre relation.

Définition : Jointure externe gauche

La jointure externe gauche entre R1 et R2 est une jointure externe pour laquelle on ajoute seulement les tuples de R1 (c'est à dire la relation de gauche) ayant été exclus.

Synonymes : Jointure gauche

Définition : Jointure externe droite

La jointure externe droite entre R1 et R2 est une jointure externe pour laquelle on ajoute seulement les tuples de R2 (c'est à dire la relation de droite) ayant été exclus.

Bien entendu une jointure externe droite peut être réécrite par une jointure externe gauche (et réciproquement) en substituant les relations opérandes R1 et R2.

Synonymes : Jointure droite

Exemtple

Soit les deux relations suivantes :

Homme (#Nom, Prénom, Age)

Voiture (#Type, #Marque, Propriétaire)

Soit les tuples suivants pour ces deux relations respectivement :

Dupont	Pierre	20
Durand	Jean	30
Martin	Georges	40

Tableau 18 Homme

Tesla	Model X	Dupont
Citroën	2 CV	Durand
Citroën	3 CV	NULL

Tableau 19 Voiture

Soit l'opération suivante :

R = JointureExterne (Homme, Voiture, =Voiture.Propriétaire)

On obtient alors la relation R composée des tuples suivants :

Dupont	Pierre	20	Tesla	Model X	Dupont
Durand	Jean	30	Citroën	2 CV	Durand
Martin	Georges	40	NULL	NULL	NULL
NULL	NULL	NULL	Citroën	3 CV	NULL

Tableau 20 R

Une jointure externe gauche n'aurait renvoyé que les trois premiers tuples et une jointure externe droite n'aurait renvoyée que les deux premiers et le dernier tuple.

c) Opérateurs ensemblistes

Atention

Les opérateurs ensemblistes sont des relations binaires (c'est à dire entre deux relations) portant sur des relationsde même schéma.

Définition : Union

L'union de deux relations R1 et R2 de même schéma produit une relation R3 de même schéma constituée de l'ensemble des tuples appartenant à R1 et/ou à R2.

Définition : Diférence

La différence entre deux relations R1 et R2 de même schéma produit une relation R3 de même schéma constituée de l'ensemble des tuples de R1 n'appartenant pas à R2. Notons que la différence entre R1 et R2 n'est pas égale à la différence entre R2 et R1.

Définition : Intersection

L'intersection de deux relations R1 et R2 de même schéma produit une relation R3 de même schéma constituée de l'ensemble des tuples appartenant à la fois à R1 et à R2. Notons que l'intersection n'est pas une opération de base, car elle est équivalent à deux opérations de différence successives.

Exemtple

Soit les deux relations suivantes :

Homme (#Nom, Prénom, Age)

Femme (#Nom, Prénom, Age)

Soit les tuples suivants pour ces deux relations respectivement :

Dupont		Pierre		20
Durand		Jean		30
	Tableau 21		Homme
Martin		Isabelle		24
Blanc		Hélène		25

Tableau 22 Femme

Soit l'opération suivante :

R = Union (Homme, Femme)

On obtient alors la relation R composée des tuples suivants :

Dupont	Pierre	20
Durand	Jean	30
Martin	Isabelle	24
Blanc	Hélène	25

Tableau 23 R

La différence entre Homme et Femme (respectivement entre Femme et Homme) renvoie la relation Homme (respectivement Femme), car aucun tuple n'est commun aux deux relations. L'intersection entre Homme est Femme est vide, pour la même raison.

Remarque: Union externe

Il est possible de définir une opération d'union externe, qui permet de réaliser l'union de deux relations de schéma différent, en ramenant les relations aux mêmes schémas, et en les complétant avec des valeurs nulles.

d) Division

Définition : Division

La division est une opération binaire (c'est à dire portant sur deux relations). La division de R1 par R2, sachant que R1 et R2 ont au moins un attribut commun (c'est à dire de même nom et de même domaine), produit une relation R3 qui comporte les attributs appartenant à R1 mais n'appartenant pas à R2 et l'ensemble des tuples qui concaténés à ceux de R2 donnent toujours un tuple de R1.

Exemtple

Soit les deux relations suivantes :

Pratique (#Homme, #Métier, Salaire)

Métier (#Metier)

Soit les tuples suivants pour ces deux relations respectivement :

Dupont	Ingénieur	35
Durand	Professeur	40
Dupont	Ingénieur	45
Martin	Ingénieur	50

Tableau 24 Pratique

Ingénieur

Professeur

Tableau 25 Métier

Soit l'opération suivante :

R = Division (Homme, Métier)

On obtient alors la relation R composée des tuples suivants :

Dupont

Tableau 26 R

Méthode : Rétponse aux questions : Pour tous les

La division permet de répondre aux questions du type : "Donnez toutes les personnes qui pratiquent tous les métiers de la relation métier".

Remarque: Otpération additionnelle

La division n'est pas une opération de base, elle peut être réécrite en combinant le produit, la restriction et la différence.

e) Proposition de notations

Introduction

Il existe plusieurs syntaxes pour écrire des opérations d'algèbre relationnelle, certaines inspirées de l'algèbre classiques, d'autres reposant sur des notations graphiques. Nous proposons une notation fonctionnelle qui a le mérite d'être facile à écrire et d'être lisible. Si cette notation peut parfois perdre en simplicité, lorsqu'elle concerne un nombre élevé d'opérateurs, il est possible de décomposer une opération compliquée afin de l'alléger.

Syntaxe

1
2
3
4
5
6
7
8
9
10
11
12

R = Union (R1, R2)

R = Différence (R1, R2)

R = Intersection (R1, R2)

R = Projection (R1, A1, A2, )

R = Restriction (R1, condition)

R = Produit (R1, R2)

R = Jointure (R1, R2, condition)

R = JointureNaturelle (R1, R2)

R = JointureExterne (R1, R2, condition)

R = JointureGauche (R1, R2, condition)

R = JointureDroite (R1, R2, condition)

R = Division (R1, R2)

Exemtple : Notation synthétique

R = Projection(Restriction(R1, A1=1 AND A2=2), A3)

Exemtple : Notation décomtposée

R' = Restriction(R1, A1=1 AND A2=2)

R = Projection (R', A3)

f) Exercice

Soit les deux relations R1 et R2 suivantes, définies en extension :

A		B
1	A
2	B
3	C

Tableau 27 R1

Tableau 28 R2

Combien de tuples renvoie l'opération relationnelle suivante ?

R3 = JointureNaturelle (Intersection (Projection(R1,A), R2), R2)

g) Opérateurs de base et additionnels

Réécrivez les opérateurs additionnels suivants, à partir d'opérateurs de base :

Question 1

Réécrivez Intersection à partir de Différence

Question 2

Réécrivez Jointure à partir de Produit et Restriction

[30 minutes]

On considère les deux relations suivantes :

FILMS (titre, pays, année, réalisateur, durée)

ACTEURS (titre, acteur)

où les attributs ont les significations et les types suivants :  titre : titre d'un film (chaîne 50 caractères)

 pays : pays d'où un film est originaire (chaîne 10 caractères)

 annee : année de sortie du film (entier 4 chiffres)

 realisateur : nom du réalisateur du film (chaîne 20 caractères)

 duree : durée du film en minutes (entier 3 chiffres)

 acteur : nom d'acteur (chaîne 20 caractères)

La relation FILMS donne pour chaque film, identifié par son titre, le pays, l'année de sortie, réalisateur et la durée.

La relation ACTEURS donne pour chaque film l'ensemble des principaux acteurs.

À l'aide de l'algèbre relationnelle, exprimer les requêtes suivantes :

Question 1

Lister les films français (titre, année, réalisateur).

Question 2

Donnez les années de sortie des films dans lesquels l'acteur Jean GABIN a joué.

Question 3

Trouver les acteurs qui ont tourné avec François Truffaut comme réalisateur.

Question 4

Trouver tous les acteurs qui ont été partenaires de l'actrice Catherine Deneuve.

Question 5

Lister les films dans lesquels le réalisateur est aussi acteur.

Question 6

Lister les réalisateurs n'ayant joué comme acteurs que dans des films qu'ils ne réalisaient pas eux-mêmes.

Question 7

Lister les réalisateurs ayant joué comme acteurs dans des films qu'ils ne réalisaient pas eux-mêmes.

Question 8

Donnez les acteurs qui jouent dans tous les films de François TRUFFAUT.

Exercice 1

Quelles sont les opérations relationnelles, qui appliquées sur les relations instanciées cidessous, renvoient un ensemble non nul de tuples ?

Num	Nom	Famille
1	Ours	Mammifère
2	Truite	Poisson
3	Homme	Mammifère
4	Martinpêcheur	Oiseau

Tableau 29 Animal

Num	Nom
1	Forêt
2	Montagne
3	Ciel
4	Rivière
5	Mer

Tableau 30 Environnement

Animal	Environnement
1	1
1	2
1	4
2	4
4	3

Tableau 31 Habiter

Mangeur	Mangé	Fréquence
1	2	Souvent
1	3	Rarement
1	4	Rarement
4	2	Souvent
3	1	Rarement
3	2	Souvent

Tableau 32 Manger

	Restriction( Projection (Animal, Nom, Famille), Famille='Mammifère')
	Restriction( Jointure( Jointure (Animal, Habiter, =Habiter.Animal), Environnement, =Habiter.Environnement), ='3')
	Restriction( JointureExterneGauche( Animal, Habiter, =Habiter.Animal), ='Homme')
	Jointure( Animal, Manger, =Manger.Mangeur AND é)

Exercice 2

Soit le schéma relationnel :

R1(X, Y)

R2(X, Y)

Quelles sont les opérations relationnelles équivalentes à l'opération :

Projection( JointureNaturelle(R1,R2), R1.X)

JointureNaturelle(Projection(R1, X), Projection(R2, X))

Projection( Selection (Produit(R1, R2), R1.X=R2.X AND R1.Y=R2.Y), R1.X)

Projection( Union( R1, R2), R1.X)

Projection( JointureExterne ( R1, R2, R1.X=R2.X AND R1.Y=R2.Y), R1.X)

Projection( Jointure ( R1, R2, R1.X=R2.X AND R1.Y=R2.Y), R1.X)

Exercice 3

Soit la relation instanciée suivante :

A	B	C
1	1
1		1
	1	1

Tableau 33 Relation R1

Quelles relations sont retournées par l'opération relationnelle suivante :

R2 = JointureNaturelle(R1, R1)

1	1
1		1
	1	1

Tableau 34 R2a

1	1
1		1
	1	1
1	1
1		1
	1	1

Tableau 35 R2b

1	1	1

Tableau 36 R2c

1	1		1	1
1		1	1		1
	1	1		1	1

Tableau 37 R2d

	R2a
	R2b
	R2c
	R2d
	Une relation vide (aucun tuple)

[45 minutes]

Soit le schéma relationnel suivant :

IMMEUBLE (#ADI, NBETAGES, DATEC, PROP)

APPIM (#ADI, #NAPR, OCCUP, TYPE, SUPER, ETAGE)

PERSONNE (#NOM, AGE, PROF, ADR, NAPR)

ÉCOLE (#NOMEC, ADEC, DIR)

CLASSE (#NOMEC, #NCL, MAITRE)

ENFANT (#NOMP, #PRENOM, AN, NOMEC, NCL)

Avec la signification suivante :

 Relation IMMEUBLE

ADI : adresse d'immeuble, clé ; on fait l'hypothèse pour simplifier, que l'adresse identifie de manière unique un immeuble NBETAGES : nombre d'étages d'un immeuble

DATEC : date de construction (année)

PROP : nom du propriétaire de l'immeuble qui est une personne

 Relation APPIM (Appartement) ADI : adresse d'immeuble

NAPR : numéro d'appartement

OCCUP : occupant de l'appartement (nom de la personne ayant signé le contrat de location, éventuellement aucun)

TYPE : type de l'appartement (Studio, F2, )

SUPER : superficie de l'appartement

ETAGE : étage où se situe l'appartement

 Relation PERSONNE

NOM : nom de personne, clé ; on fait l'hypothèse pour simplifier, que ce nom est unique sur l'ensemble des personnes que l'on considère dans la base

AGE : âge de la personne

PROF : profession de la personne

ADR : adresse de la résidence d'une personne, il s'agit d'un immeuble NAPR : numéro d'appartement

 Relation ÉCOLE

NOMEC : nom d'une école, clé

ADEC : adresse d'une école

DIR : nom du directeur

 Relation CLASSE

NOMEC : nom d'une école

NCL : nom de la classe, e.g., CP1, CE2, CE3, etc MAITRE : nom de l'instituteur

 Relation ENFANT

NOMP : nom de la personne responsable de l'enfant, clé e.g., père, mère etc

PRENOM : prénom de l'enfant

AN : année de naissance

NOMEC : nom d'une école

NCL : nom de la classe

La relation IMMEUBLE décrit un ensemble d'immeubles. Chaque immeuble a un propriétaire. La relation APPIM décrit pour chaque immeuble l'ensemble des appartements qui le compose (il y a au mimimum un appartement par immeuble). Chaque appartement peut héberger plusieurs personnes mais il y en a une qui est responsable (par exemple la personne qui a signé le contrat de location) et qui est désignée par l'attribut OCCUP. Si l'appartement est inoccupé, il prend la valeur NULL. La relation PERSONNE décrit un ensemble de personnes. ADR et NAPR représentent l'adresse où réside une personne. Une personne peut avoir plusieurs enfants décrits par la relation ENFANT. Pour simplifier, on ne considère que les enfants allant à l'école primaire. Les écoles et les classes sont décrites dans les relations ÉCOLE et CLASSE, chaque école est composée au minimum d'une classe et chaque classe est au moins fréquentée par un enfant.

Question 1

Donner l'adresse des immeubles ayant plus de 10 étages et construits avant 1970.

Question 2

Donner les noms des personnes qui habitent dans un immeuble dont ils sont propriétaires.

Question 3

Donner les noms des personnes qui ne sont pas propriétaires.

Question 4

Donner les adresses des immeubles possédés par des informaticiens dont l'âge est inférieur à 40 ans .

Question 5

Donner la liste des occupants (nom, âge, profession) des immeubles possédés par DUPONT.

Question 6

Donner le nom et la profession des propriétaires d'immeubles dans lesquels il y a des appartements vides.

Question 7

Donner les noms des maîtres qui habitent dans le même immeuble (à la même adresse) qu'au moins un de leurs élèves (on suppose que les enfants vivent sous le même toit que leur parents).

Question 8

Donner l'adresse de l'immeuble, la date de construction, le type d'appartement et l'étage où habitent chacun des maîtres des enfants de DUPONT.

Pourquoi est-il fondamental mais difficile de parvenir à un MCD correct ?

Énoncer quelques actions à mener pour réaliser une spécification générale de l'existant et des besoins ?

Qu'est ce qui différencie fondamentalement un MCD d'un MLD ?

Quels sont les principaux éléments du diagramme de classes UML ?

Quelles sont les différences et points communs entre la diagramme de classe UML et le modèle E-A étendu ?

Qu'est ce qu'un domaine ?

Comment identifie-t-on un attribut d'une relation ?

Comment identifie-t-on un enregistrement d'une relation ?

Quand doit-on ajouter des clés artificielles ?

Quelle problème pose la redondance et comment le résoudre ?

A quoi sert le LDD ?

Quel rapport y-a-t il entre le LDD et le concept de relation ?

Pourquoi la jointure est-elle un opérateur essentiel ?

Quels sont les opérateurs algébriques de base ? Quels sont les autres opérateurs ? Qu'est ce qui les différencie ?

Quels sont les opérateurs ensemblistes ? Qu'est ce qui les caractérise ?

Pourquoi la jointure est-elle un opérateur essentiel ?

Qu'est ce qui différencie une jointure externe d'une jointure classique ?

Clé artificielle

Une clé artificielle est un attribut ajouté à une relation afin d'identifier ses enregistrements. On fait appel à une clé artificielle lorsque la relation ne comporte aucune clé naturelle ou que ses clés naturelles sont jugées inadaptées à l'identification au sein de la base de données.

Extension

L'extension est l'explicitation d'un domaine par l'énonciation exhaustive de l'ensemble des objets du domaine.

Elle s'oppose à l'instension qui est une description abstraite des caractéristiques du domaine.

 Exemple : {bleu, rouge, vert}

 Contre-exemple : Le domaine des couleurs

Intension

L'intension est l'explicitation d'un domaine par la description de ses caractéristiques (en vue de sa compréhension abstraite, générale).

Elle s'oppose à l'extension qui est l'énonciation exhaustive de l'ensemble des objets du domaine.

 Exemple : Le domaine des couleurs

 Contre-exemple : {bleu, rouge, vert}

- ANSI American National Standards Institute

- BD Base de Données

- E-A Entité-Association

- ISO International Standardization Organization

- LCD Langage de Contrôle de Données

- LDD Langage de Définition de Données

- LMD Langage de Manipulation de Données

- OMG Object Management Group

- PSM Persistent Stored Modules

- RO Relationnel-Objet

- SGBD Système de Gestion de Bases de Données

- SGBDR Système de Gestion de Bases de Données Relationnelles

- SQL Structured Query Language

- UML Unified Modeling Language

- XML eXtensible Markup Language

[]

3 -

[Arribe, 2014] A_RRIBE T_HIBAUT. 2014. Conception des chaînes éditoriales : documentariser l'activité et structurer le graphe documentaire pour améliorer la maîtrise de la rééditorialisation. Université de Technologie de Compiègne, Mémoire de Doctorat. .

[Codd70] C_ODD EF, A relational model for large shared data banks, Communications de l'ACM, juin 1970.

[Delmal01] D_ELMAL P_IERRE. SQL2 SQL3, applications à Oracle. De Boeck Université, 2001.

[Gardarin99] G_ARDARIN G_EORGES. Bases de données : objet et relationnel. Eyrolles, 1999.

[Gulutzan and Pelzer, 1999] GULUTZAN PETER, PELZER TRUDY. 1999. SQL-99 complete, really. CMP books.

[Muller98] M_ULLER P.A., Modélisation objet avec UML, Eyrolles, 1998.

[Roques04] R_OQUES P_ASCAL, V_ALLÉE F_RANCK. UML 2 en action : De l'analyse des besoins à la conception J2EE. ISBN 2212-11462-1 (3ème édition). Paris : Eyrolles, 2004. 385 p. architecte logiciel.

[Rothenberg et al., 1989] ROTHENBERG JEFF, WIDMAN LAWRENCE E, LOPARO KENNETH A, NIELSEN NORMAN R. 1989. The nature of modeling. Rand. vol.3027.

[Soutou02] S_OUTOU C_HRISTIAN. De UML à SQL : Conception de bases de données. Eyrolles, 2002.

[Tardieu83] T_ARDIEU H., R_OCHFELD A., C_OLLETI R., Méthode MERISE Tome 1 : Principes et outils, Les Editions d'Organisation, 1983.

[(1)] M_ORLON J_ÉRÔME, UML en 5 étapes, , 2004.

[(2)] B_ORDERIE X_AVIER, Cinq petits conseils pour un schéma UML efficace, , 2004.

[w_objecteering]Objecteering software. . [2002-septembre].

[]UML en Français, , consulté en 2002.

1:1p.54, Error: Reference source not found

1:Np.53, Error: Reference source not found

Algèbrep.48, 71, 71, 72, 73, 74, 75, 76,

76, 76, 78, 79, 80

ALTER TABLEp.67, 68

Analyse ..p.9, 11, 12, 13

Application ..p.6

Associationp.Error: Reference source not found, 31, Error: Reference source not found, 32, 44, Error: Reference source not found, 53, Error: Reference source not found, 53, Error: Reference source not found, 54, Error: Reference source not found, 54

Attributp.28, 32, 40, 41, 41, 42, 44, 51,

52, 54

Base de données ..p.17

BD ..p.4Cardinalité ..p.32, Error: Reference source not found, Error: Reference source not found

Cartésienp.74

CHECKp.62, 63

Classep.27, 50, 54

Clé .p.41, 42, 42, 44

Clé artificielle .p.42

Clé candidate .p.42

Clé primaire ..p.42, 42

Clé signifiantep.42

Coddp.39

Compositionp.Error: Reference source not found, Error: Reference source not found

Conception ..p.9, 11

Conceptuelp.9, 11, 13, 15, 26, 27, 50, 53

CREATE TABLE ..p.59

Création ..p.58

Déclaratifp.58

DELETE ..p.65, 66

Diagrammep.26

Différence ..p.78

Divisionp.79

Domaine .p.39, 48, 48, 60, 61

DROPp.67

E-Ap.32

Enregistrementp.40, 41

Externe ..p.15, 76

FOREIGN KEY ..p.62, 63

INSERT ..p.65, 65

Instance .p.15

Internep.15

Intersectionp.78

Jointure .p.75, 76, 76

Langage ..p.8, 65

LDD .p.8, 58, 67

Lien ..p.44

LMD ..p.8, 65

Logique .p.38, 38, 39, 39, 41, 50, 53

Manipulation ..p.71

Méthode ..p.30

Modèle.p.13, 23, 38, 39, 39, 41, 45, 46

Modélisation ..p.42

Modificationp.67

N:M ..p.53

N :Mp.Error: Reference source not found

Naturelle .p.76

NOT NULL ..p.62, 63

Null ..p.61

OMG .p.27

Opération ..p.30, 48

Passage .p.50, 53PostgreSQLp.17

PRIMARY KEYp.62, 63

Produit ..p.48, 48, 74

Projectionp.72

Propriétép.28, 32

Référencep.44

REFERENCESp.62, 63

Relationp.40, 41, 41, 42, 44, 44, 45, 48Relationnel ..p.5, Error: Reference source not found, 38, 38, 39, 39, 41, 45, 46, 48, 48, 50, 50, 51, Error: Reference source not found, 52, 53, 53, Error: Reference source not found, 53, Error:

Reference source not found, 54, Error:

Reference source not found, 54, 71, 71,

76, 80

Relationnel-objet .p.38, 39

Requête ..p.65

Restriction ..p.73

Schéma .p.15, 45, 46

SGBD .p.5

Spécificationsp.12

SQL .p.8, 58, 65, 67

Suppression ..p.67

Table ..p.58, 67

Tuplep.40

Typep.60, 61

UML .p.13, 26, 27, 27, 28, Error: Reference source not found, 30, 31, Error:

Reference source not found, 32, 50, 50, 51, Error: Reference source not found, 52,

53, 53, Error: Reference source not found,

53, Error: Reference source not found, 54,

Error: Reference source not found, 54

Union ..p.78

UNIQUE .p.62, 63

UPDATE .p.65, 66

-Définition du mouvement NoSQL

Définition

Le NoSQL regroupe de nombreuses bases de données, récentes pour laplupart, qui se caractérisent par une logique de représentation de données non relationnelle et qui n'offrent donc pas une interface de requêtes en SQL.

NoSQL signifieNot Only SQLet non pasNo SQL, il s'agit de compléments aux SGBDR pour des besoins spécifiques et non de solutions de remplacement.Exemtple

 BD orientée clé-valeur

 BD orientée graphe

 BD orientée colonne

 BD orientée document

Comtplément

-Transformation des méthodes par des vues

Méthode

Lorsqu'une méthode est spécifiée sur un diagramme UML pour une classe C, si cette méthode est une fonction relationnelle (elle renvoie une unique valeur et elle peut être résolue par une requête SQL), alors on crée une vue qui reprend les attributs de la classe C et ajoute des colonnes calculées pour les méthodes.

Remarque: Atributs dérivés

Les attributs dérivés étant apparentés à des méthodes, ils peuvent également être gérés par des vues.

4 -

5 -

-Composition

Définition : Association de comtposition

On appelle composition une association particulière qui possède les propriétés suivantes :

 La composition associe une classe composite et des classes parties, tel que tout objet partie appartient à un et un seul objet composite. C'est donc une association 1:N (voire 1:1).

 La composition n'est pas partageable, donc un objet partie ne peut appartenir qu'à un seul objet composite à la fois.

 Le cycle de vie des objets parties est lié à celui de l'objet composite, donc un objet partie disparaît quand l'objet composite auquel il est associé disparaît.

Remarque

 La composition est une association particulière (binaire de cardinalité contrainte).

 La composition n'est pas symétrique, une classe joue le rôle de conteneur pour les classes liées, elle prend donc un rôle particulier a priori.

 La composition est une agrégation avec des contraintes supplémentaires (non partageabilité et cycle de vie lié).

Image 14 Notation de la composition en UML

Atention : Composition et cardinalité

La cardinalité côté composite est toujours de exactement 1.

Côté partie la cardinalité est libre, elle peut être 0..1, 1, * ou bien 1..*.

Image 15 Un livre

On voit bien ici qu'un chapitre n'a de sens que faisant partie d'un livre, qu'il ne peut exister dans deux livres différents et que si le livre n'existe plus, les chapitres le composant non plus.

Remarque: Comtposition et entités faibles

La composition permet d'exprimer une association analogue à celle qui relie une entité faible à une entité identifiante en modélisation E-A. L'entité de type faible correspond à un objet partie et l'entité identifiante à un objet composite.

Conseil : Comtposition et atribut multivalué

 Une composition avec une classe partie dotée d'un seul attribut peut s'écrire avec un attribut multivalué.

 Un attribut composé et multivalué peut s'écrire avec une composition.

Ratptpel : Voir aussi

 Attributs

 Agrégation - p.100

-Agrégation

Définition : Association d'agrégation

L'agrégation est une association particulière utilisée pour préciser une relation tout/partie (ou ensemble/élément), on parle d'association méréologique.

Elle possède la propriété suivante : L'agrégation associe une classe agrégat et des classes parties, tel que tout objet partie appartient à au moins un objet agrégat.

Remarque

 L'agrégation est une association particulière (binaire de cardinalité libre).

 L'agrégation n'est pas symétrique. Syntaxe

Image 16 Notation de l'agrégation en UML

La cardinalité, peut être exprimée librement, en particulier les instances de la classe Élément peuvent être associées à plusieurs instances de la classe Ensemble, et même de plusieurs classes.

Atention

L'agrégation garde toutes les propriétés d'une association classique (cardinalité, cycle de vie, etc.), elle ajoute simplement une terminologie un plus précise via la notion de tout/partie.

-Explicitation des associations

Syntaxe : Sens de lecture

Il est possible d'ajouter le sens de lecture du verbe caractérisant l'association sur un diagramme de classe UML, afin d'en faciliter la lecture. On ajoute pour cela un signe < ou > (ou un triangle noir) à côté du nom de l'association

Syntaxe : Rôle

Il est possible de préciser le rôle joué par une ou plusieurs des classes composant une association afin d'en faciliter la compréhension. On ajoute pour cela ce rôle à côté de la classe concernée (parfois dans un petit encadré collé au trait de l'association.

Exemtple

Image 17 Rôle et sens de lecture sur une association

Définition : Association réflexive

Une association réflexive est une association qui associe une classe avec elle-même.

L'explicitation des associations est particulièrement utile dans le cas des associations réflexives.

-Associations ternaires

Image 18 Notation d'une association ternaire

Conseil : Ne tpas abuser des associations ternaires

Il est toujours possible de réécrire une association ternaire avec trois associations binaires, en transformant l'association en classe.

Conseil : Pas de degré sutpérieur à 3

En pratique on n'utilise jamais en UML d'association de degré supérieur à 3.

-Transformation des agrégations

Ratptpel : Agrégation

Les associations de type agrégation se traitent de la même façon que les associations classiques.

Graphique 12 Agrégation 1:N

Classe1(#a,b)

Classe2(#c,d,a=>Classe1)

Graphique 13 Agrégation N:M

Classe1(#a,b)

Classe2(#c,d)

Assoc(#a=>Classe1,#c=>Classe2)

-Transformation des compositions

Méthode

Une composition

 est transformée comme une association 1:N,

 puis on ajoute à la clé de la classe partie (dite clé locale) la clé étrangère vers la classe composite pour construire une clé primaire composée.

Graphique 14 Composition

Classe1(#a,b)

Classe2(#c,#a=>Classe1,d)

Remarque: Clé locale

Pour identifier une classe partie dans une composition, on utilise une clé locale concaténée à la clé étrangère vers la classe composite, afin d'exprimer la dépendance entre les deux classes.

Si une clé naturelle globale permet d'identifier de façon unique une partie indépendamment du tout, on préférera la conserver comme clé candidate plutôt que de la prendre pour clé primaire.

Si on la choisit comme clé primaire cela revient à avoir transformé la composition en agrégation, en redonnant une vie propre aux objets composants.

Comtplément : Comtposition et entités faibles en E-A

Une composition est transformée selon les mêmes principes qu'une entité faible en E-A.

Comtplément : Atributs multivalués et comtposés

La transformation d'un attribut composé multivalué donne un résultat équivalent à la transformation d'une composition.

Classe1

a {key} b [0..N] -b1

-b2

Graphique 15 Composition et attribut composé multivalué

Classe1(#a)

RB(#b_b1,#b_b2,#a=>Classe1)

La transformation d'une composition avec un seul attribut pour la classe composante donne un résultat équivalent à la transformation d'un attribut multivalué.

Graphique 16 Composition et attribut multivalué

Classe1(#a)

RB(#b,#a=>Classe1)

Ratptpel : Voir aussi

Transformation des attributs

-Contrainte de cardinalité minimale 1 dans les associations 1:N

Ratptpel

Transformation des associations 1:N

Méthode

Classe1	1	association	1..N	Classe2
a {key} b				c {key} d

Graphique 17 Association 1:N

 Si la cardinalité est exactement 1 (1..1) côté 1, alors on ajoutera une contrainte de non nullité sur la clé étrangère,

 si la cardinalité est au moins 1 (1..N) côté N, on ajoutera une contrainte d'existence de tuples référençant pour chaque tuple de la relation référencée.

Classe1(#a,b)

Classe2(#c,d,a=>Classe1)

Contraintes : a NOT NULL et PROJECTION(Classe1,a) PROJECTION(Classe2,a)⊆

Graphique 18 Classe d'association (1:N)

Classe1(#a,b)

Classe2(#c,d,a=>Classe1, e, f) avec e KEY

Contraintes : a NOT NULL et PROJECTION(Classe1,a) PROJECTION(Classe2,a)⊆

Comtplément

Projection

-Contrainte de cardinalité minimale 1 dans les associations N:M

Ratptpel

Transformation des associations N:M

Méthode

 Si la cardinalité est exactement au moins 1 (1..N) d'un côté et/ou de l'autre, alors des contraintes d'existence simultanée de tuple devront être ajoutée.

 Ce n'est pas nécessaire si la cardinalité est 0..N.

Graphique 19 Association N:M

Classe1(#a,b)

Classe2(#c,d)

Assoc(#a=>Classe1,#c=>Classe2)

Contraintes : PROJ(Classe1,a) PROJ(Assoc,a) et PROJ(Classe2,c) PROJ(Assoc,c)⊆ ⊆

Graphique 20 Classe assocation (N:M)

Classe1(#a,b)

Classe2(#c,d)

Cl-Assoc(#a=>Classe1,#c=>Classe2,#e,f)

Contraintes : PROJ(Classe1,a) PROJ(Assoc,a) et PROJ(Classe2,c) PROJ(Assoc,c)⊆ ⊆

Comtplément

Projection

-Transformation des associations 1:1 (approche générale)

Il existe deux solutions pour transformer une association 1:1 :

 Avec deux relations : on traite l'association 1:1 comme une association 1:N, puis l'on ajoute une contrainte UNIQUE sur la clé étrangère pour limiter la cardinalité maximale à 1 ;

 Avec une seule relation : on fusionne les deux classes en une seule relation.

Classe1	1..1	association	1..1	Classe2
a {key} b				c {key} d

Graphique 21 Association 1:1

Méthode : Avec deux relations (clé étrangère)

 Une des deux relations est choisie pour porter la clé étrangère ;

 on ajoute les contraintes : UNIQUE ou KEY (clé candidate) sur la clé étrangère ; et si nécessaire une contrainte imposant l'instanciation simultanée des deux relations.

Classe1(#a,b,c=>Classe2) avec c UNIQUE ou KEY

Classe2(#c,d)

Contrainte (éventuellement) : PROJ(Classe1,c)=PROJ(Classe2,c) ou

Classe1(#a,b)

Classe2(#c,d,a=>Classe1) avec a UNIQUE ou KEY

Contrainte (éventuellement) : PROJ(Classe1,a)=PROJ(Classe2,a)

Méthode : Avec une relation (fusion)

 On créé une seule relation contenant l'ensemble des attributs des deux classes ;  on choisit une clé parmi les clés candidates.

Classe12(#a,b,c,d) avec c UNIQUE ou KEY ou

Classe21(#c,d,a,b) avec a UNIQUE ou KEY

Remarque: Fusion des relations dans le cas de la traduction de l'association 1:1

Ce choix entre les deux méthodes sera conduit par une appréciation du rapport entre :  La complexité introduite par le fait d'avoir deux relations là ou une suffit

 La pertinence de la séparation des deux relations d'un point de vue sémantique

 Les pertes de performance dues à l'éclatement des relations

 Les pertes de performance dues au fait d'avoir une grande relation

 Les questions de sécurité et de sûreté factorisées ou non au niveau des deux relations

