crebas4.sql

北京铁路局住房分配系统,数据库为MSSql2000,依次执行crebas4.sql

/*==============================================================*/
/* Database name:  PhysicalDataModel_1                          */
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2002-8-2 11:27:36                            */
/*==============================================================*/


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


/*==============================================================*/
/* Table: dwxxb                                                 */
/*==============================================================*/
create table dwxxb (
dwbh                 char(3)              not null,
dwmc                 varchar(20)          null,
kfts                 smallint             null,
yfts                 smallint             null,
Col                  varchar(30)          null,
constraint PK_DWXXB primary key  (dwbh)
)
go


/*==============================================================*/
/* Table: fjfpb                                                 */
/*==============================================================*/
create table fjfpb (
fptjbh               char(2)              not null,
ytbh                 char(2)              not null,
yjsl                 smallint             null,
ejsl                 smallint             null,
sjsl                 smallint             null,
fsej                 smallint             null,
fssj                 smallint             null,
qt                   smallint             null,
hj                   smallint             null,
constraint PK_FJFPB primary key  (fptjbh, ytbh)
)
go


/*==============================================================*/
/* Table: fptjb                                                 */
/*==============================================================*/
create table fptjb (
bh                   char(2)              not null,
mc                   char(12)             null,
bz                   varchar(20)          null,
constraint PK_FPTJB primary key  (bh)
)
go


/*==============================================================*/
/* Table: fpzb                                                  */
/*==============================================================*/
create table fpzb (
fptjbh               char(2)              not null,
fpcs                 char(2)              not null,
yjsl                 smallint             null,
ejsl                 smallint             null,
sjsl                 smallint             null,
fsej                 smallint             null,
fssj                 smallint             null,
qt                   smallint             null,
hj                   smallint             null,
constraint PK_FPZB primary key  (fptjbh, fpcs)
)
go


/*==============================================================*/
/* Table: fwcxb                                                 */
/*==============================================================*/
create table fwcxb (
bh                   char(2)              not null,
mc                   char(12)             null,
bz                   varchar(20)          null,
constraint PK_FWCXB primary key  (bh)
)
go


/*==============================================================*/
/* Table: fwdjb                                                 */
/*==============================================================*/
create table fwdjb (
bh                   char(2)              not null,
mc                   char(12)             null,
bz                   varchar(20)          null,
constraint PK_FWDJB primary key  (bh)
)
go


/*==============================================================*/
/* Table: fwhxb                                                 */
/*==============================================================*/
create table fwhxb (
bh                   char(2)              not null,
mc                   char(12)             null,
bz                   varchar(20)          null,
constraint PK_FWHXB primary key  (bh)
)
go


/*==============================================================*/
/* Table: fwytb                                                 */
/*==============================================================*/
create table fwytb (
bh                   char(2)              not null,
mc                   char(12)             null,
bz                   varchar(20)          null,
constraint PK_FWYTB primary key  (bh)
)
go


/*==============================================================*/
/* Table: grxxb                                                 */
/*==============================================================*/
create table grxxb (
sfzh                 char(18)             not null,
xm                   varchar(12)          null,
dwbh                 char(3)              null,
zw                   varchar(16)          null,
constraint PK_GRXXB primary key  (sfzh)
)
go


/*==============================================================*/
/* Table: lyxxb                                                 */
/*==============================================================*/
create table lyxxb (
lybh                 char(5)              not null,
xqbh                 char(3)              null,
mc                   varchar(20)          null,
cs                   smallint             null,
lxbh                 char(2)              null,
fjts                 smallint             null,
constraint PK_LYXXB primary key  (lybh)
)
go


/*==============================================================*/
/* Table: userid                                                */
/*==============================================================*/
create table userid (
userid               char(8)              not null,
username             char(20)             not null,
userpassword         char(10)             not null,
userpurview          char(20)             not null,
usertype             tinyint              not null,
constraint pk_bbuser primary key  (userid)
)
go


/*==============================================================*/
/* Table: xqxxb                                                 */
/*==============================================================*/
create table xqxxb (
xqbh                 char(3)              not null,
xqmc                 varchar(20)          null,
dz                   varchar(40)          null,
constraint PK_XQXXB primary key  (xqbh)
)
go


/*==============================================================*/
/* Table: ytfpb                                                 */
/*==============================================================*/
create table ytfpb (
dwbh                 char(3)              not null,
zgfp                 smallint             null,
jlfp                 smallint             null,
cqfp                 smallint             null,
ldgb                 smallint             null,
shyf                 smallint             null,
gyfw                 smallint             null,
jdfw                 smallint             null,
qtyf                 smallint             null,
constraint PK_YTFPB primary key  (dwbh)
)
go


/*==============================================================*/
/* Table: zfxxb                                                 */
/*==============================================================*/
create table zfxxb (
zfbh                 char(10)             not null,
lybh                 char(5)              null,
dyh                  char(1)              null,
hxbh                 char(2)              null,
lc                   char(2)              null,
cxbh                 char(2)              null,
djbh                 char(2)              null,
ytbh                 char(2)              null,
sffp                 char(1)              null,
kffp                 char(1)              null,
sfzh                 char(18)             null,
dwbh                 char(3)              null,
area                 decimal(8,2)         null,
cost                 decimal(16,2)        null,
constraint PK_ZFXXB primary key  (zfbh)
)
go


/*==============================================================*/
/* Table: zgfpb                                                 */
/*==============================================================*/
create table zgfpb (
dwbh                 char(3)              not null,
ytbh                 char(2)              not null,
yjsl                 smallint             null,
ejsl                 smallint             null,
sjsl                 smallint             null,
fsej                 smallint             null,
fssj                 smallint             null,
qt                   smallint             null,
hj                   smallint             null,
constraint PK_ZGFPB primary key  (dwbh, ytbh)
)
go