College schema

Created on: Jan 30, 2025

Create user tables.

create database college;

use college;

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    address TEXT,
    dob DATE,
    role ENUM('student', 'professor', 'staff') NOT NULL,  -- Determines role
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    enrollment_number VARCHAR(20) UNIQUE NOT NULL,
    department_id INT NOT NULL,
    admission_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

CREATE TABLE Professors (
    professor_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    employee_number VARCHAR(20) UNIQUE NOT NULL,
    department_id INT NOT NULL,
    hire_date DATE NOT NULL,
    tenure_status BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

We will be creating separate table for different entity like student, professor, staff. Separate tables provide better data integrity, performance, and scalability

Insert entry for student and professor

-- Insert Students into Users Table
INSERT INTO Users (name, email, phone, address, dob, role)
VALUES
('John Doe', 'john.doe@example.com', '9876543210', '123 Main St, NY', '2003-05-15', 'student'),
('Alice Smith', 'alice.smith@example.com', '9234567890', '456 Park Ave, LA', '2004-07-21', 'student'),
('Michael Johnson', 'michael.johnson@example.com', '9123456789', '789 Elm St, TX', '2002-09-10', 'student');

-- Insert Professors into Users Table
INSERT INTO Users (name, email, phone, address, dob, role)
VALUES
('Dr. Robert Brown', 'robert.brown@example.com', '8123456789', '101 College Rd, CA', '1980-02-05', 'professor'),
('Dr. Emily White', 'emily.white@example.com', '8567891234', '202 University Ln, FL', '1975-11-23', 'professor');


-- Insert into Students Table
INSERT INTO Students (user_id, enrollment_number, department_id, admission_date)
VALUES
((SELECT user_id FROM Users WHERE email = 'john.doe@example.com'), 'STU2023001', 1, '2023-08-10'),
((SELECT user_id FROM Users WHERE email = 'alice.smith@example.com'), 'STU2023002', 2, '2023-08-12'),
((SELECT user_id FROM Users WHERE email = 'michael.johnson@example.com'), 'STU2023003', 3, '2023-08-15');


-- Insert into Professors Table
INSERT INTO Professors (user_id, employee_number, department_id, hire_date, tenure_status)
VALUES
((SELECT user_id FROM Users WHERE email = 'robert.brown@example.com'), 'EMP1001', 1, '2010-06-15', TRUE),
((SELECT user_id FROM Users WHERE email = 'emily.white@example.com'), 'EMP1002', 2, '2015-09-20', FALSE);

Update table to have the options of soft delete.

ALTER TABLE Users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
ALTER TABLE Students ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
ALTER TABLE Professors ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

Transition a student to teacher.

-- Step 1: Update the User Role to 'professor'
UPDATE Users
SET role = 'professor'
WHERE user_id = 1;

-- Step 2: Soft delete the Student record
UPDATE Students
SET is_active = FALSE
WHERE user_id = 1;

-- Step 3: Insert a new Professor record
INSERT INTO Professors (user_id, employee_number, department_id, hire_date, tenure_status, is_active)
VALUES (1, 'EMP2001', 1, CURDATE(), FALSE, TRUE);

Table for Departments and Courses.

CREATE TABLE Departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL,
    department_head INT,  -- This will be a foreign key referring to the Professor who heads the department
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (department_head) REFERENCES Professors(professor_id) ON DELETE Cascade
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    professor_id INT NOT NULL,  -- professor_id remains NOT NULL
    credits INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE CASCADE,
    FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
);

Create a view to fetch all information of professor.


CREATE VIEW ProfessorDetails AS
SELECT
    u.user_id,
    u.name,
    u.email,
    u.phone,
    u.address,
    u.dob,
    u.created_at AS user_created_at,
    p.professor_id,
    p.employee_number,
    p.department_id,
    p.hire_date,
    p.tenure_status,
    p.is_active AS professor_is_active,
    d.department_name
FROM
    Users u
JOIN
    Professors p ON u.user_id = p.user_id
JOIN
    Departments d ON p.department_id = d.department_id
WHERE
    u.is_active = TRUE AND p.is_active = TRUE;

Query to get count of professor who were student in past.

select count(*) as student_as_professor_count from Professors p inner join
Students s
where p.user_id = s.user_id;

Other tables can be