🎓 Student Assignment — PostgreSQL Database Project

Topic:

University Management System (Students, Courses, Professors)

---

Task Description (for student)

Goal

Design and build a relational database in PostgreSQL to manage the data of a university system. Your system should handle information about students, professors, courses, and their relationships.

---

Step 1: Database creation

Create a new database named:

university_system

---

Step 2: Database design

You must decide on the database structure yourself.

Your design must include:

• At least 4–5 tables that represent main entities of a university system. Examples of possible entities:

  • Students
  • Professors
  • Courses
  • Departments
  • Enrollments (or Registrations)
  • Classrooms or Semesters

You may include additional tables if they make logical sense.

For each table:

• Choose appropriate column names and data types
• Define primary keys
• Use foreign keys to link related tables
• Add constraints where needed (NOT NULL, UNIQUE, CHECK, etc.)

Tip: Think about how you would track which student is taking which course, who teaches each course, and in which department professors belong.

---

Step 3: Sample data

Insert at least:

• 5 professors
• 6 courses
• 10 students
• Several relationships (e.g., students enrolled in different courses)

The data must make logical sense. For example, each course should have a professor; each student can enroll in multiple courses.

---

Step 4: Write at least 6 SQL queries

Write SQL queries that demonstrate your understanding of:

1. JOINs (inner, left, etc.)
2. Filtering (WHERE)
3. Aggregation (COUNT, AVG, etc.)
4. Grouping (GROUP BY)
5. Sorting (ORDER BY)
6. Subqueries or views

Your queries must produce meaningful information, such as:

• List of students enrolled in a specific course
• Courses taught by a certain professor
• Number of students per course
• Average grade per course or per student (if you add grades)
• Departments with the most professors
• Students not enrolled in any course

---

Step 5 (Optional Bonus)

Add one of the following:

• A VIEW showing full student-course-professor relationships
• A TRIGGER to automatically update a student’s total enrolled credits
• A STORED FUNCTION returning a professor’s teaching load (number of courses taught)

---

Step 6: Deliverables

Submit:

1. .sql file that includes:

   • CREATE DATABASE
   • All table creation statements
   • Insert data statements
   • All queries

2. A short README (or comment block) explaining:

   • What each table represents
   • The relationships between them
   • What each query shows

---