Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Advice on SQL and records

Re: Advice on SQL and records

From: FRAN <fran_beta_at_hotmail.com>
Date: 23 Aug 2005 03:23:18 -0700
Message-ID: <1124792598.312223.174910@g43g2000cwa.googlegroups.com>

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?

> _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)

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 - 05:23:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US