工資管理系統 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(ItemId) on delete no action on update cascade,
 foreign key (EmpId) references Employee(EmpId) on delete no action on update cascade
);




/*觸發器 插入一項工資，對應工資項總數加一*/
create trigger SalaryInsert  
on  Salary  
for Insert as    
declare     
 @ItemId int  
Begin   
    select @ItemId = ItemId   
        from inserted  
    update Item     
    set counter = counter + 1    
    where ItemId = @ItemId    
End   
/*觸發器 删除一項工資，對應工資項總數減一*/


create trigger SalaryDelete  
on  Salary  
for delete as    
declare     
 @ItemId int  
Begin   
    select @ItemId = ItemId   
        from deleted  
    update Item     
    set counter = counter - 1    
    where ItemId = @ItemId    
End  


/*觸發器 插入一個員工，對應員工總數加一*/
create trigger EmpInsert  
on  Employee  
for Insert as    
declare     
 @DepId int  
Begin   
    select @DepId = DepId   
        from inserted  
    update Department     
    set counter = counter + 1    
    where DepId = @DepId    
End   
/*觸發器 删除一個員工，對應員工總數減一*/
create trigger EmpDelete   
on  Employee   
for delete as    
declare     
 @DepId int  
Begin   
    select @DepId = DepId   
        from deleted  
    update Department     
    set counter = counter - 1    
    where DepId = @DepId    
End
 
/*視圖：工資查詢*/
create view  v_salary
as
select Salary.SDate,Item.ItemName,Employee.EmpId, Employee.EmpName,Item.ItemType*Salary.ItemSalary as Salary
from Salary,Employee,Item
where Salary.EmpId = Employee.EmpId and Salary.ItemId = Item.ItemId ;


/*視圖：總工資查詢*/
create view  v_TotalSalary
as
select Salary.SDate,Employee.EmpId, Employee.EmpName,sum(Item.ItemType*Salary.ItemSalary) as  totalSalary
from Salary,Employee,Item
where Salary.EmpId = Employee.EmpId and Salary.ItemId = Item.ItemId 
group by Salary.SDate,Employee.EmpId,Employee.EmpName;           

複制