views:

102

answers:

3
+3  Q: 

Relational Algebra

I was wondering if I could get some feedback on the relational algebra I have done and any advice on improving it also if you see anything wrong with my SQL could you possibly point it out.

SELECT CourseName
FROM Course
WHERE CourseNo = 6;

∏CourseName(σCourseNo=6(Course))

SELECT CU.UnitNo
FROM C.Course, CU CourseUnit
WHERE C.CourseName = CU.CourseNo
AND C.CourseName = 'Research';

∏UnitNo(CourseUnit(σCourseName=’Research’(Course)))

SELECT U.UnitName
FROM S Student, SU StudentUnit, U Unit
WHERE S.StudentNo = SU.StudentNo
AND SU.UnitNo = U.UnitNo
AND S.StudentName = 'John Perry';

∏UnitName(Unit(StudentUnit(σStudentName=’John Perry’(Student))))

SELECT count(S.StudentNo) ResearchStudents
FROM C Course, S Student
WHERE C.CourseNo = S.CourseNo
AND C.CourseName = 'Research';

∏ResearchStudents((C)count(StudentNo)(Student (σCourseName=’Research’(Course))))

+1  A: 

there is an issue in the from clause, I removed a dot between C.Course

SELECT CU.UnitNo
FROM C Course, CU CourseUnit
WHERE C.CourseName = CU.CourseNo
AND C.CourseName = 'Research';
Pentium10
+1  A: 

On top of Pentium10's comment, you may consider using JOIN clauses as opposed to specifying the joins in the WHERE clause. In my opinion, it can lead to queries that are easier to follow. So

SELECT CU.UnitNo
FROM C Course, CU CourseUnit
WHERE C.CourseName = CU.CourseNo
AND C.CourseName = 'Research';

becomes

SELECT CU.UnitNo
FROM C Course 
INNER JOIN CU CourseUnit
  ON C.CourseName = CU.CourseNo
WHERE C.CourseName = 'Research';

(side note: the tables have been given aliases but the aliases aren't used - If my memory serves me correctly, SQL Server will complain about this and not execute the query. Not sure about other RDBMS)

Russ Cam
A: 

If your syntax is going to be used, I guess the special characters must be exchanged. (remember the APL keyboards?)

The distance between Course and C is disturbing, in your last example.

Also your Join in the 3rd example is not complete.

I think Query By Example or Linq are more interesting alternatives.

Kri-ban
Uhhhhh... what?
Charlie Somerville