Lab Assignment $01$ Example : University Database ImplementationBusiness Use Case:A university wants to develop a database system to manage its academic operations. The system should keep track of students, professors, courses, departments, enrollments, and course assignments. The database will be used to store and retrieve information efficiently and ensure data integrity.Tasks:Identify Tables and Attributes:Based on the use case, identify at least five tables required for the university database.Define appropriate attributes for each table.Identify candidate keys and primary keys for each table.Create Tables:Write SQL scripts to create the identified tables with the defined attributes, including constraints for candidate keys, primary keys, and foreign keys.Insert Data:Insert sample data into each table to represent realistic university scenarios.Create Database Diagram:Generate a database diagram that includes all the tables and their relationships.Run SELECT Queries:Execute SELECT queries to retrieve and display the data from each table.Documentation:Include screenshots of the database diagram and the results of your SELECT queries in your submission.Step$-$by$-$Step Instructions:Step $1$: Identify Tables and AttributesTables and Attributes:DepartmentsDepartmentID $($INT$,$ Primary Key$)$DepartmentCode $($VARCHAR$(10),$ Candidate Key$)$DepartmentName $($VARCHAR$(100))$ProfessorsProfessorID $($INT$,$ Primary Key$)$SSN $($CHAR$(11),$ Candidate Key$)$Email $($VARCHAR$(255),$ Candidate Key$)$FirstName $($VARCHAR$(50))$LastName $($VARCHAR$(50))$HireDate $($DATE$)$DepartmentID $($INT$,$ Foreign Key$)$StudentsStudentID $($INT$,$ Primary Key$)$SSN $($CHAR$(11),$ Candidate Key$)$Email $($VARCHAR$(255),$ Candidate Key$)$FirstName $($VARCHAR$(50))$LastName $($VARCHAR$(50))$DateOfBirth $($DATE$)$CoursesCourseID $($INT$,$ Primary Key$)$CourseCode $($VARCHAR$(10),$ Candidate Key$)$CourseName $($VARCHAR$(100))$Credits $($INT$)$DepartmentID $($INT$,$ Foreign Key$)$EnrollmentsEnrollmentID $($INT$,$ Primary Key$)$StudentID $($INT$,$ Foreign Key$)$CourseID $($INT$,$ Foreign Key$)$EnrollmentDate $($DATE$)$Grade $($CHAR$(2))$UNIQUE $($StudentID$,$ CourseID$)($Composite Candidate Key$)$CourseAssignmentsAssignmentID $($INT$,$ Primary Key$)$CourseID $($INT$,$ Foreign Key$)$ProfessorID $($INT$,$ Foreign Key$)$AssignmentDate $($DATE$)$UNIQUE $($CourseID$,$ ProfessorID$)($Composite Candidate Key$)$Step $2$: Create Tables$--$ Creating Departments TableCREATE TABLE Departments $($ DepartmentID INT NOT NULL, $--$ Candidate Key DepartmentCode VARCHAR$(10)$ NOT NULL UNIQUE, $--$ Candidate Key DepartmentName VARCHAR$(100)$ NOT NULL, PRIMARY KEY $($DepartmentID$)--$ Primary Key$)$;$--$ Creating Professors TableCREATE TABLE Professors $($ ProfessorID INT NOT NULL, $--$ Candidate Key SSN CHAR$(11)$ NOT NULL UNIQUE, $--$ Candidate Key Email VARCHAR$(255)$ NOT NULL UNIQUE, $--$ Candidate Key FirstName VARCHAR$(50),$ LastName VARCHAR$(50),$ HireDate DATE, DepartmentID INT, PRIMARY KEY $($ProfessorID$),--$ Primary Key FOREIGN KEY $($DepartmentID$)$ REFERENCES Departments$($DepartmentID$))$;$--$ Creating Students TableCREATE TABLE Students $($ StudentID INT NOT NULL, $--$ Candidate Key SSN CHAR$(11)$ NOT NULL UNIQUE, $--$ Candidate Key Email VARCHAR$(255)$ NOT NULL UNIQUE, $--$ Candidate Key FirstName VARCHAR$(50),$ LastName VARCHAR$(50),$ DateOfBirth DATE, PRIMARY KEY $($StudentID$)--$ Primary Key$)$;$--$ Creating Courses TableCREATE TABLE Courses $($ CourseID INT NOT NULL, $--$ Candidate Key CourseCode VARCHAR$(10)$ NOT NULL UNIQUE, $--$ Candidate Key CourseName VARCHAR$(100)$ NOT NULL, Credits INT, DepartmentID INT, PRIMARY KEY $($CourseID$),--$ Primary Key FOREIGN KEY $($DepartmentID$)$ REFERENCES Departments$($DepartmentID$))$;$--$ Creating Enrollments TableCREATE TABLE Enrollments $($ EnrollmentID INT NOT NULL, $--$ Candidate Key StudentID INT NOT NULL, CourseID INT NOT NULL, EnrollmentDate DATE, Grade CHAR$(2),$ PRIMARY KEY $($EnrollmentID$),--$ Primary Key UNIQUE $($StudentID$,$ CourseID$),--$ Candidate Key $($Composite$)$ FOREIGN KEY $($StudentID$)$ REFERENCES Students$($StudentID$),$ FOREIGN KEY $($CourseID$)$ REFERENCES Courses$($CourseID$))$;$--$ Creating CourseAssignments TableCREATE TABLE CourseAssignments $($ AssignmentID INT NOT NULL, $--$ Candidate Key CourseID INT NOT NULL, ProfessorID INT NOT NULL, AssignmentDate DATE, PRIMARY KEY $($AssignmentID$),--$ Primary Key UNIQUE $($CourseID$,$ ProfessorID$),--$ Candidate Key $($Composite$)$ FOREIGN KEY $($CourseID$)$ REFERENCES Courses$($CourseID$),$ FOREIGN KEY $($ProfessorID$)$ REFERENCES Professors$($ProfessorID$))$;Step $3$: Insert DataSample Data:$--$ Inserting data into DepartmentsINSERT INTO Departments $($DepartmentID$,$ DepartmentCode, DepartmentName$)$VALUES $(1,\text{'}$CS$\text{'},$ 'Computer Science'$),(2,$ 'MATH', 'Mathematics'$),(3,\text{'}$PHYS$\text{'},$ 'Physics'$)$;$--$ Inserting data into ProfessorsINSERT INTO Professors $($ProfessorID$,$ SSN$,$ Email, FirstName, LastName, HireDate, DepartmentID$)$VALUES $(1,\text{'}123-45-6789\text{'},$ 'jdoe@university.edu', 'John', 'Doe', $\text{'}2005-08-15\text{'},1),(2,\text{'}987-65-4321\text{'},$ 'asmith@university.edu', 'Alice', 'Smith', $\text{'}2010-09-01\text{'},2)$;$--$ Inserting data into StudentsINSERT INTO Students $($StudentID$,$ SSN$,$ Email, FirstName, LastName, DateOfBirth$)$VALUES $(1,\text{'}111-22-3333\text{'},$ 'bwilson@student.edu', 'Bob', 'Wilson', $\text{'}1998-05-21\text{'}),(2,\text{'}444-55-6666\text{'},$ 'mjones@student.edu', 'Mary', 'Jones', $\text{'}1999-07-15\text{'})$;$--$ Inserting data into CoursesINSERT INTO Courses $($CourseID$,$ CourseCode, CourseName, Credits, DepartmentID$)$VALUES $(1,\text{'}$CS$101\text{'},$ 'Introduction to Computer Science', $4,1),(2,$ 'MATH$101\text{'},$ 'Calculus I$\text{'},3,2)$;$--$ Inserting data into EnrollmentsINSERT INTO Enrollments $($EnrollmentID$,$ StudentID, CourseID, EnrollmentDate, Grade$)$VALUES $(1,1,1,\text{'}2023-01-15\text{'},\text{'}$A$\text{'}),(2,2,2,\text{'}2023-01-15\text{'},\text{'}$B$\text{'})$;$--$ Inserting data into CourseAssignmentsINSERT INTO CourseAssignments $($AssignmentID$,$ CourseID, ProfessorID, AssignmentDate$)$VALUES $(1,1,1,\text{'}2023-01-01\text{'}),(2,2,2,\text{'}2023-01-01\text{'})$;Step $4$: Create Database DiagramGenerate a database diagram that shows all the tables and their relationships. Ensure that the diagram clearly illustrates primary keys, foreign keys, and the relationships between the tables.Step $5$: Run SELECT QueriesQueries to Verify Data:$--$ Select data from DepartmentsSELECT $*$ FROM Departments;$--$ Select data from ProfessorsSELECT $*$ FROM Professors;$--$ Select data from StudentsSELECT $*$ FROM Students;$--$ Select data from CoursesSELECT $*$ FROM Courses;$--$ Select data from EnrollmentsSELECT $*$ FROM Enrollments;$--$ Select data from CourseAssignmentsSELECT $*$ FROM CourseAssignments;