Objective: Design and implement a University Management System $($UMS$)$ that manages information about students, courses, instructors, departments, and enrollment records. Your goal is to design the underlying database architecture, write queries to manipulate and retrieve data, and ensure data integrity through constraints. This project will give you practical experience with core database concepts and SQL operations. Requirements: Your project should focus on the following areas: $1.$ Database Design: $-$ Identify key entities such as Students, Instructors, Courses, Departments, and Enrollments. $-$ Define relationships between these entities and represent them in an ER diagram. $-$ Determine appropriate attributes for each entity, making decisions about data types, primary keys, and foreign keys. $-$ Implement constraints such as NOT NULL, UNIQUE, and CHECK to ensure data integrity. $-$ Create the SQL schema for your database based on your ER diagram. $2.$ Database Manipulation: Write SQL queries to perform the following operations: $1.$ Insert records for students, instructors, courses, departments, and enrollments. $2.$ Update an instructor's information. $3.$ Delete a course from the database. $4.$ Modify the schema by adding a new column to the student table. $5.$ Retrieve all courses offered by a specific department. $6.$ Find students who are enrolled in a particular course. $7.$ Display all courses, along with the corresponding department and instructor. $8.$ Retrieve all students who are enrolled in more than one course. Ensure your queries correctly manipulate the relationships between the entities and make use of appropriate join operations to retrieve data from multiple tables. $3.$ Data Integrity and Constraints: $-$ Implement foreign key constraints, considering how to handle ON DELETE$/$UPDATE actions with options like CASCADE, SET NULL, and RESTRICT. $-$ Carefully manage NULL values and implement logic to handle them properly when inserting and querying data. $-$ Demonstrate proper use of constraints to enforce business rules at the database level. $4.$ Advanced Queries: Write SQL queries to solve the following analytical questions: $1.$ Determine how many students are enrolled in each course. $2.$ Find the minimum and maximum grades received in each course. $3.$ Calculate the total number of credits each student has taken. $4.$ Compute the average grade for all students in each course. $5.$ Identify courses where the average grade is greater than $75.6.$ List all courses, ordered by the number of students enrolled in them, starting with the course that has the most students. $7.$ Find all departments that offer more than $5$ courses. $8.$ List students along with the total number of courses they are enrolled in$,$ ordered by the highest enrollment count. $9.$ Find all courses where fewer than $10$ students are enrolled. $10.$ Identify the departments where the total number of enrolled students across all courses is greater than $100.$ Your queries should consider how to group, sort, and filter data to answer these questions efficiently. $5.$ Join Operations: Write SQL queries to retrieve data from related tables using join operations to answer the following questions: $1.$ List all students enrolled in each course, along with the course name and instructor's name. $2.$ Find all instructors who teach courses in more than one department. $3.$ Display all courses along with the number of students enrolled in each course. $4.$ Retrieve the names of students and the courses they are enrolled in$,$ along with the department offering the course. $5.$ Find courses that have not been assigned to any instructor yet. Ensure you use appropriate join operations to retrieve the necessary data from multiple related tables. $6.$ Reporting and Documentation: $-$ Document your project with a clear explanation of the database schema, entity relationships, and constraints applied. $-$ Write a detailed description of the SQL queries you have developed, explaining how each one answers the specified questions. $-$ Discuss any challenges faced during the project and how you solved them. Project Submission: $-$ Submit the following via Canvas: $1.$ ER diagram: Representing your database design. $2.$ SQL schema: The complete set of SQL statements used to create your database. $3.$ SQL queries: Queries that answer the advanced questions listed above. $4.$ Documentation: Explanation of your design, queries, and decisions made during the project.