Its a management system of a college in which students and there courses can be added. We can display the list of student with their opted courses.
mysql express ejs body-parser
Our first page is the homepage in which their are 3 links: EnterStudent,Display,Course
#EnterStudent In enterStudent one needs to fill out name,phone,city and after that click on submit button to enter student.
#Display In display link one needs to fill student name and then click on submit button, after submitting the page will redirect it to the getinfo page.
#course In course link one need to fill student_id,course_id to insert the course after submitting the page will redirect it to the insertcourse page where message is highlighted "course inserted"
-- phpMyAdmin SQL Dump -- version 5.1.1 -- https://www.phpmyadmin.net/
-- Host: 127.0.0.1 -- Generation Time: Dec 22, 2021 at 07:11 AM -- Server version: 10.4.22-MariaDB -- PHP Version: 7.3.33
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8mb4 */;
CREATE TABLE course
(
course_id
int(10) NOT NULL,
course_name
varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO course
(course_id
, course_name
) VALUES
(1, 'java'),
(2, 'python'),
(3, 'ds'),
(4, 'nodejs');
CREATE TABLE department
(
department_id
int(10) NOT NULL,
department_name
varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO department
(department_id
, department_name
) VALUES
(1, 'cse'),
(2, 'Civil'),
(4, 'bca'),
(5, 'mca'),
(6, 'bba');
CREATE TABLE student
(
student_id
int(10) NOT NULL,
name
varchar(20) NOT NULL,
phone
int(10) NOT NULL,
city
varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO student
(student_id
, name
, phone
, city
) VALUES
(1, 'aahna', 214748364, 'jammu'),
(2, 'kamal', 214748367, 'chandigarh'),
(3, 'riya', 214743647, 'ambala'),
(4, 'priya', 247483647, 'bihar'),
(22, 'kamal', 23567456, 'klimo;'),
(43, 'chirag', 78, 'oo'),
(53, 'chirag', 23466, 'hkh'),
(54, 'payal', 4454545, 'jijijj'),
(55, 'piya', 2232311, 'kkkk'),
(56, 'ritu', 33451233, 'mmmlk'),
(57, 'tina', 12673, 'ssds'),
(58, 'tina', 5565656, 'sdnjsckj'),
(59, 'tina', 7878787, 'vsdc'),
(60, 'yellow', 333333, 'rrrrrr'),
(61, 'taran nandha', 56783456, 'chd');
CREATE TABLE student_course
(
student_id
int(10) NOT NULL,
course_id
int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO student_course
(student_id
, course_id
) VALUES
(1, 1),
(1, 2),
(1, 4),
(2, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(3, 3),
(3, 4),
(54, 2),
(55, 2),
(56, 2),
(57, 1),
(58, 1),
(59, 2),
(60, 1),
(1, 2),
(1, 3),
(1, 1),
(1, 4),
(1, 3),
(2, 1),
(1, 4);
CREATE TABLE student_department
(
student_id
int(11) NOT NULL,
department_id
int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO student_department
(student_id
, department_id
) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(60, 1);
CREATE TABLE teacher
(
teacher_id
int(10) NOT NULL,
teacher_name
varchar(25) NOT NULL,
teacher_address
varchar(50) NOT NULL,
teacher_phone
int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO teacher
(teacher_id
, teacher_name
, teacher_address
, teacher_phone
) VALUES
(1, 'vishali', 'chandigarh', 1234567890),
(2, 'rahul', 'chandigarh', 1234567899),
(3, 'renu', 'chandigarh', 1234567897),
(4, 'radhika', 'chandigarh', 1234567898);
ALTER TABLE course
ADD PRIMARY KEY (course_id
);
ALTER TABLE department
ADD PRIMARY KEY (department_id
);
ALTER TABLE student
ADD PRIMARY KEY (student_id
),
ADD UNIQUE KEY student_phone
(phone
);
ALTER TABLE student_course
ADD KEY student_id
(student_id
),
ADD KEY course_id
(course_id
);
ALTER TABLE student_department
ADD KEY student_id
(student_id
),
ADD KEY department_id
(department_id
);
ALTER TABLE teacher
ADD PRIMARY KEY (teacher_id
);
ALTER TABLE course
MODIFY course_id
int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE department
MODIFY department_id
int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE student
MODIFY student_id
int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=62;
ALTER TABLE teacher
MODIFY teacher_id
int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE student_course
ADD CONSTRAINT student_course_ibfk_1
FOREIGN KEY (student_id
) REFERENCES student
(student_id
),
ADD CONSTRAINT student_course_ibfk_2
FOREIGN KEY (course_id
) REFERENCES course
(course_id
);
ALTER TABLE student_department
ADD CONSTRAINT student_department_ibfk_1
FOREIGN KEY (student_id
) REFERENCES student
(student_id
),
ADD CONSTRAINT student_department_ibfk_2
FOREIGN KEY (department_id
) REFERENCES department
(department_id
);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;