Re: Advice on SQL and records

Stefan Rybacki wrote:
> 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
>

I guess the problem is "what exactly are the records in 'StudentClasses'"?

It seems implicit from what you say that StudentClasses includes all ClassID records AND all StudentID records, whereas I assume that only ClassID records would be in there.

Thus even if there are 10000 students if the college is running for example, 40 different classes, there would only be 40 records in StudentClasses, even if each of these records had a minimum of 30 and a maximum of 100 student names in it.

Is it the case that records contain records?

Thanks for your continuing interest.

Fran Received on Mon Aug 22 2005 - 03:33:55 CEST