Как перевести SQL в реляционную алгебру на таблицах реляционных баз данных

Есть 3 таблицы: Students, Courses а также Grades,

sid, cid а также sid-cid(Супер ключ) являются первичными ключами каждой таблицы.

Students:

sid sname address
-----------------
S1  Adam  Abawama
S2  Mery  Ignora
S3  Aisha Icterra
S4  Sello Icterra
S5  Mery  Kaysers

Courses:

cid cname    dept
------------------
C1  Db       Ceng
C2  Prog     Ceng
C3  Calculus Math
C4  Stat     EE
C5  Alg      Ceng

Grades:

sid cid grade
--------------
S1  C1  50
S1  C2  85
S1  C3  60
S1  C4  90
S1  C5  50
S2  C1  30
S2  C2  40
S3  C2  85
S4  C2  80
S4  C4  75
S4  C5  60

Вопросы

  1. Перечислите имена студентов, которые ceng и EE курс

  2. Найти sid студент, который получает высшую оценку от курса database

Мой SQl Ответ за Q1

SELECT s.sname
FROM Students s
JOIN Grades g ON s.sid = g.sid
JOIN Courses c ON g.sid = c.sid
AND c.dept IN ("Ceng","EE")

Мой ответ SQL для Q2

SELECT sid
FROM Grades
WHERE grade =
    (SELECT max(grade)
     FROM Grades
     GROUP BY cid HAVING Grades.cid = "C1")

Ответ 1 - возвращает неверный результат, как это исправить? Как я могу написать реляционную алгебру для этих команд?

Источник

3 ответа

Решение

Мне нравится использовать TDQD - Test-Driven Query Design. Вы строите запрос поэтапно, проверяя каждый этап. Это довольно простой запрос - ему нужно всего два шага (хотя, возможно, я сжал два шага в шаге 2).

Шаг 1

Вам нужно сделать самостоятельное соединение в той или иной форме. Этот запрос перечисляет студентов (по sid) с оценкой по курсу EE:

SELECT g.sid
  FROM Grades  AS g
  JOIN Courses AS c ON g.cid = c.cid AND c.dept = "EE"

Шаг 2

Вы можете использовать запрос на шаге 1 как один подзапрос и аналогичный подзапрос для "Ceng", чтобы сгенерировать две "таблицы" со списком студентов, которые делают EE и Ceng; Вы присоединяетесь к этим таблицам, чтобы найти учеников, которые делают EE и Ceng, и присоединитесь к ним с таблицей учеников, чтобы перечислить их имена:

SELECT s.name
  FROM Students AS s
  JOIN (SELECT DISTINCT g.sid
          FROM Grades  AS g
          JOIN Courses AS c ON g.cid = c.cid AND c.dept = "EE"
       ) AS ee
    ON s.sid = ee.sid
  JOIN (SELECT DISTINCT g.sid
          FROM Grades  AS g
          JOIN Courses AS c ON g.cid = c.cid AND c.dept = "Ceng"
       ) AS ceng
    ON s.sid = ceng.sid

Квалификаторы DISTINCT необходимы в случае, если какой-то студент проходит 2 курса EE и 1 курс Ceng; это предотвращает их появление в выходных данных дважды. Конечно, вы также можете поместить DISTINCT в основной список выбора запросов.

Источник

Чтобы получить представление, вы можете прочитать "Концепции системы баз данных" или этот бесплатный слайд, предоставленный автором.

Использовать этот:

SELECT s.sname
FROM Students s
JOIN Grades g ON s.sid = g.sid
JOIN Courses c ON g.cid = c.cid
WHERE c.dept = 'Ceng'
INTERSECT 
SELECT s.sid
FROM Students s
JOIN Grades g ON s.sid = g.sid
JOIN Courses c ON g.cid = c.cid
WHERE c.dept = 'EE';
Источник

Другой подход заключается в использовании двух взаимосвязанных подзапросов, например:

select S.SNAME
from STUDENTS S
where exists (select G1.SID
              from GRADES G1 inner join COURSES C1 on G1.CID=C1.CID
              where G1.SID = S.SID
                and C1.DEPT = 'Ceng')
  and exists (select G2.SID
              from GRADES G2 inner join COURSES C2 on G2.CID=C2.CID
              where G2.SID = S.SID
                and C2.DEPT = 'EE')

Смотрите: http://sqlfiddle.com/

Источник
Другие вопросы по тегам