Scribd
Upload
11 views3 pages

Esquema SQL para Datawarehouse

The document contains SQL scripts to create multiple dimension tables and fact tables to build a data warehouse schema for tracking information about countries, provinces, companies, fish farms, harvests, shipments and sizes. Tables are created for dimensions like country, province, company, month, and shipment run. Fact tables are also created for harvest, shipment run details, and correlated data.

Uploaded by

dennys coronel
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
11 views3 pages

Esquema SQL para Datawarehouse

The document contains SQL scripts to create multiple dimension tables and fact tables to build a data warehouse schema for tracking information about countries, provinces, companies, fish farms, harvests, shipments and sizes. Tables are created for dimensions like country, province, company, month, and shipment run. Fact tables are also created for harvest, shipment run details, and correlated data.

Uploaded by

dennys coronel
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SCRIPT SQL DEL ESQUEMA DE LA DATAWAREHOUSE

CREATE TABLE IF NOT EXISTS dim_pais


(
pais_cod int
, pais_nom varchar(100)
)
;

CREATE TABLE IF NOT EXISTS dim_provincia


(
prov_cod int
, prov_nom varchar(150)
, pais_cod int
)
;

CREATE TABLE IF NOT EXISTS dim_canton


(
can_cod int
, can_nom varchar(150)
, prov_cod int
)
;

CREATE TABLE IF NOT EXISTS dim_empresa


(
emp_cod int
, emp_nom varchar(100)
, emp_nom_cam varchar(100)
, pais_cod int
, prov_cod int
, can_cod int
)
;

CREATE TABLE IF NOT EXISTS piscina


(
pis_cod int
, pis_nom varchar(20)
, pis_num_hect DOUBLE PRECISION
, emp_cod int
)
;

CREATE TABLE IF NOT EXISTS dim_mes


(
mes_codigo int
, mes varchar(20)
, trimestre varchar(10)
, semestre varchar(10)
)
;
CREATE TABLE IF NOT EXISTS th_cosecha
(
cos_cant_kg DOUBLE PRECISION
, cos_pre_kg DOUBLE PRECISION
, cor_cod int
, pis_cod int
, tal_cod int
, emp_cod int
)
;

CREATE TABLE IF NOT EXISTS dim_corrida


(
cor_cod int
, cor_fec_ini TIMESTAMP
, cor_fec_fin TIMESTAMP
, cor_est varchar(20)
, anio int
, mes_codigo int
)
;

CREATE TABLE IF NOT EXISTS th_corrida_piscina


(
cp_num_larv int
, cp_cos_inv DOUBLE PRECISION
, cp_total_ing DOUBLE PRECISION
, emp_cod int
, pis_cod int
, cor_cod int
)
;

CREATE TABLE IF NOT EXISTS dim_tallas


(
tall_cod int
, tall_nombre varchar(100)
, tall_cam varchar(100)
)
;
SCRIPT SQL VISTA vi_Piscina

SELECT e.emp_nom AS empresa,


e.emp_nom_cam AS camaronera,
pa.pais_nom AS pais,
pr.prov_nom AS provincia,
c.can_nom AS canton,
pi.pis_nom AS piscina,
pi.pis_num_hect AS hectareas
FROM dim_canton c,
dim_empresa e,
dim_pais pa,
dim_provincia pr,
piscina pi
WHERE c.can_cod = e.can_cod AND e.emp_cod = pi.emp_cod AND
pa.pais_cod = e.pais_cod AND pr.prov_cod = e.prov_cod
ORDER BY e.emp_nom, pi.pis_nom;

ALTER TABLE "vi_Piscina"


OWNER TO postgres;

You might also like