Travaux dirigés

Schéma entités-relations (ER Diagram)

Le schéma entité-relation est l'étape préparatoire à la réalisation d'une base de données. Il modélise sous forme graphique un problème réel.

Exercice 1. Association

Une association est constituée d'adhérent. Un "marché" biologique est mis en place à destination de ces adhérents. Différents produits sont vendus. Les produits sont organisés en familles. Les adhérent viennent acheter des produits. Un ticket de caisse est édité.

Amélioration 1 : Les différents produits sont achetés chez des fournisseurs.

Amélioration 2 : Un même produit peut être approvisionné chez plusieurs fournisseurs.

Exercice 2. Société de transport

Une société de transport possède une flotte de camion et plusieurs entrepots.

Cette société embauche des chauffeurs.

Les camions sont utilisés pour effectuer des transports entre les entrepôts. Les camions sont affectés aux chauffeurs au jour le jour.

erDiagram
    SOCIETE ||--o{ ENTREPOT : possede
    SOCIETE ||--o{ CAMION : possede
    SOCIETE ||--o{ CHAUFFEUR : embauche

    CAMION ||--o{ TRANSPORT : effectue
    ENTREPOT ||--o{ TRANSPORT : départ
    ENTREPOT ||--o{ TRANSPORT : arrivée
    CHAUFFEUR ||--o{ TRANSPORT : conduit


    SOCIETE {
        int id
        string nom
    }

    ENTREPOT {
        int id
        string ville
    }

    CAMION {
        string immatriculation
        string modele
    }

    CHAUFFEUR {
        text nom
    }

    TRANSPORT {
        int id
        date jour
        int chauffeur
        int camion
        int entrepot_depart
        int entrepot_arrivee
    }

Amélioration 1 : La nature du permis limite les camions que le chauffeur peut conduire.

Amélioration 2 : Suvant l'age et le permis des visites médicales périodiques sont nécessaires.

create table entrepot (
  id integer primary key,
  ville text
);

create table societe (
  id integer primary key,
  nom text
);

create table chauffeur (
  id integer primary key,
  nom text
);

create table camion (
  immatriculation text primary key,
  marque text,
  permis text,
  foreign key (permis) references permis(categorie)
);

create table permis (
  categorie text primary key,
  poids numeric
);


create table transport (
  id integer primary key,
  jour text,
  depart integer,
  arrivee integer,
  chauffeur integer,
  camion text,
  foreign key (depart) references entrepot(id),
  foreign key (arrivee) references entrepot(id),
  foreign key (chauffeur) references chauffeur(id),
  foreign key (camion) references camion(immatriculation),
  check (arrivee <> depart)
);

Insertion des données

Je ne spécifie pas les colonnes. Je dois mettre toutes les valeurs dans l'ordre des colonnes

insert into societe values (1, 'UPS');

Je spécifie uniquement la colonne nom. la colonne id qui est integer primary key est automatiquement incrémentée : 1, 2, 3, ...

insert into chauffeur (nom) values ('Albert');

Insérer 2 lignes en une seule requête

insert into chauffeur (nom) values
  ('Samia'),
  ('Samy');

Supprimer un enregistrement

delete from chauffeur where id = 5;

insert into permis (categorie, poids) values
  ('B', 3.5),
  ('C', 32),
  ('CE', 44);

insert into entrepot values
  (1, 'Saint Dié'),
  (2, 'Paris'),
  (3, 'Lyon'),
  (4, 'Marseille');

insert into camion (immatriculation, marque) values
  ('AA123BB', 'Volvo'),
  ('ZZ342VD', 'Renault'),
  ('SP457MP', 'Mercedes'),
  ('XQ876YE', 'Volvo');

-- modification de table
-- ajouter une colonne pour le permis
alter table camion
  add column permis text;

update camion set permis = 'B'
 where immatriculation  = 'ZZ342VD';

update camion set permis = 'C'
 where immatriculation  = 'SP457MP';

update camion set permis = 'CE'
 where immatriculation  = 'AA123BB';

update camion set permis = 'CE'
 where immatriculation  = 'XQ876YE';

-- 1 = Saint Dié
-- 3 = Lyon
-- 2 = Samia
insert into transport (id, jour, depart, arrivee,
  chauffeur, camion)
values (1, '2025-09-19', 1, 3, 2, 'SP457MP')

insert into transport (id, jour, depart, arrivee,
  chauffeur, camion)
values (50, '2030-09-19', 100, 30, 20, 'XXXXXXX')

drop table camion ;

Activer le contrôle des liens

pragma foreign_keys = ON;

insert into camion (immatriculation, marque, permis)
  values ('DF244JG', 'Mercedes', 'Z');

Supprimer une table

drop table transport ;