-- ============================================================ -- BABA BHAIRABANANDA AUTONOMOUS MAHAVIDYALAYA -- B.Voc Tourism & Hospitality Management - Database Schema -- ============================================================ CREATE DATABASE IF NOT EXISTS bbam_college CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE bbam_college; -- ------------------------------------------------------- -- STUDENTS TABLE (Admission Register) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, sl_no INT NOT NULL, session_year VARCHAR(10) NOT NULL DEFAULT '2023-24', roll_no VARCHAR(20) NOT NULL UNIQUE, regd_no VARCHAR(20) DEFAULT NULL, name VARCHAR(150) NOT NULL, date_of_birth DATE NOT NULL, gender ENUM('Male','Female','Other') NOT NULL, category VARCHAR(30) NOT NULL, religion VARCHAR(50) NOT NULL, date_of_admission DATE NOT NULL, father_name VARCHAR(150) NOT NULL, mother_name VARCHAR(150) NOT NULL, address TEXT NOT NULL, pin_code VARCHAR(10) NOT NULL, district VARCHAR(100) NOT NULL, aadhaar_no VARCHAR(20) DEFAULT NULL, contact VARCHAR(15) NOT NULL, email_id VARCHAR(150) DEFAULT NULL, photo VARCHAR(255) DEFAULT NULL, status ENUM('Active','Inactive','Passed Out') DEFAULT 'Active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------- -- MARKS TABLE (All 6 semesters in one wide table) -- ------------------------------------------------------- CREATE TABLE IF NOT EXISTS marks ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, -- 1st Semester s1_thm101_ms TINYINT UNSIGNED DEFAULT 0, s1_thm101_es TINYINT UNSIGNED DEFAULT 0, s1_thm102_ms TINYINT UNSIGNED DEFAULT 0, s1_thm102_es TINYINT UNSIGNED DEFAULT 0, s1_thm103_ms TINYINT UNSIGNED DEFAULT 0, s1_thm103_es TINYINT UNSIGNED DEFAULT 0, s1_nsqf4_th SMALLINT UNSIGNED DEFAULT 0, s1_nsqf4_pr SMALLINT UNSIGNED DEFAULT 0, -- 2nd Semester s2_thm201_ms TINYINT UNSIGNED DEFAULT 0, s2_thm201_es TINYINT UNSIGNED DEFAULT 0, s2_thm202_ms TINYINT UNSIGNED DEFAULT 0, s2_thm202_es TINYINT UNSIGNED DEFAULT 0, s2_thm203_ms TINYINT UNSIGNED DEFAULT 0, s2_thm203_es TINYINT UNSIGNED DEFAULT 0, s2_nsqf5_th SMALLINT UNSIGNED DEFAULT 0, s2_nsqf5_pr SMALLINT UNSIGNED DEFAULT 0, -- 3rd Semester s3_thm301_ms TINYINT UNSIGNED DEFAULT 0, s3_thm301_es TINYINT UNSIGNED DEFAULT 0, s3_thm302_ms TINYINT UNSIGNED DEFAULT 0, s3_thm302_es TINYINT UNSIGNED DEFAULT 0, s3_thm303_ms TINYINT UNSIGNED DEFAULT 0, s3_thm303_es TINYINT UNSIGNED DEFAULT 0, s3_nsqf55_th SMALLINT UNSIGNED DEFAULT 0, s3_nsqf55_pr SMALLINT UNSIGNED DEFAULT 0, -- 4th Semester s4_thm401_ms TINYINT UNSIGNED DEFAULT 0, s4_thm401_es TINYINT UNSIGNED DEFAULT 0, s4_thm402_ms TINYINT UNSIGNED DEFAULT 0, s4_thm402_es TINYINT UNSIGNED DEFAULT 0, s4_thm403_ms TINYINT UNSIGNED DEFAULT 0, s4_thm403_es TINYINT UNSIGNED DEFAULT 0, s4_nsqf55_project SMALLINT UNSIGNED DEFAULT 0, s4_nsqf55_viva SMALLINT UNSIGNED DEFAULT 0, -- 5th Semester s5_thm501_ms TINYINT UNSIGNED DEFAULT 0, s5_thm501_es TINYINT UNSIGNED DEFAULT 0, s5_thm502_ms TINYINT UNSIGNED DEFAULT 0, s5_thm502_es TINYINT UNSIGNED DEFAULT 0, s5_thm503_ms TINYINT UNSIGNED DEFAULT 0, s5_thm503_es TINYINT UNSIGNED DEFAULT 0, s5_nsqf6_th SMALLINT UNSIGNED DEFAULT 0, s5_nsqf6_pr SMALLINT UNSIGNED DEFAULT 0, -- 6th Semester s6_thm601_ms TINYINT UNSIGNED DEFAULT 0, s6_thm601_es TINYINT UNSIGNED DEFAULT 0, s6_thm602_ms TINYINT UNSIGNED DEFAULT 0, s6_thm602_es TINYINT UNSIGNED DEFAULT 0, s6_thm603_ms TINYINT UNSIGNED DEFAULT 0, s6_thm603_es TINYINT UNSIGNED DEFAULT 0, s6_nsqf6_project SMALLINT UNSIGNED DEFAULT 0, s6_nsqf6_viva SMALLINT UNSIGNED DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ------------------------------------------------------- -- SAMPLE DATA -- ------------------------------------------------------- INSERT INTO students (sl_no,session_year,roll_no,regd_no,name,date_of_birth,gender,category,religion,date_of_admission,father_name,mother_name,address,pin_code,district,aadhaar_no,contact,email_id,status) VALUES (1,'2023-24','BV-T-23-01','6253/23','SAI MOHAN','2003-05-12','Male','General','Hindu','2023-07-10','MOHAN KUMAR','SITA DEVI','Vill- Chandikhole, PO- Chandikhole','755044','Jajpur','XXXX-XXXX-0001','9876500001','saimohan@example.com','Active'), (2,'2023-24','BV-T-23-02','6252/23','RUDRA MOHANTY','2003-08-22','Male','OBC','Hindu','2023-07-10','BIMAL MOHANTY','PUJA MOHANTY','Vill- Kakatpur, PO- Kakatpur','754108','Puri','XXXX-XXXX-0002','9876500002','rudra@example.com','Active'); INSERT INTO marks (student_id,s1_thm101_ms,s1_thm101_es,s1_thm102_ms,s1_thm102_es,s1_thm103_ms,s1_thm103_es,s1_nsqf4_th,s1_nsqf4_pr,s2_thm201_ms,s2_thm201_es,s2_thm202_ms,s2_thm202_es,s2_thm203_ms,s2_thm203_es,s2_nsqf5_th,s2_nsqf5_pr,s3_thm301_ms,s3_thm301_es,s3_thm302_ms,s3_thm302_es,s3_thm303_ms,s3_thm303_es,s3_nsqf55_th,s3_nsqf55_pr,s4_thm401_ms,s4_thm401_es,s4_thm402_ms,s4_thm402_es,s4_thm403_ms,s4_thm403_es,s4_nsqf55_project,s4_nsqf55_viva,s5_thm501_ms,s5_thm501_es,s5_thm502_ms,s5_thm502_es,s5_thm503_ms,s5_thm503_es,s5_nsqf6_th,s5_nsqf6_pr,s6_thm601_ms,s6_thm601_es,s6_thm602_ms,s6_thm602_es,s6_thm603_ms,s6_thm603_es,s6_nsqf6_project,s6_nsqf6_viva) VALUES (1, 7,36,6,30,6,34,89,247, 8,17,7,14,8,24,131,265, 6,29,7,31,7,20,75,200, 7,20,8,13,7,23,52,27, 8,27,7,15,7,15,123,156, 7,13,6,14,6,26,55,26), (2, 8,33,7,20,7,21,65,261, 7,25,9,19,8,25,70,266, 7,21,7,18,6,22,58,202, 8,19,7,23,8,22,53,29, 7,30,8,25,8,24,102,162, 6,18,7,26,6,26,52,28);