mysql 员工打卡考勤表 数据库考勤表设计

实验要求

1．设计与建立上课考勤表Attend_???，能登记每个学生的考勤记录包括正常、迟到、旷课、请假。能统计以专业为单位的出勤类别并进行打分评价排序，如迟到、旷课、请假分别扣2，5，1分。可以考虑给一初始的分值，以免负值。

2．为major表与stud表增加sum_evaluation 数值字段，以记录根据考勤表Attend_???(Attendance)中出勤类别打分汇总的值。

3．建立个人考勤汇总表stud_attend与专业考勤表major_attend，表示每个学生或每个专业在某时间周期（起始日期，终止日期）正常、迟到、旷课、请假次数及考勤分值。

4．根据major表中的值与stud中的值，为考勤表Attend输入足够的样本值，要求每个专业都要有学生，有部分学生至少要有一周的每天5个单元（12，34，56，78，90，没有课的单元可以没有考勤记录）的考勤完整记录，其中正常、迟到、旷课、请假可以用数字或字母符号表示。

5．建立触发器，当对考勤表Attend表进行相应插入、删除、修改时，对stud表的sum_evaluation 数值进行相应的数据更新。

6．建立过程，生成某专业某时段（起、止日期）的考勤汇总表major_attend中各字段值，并汇总相应专业，将考勤分值的汇总结果写入到major表中的sum_evaluation中。

create table T_attendscore_J122
(
    mstatus nchar(2) primary key,
    score int
);
insert into T_attendscore_J122 values('正常',0);
insert into T_attendscore_J122 values('请假',1);
insert into T_attendscore_J122 values('迟到',2);
insert into T_attendscore_J122 values('旷课',5);

drop table T_attend_J122;
create table T_attend_J122
(
    sno char(10),
    mno char(2),
    sday date,
    unit char(2) check(unit in('12','34','56','78','90')),
    mstatus nchar(2) check(mstatus in('正常','请假','迟到','旷课')),
    constraint pk_T_attend primary key(sno,sday,unit),
    constraint fk_T_attend_sno foreign key(sno) references T_stud_J122(sno),
    constraint fk_T_attend_mno foreign key(mno) references T_major_J122(mno)
);

alter table T_stud_J122 add(sum_evaluation number);
alter table T_major_J122 add(sum_evaluation number);

insert into T_attend_J122 values('0902160122','02','11-11月-2018','12','正常');
insert into T_attend_J122 values('0902160122','02','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0902160122','02','12-11月-2018','12','正常');
insert into T_attend_J122 values('0902160122','02','12-11月-2018','78','正常');
insert into T_attend_J122 values('0902160122','02','12-11月-2018','90','正常');
insert into T_attend_J122 values('0902160122','02','13-11月-2018','12','旷课');
insert into T_attend_J122 values('0902160122','02','13-11月-2018','34','请假');
insert into T_attend_J122 values('0902160122','02','13-11月-2018','90','旷课');
insert into T_attend_J122 values('0902160122','02','14-11月-2018','34','正常');
insert into T_attend_J122 values('0902160122','02','14-11月-2018','56','正常');
insert into T_attend_J122 values('0902160122','02','14-11月-2018','78','正常');
insert into T_attend_J122 values('0902160122','02','15-11月-2018','12','请假');
insert into T_attend_J122 values('0902160122','02','15-11月-2018','34','请假');
insert into T_attend_J122 values('0902160122','02','15-11月-2018','78','正常');

insert into T_attend_J122 values('0902160121','02','11-11月-2018','12','正常');
insert into T_attend_J122 values('0902160121','02','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0902160121','02','12-11月-2018','12','迟到');
insert into T_attend_J122 values('0902160121','02','12-11月-2018','78','正常');
insert into T_attend_J122 values('0902160121','02','12-11月-2018','90','旷课');
insert into T_attend_J122 values('0902160121','02','13-11月-2018','12','旷课');
insert into T_attend_J122 values('0902160121','02','13-11月-2018','34','请假');
insert into T_attend_J122 values('0902160121','02','13-11月-2018','90','旷课');
insert into T_attend_J122 values('0902160121','02','14-11月-2018','34','正常');
insert into T_attend_J122 values('0902160121','02','14-11月-2018','56','正常');
insert into T_attend_J122 values('0902160121','02','14-11月-2018','78','正常');
insert into T_attend_J122 values('0902160121','02','15-11月-2018','12','请假');
insert into T_attend_J122 values('0902160121','02','15-11月-2018','34','请假');
insert into T_attend_J122 values('0902160121','02','15-11月-2018','78','正常');

insert into T_attend_J122 values('0902160120','02','11-11月-2018','12','正常');
insert into T_attend_J122 values('0902160120','02','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0902160120','02','12-11月-2018','12','迟到');
insert into T_attend_J122 values('0902160120','02','12-11月-2018','78','正常');
insert into T_attend_J122 values('0902160120','02','12-11月-2018','90','正常');
insert into T_attend_J122 values('0902160120','02','13-11月-2018','12','旷课');
insert into T_attend_J122 values('0902160120','02','13-11月-2018','34','旷课');
insert into T_attend_J122 values('0902160120','02','13-11月-2018','90','旷课');
insert into T_attend_J122 values('0902160120','02','14-11月-2018','34','正常');
insert into T_attend_J122 values('0902160120','02','14-11月-2018','56','正常');
insert into T_attend_J122 values('0902160120','02','14-11月-2018','78','正常');
insert into T_attend_J122 values('0902160120','02','15-11月-2018','12','请假');
insert into T_attend_J122 values('0902160120','02','15-11月-2018','34','请假');
insert into T_attend_J122 values('0902160120','02','15-11月-2018','78','正常');

insert into T_attend_J122 values('0919160122','19','11-11月-2018','12','正常');
insert into T_attend_J122 values('0919160122','19','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0919160122','19','12-11月-2018','12','迟到');
insert into T_attend_J122 values('0919160122','19','12-11月-2018','78','正常');
insert into T_attend_J122 values('0919160122','19','12-11月-2018','90','正常');
insert into T_attend_J122 values('0919160122','19','13-11月-2018','12','旷课');
insert into T_attend_J122 values('0919160122','19','13-11月-2018','34','请假');
insert into T_attend_J122 values('0919160122','19','13-11月-2018','90','旷课');
insert into T_attend_J122 values('0919160122','19','14-11月-2018','34','正常');
insert into T_attend_J122 values('0919160122','19','14-11月-2018','56','正常');
insert into T_attend_J122 values('0919160122','19','14-11月-2018','78','正常');
insert into T_attend_J122 values('0919160122','19','15-11月-2018','12','请假');
insert into T_attend_J122 values('0919160122','19','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0919160122','19','15-11月-2018','78','正常');

insert into T_attend_J122 values('0919160121','19','11-11月-2018','12','正常');
insert into T_attend_J122 values('0919160121','19','11-11月-2018','34','正常');
insert into T_attend_J122 values('0919160121','19','12-11月-2018','12','迟到');
insert into T_attend_J122 values('0919160121','19','12-11月-2018','78','正常');
insert into T_attend_J122 values('0919160121','19','12-11月-2018','90','旷课');
insert into T_attend_J122 values('0919160121','19','13-11月-2018','12','旷课');
insert into T_attend_J122 values('0919160121','19','13-11月-2018','34','请假');
insert into T_attend_J122 values('0919160121','19','13-11月-2018','90','旷课');
insert into T_attend_J122 values('0919160121','19','14-11月-2018','34','正常');
insert into T_attend_J122 values('0919160121','19','14-11月-2018','56','正常');
insert into T_attend_J122 values('0919160121','19','14-11月-2018','78','正常');
insert into T_attend_J122 values('0919160121','19','15-11月-2018','12','请假');
insert into T_attend_J122 values('0919160121','19','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0919160121','19','15-11月-2018','78','正常');

insert into T_attend_J122 values('0919160120','19','11-11月-2018','12','正常');
insert into T_attend_J122 values('0919160120','19','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0919160120','19','12-11月-2018','12','迟到');
insert into T_attend_J122 values('0919160120','19','12-11月-2018','78','正常');
insert into T_attend_J122 values('0919160120','19','12-11月-2018','90','请假');
insert into T_attend_J122 values('0919160120','19','13-11月-2018','12','迟到');
insert into T_attend_J122 values('0919160120','19','13-11月-2018','34','请假');
insert into T_attend_J122 values('0919160120','19','13-11月-2018','90','正常');
insert into T_attend_J122 values('0919160120','19','14-11月-2018','34','正常');
insert into T_attend_J122 values('0919160120','19','14-11月-2018','56','正常');
insert into T_attend_J122 values('0919160120','19','14-11月-2018','78','正常');
insert into T_attend_J122 values('0919160120','19','15-11月-2018','12','请假');
insert into T_attend_J122 values('0919160120','19','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0919160120','19','15-11月-2018','78','正常');

insert into T_attend_J122 values('0921160122','21','11-11月-2018','12','请假');
insert into T_attend_J122 values('0921160122','21','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0921160122','21','12-11月-2018','12','正常');
insert into T_attend_J122 values('0921160122','21','12-11月-2018','78','请假');
insert into T_attend_J122 values('0921160122','21','12-11月-2018','90','迟到');
insert into T_attend_J122 values('0921160122','21','13-11月-2018','12','迟到');
insert into T_attend_J122 values('0921160122','21','13-11月-2018','34','请假');
insert into T_attend_J122 values('0921160122','21','13-11月-2018','90','正常');
insert into T_attend_J122 values('0921160122','21','14-11月-2018','34','旷课');
insert into T_attend_J122 values('0921160122','21','14-11月-2018','56','正常');
insert into T_attend_J122 values('0921160122','21','14-11月-2018','78','正常');
insert into T_attend_J122 values('0921160122','21','15-11月-2018','12','迟到');
insert into T_attend_J122 values('0921160122','21','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0921160122','21','15-11月-2018','78','正常');

insert into T_attend_J122 values('0921160121','21','11-11月-2018','12','正常');
insert into T_attend_J122 values('0921160121','21','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0921160121','21','12-11月-2018','12','正常');
insert into T_attend_J122 values('0921160121','21','12-11月-2018','78','请假');
insert into T_attend_J122 values('0921160121','21','12-11月-2018','90','迟到');
insert into T_attend_J122 values('0921160121','21','13-11月-2018','12','请假');
insert into T_attend_J122 values('0921160121','21','13-11月-2018','34','请假');
insert into T_attend_J122 values('0921160121','21','13-11月-2018','90','正常');
insert into T_attend_J122 values('0921160121','21','14-11月-2018','34','正常');
insert into T_attend_J122 values('0921160121','21','14-11月-2018','56','正常');
insert into T_attend_J122 values('0921160121','21','14-11月-2018','78','正常');
insert into T_attend_J122 values('0921160121','21','15-11月-2018','12','迟到');
insert into T_attend_J122 values('0921160121','21','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0921160121','21','15-11月-2018','78','正常');

insert into T_attend_J122 values('0921160120','21','11-11月-2018','12','正常');
insert into T_attend_J122 values('0921160120','21','11-11月-2018','34','迟到');
insert into T_attend_J122 values('0921160120','21','12-11月-2018','12','正常');
insert into T_attend_J122 values('0921160120','21','12-11月-2018','78','请假');
insert into T_attend_J122 values('0921160120','21','12-11月-2018','90','迟到');
insert into T_attend_J122 values('0921160120','21','13-11月-2018','12','迟到');
insert into T_attend_J122 values('0921160120','21','13-11月-2018','34','请假');
insert into T_attend_J122 values('0921160120','21','13-11月-2018','90','正常');
insert into T_attend_J122 values('0921160120','21','14-11月-2018','34','正常');
insert into T_attend_J122 values('0921160120','21','14-11月-2018','56','正常');
insert into T_attend_J122 values('0921160120','21','14-11月-2018','78','正常');
insert into T_attend_J122 values('0921160120','21','15-11月-2018','12','请假');
insert into T_attend_J122 values('0921160120','21','15-11月-2018','34','旷课');
insert into T_attend_J122 values('0921160120','21','15-11月-2018','78','正常');

select T_attend_J122.mstatus,mno,sum(score)
from T_attend_J122,T_attendscore_J122
where T_attend_J122.mstatus=T_attendscore_J122.mstatus and 
T_attend_J122.mstatus<>'正常'
group by mno,T_attend_J122.mstatus
order by sum(score);

create table T_stud_attend_J122
(
    sno char(10),
    stime date,
    etime date,
    normalcnt int,
    leavecnt int,
    latecnt int,
    absentcnt int,
    score number,
    constraint pk_T_stud_attend_time primary key(sno,stime,etime),
    constraint fk_T_stud_attend_sno foreign key(sno) references T_stud_J122(sno)
);

create table T_major_attend_J122
(
    mno char(2),
    stime date,
    etime date,
    normalcnt int,
    leavecnt int,
    latecnt int,
    absentcnt int,
    score number,
    constraint pk_T_major_attend_time primary key(mno,stime,etime),
    constraint fk_T_major_attend_J122 foreign key(mno) references T_major_J122(mno)
);

drop trigger tg_T_attend_J122;
create trigger tg_T_attend_J122
    before insert or update or delete on T_attend_J122
    for each row
declare
    mnewscore binary_integer;
    moldscore binary_integer;
begin 
    if inserting then
        select score into mnewscore from T_attendscore_J122 where mstatus=:new.mstatus;
        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)+mnewscore where T_stud_J122.sno=:new.sno;
    elsif updating then
        select score into mnewscore from T_attendscore_J122 where mstatus=:new.mstatus;
        select score into moldscore from T_attendscore_J122 where mstatus=:old.mstatus;
        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)-moldscore where T_stud_J122.sno=:old.sno;
        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)+mnewscore where T_stud_J122.sno=:new.sno;
    else 
        select score into moldscore from T_attendscore_J122 where mstatus=:old.mstatus;
        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)-moldscore where T_stud_J122.sno=:old.sno;
    end if;
end;

select sno,sum_evaluation from T_stud_J122 where sno='0902160122';
insert into T_attend_J122 values('0902160122','02','16-11月-2018','56','请假');
select sno,sum_evaluation from T_stud_J122 where sno='0902160122';

drop procedure P_attend_J122;
create procedure P_attend_J122 (pmno char,pstime date,petime date)as
    pscore int:=0;
begin
    insert into T_major_attend_J122 values(pmno,pstime,petime,0,0,0,0,0);
    for cur in(
        select T_attend_J122.mstatus,count(T_attend_J122.mstatus)mstatus_cnt,sum(score)pscore
        from T_attend_J122,T_attendscore_J122
        where T_attend_J122.mstatus=T_attendscore_J122.mstatus and T_attend_J122.mno=pmno
        and sday>=pstime and sday<=petime
        group by T_attend_J122.mstatus)loop
        if cur.mstatus='正常' then
        update T_major_attend_J122 set normalcnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;
        elsif cur.mstatus='请假' then
        update T_major_attend_J122 set leavecnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;
        elsif cur.mstatus='迟到' then
        update T_major_attend_J122 set latecnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;
        elsif cur.mstatus='旷课' then
        update T_major_attend_J122 set absentcnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;
        end if;
        pscore:=pscore+cur.pscore;
    end loop;
    update T_major_attend_J122 set score=pscore where mno=pmno and stime=pstime and etime=petime;
    update T_major_J122 set sum_evaluation=pscore where mno=pmno;
end;
.
/

set linesize 200;
select * from T_major_attend_J122;
select * from T_major_J122;
exec P_attend_J122('02','11-11月-2018','15-11月-2018');
exec P_attend_J122('19','11-11月-2018','15-11月-2018');
exec P_attend_J122('21','11-11月-2018','15-11月-2018');
select * from T_major_attend_J122;
select * from T_major_J122;