Re: Advice on SQL and records

From: Stefan Rybacki <stefan.rybacki_at_gmx.net>
Date: Tue, 23 Aug 2005 12:35:35 +0200
Message-ID: <3n0cfoF188iquU1_at_individual.net>


FRAN wrote:

> Stefan Rybacki wrote:
> 

>>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)
>>
> 
> 
> OK ... Is that because a relationship has already been created by the
> FK in the _StudentCourse_ table relflecting the PK in _Student_ below?
> 

yep (FK1 -> PK(Course) and FK2->PK(Student)). The additional table _StudentCourse_ prevents you to have redundances in your schema and normalizes the 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)
>>
> 
> 
> Thanks
> 
> For the record, would the schema as proposed here be a good example of
> a many-to-many relationship? (on the basis that many CourseIDs = one
> StudentID AND many StudentIDs = one CourseId)
> 

Yes this is a m:n relation, and a good example indeed. BTW: in _StudentCourse_ the PK should be on (CourseID, StudentID) to prevent duplicates.

Stefan

> Fran
> 
> 
> 

>>>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 - 12:35:35 CEST

Original text of this message