项目整体需求分析
项目： 项目名称， 计划名称， 项目类型， 项目进度， 项目负责人， 项目创建时间
团队： 团队名称， 团队负责人， 团队人数， 团队创建时间
人员： 姓名， 团队负责人， 任务负责人， 工号， 性别， 年龄， 工龄， 归属单位
任务资源： 资源名称， 资源种类， 资源能力， 归属单位
任务： 任务名称， 任务负责人， 任务预算， 实际花费， 任务总进度， 花费时间
订单：物品名称， 价格， 种类
项目计划： 计划名称， 预算， 花费时间， 阶段划分
风险： 风险名称， 风险种类， 解决方法
项目变更： 变更编号， 变更前类型， 变更后类型， 变更原因， 变更时间
加粗的为主码
ER图实现
分表实现
总和实现
CDM图实现
PMD图实现
SQL SERVER2012 代码生成
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2012 */
/* Created on: 2019/12/14 18:45:09 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Change') and o.name = 'FK_CHANGE_ADMINISTE_PROJECT')
alter table Change
drop constraint FK_CHANGE_ADMINISTE_PROJECT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Difficult') and o.name = 'FK_DIFFICUL_ADMINISTE_PROJECT')
alter table Difficult
drop constraint FK_DIFFICUL_ADMINISTE_PROJECT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('"Plan"') and o.name = 'FK_PLAN_ADMINISTE_PROJECT')
alter table "Plan"
drop constraint FK_PLAN_ADMINISTE_PROJECT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Project') and o.name = 'FK_PROJECT_ADMINISTE_PLAN')
alter table Project
drop constraint FK_PROJECT_ADMINISTE_PLAN
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Purchaes') and o.name = 'FK_PURCHAES_PURCHAES_TASK')
alter table Purchaes
drop constraint FK_PURCHAES_PURCHAES_TASK
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Purchaes') and o.name = 'FK_PURCHAES_PURCHAES2_ORDER')
alter table Purchaes
drop constraint FK_PURCHAES_PURCHAES2_ORDER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('"Resource Usage"') and o.name = 'FK_RESOURCE_RESOURCE _TASK')
alter table "Resource Usage"
drop constraint "FK_RESOURCE_RESOURCE _TASK"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('"Resource Usage"') and o.name = 'FK_RESOURCE_RESOURCE _RESOURCE')
alter table "Resource Usage"
drop constraint "FK_RESOURCE_RESOURCE _RESOURCE"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Staff') and o.name = 'FK_STAFF_MAKE2_TASK')
alter table Staff
drop constraint FK_STAFF_MAKE2_TASK
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Staff') and o.name = 'FK_STAFF_RELATIONS_TEAM')
alter table Staff
drop constraint FK_STAFF_RELATIONS_TEAM
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Staff') and o.name = 'FK_STAFF_TEAM LEAD_STAFF')
alter table Staff
drop constraint "FK_STAFF_TEAM LEAD_STAFF"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Task') and o.name = 'FK_TASK_INCLUDE_PROJECT')
alter table Task
drop constraint FK_TASK_INCLUDE_PROJECT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Task') and o.name = 'FK_TASK_MAKE_STAFF')
alter table Task
drop constraint FK_TASK_MAKE_STAFF
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Team') and o.name = 'FK_TEAM_IMPLEMENT_PROJECT')
alter table Team
drop constraint FK_TEAM_IMPLEMENT_PROJECT
go
if exists (select 1
from sysindexes
where id = object_id('Change')
and name = 'Administer4_FK'
and indid > 0
and indid < 255)
drop index Change.Administer4_FK
go
if exists (select 1
from sysobjects
where id = object_id('Change')
and type = 'U')
drop table Change
go
if exists (select 1
from sysindexes
where id = object_id('Difficult')
and name = 'Administer3_FK'
and indid > 0
and indid < 255)
drop index Difficult.Administer3_FK
go
if exists (select 1
from sysobjects
where id = object_id('Difficult')
and type = 'U')
drop table Difficult
go
if exists (select 1
from sysobjects
where id = object_id('"Order"')
and type = 'U')
drop table "Order"
go
if exists (select 1
from sysindexes
where id = object_id('"Plan"')
and name = 'Administer2_FK'
and indid > 0
and indid < 255)
drop index "Plan".Administer2_FK
go
if exists (select 1
from sysobjects
where id = object_id('"Plan"')
and type = 'U')
drop table "Plan"
go
if exists (select 1
from sysindexes
where id = object_id('Project')
and name = 'Administer1_FK'
and indid > 0
and indid < 255)
drop index Project.Administer1_FK
go
if exists (select 1
from sysobjects
where id = object_id('Project')
and type = 'U')
drop table Project
go
if exists (select 1
from sysindexes
where id = object_id('Purchaes')
and name = 'Purchaes2_FK'
and indid > 0
and indid < 255)
drop index Purchaes.Purchaes2_FK
go
if exists (select 1
from sysindexes
where id = object_id('Purchaes')
and name = 'Purchaes_FK'
and indid > 0
and indid < 255)
drop index Purchaes.Purchaes_FK
go
if exists (select 1
from sysobjects
where id = object_id('Purchaes')
and type = 'U')
drop table Purchaes
go
if exists (select 1
from sysindexes
where id = object_id('"Resource Usage"')
and name = 'Resource Usage2_FK'
and indid > 0
and indid < 255)
drop index "Resource Usage"."Resource Usage2_FK"
go
if exists (select 1
from sysindexes
where id = object_id('"Resource Usage"')
and name = 'Resource Usage_FK'
and indid > 0
and indid < 255)
drop index "Resource Usage"."Resource Usage_FK"
go
if exists (select 1
from sysobjects
where id = object_id('"Resource Usage"')
and type = 'U')
drop table "Resource Usage"
go
if exists (select 1
from sysobjects
where id = object_id('Resources')
and type = 'U')
drop table Resources
go
if exists (select 1
from sysindexes
where id = object_id('Staff')
and name = 'Team Leader_FK'
and indid > 0
and indid < 255)
drop index Staff."Team Leader_FK"
go
if exists (select 1
from sysindexes
where id = object_id('Staff')
and name = 'Make2_FK'
and indid > 0
and indid < 255)
drop index Staff.Make2_FK
go
if exists (select 1
from sysindexes
where id = object_id('Staff')
and name = 'Relationship_6_FK'
and indid > 0
and indid < 255)
drop index Staff.Relationship_6_FK
go
if exists (select 1
from sysobjects
where id = object_id('Staff')
and type = 'U')
drop table Staff
go
if exists (select 1
from sysindexes
where id = object_id('Task')
and name = 'Make_FK'
and indid > 0
and indid < 255)
drop index Task.Make_FK
go
if exists (select 1
from sysindexes
where id = object_id('Task')
and name = 'Include_FK'
and indid > 0
and indid < 255)
drop index Task.Include_FK
go
if exists (select 1
from sysobjects
where id = object_id('Task')
and type = 'U')
drop table Task
go
if exists (select 1
from sysindexes
where id = object_id('Team')
and name = 'Implement_FK'
and indid > 0
and indid < 255)
drop index Team.Implement_FK
go
if exists (select 1
from sysobjects
where id = object_id('Team')
and type = 'U')
drop table Team
go
/*==============================================================*/
/* Table: Change */
/*==============================================================*/
create table Change (
ChangeNo char(10) not null,
ProjectName char(20) null,
ChangeBeforeType char(15) not null,
ChangeAfterType char(15) not null,
ChangeReason char(50) not null,
ChangeTime datetime not null,
constraint PK_CHANGE primary key nonclustered (ChangeNo)
)
go
/*==============================================================*/
/* Index: Administer4_FK */
/*==============================================================*/
create index Administer4_FK on Change (
ProjectName ASC
)
go
/*==============================================================*/
/* Table: Difficult */
/*==============================================================*/
create table Difficult (
DifficultName char(20) not null,
ProjectName char(20) null,
DifficultType char(15) not null,
constraint PK_DIFFICULT primary key nonclustered (DifficultName)
)
go
/*==============================================================*/
/* Index: Administer3_FK */
/*==============================================================*/
create index Administer3_FK on Difficult (
ProjectName ASC
)
go
/*==============================================================*/
/* Table: "Order" */
/*==============================================================*/
create table "Order" (
OrderName char(10) not null,
OrderPrice int not null,
OrderType char(15) not null,
constraint PK_ORDER primary key nonclustered (OrderName)
)
go
/*==============================================================*/
/* Table: "Plan" */
/*==============================================================*/
create table "Plan" (
PlanName char(20) not null,
ProjectName char(20) null,
PlanBudget int not null,
PlanTime datetime not null,
PlanFeasibility char(2) not null,
constraint PK_PLAN primary key nonclustered (PlanName)
)
go
/*==============================================================*/
/* Index: Administer2_FK */
/*==============================================================*/
create index Administer2_FK on "Plan" (
ProjectName ASC
)
go
/*==============================================================*/
/* Table: Project */
/*==============================================================*/
create table Project (
ProjectName char(20) not null,
PlanName char(20) null,
ProjectType char(15) not null,
ProjectProgress char(10) not null,
ProjectLeader char(10) not null,
ProjectCreateTime datetime not null,
constraint PK_PROJECT primary key nonclustered (ProjectName)
)
go
/*==============================================================*/
/* Index: Administer1_FK */
/*==============================================================*/
create index Administer1_FK on Project (
PlanName ASC
)
go
/*==============================================================*/
/* Table: Purchaes */
/*==============================================================*/
create table Purchaes (
TaskName char(20) not null,
OrderName char(10) not null,
PurchaseNumber int null,
constraint PK_PURCHAES primary key (TaskName, OrderName)
)
go
/*==============================================================*/
/* Index: Purchaes_FK */
/*==============================================================*/
create index Purchaes_FK on Purchaes (
TaskName ASC
)
go
/*==============================================================*/
/* Index: Purchaes2_FK */
/*==============================================================*/
create index Purchaes2_FK on Purchaes (
OrderName ASC
)
go
/*==============================================================*/
/* Table: "Resource Usage" */
/*==============================================================*/
create table "Resource Usage" (
TaskName char(20) not null,
ResourcesName char(20) not null,
"Use Duration" char(10) null,
constraint "PK_RESOURCE USAGE" primary key (TaskName, ResourcesName)
)
go
/*==============================================================*/
/* Index: "Resource Usage_FK" */
/*==============================================================*/
create index "Resource Usage_FK" on "Resource Usage" (
TaskName ASC
)
go
/*==============================================================*/
/* Index: "Resource Usage2_FK" */
/*==============================================================*/
create index "Resource Usage2_FK" on "Resource Usage" (
ResourcesName ASC
)
go
/*==============================================================*/
/* Table: Resources */
/*==============================================================*/
create table Resources (
ResourcesName char(20) not null,
ResourcesType char(15) not null,
ResourcesAbility char(20) not null,
ResourcesAscription char(10) not null,
constraint PK_RESOURCES primary key nonclustered (ResourcesName)
)
go
/*==============================================================*/
/* Table: Staff */
/*==============================================================*/
create table Staff (
StaffName char(10) not null,
StaffNo char(10) not null,
TaskName char(20) null,
TeamName char(20) null,
Sta_StaffNo char(10) null,
StaffSex char(2) not null,
StaffAge int not null,
StaffWorkYear int not null,
StaffAscription char(10) not null,
constraint PK_STAFF primary key nonclustered (StaffNo)
)
go
/*==============================================================*/
/* Index: Relationship_6_FK */
/*==============================================================*/
create index Relationship_6_FK on Staff (
TeamName ASC
)
go
/*==============================================================*/
/* Index: Make2_FK */
/*==============================================================*/
create index Make2_FK on Staff (
TaskName ASC
)
go
/*==============================================================*/
/* Index: "Team Leader_FK" */
/*==============================================================*/
create index "Team Leader_FK" on Staff (
Sta_StaffNo ASC
)
go
/*==============================================================*/
/* Table: Task */
/*==============================================================*/
create table Task (
TaskName char(20) not null,
StaffNo char(10) null,
ProjectName char(20) null,
TaskLeader char(10) not null,
TaskBudget int not null,
TaskCost int not null,
TaskState char(10) not null,
TaskTime char(10) not null,
constraint PK_TASK primary key nonclustered (TaskName)
)
go
/*==============================================================*/
/* Index: Include_FK */
/*==============================================================*/
create index Include_FK on Task (
ProjectName ASC
)
go
/*==============================================================*/
/* Index: Make_FK */
/*==============================================================*/
create index Make_FK on Task (
StaffNo ASC
)
go
/*==============================================================*/
/* Table: Team */
/*==============================================================*/
create table Team (
TeamName char(20) not null,
ProjectName char(20) null,
TeamLeader char(10) not null,
TeamNumber int not null,
TeamCreateTime datetime not null,
constraint PK_TEAM primary key nonclustered (TeamName)
)
go
/*==============================================================*/
/* Index: Implement_FK */
/*==============================================================*/
create index Implement_FK on Team (
ProjectName ASC
)
go
alter table Change
add constraint FK_CHANGE_ADMINISTE_PROJECT foreign key (ProjectName)
references Project (ProjectName)
go
alter table Difficult
add constraint FK_DIFFICUL_ADMINISTE_PROJECT foreign key (ProjectName)
references Project (ProjectName)
go
alter table "Plan"
add constraint FK_PLAN_ADMINISTE_PROJECT foreign key (ProjectName)
references Project (ProjectName)
go
alter table Project
add constraint FK_PROJECT_ADMINISTE_PLAN foreign key (PlanName)
references "Plan" (PlanName)
go
alter table Purchaes
add constraint FK_PURCHAES_PURCHAES_TASK foreign key (TaskName)
references Task (TaskName)
go
alter table Purchaes
add constraint FK_PURCHAES_PURCHAES2_ORDER foreign key (OrderName)
references "Order" (OrderName)
go
alter table "Resource Usage"
add constraint "FK_RESOURCE_RESOURCE _TASK" foreign key (TaskName)
references Task (TaskName)
go
alter table "Resource Usage"
add constraint "FK_RESOURCE_RESOURCE _RESOURCE" foreign key (ResourcesName)
references Resources (ResourcesName)
go
alter table Staff
add constraint FK_STAFF_MAKE2_TASK foreign key (TaskName)
references Task (TaskName)
go
alter table Staff
add constraint FK_STAFF_RELATIONS_TEAM foreign key (TeamName)
references Team (TeamName)
go
alter table Staff
add constraint "FK_STAFF_TEAM LEAD_STAFF" foreign key (Sta_StaffNo)
references Staff (StaffNo)
go
alter table Task
add constraint FK_TASK_INCLUDE_PROJECT foreign key (ProjectName)
references Project (ProjectName)
go
alter table Task
add constraint FK_TASK_MAKE_STAFF foreign key (StaffNo)
references Staff (StaffNo)
go
alter table Team
add constraint FK_TEAM_IMPLEMENT_PROJECT foreign key (ProjectName)
references Project (ProjectName)
go
