Course-Management-System-SQL-DATABASE-PROJECT

SQL database project with full ERD, normalization, table creation, data insertion, and SQL query tests. Academic project for learning DBMS concepts.

Course Management System — Database Project

This repository contains the complete Course Management System project developed for the Introduction to Database (2108) course

---

📘 Project Overview This system manages academic activities and stores data about:

• Students
• Courses
• Departments
• Faculties
• Exams
• Grades
• Registrations

The project ensures data consistency, normalization, and relational integrity following proper DBMS principles.

---

🔍 Key Components

1. ER Diagram

• Represents the relationship between all entities.

2. Normalization

• Converted from UNF → 1NF → 2NF → 3NF for all main relations.

3. Finalized Table Structure

• All entities and associative tables finalized before DDL.

4. DDL Queries (Table Creation)

• Each table created using Oracle SQL.
• Includes primary keys, foreign keys, and constraints.

5. DML Queries (Data Insertion)

• Sample data inserted into all tables.

6. SQL Query Tests

Includes:

• Simple Queries
• Aggregate Queries
• Single-row Subquery
• Multi-row Subquery
• Joins (Left Outer Join, Self Join)
• Views (Simple & Complex)

---

🧑‍💻 Team Members

• Mohammad Asad Bin Jafor — 23-50088-1
• Kazi Arman Alam — 23-50089-1
• Taki Tajuar — 23-50103-1
• Sk Mumitur Rahman Saba — 23-50123-1

---

🛠️ Technologies Used

• Oracle SQL
• SQL Developer
• JDBC/ODBC Concept

---

📁 Project Folder Structure

Course-Management-System-DB-Project/ │ ├── README.md ├── Project_Report.pdf │ ├── ER_Diagram/ │ └── er_diagram.png │ ├── SQL/ │ ├── 01_table_creation.sql │ ├── 02_data_insertion.sql │ ├── 03_simple_queries.sql │ ├── 04_aggregate_queries.sql │ ├── 05_single_row_subquery.sql │ ├── 06_multi_row_subquery.sql │ ├── 07_outer_join.sql │ ├── 08_self_join.sql │ └── 09_views.sql │ └── Screenshots/ ├── tables/ ├── insertions/ ├── queries/ └── views/

📌 01_table_creation.sql** sql CREATE TABLE Departments ( Department_ID NUMBER PRIMARY KEY, Department_Name VARCHAR2(50) );

CREATE TABLE Courses ( Course_ID NUMBER PRIMARY KEY, Course_Name VARCHAR2(100), Description VARCHAR2(255), Credits NUMBER, Department_ID NUMBER, FOREIGN KEY (Department_ID) REFERENCES Departments(Department_ID) );

CREATE TABLE Faculty ( Faculty_ID NUMBER PRIMARY KEY, First_Name VARCHAR2(50), Last_Name VARCHAR2(50), Email VARCHAR2(100) );

CREATE TABLE Faculty_Courses ( Faculty_ID NUMBER, Course_ID NUMBER, PRIMARY KEY (Faculty_ID, Course_ID), FOREIGN KEY (Faculty_ID) REFERENCES Faculty(Faculty_ID), FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID) );

CREATE TABLE Students ( Student_ID NUMBER PRIMARY KEY, First_Name VARCHAR2(50), Last_Name VARCHAR2(50), Email VARCHAR2(100), Date_Of_Birth DATE, CGPA NUMBER(3,2) );

CREATE TABLE Exams ( Exam_ID NUMBER PRIMARY KEY, Exam_Name VARCHAR2(100), Max_Score NUMBER, Exam_Date DATE, Course_ID NUMBER, FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID) );

CREATE TABLE Registrations ( Registration_ID NUMBER PRIMARY KEY, Course_ID NUMBER, Student_ID NUMBER, Registration_Date DATE, FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID), FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID) );

CREATE TABLE Grades ( Grade_ID NUMBER PRIMARY KEY, Exam_ID NUMBER, Student_ID NUMBER, Full_Grade NUMBER, Review VARCHAR2(255), FOREIGN KEY (Exam_ID) REFERENCES Exams(Exam_ID), FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID) );

📌 02_data_insertion.sql sql INSERT INTO Departments VALUES (1, 'CSE'); INSERT INTO Departments VALUES (2, 'EEE');

INSERT INTO Courses VALUES (101, 'Database', 'Intro to DBMS', 3, 1); INSERT INTO Courses VALUES (102, 'Algorithms', 'Algorithm Design', 3, 1);

INSERT INTO Faculty VALUES (1, 'Asad', 'Jafor', 'asad@gmail.com'); INSERT INTO Faculty VALUES (2, 'Arman', 'Alam', 'arman@gmail.com');

INSERT INTO Faculty_Courses VALUES (1, 101); INSERT INTO Faculty_Courses VALUES (2, 102);

INSERT INTO Students VALUES (501, 'Taki', 'Tajuar', 'taki@gmail.com', DATE '2003-05-12', 3.75); INSERT INTO Students VALUES (502, 'Saba', 'Rahman', 'saba@gmail.com', DATE '2004-02-10', 3.80);

INSERT INTO Exams VALUES (1, 'Midterm', 30, DATE '2023-03-20', 101); INSERT INTO Exams VALUES (2, 'Final', 50, DATE '2023-05-15', 101);

INSERT INTO Registrations VALUES (1, 101, 501, DATE '2023-05-01'); INSERT INTO Registrations VALUES (2, 102, 502, DATE '2023-05-01');

INSERT INTO Grades VALUES (1, 1, 501, 27, 'Good'); INSERT INTO Grades VALUES (2, 2, 501, 45, 'Very Good');

📌 03_simple_queries.sql sql SELECT s.Student_ID, s.First_Name, r.Registration_Date FROM Students s JOIN Registrations r ON s.Student_ID = r.Student_ID WHERE r.Course_ID = 101;

📌 04_aggregate_queries.sql sql SELECT AVG(CGPA) AS Avg_CGPA, MAX(CGPA) AS Max_CGPA, MIN(CGPA) AS Min_CGPA, COUNT(*) AS Total_Students FROM Students;

📌 05_single_row_subquery.sql sql SELECT Course_Name, Credits FROM Courses WHERE Course_ID = ( SELECT Course_ID FROM Exams WHERE Max_Score = (SELECT MAX(Max_Score) FROM Exams) );

📌 06_multi_row_subquery.sql sql SELECT First_Name, Last_Name FROM Students WHERE Student_ID IN ( SELECT Student_ID FROM Registrations WHERE Course_ID IN ( SELECT Course_ID FROM Faculty_Courses WHERE Faculty_ID = 1 ) );

---

📌 07_outer_join.sql sql SELECT s.Student_ID, s.First_Name, g.Full_Grade FROM Students s LEFT JOIN Grades g ON s.Student_ID = g.Student_ID;

📌 08_self_join.sql sql SELECT s1.Student_ID, s2.Student_ID FROM Students s1 JOIN Students s2 ON EXTRACT(YEAR FROM s1.Date_Of_Birth) = EXTRACT(YEAR FROM s2.Date_Of_Birth) AND s1.Student_ID <> s2.Student_ID;

📌 09_views.sql

sql CREATE VIEW Faculty_Details AS SELECT Faculty_ID, First_Name, Last_Name, Email FROM Faculty;

CREATE VIEW Registration_Range AS SELECT s.Student_ID, s.First_Name, r.Registration_Date FROM Students s JOIN Registrations r ON s.Student_ID = r.Student_ID WHERE r.Registration_Date BETWEEN DATE '2023-05-25' AND DATE '2023-08-10';

Course-Management-System-DB-Project/ │ ├── README.md ├── Project_Report.pdf │ ├── ER_Diagram/ │ └── er_diagram.png │ ├── SQL/ │ ├── 01_table_creation.sql │ ├── 02_data_insertion.sql │ ├── 03_simple_queries.sql │ ├── 04_aggregate_queries.sql │ ├── 05_single_row_subquery.sql │ ├── 06_multi_row_subquery.sql │ ├── 07_outer_join.sql │ ├── 08_self_join.sql │ └── 09_views.sql │ └── Screenshots/ ├── tables/ ├── insertions/ ├── queries/ └── views/