Re: Advice on SQL and records

From: Stefan Rybacki <stefan.rybacki_at_gmx.net>
Date: Tue, 23 Aug 2005 11:43:35 +0200
Message-ID: <3n09e3F18o2b9U1@individual.net>

FRAN wrote:

>... 

>
> I'm learning so much here. Thanks everyone. I hope I don't sound like
> an ignorant moron, but frankly, I'd much prefer to run that risk here,
> than in front of a class, especially when I might spread my own
> ignorance about prior to assessment.
>
> Lets say for the sake of argument, that we have a school with 500
> students (pretty close to the size of the place where I teach
> actually).

Ok

>
> In this hypothetical school, students take a minimum of five courses
> and a maximum of 8 courses. In total, there are 40 different courses at
> the school.

Ok

>
> Each course is listed in the StudentCourses table. Each course name is
> the value of the CourseID field. A foreign key from the Students table
> called "StudentID" uniquely identifies each student in each course.

That is wrong design. Create a table _course_ with all 40 courses (CourseID (PK), Name)

Let the _StudentCourse_ table just have 2 fields (CourseID (FK), StudentID (FK))

>
> All 500 students are listed in the "Students" table. This table has
> "CourseID" as a foreign key.

Don't do that. Just have the 500 students in your _student_ table, no foreign keys in _student_ (this will lead to a denormalized DB schema)

_student_ (StudentID (PK), additional attributes)

>
> The aggregate number of instances of a StudentID (regardless of
> duplicates) appears in all the courses for which there are records is
> 3860.
>
> Is it likely that the number of records in StudentCourses is

With the new design the "records" in _StudentCourse_ are between 2500 and 4000 (5*500 - 8*500)

>
> a) 40
> b) 3860
> c) 40*500 (ie 20000)
> d) Something else worked out by another method
> e) Insufficient data to determine an ballpark answer
>
> ...

 >

Stefan

> Thanks once again.
>
> Fran
>
Received on Tue Aug 23 2005 - 04:43:35 CDT