mis.ddl

这是一个自己研发的薪酬管理系统

/*    此 SQL DDL 脚本由 Microsoft Visual Studio（发布日期:LOCAL BUILD）生成。                              */

/*    所用驱动程序：Microsoft Visual Studio － Microsoft SQL Server 驱动程序。                             */
/*    文档：E:\WEB\working\Whir_Salary_MIS\database\MIS.vsd。                                     */
/*    创建时间：2003年8月25日 13:22。                                                                  */
/*    操作：来自 Visio 生成向导。                                                                       */
/*    已连接的数据源：Whir_Salary_MIS                                                                 */
/*    已连接的服务器：WHIR12                                                                          */
/*    已连接的数据库：Whir_Salary_MIS                                                                 */



/* 创建新表 "wh_salaryItems2"。                                                                    */
/* "wh_salaryItems2" : Table of wh_salaryItems2                                               */
/* 	"item2_id" : item2_id 标识 wh_salaryItems2                                                  */
/* 	"item2_name" : item2_name 属于 wh_salaryItems2                                              */
/* 	"item2_isused" : item2_isused 属于 wh_salaryItems2                                          */
/* 	"item2_number" : item2_number 属于 wh_salaryItems2                                          */
/* 	"item2_tech" : item2_tech 属于 wh_salaryItems2                                              */
/* 	"item2_market" : item2_market 属于 wh_salaryItems2                                          */
/* 	"item2_adm" : item2_adm 属于 wh_salaryItems2                                                */
/* 	"item2_other" : item2_other 属于 wh_salaryItems2                                            */
/* 	"item2_memo" : item2_memo 属于 wh_salaryItems2                                              */  
create table "wh_salaryItems2" ( 
	"item2_id" int identity not null,
	"item2_name" varchar(20) not null,
	"item2_isused" bit not null,
	"item2_number" int not null,
	"item2_tech" int null,
	"item2_market" int null,
	"item2_adm" int null,
	"item2_other" int null,
	"item2_memo" varchar(50) null) ON 'PRIMARY'  

go

alter table "wh_salaryItems2"
	add constraint "wh_salaryItems2_PK" primary key clustered ("item2_id")   


go

/* 创建新表 "wh_salaryItems1"。                                                                    */
/* "wh_salaryItems1" : Table of wh_salaryItems1                                               */
/* 	"item1_id" : item1_id 标识 wh_salaryItems1                                                  */
/* 	"item1_name" : item1_name 属于 wh_salaryItems1                                              */
/* 	"item1_isused" : item1_isused 属于 wh_salaryItems1                                          */
/* 	"item1_number" : item1_number 属于 wh_salaryItems1                                          */
/* 	"item1_tech" : item1_tech 属于 wh_salaryItems1                                              */
/* 	"item1_market" : item1_market 属于 wh_salaryItems1                                          */
/* 	"item1_adm" : item1_adm 属于 wh_salaryItems1                                                */
/* 	"item1_other" : item1_other 属于 wh_salaryItems1                                            */
/* 	"item1_memo" : item1_memo 属于 wh_salaryItems1                                              */  
create table "wh_salaryItems1" ( 
	"item1_id" int identity not null,
	"item1_name" varchar(20) not null,
	"item1_isused" bit not null,
	"item1_number" int not null,
	"item1_tech" int null,
	"item1_market" int null,
	"item1_adm" int null,
	"item1_other" int null,
	"item1_memo" varchar(50) null) ON 'PRIMARY'  

go

alter table "wh_salaryItems1"
	add constraint "wh_salaryItems_PK" primary key clustered ("item1_id")   


go

/* 创建新表 "wh_tech_bonuses"。                                                                    */
/* "wh_tech_bonuses" : Table of wh_tech_bonuses                                               */
/* 	"project_id" : project_id 部分标识 wh_tech_bonuses                                            */  
create table "wh_tech_bonuses" ( 
	"tbonus_id" int identity not null,
	"tbonus_memo" varchar(100) null,
	"tbonus_quote" float default 3 null,
	"tbonus_firstsum" float null,
	"tbonus_fpercent" float null,
	"tbonus_fisgive" bit null,
	"tbonus_fdate" datetime default getdate() null,
	"tbonus_lastsum" float null,
	"tbonus_lpercent" float null,
	"tbonus_lisgive" bit null,
	"tbonus_ldate" datetime null,
	"employee_id" int null,
	"project_id" int not null) ON 'PRIMARY'  

go

alter table "wh_tech_bonuses"
	add constraint "wh_tech_bonuses_PK" primary key clustered ("tbonus_id")   


go

/* 创建新表 "wh_salaries"。                                                                        */
/* "wh_salaries" : Table of wh_salaries                                                       */
/* 	"salary_date" : salary_date 属于 wh_salaries                                                */
/* 	"salary_info1" : salary_info1 属于 wh_salaries                                              */
/* 	"salary_info2" : salary_info2 属于 wh_salaries                                              */
/* 	"salary_memo" : salary_memo 属于 wh_salaries                                                */  
create table "wh_salaries" ( 
	"salary_id" int identity not null,
	"salary_date" datetime not null,
	"salary_month" int not null,
	"employee_id" int not null,
	"salary_info1" text not null,
	"salary_info2" text not null,
	"salary_memo" varchar(50) null) ON 'PRIMARY' TEXTIMAGE_ON 'PRIMARY'  

go

alter table "wh_salaries"
	add constraint "PK_wh_salaries" primary key clustered ("salary_id")   


go

/* 创建新表 "wh_projects"。                                                                        */
/* "wh_projects" : Table of wh_projects                                                       */  
create table "wh_projects" ( 
	"project_id" int identity not null,
	"project_name" varchar(50) not null,
	"project_begin" datetime null,
	"project_end" datetime null,
	"project_finish" datetime null,
	"project_score" float null,
	"project_sum" float null,
	"project_firstpay" float default 0 null,
	"project_lastpay" float default 0 null,
	"project_notpay" float default 0 null,
	"project_cost" float default 0 null,
	"project_operation" float default 0 null,
	"project_codes" float default 0 null,
	"project_host" float default 0 null,
	"project_dns" float default 0 null,
	"project_search" float default 0 null,
	"project_mail" float default 0 null,
	"project_other" float default 0 null,
	"project_cost2" float default 0 null,
	"project_operation2" float default 0 null,
	"project_codes2" float default 0 null,
	"project_host2" float default 0 null,
	"project_dns2" float default 0 null,
	"project_search2" float default 0 null,
	"project_mail2" float default 0 null,
	"project_other2" float default 0 null,
	"project_memo" varchar(100) null) ON 'PRIMARY'  

go

alter table "wh_projects"
	add constraint "wh_projects_PK" primary key clustered ("project_id")   


go

/* 创建新表 "wh_market_bonuses"。                                                                  */
/* "wh_market_bonuses" : Table of wh_market_bonuses                                           */
/* 	"project_id" : project_id 部分标识 wh_market_bonuses                                          */  
create table "wh_market_bonuses" ( 
	"mbonus_id" int identity not null,
	"mbonus_memo" varchar(100) null,
	"mbonus_sum" float null,
	"mbonus_quota" float null,
	"mbonus_percent" float null,
	"mbonus_isgive" bit default 1 null,
	"mbonus_date" datetime default getdate() null,
	"employee_id" int null,
	"project_id" int not null) ON 'PRIMARY'  

go

alter table "wh_market_bonuses"
	add constraint "wh_market_bonuses_PK" primary key clustered ("mbonus_id")   


go

/* 创建新表 "wh_employees"。                                                                       */
/* "wh_employees" : Table of wh_employees                                                     */  
create table "wh_employees" ( 
	"employee_id" int identity not null,
	"employee_name" varchar(20) not null,
	"employee_pwd" varchar(50) not null,
	"employee_type" char(10) default '员工' not null,
	"employee_edu" varchar(20) null,
	"employee_joindate" datetime null,
	"employee_birth" datetime default getdate() null,
	"employee_memo" varchar(100) null,
	"employee_right" int default 1 not null,
	"dpt_id" int not null) ON 'PRIMARY'  

go

alter table "wh_employees"
	add constraint "wh_employees_PK" primary key clustered ("employee_id")   


go

/* 创建新表 "wh_departments"。                                                                     */
/* "wh_departments" : Table of wh_departments                                                 */  
create table "wh_departments" ( 
	"dpt_id" int identity not null,
	"dpt_name" varchar(20) not null,
	"dpt_leader" varchar(20) not null) ON 'PRIMARY'  

go

alter table "wh_departments"
	add constraint "wh_departments_PK" primary key clustered ("dpt_id")   


go

/* 在表 "wh_tech_bonuses" 中添加外键约束。                                                              */
alter table "wh_tech_bonuses"
	add constraint "wh_employees_wh_tech_bonuses_FK1" foreign key (
		"employee_id")
	 references "wh_employees" (
		"employee_id") on update no action on delete no action