Schema for SIRF

 Here is the complete SQL schema and sample data inserts for your educational ecosystem involving Universities, Colleges, Departments, Programs, Courses, Teachers, Students, Roles, Users, and Documents:

---

🧱 SQL DDL: Create Tables

-- Table: universities
CREATE TABLE universities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('Public', 'Private') NOT NULL,
    location VARCHAR(255),
    state VARCHAR(100),
    website VARCHAR(255)
);

-- Table: colleges
CREATE TABLE colleges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    university_id INT,
    name VARCHAR(255) NOT NULL,
    category ENUM('Affiliated', 'Autonomous', 'Campus') NOT NULL,
    location VARCHAR(255),
    email VARCHAR(255),
    contact_no VARCHAR(20),
    FOREIGN KEY (university_id) REFERENCES universities(id)
);

-- Table: departments
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    college_id INT,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50),
    FOREIGN KEY (college_id) REFERENCES colleges(id)
);

-- Table: programs
CREATE TABLE programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    name VARCHAR(255) NOT NULL,
    level ENUM('UG', 'PG', 'PhD') NOT NULL,
    duration_years INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Table: courses
CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    program_id INT,
    code VARCHAR(20),
    name VARCHAR(255),
    semester INT,
    credits INT,
    FOREIGN KEY (program_id) REFERENCES programs(id)
);

-- Table: teachers
CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    mobile VARCHAR(20),
    designation VARCHAR(100),
    qualification VARCHAR(100),
    doj DATE,
    photo VARCHAR(255),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Table: students
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    program_id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    mobile VARCHAR(20),
    gender ENUM('Male', 'Female', 'Other'),
    dob DATE,
    enrollment_no VARCHAR(100),
    admission_year YEAR,
    current_sem INT,
    photo VARCHAR(255),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (program_id) REFERENCES programs(id)
);

-- Table: roles
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Table: users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    role_id INT,
    student_id INT,
    teacher_id INT,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

-- Table: documents
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    doc_type ENUM('TC', 'MC', 'Marksheets') NOT NULL,
    file_path VARCHAR(255),
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

---

✨ Sample INSERT Statements (2 per table)

-- Sample Universities
INSERT INTO universities (name, type, location, state, website) VALUES 
('Tamil Nadu State University', 'Public', 'Chennai', 'Tamil Nadu', 'http://tnsu.edu.in'),
('Global Tech University', 'Private', 'Coimbatore', 'Tamil Nadu', 'http://gtu.ac.in');

-- Sample Colleges
INSERT INTO colleges (university_id, name, category, location, email, contact_no) VALUES 
(1, 'Government Arts College', 'Affiliated', 'Salem', 'gac@example.com', '0427-1234567'),
(2, 'GTU School of Engineering', 'Campus', 'Coimbatore', 'engg@gtu.ac.in', '0422-7654321');

-- Sample Departments
INSERT INTO departments (college_id, name, code) VALUES 
(1, 'Computer Science', 'CSE'),
(2, 'Electronics and Communication', 'ECE');

-- Sample Programs
INSERT INTO programs (department_id, name, level, duration_years) VALUES 
(1, 'B.Sc Computer Science', 'UG', 3),
(2, 'M.E Communication Systems', 'PG', 2);

-- Sample Courses
INSERT INTO courses (program_id, code, name, semester, credits) VALUES 
(1, 'CS101', 'Programming Fundamentals', 1, 4),
(2, 'EC501', 'Digital Signal Processing', 1, 4);

-- Sample Teachers
INSERT INTO teachers (department_id, name, email, mobile, designation, qualification, doj, photo) VALUES 
(1, 'Dr. R. Meena', 'meena@edu.in', '9000000001', 'Professor', 'Ph.D', '2010-06-15', 'meena.jpg'),
(2, 'Mr. K. Raj', 'raj@gtu.ac.in', '9000000002', 'Assistant Professor', 'M.Tech', '2018-07-10', 'raj.jpg');

-- Sample Students
INSERT INTO students (department_id, program_id, name, email, mobile, gender, dob, enrollment_no, admission_year, current_sem, photo) VALUES 
(1, 1, 'Anita Sharma', 'anita@edu.in', '9876543210', 'Female', '2003-04-22', 'UG2023CS001', 2023, 2, 'anita.jpg'),
(2, 2, 'Vijay Kumar', 'vijay@gtu.ac.in', '9876509876', 'Male', '2001-11-10', 'PG2024EC002', 2024, 1, 'vijay.jpg');

-- Sample Roles
INSERT INTO roles (name) VALUES 
('Admin'), 
('Teacher'), 
('Student');

-- Sample Users
INSERT INTO users (username, password, email, role_id, student_id, teacher_id) VALUES 
('anita23', 'hashed_pwd1', 'anita@edu.in', 3, 1, NULL),
('meena_r', 'hashed_pwd2', 'meena@edu.in', 2, NULL, 1);

-- Sample Documents
INSERT INTO documents (student_id, doc_type, file_path) VALUES 
(1, 'TC', 'docs/anita_tc.pdf'),
(1, 'Marksheets', 'docs/anita_marksheet1.pdf');

---

Would you like:

• A downloadable .sql file with all the above?

• PHP CRUD interfaces for any/all tables?