CollegeManagementSystem

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.

Dependencies

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 */;

-- -- Database: cmss

---

-- -- Table structure for table course

CREATE TABLE course ( course_id int(10) NOT NULL, course_name varchar(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- Dumping data for table course

INSERT INTO course (course_id, course_name) VALUES (1, 'java'), (2, 'python'), (3, 'ds'), (4, 'nodejs');

---

-- -- Table structure for table department

CREATE TABLE department ( department_id int(10) NOT NULL, department_name varchar(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- Dumping data for table department

INSERT INTO department (department_id, department_name) VALUES (1, 'cse'), (2, 'Civil'), (4, 'bca'), (5, 'mca'), (6, 'bba');

---

-- -- Table structure for table student

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;

-- -- Dumping data for table student

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');

---

-- -- Table structure for table student_course

CREATE TABLE student_course ( student_id int(10) NOT NULL, course_id int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- Dumping data for table student_course

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);

---

-- -- Table structure for table student_department

CREATE TABLE student_department ( student_id int(11) NOT NULL, department_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- Dumping data for table student_department

INSERT INTO student_department (student_id, department_id) VALUES (1, 1), (2, 1), (3, 2), (4, 1), (60, 1);

---

-- -- Table structure for table teacher

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;

-- -- Dumping data for table teacher

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);

-- -- Indexes for dumped tables

-- -- Indexes for table course

ALTER TABLE course ADD PRIMARY KEY (course_id);

-- -- Indexes for table department

ALTER TABLE department ADD PRIMARY KEY (department_id);

-- -- Indexes for table student

ALTER TABLE student ADD PRIMARY KEY (student_id), ADD UNIQUE KEY student_phone (phone);

-- -- Indexes for table student_course

ALTER TABLE student_course ADD KEY student_id (student_id), ADD KEY course_id (course_id);

-- -- Indexes for table student_department

ALTER TABLE student_department ADD KEY student_id (student_id), ADD KEY department_id (department_id);

-- -- Indexes for table teacher

ALTER TABLE teacher ADD PRIMARY KEY (teacher_id);

-- -- AUTO_INCREMENT for dumped tables

-- -- AUTO_INCREMENT for table course

ALTER TABLE course MODIFY course_id int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

-- -- AUTO_INCREMENT for table department

ALTER TABLE department MODIFY department_id int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

-- -- AUTO_INCREMENT for table student

ALTER TABLE student MODIFY student_id int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=62;

-- -- AUTO_INCREMENT for table teacher

ALTER TABLE teacher MODIFY teacher_id int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

-- -- Constraints for dumped tables

-- -- Constraints for table student_course

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);

-- -- Constraints for table student_department

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 */;