Compilation Architecture BDD Réseau

M1

Exercice - CM n°3

Table bien :

Table proprio :

Les biens du proprio Dupont :

SELECT B.* FROM Bien B, Proprio P
WHERE B.numProprio P.numProprio
AND P.nom = "Dupont";

=> Objet / relationnel

Bien_t et Proprio_t avec Bien_t qui pointe sur un Proprio_t (attribut proprio pour un bien).

-> 2 vues objet / relationnelles ovBien et ovProprio

1)

SELECT OB.numBien, OB.description
FROM ovBien OB
WHERE OB.proprio.nom = "Dupont";

Il y a 2 types à créer :

CREATE TYPE proprio_t AS OBJECT (
  numProprio NUMBER,
  nom        VARCHAR2(20),
  prenom     VARCHAR2(20),
);

CREATE TYPE bien_t AS OBJECT (
  ...
  proprio REF proprio_t
);

Ensuite, on crée les vues :

CREATE VIEW ov_proprio OF proprio_t
WITH OBJECT oid(num_proprio) AS
  SELECT * FROM proprio;

CREATE VIEW ov_bien OF bien_t
WITH OBJECT oid(numBien) AS
  SELECT numBien, description, adresse ...
  MAKE_REF(ov_proprio, P.num_proprio)
  FROM proprio P, Bien B
  WHERE  P.num_proprio = B.num_proprio;

Création des types

Pour les employés et les biens :

CREATE TYPE employe_t AS OBJECT (
  numeploye NUMBER,
  nom       VARCHAR2(20),
  prenom    VARCHAR2(20)
);

CREATE TYPE bien_t;

Pour les visites et les clients :

CREATE TYPE VisiteClient_t AS OBJECT (
  employe REF employe_t,
  bien    REF bien_t,
  dateV   date
);

CREATE TYPE VisiteCient_TI_T AS TABLE OF VisiteClient_t;

CREATE TYPE Client_t AS OBJECT (
  numC    number,
  nom     VARCHAR2(20),
  prenom  VARCHAR2(20),
  Visites VisiteClient_TI_t
);

Pour les visites de biens :

CREATE TYPE VisiteBien_t AS OBJECT (
  client  REF client_t,
  employe REF employe_t,
  dateV       date
);

CREATE TYPE VisiteBien_TI_t AS TABLE OF VisiteBien_t;

CREATE OR REPLACE TYPE Bien_t AS OBJECT (
  numBien      NUMBER,
  description  VARCHAR2(100),
  prix         NUMBER,
  adresse      VARCHAR2(50),
  Vusute       VisiteBien_t
);

Création des tables

CREATE TABLE employe OF employe_t (PRIMARY KEY (numemploye));

CREATE TABLE client of Client_t (PRIMARY KEY (numc))
  NESTED TABLE Visites STORE AS tabVisitesClient;

CREATE TABLE Bien of Bien_t (PRIMARY KEY (numBien))
  NESTED TABLE Visites STORE AAS tabVisiteBien;

Insertion du bien n°1 avec 2 visites

INSERT INTO employe VALUES(1, 'toto', 'alain');

INSERT INTO client VALUES(1, "Dupont", "Pierre", VisiteClient_TI_t());

INSERT INTO client VALUES(2, "Martin", "Jean", VisiteClient_TI_t());

INSERT INTO Bien(1, "Maison 100m2", 150 000, "Valenciennes", VisiteBien_TI_t());

INSERT INTO TABLE(SELECT visites FROM Bien WHERE numB = 1)
  (SELECT REF(c), REF(e), '10-09-2017' FROM client c, employe e
    WHERE c.numC = 1 AND e.numEmploye = 1)
  UNION
  (SELECT REF(c), REF(e), '12-09-2017' FROM client c, employe e
    WHERE c.numC = 2 and e.numEmploye = 1);

-- Maintenant, pour rendre notre table cohérente et ne pas avoir seulement
-- les visites au niveau des biens, il faut que les clients répertorient
-- aussi les visites qu'ils ont effectués de leur côté.

INSERT INTO TABLE(SELECT Visites FROM client WHERE numC = 1)
  SELECT REF(e), REF(b), '10-09-2017' FROM employe e, Bien b
    WHERE e.numEmploye = 1 AND b.numB = 1;

INSERT INTO TABLE(SELECT Visites FROM client WHERE numC = 2)
  SELECT REF(e), REF(b), '12-09-2017' FROM employe e, Bien b
    WHERE b.numB = 1 AND e.numEmploye = 1;

Ecriture de requêtes

• Les clients ayant visité le bien n°3 :

  SELECT V.client.numC, V.client.nom FROM
    TABLE(SELECT Visites FROM Biens b WHERE b.numb = 3) V;

• Les biens visités par le client 1 en septembre 2017 :

  SELECT V.Bien.description FROM
    TABLE(SELECT Visites FROM Client cWHERE c.numc = 1) V
  WHERE V.dateV >= '01-09-2017'
  AND V.dateV < '01-10-2017';