DocumentBD

Système d’Information et Base de Données 1 Système d’Information et Base de Données

Système d’Information et

Base de Données

EMSI

2009-2010


Chapitre 1:

Introduction aux base

de données

Système d’Information et Base de Données 3

Sommaire du chapitre 1

Définitions

Fonctions d’un SGBD

Utilisateurs d’une BD

Modèles de données

Chronologie des SGBD

Modèle hiérarchique

Modèle réseau

Architecture des SGBD

Architecture à trois niveaux


Définitions

Une base de données (BD) est un ensemble

structuré de données enregistrées dans un

ordinateur et accessibles de façon sélective

par plusieurs utilisateurs.

Exemples :

• BD des patients d’un hôpital.

• BD des personnels d’une société.

• BD des étudiants d’une faculté.

• BD des clients d’une banque.


Définitions

Un système de gestion de base de

données (SGBD) est un logiciel qui permet

d'interagir avec une base de données.

Exemples :

• MS-SQL SERVER

• Oracle

• DB2

• Informix

• MySQL



Décrire le schéma d’une BD.

Créer une BD.

Manipuler une BD par :

• l’ajout des données

• la modification des données

• la suppression des données

Consulter une BD :

• sélection

• tri

• calcul

• agrégat


Fonctions d’un SGBD Définir des contraintes d'intégrité sur une BD :

• contraintes de domaines

• contraintes d'existence

Définir des protections d'accès :

• attribution de mots de passe

• autorisations d’accès

Résoudre les problèmes d'accès multiples aux données :

• blocages, interblocages.

Prévoir des procédures de reprise en cas d'incident : sauvegardes des données, journaux, etc.



Base de

données

Utilisateurs Logiciel Support

physique


Utilisateurs d’une BD

Une BD est accessible par plusieurs utilisateurs, chacun avec des objectifs précis et selon des interfaces fournies par le SGBD.

Il y a trois types d’utilisateurs pour une BD :

• l’administrateur de la BD.

• l’utilisateur final de la BD.

• les programmeurs d’applications.

L’administrateur de la BD se charge de l’implémentation de celle-ci, assigne ou supprime des niveaux d’accès aux autres utilisateurs de la BD.


Utilisateurs d’une BD L’utilisateur final de la BD est la personne qui

interagisse directement avec le système. Il peut être un client, un opérateur, etc.

Les programmeurs d’applications accèdent à une BD via des programmes écrits dans des langages de haut niveau tels que Java/J2EE, C++ ou Php ....

Exemples de programmes d’applications :

• Gestion des salaires.

• Gestion de la facturation.

• Gestion de l’inventaire.

• Gestion des Rdv.


Exercice corrigé N° 1 Indiquer quel type d’utilisateur doit exécuter

les fonctions suivantes pour un système de paie d’une grande entreprise :

a)Écrire un programme d’application pour générer et imprimer les chèques.

b)Changer dans la base de données l’adresse d’un employé qui a déménagé.

c)Créer un nouveau compte d’utilisateur pour un employé nouvellement embauché.


Solution de l’exercice N° 1

a)Un programmeur d’application.

b)Un utilisateur final de la BD.

c)L’administrateur de la BD.


Modèles de données Un modèle de données est un moyen pour

décrire la forme logique des données.

Exemples :

• Fichiers Plats : accès séquentiel.

• Modèle hiérarchique : modèle en arborescence.

• Modèle réseau : extension du modèle hiérarchique (liens arborescences).

• Modèle relationnel : modèle en tables ou relations.

• Modèle objet : modèle fondé sur les objets.


Chronologie des SGBD

Hiérarchique

mi-1960

Flat

Files

Network

fin-1960

Relationnel

1974-77

Orienté

objet

1990


Modèle hiérarchique

Un SGBD hiérarchique présente les

données sous forme d’un arbre (structure

arborescente).

Exemple :

Cours

Gauss Newton Bohr Newton Monge


Modèle hiérarchique Inconvénients :

• Redondance de données.

• Manipulation difficile de données

hétérogènes.

XML : une application actuelle des SGBD

hiérarchique.

Xpath : langage de navigation dans un

document XML.

Xquery : langage d’interrogation d’un

document XML.

IMS : SGBD hiérarchique le plus connu.


Modèle réseau Un SGBD réseau présente les données sous

forme de structures avec des liens (des

pointeurs).

Exemple : Les cours

Maths

Chimie

Gauss

Newton

Bohr

Monge

Physique

Les profs


Modèle réseau Un avantage par rapport aux SGBD

hiérarchique : la non redondance de données est assurée.

Inconvénients :

• Le schéma de la BD devient vite complexe quand la base de données est importante (combien de pointeurs gérés ?).

• Modèle très rigide pour une exploitation future.

Exemples : SOCRATE (Système Offrant à la Clientèle des Réservations d'Affaires et de Tourisme en Europe).


Exercice corrigé N° 2 Étant donné le modèle de données suivant

d’une société de jardinerie, doit-il être hiérarchique, réseau ou relationnel ?

Nom de la plante

Instructions

de la plantation Conditions Coût

Luminosité

nécessaire

Conditions

de sol


Solution de l’exercice N° 2 Ce doit être un modèle hiérarchique car il a

un aspect arborescent.

Remarques sur le modèle de l’exemple :

• Pour déterminer la quantité de lumière nécessaire, il faut accéder d’abord au nom de la plante, puis aux conditions.

• Il serait difficile d’accéder aux instructions de plantation uniquement pour les plantes qui doivent être plantées en plein soleil.


Architecture d’un SGBD Les BD reposent sur la séparation entre les

données effectives et les programmes qui les exploitent.

Un SGBD doit fournir :

• un langage de définition des données (LDD).

• un langage de manipulation des données (LMD).

Un LDD permet à l’utilisateur de définir l’organisation des données au niveau logique (conceptuel ou abstrait). L’organisation physique des données est prise en charge avec le SGBD.


Architecture d’un SGBD

Un LMD permet à l’utilisateur de saisir, récupérer et mettre à jour les informations d’une BD.

La majorité des SGBD utilisent le langage SQL comme LMD.

Certains systèmes disposent d’interfaces graphiques visuels pour favoriser une bonne communication entre l’utilisateur et la BD.

Un schéma de BD décrit l’organisation abstraite du future système de BD. Le concepteur de la BD le définit au moyen du LDD.


Exercice corrigé N° 3

L’utilisateur doit-il faire appel au LDD ou au LMD pour accomplir chacune des tâches suivantes ?

a)Changer l’adresse d’un client.

b)Définir une table d’inventaire.

c)Entrer les informations sur un nouvel employé.



a)Changer l’adresse d’un client suppose de manipuler des données dans des tables qui existent déjà LMD.

b)Définir une table d’inventaire oblige d’utiliser le LDD (on doit définir d’abord le schéma de la table).

c)Idem à la réponse a).


Architecture à trois niveaux Cette architecture a été imposé comme le

standard (ANSI) dans le domaine des architecture des SGBD.

Elle est basée sur trois niveaux :

• niveau interne : concerne le stockage physique des données sur les mémoires de masse. Le SGBD prend en charge ce niveau.

• niveau conceptuel : concerne la définition logique des données. L’administrateur de la BD est probablement la personne qui va s’en charger de définir le schéma conceptuel de la BD au moyen du LDD.


Architecture à trois niveaux

• niveau externe : concerne les utilisateurs finals et les programmeurs d’applications. Ces derniers utilisent une vue (ou modèle mentale) de la BD et de son contenu.


Chapitre 2 :

Le modèle relationnel


Sommaire du chapitre 2 Vocabulaire des BDR

Formalisme mathématique

Clés candidates et clé primaire

Valeur NULL

Clés extérieures et CIR

Les relations

Opérateurs relationnelles

Opérateur de sélection

Opérateur de projection

Opérateur de jointure d’égalité

Opérateurs ensemblistes


Vocabulaire des BDR

Dans les années 70, E. F. Codd a décrit une

nouvelle sorte de modèle : le modèle

relationnel (MR) pour les systèmes de bases

de données.

Dans le MR, les informations sont

enregistrées dans des tables (appelées

également relations).

Un SGBD relationnel (SGBDR) est un SGBD

fondé sur le modèle relationnel.


Vocabulaire des BDR

Une BD relationnelle (BDR) est un ensemble de relations.

Une relation est représentée sous forme de tables (ou tableaux) à deux dimensions qui respectent certaines conditions.

Cette représentation est purement logique et non physique : dans un SGBDR, les données sont logiquement perçues sous forme de tables, mais elles ne le sont pas physiquement.


Vocabulaire des BDR

Chaque table est identifiée par un nom

unique.

Exemple : dans une BDR décrivant une

société, on peut trouver les tables suivantes :

• Table « Client ».

• Table « Produit ».

• Table « Facture ».

• Table « Fournisseur ».

• Table « commande », etc.


Vocabulaire des BDR

Chaque table est formée d’un certain nombre

de colonnes ou attributs ou champs.

Chaque colonne doit avoir un nom unique et

deux colonnes d’une même table ne peuvent

avoir le même nom.

Exemple : la table « Produit » peut contenir

les colonnes suivantes :

• « code produit » , « libellé du produit », «

prix unitaire », et « quantité en stock ».


Vocabulaire des BDR

Le degré d’une table est le nombre de ses

attributs ou colonnes.

Exemple :

• La table « Produit » contient les colonnes

suivantes : « code produit » , « libellé du

produit », « prix unitaire », et « quantité en

stock ».

• Donc, le degré de la table « Produit » est

égale à 4.


Vocabulaire des BDR

Pour chaque colonne d’une table, il existe un ensemble de valeurs possibles appelé son domaine.

Le domaine désigne toutes les valeurs permises qui peuvent apparaître dans la colonne.

Si « attrib » est le nom d’une colonne, alors « Dom(atrib) » désigne le domaine de cette colonne.

Toute valeur qui figure dans une colonne doit appartenir à son domaine.


Vocabulaire des BDR

Exemple : pour la table « Produit », les domaines sont :

• Dom(code produit) : un sous-ensemble des chaînes de caractères désignant le code d’un produit.

• Dom(libellé) : l’ensemble des noms de tous les produits.

• Dom(prix unitaire) : un sous-ensemble de l’ensemble des nombres réels positifs.

• Dom(quantité en stock) : un sous ensemble de l’ensemble des nombres entiers positifs.


Vocabulaire des BDR Les données d’une table apparaissent comme

un ensemble de lignes ou n-tuple où n est le

nombre d’attributs de la table.

Exemple :

Code produit Libellé Prix unitaire Quantité en

stock

DD120 Disque dur

120 Go

700.75 23

CL102 Clavier 102 100 58

CU256 Clé USB 256 450 79


Vocabulaire des BDR

Les lignes ou les tuples d’une table sont

également nommées des enregistrements.

Les enregistrements d’une table ont le même

format et représentent des objets ou des

relations du monde réel.

Le nombre totale des enregistrements

présents dans une table est appelé la

cardinalité de la table.


Vocabulaire des BDR

Remarques :

• Le degré d’une table est fixe (ne change

pas).

• La cardinalité d’une table est dynamique :

elle peut changer au cours du temps (par

effet d’ajout ou de suppression

d’enregistrements de la table).


Vocabulaire des BDR

Format général d’une table (représentation

logique d’une relation) :

Attrib1 Attrib2 Attrib3 Attrib4 … AttribN

Val11 Val12 Val13 Val14 ... Val1N

Val21 Val22 Val23 Val24 ... Val2N

... ... ... ... ... ...

ValM1 ValM2 ValM3 ValM4 ... ValMN

Attributs

Colonne

Ligne


Vocabulaire des BDR

Pour une relation donnée R, pour un attribut A

de R et un tuple t de R, on note t(A) la valeur

de l’entrée du tuple t qui se trouve dans la

colonne A.

Exemple : soit t le second tuple de la relation

« Produit » (voir page 35). Alors :

• t(Code Produit) = CL102, t(Libellé) =

Clavier 102, t(Prix unitaire) = 100 et

t(Quantité en stock) = 58.


Vocabulaire des BDR

Règles d’or à retenir pour une BDR :

• Une table est formée de colonnes et de

lignes.

• Chaque table a un nom unique.

• Chaque colonne a un nom unique.

• L’ordre des colonnes dans la table est sans

aucune importance.

• Toutes les lignes d’une table ont le même

format et le même nombre d’entrée.


Vocabulaire des BDR

Règles d’or à retenir pour une BDR (suite) :

• Les valeurs de chaque colonne appartiennent au même domaine.

• Chaque entrée dans chaque ligne doit être une valeur unique.

• L’ordre des lignes est sans importance du fait qu’elles sont identifiées par leur contenu et non pas par leur position.

• Il n’y a pas deux lignes identiques dans toutes leurs entrées.



Un schéma relationnel R est un ensemble fini de noms d’attributs A1, A2, …, An.

• On écrit : R = {A1, A2, …, An}.

À chaque attribut Ai est associé un ensemble non vide de valeurs appelé domaine de l’attribut Ai et noté Di = Dom(Ai).

Soit D le produit cartésien des ensembles Di, c’est-à-dire que D = D1 D2 … Dn.

Une relation r sur le schéma relationnel R est un ensemble fini de correspondances r = {t1, t2, …, tm} de R sur D.



Un correspondance individuel tk est appelé

tuple (ou n-tuple).

Pour tout tuple t appartenant à la relation r, on

note t(Ai) la valeur de l’attribut Ai pour le tuple

t. une condition importante :

• Pour tout élément t de r, on a : t(Ai) Di.

La cardinalité de r est card(r) = m.

Le degré de r est degré(r) = n.



Exemple d’une table « Vol » :

Numéro Départ Arrivée H_départ H_arriv

83 Oujda Rabat 9:55 11:30

84 Oujda Rabat 12:00 12:47

109 Marrakech Agadir 6:00 7:00

213 Tanger Fès 1:13 2:19

214 Laayoune Casablanca 8:15 9:59



Le schéma relationnel correspondant à la

table « Vols » est :

• Vols = {Numéro, Départ, Arrivée, H_départ,

H_arriv}.

Dom(Numéro) = {1, 2, 3, …, 999}.

Dom(Départ) = Dom(Arrivée) = {Casablanca,

Oujda, Marrakech, Fès, Tanger, Laayoune,

Agadir}.

Dom(H_départ) = Dom(H_arriv) = {0:00, 0:01,

…, 23:58, 23:59}.


clés candidates et clé primaire

La notion de clé est un concept fondamental du modèle relationnel parce qu’elle fournit le mécanisme de base pour récupérer des tuples dans une table de la BD.

On appelle clé candidate d’une relation r sur le schéma relationnel R, tout sous-ensemble K = {B1, B2, …, Bj} de R (1 j < n) vérifiant les deux conditions suivantes :

• Pour deux tuples distincts t1 et t2 de r, il existe un attribut Bh de K tel que t1(Bh) t2(Bh).



• Aucun sous-ensemble propre K’ de K ne satisfait la condition précédente.

La première condition est appelée la propriété d’unicité de la clé. Elle implique qu’il n’y a pas deux tuples différents de r à avoir des entrées identiques dans tous les attributs de l’ensemble K.

La deuxième condition s’appelle la propriété de minimalité de la clé. Elle signifie qu’aucun élément de K ne peut être abandonné sans perdre la propriété d’unicité.



Exemple : des clés candidates de la table « Vols » peuvent être :

• {Numéro}.

• {Départ, Arrivée, H_départ}.

Remarque : une relation peut comporter plusieurs clés candidates. L’une d’elles doit être désignée comme clé primaire.

Les valeurs de la clé primaire permettent d’identifier de manière univoque un tuple de la relation.


clés candidates et clé primaire Dès qu’une clé primaire a été choisie, les

autres clés candidates, si elles existent sont appelées clés alternatives de la relation.

Un SGBD n’autorise qu’une clé primaire par table.

La clé primaire peut être unique s’il comporte un seul attribut ou composé s’il contient au moins deux attributs.

Exemple : la clé primaire {Numéro} est unique alors que {Départ, Arrivée, H_départ} est composée.



Les attributs de la clé primaire sont soulignés

pour les distinguer des autres attributs ne

faisant pas partie de la clé.

Exemple : le schéma relationnel de la table se

présente ainsi :

• {Numéro, Départ, Arrivée, H_départ,

H_arriv} ou bien :

• {Numéro, Départ, Arrivée, H_départ,

H_arriv}.



Les clés primaires sont définies au moyen d’instruction LDD et sont automatiquement imposées par le SGBDR.

Les clés primaires sont généralement définies au moment de la création des tables.

Les clés primaires peuvent être naturelles ou artificielles.

Dans une table contenant des données descriptives sur les éléments chimiques, par exemple, « le poids atomique » serait une clé primaire naturelle.



Il faut noter que la plupart des clés primaires

sont inventées (artificielles).

Exemples :

• Numéro d’assurance sociale

• Numéro de permis de conduire

• Numéro de carte de crédit

• Numéro de carte guichet

• CIN : code d’identité nationale

• CNE : code national d’étudiant



Les clés sont des éléments très importants

dans la conception d’une BD relationnelle,

puisqu’elles forment la base pour représenter

les relations entre les tables.

Les clés sont les éléments qui lient les tables

entre elles.


La valeur NULL

Dans une relation, la valeur NULL représente des données manquantes, inconnues ou des données inapplicables.

La valeur NULL correspond à une entrée non renseignée (non saisie, non introduite, non obligatoire).

Une règle (Contrainte d’intégrité de la clé primaire) : aucun des attributs de la clé primaire ne doit être NULL (du fait que la clé primaire identifie de manière unique les tuples d’une relation).


La valeur NULL

La règle précédente signifie que la saisie des

valeurs pour les attributs de la clé primaire est

obligatoire.

Un attribut n’appartenant pas à la clé primaire

peut avoir la valeur NULL.

Attention :

• La valeur NULL n’est pas égale à 0 (zéro)

et ne représente aucune valeur particulière

pour l’ordinateur.


Exercice corrigé N° 4 Considérons la table DEPT et les lignes

montrées à la suite. Expliquez si ces les

lignes peuvent être ou non insérées dans la

table DEPT.

DEPARTEMENT NOM LIEU BUDGET

20 Ventes Oujda 1 700 000

10 Marketing Fès 3 575 000

10 Recherche Agadir 1 500 000

Informatique Casa 1 200 000

15 Comptabilité Rabat 1 500 000



10 Recherche Agadir 1 500 000

Informatique Casa 1 200 000

15 Comptabilité Rabat 1 500 000

Non : cette ligne ne peut pas être insérée, car elle viole

le principe d’unicité de la clé primaire (la valeur 10

existe déjà dans la table).

Non : cette ligne ne peut pas être insérée, car elle viole

la contrainte d’intégrité de la clé primaire (la clé

primaire ne peut pas être NULL).

Oui : cette ligne peut être insérée sans problème, car

aucune contrainte n’est violée.


Clés étrangères et CIR Le concept de clé étrangère (ou clé

extérieure) permet au SGBDR de maintenir

la cohérence des lignes de deux relations ou

des lignes de la même relation.

Définition : une clé étrangère est un attribut

appartenant à une table de BDR qui existe

aussi en tant que clé primaire dans une autre

table de BDR.

Une clé étrangère d’une table (dite table

enfant) référence une clé primaire d’une

autre table (dite table parent).


Clés étrangères et CIR

Exemple :

Considérons les deux relations :

• Equipe = (Code équipe, Nom équipe,

Directeur équipe)

• Joueur = (Numéro joueur, Nom joueur,

Prénom joueur, Code équipe).

L’attribut « Code équipe » dans la table

« Equipe » est la clé primaire. C’est une clé

étrangère dans la table « Joueur ».


Clés étrangères et CIR Code équipe Nom équipe Directeur équipe

1 WAC Ahmed Essalami

2 RAC Yassine Outmani

3 MO Said Daya

Numéro Nom Prénom Code équipe

1000 Basime Yahia 2

1001 El Madani Saad 2

1002 Loukili Mounir 1

1003 Salhi Kamal 3

J

o

u

e

u

r

é

q

u

i

p

e


Clés étrangères et CIR C'est ainsi que dans la table « Joueur » la clé

étrangère « Code équipe » (faisant référence à la clé primaire de même nom dans la table « Equipe ») traduit les deux règles de gestion suivantes :

• Un joueur appartient à une équipe.

• Une équipe est composée de plusieurs joueurs.

Les clés étrangères traduisent donc des liens sémantiques entre plusieurs tables d’une BD.



Chaque clé étrangère est une référence à la clé primaire d’une autre table.

Dans la plupart des cas, il faut s’assurer que ces références sont cohérentes (non contradictoires).

Les valeurs de clé étrangère qui apparaissent dans une table doivent être les mêmes que celles de clé primaire dans la table connexe.

Exemples dans le monde réel :

• Une commande doit provenir d’un client.


Clés étrangères et CIR • Un produit commandé doit être quelque

chose que nous gardons normalement en stock.

• Un cours est assuré par un professeur.

• Un étudiant doit avoir une note finale dans une matière de la filière à qui est inscrit l’étudiant.

Ces contraintes sont appelées « contraintes d’intégrité référentielle » (CIR).

Les contraintes d’intégrité référentielle doivent être supportées par le SGBD d’une façon automatique.



Un SGBDR doit appliquer les règles suivantes afin d’assurer les CIR :

• Ajout : lors de l’ajout d’une ligne contenant une valeur de clé étrangère, le SGBD vérifie que cette valeur existe aussi comme valeur de clé primaire dans la table connexe.

• Suppression : lors de la suppression d’une ligne, le SGBD vérifie qu’aucune clé étrangère dans les tables connexes ne possède la même valeur que la clé primaire de la ligne supprimée.



Un SGBDR doit appliquer les règles suivantes afin d’assurer les CIR (suite) :

• Mise à jour (ou modification) : lors de la mise à jour d’une valeur de clé primaire, le SGBD vérifie qu’aucune clé étrangère dans les tables connexes n’a la même valeur.

Dans le premier cas, le SGBD rejettera simplement l’ajout d’une ligne contenant une valeur clé étrangère inconnue.



Dans les deux derniers cas, un concepteur de BD conserve habituellement un certain contrôle sur la façon dont l’application référentielle est appliquée.

Lorsqu’une ligne contenant une clé primaire est supprimée, le concepteur peut spécifier au SGBD de supprimer toutes les lignes des autres tables ayant des clés correspondantes ou de régler toutes les clés étrangères correspondantes à NULL.

Un choix similaire est possible lors de la modification d’une valeur de clé primaire.


Les relations

Dans une BD, certaines tables sont en

relation les unes avec les autres.

Dans les BDR, les relations entre tables sont

généralement établies par l’intermédiaire de

clés primaires et étrangères.

On répartie les données dans des tables que

l’on relie pour réduire la redondance des

données.

Il y a trois types de relations entre deux tables

d’une BDR :


Les relations

Relation « un à un » : une ligne d’une table A

est en relation avec une seule ligne d’une

autre table B.

Relation « un à plusieurs » : une ligne d’une

table A peut être en relation avec plusieurs

lignes d’une autre table B.

Relation «plusieurs à plusieurs » : une ligne

d’une table A peut être en relation avec

plusieurs lignes d’une autre table B, et

inversement, une ligne de la table B peut être

en relation avec plusieurs lignes de la table A.


Les relations

Exemples de relations entre tables :

• Un recteur dirige une université :

– Relation de type un à un.

• Un propriétaire possède plusieurs voitures,

une voiture appartient à un seul propriétaire :

– Relation de type un à plusieurs.

• Un livre est écrit par plusieurs auteurs, un

auteur peut publier plusieurs livres :

–Relation de type plusieurs à plusieurs.


Les relations

IDREC NOM PRENOM

1 El Farissi Mohamed

2 Bourkia Rahma

IDUNIV NOM VILLE IDREC

100 Mohamed Premier Oujda 1

200 Hassan II Mohamadia 2

Table « Recteur »

Table « Université »


Les relations

IDPROP NOM PRENOM

1 Mhamedi Rachid

2 Naife Houria

3 Kamali Nacef

MATRICULE MODELE IDPROP

A12345 Peugeot 205 1

B47588 Renault 19 1

C25254 Fiat 105 2

C25482 Mercedes 240 3

Propriétaire

Voiture


Opérateurs relationnelles

Les opérateurs relationnelles opèrent sur une ou plusieurs relations pour produire de nouvelles relations.

Ces opérateurs forment ce qu’on appelle en BDR, « l’algèbre relationnelle ».

Chaque opération sur une relation répond à une question posée à la BD.

Une question qu’on peut poser à une BD s’appelle une « requête ».

L’algèbre relationnelle est la base de l’interrogation des BDR.



Appliqué à une relation r, cet opérateur produit une autre relation dont les lignes sont un sous-ensemble des lignes de r qui ont une valeur particulière pour un attribut spécifique. La relation résultante et r comportent les mêmes attributs.

Si r est une relation sur le schéma R, A un attribut spécifique de r et a une valeur appartenant à Dom(A), alors la sélection de r sur l’attribut A pour l’élément a est l’ensemble des tuples t de la relation r tels que t(A) = a.



Cela signifie que toues les lignes de la nouvelle relation ont a dans la colonne A.

Cette opération se note A = a(r).

Le prédicat « A = a » doit être compris comme t(A) = a.

Mathématiquement, la sélection se traduit par la relation suivante :

• A = a(r) = { t r / t(A) = a}.

Le schéma de la nouvelle relation A=a(r) est le même que celui de la relation r.



Exemples pratiques d’application de

l’opérateur de sélection :

• Étudiants nés à Oujda :

– Ville de naissance = oujda(Etudiant).

• Produits de prix unitaires 1450 :

– Prix unitaire = 1450(Produit).

• Employés de salaire 7500 :

– Salaire = 7500(Employé).

• Clients de prénom « Saber » :

– Prénom = Saber(Client).



Considérons la table « Etudiant » :

CNE Nom Prénom Ville

123456 Abid Yahya Oujda

789012 BenYahia Samir Fès

311117 Chadli Najat Rabat

125487 Dani Yasser Oujda

215465 Farah Ali Taza



Résultat de Ville de naissance = oujda(Etudiant) sur la table Etudiant :

CNE Nom Prénom Ville

123456 Abid Yahya Oujda

125487 Dani Yasser Oujda



Considérons la table « Employé » :

Code Nom Prénom Salaire Âge

1000 Saidi Yassine 8000 55

1001 Alami Brahim 7000 45

1002 El ouali Saad 7450 48

1003 Belyazid Noura 7500 48

1004 Ouahab Ismail 6500 35

1005 Atmani Fatima 7500 47

1006 Mekaoui Fayssal 7550 47

1007 Maliki Kaoutar 7500 49



Code Nom Prénom Salaire Âge

1003 Belyazid Noura 7500 48

1005 Atmani Fatima 7500 47

1007 Maliki Kaoutar 7500 49

Résultat de Salaire = 7500(Employé) sur la table Employé :



Cet opérateur choisit un sous-ensemble de

colonnes.

La projection de la relation r sur un ensemble

X de ses attributs, notée par X(r), est une

relation obtenue à partir de r en éliminant

d’abord les colonnes de r non spécifiées dans

X, puis en supprimant tout tuple redondant

(un doublon).

La projection s’exprime mathématiquement

par la relation : X(r) = { t(X) / t r}.



Exemples pratiques d’application de l’opérateur de projection :

• Codes des clients :

– Code client(Client).

• Villes des représentants :

– Ville(Représentant).

• Noms et prénoms des employés :

– Nom, Prénom(Employé).

• Numéros de téléphones et emails des professeurs :

– Numéro tél, Email(Professeur).



Considérons la table « Département » :

Code Nom Ville

10 Comptabilité Tanger

20 Informatique Casablanca

30 Marketing Casablanca

40 Fabrication Fès

50 Planification Fès

60 Statistique Oujda

70 Ventes Rabat

80 Formation Tanger



Résultat de l’opération Ville(Département) Localisations des différents départements :

Ville

Tanger

Casablanca

Fès

Oujda

Rabat


Opérateur de projection Résultat de l’opération Nom, Ville(Département)

Les différents départements et leurs localisations:

Nom Ville

Comptabilité Tanger

Informatique Casablanca

Marketing Casablanca

Fabrication Fès

Planification Fès

Statistique Oujda

Ventes Rabat

Formation Tanger



C’est un opérateur binaire qui permet

d’associer deux relations d’une même BD.

En général, cet opérateur associe deux

relations sur tous leurs attributs communs.

La jointure est constituée de tous les tuples

résultant de la concaténation des tuples de la

première relation avec ceux de la seconde qui

ont des valeurs identiques pour un ensemble

commun d’attributs X.



Par attributs communs, on entend des

attributs qui, bien qu’ils puissent ne pas avoir

le même nom, doivent avoir le même

domaine et la même signification sous-

jacente.

Soit r une relation avec un ensemble

d’attributs R et s une relation avec un

ensemble d’attributs S.

On suppose de plus que R et S aient des

attributs communs et soit X cet ensemble

d’attributs commun.



La dernière condition s’écrit : R S = X.

La jointure d’égalité (ou jointure naturelle ou équijointure) de r et s, notée r Jointure s, est une nouvelle relation dont les attributs sont les éléments de R S.

En outre, pour chaque tuple t de cette nouvelle relation, les trois conditions suivantes doivent être vérifiées :

• t(R) = tr pour un tuple tr de la relation r.

• t(S) = ts pour un tuple ts de la relation s.

• tr(X) = ts(X).


Opérateur de jointure d’égalité Exemple :

• Considérons une table « Département »

dont le schéma est (ID, NOM, VILLE) :

ID NOM VILLE

100 Comptabilité Oujda

200 Marketing Fès

300 Ventes Rabat

• Et une table « Personnel » dont le schéma est (ID, NOM, DEPT, TITRE) :

Département



ID NOM DEPT TITRE

100 Ali Ventes Employé

200 Fatima Marketing Employé

300 Yassine Comptabilité Employé

400 Saad Comptabilité Chef comptable

Personnel



Dans cet exemple, les attributs communs sont

les attributs « NOM » et « DEPT ».

Du fait que les deux tables ont un attribut appelé

ID, pour éviter de confondre l’attribut « ID » de la

table « Département » avec celui de la table «

Personnel », il est nécessaire de qualifier

chaque attribut en le faisant suivre du nom de la

table correspondante avant de les joindre.

Pour une raison analogue, l’attribut « NOM » des

deux tables doivent être qualifiés.



« Département » jointure « Personnel » :

ID

DEPT

NOM

DEPT

VILLE ID

PERS

NOM

PERS

TITRE

100 Comptabilité Oujda 300 Yassine Employé

100 Comptabilité Oujda 400 Saad Chef

comptable

200 Marketing Fès 200 Fatima Employé

300 Ventes Rabat 100 Ali employé



• Les résultats de jointure de « Département » avec « Personnel » pourraient satisfaire une requête d’utilisateur pour « Afficher toutes les informations sur les employés avec leur id de département, leur nom et leur ville ».

• Dans cette opération de jointure, l’attribut commun est X = {NOM DEPT}.

• Vérifions que cette jointure satisfait bien les trois conditions mentionnées ci-dessus.

• En fait, si r est la relation « Département » et s la relation « Personnel », nous aurons les schémas respectifs :



R = {ID, NOM, VILLE}

S = {ID, NOM, DEPT, TITRE}

• Pour chaque tuple t de la relation jointure, on a :

t(R) = t(ID, NOM, VILLE) = tr pour un tuple de r.

t(S) = t(ID, NOM, DEPT, TITRE) = ts pour un tuple de s.

tr(X) = ts(X).

• Exemple :

Prenons le premier tuple de la relation jointure (100, Comptabilité, Oujda, 300, Yassine, Employé) et appelons-le « t ».



• Nous avons :

t(ID DEPT, NOM DEPT, VILLE) = (100,

Comptabilité, Oujda) = tr pour un tuple de r.

t(ID PERS, NOM PERS, DEPT, TITRE) = (300,

Yassine, Comptabilité, Employé) = ts pour un

tuple de s.

tr(NOM DEPT) = ts(NOM DEPT) = Comptabilité.



L’union :

• L’union de deux relations r(R) et s(S) est la relation obtenue à partir de r et s et formée par les tuples qui sont présents dans r ou dans s.

• Elle se note par r s.

L’intersection :

• L’intersection de deux relations r(R) et s(S) est la relation obtenue à partir de r et s et formée par les tuples qui sont présents simultanément dans r et s.




La différence :

• La différence de deux relations r(R) et s(S) est la relation obtenue à partir de r et s et formée par les tuples qui sont présents dans r et non présents dans s.

• Elle se note par r - s.

Pour effectuer l’une de ces trois opérations, il faut que les deux relations r et s soient compatibles.

Deux relations r et s sont dites compatibles si elles ont le même degré et leurs attributs ont les mêmes domaines.



Exemple de l’union :

ID NOM PRENOM PROJET DEPT

10 Jamali Said e-commerce Ventes

11 Nadra Yousra Pare-feu Informatique

12 Badre Malek e-commerce Ventes


29 Najib Ziryabe Site web Informatique


Programmeurs_C

Programmeurs_Java








Programmeurs_C Programmeurs_Java



Exemple de l’intersection :








Programmeurs_C

Programmeurs_Java





Programmeurs_C Programmeurs_Java



Exemple de la différence :








Programmeurs_C

Programmeurs_Java





11 Nadra Yousra Pare-feu informatique

Programmeurs_C - Programmeurs_Java


29 Najib Ziryabe Site web informatique

Programmeurs_C - Programmeurs_Java

Système d’Information et Base de Données 104 104


Le produit cartésien :

• Le produit cartésien de deux relations r(R)

et s(S) est la relation obtenue à partir de r

et s et formée en concaténant tous les

tuples de r avec tous les tuples de s.


• Remarques :

– Degré(r s) = Degré(r) + Degré(s).

– le produit cartésien des relations n’est

pas commutatif.



• Exemple du produit cartésien :

ID_CLIENT ARTICLE

100 A

200 B

CODE NOM PRIX

A Bicyclette 250

B Chaussures 90

C Lunettes 150

D Gants 35

CLIENT PRODUIT



ID_CLIENT ARTICLE CODE NOM PRIX

100 A A Bicyclette 250

100 A B Chaussures 90

100 A C Lunettes 150

100 A D Gants 35

200 B A Bicyclette 250

200 B B Chaussures 90

200 B C Lunettes 150

200 B D Gants 35

CLIENT PRODUIT


Chapitre 3 :

Introduction à SQL


Sommaire du chapitre 3 Présentation du langage SQL

Définition d’une base de données

Requêtes SQL


Présentation du langage SQL

• SQL est le langage informatique standard pour la communication avec les SGBDR.

• Le standard SQL a été défini par l’ANSI (American National Standard Institute) et l’ISO (International Standards Organization).

• Le sigle SQL signifie Structured Query Language ou langage de requêtes structuré.

• La dernière version de SQL est appelé en général SQL/92 ou SQL.

• SQL est essentiellement un langage servant à effectuer des requêtes sur une base de données pour en extraire des informations.



• Caractéristiques de SQL :

C’est un langage déclaratif et non procédural : on explicite ce qu’on veut et non pas la manière de l’avoir.

Il offre un LDD pour définir et créer des objets de BDR (tables, requêtes, …etc)

Il offre un LMD pour manipuler les objets d’une BDR.

C’est un langage facile à comprendre et à utiliser (syntaxe très proche du langage naturel, pas d’instructions de contrôle, ni de structures de données).



• Utilisation de SQL :

En mode interactif : l’utilisateur écrit

textuellement une commande SQL et récupère

le résultat immédiatement.

En mode intégré : une commande SQL est

mélangée avec les instructions d’un programme

en langage de haut niveau tel que C, C++ ou

Cobol.


Création d’une BD

• Création d’une table :

Cette opération donne comme résultat une

table vide (ne contenant aucun enregistrement).

Il faut préciser :

• Le nom de la table;

• La description de ses colonnes : nom, type

de données et contraintes.

SQL dispose d’un nombre de types de données

: numériques, alphanumériques, logiques, dates

et heures, etc.



• Quelques types de données en SQL :

INTEGER ou INT : entiers signés.

CHARACTER(p) ou CHAR(p) : chaîne de

caractères de longueur fixe de p caractères.

DATE : dates et/ou heures.

LOGICAL : valeur logique « oui » ou « non ».

DECIMAL(p, q) : nombres décimaux de p

chiffres dont q après le point décimal (par

défaut, q = 0) (ne fonctionne pas avec Access).

FLOAT : nombre réel en virgule flottante.



• Quelques propriétés des attributs en SQL :

PRIMARY KEY : clé primaire.

FOREIGN KEY : clé étrangère.

NULL / NOT NULL : valeurs non obligatoires /

obligatoires.

DEFAULT = Valeur : pour définir une valeur

par défaut (ne fonctionne pas sous Access).

CHECK (Condition): pour contrôler la validité

des valeurs (ne fonctionne pas sous Access).



• Syntaxe SQL pour créer une nouvelle table :

CREATE TABLE Nom_table

(Nom_champ1 Type_champ1 [CONSTRAINT]

Nom_champ1 Propriété_champ1, …,

Nom_champN Type_champN [CONSTRAINT]

Nom_champN Propriété_champN);

Remarque :

• Toute commande SQL se termine par un

point virgule.



• Exemple 1 :

CREATE TABLE CLIENT

(ID INTEGER CONSTRAINT ID PRIMARY KEY,

NOM CHAR(15),

PRENOM CHAR(15),

DATENAIS DATE,

VILLE CHAR(20),

TEL CHAR(12),

EMAIL CHAR(50));



• Exemple 2 :

CREATE TABLE ETUDIANT

(ID INTEGER CONSTRAINT ID PRIMARY KEY,

NOM CHAR(15),

PRENOM CHAR(15),

AGE INTEGER CONSTRAINT AGE CHECK(<

35),

BOURSIER LOGICAL);



EXEMPLE COMPLET



• Exemple complet :

CREATE TABLE CLIENT

(IDCLIENT INTEGER CONSTRAINT IDCLIENT

PRIMARY KEY,

NOMCLIENT CHAR(15),

PRENOMCLIENT CHAR(15),

ADRCLIENT CHAR(50));




CREATE TABLE AGENCE

(IDAGENCE INTEGER CONSTRAINT

IDAGENCE PRIMARY KEY,

NOMAGENCE CHAR(40),

ADRAGENCE CHAR(50));




CREATE TABLE COMPTE

(IDCOMPTE INTEGER CONSTRAINT

IDCOMPTE PRIMARY KEY,

CONSTRAINT IDCLIENT FOGEIGN KEY

(IDCLEINT) REFERENCES CLIENT,

CONSTRAINT IDAGENCE FOGEIGN KEY

(IDCAGENCE) REFERENCES AGENCE,

SOLDE FLOAT);



• Suppression d’une table :

On peut supprimer n’importe quelle table en

utilisant SQL.

Si au moment de la suppression, la table

contenait des enregistrements, ces derniers

sont préalablement supprimés, opération qui est

soumise aux contraintes d’intégrité référentielle

qui concernent la table en question.

Par exemple, la suppression de la table CLIENT

entraînera la suppression de toutes les lignes

de la table COMPTE (mais non pas la table).



• Suppression d’une table :

La syntaxe SQL pour supprimer une table est

très simple :

• DROP TABLE Nom_table

Exemples :

• DROP TABLE CLIENT

• DROP TABLE AGENCE

• DROP TABLE COMPTE



• Modification de la structure d’une table :

Il y a trois types d’actions concernant la modification de structure d’une table :

• Ajouter une ou plusieurs colonnes.

• Supprimer une ou plusieurs colonnes.

• Modifier les propriétés d’une ou de plusieurs colonnes.

Les commandes SQL relatives à ces actions :

• ADD

• DROP

• MODIFY



• Modification de la structure d’une table :

Pour modifier la structure d’une table, on

précise tout d’abord le nom de celle-ci en

écrivant la commande suivante :

• ALTER TABLE Nom_table

En suite, on spécifie la nature de l’action de

modification (ajout, suppression ou

modification).

Pour cela, il faut utiliser l’une des trois

commandes : ADD, DROP ou MODIFY.



• Exemples d’ajout :

Ajouter une colonne « AGE » dans la table «

CLIENT » :

ALTER TABLE CLIENT

ADD AGE INTEGER;

Ajouter une colonne « DATECOMPTE » dans la

table « COMPTE » :

ALTER TABLE COMPTE

ADD DATECOMPTE DATE;



• Exemples de suppression :

Supprimer la colonne « ADRAGENCE » de la

table « AGENCE » :

ALTER TABLE CLIENT

DROP ADRAGENCE;

Supprimer la colonne « DATECOMPTE » de la

table « COMPTE » :

ALTER TABLE COMPTE

DROP DATECOMPTE;



• Exemples de modification de propriétés :

Modifier le type de la colonne « ADRAGENCE » pour qu’il soit un CHAR(100) au lieu de CHAR(40) :

ALTER TABLE AGENCE

MODIFY ADRAGENCE CHAR(100);

Modifier la colonne « AGE » de la table « CLIENT » pour qu’elle soit obligatoire :

ALTER TABLE CLEINT

MODIFY AGE NOT NULL;


Création d’une BD • Insertion d’enregistrements dans une table :

La commande qui permet d’insérer une ligne

dans une table est la suivante :

INSERT

INTO Nom_table (Champ_i, …, Champ_n)

VALUES ('valeur_i', …, 'valeur_n',);

Dans la clause “INTO…”, on spécifie le nom de

la table ainsi que les noms des colonnes.

Si l’on veut ajouter une ligne contenant les

valeurs pour tous les champs, dans ce cas on

peut omettre les noms de colonnes.


Création d’une BD • Insertion d’enregistrements dans une table :

Les valeurs des champs doivent être mises

entre deux quottes :

• '17'

• '-15.5267'

• 'Ahmed'

• '17/02/1987'

• '[email protected]'

Exception : la valeur « NULL » ne respecte pas

cette syntaxe (on l’écrit sans les quottes).


Création d’une BD • Exemples :

INSERT

INTO CLIENT

VALUES ('1', 'Jawadi', 'Hicham', '12, Rue Nahda,

Oujda');

INSERT

INTO CLIENT (IDCLIENT, NOMCLIENT)

VALUES ('2', 'Rafik');



INSERT

INTO CLIENT

VALUES ('3', 'Jaydi', 'Wiaam', NULL);

INSERT

INTO AGENCE (IDAGECNE)

VALUES ('25');


Création d’une BD • Modification d’un enregistrement :

La commande qui permet de modifier la valeur

d’un champ d’une ligne dans une table est :

UPDATE Nom_table

SET Nom_champ_n = 'valeur_n'

WHERE Condition;

On modifie la valeur d’un champ d’un

enregistrement qui vérifie une condition précise

(critère pour accéder à la ligne qui sera le sujet

de la modification).



La clause SET indique quels champs de la table

vont être mis à jour et avec quelles valeurs ils

vont l’être.

Les champs non spécifiés après la clause SET

ne seront pas modifiés.

Les champs que nous voulons mettre à jour

doivent être écrits dans la clause SET, l’un

après l’autre (avec leurs valeurs bien sûr) et

séparés par des virgules.



La commande UPDATE affecte tous les

enregistrements qui répondent à la condition

donnée dans la clause WHERE.

Si la clause WHERE est absente, tous les

enregistrements de la table seront affectées.



On veut, dans la table COMPTE, modifier le

solde d’un client dont le code est « 1725 ».

On doit taper alors la commande suivante :

UPDATE COMPTE

SET Solde = 250000

WHERE IDCLIENT = 1725;

On veut maintenant changer le prix unitaire de

tous les produits à 1000 :



UPDATE PRODUIT

SET Prix_unitaire = 1000;

On veut, pour les étudiants dont les pères ont

des salaires qui dépassent 10000, ne pas

leur attribuer une bourse :

UPDATE ETUDIANT

SET Boursier = 'non'

WHERE Salaire_pere > 10000;


Création d’une BD • Suppression d’un enregistrement :

Pour supprimer un enregistrement, il faut utiliser

la commande :

DELETE

FROM Nom_table

WHERE Condition;

Pour supprimer tous les enregistrements d’une

table, il suffit de ne pas indiquer la clause

WHERE.

Remarque : la table sera vide et ne sera pas

supprimée de la BD.



Supprimer tous les candidats qui n’ont pas une

moyenne supérieure ou égale à 13.75 :

DELETE

FROM CANDIDAT

WHERE Moyenne < 13.75;

Supprimer tous clients :

DELETE

FROM CLIENT


Les requêtes de sélection • Présentation et syntaxe :

La commande SELECT est la commande la plus complexe de SQL.

Cette commande servira à faire des requêtes pour extraire des données à partir des tables d’une BD.

Elle peut être associée à une des commandes de manipulation de tables vues précédemment pour spécifier une condition.

Sa syntaxe est :

SELECT champ_1, champ_2, champ_3, ...

FROM Nom_table

WHERE Condition;



S'il y a plus d'un champ spécifié après la clause SELECT, les champs doivent être séparés par des virgules.

Les champs sont retournés dans l'ordre spécifié après la clause SELECT, et non pas dans l'ordre qu'ils ont été créés dans la table.

Par exemple, pour sélectionner les champs « Prenom » et « Nom » de tous les enregistrements de la table CLIENT :

SELECT Prenom, Nom

FROM CLIENT

va renvoyer les prénoms et noms de tous les clients de la table CLIENT.



Si on veut récupérer tous les champs des enregistrements sélectionnés, la syntaxe est la suivante :

SELECT *

FROM Nom_table;

Les clauses SELECT et FROM doivent obligatoirement apparaître au début de chaque requête.

On peut, ensuite, indiquer des critères de sélection avec la clause WHERE :

SELECT * FROM table

WHERE condition;



Par exemple, pour sélectionner tous les clients de la table CLIENT dont le code postal est 75000, on écrit :

SELECT *

FROM CLIENT

WHERE Code_postal = 75000;


Les requêtes de sélection • Exemples :

Noms et prénoms des clients qui habitent à Oujda :

SELECT Nom, Prenom

FROM CLIENT

WHERE Ville = 'Oujda';

Étudiants boursiers :

SELECT *

FROM ETUDIANT

WHERE Boursier = 'oui;



Clients ne résidant pas à Fès :

SELECT *

FROM CLIENT

WHERE Ville <> 'Fès';

Étudiants dont l’âge dépassent 25 ans :

SELECT *

FROM ETUDIANT

WHERE Age > 25;


Les requêtes de sélection • Les opérateurs de comparaison :

On peut utiliser les opérateurs suivants dans les conditions d’une requête :

• = : égal

• <> : différent

• < : inférieur strict

• > : supérieur strict

• <= : inférieur ou égal

• >= : supérieur ou égal

Comment écrire les valeurs constantes :

• Numériques : 125, -654, -7.54, etc.

• Chaînes de caractères et dates : entre « ' ».


Les requêtes de sélection • Les opérateurs logiques :

Ils permettent de construire des conditions plus complexes.

Les opérateurs logiques de SQL sont :

• AND (ET) : conjonction.

• OR (OU) : disjonction

• NOT (NON) : négation

L'opérateur AND réunit deux ou plusieurs conditions et sélectionne un enregistrement seulement si cet enregistrement satisfait toutes les conditions listées.

L'opérateur OR réunit deux conditions, mais sélectionne un enregistrement si une des conditions listées est satisfaite.



L'opérateur NOT opère sur une combinaison de conditions et sélectionne un enregistrement seulement si cet enregistrement ne satisfait pas la condition qu’engendre cette combinaison.

• Exemples :

Sélectionner tous les clients nommés 'Ahmed' qui habitent Rabat :

SELECT *

FROM CLIENT

WHERE Nom = 'Ahmed' AND Ville = 'Rabat';



Sélectionner tous les clients nommés 'Ahmed' qui habitent Rabat :

SELECT *

FROM CLIENT

WHERE Nom = 'Ahmed' AND Ville = 'Rabat';

Sélectionner tous les clients nommés 'Ahmed' ou 'Ali' :

SELECT *

FROM CLIENT

WHERE Nom = 'Ahmed' OR Nom = 'Ali';



Sélectionner tous les clients nommés 'Ahmed' qui habitent Rabat ou Fès :

SELECT *

FROM CLIENT

WHERE Nom = 'Ahmed' AND (Ville = 'Rabat' OR Ville = 'Fès');



Par exemple, pour L'opérateur OR réunit deux conditions mais sélectionne un enregistrement si UNE des conditions listées

est satisfaite. Par exemple, AND et OR peuvent être combinés :

SELECT *

FROM Clients

WHERE nom = 'Dupond' AND (ville = 'Saint-Quentin' OR ville =

'Paris');

Nous sélectionnons ici les clients nommés "Dupond" qui habitent soit à Saint-Quentin, soit à Paris.

Pourquoi avons-nous placé des parenthèses ? Pour résumer, on peut dire que l'opérateur AND a une plus

grande priorité que l'opérateur OR. Ce qui signifie que SQL va d'abord sélectionner les conditions

séparées par des AND, puis celles séparées par des OR, si on avait omis les parenthèses ici, SQL aurait

cherché les clients nommés "Dupond" vivant à Saint-Quentin ou les clients habitant à Paris, ce qui n'est

pas le but recherché. Pour généraliser, mettez toujours des parenthèses pour bien séparer vos conditions.

Page 10 sur 15

Documents

DocumentBD