COLLEGE DATA MANAGEMENT SYSTEM

DBMS Project Report

Submitted by - 1NT19CS059 Chinmay Hegde

1NT19CS056 Chamarthi Dedeepya

Subject teacher- Bhuvaneshwari mam

Submitted on - 08/07/2021
 INDEX

[Link]

[Link] description

[Link]

[Link] and Executed output

[Link]
 Introduction
The main objective of college management system is to easily carry out all functionalities of
a college or university. Using this system you can manage all college management work like
a fees submission, student personal information like phone number ,address and gender and
other details ,subject details ,department details,marks and attendance details . Using this
college management system you can view or update data and information about students and
faculty easily.

Project Description
This project is about creating a database about college data management. This has the
information about student like student id,name,phone number, address,gender,the admission
date ,academic year ,section they are studying in,cet marks/or any other valid marks for their
admission in the college.
This particular database also contains the information of faculty like faculty id ,faculty
name,gender,salary and personal information like phone number, address of the faculty.
We will also store the information regarding the departments in the college and the
information regarding fee structure and subjects .we will have department id,department
name and the hod id of the respective departments.
Regarding fee we will have the information of the hostel fee and tuition fee separately of each
student which is to be updated whenever the amount is paid by the particular student.
Regarding subjects we will have information of the subject id,subject name ,faculty id whose
is handling that particular subject ,we will also have department id to know which department
that particular subject belongs to.
Finally we will have information of marks of students ,for this we will collect the information
like student id, marks in the particular subject and also the attendance in that particular
subject these things should be updated in the marks table .
 Objective

The objectives of this college data management system are:

➔ To reduce paperwork.
➔ Reduced operational time
➔ Increased accuracy and reliability
➔ Increased operational efficiency
➔ Data security
➔ New features can be added as per the requirements.
➔ Easy searching of student ,faculty information.
➔ Main goal of this system is to automate the process carried out
in the organisation.
➔ Storing and retrieving of information is easy.
Code & Executed Output :

create database College_Database;

show databases;

use College_Database;

create table departments(id int not null,name varchar(30) not null,hod_id int(10) default null);
ALTER TABLE departments ADD PRIMARY KEY (id);

CREATE TABLE faculty ( id int NOT NULL,first_name varchar(25) NOT NULL,last_name

varchar(25) NOT NULL,department_id int NOT NULL,phone varchar(10) DEFAULT
NULL);
ALTER TABLE faculty ADD PRIMARY KEY (`id`);
ALTER TABLE faculty ADD FOREIGN KEY (department_id) REFERENCES departments
(id);

CREATE TABLE students ( roll_num int NOT NULL, first_name varchar(25) NOT NULL,
last_name varchar(25) NOT NULL, gender char, address varchar(50), department_id int
NOT NULL, academic_year int,section char NOT NULL, phone varchar(10) NOT NULL,
admission_date date NOT NULL, cet_marks int NOT NULL);
ALTER TABLE students ADD PRIMARY KEY (roll_num);
ALTER TABLE students FOREIGN KEY (department_id) REFERENCES departments (id);
CREATE TABLE subjects (id int NOT NULL, department_id int NOT NULL, name
varchar(50) NOT NULL,faculty_id int NOT NULL);
ALTER TABLE subjects ADD PRIMARY KEY (id);
ALTER TABLE subjects ADD FOREIGN KEY (department_id) REFERENCES
departments (id);
ALTER TABLE subjects ADD FOREIGN KEY (faculty_id) REFERENCES faculty (id);

CREATE TABLE marks (student_roll_num int NOT NULL, subject_id int NOT NULL,
marks int NOT NULL check(marks<=100), attendance int check(attendance<=100));
ALTER TABLE marks ADD PRIMARY KEY (student_roll_num,subject_id);
ALTER TABLE subjects ADD FOREIGN KEY (department_id) REFERENCES
departments (id);
ALTER TABLE subjects ADD FOREIGN KEY (faculty_id) REFERENCES faculty (id);

CREATE TABLE fees (sid int,hostel_fees int,tuition_fees int);

ALTER TABLE fees ADD PRIMARY KEY (sid);
ALTER TABLE fees ADD FOREIGN KEY (sid) REFERENCES students(roll_num);

show tables;

desc departments;
desc faculty;

desc students;

desc subjects;
desc marks;

desc fees;

insert into departments values(1,"CSE",1111);

insert into departments values(2,"ISE",2222);
insert into departments values(3,"Mech",3333);
insert into departments values(4,"Civil",4444);

insert into faculty values(1111,'Manisha', 'Marth',1, '9845167849','F','Bengaluru',50000);

insert into faculty values(2222,'Anil', 'Bhadgale',2, '9845167849','M','Bengaluru',60000);
insert into faculty values(3333,'Lakshmi', 'Kanade', 1, '8983547891','F','Mysore',40000);
insert into faculty values(4444,'Minal', 'Apsangi', 4, '7704159854','M','Mysore',70000);
insert into faculty values(5555,'Shubha', 'Dixit', 4, '8804159854','F','Mangaluru',50000);

insert into students values(180101, 'Shreesh', 'Kamat','M','Mangaluru', 1,4,'A' ,'7474986413',

'2017-08-01', 107);
insert into students values(180102, 'Adwait', 'Bhope','M','Bengaluru', 1,3,'B', '8862451783',
'2017-08-01', 113);
insert into students values(180103, 'Aashay', 'Zanpure','M','Bengaluru', 1,2,'C', '9029742685',
'2018-08-21', 140);
insert into students values(180104,'Atharva', 'Dhekne','M','Mysore', 2,2,'C', '9029742685',
'2019-08-21', 132);
insert into students values(180105,'Rashmi', 'Mokashi','F','Mysore', 3,3,'C', '9029742685',
'2018-08-21', 132);

insert into subjects values(11,1,'Object Oriented Programming',1111);

insert into subjects values(22,2,'Discrete Mathematics',2222);
insert into subjects values(12,1,'CCP',1111);
insert into subjects values(33,3,'Automobiles',3333);

INSERT INTO marks VALUES(180101, 11, 80,88);

INSERT INTO marks VALUES(180101, 12, 70,90);
INSERT INTO marks VALUES(180102, 21, 80,75);
INSERT INTO marks VALUES(180102, 33, 90,95);
INSERT INTO marks VALUES(180103, 33, 90,95);

INSERT INTO fees VALUES(180101, 80000, 100000);

INSERT INTO fees VALUES(180102, 0, 100000);
INSERT INTO fees VALUES(180103, 80000, 80000);
INSERT INTO fees VALUES(180104, 80000, 50000);
INSERT INTO fees VALUES(180105, 0, 50000);
QUERIES :

1. Update the phone number of a student whose roll number is 180101.

update students set phone='9029341421' where roll_num=180101;

select phone from students;
2. Delete the marks of a student whose whose attendance is less than 80%.

delete from marks where attendance<80;

select * from marks;

3. List all the students whose CET marks is greater than or equal to 100.

select roll_num,first_name from students where cet_marks>=100;

4. Find the faculties who teaches OOP.

select [Link],f.first_name from faculty f,subjects s where [Link]=s.faculty_id and name='Object

Oriented Programming';
5. Find the average marks of all the students in descending order.

select student_roll_num,first_name,avg(marks) as average from marks,students where

student_roll_num=roll_num group by student_roll_num order by average desc;

6. Create a view of student id, student name,subject id he is studying with marks and
attendance.

select student_roll_num,first_name,avg(marks) as average from marks,students where

student_roll_num=roll_num group by student_roll_num order by average desc;
[Link] a trigger on student table

CREATE TRIGGER delete_student BEFORE DELETE ON students FOR EACH ROW

DELETE FROM marks WHERE student_roll_num = OLD.roll_num;
 Conclusion

➔ The project ‘college Database management system’

has been developed as per the required specifications .It
has been developed using SQL server . This project helps
the admin to get the information student, faculty,
department, subjects , departments.
This project is designed keeping in view the day to day
problems faced in colleges.