Re: Advice on SQL and records

From: Stefan Rybacki <stefan.rybacki_at_gmx.net>
Date: Mon, 22 Aug 2005 02:08:02 +0200
Message-ID: <3msjb3F1859h8U1@individual.net>

fran_beta_at_hotmail.com wrote:

> Stefan Rybacki wrote:
> 

>>fran_beta_at_hotmail.com 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
> 
> 
> 
> 
> 

Received on Sun Aug 21 2005 - 19:08:02 CDT