Problem 3:
SELECT H.name
FROM Highschooler H
WHERE grade = 10
ORDER BY H.name ;


Problem 4:
SELECT H.grade, COUNT(*)
FROM Highschooler H
WHERE H.name = 'Alison'
GROUP BY H.grade
ORDER BY H.grade ;


Problem 5:
SELECT H.name, COUNT(*)
FROM Highschooler H, Highschooler Hother, Friend F
WHERE H.ID = F.ID1 and Hother.ID = F.ID2 and H.grade = Hother.grade
GROUP BY H.ID
ORDER BY H.name ;


Problem 6:
SELECT H.name
FROM Highschooler H, Highschooler Hfriend, Friend F
WHERE H.ID = F.ID1 AND Hfriend.ID = F.ID2
   AND H.grade <> Hfriend.grade
GROUP BY H.ID
HAVING COUNT(*) >= 2
ORDER BY H.name ;
  or
SELECT DISTINCT(H.name)
FROM Highschooler H, Highschooler Hx, Highschooler Hy, Friend Fx, Friend Fy
WHERE H.ID = Fx.ID1 AND Hx.ID = Fx.ID2 AND H.ID = Fy.ID1 AND Hy.ID = Fy.ID2
   AND H.grade <> Hx.grade AND H.grade <> Hy.grade
   AND Hx.ID < Hy.ID
ORDER BY H.name ;


Problem 7:
   Francis English
      Condor Smith
         LaVere Andrews
         Patricia Andrews
         Tom Smothers
      Alexander Bell
         Maxene Andrews
         Dick Smothers

It is also possible to give the groups names. (Kids like group names.)


Problem 8:
CREATE TABLE FieldTrip(
   Teacher TEXT,
   Chaperon TEXT, 
   Student TEXT,
   ContactNumber INT) ;

and groups, if you wish...


Problem 9:
The UML contains classes of objects, not the objects themselves


   ----------- 1.1        1.*  ------------ 1.1        2.4 -----------
  |  Teacher  +---------------+  Chaperon  +--------------+  Student  +
   -----------                 ------------                -----------

The Teacher, Chaperon, and Student box should all contains name attribute.
The Student box should also contain contact number.

With Group there 1.1 cardinality in both ways between Chaperon and Group.


Problem 10:

Functional dependencies, means if you know one, you know the other
   Student  --> Teacher, Chaperon, ContactNumber
   Chaperon --> Teacher

Adding Groups makes it a bit more complicated
   Student  --> Teacher, Group, Chaperon, ContactNumber
   Chaperon --> Teacher, Group
   Group    --> Teacher, Chaperon

We are assuming that two students may have the same contact number.


Problem 11:
The main thing to avoid is the Student --> Chaperon --> Teacher
transitive relation.

The classic breakdown would give
   Table TC with attributes:   Teacher, Chaperon
   Table CS with attributes:   Chaperon, Student, ContactNumber
This breakdown, seems more natural
   Table TS with attributes:   Teacher, Student, ContactNumber
   Table CS with attributes:   Chaperon, Student

With groups, you need to be more careful because of the mutual
functional dependency of Group and Chaperon, Group --> Chaperon
and Chaperon --> Group.

ID numbers are a good idea, but a table with columns such as
   Teacher    TeacherID  Student ....
can be a little problematic with our rule that the name uniquely
identifies the person.