Scribd
Upload
4 views2 pages

Advanced Student Management System

The document outlines the development of an Advanced Student Management System using PostgreSQL on a Linux platform, focusing on managing student data through various tables. It includes the design of tables for student details, academic records, and semester marks, along with functions for CGPA calculation and triggers for fail detection. Additionally, it describes the project flow and important queries for data retrieval and reporting.

Uploaded by

sejalnaik0602
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Advanced Student Management System

The document outlines the development of an Advanced Student Management System using PostgreSQL on a Linux platform, focusing on managing student data through various tables. It includes the design of tables for student details, academic records, and semester marks, along with functions for CGPA calculation and triggers for fail detection. Additionally, it describes the project flow and important queries for data retrieval and reporting.

Uploaded by

sejalnaik0602
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Advanced Student Management System (PostgreSQL, Linux)

Overview:

This project is an advanced database system built using PostgreSQL. It manages student personal,
academic, and performance data with automation using functions, triggers, and views.

Database Design:

1. Students Table – Stores personal details.


CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
class VARCHAR(50) NOT NULL,
dob DATE,
pob VARCHAR(100),
phone VARCHAR(15) UNIQUE,
address TEXT,
aadhaar_no VARCHAR(12) UNIQUE,
admission_date DATE DEFAULT CURRENT_DATE
);

2. Academic Details Table – Stores 12th marks and CAP round.

CREATE TABLE academic_details (


academic_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
marks_12th DECIMAL(5,2) CHECK (marks_12th BETWEEN 0 AND 100),
cap_round INT CHECK (cap_round BETWEEN 1 AND 8)
);

3. Semester Marks Table – Stores marks of all semesters.

CREATE TABLE semester_marks (


sem_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
sem1 DECIMAL(5,2),
sem2 DECIMAL(5,2),
sem3 DECIMAL(5,2),
sem4 DECIMAL(5,2),
sem5 DECIMAL(5,2),
sem6 DECIMAL(5,2),
sem7 DECIMAL(5,2),
sem8 DECIMAL(5,2)
);

Function (CGPA Calculation):

CREATE FUNCTION calculate_cgpa(sid INT)


RETURNS DECIMAL(3,2) AS $$
DECLARE result DECIMAL(3,2);
BEGIN
SELECT (sem1+sem2+sem3+sem4+sem5+sem6+sem7+sem8)/8
INTO result
FROM semester_marks WHERE student_id = sid;
RETURN result;
END;
$$ LANGUAGE plpgsql;

Trigger (Fail Detection):

CREATE FUNCTION fail_warning()


RETURNS TRIGGER AS $$
BEGIN
IF NEW.sem1 < 4 OR NEW.sem2 < 4 THEN
RAISE NOTICE 'Student may have failed!';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
View (Full Report):
CREATE VIEW full_student_report AS
SELECT [Link], [Link], a.marks_12th, a.cap_round,
sm.sem1, sm.sem2, sm.sem3, sm.sem4,
sm.sem5, sm.sem6, sm.sem7, sm.sem8,
calculate_cgpa(s.student_id) AS cgpa
FROM students s
JOIN academic_details a ON s.student_id = a.student_id
JOIN semester_marks sm ON s.student_id = sm.student_id;

Project Flow:

Start → Create Tables → Insert Data → Apply Constraints → Trigger Execution → CGPA
Calculation → View Report → End

Important Queries:

SELECT * FROM full_student_report;


SELECT name, calculate_cgpa(student_id) FROM students;
SELECT AVG(calculate_cgpa(student_id)) FROM students;

Resume Description:

Developed an advanced Student Management System using PostgreSQL with normalized schema,
triggers, functions, and views for automated data processing and reporting.

You might also like