File size: 10,125 Bytes
f742815 3f635ef f742815 3f635ef f742815 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | CREATE DATABASE IF NOT EXISTS attendr;
USE attendr;
START TRANSACTION;
CREATE TABLE attendance_records (
id INTEGER NOT NULL,
session_id INTEGER NOT NULL,
student_id INTEGER NOT NULL,
marked_at DATETIME,
face_verified BOOLEAN,
location_verified BOOLEAN,
code_verified BOOLEAN,
student_latitude FLOAT,
student_longitude FLOAT,
distance_from_classroom FLOAT,
status VARCHAR(20),
PRIMARY KEY (id),
FOREIGN KEY(session_id) REFERENCES attendance_sessions (id),
FOREIGN KEY(student_id) REFERENCES students (id)
);
INSERT INTO `attendance_records` VALUES(1,2,1,'2025-11-22 16:39:17.926194',1,1,1,3.1222562,101.7636743,6.719226959803195314e+02,'present');
INSERT INTO `attendance_records` VALUES(2,3,1,'2025-11-22 16:46:07.075823',1,1,1,3.1222616,101.7636711,6.714557167968994236e+02,'present');
INSERT INTO `attendance_records` VALUES(3,6,1,'2025-11-22 17:12:17.627831',1,1,1,3.122261,101.7636698,6.715625427798626106e+02,'present');
INSERT INTO `attendance_records` VALUES(4,7,1,'2025-11-22 15:50:16.138101',1,1,1,0.0,0.0,10.0,'present');
INSERT INTO `attendance_records` VALUES(5,8,1,'2025-11-22 17:31:19.262173',1,1,1,3.1222586,101.7636816,6.714266242365291645e+02,'present');
CREATE TABLE attendance_sessions (
id INTEGER NOT NULL,
course_name VARCHAR(100) NOT NULL,
classroom_id INTEGER NOT NULL,
lecturer_name VARCHAR(100) NOT NULL,
start_time DATETIME,
end_time DATETIME,
is_active BOOLEAN,
current_code VARCHAR(10),
code_generated_at DATETIME,
PRIMARY KEY (id),
FOREIGN KEY(classroom_id) REFERENCES classrooms (id)
);
INSERT INTO `attendance_sessions` VALUES(1,'AI',46,'ZHANG LING HE','2025-11-22 16:34:53.967623','2025-11-22 16:35:22.483428',0,'EAB1FQ','2025-11-22 16:34:53.977748');
INSERT INTO `attendance_sessions` VALUES(2,'AI',46,'ZHANG LING HE','2025-11-22 16:35:34.223978','2025-11-22 16:45:32.132931',0,'87JFTB','2025-11-22 16:45:23.271856');
INSERT INTO `attendance_sessions` VALUES(3,'AD',46,'ZHANG LING HE','2025-11-22 16:45:46.604739','2025-11-22 16:50:30.716018',0,'RJII20','2025-11-22 16:50:22.204453');
INSERT INTO `attendance_sessions` VALUES(4,'SECR3104 - Applications Development',46,'ZHANG LING HE','2025-11-22 17:04:10.912458','2025-11-22 17:04:36.114555',0,'86SONH','2025-11-22 17:04:32.116897');
INSERT INTO `attendance_sessions` VALUES(5,'SECR3104 - Applications Development',46,'ZHANG LING HE','2025-11-22 17:04:54.989793','2025-11-22 17:09:42.210274',0,'L8D01H','2025-11-22 17:09:33.982659');
INSERT INTO `attendance_sessions` VALUES(6,'SECJ3553 - Artificial Intelligence',46,'ZHANG LING HE','2025-11-22 17:12:01.124969','2025-11-22 17:26:36.046635',0,'446MC7','2025-11-22 17:26:33.821599');
INSERT INTO `attendance_sessions` VALUES(7,'TEST101 - Testing',1,'Test Lecturer','2025-11-22 15:20:16.131518','2025-11-22 16:20:16.131518',0,NULL,NULL);
INSERT INTO `attendance_sessions` VALUES(8,'SECR1013 - Digital Logic',46,'ZHANG LING HE','2025-11-22 17:30:15.307311','2025-11-22 17:31:23.154043',0,'D5DYN5','2025-11-22 17:31:15.654521');
CREATE TABLE classrooms (
id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
building VARCHAR(100),
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
radius_meters INTEGER,
PRIMARY KEY (id)
);
INSERT INTO `classrooms` VALUES(1,'N28-105-01','N28 - Faculty of Computing',1.5638,103.6388,2000);
INSERT INTO `classrooms` VALUES(2,'N28-116-02','N28 - Faculty of Computing',1.5639,103.6388,2000);
INSERT INTO `classrooms` VALUES(3,'N28-112-02','N28 - Faculty of Computing',1.564,103.6388,2000);
INSERT INTO `classrooms` VALUES(4,'N28-111-01','N28 - Faculty of Computing',1.5641,103.6388,2000);
INSERT INTO `classrooms` VALUES(5,'N28-106-01','N28 - Faculty of Computing',1.5642,103.6388,2000);
INSERT INTO `classrooms` VALUES(6,'N28-107-01','N28 - Faculty of Computing',1.5643,103.6388,2000);
INSERT INTO `classrooms` VALUES(7,'N28-108-01','N28 - Faculty of Computing',1.5644,103.6388,2000);
INSERT INTO `classrooms` VALUES(8,'N28-351-02','N28 - Level 3',1.5645,103.6388,2000);
INSERT INTO `classrooms` VALUES(9,'N28-301-01','N28 - Level 3',1.5646,103.6388,2000);
INSERT INTO `classrooms` VALUES(10,'N28-301-01B','N28 - Level 3',1.5647,103.6388,2000);
INSERT INTO `classrooms` VALUES(11,'N28A-BT1','N28A - Tutorial Block',1.5638,103.6389,2000);
INSERT INTO `classrooms` VALUES(12,'N28A-BT2','N28A - Tutorial Block',1.5639,103.6389,2000);
INSERT INTO `classrooms` VALUES(13,'N28A-BT3','N28A - Tutorial Block',1.564,103.6389,2000);
INSERT INTO `classrooms` VALUES(14,'N28A-BT4','N28A - Tutorial Block',1.5641,103.6389,2000);
INSERT INTO `classrooms` VALUES(15,'N28A-BK1','N28A - Tutorial Block',1.5642,103.6389,2000);
INSERT INTO `classrooms` VALUES(16,'N28A-BK2','N28A - Tutorial Block',1.5643,103.6389,2000);
INSERT INTO `classrooms` VALUES(17,'N28A-BK5','N28A - Tutorial Block',1.5644,103.6389,2000);
INSERT INTO `classrooms` VALUES(18,'N28A-BK6','N28A - Tutorial Block',1.5645,103.6389,2000);
INSERT INTO `classrooms` VALUES(19,'N28-502-01','N28 - Level 5',1.5646,103.6389,2000);
INSERT INTO `classrooms` VALUES(20,'N28-505-01','N28 - Level 5',1.5647,103.6389,2000);
INSERT INTO `classrooms` VALUES(21,'N28-506-01','N28 - Level 5',1.5638,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(22,'N28-512-01','N28 - Level 5',1.5639,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(23,'N28-516-01','N28 - Level 5',1.564,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(24,'N28-517-02','N28 - Level 5',1.5641,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(25,'N28-518-02','N28 - Level 5',1.5642,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(26,'N28-524-02','N28 - Level 5',1.5643,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(27,'N28-525-01','N28 - Level 5',1.5644,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(28,'N28-527-02','N28 - Level 5',1.5645,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(29,'N28A-02-33','N28A - Level 2',1.5646,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(30,'N28A-02-34','N28A - Level 2',1.5647,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(31,'N28-223-02','N28 - Level 2',1.5638,103.6391,2000);
INSERT INTO `classrooms` VALUES(32,'N28-204-01','N28 - Level 2',1.5639,103.6391,2000);
INSERT INTO `classrooms` VALUES(33,'N28-203-01','N28 - Level 2',1.564,103.6391,2000);
INSERT INTO `classrooms` VALUES(34,'N28-202-01','N28 - Level 2',1.5641,103.6391,2000);
INSERT INTO `classrooms` VALUES(35,'N28-330-01','N28 - Level 3',1.5642,103.6391,2000);
INSERT INTO `classrooms` VALUES(36,'N28-352-01','N28 - Level 3',1.5643,103.6391,2000);
INSERT INTO `classrooms` VALUES(37,'N28-321-02','N28 - Level 3',1.5644,103.6391,2000);
INSERT INTO `classrooms` VALUES(38,'N28-329-01','N28 - Level 3',1.5645,103.6391,2000);
INSERT INTO `classrooms` VALUES(39,'N28-350-03','N28 - Level 3',1.5646,103.6391,2000);
INSERT INTO `classrooms` VALUES(40,'N28-422-01','N28 - Level 4',1.5647,103.6391,2000);
INSERT INTO `classrooms` VALUES(41,'N28-423-01','N28 - Level 4',1.5638,103.6392,2000);
INSERT INTO `classrooms` VALUES(42,'L50-DK3','Block L50 - Centre Point',1.5639,103.6392,2000);
INSERT INTO `classrooms` VALUES(43,'P19-DK4','Block P19 - FKE',1.564,103.6392,2000);
INSERT INTO `classrooms` VALUES(44,'P19-DK5','Block P19 - FKE',1.5641,103.6392,2000);
INSERT INTO `classrooms` VALUES(45,'P19-DK6','Block P19 - FKE',1.5642,103.6392,2000);
INSERT INTO `classrooms` VALUES(46,'PUTERI-COURT','Puteri Court Condominium (Home Demo)',3.128024,101.765479,1000);
INSERT INTO `classrooms` VALUES(47,'Pejabat Pos UTM (KDSE)','W1, Kolej Datin Seri Endon, 81110 Johor Bahru',1.560662,103.638421,2000);
CREATE TABLE students (
id INTEGER NOT NULL,
user_id INTEGER,
student_id VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
face_encoding TEXT,
registered_at DATETIME,
is_active BOOLEAN,
PRIMARY KEY (id),
UNIQUE (user_id),
FOREIGN KEY(user_id) REFERENCES users (id),
UNIQUE (student_id),
UNIQUE (email)
);
INSERT INTO `students` VALUES(1,1,'B24CS0011','CHUA LIN WEI','chuawei@graduate.utm.my','[15.0, 180.0, 15.0, 242.0, 74.0, 75.0, 180.0, 242.0, 127.0, 136.0, 138.0, 179.0, 93.0, 68.0, 122.0, 148.0, 34.0, 224.0, 223.0, 39.0, 58.0, 228.0, 180.0, 183.0, 91.0, 67.0, 219.0, 4.0, 131.0, 147.0, 136.0, 58.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]','2025-11-22 16:33:50.673821',1);
CREATE TABLE users (
id INTEGER NOT NULL,
matric_no VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
password_hash VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL,
is_active BOOLEAN,
created_at DATETIME,
PRIMARY KEY (id),
UNIQUE (matric_no),
UNIQUE (email)
);
INSERT INTO `users` VALUES(1,'B24CS0011','CHUA LIN WEI','chuawei@graduate.utm.my','scrypt:32768:8:1$hE1y6RkAGBX68GXd$0f613cc449e9b0f9bf2be44cc9914af63d03bc6af78c96baf25081d085e8e64fdd001d01b286afe3cde65fba0ce980d5666c4a233057c7937f6924302e38cd84','student',1,'2025-11-22 16:33:49.914535');
INSERT INTO `users` VALUES(2,'L001','ZHANG LING HE','miaofifi146@gmail.com','scrypt:32768:8:1$0Lde9MkTKZHggMvp$fd98cbd8955f87ffc01b1e383429b350d50ff4c3e5b05ac7f39bf25dac99aa1f0858f8c5c562decc81c297fd5b1f98bbce7ec69027875bd9882eb052e07d777a','lecturer',1,'2025-11-22 16:34:27.215798');
INSERT INTO `users` VALUES(3,'L9999','Test Lecturer',NULL,'scrypt:32768:8:1$mVC87j8AGr8uHDsu$4b3a45675a2731d350460403556460e2ebbdd85fcdb204cc9ad81695a0f573f20c36405be345ead28baedc30f21f4cf3fc8e32c14bfc3b83f89d2d4a0edf74bf','lecturer',1,'2025-11-22 17:20:16.122417');
COMMIT;
|