STUDENT INFORMATION SYSTEM

NADEEM KHAN BU22CSEN0300262

P. SUPARNA CHANDRA BU22CSEN0300261

T. HARISH REDDY BU22CSEN0300111

D. MOHAN SANDEEP BU22CSEN0300373

M. KOUSHIK CHOWDARY BU22CSEN0300282

DATABASE MANAGEMENT SYSTEM

GITAM (DEEMED TO BE UNIVERSITY), BENGALURU CAMPUS

OCTOBER 2024

1|Page
1.AIM:

The aim of this project is to design and implement a Student Information System that enables
administrators and faculty to efficiently manage and access student details, course
information, hostel assignments, and grade records. This system will provide real-time access
to essential information, including student profiles, course enrollments, academic
performance, and housing arrangements, thereby facilitating seamless data management
within an academic institution.

DESCRIPTION:

A Student Information System is essential for managing the academic and administrative
information related to students within an educational institution. The system enables users to
access details about student enrollment, courses, grades, and hostel accommodations, as well
as perform essential operations on these data points.

In this project, the system is designed using a relational database model with the following
core components:
• Department: Represents various academic departments within the institution, each
housing multiple courses.

• Course: Contains the list of courses offered, specifying details such as credits and the
associated department.

• Student: Manages individual student information, including name, contact details,

department affiliation, and enrollment records.

• Hostel: Manages the hostel details where students are assigned, including hostel name
and location.

• Grades: Records the grades achieved by students in each course, allowing faculty and
administrators to monitor academic progress.
2. ER DIAGRAM:

The ER Diagram includes the entities attributes and relationships necessary to model the
student information system important entities could include :

• Student( Student_id,Student_name{
first_name,middle_name,last_name},Age,Date_of_birth,Gender,Phone_no,Address,M
ail_id)
• Grades (course_grades)
• Courses ( Course_id, Course_name,Course_type,credits)
• Department(Dept_id,Dept_name,Building)
• Hostel(Hostel_name,Location)

2|Page
3. Create table and insert records
✓ Department
CREATE TABLE Department (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(30),
Building VARCHAR(30)
);

Inserting records:

INSERT INTO Department VALUES (1, 'Computer Science', 'Science Block');

INSERT INTO Department VALUES (2, 'Electrical Engineering', 'Engineering Block');

INSERT INTO Department VALUES (3, 'Mechanical Engineering', 'Main Building');

INSERT INTO Department VALUES (4, 'Civil Engineering', 'East Wing');

INSERT INTO Department VALUES (5, 'Biotechnology', 'Biotech Block');

INSERT INTO Department VALUES (6, 'Physics', 'Science Annex');

INSERT INTO Department VALUES (7, 'Chemistry', 'Chemistry Block');

3|Page
INSERT INTO Department VALUES (8, 'Mathematics', 'Math Wing');

INSERT INTO Department VALUES (9, 'Psychology', 'Social Sciences Block');

INSERT INTO Department VALUES (10, 'Business Administration', 'Commerce Block');

✓ Courses

CREATE TABLE Course (

Course_ID INT PRIMARY KEY,

Course_Name VARCHAR(20),

Credits INT,

Dept_ID INT REFERENCES Department(Dept_ID)

);
Inserting Records:

4|Page
Inserting Records

5|Page
✓ Students

CREATE TABLE Student (

Student_ID INT PRIMARY KEY,

Name VARCHAR(20),

Email VARCHAR(20),

Dept_ID INT REFERENCES Department(Dept_ID),

Address VARCHAR(30),

Gender VARCHAR(10),

Age INT,

Phone_NO INT
);

Inserting Records:

6|Page
✓ Hostel

CREATE TABLE Hostel (

Hostel_Name VARCHAR(20),

Location VARCHAR(20)

);

7|Page
Inserting Records:
INSERT INTO Hostel VALUES ('Sunrise Hall', 'North Campus');

INSERT INTO Hostel VALUES ('Maple Lodge', 'East Campus');

INSERT INTO Hostel VALUES ('Willow Inn', 'South Campus');

INSERT INTO Hostel VALUES ('Oak Residence', 'West Campus');

INSERT INTO Hostel VALUES ('Pine Hall', 'Central Campus');

INSERT INTO Hostel VALUES ('Cedar Villa', 'North Campus');

INSERT INTO Hostel VALUES ('Spruce House', 'East Campus');

INSERT INTO Hostel VALUES ('Birch Hall', 'South Campus');

INSERT INTO Hostel VALUES ('Elm Lodge', 'West Campus');

INSERT INTO Hostel VALUES ('Fir Residence', 'Central Campus');

8|Page
✓ Grades

CREATE TABLE Grades (

Student_ID INT REFERENCES Student(Student_ID),
Course_ID INT REFERENCES Course(Course_ID),
Grade CHAR(2)
);

Inserting Records:
INSERT INTO Grades VALUES (1, 101, 'A');
INSERT INTO Grades VALUES (2, 102, 'B');
INSERT INTO Grades VALUES (3, 103, 'A');
INSERT INTO Grades VALUES (4, 104, 'C');
INSERT INTO Grades VALUES (5, 105, 'B');
INSERT INTO Grades VALUES (6, 106, 'A');
INSERT INTO Grades VALUES (7, 107, 'A');
INSERT INTO Grades VALUES (8, 108, 'B');
INSERT INTO Grades VALUES (9, 109, 'C');
INSERT INTO Grades VALUES (10, 110, 'B');

9|Page
10 | P a g e
4.CREATING A VIEW

CREATE VIEW StudentGradesView AS

SELECT

Student.Student_ID,

Student.Name AS Student_Name,

Department.Dept_Name AS Department,

Course.Course_Name AS Course_Name,

Grades.Grade

FROM
Student, Grades, Course, Department
WHERE

Student.Student_ID = Grades.Student_ID

AND Grades.Course_ID = Course.Course_ID

AND Student.Dept_ID = Department.Dept_ID;

11 | P a g e
5.CREATING USER AND GRANT PRIVILAGES

CREATE USER Raman IDENTIFIED BY password;

12 | P a g e
Granting insert , update privilages

6.Use of joins
• Inner join
SELECT

Student.Student_ID,

Student.Name AS Student_Name,

Course.Course_Name,

Grades.Grade

FROM

Student
INNER JOIN

Grades ON Student.Student_ID = Grades.Student_ID

INNER JOIN

Course ON Grades.Course_ID = Course.Course_ID;

13 | P a g e
• Left Outer Join

SELECT
Student.Student_ID,
Student.Name AS Student_Name,
Course.Course_Name,
Grades.Grade
FROM
Student
LEFT JOIN
Grades ON Student.Student_ID = Grades.Student_ID
LEFT JOIN
Course ON Grades.Course_ID = Course.Course_ID;

• RIGHT OUTER JOIN

SELECT
Student.Student_ID,
Student.Name AS Student_Name,

14 | P a g e
Course.Course_Name,
Grades.Grade
FROM
Student
RIGHT JOIN
Grades ON Student.Student_ID = Grades.Student_ID
RIGHT JOIN
Course ON Grades.Course_ID = Course.Course_ID;

• FULL OUTER JOIN

SELECT
Student.Student_ID,
Student.Name AS Student_Name,
Course.Course_Name,
Grades.Grade
FROM
Student
FULL OUTER JOIN
Grades ON Student.Student_ID = Grades.Student_ID
FULL OUTER JOIN
Course ON Grades.Course_ID = Course.Course_ID;

15 | P a g e
7. PL/SQL procedures:
CREATE TABLE LogDetails (

Log_ID INT PRIMARY KEY,

Operation VARCHAR(510),

TableName VARCHAR(510),

ModificationDate TIMESTAMP

);

16 | P a g e
CREATE OR REPLACE PROCEDURE LogUpdate AS

BEGIN

INSERT INTO LogDetails VALUES (1, 'INSERT', 'STUDENT', SYSTIMESTAMP);

END;
/

INSERT INTO LogDetails VALUES (1, 'INSERT', 'STUDENT',

CURRENT_TIMESTAMP);

INSERT INTO LogDetails VALUES (2, 'UPDATE', 'STUDENT',

CURRENT_TIMESTAMP);

SELECT * FROM LogDetails;

17 | P a g e
8.Delete,Drop and Truncate

DELETE

DELETE FROM Grades WHERE Grade = 'C';

SELECT * FROM grades;

TRUNCATE

18 | P a g e
DROP

DROP TABLE Hostel;

SELECT * FROM Hostel;

19 | P a g e