数据仓库应用（1）：数据仓库模型设计
一、问题背景
某电子商务网站主要销售电子类产品，产品又分为几个大类别，包括：电脑类、手机类、键盘类等，每个类别内又细分为几个小类别，各类别下又有诸多的商品，每一个商品都有一个唯一的商品编号。用户可以通过注册成为会员来进行商品的下单购买。用户下单后会由系统自动产生一个唯一的订单号。
为该电子商务平台建立一个能够为不同年龄段的用户提供相应年龄段需求量最大的产品的数据仓库。以提供给该企业一个个性化产品的需求量分析方案，增强企业市场竞争力。
二、需求分析
目的：不同年龄段的用户对产品的需求数据分析，以提供更好的产品销售方案，方便向不同年龄段的用户提供适合的产品信息。
三、数据仓库体系结构图
四、主要主题域的概念模型图
五、雪花模型图
六、逻辑模型图
七、物理模型图
用户关系存储结构关系模型
商品关系存储结构关系模型
时间关系存储结构关系模型
地域关系存储结构关系模型
年龄关系存储结构关系模型
学历关系存储结构关系模型
销售事件存储结构关系模型
八、粒度模型图
用户对商品的下单记录
九、在SQL SERVER2005中建立数据仓库数据库的sql语句
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2005 */
/* Created on: 2014/3/4 11:07:22 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_USER')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_USER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_TIME')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_TIME
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_AREA')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_AREA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_EDU')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_EDU
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_TYPE')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_TYPE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_GOOD')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_GOOD
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tbl_order') and o.name = 'FK_TBL_ORDE_REFERENCE_TBL_AGE')
alter table tbl_order
drop constraint FK_TBL_ORDE_REFERENCE_TBL_AGE
go
if exists (select 1
from sysobjects
where id = object_id('tbl_age')
and type = 'U')
drop table tbl_age
go
if exists (select 1
from sysobjects
where id = object_id('tbl_area')
and type = 'U')
drop table tbl_area
go
if exists (select 1
from sysobjects
where id = object_id('tbl_edu')
and type = 'U')
drop table tbl_edu
go
if exists (select 1
from sysobjects
where id = object_id('tbl_goods')
and type = 'U')
drop table tbl_goods
go
if exists (select 1
from sysobjects
where id = object_id('tbl_order')
and type = 'U')
drop table tbl_order
go
if exists (select 1
from sysobjects
where id = object_id('tbl_time')
and type = 'U')
drop table tbl_time
go
if exists (select 1
from sysobjects
where id = object_id('tbl_type')
and type = 'U')
drop table tbl_type
go
if exists (select 1
from sysobjects
where id = object_id('tbl_user')
and type = 'U')
drop table tbl_user
go
/*==============================================================*/
/* Table: tbl_age */
/*==============================================================*/
create table tbl_age (
age_id char(10) not null,
age_area varchar(100) null,
constraint PK_TBL_AGE primary key (age_id)
)
go
/*==============================================================*/
/* Table: tbl_area */
/*==============================================================*/
create table tbl_area (
area_id char(10) not null,
country varchar(100) null,
province varchar(100) null,
city varchar(100) null,
constraint PK_TBL_AREA primary key (area_id)
)
go
/*==============================================================*/
/* Table: tbl_edu */
/*==============================================================*/
create table tbl_edu (
edu_id char(10) not null,
edu_class varchar(100) null,
constraint PK_TBL_EDU primary key (edu_id)
)
go
/*==============================================================*/
/* Table: tbl_goods */
/*==============================================================*/
create table tbl_goods (
goods_id char(10) not null,
g_name character varying(30) null,
g_price character varying(10) null,
type_pid character varying(10) null,
type_ppid character varying(10) null,
constraint PK_TBL_GOODS primary key (goods_id)
)
go
/*==============================================================*/
/* Table: tbl_order */
/*==============================================================*/
create table tbl_order (
user_id char(10) not null,
goods_id char(10) not null,
type_pid char(10) not null,
edu_id char(10) not null,
area_id char(10) not null,
age_id char(10) not null,
time_id char(10) not null,
order_num int null,
order_money numeric(10) null,
constraint PK_TBL_ORDER primary key (user_id, goods_id, type_pid, edu_id, age_id, area_id, time_id)
)
go
/*==============================================================*/
/* Table: tbl_time */
/*==============================================================*/
create table tbl_time (
time_id char(10) not null,
year int null,
quarter int null,
month int null,
day int null,
constraint PK_TBL_TIME primary key (time_id)
)
go
/*==============================================================*/
/* Table: tbl_type */
/*==============================================================*/
create table tbl_type (
type_pid char(10) not null,
type_ppid char(10) null,
t_name varchar(100) null,
constraint PK_TBL_TYPE primary key (type_pid)
)
go
/*==============================================================*/
/* Table: tbl_user */
/*==============================================================*/
create table tbl_user (
user_id char(10) not null,
user_name varchar(30) null,
birth datetime null,
constraint PK_TBL_USER primary key (user_id)
)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_USER foreign key (user_id)
references tbl_user (user_id)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_TIME foreign key (time_id)
references tbl_time (time_id)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_AREA foreign key (area_id)
references tbl_area (area_id)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_EDU foreign key (edu_id)
references tbl_edu (edu_id)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_TYPE foreign key (type_pid)
references tbl_type (type_pid)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_GOOD foreign key (goods_id)
references tbl_goods (goods_id)
go
alter table tbl_order
add constraint FK_TBL_ORDE_REFERENCE_TBL_AGE foreign key (age_id)
references tbl_age (age_id)
go