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
.sqlfile with all the above? -
PHP CRUD interfaces for any/all tables?
