1.
(a) select name, email
    from Students
    where sno IN (select sno
                  from Records R natural join Courses
                  where term = '2017-1'
                    and (subject = 'Physics'
                     or  subject = 'Chemistry'));
(b) select subject, level, description
    from Courses
    where cid NOT IN (select cid
                      from Records);
(c) (assuming that there are multiple Math 12 courses
     in the first term of year 2017)
    select distinct S2.sno, S2.name
    from Students S1, Records R1, Courses C,
         Students S2, Records R2
    where S1.name = 'John Smith'
      and S1.sno = R1.sno
      and R1.term = '2017-1'
      and R1.cid = C.cid
      and C.subject = 'Math'
      and C.level = 12
      and R1.cid = R2.cid
      and R1.term = R2.term
      and R2.sno = S2.sno;
   (assuming there is only one math 12 course in the first term
    of year 2017, and John Smith is in it)
   select sno, name
   from Students natural join Records
        natural join Courses
   where term = '2017-1'
     and subject = 'Math'
     and level = 12; 

(d) select S.sno, name, count(cid) as numOfPassedCourses
    from students S join Records R on S.sno = R.sno
    where mark >= 50
    group by sno, name
    order by numOfPassedCourses desc;

(e) select name, email
    from Students natural join Records
         natural join Courses
    where mark >= 50
      and level = 12
    group by name, email
    having sum(credits) >= 8;

2.
RA: (I'd prefer to use the RA expression tree,
     but this is a text file, so ...)
    J means natural join, S means selection,
    P means projection and - means set difference

    P_{sno, name} (Students J Records J (S_(level = 12) Courses))
    intersect
    (P_{sno, name} Students
     - (set difference)
     P_{sno, name} (Students J Records J (S_(subject = 'English'
                    and level = 12) Courses))
    }
    
Datalog:
   
  Result(s, n) ::= Students(s, n, _)
               and Records(s, c1, _, _, _)
               and Courses(c1, _, 12, _, _)
               and courses(c2, 'English', 12, _, _)
               and NOT Records(s, c2, _, _, _)

3. run the following SQL first:
   select mark
   from Records
   where sno = '231196'
     and cid = 'ENGL-12'
     and term = '2016-1';

if the previous query returns a result, then run the following
SQL query:
   update records
   set mark = 87
   where sno = '231196'
     and cid = 'ENGL-12'
     and term = '2016-1';
else run the following SQL query:
   insert into Records
   values ('231196', 'ENGL-12', '2016-1', 'E10257', 87);

4. 
(a) (code, num)
(b) Instructors are specialization of Employees.
    That is, an instructor must be an employee,
    some employees are instructors, but it may not
    be true that all employees are instructors.
(c) Relations include:

    Employees({sno}, name)
    Instructors({eno}, research)
    Departments({code}, name)
    Courses({code, num}, title)
    Teach({eno, code, num})

    where primary keys are indicated inside the curly brackets,
    and there are the following foreign key constraints:
    eno of Instructor references Employees
    code of Courses references Departments
    eno of Teach references Instructors
    (code, num) of Teach references Courses.