DUT Génie BiologiqueOption Bioinformatique

Les bases de données relationnellesavec MySQL

ric PipardÉ

Travaux Pratiques n° 3

Les bases de données multi­tables 

Objectifs :Créer et manipuler des tables avec clés étrangères.Utiliser les relations un­à­un, un­à­plusieurs, plusieurs­à­plusieurs.Savoir choisir des clés primaires pour respecter les formes normales.Utiliser les jointures internes.

Table des matièresTravail demandé......................................................................................................................................31 – Concevoir des tables avec des clés étrangères...................................................................................42 – Contrainte d'intégrité référentielle....................................................................................................43 – Créer une table avec une clé étrangère : FOREIGN KEY.................................................................44 – Relation entre tables..........................................................................................................................5

4.1 – Relation un­à­un........................................................................................................................54.2 – Relation un­à­plusieurs..............................................................................................................54.3 – Relation plusieurs­à­plusieurs...................................................................................................6

5 – Dépendances fonctionnelles..............................................................................................................76 – Formes normales...............................................................................................................................7

6.1 – Première forme normale ou 1FN...............................................................................................76.2 – Deuxième forme normale ou 2FN.............................................................................................76.3 – Troisième forme normale ou 3FN.............................................................................................7

7 – Jointure interne cartésienne : CROSS JOIN......................................................................................77.1 – Alias de table : AS.....................................................................................................................87.2 – Alias de colonne : AS................................................................................................................8

8 – Jointure interne équi­jointure : INNER JOIN et =............................................................................89 – Jointure interne non équi­jointure : INNER JOIN et <>...................................................................810 – Jointure interne jointure naturelle : NATUREL JOIN.....................................................................911 – Union de tables : UNION................................................................................................................9Annexe...................................................................................................................................................10Tables pour les jointures........................................................................................................................11

2

Travail demandé

 1. Tout   d'abord,   vous   devez   lire   attentivement   les   paragraphes   suivants   et   comprendre   les exemples.

 2. Utiliser la commande SHOW pour afficher la description de la table mes_contacts. 3. Rendre conforme mes_contacts à partir des schémas relationnels donné en annexe.  4. Pour la suite utiliser les tables de données données en annexe «Tables pour les jointures». 5. Faire une jointure cartésienne entre les tables profession et statut. 6. Afficher les adresses email et les professions de chaque personne. 7. Afficher nom, prénom, statut de chaque personne. 8. Afficher les personnes mariés. 9. Quel est le résultat de cette requête  

SELECT mc.nom, mc.prenom  FROM mes_contacts mc INNER JOIN profession INNER JOIN contact_int;

3

1 – Concevoir des tables avec des clés étrangèresLa clé étrangère est une colonne d'une table qui référence la clé primaire d'une autre table. Les deux tables sont donc reliées via clé primaire = clé étrangère.Une clé étrangère peut avoir un nom différent de la clé primaire dont elle provient.La clé primaire utilisée par une clé étrangère s'appelle également clé parente ; la table d'où vient la clé primaire est appelée table parente ; la table qui contient la clé étrangère est appelée table enfant.La clé étrangère peut s'utiliser pour vérifier que les lignes d'une table ont des lignes correspondantes dans une autre table.Les valeurs d'une clé étrangère peuvent être NULL (pas de clé primaire correspondante dans la table parente) même si celles d'une clé primaire ne le peuvent pas.Les clés étrangères ne sont pas forcément uniques ; en réalité elles ne le sont souvent pas. Il  n'est  pas obligatoire que la clé  étrangère référence une clé  primaire mais  la clé  étrangére doit référencer un unique tuple de la table parente.

2 – Contrainte d'intégrité référentielleLes valeurs d'une clé étrangère doivent exister dans la table parente ; cela crée un lien entre les deux tables. S'il  existe un tuple avec clé  étrangère alors  il  existe un tuple correspondant dans la  table parente. Si on essaye de supprimer un tuple parent ou de changer la valeur de la clé primaire il y aura une erreur si et seulement si la clé primaire est une contrainte de clé étrangère dans une autre table ; il faut supprimer le tuple de clé étrangère en premier.

3 – Créer une table avec une clé étrangère : FOREIGN KEY

id_contact nom prenom telephone sexe

id_int interet cleEtrangere

Un id_contact a plusieurs centre d'intérêt.

mysql> CREATE TABLE centreInteret (    ­> id_int INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    ­> interet VARCHAR(50) NOT NULL,    ­> cleEtrangere INT NOT NULL,    ­> CONSTRAINT mesContacts_cleEtrangere_ce,    ­> FOREIGN KEY (cleEtrangere) REFERENCES mesContacts (id_contact)    ­> );

4

mesContacts

centreInteret

La clause CONSTRAINT (qui est facultative) permet de donner un nom à une clé étrangère ; ce nom est utilisé pour supprimer une clé étrangère :

mysql> ALTER TABLE centreInteret    ­> DROP FOREIGN KEY mesContacts_cleEtrangere_ce;

Si la clause  CONSTRAINT  est  ommise, vous devez récupérer ce nom par  la commande  SHOW CREATE TABLE.

Vous pouvez aussi préciser qu'une clé est une clé étrangère après avoir créé la clé :

mysql> ALTER TABLE  centreInteret   ­> ADD FOREIGN KEY (cleEtrangere)    REFERENCES mesContacts (id_contact);

4 – Relation entre tablesCes relations sont induites par le couple clé primaire ­ clé étrangère.

4.1 – Relation un­à­un

À un tuple d'une table A ne peut correspondre au plus un tuple dans une table B. Une ligne de la table parente est reliée à une ligne de la table enfant.Il est plus sensé de laisser les données un­à­un dans la table principale mais il y a des avantages à extraire des colonnes dans une autre table :

­ requêtes s'exécutant plus rapidement,­ placer des valeurs NULL en dehors de la table principale,­ restreindre l'accès aux données de la table principale,­ isoler les données volumineuses.

Chaque id_contact a un seul téléphone et à un telephone correspond un seul id_contact.

id_contact nom prenom sexe

id_int telephone cleEtrangere

4.2 – Relation un­à­plusieurs

Un tuple d'une table A peut avoir plusieurs tuples correspondants dans une table B mais à un tuple de la table B ne peut correspondre qu'à un tuple de la table A. Dans la table B une clé étrangère référence un tuple de la table A.

5

mesContacts

Telephone

Chaque  id_contact  n'a qu'une seule profession mais dans la  table  mesContacts  on peut  retrouver plusieurs fois la même profession et donc plusieurs fois la même valeur de clé étrangère.

id_contact nom prenom telephone cleEtrangere

id_prof profession

4.3 – Relation plusieurs­à­plusieurs

Plusieurs tuple d'une table A peuvent avoir plusieurs tuples correspondants dans une table B et à plusieurs tuples de la table B correspondent plusieurs tuples de la table A. Ajouter une clé étrangère à l'une ou l'autre table donne des données dupliquées (la 1ère Forme Normale n'est plus respectée). Il faut transformer la relation plusieurs­à­plusieurs en deux relations un­à­plusieurs. Pour ce faire, il faut créer une table de jonction qui contient les clés primaires des deux tables.

id_contact nom prenom telephone sexe

id_contact id_int

id_int interet

6

mesContacts

professions

mesContacts

centreInteret

jonction

5 – Dépendances fonctionnellesSi les données d'une colonne doivent changer quand les données d'une autre colonne sont modifiées alors la première colonne est fonctionnellement dépendante de la seconde.Une clé primaire composite est une clé primaire composée de plusieurs colonnes.Une dépendance fonctionnelle  partielle est  une dépendance entre  une colonne non­clé  et  une ou plusieurs mais pas de toutes les colonnes d'une clé primaire composite.Une dépendance fonctionnelle transitive X  Z et Z  Y est une dépendance via des colonnes non­clé Z.

6 – Formes normales

6.1 – Première forme normale ou 1FN

Les colonnes ne contiennent que des valeurs atomiques et il n'y a pas de répétition de groupes de données.

Contre­exemple

id nomPrenom couleur1 couleur2

Gram, Smith blanc bleu

Henry, Paul  jaune vert

6.2 – Deuxième forme normale ou 2FN

Une table est en 2FN si elle est en 1FN et si elle ne contient pas de dépendances fonctionnelles partielles. Une table qui est en 1FN et telle que toutes ses colonnes font partie de sa clé primaire est en 2FN. Une table qui est en 1FN et telle que sa clé primaire tient sur une colonne (donc non composite) est en 2FN.   Donc   il   est   avantageux   de   créer   comme   clé   primaire   une   colonne   artificielle   comme "Identification" de type INT et AUTO_INCREMENT.

6.3 – Troisième forme normale ou 3FN

Une table est en 3FN si elle est en 2FN et si elle n'a pas de dépendance transitive.

7 – Jointure interne cartésienne : CROSS JOINUne jointure est une opération binaire correspondant à  un produit  cartésien suivi d'une sélection. Voici comment effectuer la jointure cartésienne sur les attributs  jouet  et  enfant des tables  Jouets  et Enfants :

7

Non atomiqueMême catégorie et même 

type de données

mysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets     ­> CROSS JOIN    ­> Enfants;ou mysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets , Enfants;

7.1 – Alias de table : AS

Pour manipuler la table Jouets sous l'alias j utiliser Jouets AS j.

mysql> SELECT j.jouet, e.enfant    ­> FROM Jouets AS j    ­> CROSS JOIN    ­> Enfants AS e;

7.2 – Alias de colonne : AS

Pour manipuler une colonne virtuelle.

mysql> SELECT salaire_actuel, salaire_voulu, salaire_voulu ­ salaire_actuel AS augmentation    ­> FROM SALAIRE;

8 – Jointure interne équi­jointure : INNER JOIN et =mysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets     ­> INNER JOIN    ­> Enfants    ­> ON Jouets.id_jouet = Enfants.id_jouets;

ou

mysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets     ­> INNER JOIN    ­> Enfants    ­> WHERE Jouets.id_jouet = Enfants.id_jouets;

La clause ON peut être remplacée par WHERE.

9 – Jointure interne non équi­jointure : INNER JOIN et <>mysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets INNER JOIN Enfants    ­> ON Jouets.id_jouet <> Enfants.id_jouets;

8

10 – Jointure interne jointure naturelle : NATUREL JOINmysql> SELECT Jouets.jouet, Enfants.enfant    ­> FROM Jouets NATUREL JOIN Enfants;

11 – Union de tables : UNIONUne union combine le résultat de deux ou plus de requêtes dans une table à partir des spécifications de la clause SELECT. Le nombre de colonnes de chaque SELECT doit être identique. Il n'y a pas de valeurs dupliquées dans le résultat d'une UNION ; pour avoir les duplicata, il faut utiliser  UNION ALL. UNION ne prend qu'un ORDER BY à la fin de la requête.

mysql> SELECT profession FROM emploi_actuel     ­> UNION SELECT profession FROM emploi_voulu    ­> UNION SELECT profession FROM offres_emplois    ­> ORDER BY profession;

mysql> CREATE TABLE resultat AS    ­> SELECT profession FROM emploi_actuel     ­> UNION SELECT profession FROM emploi_voulu    ­> UNION SELECT profession FROM offres_emplois    ­> ORDER BY profession;

9

Annexe

id_prof profession code_postal ville dpmt id_statut statut

id_contact nom prenom telephone email sexe anniversaire id_prof code_postal id_statut

id_contact id_int id_contact id_cherche

id_int centre_interet id_cherche cherche

Les   relations  profession/mes_contacts,  code_postal/mes_contacts  et  statut/mes_contacts  sont   3 relations un­à­plusieurs.centres_interet/contact_id et cherche/contact_cherche sont 2 relations un­à­plusieurs.mes_contacts/contact_id et mes_contacts/contact_cherche sont 2 relations un­à­plusieurs.

La relation mes_contacts/centres_interet  est une relation plusieurs­à­plusieurs formée de 2 relations un­à­plusieurs et d'une table de liaison contact_int.La relation  mes_contacts/cherche  est une relation plusieurs­à­plusieurs formée de 2 relations un­à­plusieurs et d'une table de liaison contact_cherche.

10

codePostal statut

mes_contacts

contact_id contact_cherche

centres_interet cherche

profession

Tables pour les jointures/***********************Table mes_contacts pour les jointures*************************/+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+| id_contact | nom     | prenom  | telephone  | email                           |+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+|          1 | Neveu   | Anne    | 0421234564 | anne@planchesagogo.com          | |          2 | Gros    | Etienne | 0654364784 | nessayepas@pizzaprestissima.com | |          3 | Souk    | Alain   | 0653412981 | souk@pizzaprestissima.com       | |          4 | Mendoza | Lucie   | 0234564312 | lucie@cafestarbuzz.com          | +­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+

+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­+­­­­­­­­­­­+| id_contact | nom     | sexe | anniversaire | id_prof | code_postal | id_statut |+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­+­­­­­­­­­­­+|          1 | Neveu   | F    | 1962­01­07   |       5 |           2 |         3 | |          2 | Gros    | M    | 1964­09­10   |       1 |           1 |         2 | |          3 | Souk    | M    | 1966­07­01   |       4 |           3 |         1 | |          4 | Mendoza | F    | 1979­08­19   |       1 |           1 |         1 | +­­­­­­­­­­­­+­­­­­­­­­+­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­+­­­­­­­­­­­+/********************Table profession pour les jointures*************************/+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+| id_prof | profession                   |+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+|       1 | Administrateur Système       | |       2 | Administrateur Système Unix  | |       3 | Ingénieur                    | |       4 | Ingénieur Aéronautique       | |       5 | Ingénieur Informatique       | |       6 | Technicien                   | +­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+/***********************Table code_postal pour les jointures************************/+­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­+| code_postal | ville    | dept |+­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­+|           1 | Nice     |    6 | |           2 | Grenoble |   38 | |           3 | Orléans |   45  | +­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­+/*************************Table statut pour les jointures*********************/+­­­­­­­­­­­+­­­­­­­­­­­­­­+| id_statut | statut       |+­­­­­­­­­­­+­­­­­­­­­­­­­­+|         1 | Marié        | |         2 | Célibataire  | |         3 | Pacsé        | +­­­­­­­­­­­+­­­­­­­­­­­­­­+

11

/***************************Table contact_cherche pour les jointures*********************/+­­­­­­­­­­­­+­­­­­­­­­­­­+| id_cherche | id_contact |+­­­­­­­­­­­­+­­­­­­­­­­­­+|          1 |          2 | |          2 |          1 | |          2 |          4 | |          3 |          3 | |          4 |          2 | +­­­­­­­­­­­­+­­­­­­­­­­­­+/********************Table contact_int pour les jointures************************* +­­­­­­­­+­­­­­­­­­­­­+| id_int | id_contact |+­­­­­­­­+­­­­­­­­­­­­+|      1 |          1 | |      9 |          1 | |      8 |          1 | |      7 |          2 | |      6 |          2 | |      4 |          3 | |      5 |          3 | |      3 |          4 | |      2 |          4 | +­­­­­­­­+­­­­­­­­­­­­+/****************************Table cherche pour les jointures**************************/+­­­­­­­­­­­­+­­­­­­­­­­­­­­­­+| id_cherche | cherche        |+­­­­­­­­­­­­+­­­­­­­­­­­­­­­­+|          1       | Amis                  | |          2       | Nouveau boulot | |          3       | Rien                   | |          4       | Rdv                    | +­­­­­­­­­­­­+­­­­­­­­­­­­­­­­+/***********************Table centres_interet pour les jointures*************************/+­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+| id_int | centre_interet                  |+­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+|      1    | Collection de livres        | |      2    | Théâtre                           | |      3    | Danse                             | |      4    | Java                                | |      5    | Programmation              | |      6    | Randonnée                     | |      7    | Ecriture                          | |      8    | Equitation                      | |      9    | Fabrication de la bière   | +­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­+

12