SQL Server 工资管理系统数据库设计与实现

Create Database SalaryMS;
use SalaryMS;

/*管理员表*/
CREATE TABLE Admin (
 AdminId INT identity(1,1) primary key NOT NULL,
 AdminName VARCHAR(40) unique not null,
 pswd VARCHAR(40) not null
);

/*部门表*/
CREATE TABLE Department (
 DepId INT identity(1,1) primary key NOT NULL,
 DepName VARCHAR(40),
 DepTel VARCHAR(15),
 counter int DEFAULT 0
);
/*工资项表*/
CREATE TABLE Item (
 ItemId INT identity(1,1) primary key,
 ItemName VARCHAR(40) unique NOT NULL,
 ItemType int DEFAULT 1 NOT NULL,
 counter int DEFAULT 0
);
/*员工表*/
CREATE TABLE Employee (
 EmpId varchar(18) primary key,
 EmpName VARCHAR(40) not null,
 pswd VARCHAR(40) not null,
 DepId INT not null,
 foreign key (DepId) references Department(DepId) on delete no action on update cascade
);
/*工资表*/
CREATE TABLE Salary (
 EmpId varchar(18) NOT NULL,
 ItemId INT NOT NULL,
 ItemSalary numeric(18,2),
 SDate varchar(18) NOT NULL,
 primary key(empId,itemId,SDate),
 foreign key (ItemId) references Item(Item