Bases de Données / Databases

Site Web de l'équipe BD du LIP6 / LIP6 DB Web Site

Outils pour utilisateurs

Outils du site


site:enseignement:master:bium:tme1

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 ;
site/enseignement/master/bium/tme1.txt · Dernière modification: 07/02/2022 16:05 par amine