Des voitures et des hommes de collection
[45 minutes]
Soit le diagramme de classe UML suivant :
Question
A partir de ce modèle conceptuel établissez un modèle logique en relationnel-objet.
On utilisera des tables d'objets et les OID pour effectuer les références, ainsi qu'une table imbriquée pour gérer l'association N:M comme une collection de référence à des OID.
On privilégiera la table Voiture, qui sera au centre des requêtes posées à la BD.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
Type Personne : <
2
nom:string,
3
ddn:date,
4
=age():entier
5
>
6
tPersonne de Personne (#nom)
7
8
Type Conducteur : <
9
nom:string,
10
ddn:date,
11
typePermis:{A,B,E},12
datePermis:date
13
=age():entier
14
>
15
tConducteur de Conducteur (#nom)
16
17
Type RefConducteur : <refConducteur =>o tConducteur>
18
Type ListeRefConducteur : collection de <RefConducteur>
19
20
Type Voiture : <
21
type:string,
22
marque:string,
23
nbPortes:integer,
24
puissance:integer,
25
refPropriétaire =>o tPersonne,
26
refConducteurs:ListeRefConducteur
27
>
28
tVoiture de Voiture (#type, #marque)
Type Personne : <
nom:string,
ddn:date,
=age():entier
>
tPersonne de Personne (#nom)
Type Conducteur : <
nom:string,
ddn:date,
typePermis:{A,B,E},
datePermis:date
=age():entier
>
tConducteur de Conducteur (#nom)
Type RefConducteur : <refConducteur =>o tConducteur>
Type ListeRefConducteur : collection de <RefConducteur>
Type Voiture : <
type:string,
marque:string,
nbPortes:integer,
puissance:integer,
refPropriétaire =>o tPersonne,
refConducteurs:ListeRefConducteur
>
tVoiture de Voiture (#type, #marque)Question
Proposer une implémentation sous Oracle de votre modèle logique (sans implémenter les méthodes et sans utiliser l'héritage de type).
Solution
CTRL+C pour copier, CTRL+V pour coller
1
CREATE OR REPLACE TYPE Personne AS OBJECT (
2
nom varchar(20),
3
ddn date,4
MEMBER FUNCTION age RETURN number
5
);
6
/
7
CREATE TABLE tPersonne OF Personne (
8
PRIMARY KEY (nom)
9
);
10
11
CREATE OR REPLACE TYPE Conducteur AS OBJECT(
12
nom varchar(20),
13
ddn date,14
typePermis char(1),
15
datePermis date,16
MEMBER FUNCTION age RETURN number
17
);
18
/
19
CREATE TABLE tConducteur OF Conducteur (
20
PRIMARY KEY (nom),
21
CHECK (typePermis IN ('A','B','E'))
22
);
23
24
CREATE OR REPLACE TYPE RefConducteur AS OBJECT (refConducteur REF Conducteur);
25
/
26
CREATE OR REPLACE TYPE ListeRefConducteur AS TABLE OF RefConducteur;
27
/
28
CREATE OR REPLACE TYPE Voiture AS OBJECT (
29
type varchar(20),
30
marque varchar(20),
31
nbPortes number,32
puissance number,33
refProprietaire REF Personne,34
refConducteurs ListeRefConducteur
35
);
36
/
37
CREATE TABLE tVoiture OF Voiture (
38
PRIMARY KEY (type, marque),
39
SCOPE FOR (refProprietaire) IS tPersonne
40
)
41
NESTED TABLE refConducteurs STORE AS ntRefConducteurs;
42
CREATE OR REPLACE TYPE Personne AS OBJECT (
nom varchar(20),
ddn date,
MEMBER FUNCTION age RETURN number
);
/
CREATE TABLE tPersonne OF Personne (
PRIMARY KEY (nom)
);
CREATE OR REPLACE TYPE Conducteur AS OBJECT(
nom varchar(20),
ddn date,
typePermis char(1),
datePermis date,
MEMBER FUNCTION age RETURN number
);
/
CREATE TABLE tConducteur OF Conducteur (
PRIMARY KEY (nom),
CHECK (typePermis IN ('A','B','E'))
);
CREATE OR REPLACE TYPE RefConducteur AS OBJECT (refConducteur REF Conducteur);
/
CREATE OR REPLACE TYPE ListeRefConducteur AS TABLE OF RefConducteur;
/
CREATE OR REPLACE TYPE Voiture AS OBJECT (
type varchar(20),
marque varchar(20),
nbPortes number,
puissance number,
refProprietaire REF Personne,
refConducteurs ListeRefConducteur
);
/
CREATE TABLE tVoiture OF Voiture (
PRIMARY KEY (type, marque),
SCOPE FOR (refProprietaire) IS tPersonne
)
NESTED TABLE refConducteurs STORE AS ntRefConducteurs;
Remarque :
On notera que le choix d'implémentation de fkConducteurs en collection de références à des OID nous empêche dans l'implémentation sous Oracle de définir le SCOPE FOR et donc de contraindre l'intégrité référentielle à la table tConducteur. Dans le cas présent ce n'est pas préjudiciable dans la mesure où seule la table tConducteur contient des objets Conducteur.
Question
Insérer une voiture, un propriétaire et deux conducteurs dans le base de données.
Solution
CTRL+C pour copier, CTRL+V pour coller
1
DECLARE 2
3
c1 REF Conducteur;4
c2 REF Conducteur;5
p1 REF Personne;6
7
BEGIN8
9
INSERT INTO tPersonne (nom) VALUES ('Al');
10
11
SELECT REF(p) INTO p1
12
FROM tPersonne p13
WHERE nom='Al';
14
15
INSERT INTO tConducteur (nom, typePermis) VALUES ('Bob', 'A');
16
17
SELECT REF(c) INTO c1
18
FROM tConducteur c19
WHERE nom='Bob';
20
21
INSERT INTO tConducteur (nom, typePermis) VALUES ('Charlie', 'B');
22
23
SELECT REF(c) INTO c2
24
FROM tConducteur c25
WHERE nom='Charlie';
26
27
INSERT INTO tVoiture (type, marque, refProprietaire, refConducteurs)
28
VALUES ('Superstar', 'S12', p1, ListeRefConducteur(RefConducteur(c1),RefConducteur(c2)));
29
30
END;DECLARE
c1 REF Conducteur;
c2 REF Conducteur;
p1 REF Personne;
BEGIN
INSERT INTO tPersonne (nom) VALUES ('Al');
SELECT REF(p) INTO p1
FROM tPersonne p
WHERE nom='Al';
INSERT INTO tConducteur (nom, typePermis) VALUES ('Bob', 'A');
SELECT REF(c) INTO c1
FROM tConducteur c
WHERE nom='Bob';
INSERT INTO tConducteur (nom, typePermis) VALUES ('Charlie', 'B');
SELECT REF(c) INTO c2
FROM tConducteur c
WHERE nom='Charlie';
INSERT INTO tVoiture (type, marque, refProprietaire, refConducteurs)
VALUES ('Superstar', 'S12', p1, ListeRefConducteur(RefConducteur(c1),RefConducteur(c2)));
END;Question
Afficher toutes les voitures avec les personnes qui les possèdent et les conduisent.