Modèle d'ordre et Example

Définition d’un “modèle d’ordre” :

Un modèle d’ordre est un gabarit qui décrit la structure générale d’un ensemble d’actions ou de requêtes SQL (ou pseudo-SQL) à exécuter pour accomplir une tâche spécifique définie par un cahier des charges. Il aide à formaliser la façon dont les interactions avec la base de données doivent être réalisées, en séparant les paramètres d’entrée, l’action à réaliser, et les résultats attendus.

Le modèle d’ordre n’est pas seulement une requête SQL simple, mais une abstraction qui peut être réutilisée dans des scénarios similaires, en changeant les paramètres d’entrée et adaptant les résultats à chaque situation.

Objectifs pédagogiques :

• Généralisation : Il permet de généraliser des actions, en montrant qu’un même modèle peut être réappliqué avec des paramètres différents.
• Cadre : Il fournit un cadre clair pour rédiger des requêtes SQL tout en respectant les contraintes du cahier des charges.
• Séparation des préoccupations : Il dissocie les paramètres, les actions à réaliser, et les résultats, afin de rendre la structure plus compréhensible et modulaire.

Structure typique d’un modèle d’ordre :

1. Paramètres d’entrée : Les éléments fournis par l’utilisateur ou nécessaires à l’exécution du traitement. Par exemple, des identifiants, des noms ou des dates.

2. Action : La commande ou série de commandes à exécuter pour accomplir la tâche, en fonction des paramètres. Cela inclut souvent des sélections, insertions, modifications ou suppression dans les tables de la base de données.

3. Retour(s) : Ce que la requête ou le traitement renvoie. Cela peut être des lignes de résultats (pour une requête SELECT), des identifiants (lors d’une insertion), ou des statuts (dans le cas d’un échec ou d’une réussite).

Exemple de modèle d’ordre :

Prenons l’exemple du traitement de la réservation de séjour par un client.

• Paramètres : idc (identifiant du client), ville (nom de la ville), jour (date souhaitée pour le séjour).
• Action :
  • Chercher un village dans la ville donnée avec le prix le plus élevé.
  • Si un village est trouvé, créer une ligne dans la table Séjour et diminuer l’avoir du client.
  • Sinon, ne rien faire.
• Retour : Identifiant du village et identifiant du séjour, ou -1 si aucune réservation n’a été faite.

select idv
  from village
  where ville = :ville
  order by prix desc
  fetch first 1 row only;

insert into sejour(idc, idv, jour)
  values (:idc, :idv, :jour);

update client
  set avoir = avoir - (select prix from village where idv = :idv)
  where idc = :idc;

Pourquoi enseigner les modèles d’ordre ?

• Réutilisabilité : Apprendre à réutiliser des structures similaires avec des données différentes, ce qui renforce la compréhension des concepts de modularité et de réduction de la duplication.
• Structure : Le modèle d’ordre impose une structure claire, essentielle pour des projets complexes où plusieurs acteurs interagissent avec la base de données.
• Cahier des charges : Il s’agit d’un moyen d’assurer que le développement respecte les exigences du projet en fournissant un cadre d’exécution rigoureux et prévisible.

Cahier des charges : Emprunts, réservations et retours de livres

Une bibliothèque permet à ses clients d’emprunter, réserver et rapporter des livres.

Données

La base suivante décrit les informations correspondantes.

• Livre(idl, titre, auteur)

• Exemplaire(ide, idl, état)

• Client(idc, nom)

• NbEmpruntsPlusRésas(nbMax)

• Emprunt(ide, idc)

• Réservation(idl, idc, jour)

• Une œuvre (un livre) a un identifiant, un titre et un auteur.

• Un exemplaire physique d’une œuvre a un identifiant, un identifiant d’œuvre, et un état : neuf, bon ou vieux.

• Un client a un identifiant et un nom.

• La somme du nombre d’emprunts et du nombre de réservations d’un client est limitée. Ce nombre limite est l’unique entier dans la table NbEmpruntsPlusRésas.

• Un emprunt a un identifiant d’exemplaire et un identifiant de client.

• Une réservation a un identifiant d’œuvre, un identifiant de client, et la date à laquelle a été faite la réservation (un entier entre 1 et 365).

Exemple

Le livre Pays de neige est une œuvre de Kawabata. La bibliothèque possède un exemplaire, d’identifiant 10, de cette œuvre, en état : bon. Cet exemplaire a été emprunté par le client 101. Rita a réservé cette œuvre le 31 décembre.

Livre(1, Pays de neige, Kawabata)  
Exemplaire(10, 1, bon)  
Client(100, Rita)  
NbEmpruntsPlusRésa(7)  
Emprunt(10, 101)  
Réservation(1, 100, 365)

Précisions et restrictions

• Les identifiants (entiers) sont uniques.
• Une œuvre a un unique auteur.
• Plusieurs œuvres peuvent avoir le même titre.
• Deux auteurs n’ont pas le même nom.
• Un auteur ne peut pas écrire deux œuvres avec le même titre.
• Il peut y avoir plusieurs exemplaires d’une œuvre, ou aucun.
• Plusieurs clients peuvent avoir le même nom.
• Un client ne peut pas réserver deux fois la même œuvre, ni réserver une œuvre qu’il a empruntée.
• On ne peut réserver que des œuvres dont aucun exemplaire n’est disponible.
• Il peut y avoir d’autres contraintes Oracle, mais on ne les connaît pas.

Accès aux données

• Les employés ont des comptes SGBD sur la base (les logins ne sont pas les noms), et il y a un unique client.
• Un employé peut consulter les livres et en ajouter, consulter les exemplaires et en ajouter, et consulter les emprunts mais sous la forme plus lisible : titre, nom (du client), sans ide, idc.
• Un client peut effectuer les traitements 1, 2, et 3, consulter tous les livres, et consulter les titres (uniquement) qu’il a réservés. Son identifiant suffit comme authentification. Rien d’autre n’est possible.

Fonctionnement

Les traitements ne vérifieront pas les contraintes, ni si les paramètres sont corrects.

Traitement 1

L’inscription d’un client est faite par lui-même comme suit. Il donne son nom, une nouvelle ligne est alors créée dans Client, et il obtient son identifiant.

• Paramètre(s) : nom.
• Retour(s) : identifiant client, et nombre de clients ayant ce nom (y compris lui-même).

Traitement 2

L’emprunt/réservation est fait par un client comme suit. Il arrive avec en tête un identifiant d’œuvre. S’il reste des exemplaires de cette œuvre disponibles, l’un quelconque est choisi (par exemple le premier qui vient), et la ligne correspondante est insérée dans Emprunt. Sinon une réservation est créée.

• Paramètre(s) : identifiants client et œuvre, et jour.
• Retour(s) : identifiant exemplaire emprunté ; sinon -1 si réservation faite.

Traitement 3

Le retour est fait par un client comme suit. Lorsqu’un exemplaire physique est rapporté, l’emprunt correspondant est supprimé. S’il y a des réservations pour l’œuvre correspondante, l’une quelconque parmi celles de date la plus ancienne est transformée en emprunt et supprimée.

• Paramètre(s) : identifiant exemplaire rapporté.
• Retour(s) : identifiant client et jour si réservation transformée en emprunt ; et sinon -1, -1.

Traitement 4

L’archivage d’un emprunt est effectué automatiquement (pas par les employés ni les clients) en cas de destruction, avec l’état de son exemplaire.

• Paramètre(s) : néant.
• Retour(s) : néant.

Liste des actions :

Programmeur P :

0. (Action 0) Création des tables :
   • livre, exemplaire, client, nbEmpruntPlusResa, emprunt, reservation, archive (colonnes emprunt + état)

Employés e1, …, en :

1. (Action 1) Consulter livres
2. Créer livres
3. Consulter exemplaires
4. Créer exemplaires
5. Consulter les emprunts sous forme lisible

Clients c1, …, cm :

6. Traitement 1
7. Traitement 2
8. Traitement 3
9. Consulter livres
10. Consulter ses réservations

Système :

1. Traitement 4 (Quand destruction d’un emprunt)

Ordres tapés par le programmeur / DBA :

Action 0 :

drop table livre;
drop table exemplaire;
drop table client;
drop table nbEmpruntsPlusResas;
drop table emprunt;
drop table reservation;

create table livre(
  idl int,
  titre varchar(25),
  auteur varchar(10),
);

-- rem : varchar2 pas plus 10 caractères, sinon affichage select illisible

create table exemplaire(
  ide int,
  idl int,
  etat varchar(5) default 'neuf'
);

create table client(
  idc int,
  nom varchar(10)
);

create table nbEmpruntsPlusResas(
  nbmax int
);

create table emprunt(
  ide int,
  idc int
);

create table reservation(
  idl int,
  idc int,
  jour int
);

Remarque :

Pas de modèle d’ordre ici, ces actions ne sont tapées qu’une fois.

Employés :

Action 1 :

select * from livre;

Modèle d’ordre :

• Paramètres : colonne et valeur de sélection
• Retour : les lignes

Action 2 :

insert into livre values(1, 'Pays de neige', 'Kawabata');
insert into livre values(2, 'Les planches courbes', 'Bonnefoy');
insert into livre values(3, 'La Terre nous est étroite', 'Darwich');

Modèle d’ordre :

• Paramètres : i, t, a
• Retour : néant

insert into livre values(i, t, a);

Action 3 :

select * from exemplaire;

Action 4 :

insert into exemplaire(ide, idl) values(10, 1);
insert into exemplaire values(11, 1, 'bon');
insert into exemplaire values(12, 1, 'vieux');
insert into exemplaire values(13, 2);
insert into exemplaire values(14, 3, 'vieux');

Action 5 :

select titre, nom
  from emprunt, exemplaire, livre, client
  where emprunt.ide = exemplaire.ide
    and exemplaire.idl = livre.idl
    and emprunt.idc = client.idc;

Clients :

Action 6 :

Traitement 1 : Exemple sur Rita :

• Choix d’un identifiant, disons 100

Remarque : Identifiant pas paramètre, donc choisi dans traitement

insert into client values(100, 'Rita');
select count(*)
  from client
  where nom = 'Rita';
-- Renvoie : 1

Exemple sur une autre Rita :

• Choix d’un identifiant, disons 101 Remarque : Identifiant pas paramètre, donc choisi dans traitement

insert into client values(101, 'Rita');
select count(*)
  from client
  where nom = 'Rita';
-- Renvoie : 2

Exemple sur Riton :

• Choix d’un identifiant, disons 102

insert into client values(102, 'Riton');
select count(*)
  from client
  where nom = 'Riton';
-- Renvoie : 1

Modèle d’ordre :

• Traitement (n) = choix d’un nouvel identifiant unique, disons i

insert into client values(i, n);
select count(*)
  from client
  where nom = n;
-- Renvoie : k
-- Return k;

Action 7 :

Traitement 2 : Exemple sur Rita, idc 100, jour 45, veut œuvre 3, il y a exemplaire libre :

select ide
  from exemplaire
  where idl = 3
    and ide not in (select ide from emprunt);
-- Renvoie : 14
insert into emprunt values(14, 100);

Exemple sur Riton, idc 102, jour 46, veut œuvre 3, pas d’exemplaire libre :

select ide
  from exemplaire
  where idl = 3
    and ide not in (select ide from emprunt);
-- Renvoie : vide
insert into reservation values(3,102,45);

Exemple sur Rita, idc 100, jour 47, veut œuvre 1, il y a exemplaire libre :

select ide
  from exemplaire
  where idl = 1
    and ide not in (select ide from emprunt);

Modèle d’ordre :

Traitement 2 (i,il,j) =

select ide
  from exemplaire
  where idl = il
    and ide not in (select ide from emprunt);
-- Renvoie : ie ou vide
    si ie alors
      insert into emprunt values(ie,i);
    return ie;
    sinon — vide
      insert into reservation values(il,i,j);
    return –1;

Remarque :

Exécuter action 10 maintenant pour voir resa Riton avant détruite.

Action 8 :

Traitement 3 : Exemple sur Rita, idc 100, rapporte exemplaire 10, pas de réservation :

delete emprunt
  where ide = 10;
select idl, idc, jour
  from exemplaire, reservation
  where exemplaire.ide = 10
    and exemplaire.idl = reservation.idl
  order by jour croissant;
-- Renvoie : vide

Exemple sur Rita, idc 100, rapporte exemplaire 14, il y a réservation :

delete emprunt
  where ide = 14;
select idl, idc, jour
  from exemplaire, reservation
  where exemplaire.ide = 10
    and exemplaire.idl = reservation.idl
  order by jour croissant;
-- Renvoie : idl 3, idc 102, jour 46
delete reservation
  where idl = 3
    and idc = 102;
insert into emprunt values(14, 102);

Modèle d’ordre : Traitement 3 (ie) =

delete emprunt
  where ide = ie;
select idl, idc, jour
  from exemplaire, reservation
  where exemplaire.ide = ie
    and exemplaire.idl = reservation.idl
  order by jour croissant;
-- Renvoie : il, ic, j ou vide
    si il, ic alors
      delete reservation
        where idl = il;

-- and idc = ic;
-- insert into emprunt values(il, ic);
-- return ic, j;
-- sinon — vide
-- return –1;

Action 9 :

select * from livre;

Action 10 :

Exemple sur Riton, idc 102 :

select titre
  from reservation, livre
  where idc = 102
    and reservation.idl = livre.idl;

Modèle d’ordre : ConsulteReservations(i) =

select titre
  from reservation, livre
  where idc = i
    and reservation.idl = livre.idl;

Système

Action 11 :

À compléter :

select etat from exemplaire where ide = ie : e
insert archive(ie, ic, e);