create database 项目管理系统 on primary (name='项目管理系统_data', filename='e:\sql\项目管理系统_data.mdf', size=5mb, maxsize=20mb, filegrowth=20% ) log on (name='项目管理系统_log', filename='e:\sql\项目管理系统_log.mdf', size=5mb, maxsize=20mb, filegrowth=20% ) use 项目管理系统 create table personnel( pid char(5) primary key, name varchar(10) not null, sex varchar(5) check(sex='man' or sex='woman'), position varchar(10) not null, telephone varchar(20) not null, adress varchar(30) null ) create table department( did char(5) primary key, name varchar(10) not null, found_time datetime not null, person_amount int not null, telephone varchar(20) not null, fax varchar(20) null ) create table item( Iid char(5) primary key, name varchar(5) unique, worker_numbers int, start_time datetime not null, terminal_time datetime not null, bcws int not null, res text not null ) create table item_progress( Iid char(5) primary key, acwp int not null, bcwp int not null, cb numeric check(cb>-1 and cb<=0), cpi numeric check(cpi>=0 and cpi<1), sv int not null, spi numeric check(spi>=0 and spi<1), foreign key(Iid) references item(Iid) ) create table personnal_achievement( pid char(5), did char(5), Iid char(5), recieve_items int, complete_items int, salary int primary key(pid,did,Iid), foreign key(pid) references personnel(pid), foreign key(did) references department(did), foreign key(Iid) references item(Iid) ) alter table item_progress add bcws int not null
on primary
(name='项目管理系统_data',
filename='e:\sql\项目管理系统_data.mdf',
size=5mb,
maxsize=20mb,
filegrowth=20%
)
log on
(name='项目管理系统_log',
filename='e:\sql\项目管理系统_log.mdf',
size=5mb,
maxsize=20mb,
filegrowth=20%
)
use 项目管理系统
create table personnel(
pid char(5) primary key,
name varchar(10) not null,
sex varchar(5) check(sex='man' or sex='woman'),
position varchar(10) not null,
telephone varchar(20) not null,
adress varchar(30) null
)
create table department(
did char(5) primary key,
name varchar(10) not null,
found_time datetime not null,
person_amount int not null,
telephone varchar(20) not null,
fax varchar(20) null
)
create table item(
Iid char(5) primary key,
name varchar(5) unique,
worker_numbers int,
start_time datetime not null,
terminal_time datetime not null,
bcws int not null,
res text not null
)
create table item_progress(
Iid char(5) primary key,
acwp int not null,
bcwp int not null,
cb numeric check(cb>-1 and cb<=0),
cpi numeric check(cpi>=0 and cpi<1),
sv int not null,
spi numeric check(spi>=0 and spi<1),
foreign key(Iid)
references item(Iid)
)
create table personnal_achievement(
pid char(5),
did char(5),
Iid char(5),
recieve_items int,
complete_items int,
salary int
primary key(pid,did,Iid),
foreign key(pid)
references personnel(pid),
foreign key(did)
references department(did),
foreign key(Iid)
references item(Iid)
)
alter table item_progress
add bcws int not null