Re: Advice on SQL and records

fran_beta@xxxxxxxxxxx wrote: 
Stefan Rybacki wrote:

fran_beta@xxxxxxxxxxx wrote:

I'm teaching in computing. A question appeared in a trial paper
recently and I'm trying to establish whether the suggested answer is
correct.

The question was:

SELECT   Students.Surname, Students.Name
FROM     Courses, Classes, StudentClasses, Students
WHERE    Courses.CourseID="10MA"

AND
   Courses.CourseID=Classes.CourseID
AND
   Classes.ClassID=StudentClasses.ClassID  AND

StudentClasses.StudentID=STudents.StudentID

ORDER BY Students.Surname, Students.Name


Which of the tables is likely to contain the most records?

The suggested answer from Courses; Classes; StudentClassses and
Students was:

StudentClasses


I guess that is right in the average case.


How would this be determined? Why wouldn't Students contain the most
records?


Imagine 10000 students. So your students table has 10000 entries. Now say each student
take part in one class, so studentclasses has 10000 entries. But because usually a student
takes part in more than one class say 3, you have 30000 entries in studentclasses.

Stefan


Thanks

Fran 




Firstly, thanks for responding so promptly. I really appreciate that.


Welcome.

I'm very much hoping to be corrected here, if I'm under a
misapprehension, but isnt the ideal in a normalised database to have
all records always only in one relevant table/object?


Yes. The target of normilization is do avoid redundances.


Thus wouldn't the StudentClasses table merely "call" the records from
students through a relationship? 

Yep, "calling" by using a foreign key to the primary key in students

> Wouldn't StudentID be simply a field
in each or the records in that table, rather than a new record? 

Just a field (as I said, a foreing key)

So where is the problem you have? Can't follow you.

Stefan


Thanks once again for your trouble.

Fran





.