xiaoyu1111111.sql

用于大学 课程设计 主要是介绍安利物品销售的流程

/*==============================================================*/
/* Database name:  ConceptualDataModel_1                        */
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2005-12-10 23:57:23                          */
/*==============================================================*/


alter table 产品
   drop constraint FK_产品_RELATIONS_库存
go


alter table 产品
   drop constraint FK_产品_RELATIONS_安利店铺
go


alter table 登记
   drop constraint FK_登记_登记_日志
go


alter table 登记
   drop constraint FK_登记_登记2_管理员
go


alter table 客户
   drop constraint FK_客户_RELATIONS_产品
go


alter table 客户信息
   drop constraint FK_客户信息_客户信息_客户
go


alter table 客户信息
   drop constraint FK_客户信息_客户信息2_日志
go


alter table 收入
   drop constraint FK_收入_RELATIONS_产品
go


alter table 支出
   drop constraint FK_支出_RELATIONS_产品
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('产品')
            and   name  = 'Relationship_5_FK'
            and   indid > 0
            and   indid < 255)
   drop index 产品.Relationship_5_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('产品')
            and   name  = 'Relationship_8_FK'
            and   indid > 0
            and   indid < 255)
   drop index 产品.Relationship_8_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('登记')
            and   name  = '登记2_FK'
            and   indid > 0
            and   indid < 255)
   drop index 登记.登记2_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('登记')
            and   name  = '登记_FK'
            and   indid > 0
            and   indid < 255)
   drop index 登记.登记_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('客户')
            and   name  = 'Relationship_9_FK'
            and   indid > 0
            and   indid < 255)
   drop index 客户.Relationship_9_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('客户信息')
            and   name  = '客户信息2_FK'
            and   indid > 0
            and   indid < 255)
   drop index 客户信息.客户信息2_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('客户信息')
            and   name  = '客户信息_FK'
            and   indid > 0
            and   indid < 255)
   drop index 客户信息.客户信息_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('收入')
            and   name  = 'Relationship_6_FK'
            and   indid > 0
            and   indid < 255)
   drop index 收入.Relationship_6_FK
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('支出')
            and   name  = 'Relationship_7_FK'
            and   indid > 0
            and   indid < 255)
   drop index 支出.Relationship_7_FK
go


if exists (select 1
            from  sysobjects
           where  id = object_id('安利店铺')
            and   type = 'U')
   drop table 安利店铺
go


if exists (select 1
            from  sysobjects
           where  id = object_id('产品')
            and   type = 'U')
   drop table 产品
go


if exists (select 1
            from  sysobjects
           where  id = object_id('登记')
            and   type = 'U')
   drop table 登记
go


if exists (select 1
            from  sysobjects
           where  id = object_id('管理员')
            and   type = 'U')
   drop table 管理员
go


if exists (select 1
            from  sysobjects
           where  id = object_id('客户')
            and   type = 'U')
   drop table 客户
go


if exists (select 1
            from  sysobjects
           where  id = object_id('客户信息')
            and   type = 'U')
   drop table 客户信息
go


if exists (select 1
            from  sysobjects
           where  id = object_id('库存')
            and   type = 'U')
   drop table 库存
go


if exists (select 1
            from  sysobjects
           where  id = object_id('日志')
            and   type = 'U')
   drop table 日志
go


if exists (select 1
            from  sysobjects
           where  id = object_id('收入')
            and   type = 'U')
   drop table 收入
go


if exists (select 1
            from  sysobjects
           where  id = object_id('支出')
            and   type = 'U')
   drop table 支出
go


/*==============================================================*/
/* Table: 安利店铺                                                  */
/*==============================================================*/
create table 安利店铺 (
   dp_id                char(10)             not null,
   dp_dz                varchar(30)          not null,
   constraint PK_安利店铺 primary key  (dp_id)
)
go


/*==============================================================*/
/* Table: 产品                                                    */
/*==============================================================*/
create table 产品 (
   cp_id                char(13)             not null,
   kc_id                char(13)             null,
   dp_id                char(10)             null,
   cp_sl                char(3)              not null,
   cp_name              varchar(20)          not null,
   cp_gg                varchar(50)          null,
   cp_jg                char(4)              not null,
   cp_lx                varchar(20)          null,
   cp_syqx              datetime             null,
   constraint PK_产品 primary key  (cp_id)
)
go


/*==============================================================*/
/* Index: Relationship_5_FK                                     */
/*==============================================================*/
create   index Relationship_5_FK on 产品 (
kc_id
)
go


/*==============================================================*/
/* Index: Relationship_8_FK                                     */
/*==============================================================*/
create   index Relationship_8_FK on 产品 (
dp_id
)
go


/*==============================================================*/
/* Table: 登记                                                    */
/*==============================================================*/
create table 登记 (
   rz_name              varchar(4)           not null,
   id                   varchar(20)          not null,
   password             char(20)             not null,
   constraint PK_登记 primary key  (rz_name, id, password)
)
go


/*==============================================================*/
/* Index: 登记_FK                                                 */
/*==============================================================*/
create   index 登记_FK on 登记 (
rz_name
)
go


/*==============================================================*/
/* Index: 登记2_FK                                                */
/*==============================================================*/
create   index 登记2_FK on 登记 (
id,
password
)
go


/*==============================================================*/
/* Table: 管理员                                                   */
/*==============================================================*/
create table 管理员 (
   id                   varchar(20)          not null,
   password             char(20)             not null,
   constraint PK_管理员 primary key  (id, password)
)
go


/*==============================================================*/
/* Table: 客户                                                    */
/*==============================================================*/
create table 客户 (
   kh_id                char(13)             not null,
   cp_id                char(13)             null,
   kh_name              varchar(20)          not null,
   kh_sex               varchar(2)           not null,
   kh_ad                varchar(30)          not null,
   kh_zy                varchar(20)          not null,
   kh_tel               char(15)             not null,
   kh_yj                image                null,
   kh_hf                varchar(2)           null,
   kh_jtcy              varchar(50)          null,
   kh_gmwp              varchar(100)         null,
   kh_sj                varchar(100)         null,
   kh_jhid              char(12)             null,
   constraint PK_客户 primary key  (kh_id)
)
go


/*==============================================================*/
/* Index: Relationship_9_FK                                     */
/*==============================================================*/
create   index Relationship_9_FK on 客户 (
cp_id
)
go


/*==============================================================*/
/* Table: 客户信息                                                  */
/*==============================================================*/
create table 客户信息 (
   kh_id                char(13)             not null,
   rz_name              varchar(4)           not null,
   constraint PK_客户信息 primary key  (kh_id, rz_name)
)
go


/*==============================================================*/
/* Index: 客户信息_FK                                               */
/*==============================================================*/
create   index 客户信息_FK on 客户信息 (
kh_id
)
go


/*==============================================================*/
/* Index: 客户信息2_FK                                              */
/*==============================================================*/
create   index 客户信息2_FK on 客户信息 (
rz_name
)
go


/*==============================================================*/
/* Table: 库存                                                    */
/*==============================================================*/
create table 库存 (
   kc_id                char(13)             not null,
   kc_sl                char(3)              not null,
   kc_gg                varchar(50)          not null,
   kc_jg                char(4)              not null,
   kc_lx                char(20)             not null,
   kc_syrq              datetime             null,
   kc_bz                char(2)              null,
   kc_gxrq              datetime             null,
   kc_name              varchar(20)          null,
   constraint PK_库存 primary key  (kc_id)
)
go


/*==============================================================*/
/* Table: 日志                                                    */
/*==============================================================*/
create table 日志 (
   rz_name              varchar(4)           not null,
   rz_sj                varchar(100)         not null,
   rz_time              datetime             not null,
   rz_ad                varchar(30)          not null,
   rz_rq                datetime             not null,
   constraint PK_日志 primary key  (rz_name)
)
go


/*==============================================================*/
/* Table: 收入                                                    */
/*==============================================================*/
create table 收入 (
   sr_id                char(13)             not null,
   cp_id                char(13)             null,
   sr_jg                char(3)              not null,
   sr_sl                char(3)              not null,
   sr_rq                money                not null,
   sr_lx                varchar(20)          null,
   sr_je                char(4)              not null,
   sr_zje               char(5)              not null,
   sr_name              varchar(20)          not null,
   constraint PK_收入 primary key  (sr_id)
)
go


/*==============================================================*/
/* Index: Relationship_6_FK                                     */
/*==============================================================*/
create   index Relationship_6_FK on 收入 (
cp_id
)
go


/*==============================================================*/
/* Table: 支出                                                    */
/*==============================================================*/
create table 支出 (
   zc_id                char(13)             not null,
   cp_id                char(13)             null,
   zc_jg                char(3)              not null,
   zc_sl                char(3)              not null,
   zc_gxsj              datetime             null,
   zc_lx                varchar(20)          null,
   zc_je                char(5)              not null,
   zc_zje               char(5)              not null,
   zc_name              varchar(20)          not null,
   constraint PK_支出 primary key  (zc_id)
)
go


/*==============================================================*/
/* Index: Relationship_7_FK                                     */
/*==============================================================*/
create   index Relationship_7_FK on 支出 (
cp_id
)
go


alter table 产品
   add constraint FK_产品_RELATIONS_库存 foreign key (kc_id)
      references 库存 (kc_id)
go


alter table 产品
   add constraint FK_产品_RELATIONS_安利店铺 foreign key (dp_id)
      references 安利店铺 (dp_id)
go


alter table 登记
   add constraint FK_登记_登记_日志 foreign key (rz_name)
      references 日志 (rz_name)
go


alter table 登记
   add constraint FK_登记_登记2_管理员 foreign key (id, password)
      references 管理员 (id, password)
go


alter table 客户
   add constraint FK_客户_RELATIONS_产品 foreign key (cp_id)
      references 产品 (cp_id)
go


alter table 客户信息
   add constraint FK_客户信息_客户信息_客户 foreign key (kh_id)
      references 客户 (kh_id)
go


alter table 客户信息
   add constraint FK_客户信息_客户信息2_日志 foreign key (rz_name)
      references 日志 (rz_name)
go


alter table 收入
   add constraint FK_收入_RELATIONS_产品 foreign key (cp_id)
      references 产品 (cp_id)
go


alter table 支出
   add constraint FK_支出_RELATIONS_产品 foreign key (cp_id)
      references 产品 (cp_id)
go