🎓 University Management SQL Project

Author: Behnam Zoghi Roudsari Database: projectsql1
Language: T-SQL (Microsoft SQL Server)

---

📘 Overview

This project designs and implements a relational database system to manage a simplified university environment, including students, departments, courses, teachers, and their relationships.
It demonstrates essential SQL skills such as:

• Database design and normalization
• Table creation with primary and foreign key constraints
• Data insertion, update, and deletion
• Triggers for automatic updates and logging
• Stored procedures and user-defined functions for analytical queries
• Transaction control and rollback mechanisms
• Views for simplified data access

---

🧱 Database Schema

The database projectsql1 includes the following tables:

Table Name	Description
TBLstudent	Stores student information including ID, name, city, and department affiliation.
TBLDepartment	Contains department data such as department code, name, head, and location.
TblCourse	Defines courses offered by departments, with ETS (credit) values.
TBLteacher	Stores teacher information including degree and office code.
TBLgroup	Connects students, teachers, and courses, recording scores and academic status.
Tbl_Log_Del	Tracks deleted records from TBLgroup via trigger for audit purposes.

Relationships:

• Each student belongs to one department.
• Each course is offered by one department.
• TBLgroup forms a many-to-many relationship between students, teachers, and courses.

---

⚙️ Key Features and Functionality

1. Triggers for Data Tracking

• Insert/Update Triggers: Automatically update a ModifiedDate column each time a record in TBLgroup changes.
• Delete Trigger: Logs deletions in Tbl_Log_Del with timestamps for accountability.

2. Stored Procedures

• Averagescore – Computes the average score per student.
• threeets – Returns courses with ETS = 3.
• etstaken – Calculates the total ETS credits taken by each student.

3. User-Defined Function

• udfStudent() – Returns the names of students who have taken more than 4 ETS credits and belong to departments 201–205.

4. Views

• SQLTeachers – Displays names of teachers associated with a specific course (e.g., course code 300).

5. Transactions

• Demonstrates BEGIN TRANSACTION, COMMIT, and ROLLBACK for:
  • Controlled inserts, updates, and deletions on TBLstudent
  • Safe updates to TBLgroup to ensure data integrity

6. Conditional Updates

• Score adjustments:
  • +1 point for scores between 17–19
  • Capped at 20 for top scores

---

🧩 Sample Analytical Outputs

• Students marked as “failed” for scores below 12
• Average score per student (via stored procedure)
• Sum of ETS credits per student
• List of high-performing students (via UDF)

---

🗺️ ER Diagram Included