In this assignment, you are going to consider an example that might come straight from the University.You are going to consider the following tables, each with the following fields:

Students

• StudentID (PK)
• FirstName
• LastName
• AdvisorID
• Major
• Center
• Email
• Telephone
• CreditsEarned
• ProgramTerm

Classes

• CourseID (PK)
• CourseDesignation
• FacultyID
• MeetingType
• MeetingLocation
• Time1
• Day1
• Time2
• Day2
• Time3
• Day3
• Time4
• Day4
• Time5
• Day5

Faculty

• FacultyID (PK)
• FirstName
• LastName
• Department
• Salary

Enrollments

• StudentID (PK)
• CourseID (PK)

A couple of notes:

• If a course is Online, it will have no meeting location
• If a course is In-Person, it will have a location, and at least one meeting time/day
• A course cannot have a 2^nd meeting time/day without the first, etc.
• A course cannot have a meeting time without a day and vice versa

You have four users who are going to motivate the tasks:

• Ella Gantt, the Registrar of Ball So Hard University
• Olivia Enonapraya, the Department Chair of Geography at Ball So Hard University
• Nicholas Elandime, a student at Ball So Hard University
• Susan Yoo, a faculty member in the Geography Department at Ball So Hard University

Given the information provided, provide the SQL to do the following tasks:

1. USERSMake the following four users with the following passwords (following the dash)

• ellaGantt - parrot756
• livEnonapraya - bonJovi!
• nickElandime - 5and10cents
• sueYoo - joeBornstein4Life

2. VIEW/ASSIGNMENTA student should be able to see their course schedule (but only theirs). In that course schedule, (s)he should see the course name, the teacher, the location, and any days or times that the class meets. Nicholas has ID number 0000064. Make a view that allows Nicholas to do that and assign Nicholas the ability to observe that view.

3. ROLE/VIEW/ASSIGNMENTYou realize that Ball So Hard University has several students.As such you realize you should create a role called student that will assign to every student that ability that Nicholas has. For the purposes of this exercise, treat the ID as "0000000" (assuming that this will allow everyone to fill in the parameter properly with their own ID number). Remove the prior privilege from Nicholas and then give him the role of student.

4. VIEW/ROLE ADJUSTMENTA student should have the ability to update their own records due to privacy laws. Make a view that shows the student their ID, names, and any contact information.Then assign update privileges on this table to the student role.

5. VIEW/ROLE/ASSIGNMENTAs I have said on numerous occasions, faculty members do not necessarily want to see student id numbers (as they are meaningless in the vast majority of cases). Make a class roster view that will provide the students in a specific class that a faculty member teaches. In this case, assume the courseID is "00000" and the faculty member is "0000000".That class roster should include simply a list of student names in one field. Allow both Susan and Olivia access to observe, update, delete, or add to that, but do so in a conscionable manner going forward. You don't know how many faculty members there are, but you suspect it is quite common.

6. ASSIGNMENTThe registrar of Ball So Hard University is the closest thing to omniscient in academic circles. Ella should have complete access to all tables in this database. Provide her that access. Allow her to grant access to the delete feature to department chairs which she trusts.

7. ROLE/VIEW/ASSIGNMENTAs department chair, Olivia is responsible for scheduling classes. She would like to see what rooms are scheduled when. Make a view that spills out the schedule. Note that the schedule is provided in a less than efficient manner. Since Olivia is clearly not the only department chair, this view should be accessible to all department chairs. Department chairs should be able to add, update, and view the course schedule.Assign Olivia that role.

8. VIEW/ROLE ADJUSTMENTHuman Resources got wind that the registrar was able to see the salary of a faculty member. That's a problem. Modify Ella's permissions appropriately.

9. ROLE ADJUSTMENTNicholas has just been hired by the Geography department to TA GEO 101. He needs to be able to see the class roster for that class. Its course id is 45678. Give him the ability to do that, but do not allow him to do anything but see it.

10. You have created a fair amount of views in this assignment.Think about the challenges that are associated with adding, updating, and deleting from views. What issues are present with the views created?