Salary数据库信息
- 格式：doc
- 大小：1.96 MB
- 文档页数：18
表：
1、员工信息表：
create table Employee(
Employee_ID varchar (5) primary key,
EmployeeName varchar(20) NOT NULL ,
Department varchar (20) NOT NULL ,
Category varchar (5) NOT NULL ,
EmployeeTitle varchar(15) NOT NULL,
Date_time_in datetime NOT NULL,
Base_salary int NOT NULL,
Tra_allowance int NOT NULL,
Phone nvarchar (30) NOT NULL,
Age smallint NOT NULL,
)
2、考勤表：
CREATE TABLE [dbo].[Attendance] (
[Date1] [datetime] NOT NULL ,
[EmployeeID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EmployeeName] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[StartWork] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[EndWork] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[LateTime] [int] NULL ,
EarlyTime int,
) ON [PRIMARY]
GO
3、加班表：
create table Overtime
(
EmployeeID varchar(5) Not Null ,
EmployeeName varchar(40) Not Null,
EmployeeTitle varchar(20)Not Null,
DateT datetime Not Null,
TotalTime int,
Unit varchar(10),
Signature varchar(10), primary key(EmployeeID ,DateT)
)
4、出差表：
create table travel
(
EmployeeID varchar(5) ,
EmployeeName varchar(40) Not Null,
EmployeeTitle varchar(20)Not Null,
StartTime datetime,
EndTime datetime, TotalTime int,
primary key(EmployeeID,StartTime)
)
5、请假表：
create table leave
(
EmployeeID varchar(5) Not Null ,
EmployeeName varchar(40) Not Null,
EmployeeTitle varchar(20)Not Null,
StartTime datetime,
EndTime datetime,
TotalTime int default 0, Cancel int,
Totalleave int default 0,
primary key(EmployeeID ,StartTime)
)
6、五险一金和个人所得税表：
create table Tax
(
EmployeeID varchar(5) Not Null ,
EmployeeName varchar(40) Not Null,
DateT varchar(20)Not Null,
SendMoney int default 0,
PensionInsurance int default 0, UnemplInsurance int default 0,
MedicalInsurance int default 0,
HousingFund int default 0,
IndividualTax int default 0,
primary key(EmployeeID ,DateT)
)
7、销售部绩效表：
CREATE TABLE [dbo].[Sale_score] (
[Department] [varchar] (10) NOT NULL,
[year] [varchar] (5) NOT NULL,
[month] [varchar] (10)NOT NULL,
[Score] [varchar] (10)NOT NULL,
[Unit] [varchar] (5)NOT NULL,
primary key(Department,year,month)
)
ON [PRIMARY]
8、一车间绩效表：
CREATE TABLE [dbo].[Produ_score] (
[Department] [varchar] (10) NOT NULL,
[year] [varchar] (5) NOT NULL,
[month] [varchar] (10)NOT NULL,
[Score] [varchar] (10)NOT NULL,
[Unit] [varchar] (5)NOT NULL,
primary key(Department,year,month)
) ON [PRIMARY]
9、工资明细表：
create table Detail_salary(
EmployeeID varchar (5) not null,
EmployeeName varchar(20) NOT NULL ,
Department varchar (20) NOT NULL ,
Category varchar (5) NOT NULL ,
EmployeeTitle varchar(15) NOT NULL,
Base_salary int NOT NULL,
Tra_allowance int NOT NULL,
Total_fixed_salary int NOT NULL,
Overtime_reward int NOT NULL,
perfect_attendence_reward int NOT NULL, merits_reward int NOT NULL,
Total_reward_salary int NOT NULL,
check_in_salary int NOT NULL,
SendMoney int NOT NULL,
Insurance int not null,
IndividualTax int NOT NULL,
Total_cut_payment int NOT NULL,
Total_deliver_salary int NOT NULL,
Date2 datetime not null,
primary key(EmployeeID,Date2)
)
视图： 出勤：
create view "AttendanceView" AS
SELECT EmployeeID,MONTH(Date1) as MonthA,count(EmployeeID) AS M_totalAtten
FROM Attendance
GROUP BY EmployeeID,MONTH(Date1) 该视图可以把 员工正月的出勤天数显示出来
加班视图：
create view "OvertimeView" AS
SELECT EmployeeID,MONTH(DateT) as MonthN,Sum(TotalTime) AS M_totalTime
FROM Overtime
WHERE Signature='陈明'
GROUP BY EmployeeID,MONTH(DateT)
GO 每个员工每月的有效（有主管签名的）加班总天数显示出来
迟到不足十分钟视图：
create view "AttendanceView2" AS
SELECT EmployeeID,MONTH(Date1) as MonthA,count(Latetime) AS Late1
FROM Attendance
where Latetime<=10 and Latetime>0
