create table classroom (building varchar(15), room_number varchar(7), capacity numeric(4,0), primary key (building, room_number));

create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2) check (budget > 0), primary key (dept_name));

create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0) check (credits > 0), primary key (course_id), foreign key (dept_name) references department on delete set null);

create table instructor (ID varchar(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2) check (salary > 29000), primary key (ID), foreign key (dept_name) references department on delete set null);

create table section (course_id varchar(8), sec_id varchar(8), semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), year numeric(4,0) check (year > 1701 and year < 2100), building varchar(15), room_number varchar(7), time_slot_id varchar(4), primary key (course_id, sec_id, semester, year), foreign key (course_id) references course on delete cascade, foreign key (building, room_number) references classroom on delete set null);

create table teaches (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), 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 varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0) check (tot_cred >= 0), primary key (ID), foreign key (dept_name) references department on delete set null);

create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), 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 student on delete cascade);

-- 1. Modify the classroom schema so that the value -- of capacity must be greater than 10. Insert your modified -- version of the classroom schema below.

-- 2. Modify the takes schema so that the legal grade values can be -- only A, "A-", "B+", B, "B-", "C+", C, "C-", "D", or F. -- Insert your modified version of the takes schema below.

-- 3. Write an SQL 'create table' statement to create a grade_audit -- table with attributes ID, name, dept_name, course_id, grade, and -- time_of. -- This table will be used to record information about a student who -- receives a poor grade in a section of of course, so ID and name -- will refer to a student. Use the appropriate domains for each -- attribute, based on why you see in the rest of the courses schema. -- For the 'time_of' attribute use domain 'varchar(25)'. You do not -- need to include check constraints, but should define a primary key, -- using your judgement.

-- 4. Create a trigger so that if there is an insert into the takes -- table with a grade of F, then the students ID, name, dept_name -- is inserted into the grade_audit table, along with the course_id -- and grade for the course, and the current time. -- Hint: within the insert statement in the body of the trigger, you -- will need one or more joins.

-- 5. Create another trigger, this time for updates to the takes -- table. The trigger should remove the students record for a course -- in the grade_audit table if the updated grade is C or better.