star-schema.sql

DROP DATABASE IF EXISTS NORTHWIND_DWH; 
CREATE DATABASE NORTHWIND_DWH;         
USE NORTHWIND_DWH; 


#DROP TABLE IF EXISTS kunde_dim;
CREATE TABLE kunde_dim 
(
KUNDE_ID_DIM   		INT(11) NOT NULL AUTO_INCREMENT,
CUSTOMER_ID			INT(11) NOT NULL,
COMPANY             VARCHAR(50) NULL DEFAULT NULL,
FIRST_NAME         	VARCHAR(50) NULL DEFAULT NULL,
LAST_NAME          	VARCHAR(50) NULL DEFAULT NULL,
CONSTRAINT PK_KUNDE_DIM PRIMARY KEY (KUNDE_ID_DIM)
);

  
#DROP TABLE IF EXISTS produkt_dim;
CREATE TABLE produkt_dim
(
PRODUKT_ID_DIM		INT(11) NOT NULL AUTO_INCREMENT,
PRODUKT_ID			INT(11) NOT NULL,
PRODUKT_CODE		VARCHAR(25) NULL DEFAULT NULL,
PRODUKT_NAME		VARCHAR(50) NULL DEFAULT NULL,
STANDARD_COST		DECIMAL(19,4) NULL DEFAULT '0.0000',
CONSTRAINT PK_PRODUKT_DIM PRIMARY KEY (PRODUKT_ID_DIM)
);


#DROP TABLE IF EXISTS zeit_dim;
CREATE TABLE zeit_dim
(
ZEIT_ID_DIM    		INT(11) NOT NULL AUTO_INCREMENT,
ORDER_DATE         	DATE,
ORDER_DAY	  		INTEGER,
ORDER_MONTH			INTEGER,
ORDER_YEAR			INTEGER,
CONSTRAINT PK_ZEIT_DIM PRIMARY KEY (ZEIT_ID_DIM)
);


#DROP TABLE IF EXISTS region_dim;
CREATE TABLE region_dim
(
REGION_ID_DIM      	INT(11) NOT NULL AUTO_INCREMENT,
#REGION_ID			INT(11),
CITY				VARCHAR(50) NULL DEFAULT NULL,
STATE_PROVINCE		VARCHAR(50) NULL DEFAULT NULL,
COUNTRY_REGION		VARCHAR(50) NULL DEFAULT NULL,
CONSTRAINT PK_REGION_DIM PRIMARY KEY (REGION_ID_DIM)
);


#DROP TABLE IF EXISTS bezahlung_dim;
CREATE TABLE bezahlung_dim
(
BEZ_ID_DIM     		INT(11) NOT NULL AUTO_INCREMENT,
#BEZ_ID				INT(11),
PAYMENT_TYPE		VARCHAR(50) NULL DEFAULT NULL,
CONSTRAINT PK_BEZAHLUNG_DIM PRIMARY KEY (BEZ_ID_DIM)
);



#DROP TABLE IF EXISTS verkauf_fact;
CREATE TABLE verkauf_fact
(
KUNDE_ID_DIM		INT(11) NOT NULL,
PRODUKT_ID_DIM 		INT(11) NOT NULL,
ZEIT_ID_DIM  		INT(11) NOT NULL,
REGION_ID_DIM		INT(11) NOT NULL,
BEZ_ID_DIM          INT(11) NOT NULL,
BRUTTO_ERLOES		INTEGER,
NETTO_ERLOES		INTEGER,
KOSTEN				INTEGER,
GEWINN      		INTEGER
);
 
 
/*
ALTER TABLE verkauf_fact 
ADD CONSTRAINT verkauf_kunde_fk 
FOREIGN KEY (KUNDE_ID_DIM) REFERENCES kunde_dim (KUNDE_ID_DIM);

ALTER TABLE verkauf_fact 
ADD CONSTRAINT verkauf_produkt_fk 
FOREIGN KEY (PRODUKT_ID_DIM) REFERENCES produkt_dim (PRODUKT_ID_DIM);

ALTER TABLE verkauf_fact 
ADD CONSTRAINT verkauf_zeit_fk 
FOREIGN KEY (ZEIT_ID_DIM) REFERENCES zeit_dim (ZEIT_ID_DIM);

ALTER TABLE verkauf_fact 
ADD CONSTRAINT verkauf_region_fk 
FOREIGN KEY (REGION_ID_DIM) REFERENCES region_dim (REGION_ID_DIM);

ALTER TABLE verkauf_fact 
ADD CONSTRAINT verkauf_bezahlung_fk 
FOREIGN KEY (BEZ_ID_DIM) REFERENCES bezahlung_dim (BEZ_ID_DIM);
*/