1. Consider the following database schema describing students enrolled in sections of classes at a university. student (sid*, sname, sex, age, year, gpa) [Year is a number between 1 and 5). dept(dname*, numphds) prof(pname*, dname) course(cno*, cname, dname*) (Course numbers are unique within departments.] major(dname*, sid*) section(dname*, cno*, sectno*, pname) (Associates an instructor to a section.] enroll(sid*, grade, dname*, cno*, sectno*) (Associates students to sections. (d) The university wants to make some of this information available to a student's resident advisor. The advisor should be able to see the classes a student is enrolled in, but not the grade. Create a view that provides this information. (e) Suppose that the database system has access to an identifier (think of something like Columbia's UNI) that comes from the same domain as the sid field for students. Suppose this identifier is available in the system variable $LOGIN which can be used within SQL statements. Create a view that defines the section and course information for the currently logged-in student. (1) Imagine we have three user roles: Administrators (who manage enrollments), students, and resident advisors. Describe a suitable set of table permissions (specified via grant statements) for each role. (Include the views from the previous questions.) 1. Consider the following database schema describing students enrolled in sections of classes at a university. student (sid*, sname, sex, age, year, gpa) [Year is a number between 1 and 5). dept(dname*, numphds) prof(pname*, dname) course(cno*, cname, dname*) (Course numbers are unique within departments.] major(dname*, sid*) section(dname*, cno*, sectno*, pname) (Associates an instructor to a section.] enroll(sid*, grade, dname*, cno*, sectno*) (Associates students to sections. (d) The university wants to make some of this information available to a student's resident advisor. The advisor should be able to see the classes a student is enrolled in, but not the grade. Create a view that provides this information. (e) Suppose that the database system has access to an identifier (think of something like Columbia's UNI) that comes from the same domain as the sid field for students. Suppose this identifier is available in the system variable $LOGIN which can be used within SQL statements. Create a view that defines the section and course information for the currently logged-in student. (1) Imagine we have three user roles: Administrators (who manage enrollments), students, and resident advisors. Describe a suitable set of table permissions (specified via grant statements) for each role. (Include the views from the previous questions.)
