Re: Advice on SQL and records

<fran_beta_at_hotmail.com> wrote in message news:1124611376.749896.208510_at_o13g2000cwo.googlegroups.com...
> 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
>
> How would this be determined? Why wouldn't Students contain the most
> records?
>
> Thanks
>
> Fran
>

Fran,

I goofed in one of my earlier responses. I said that the number of rows (cardinality) in a table would be something the programmer wouldn't need to know at query time, given a good optimizer. That's true enough, but it's wildly misleading.

There's a stage in database design called "Physical Database Design". Most of the time, physical database design requires the designer to estimate and reserve enough disk space to hold the volume of data that is to be expected. In general, reserving the right amount of space in advance gives you a significant performance boost.

I normally wouldn't go to the trouble of correcting myself like this over a minor point. But I seriously want to help you get ready for your courses. In that connection, I'm starting a new discussion (thread) where I outline some web pages that I think are useful in learning about database design. Received on Thu Aug 25 2005 - 14:21:45 CEST