1. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

2. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.

3. Grades are mapped to a grade point as follows: A:10, B:8, C:6, D:4 and F:0. Create a table to store these mappings and write a query to find the CPI of each student, using this table. Make sure students who have not got a non-null grade in any course are displayed with a CPI of null.

4. Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections; I suggest you use a with clause or a view to simplify this query.

5. Create a view faculty showing only the ID, name, and department of instructors.

6. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.

7. Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.

8. Grant permission to one of your friends to view all data in your student relation.

9. Now grant permission to all users to see all data in your faculty view. Conversely, find a friend who has granted you permission on their faculty view, and execute a select query on that view

These are the relevant tables:

Transcribed Image Text:

create table classroom (building room_number capacity primary key (building, room_number) ); create table department (dept_name building budget primary key (dept_name) ); create table course (course_id title create table instructor (ID name dept_name credits primary key (course_id), foreign key (dept_name) references department on delete set null varchar(15), varchar(7), numeric (4,0), ); create table section. (course_id sec_id semester varchar(20), varchar(15), numeric (12,2) check (budget > 0), varchar(8), varchar(50), varchar(20), numeric (2,0) check (credits > 0), year building room_number time_slot_id dept_name salary primary key (ID), foreign key (dept_name) references department on delete set null varchar(5), varchar(20) not null, varchar(20), numeric (8,2) check (salary > 29000), varchar(8), varchar(8), varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), numeric (4,0) check (year > 1701 and year < 2100), varchar(15), varchar(7), varchar(4), sec_id, semester, year), primary key (course_id, foreign key (course_id) references course on delete cascade, foreign key (building, room_number) references classroom on delete set null create table teaches (ID course_id sec_id semester year numeric (4,0), primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section on delete cascade, foreign key (ID) references instructor on delete cascade ); create table student (ID name dept_name tot cred ); varchar(5), varchar(8), varchar(8), varchar(6), primary key (ID), foreign key (dept_name) references department on delete set null create table takes. (ID course_id sec_id semester varchar(5), varchar(20) not null, varchar(20), numeric (3,0) check (tot_cred >= 0), ); create table advisor (s_ID i_ID varchar(5), varchar(8), varchar(8), varchar(6), year grade primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section numeric (4,0), varchar(2), on delete cascade, foreign key (ID) references student on delete cascade varchar(5), varchar(5), primary key (s_ID), foreign key (i_ID) references instructor (ID) on delete set null, foreign key (s_ID) references student (ID) on delete cascade create table classroom (building room_number capacity primary key (building, room_number) ); create table department (dept_name building budget primary key (dept_name) ); create table course (course_id title create table instructor (ID name dept_name credits primary key (course_id), foreign key (dept_name) references department on delete set null varchar(15), varchar(7), numeric (4,0), ); create table section. (course_id sec_id semester varchar(20), varchar(15), numeric (12,2) check (budget > 0), varchar(8), varchar(50), varchar(20), numeric (2,0) check (credits > 0), year building room_number time_slot_id dept_name salary primary key (ID), foreign key (dept_name) references department on delete set null varchar(5), varchar(20) not null, varchar(20), numeric (8,2) check (salary > 29000), varchar(8), varchar(8), varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), numeric (4,0) check (year > 1701 and year < 2100), varchar(15), varchar(7), varchar(4), sec_id, semester, year), primary key (course_id, foreign key (course_id) references course on delete cascade, foreign key (building, room_number) references classroom on delete set null create table teaches (ID course_id sec_id semester year numeric (4,0), primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section on delete cascade, foreign key (ID) references instructor on delete cascade ); create table student (ID name dept_name tot cred ); varchar(5), varchar(8), varchar(8), varchar(6), primary key (ID), foreign key (dept_name) references department on delete set null create table takes. (ID course_id sec_id semester varchar(5), varchar(20) not null, varchar(20), numeric (3,0) check (tot_cred >= 0), ); create table advisor (s_ID i_ID varchar(5), varchar(8), varchar(8), varchar(6), year grade primary key (ID, course_id, sec_id, semester, year), foreign key (course_id, sec_id, semester, year) references section numeric (4,0), varchar(2), on delete cascade, foreign key (ID) references student on delete cascade varchar(5), varchar(5), primary key (s_ID), foreign key (i_ID) references instructor (ID) on delete set null, foreign key (s_ID) references student (ID) on delete cascade

Answer rating: 100% (QA)

QUERIES 1 create view Failed Students as select ID courseid secid semester year grade from takes whe... View the full answer