forked from AcaMate/AcaMate_DB
69 lines
4.0 KiB
SQL
69 lines
4.0 KiB
SQL
CREATE TABLE `class_info` (
|
|
`id` CHAR(10) NOT NULL COMMENT '강의 구분 코드 (AMC+4숫자+3대문자)',
|
|
`name` VARCHAR(100) NOT NULL COMMENT '강의 제목',
|
|
`uid` CHAR(70) NOT NULL COMMENT '담당 선생님 구분 코드',
|
|
`start_date` DATETIME NOT NULL COMMENT '강의 시작일',
|
|
`end_date` DATETIME DEFAULT NULL COMMENT '강의 종료일',
|
|
`day` TINYINT NOT NULL COMMENT '수업 요일 비트(월요일 부터 가장 좌측 비트)',
|
|
`start_time` CHAR(4) NOT NULL COMMENT '수업 시작 시간',
|
|
`end_time` CHAR(4) NOT NULL COMMENT '수업 종료 시간',
|
|
PRIMARY KEY (`id`),
|
|
CONSTRAINT `fk_class_teacher_uid`
|
|
FOREIGN KEY (`uid`) REFERENCES `user`(`uid`)
|
|
ON UPDATE CASCADE
|
|
ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='강의(클래스) 정보';
|
|
|
|
|
|
CREATE TABLE `class_map` (
|
|
`cid` CHAR(10) NOT NULL COMMENT '강의 구분 코드',
|
|
`uid` CHAR(70) NOT NULL COMMENT '학생(유저) 구분 코드',
|
|
PRIMARY KEY (`cid`, `uid`),
|
|
KEY `idx_uid` (`uid`),
|
|
CONSTRAINT `fk_map_class_cid`
|
|
FOREIGN KEY (`cid`) REFERENCES class_info(`id`)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT `fk_map_class_uid`
|
|
FOREIGN KEY (`uid`) REFERENCES `user`(`uid`)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='학생-강의(클래스) 맵핑';
|
|
|
|
CREATE TABLE `class_attendance` (
|
|
`cid` CHAR(10) NOT NULL COMMENT '강의 구분 코드',
|
|
`uid` CHAR(70) NOT NULL COMMENT '학생(유저) 구분 코드',
|
|
`attendace_date` DATETIME NOT NULL COMMENT '출석 일자',
|
|
`attendance_state` TINYINT(1) NOT NULL COMMENT '출석 상태 (0=출석, 1=결석, 2=지각, 3=조퇴, 4=기타)',
|
|
PRIMARY KEY (`cid`, `uid`, `attendace_date`),
|
|
KEY `idx_uid_date` (`uid`, `attendace_date`),
|
|
CONSTRAINT `fk_class_attendance_cid`
|
|
FOREIGN KEY (`cid`) REFERENCES class_info(`id`)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT `fk_class_attendance_uid`
|
|
FOREIGN KEY (`uid`) REFERENCES `user`(`uid`)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='강의별 학생 출석 정보';
|
|
|
|
|
|
INSERT INTO class_info(id, name, uid, start_date, end_date, day, start_time, end_time)
|
|
VALUES
|
|
('AMC0000AAA','수학','AMksg345220250401','2025-06-20',NULL,124,'1800','1900'),
|
|
('AMC0000AAB','영어','AMksg345220250401','2025-06-20',NULL,124,'1900','2000'),
|
|
('AMC0000AAC','국어','AMksg345220250401','2025-06-20',NULL,124,'2000','2100'),
|
|
('AMC0000AAD','과학','AMksg345220250401','2025-06-20',NULL,124,'2100','2200'),
|
|
('AMC0000AAE','사회','AMksg345220250401','2025-06-20',NULL,124,'2200','2300');
|
|
|
|
INSERT INTO class_map(cid, uid)
|
|
VALUES
|
|
('AMC0000AAA', 'AMsdk20250612'),
|
|
('AMC0000AAB', 'AMsdk20250612'),
|
|
('AMC0000AAC', 'AMsdk20250612'),
|
|
('AMC0000AAD', 'AMsdk20250612');
|
|
|
|
|
|
# INSERT INTO class_attendance(cid, uid, attendace_date, attendance_state)
|
|
# VALUES
|
|
# ('AMC0000AAA', 'AMsdk20250612') |