www.pudn.com > WSMS.rar > wsms.sql

/*==============================================================*/ 
/* Database name:  WSMS                                         */ 
/* DBMS name:      ORACLE Version 9i                            */ 
/* Created on:     2006-8-7 9:32:41                             */ 
/*==============================================================*/ 
 
 
drop table ForecastCity cascade constraints 
/ 
 
 
drop table Provision cascade constraints 
/ 
 
 
drop table ServiceInfo cascade constraints 
/ 
 
 
drop table CityInfo cascade constraints 
/ 
 
 
drop sequence S_ProvisionId 
/ 
 
 
create sequence S_ProvisionId 
increment by 1 
start with 1 
nocycle 
/ 
 
 
/*==============================================================*/ 
/* Table: CityInfo                                              */ 
/*==============================================================*/ 
 
 
create table CityInfo  ( 
   CityId               VARCHAR2(6)                      not null, 
   CityName             VARCHAR2(20), 
   constraint PK_CITYINFO primary key (CityId) 
) 
/ 
 
 
comment on table CityInfo is 
'城市编码对照表' 
/ 
 
 
comment on column CityInfo.CityId is 
'城市编码' 
/ 
 
 
comment on column CityInfo.CityName is 
'城市名称' 
/ 
 
 
/*==============================================================*/ 
/* Table: ServiceInfo                                           */ 
/*==============================================================*/ 
 
 
create table ServiceInfo  ( 
   ServiceId            NUMBER(6)                        not null, 
   Name                 VARCHAR2(100), 
   PriceInfo            VARCHAR2(50), 
   ServiceDesc          VARCHAR2(200), 
   FreqDesc             VARCHAR2(50), 
   Example              VARCHAR2(200), 
   constraint PK_SERVICEINFO primary key (ServiceId) 
) 
/ 
 
 
comment on column ServiceInfo.ServiceId is 
'业务编码' 
/ 
 
 
comment on column ServiceInfo.Name is 
'业务名称' 
/ 
 
 
comment on column ServiceInfo.PriceInfo is 
'价格说明' 
/ 
 
 
comment on column ServiceInfo.ServiceDesc is 
'业务说明' 
/ 
 
 
comment on column ServiceInfo.FreqDesc is 
'发送频率描述' 
/ 
 
 
comment on column ServiceInfo.Example is 
'业务内容实例' 
/ 
 
 
/*==============================================================*/ 
/* Table: Provision                                             */ 
/*==============================================================*/ 
 
 
create table Provision  ( 
   ProvisionId          NUMBER(10)                       not null, 
   ServiceId            NUMBER(6), 
   UserMsisdn           VARCHAR2(11)                     not null, 
   ProvisionTime        DATE, 
   constraint PK_PROVISION primary key (ProvisionId), 
   constraint FK_PROVISIO_REFERENCE_SERVICEI foreign key (ServiceId) 
         references ServiceInfo (ServiceId) 
) 
/ 
 
 
comment on table Provision is 
'用户订购表' 
/ 
 
 
comment on column Provision.ProvisionId is 
'定购标识' 
/ 
 
 
comment on column Provision.ServiceId is 
'业务编码' 
/ 
 
 
comment on column Provision.UserMsisdn is 
'用户电话号码' 
/ 
 
 
comment on column Provision.ProvisionTime is 
'定购时间' 
/ 
 
 
/*==============================================================*/ 
/* Table: ForecastCity                                          */ 
/*==============================================================*/ 
 
 
create table ForecastCity  ( 
   ProvisionId          NUMBER(10)                       not null, 
   CityId               VARCHAR2(6), 
   constraint PK_FORECASTCITY primary key (ProvisionId), 
   constraint FK_FORECAST_REFERENCE_PROVISIO foreign key (ProvisionId) 
         references Provision (ProvisionId), 
   constraint FK_FORECAST_REFERENCE_CITYINFO foreign key (CityId) 
         references CityInfo (CityId) 
) 
/ 
 
 
comment on table ForecastCity is 
'天气预报业务订约城市对照' 
/ 
 
 
comment on column ForecastCity.ProvisionId is 
'定购标识' 
/ 
 
 
comment on column ForecastCity.CityId is 
'城市标识' 
/