Dbms

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 19

(An Autonomous Institute Affiliated to Visvesvaraya Technological University, Belagavi)

(Accredited by NAAC, UGC with 'A' Grade)

DEPARTMENT OF INFORMATION SCIENCE AND


ENGINEERING

REPORT ON
STUDENT MANAGEMENT DATABASE SYSTEM
(Subject Code: 22AIS403)
Submitted in partial fulfilment of award of the Degree of
BACHELOR OF ENGINEERING
in
ARTIFICIAL INTELLIGENCE AND MACHINE LEARNING

Submitted By
ANUBHI SINGH

1DA22AI004
SEMESTER IV
Submitted for the Academic year 2023-2024
UNDER THE GUIDANCE OF
Smt. Shilpa Biradar
Assistant Professor, Dept. of ISE, Dr AIT, Bengaluru-560056

1|Page
2|Page
ACKNOWLEDGEMENT

The satisfaction and euphoria that accompany the successful completion of any
task would be incomplete without the mention of the people who made it
possible. So with gratitude, I acknowledge all those whose guidance and
encouragement crowned my effort with success.

First and foremost, I would like to thank His Holiness Principal, DR.AIT,
Bangalore for providing an opportunity to carry out the DBMS PROJECT
(21CSL55) as a part of my curriculum in the partial fulfillment of the degree
course.

I express my sincere gratitude to our beloved Head of the department, Dr.


Vijayakumar sir, for his cooperation and encouragement at all the moments of
my approach.

It is my pleasant duty to place on record my deepest sense of gratitude to my


respected guide and mini-project coordinator Mrs. Shilpa Biradar, Assistant
Professor for the constant encouragement, valuable help, and assistance in every
possible way.

I would like to thank all ISE Department teachers and non-teaching staff for
providing me with their valuable guidance and for being there at all stages of
my work.

Regards,
Anubhi Singh
(1DA22AI004)

Abstract
3|Page
The Student Management System project addresses the inefficiencies
of manual student management processes within educational
institutions. Through analysis of the shortcomings of the current
system, it was identified that manual processes are time-consuming
and costly. To mitigate these challenges, the Student Management
System software was developed to streamline student-related
activities.
This system allows users to register as either students or
administrators. Administrators have the authority to add, edit, and
delete user accounts, while students can manage their own profiles.
Additionally, administrators can input, edit, and delete student marks,
which are accessible to all users.
By automating administrative tasks and providing a centralized
platform for student management, the Student Management System
aims to enhance efficiency and reduce costs for educational
institutions. This abstract provides an overview of the system's
objectives, functionalities, and benefits, highlighting its potential to
improve the academic experience for students and school authorities
alike.

4|Page
CONTENTS

SI NO. CHAPTER PAGE NO

1. Introduction 6-7

2. Tools required 8-10

3. ER Diagram 11

4. Schema Diagram 12

5. Installation and Creation 13

6. Queries 14-18

INTRODUCTION
5|Page
PROJECT DESCRIPTION: In today's educational landscape, the efficient
management of student data and administrative tasks is paramount for
educational institutions to streamline operations and enhance overall
productivity. A Student Management System (SMS) serves as a comprehensive
solution to address the diverse needs of educational institutions, ranging from
managing student records to facilitating communication between stakeholders.
This system integrates various functionalities to automate routine tasks, improve
data accuracy, and enhance collaboration among administrators, educators,
students, and parents. In summary, the Student Management System represents
a vital tool for modern educational institutions seeking to enhance efficiency,
transparency, and collaboration across all facets of their operations. By
harnessing the power of technology, educational institutions can embrace
innovation and adapt to the evolving needs of students, educators, and
administrators in an increasingly digital world.

PROPOSED SOLUTION: The proposed solution for a Student Management


System (SMS) should be comprehensive, user- friendly, and customizable to
meet the specific needs of educational institutions. Below are key components
and features that could be included in the proposed solution: Implement a
centralized database to store and manage student information, including
personal details, contact information, academic history, attendance records, and
health information. This database should be easily accessible and searchable by
authorized administrators. Develop features to streamline the enrollment and
registration process, allowing students to register for courses online, view their
class schedules, and track their academic progress. Enable administrators to
record and track student academic performance, including grades, transcripts,
and course history. Implement features for generating academic reports,
transcripts, and certificates.

PURPOSE: The purpose of a Student Management Portal is to serve as a


centralized platform that facilitates various aspects of student administration,
communication, and collaboration within an educational institution.

SCOPE: The scope of a Student Management Portal encompasses various


functionalities and features aimed at addressing the diverse needs of educational
institutions, students, educators, parents, and administrators.

Course Management Add Courses: Administrators will have the ability to add
new courses to the system, including details such as course name, code,
description, and other relevant information. Add Subjects: Within each course,
administrators can define subjects or modules, specifying details such as subject

6|Page
name, code, credits, and any prerequisites.

Student Registration and Management Register Students: The system will


facilitate student registration, allowing new students to create accounts and
provide necessary personal information such as name, email, contact details,
and academic background. Manage Student Profiles: Students will be able to
manage their profiles, update personal information, and view their academic
history.

Session Management Choose Sessions: Students will have the option to choose
sessions or classes for their enrolled courses. Sessions may include information
such as session timing, instructor details, and location

TOOLS USED
7|Page
MySQL
MySQL is a free open-source relational database management system (RDBMS) ideal for both small
and large applications. It is commonly used for backend operations such as adding and managing
products, orders, payment information, and other activities.
MySQL is a popular open-source relational database management system
(RDBMS). It is widely used for web applications and can run on various
platforms, including Windows, Linux, and macOS. MySQL uses SQL
(Structured Query Language) to manage and manipulate data. Here’s a brief
introduction to MySQL:

Key Features

1. Open Source: MySQL is free to use and modify, which makes it accessible to
a wide range of users and developers.
2. Cross-Platform: MySQL can run on multiple operating systems, including
Windows, Linux, and macOS.
3. Reliability and Performance: MySQL is known for its high performance,
reliability, and ease of use.
4. Scalability: It supports large databases and can handle thousands of tables
and millions of rows of data.
5. Security: MySQL provides robust security features, including user
authentication, SSL support, and data encryption.
6. Replication: MySQL supports master-slave replication, allowing data to be
replicated from one server to another for redundancy and load balancing.
7. Community and Support: Being open-source, MySQL has a large community
of developers and users who contribute to its development and provide support.

Basic Concepts

1. Database: A structured collection of data. In MySQL, a database is a


collection of tables.
2. Table: A table is a collection of related data entries and consists of columns
and rows.
3. Row: A single, data record in a table.
4. Column: A field in a table. All the rows in the table have the same columns.
5. Primary Key: A unique identifier for a table row. No two rows can have the
same primary key value.
6. Foreign Key: A field in one table that uniquely identifies a row in another
table, creating a relationship between the two tables.

Basic SQL Commands

8|Page
1. Creating a Database:
sql
CREATE DATABASE mydatabase;

2. Creating a Table:
sql
CREATE TABLE students (
student_id INT AUTO_INCREMENT,
name VARCHAR(100),
age INT,
PRIMARY KEY (student_id)
);

3. Inserting Data:
sql
INSERT INTO students (name, age) VALUES ('Alice', 23);

4. Selecting Data:
sql
SELECT * FROM students;

5. Updating Data:
sql
UPDATE students SET age = 24 WHERE name = 'Alice';

6. Deleting Data:
sql
DELETE FROM students WHERE name = 'Alice';

7. Joining Tables:
sql
SELECT students.name, courses.course_name
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.course_id;

MySQL Architecture

9|Page
1. Client-Server ModeL: MySQL uses a client-server architecture where the
MySQL server manages the databases and client applications connect to the
server to request data.
2. Storage Engines: MySQL supports multiple storage engines (e.g., InnoDB,
MyISAM), each optimized for different use cases.
3. SQL Parser and Optimizer: MySQL parses SQL queries and optimizes them
for efficient execution.
4. Query Cache: MySQL can cache the results of queries to speed up
subsequent queries.

Conclusion

MySQL is a versatile and powerful RDBMS that is suitable for a wide range of
applications. Its ease of use, performance, and community support make it a
popular choice for developers and businesses alike.

ER DIAGRAM-

10 | P a g e
11 | P a g e
Schema Diagram-

12 | P a g e
INSTALLATION and DATABASE
CREATION

13 | P a g e
ALL TABLES IN DATABASE:

TABLE ADMINISTRATOR:

14 | P a g e
TABLE STUDENT:

TABLE ATTENDANCE:

15 | P a g e
TABLE COURSE:

TABLE DEPARTMENT:

16 | P a g e
TABLE SECTION:

TABLE EXAMS:

17 | P a g e
Queries
1. Give total number of students appeared for BE exam.
Query : SELECT COUNT(DISTINCT Roll_no) AS total_students
FROM attendance
WHERE Course = 'BE';

2. Give the administrator details administering more than 2


departments.
Query : select a.admin_id, a.admin_name, COUNT(d.dept_id)>2 AS
num_departments

3. Give student whose total attendance is more than 75.

18 | P a g e
Query : SELECT s.Roll_no, s.name, a.Total_attendance AS Total_attendance
FROM student s
JOIN attendance a ON s.Roll_no = a.Roll_no
GROUP BY s.Roll_no, s.name
HAVING (a.Total_attendance) > 75;

4. Give the course details which has more that 22 classes in


the whole semester.
Query : SELECT c.course_id, c.classes
FROM course c JOIN section s ON c.course_id = s.course_idGROUP BY

c.course_id, c.classes HAVING c.classes >22;

19 | P a g e

You might also like