SQL

Part I: Database Creation and Basic Operations

$1.$ Create a new database named UniversityManagement.

$2.$ Within UniversityManagement, create the following tables:

$$ Students with fields: StudentID, FirstName, LastName, BirthDate, Department.

$$ Professors with fields: ProfessorID, FirstName, LastName, Department, Title.

$$ Courses with fields: CourseID, CourseName, Department, Credits.

$$ Enrollments with fields: EnrollmentID, StudentID, CourseID, Semester, Grade.

$3.$ Populate each table with at least $10$ entries.

$4.$ Write SQL commands to update, delete, and insert data into these tables.

Part II: Advanced Querying

$5.$ Perform complex queries involving multiple tables. Include:

$$ A query to list all students and the courses they are enrolled in$.$

$$ A query to find professors who are not advising any student.

$$ A query to calculate the average grade per course.

$6.$ Use the GROUP BY and ORDER BY clauses to organize your query results.

$7.$ Use the JOIN operation to combine rows from two or more tables.

$1$

Part III: Database Maintenance and Security

$8.$ Demonstrate how you would back up the UniversityManagement database.

$9.$ Write a guide on how to restore the database from the backup.

$10.$ Discuss the importance of database indexing and create an index on one of the fields in the Students

table.

$11.$ Implement user privileges by creating different user profiles with distinct access levels $($read$-$only,

read$-$write, and administrative$).$

Part IV: Reporting and Documentation

$12.$ Generate a report that includes:

$$ A summary of all tasks completed with the corresponding SQL commands used.

$$ Screenshots of the output or results of your SQL commands.

$$ A section discussing any challenges faced during the assignment and how you overcame them.