sql6.txt

考勤管理系统 数据库系列

/* Table: ATTENDANCE, Owner: MISDBA */
CREATE TABLE ATTENDANCE (ID INTEGER NOT NULL,
        PERSON CHAR(6) NOT NULL,
        IN_OUT CHAR(1) NOT NULL,
        IO_TIME TIMESTAMP NOT NULL,
CONSTRAINT ATTENDANCE_ID PRIMARY KEY (ID));

/* Table: OVERTIME, Owner: MISDBA */
CREATE TABLE OVERTIME (ID INTEGER NOT NULL,
        PERSON CHAR(6) NOT NULL,
        WORK_HOURS SMALLINT NOT NULL,
        WORK_DATE TIMESTAMP NOT NULL,
CONSTRAINT OVERTIME_ID PRIMARY KEY (ID));

/* Table: LEAVE, Owner: MISDBA */
CREATE TABLE LEAVE (ID INTEGER NOT NULL,
        PERSON CHAR(6) NOT NULL,
        START_DAY TIMESTAMP NOT NULL,
        END_DAY TIMESTAMP NOT NULL,
        REASON VARCHAR(10),
CONSTRAINT LEAVE_ID PRIMARY KEY (ID));

/* Table: ERRAND, Owner: MISDBA */
CREATE TABLE ERRAND (ID INTEGER NOT NULL,
        PERSON CHAR(6) NOT NULL,
        START_TIME TIMESTAMP NOT NULL,
        END_TIME TIMESTAMP NOT NULL,
        DESCRIPTION VARCHAR(50),
CONSTRAINT ERRAND_ID PRIMARY KEY (ID));

/* Table: ATTENDANCE_STAT, Owner: MISDBA */
CREATE TABLE ATTENDANCE_STAT (ID INTEGER NOT NULL,
        YEAR_MONTH CHAR(7) NOT NULL,
        PERSON CHAR(6) NOT NULL,
        WORK_HOUR INTEGER,
        OVER_HOUR INTEGER,
        LEAVE_HDAY INTEGER,
        ERRAND_HDAY INTEGER,
        LATE_TIMES SMALLINT,
        EARLY_TIMES SMALLINT,
        ABSENT_TIMES SMALLINT,
CONSTRAINT ATTENDANCE_STAT_ID PRIMARY KEY (ID));

/*  ForeignKey PERSON1  */
ALTER TABLE ATTENDANCE
  ADD CONSTRAINT PERSON1 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;
/*  CheckConstraint IN_OUT_CHECK  */
ALTER TABLE ATTENDANCE
  ADD CONSTRAINT IN_OUT_CHECK
  CHECK (IN_OUT='I' OR IN_OUT='O');
/*  ForeignKey PERSON2  */
ALTER TABLE OVERTIME
  ADD CONSTRAINT PERSON2 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;
/*  ForeignKey PERSON3  */
ALTER TABLE LEAVE
  ADD CONSTRAINT PERSON3 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;
/*  ForeignKey PERSON4  */
ALTER TABLE ERRAND
  ADD CONSTRAINT PERSON4 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;
/*  ForeignKey PERSON5  */
ALTER TABLE ATTENDANCE_STAT
  ADD CONSTRAINT PERSON5 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;