绩效考核系统 数据库 sql server 代码

create database PEM;
use PEM;
create table Admin(
	adminId int identity(1,1) primary key,
	adminName varchar(255) not null,
	pswd varchar(255) not null,
	sex varchar (255) check(sex in('男','女')) not null
);

create table Department(
	depId int identity(1,1) primary key,
	depName varchar(255) unique not null,
	total int default 0
);

create table Employee(
	empId int identity(1,1) primary key,
	empName varchar(255) not null,
	pswd varchar(255) not null,
	sex varchar (255) check(sex in('男','女')) not null,
	depId int not null,
	foreign key (depId) references Department(depId) on delete cascade   on update cascade
);
create table Contact(
	empId int ,
	contact varchar(255),
	tel int not null,
	primary key(empId,contact),
	foreign key(empId)references Employee(empId) on delete cascade on update cascade
);
create table Test(
	testId int identity(1,1)primary key,
	testName varchar (255) unique not null,
	startTime datetime not null,
	endTime datetime not null
);
create table OriginalScore(
	testId int,
	juryId int ,
	empId int ,
	workScore smallint check (workScore>=0 and workScore<=100) not null,
	
	primary key(testId,empId,juryId),
	foreign key (empId) references Employee(empId) on delete cascade on update cascade,
	foreign key (juryId) references Employee(empId),
	foreign key (testId) references Test(testId) on delete cascade on update cascade
);

create table TerminalScore(
	testId int ,
	empId int,
	workScore float,
	primary key(empId,testId),
	foreign key (empId) references Employee(empId) on delete cascade on update cascade,
	foreign key (testId) references Test(testId) on delete cascade on update cascade
);