🎓 School Database System

This project demonstrates the design and implementation of a simple School Database System using SQL.
It covers core concepts of relational database design, including table creation, primary and foreign keys, and the use of JOIN operations to query relational data effectively.

---

📘 Project Overview

The database simulates a small school system with three main entities:

• Students — represents the students enrolled in the school
• Courses — represents the courses offered
• Enrollments — acts as a linking table connecting students and courses

Through these tables, we perform various SQL operations such as inserting data, creating relationships, and testing different JOINs (INNER, LEFT, RIGHT).

---

🧱 Database Structure

Database Name: school

Tables

1. students

Column	Type	Description
student_id	INT (PK)	Unique identifier for each student
student_name	VARCHAR(100)	Full name of the student
student_age	INT	Age of the student
student_city	VARCHAR(50)	City where the student lives

2. courses

Column	Type	Description
course_id	INT (PK)	Unique identifier for each course
course_name	VARCHAR(100)	Name of the course
course_credit	INT	Course credit value

3. enrollments

Column	Type	Description
enrollment_id	INT (PK)	Unique enrollment record ID
student_id	INT (FK)	References students(student_id)
course_id	INT (FK)	References courses(course_id)
enrollment_date	DATE	Date the student enrolled

---

🧩 SQL Tasks Implemented

1️⃣ Create a database

CREATE DATABASE school; USE school;

2️⃣ Create the students, courses, and enrollments tables

(With primary and foreign keys properly defined.)

3️⃣ Insert data

Each table includes at least three sample records:

INSERT INTO students (student_id, student_name, student_age, student_city) VALUES (1, 'Aleyna Aköz', 22, 'Ankara'), (2, 'Mert Yılmaz', 21, 'İstanbul'), (3, 'Zeynep Demir', 23, 'İzmir'), (4, 'Can Öztürk', 20, 'Bursa');

4️⃣ Perform JOIN operations

🔹 INNER JOIN — Students enrolled in courses SELECT s.student_name, c.course_name, e.enrollment_date FROM enrollments e INNER JOIN students s ON e.student_id = s.student_id INNER JOIN courses c ON e.course_id = c.course_id;

🔹 LEFT JOIN — All students (even if not enrolled) SELECT s.student_name, c.course_name, e.enrollment_date FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id;

🔹 RIGHT JOIN — All courses (even if no students) SELECT s.student_name, c.course_name, e.enrollment_date FROM enrollments e RIGHT JOIN courses c ON e.course_id = c.course_id LEFT JOIN students s ON e.student_id = s.student_id;

⚖️ Comparison of JOIN Results JOIN Type Description Example Outcome INNER JOIN Displays only students who are enrolled in at least one course Excludes students not enrolled LEFT JOIN Displays all students, with NULL for unenrolled ones Shows everyone from Students RIGHT JOIN Displays all courses, with NULL for empty ones Shows every course offered

🧠 Key Learning Outcomes

Understanding relational database design Implementing foreign keys and maintaining referential integrity Using JOIN operations to connect multiple tables Differentiating between INNER, LEFT, and RIGHT joins Writing and testing SQL queries in an organized workflow

🧰 Tools Used

MySQL / SQL Server

Workbench or any SQL IDE Dataset inserted manually via SQL script

💾 How to Run

Copy the full SQL script into your SQL editor.

Execute commands step by step:

Create the database Create tables Insert data Run JOIN queries Verify each query output before saving. Export the final .sql file and commit it to GitHub.

🪶 Author

Aleyna Aköz Sivas Bilim ve Teknoloji Üniversitesi Bilgisayar Mühendisliği • Database Systems Project 📅 October 2025

📜 License

This project is open for academic and educational use under the License. License © 2025 Aleyna Aköz