Spaces:
Sleeping
Sleeping
File size: 8,491 Bytes
cf8ece2 | 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 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | -- Database schema for marksheet storage
CREATE DATABASE IF NOT EXISTS DOC_OC;
USE DOC_OC;
-- Users table
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
current_semester INT DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 10th marksheet table with subjects as JSON
CREATE TABLE marksheet_10th (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
board VARCHAR(50) NOT NULL,
student_name VARCHAR(100),
roll_number VARCHAR(50),
unique_id VARCHAR(50),
school_name VARCHAR(200),
school_code VARCHAR(20),
mother_name VARCHAR(100),
father_name VARCHAR(100),
-- Subject 1
subject1_code VARCHAR(20),
subject1_name VARCHAR(100),
subject1_theory_marks INT,
subject1_practical_marks INT,
subject1_internal_marks INT,
subject1_total_marks INT,
subject1_marks INT,
subject1_grade VARCHAR(10),
subject1_percentage DECIMAL(5,2),
-- Subject 2
subject2_code VARCHAR(20),
subject2_name VARCHAR(100),
subject2_theory_marks INT,
subject2_practical_marks INT,
subject2_internal_marks INT,
subject2_total_marks INT,
subject2_marks INT,
subject2_grade VARCHAR(10),
subject2_percentage DECIMAL(5,2),
-- Subject 3
subject3_code VARCHAR(20),
subject3_name VARCHAR(100),
subject3_theory_marks INT,
subject3_practical_marks INT,
subject3_internal_marks INT,
subject3_total_marks INT,
subject3_marks INT,
subject3_grade VARCHAR(10),
subject3_percentage DECIMAL(5,2),
-- Subject 4
subject4_code VARCHAR(20),
subject4_name VARCHAR(100),
subject4_theory_marks INT,
subject4_practical_marks INT,
subject4_internal_marks INT,
subject4_total_marks INT,
subject4_marks INT,
subject4_grade VARCHAR(10),
subject4_percentage DECIMAL(5,2),
-- Subject 5
subject5_code VARCHAR(20),
subject5_name VARCHAR(100),
subject5_theory_marks INT,
subject5_practical_marks INT,
subject5_internal_marks INT,
subject5_total_marks INT,
subject5_marks INT,
subject5_grade VARCHAR(10),
subject5_percentage DECIMAL(5,2),
-- Subject 6
subject6_code VARCHAR(20),
subject6_name VARCHAR(100),
subject6_theory_marks INT,
subject6_practical_marks INT,
subject6_internal_marks INT,
subject6_total_marks INT,
subject6_marks INT,
subject6_grade VARCHAR(10),
subject6_percentage DECIMAL(5,2),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
-- 12th marksheet table with subjects
CREATE TABLE marksheet_12th (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
board VARCHAR(50) NOT NULL,
student_name VARCHAR(100),
roll_number VARCHAR(50),
unique_id VARCHAR(50),
school_name VARCHAR(200),
school_code VARCHAR(20),
mother_name VARCHAR(100),
father_name VARCHAR(100),
-- Subject 1
subject1_code VARCHAR(20),
subject1_name VARCHAR(100),
subject1_theory_marks INT,
subject1_practical_marks INT,
subject1_internal_marks INT,
subject1_total_marks INT,
subject1_marks INT,
subject1_grade VARCHAR(10),
subject1_percentage DECIMAL(5,2),
-- Subject 2
subject2_code VARCHAR(20),
subject2_name VARCHAR(100),
subject2_theory_marks INT,
subject2_practical_marks INT,
subject2_internal_marks INT,
subject2_total_marks INT,
subject2_marks INT,
subject2_grade VARCHAR(10),
subject2_percentage DECIMAL(5,2),
-- Subject 3
subject3_code VARCHAR(20),
subject3_name VARCHAR(100),
subject3_theory_marks INT,
subject3_practical_marks INT,
subject3_internal_marks INT,
subject3_total_marks INT,
subject3_marks INT,
subject3_grade VARCHAR(10),
subject3_percentage DECIMAL(5,2),
-- Subject 4
subject4_code VARCHAR(20),
subject4_name VARCHAR(100),
subject4_theory_marks INT,
subject4_practical_marks INT,
subject4_internal_marks INT,
subject4_total_marks INT,
subject4_marks INT,
subject4_grade VARCHAR(10),
subject4_percentage DECIMAL(5,2),
-- Subject 5
subject5_code VARCHAR(20),
subject5_name VARCHAR(100),
subject5_theory_marks INT,
subject5_practical_marks INT,
subject5_internal_marks INT,
subject5_total_marks INT,
subject5_marks INT,
subject5_grade VARCHAR(10),
subject5_percentage DECIMAL(5,2),
-- Subject 6
subject6_code VARCHAR(20),
subject6_name VARCHAR(100),
subject6_theory_marks INT,
subject6_practical_marks INT,
subject6_internal_marks INT,
subject6_total_marks INT,
subject6_marks INT,
subject6_grade VARCHAR(10),
subject6_percentage DECIMAL(5,2),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
-- College marksheets table with subjects
CREATE TABLE college_marksheets (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
semester INT NOT NULL,
course VARCHAR(200),
session VARCHAR(20),
student_name VARCHAR(100),
roll_no VARCHAR(50),
enrollment_no VARCHAR(50),
father_name VARCHAR(100),
sgpa DECIMAL(4,2),
cgpa DECIMAL(4,2),
total_credits_registered INT,
total_credits_earned INT,
status VARCHAR(20),
-- Subject 1
subject1_code VARCHAR(20),
subject1_name VARCHAR(200),
subject1_credits INT,
subject1_internal_marks INT,
subject1_external_marks INT,
subject1_total_marks INT,
subject1_grade VARCHAR(10),
subject1_grade_point DECIMAL(3,2),
-- Subject 2
subject2_code VARCHAR(20),
subject2_name VARCHAR(200),
subject2_credits INT,
subject2_internal_marks INT,
subject2_external_marks INT,
subject2_total_marks INT,
subject2_grade VARCHAR(10),
subject2_grade_point DECIMAL(3,2),
-- Subject 3
subject3_code VARCHAR(20),
subject3_name VARCHAR(200),
subject3_credits INT,
subject3_internal_marks INT,
subject3_external_marks INT,
subject3_total_marks INT,
subject3_grade VARCHAR(10),
subject3_grade_point DECIMAL(3,2),
-- Subject 4
subject4_code VARCHAR(20),
subject4_name VARCHAR(200),
subject4_credits INT,
subject4_internal_marks INT,
subject4_external_marks INT,
subject4_total_marks INT,
subject4_grade VARCHAR(10),
subject4_grade_point DECIMAL(3,2),
-- Subject 5
subject5_code VARCHAR(20),
subject5_name VARCHAR(200),
subject5_credits INT,
subject5_internal_marks INT,
subject5_external_marks INT,
subject5_total_marks INT,
subject5_grade VARCHAR(10),
subject5_grade_point DECIMAL(3,2),
-- Subject 6
subject6_code VARCHAR(20),
subject6_name VARCHAR(200),
subject6_credits INT,
subject6_internal_marks INT,
subject6_external_marks INT,
subject6_total_marks INT,
subject6_grade VARCHAR(10),
subject6_grade_point DECIMAL(3,2),
-- Subject 7
subject7_code VARCHAR(20),
subject7_name VARCHAR(200),
subject7_credits INT,
subject7_internal_marks INT,
subject7_external_marks INT,
subject7_total_marks INT,
subject7_grade VARCHAR(10),
subject7_grade_point DECIMAL(3,2),
-- Subject 8
subject8_code VARCHAR(20),
subject8_name VARCHAR(200),
subject8_credits INT,
subject8_internal_marks INT,
subject8_external_marks INT,
subject8_total_marks INT,
subject8_grade VARCHAR(10),
subject8_grade_point DECIMAL(3,2),
-- Subject 9
subject9_code VARCHAR(20),
subject9_name VARCHAR(200),
subject9_credits INT,
subject9_internal_marks INT,
subject9_external_marks INT,
subject9_total_marks INT,
subject9_grade VARCHAR(10),
subject9_grade_point DECIMAL(3,2),
-- Subject 10
subject10_code VARCHAR(20),
subject10_name VARCHAR(200),
subject10_credits INT,
subject10_internal_marks INT,
subject10_external_marks INT,
subject10_total_marks INT,
subject10_grade VARCHAR(10),
subject10_grade_point DECIMAL(3,2),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
UNIQUE KEY unique_user_semester (user_id, semester)
); |