TME1

création du schéma du datawarehouse du TD (exercice achats)

/*dimension produits*/
CREATE OR REPLACE VIEW Produits AS
SELECT NUMP, NOMP, 'unknown' AS CATPROD
FROM PRODUIT
UNION
SELECT CODEPROD, DESCPROD , CATPROD 
FROM PRODACHATS ;
 
/*dimension dates*/
CREATE  OR REPLACE  VIEW calendar AS
SELECT DAY_OF_YEAR (DATEACHAT) AS codeDate,   DATEACHAT, WEEK (DATEACHAT) AS WEEK, MONTH(DATEACHAT) AS MONTH
FROM  ACHAT 
UNION
SELECT DAY_OF_YEAR (DATEACHAT) AS codeDate,   DATEACHAT, WEEK (DATEACHAT) AS WEEK, MONTH(DATEACHAT) AS MONTH 
FROM  PRODACHATS  ;
 
/*localisation*/
 
CREATE  OR REPLACE  VIEW rlocalisation AS
SELECT VILLE,  PROVINCE,  PAYS 
FROM FOURNISSEUR ;
 
CREATE  OR REPLACE  VIEW localisation AS
SELECT ROWNUM ( ) AS codeLoc, VILLE,  PROVINCE,  PAYS 
FROM rlocalisation ;