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_at_individual.net>
>>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
Date: Mon, 22 Aug 2005 02:08:02 +0200
Message-ID: <3msjb3F1859h8U1_at_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 Mon Aug 22 2005 - 02:08:02 CEST