initialize_student_result_database.sql

-- ==========================================================
-- 1. DATABASE SETUP & CLEANUP
-- ==========================================================
DROP DATABASE IF EXISTS college_db;
CREATE DATABASE college_db;
USE college_db;

-- Disable FK checks temporarily for smooth creation
SET FOREIGN_KEY_CHECKS = 0;

-- ==========================================================
-- 2. TABLE CREATION (Fixed with AUTO_INCREMENT)
-- ==========================================================

CREATE TABLE Department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    department_location VARCHAR(100)
);

CREATE TABLE Faculty (
    faculty_id INT PRIMARY KEY,
    faculty_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(department_id)
);

CREATE TABLE Students (
    usn VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    date_of_birth DATE,
    gender ENUM('Male','Female','Other'),
    address VARCHAR(255),
    FOREIGN KEY (department_id) REFERENCES Department(department_id)
);

CREATE TABLE Course (
    course_id VARCHAR(20) PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    sem INT NOT NULL,
    department_id INT,
    credits INT,
    course_faculty INT,
    FOREIGN KEY (department_id) REFERENCES Department(department_id),
    FOREIGN KEY (course_faculty) REFERENCES Faculty(faculty_id)
);

-- FIXED: Added AUTO_INCREMENT to fix "Field doesn't have a default value" error
CREATE TABLE Marks (
    marks_id INT AUTO_INCREMENT PRIMARY KEY,
    usn VARCHAR(20),
    course_id VARCHAR(20),
    internal_marks INT,
    external_marks INT,
    total_marks INT,
    grade CHAR(2),
    FOREIGN KEY (usn) REFERENCES Students(usn),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

-- FIXED: Added AUTO_INCREMENT
CREATE TABLE Result (
    result_id INT AUTO_INCREMENT PRIMARY KEY,
    usn VARCHAR(20),
    sem INT,
    sgpa DECIMAL(4,2),
    cgpa DECIMAL(4,2),
    FOREIGN KEY (usn) REFERENCES Students(usn)
);

-- ==========================================================
-- 3. INSERT STATIC DATA (Dept, Faculty, Courses)
-- ==========================================================

-- Departments
INSERT INTO Department VALUES (1, 'Computer Science and Engineering', 'CS Block');
INSERT INTO Department VALUES (2, 'Artificial Intelligence and Machine Learning', 'AI Block');

-- Faculty
INSERT INTO Faculty VALUES 
(1, 'Dr. Priya Kamath', 'kamath@college.edu', '9876543201', 1),
(2, 'Dr. Mustafa Basthikodi', 'basthikodi@college.edu', '9876543202', 1),
(3, 'Dr. Poornima B V', 'poornima@college.edu', '9876543203', 1),
(4, 'Mr. Kishore Kumar', 'kishore@college.edu', '9876543204', 1),
(5, 'Ms. Monisha', 'monisha@college.edu', '9876543205', 1),
(6, 'Ms. Chaithra', 'chaithra@college.edu', '9876543206', 1),
(7, 'Mrs. Ramitha T', 'ramitha@college.edu', '9876543207', 1),
(8, 'Mrs. Aparna Krishnan', 'aparna@college.edu', '9876543208', 1),
(9, 'Mrs. Varsha M', 'varsha@college.edu', '9876543209', 1),
(10, 'Mrs. Srividya Bhat', 'srividya@college.edu', '9876543210', 1),
(11, 'Mr. Ganaraj K', 'ganaraj@college.edu', '9876543211', 2),
(12, 'Dr. Gurusiddaya H', 'gurusiddaya@college.edu', '9876543212', 2),
(13, 'Ms. Shweta S Naik', 'shweta@college.edu', '9876543213', 2),
(14, 'Dr. Sadhana Rai', 'sadhana@college.edu', '9876543214', 2),
(15, 'Ms. Sandra Jayes', 'sandra@college.edu', '9876543215', 2),
(16, 'Ms. Soumya', 'soumya@college.edu', '9876543216', 2);

-- Courses
INSERT INTO Course VALUES 
('CS522T4C', 'DATABASE MANAGEMENT SYSTEMS', 5, 1, 3, 1),
('CS52298C', 'RESEARCH METHODOLOGY AND IPR', 5, 1, 1, 1),
('CS522T3C', 'AUTOMATA THEORY AND COMPUTABILITY', 5, 1, 3, 2),
('CS522I1C', 'MACHINE LEARNING AND APPLICATIONS', 5, 1, 4, 3),
('CS522I2C', 'COMPUTER NETWORKS', 5, 1, 4, 4),
('BS52299X', 'ENVIRONMENTAL STUDIES', 5, 1, 1, 5),
('CS522L5C', 'DBMS LABORATORY', 5, 1, 1, 6),
('CS52216CC', 'INFORMATION STORAGE MANAGEMENT', 5, 1, 3, 7),
('CS52216CA', 'SECURE CODING', 5, 1, 3, 9),
('CS52216CD', 'SOCIAL NETWORK ANALYSIS', 5, 1, 3, 10),
('CS522P7C', 'MINI-PROJECT', 5, 1, 2, 1),
('AM522I1A', 'DATABASE MANAGEMENT SYSTEM', 5, 2, 4, 11),
('AM522I2A', 'ADVANCED RISC MACHINES', 5, 2, 4, 12),
('AM522T4A', 'MACHINE LEARNING', 5, 2, 3, 13),
('CS522T3C_AI', 'AUTOMATA THEORY AND COMPUTABILITY', 5, 2, 3, 14),
('BS52299X_AI', 'ENVIRONMENTAL STUDIES', 5, 2, 1, 5),
('BS52298X', 'RESEARCH METHODOLOGY AND IPR', 5, 2, 1, 12),
('AM522L5A', 'MACHINE LEARNING LABORATORY', 5, 2, 1, 13),
('M52216AD', 'SOCIAL NETWORK ANALYSIS', 5, 2, 3, 16),
('AM522P7A', 'MINI-PROJECT', 5, 2, 2, 15);

-- ==========================================================
-- 4. INSERT STUDENTS (80 TOTAL - 40 CSE, 40 AIML)
-- ==========================================================
INSERT INTO Students (usn, name, department_id, date_of_birth, gender, address) VALUES 
-- CSE (1-40)
('4SF23CS001', 'Aarav Sharma', 1, '2003-01-15', 'Male', 'Mangalore'),
('4SF23CS002', 'Aditi Rao', 1, '2003-02-12', 'Female', 'Udupi'),
('4SF23CS003', 'Rohan Gupta', 1, '2003-03-22', 'Male', 'Mangalore'),
('4SF23CS004', 'Sneha Reddy', 1, '2003-04-10', 'Female', 'Bangalore'),
('4SF23CS005', 'Karthik Nair', 1, '2003-05-05', 'Male', 'Mysore'),
('4SF23CS006', 'Pooja Shetty', 1, '2003-06-18', 'Female', 'Mangalore'),
('4SF23CS007', 'Rahul Verma', 1, '2003-07-25', 'Male', 'Udupi'),
('4SF23CS008', 'Ananya Iyer', 1, '2003-08-30', 'Female', 'Bangalore'),
('4SF23CS009', 'Vikram Singh', 1, '2003-09-14', 'Male', 'Mangalore'),
('4SF23CS010', 'Meera Menon', 1, '2003-10-02', 'Female', 'Kerala'),
('4SF23CS011', 'Arjun Das', 1, '2003-11-11', 'Male', 'Mangalore'),
('4SF23CS012', 'Kavya Hegde', 1, '2003-12-20', 'Female', 'Udupi'),
('4SF23CS013', 'Sanjay Patil', 1, '2004-01-08', 'Male', 'Hubli'),
('4SF23CS014', 'Nisha Poojary', 1, '2004-02-15', 'Female', 'Mangalore'),
('4SF23CS015', 'Varun Kumar', 1, '2004-03-29', 'Male', 'Bangalore'),
('4SF23CS016', 'Riya Desai', 1, '2004-04-14', 'Female', 'Dharwad'),
('4SF23CS017', 'Amit Bhat', 1, '2004-05-21', 'Male', 'Mangalore'),
('4SF23CS018', 'Sana Khan', 1, '2004-06-05', 'Female', 'Mangalore'),
('4SF23CS019', 'Deepak Acharya', 1, '2004-07-19', 'Male', 'Udupi'),
('4SF23CS020', 'Tanvi Prabhu', 1, '2004-08-22', 'Female', 'Mangalore'),
('4SF23CS021', 'Naveen Kumar', 1, '2003-05-10', 'Male', 'Udupi'),
('4SF23CS022', 'Priya S', 1, '2003-06-12', 'Female', 'Mangalore'),
('4SF23CS023', 'Rajesh K', 1, '2003-07-14', 'Male', 'Bangalore'),
('4SF23CS024', 'Simran Kaur', 1, '2003-08-16', 'Female', 'Mysore'),
('4SF23CS025', 'Tarun J', 1, '2003-09-18', 'Male', 'Mangalore'),
('4SF23CS026', 'Usha M', 1, '2003-10-20', 'Female', 'Udupi'),
('4SF23CS027', 'Vinay R', 1, '2003-11-22', 'Male', 'Hubli'),
('4SF23CS028', 'Wendy D', 1, '2003-12-24', 'Female', 'Mangalore'),
('4SF23CS029', 'Yashwant P', 1, '2004-01-26', 'Male', 'Bangalore'),
('4SF23CS030', 'Zoya A', 1, '2004-02-28', 'Female', 'Mysore'),
('4SF23CS031', 'Abhishek B', 1, '2004-03-01', 'Male', 'Mangalore'),
('4SF23CS032', 'Bhavya C', 1, '2004-04-03', 'Female', 'Udupi'),
('4SF23CS033', 'Chandan D', 1, '2004-05-05', 'Male', 'Hubli'),
('4SF23CS034', 'Divya E', 1, '2004-06-07', 'Female', 'Mangalore'),
('4SF23CS035', 'Eshwar F', 1, '2004-07-09', 'Male', 'Bangalore'),
('4SF23CS036', 'Farah G', 1, '2004-08-11', 'Female', 'Mysore'),
('4SF23CS037', 'Girish H', 1, '2004-09-13', 'Male', 'Mangalore'),
('4SF23CS038', 'Hema I', 1, '2004-10-15', 'Female', 'Udupi'),
('4SF23CS039', 'Imran J', 1, '2004-11-17', 'Male', 'Hubli'),
('4SF23CS040', 'Jyothi K', 1, '2004-12-19', 'Female', 'Mangalore'),

-- AIML (1-40)
('4SF23CI001', 'Ishaan Joshi', 2, '2003-01-10', 'Male', 'Mangalore'),
('4SF23CI002', 'Diya Kulkarni', 2, '2003-02-18', 'Female', 'Hubli'),
('4SF23CI003', 'Manish Gowda', 2, '2003-03-25', 'Male', 'Mandya'),
('4SF23CI004', 'Shruti Pai', 2, '2003-04-12', 'Female', 'Udupi'),
('4SF23CI005', 'Abhinav Jain', 2, '2003-05-30', 'Male', 'Mangalore'),
('4SF23CI006', 'Vani Kamath', 2, '2003-06-14', 'Female', 'Mangalore'),
('4SF23CI007', 'Pranav Rao', 2, '2003-07-09', 'Male', 'Bangalore'),
('4SF23CI008', 'Gauri Shastry', 2, '2003-08-04', 'Female', 'Mysore'),
('4SF23CI009', 'Chetan Kumar', 2, '2003-09-22', 'Male', 'Hassan'),
('4SF23CI010', 'Rashmi Devadiga', 2, '2003-10-15', 'Female', 'Mangalore'),
('4SF23CI011', 'Suraj Holla', 2, '2003-11-02', 'Male', 'Udupi'),
('4SF23CI012', 'Swathi Nayak', 2, '2003-12-11', 'Female', 'Mangalore'),
('4SF23CI013', 'Ganesh M', 2, '2004-01-28', 'Male', 'Mangalore'),
('4SF23CI014', 'Amrita Sen', 2, '2004-02-05', 'Female', 'Bangalore'),
('4SF23CI015', 'Harish B', 2, '2004-03-17', 'Male', 'Udupi'),
('4SF23CI016', 'Latha S', 2, '2004-04-20', 'Female', 'Shimoga'),
('4SF23CI017', 'Nikhil R', 2, '2004-05-13', 'Male', 'Mangalore'),
('4SF23CI018', 'Pallavi K', 2, '2004-06-25', 'Female', 'Udupi'),
('4SF23CI019', 'Rakesh P', 2, '2004-07-07', 'Male', 'Mangalore'),
('4SF23CI020', 'Vidya G', 2, '2004-08-19', 'Female', 'Mangalore'),
('4SF23CI021', 'Akash L', 2, '2003-05-11', 'Male', 'Udupi'),
('4SF23CI022', 'Bhoomika M', 2, '2003-06-13', 'Female', 'Mangalore'),
('4SF23CI023', 'Chirag N', 2, '2003-07-15', 'Male', 'Bangalore'),
('4SF23CI024', 'Disha O', 2, '2003-08-17', 'Female', 'Mysore'),
('4SF23CI025', 'Eathan P', 2, '2003-09-19', 'Male', 'Mangalore'),
('4SF23CI026', 'Fathima Q', 2, '2003-10-21', 'Female', 'Udupi'),
('4SF23CI027', 'Gagan R', 2, '2003-11-23', 'Male', 'Hubli'),
('4SF23CI028', 'Harshitha S', 2, '2003-12-25', 'Female', 'Mangalore'),
('4SF23CI029', 'Inder T', 2, '2004-01-27', 'Male', 'Bangalore'),
('4SF23CI030', 'Jasmine U', 2, '2004-02-29', 'Female', 'Mysore'),
('4SF23CI031', 'Kiran V', 2, '2004-03-02', 'Male', 'Mangalore'),
('4SF23CI032', 'Leela W', 2, '2004-04-04', 'Female', 'Udupi'),
('4SF23CI033', 'Manoj X', 2, '2004-05-06', 'Male', 'Hubli'),
('4SF23CI034', 'Neha Y', 2, '2004-06-08', 'Female', 'Mangalore'),
('4SF23CI035', 'Omkar Z', 2, '2004-07-10', 'Male', 'Bangalore'),
('4SF23CI036', 'Parvathi A', 2, '2004-08-12', 'Female', 'Mysore'),
('4SF23CI037', 'Qasim B', 2, '2004-09-14', 'Male', 'Mangalore'),
('4SF23CI038', 'Raksha C', 2, '2004-10-16', 'Female', 'Udupi'),
('4SF23CI039', 'Samarth D', 2, '2004-11-18', 'Male', 'Hubli'),
('4SF23CI040', 'Trisha E', 2, '2004-12-20', 'Female', 'Mangalore');

-- ==========================================================
-- 5. GENERATE MARKS & GRADES (FOR ALL 80 STUDENTS)
-- ==========================================================

-- A. Insert Marks (Matches Students to Courses of their Department)
INSERT INTO Marks (usn, course_id, internal_marks, external_marks, total_marks, grade)
SELECT 
    s.usn, 
    c.course_id, 
    FLOOR(20 + RAND() * 30), -- Internal (20-50)
    FLOOR(20 + RAND() * 50), -- External (20-50)
    0, 
    'F'
FROM Students s
JOIN Course c ON s.department_id = c.department_id;

-- B. Calculate Totals
UPDATE Marks SET total_marks = internal_marks + external_marks;

-- C. Assign Grades
UPDATE Marks SET grade = CASE 
    WHEN total_marks >= 90 THEN 'A+'
    WHEN total_marks >= 80 THEN 'A'
    WHEN total_marks >= 70 THEN 'B'
    WHEN total_marks >= 60 THEN 'C'
    WHEN total_marks >= 50 THEN 'D'
    ELSE 'F'
END;

-- ==========================================================
-- 6. GENERATE RESULTS (SGPA CALCULATION)
-- ==========================================================

INSERT INTO Result (usn, sem, sgpa, cgpa)
SELECT 
    m.usn, 
    5, 
    ROUND(SUM(
        CASE 
            WHEN m.grade = 'A+' THEN 10 
            WHEN m.grade = 'A' THEN 9 
            WHEN m.grade = 'B' THEN 8 
            WHEN m.grade = 'C' THEN 7
            WHEN m.grade = 'D' THEN 6
            ELSE 0 
        END * c.credits
    ) / SUM(c.credits), 2), 
    0 
FROM Marks m
JOIN Course c ON m.course_id = c.course_id
GROUP BY m.usn;

-- Update CGPA (Same as SGPA for now)
UPDATE Result SET cgpa = sgpa;

-- Re-enable FK Checks
SET FOREIGN_KEY_CHECKS = 1;

-- Verification
SELECT 'Students Count' AS CheckType, COUNT(*) AS Val FROM Students
UNION ALL
SELECT 'Marks Count', COUNT(*) FROM Marks
UNION ALL
SELECT 'Results Count', COUNT(*) FROM Result;