/*==============================================================*/
/* 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
'業(yè)務(wù)編碼'
/


comment on column ServiceInfo.Name is
'業(yè)務(wù)名稱'
/


comment on column ServiceInfo.PriceInfo is
'價(jià)格說明'
/


comment on column ServiceInfo.ServiceDesc is
'業(yè)務(wù)說明'
/


comment on column ServiceInfo.FreqDesc is
'發(fā)送頻率描述'
/


comment on column ServiceInfo.Example is
'業(yè)務(wù)內(nèi)容實(shí)例'
/


/*==============================================================*/
/* 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
'定購標(biāo)識'
/


comment on column Provision.ServiceId is
'業(yè)務(wù)編碼'
/


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
'天氣預(yù)報(bào)業(yè)務(wù)訂約城市對照'
/


comment on column ForecastCity.ProvisionId is
'定購標(biāo)識'
/


comment on column ForecastCity.CityId is
'城市標(biāo)識'
/