Re: Advice on SQL and records
Date: Tue, 23 Aug 2005 11:43:35 +0200
Message-ID: <3n09e3F18o2b9U1_at_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)
>
With the new design the "records" in _StudentCourse_ are
between 2500 and 4000 (5*500 - 8*500)
> 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
>
> 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 - 11:43:35 CEST