Scribd
Upload
3 views

SQL Student Management DB

The document outlines the SQL schema for a Student Management Database, including tables for departments, users, programs, students, courses, student_courses, instructors, admin, and instructor_courses. It includes definitions for each table's structure, relationships, and example insert and update statements. The schema is designed to manage various aspects of student and course administration within an educational institution.

Uploaded by

slim
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views

SQL Student Management DB

The document outlines the SQL schema for a Student Management Database, including tables for departments, users, programs, students, courses, student_courses, instructors, admin, and instructor_courses. It includes definitions for each table's structure, relationships, and example insert and update statements. The schema is designed to manage various aspects of student and course administration within an educational institution.

Uploaded by

slim
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

SQL Student Management DB

-- CREATE TABLE departments (


-- id uuid NOT NULL,
-- name varchar(255) NOT NULL,
-- description varchar(255),
-- active boolean DEFAULT true,
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- PRIMARY KEY (id)
-- );

-- CREATE TABLE users (


-- id int,
-- name varchar(255),
-- email varchar(255),
-- password varchar(255),
-- gender ENUM('male', 'female'),
-- contact varchar(255),
-- country_code varchar(255),
-- phone_number varchar(255),
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE programs (


-- id int NOT NULL,
-- name varchar(255),
-- description varchar(255),
-- department_id int,
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- FOREIGN KEY (department_id) REFERENCES departments(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE students (


-- id int,
-- name varchar(255),
-- entry_year int,
-- data_of_birth DATE,
-- gender ENUM('male', 'female'),
-- status varchar(255) DEFAULT 'active',
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- department_id int,
-- program_id int,
-- FOREIGN KEY (department_id) REFERENCES departments(id),
-- FOREIGN KEY (program_id) REFERENCES programs(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE courses (


-- id int,
-- name varchar(255),
-- code varchar(255),
-- description varchar(255),
-- duration int,
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- department_id int,
-- FOREIGN KEY (department_id) REFERENCES departments(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE student_courses (


-- id int,
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- student_id int,
-- course_id int,
-- FOREIGN KEY (student_id) REFERENCES students(id),
-- FOREIGN KEY (course_id) REFERENCES courses(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE instructors (


-- id int,
-- name varchar(255),
-- experience int,
-- title varchar(255),
-- gender enum('male', 'female'),
-- contact varchar(255),
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- user_id int,
-- FOREIGN KEY (user_id) REFERENCES users(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE admin (


-- id int,
-- name varchar(255),
-- role varchar(255),
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- user_id int,
-- FOREIGN KEY (user_id) REFERENCES users(id),
-- PRIMARY KEY (id)
-- )

-- CREATE TABLE instructor_courses (


-- id int,
-- created_at datetime DEFAULT CURRENT_TIMESTAMP,
-- instructor_id int,
-- course_id int,
-- FOREIGN KEY (instructor_id) REFERENCES instructors(id),
-- FOREIGN KEY (course_id) REFERENCES courses(id),
-- PRIMARY KEY (id)
-- )

-- INSERT INTO table1


-- (field1, field2)
-- VALUES (value1, value2);

-- insert into departments


-- (id, name, description, active)
-- values (1, 'SOE', 'School Of Engineering', true)

-- insert into departments


-- (id, name, description, active)
-- values (2, 'SOP', 'School Of Product', true)

-- insert into departments


-- (id, name, description, active)
-- values (3, 'SOD', 'School Of Data', true)

-- select * from departments

-- update departments
-- set name = 'SOD'
-- where id = 3

-- delete from departments


-- where id = 3
-- select id, name, description from departments
-- where active = true or name = 'SOP'

-- insert into programs


-- (id, name, description, department_id)
-- values (1, 'NodeJS', 'Backend Engineering Nodejs', 2)

-- insert into programs


-- (id, name, description, department_id)
-- values (2, 'Python', 'Backend Engineering Python', 1)

-- insert into programs


-- (id, name, description, department_id)
-- values (3, 'Ruby', 'Backend Engineering Ruby', 1)

-- insert into programs


-- (id, name, description, department_id)
-- values (4, 'Product Design', 'Design', 2)

-- insert into programs


-- (id, name, description, department_id)
-- values (5, 'Product Management', 'Management', 2)

-- insert into programs


-- (id, name, description, department_id)
-- values (6, 'Data Science', 'Data', 3)

-- insert into programs


-- (id, name, description, department_id)
-- values (7, 'Data Analysis', 'Data', 3)

-- select * from programs join departments on departments.id = programs.department_id

You might also like