Re: "Hard" Key Determination Method is Easy. DNF Paper is Done..

In article <694b8c03-9c5e-4dc1-a4f5-c2b14fece057_at_googlegroups.com>,  Derek Asirvadem <derek.asirvadem_at_gmail.com> wrote:

> > > - No redundancies;
> >
> > In StudentExamination, the fact that, say, Room 101 will host the
> > Networks exam on 3/10, 1pm, is repeated for each student taking that
> > exam. This is a form of redundancy.
>
> 1. Are you sure you know what Redundancy means, whose "definition" are you
> using ?
> (If you are using Date' or Darwen', I know that they are wrong, because they
> don't know what a Relational Database is, and they think migrated keys are
> "redundancies". Other pig poop eaters may well think that same.)

No, foreign keys have nothing to do with it. Someone (Tegiri, I think?) has said it already in some other thread: the accepted definition of redundancy is Shannon's. The relationship between that notion and relational databases has been investigated thoroughly.

> 1.a That there are no "forms" of redundancy. Either you have it, or you
> don't ?

True. Then, read: "This is redundancy".

> 2. Are you saying that there is one row in StudentExamination for (your
> quoted values):
> Student Course DateTime Room
> xxxxxx Networks 3/10, 1pm 101 (Student not given)
>
> Or many rows, one per Student, with those three values, repeated x no of
> students ?

The latter.

> 3. Are you aware, that there are two (not one) foreign keys in
> StudentExamination ?
> 3.a PK of CourseExamination ( DateTime, Room, Course )
> 3.b PK of StudentEnrolment ( Student, Course )

Yes.

> 4. Are you aware that [3.a] and [3.b] are migrated foreign keys ?

Yes.

> 5. Ok, now that you have gone through the questions, on what basis, and what
> columns exactly [ I understand ( Course, DateTime, Room ) ], is it Redundant
> ?

Yes, it's those three columns that generate redundancy, in the sense explained below.

> 6. And why exactly is ( Course, DateTime, Room ) included in the "forms of
> redundancy" list, and ( Student ) excluded ?

Note that if you show me *only* this:

StudentExamination
Student Course DateTime Room

given the requirements, I can infer the only possible value for ?. This is not possible for Student.

> 6.a Note that Student is "repeated" (by your wording) for every Course they
> take.

You can't make the same kind of inference in that case.

> 7. And what exactly, would you do, to fix this "form of redundancy" that you
> claim ?

I wouldn't fix it. Your model is good enough. My point was just that it is not true that it has "no redundancy".

Anyway, I'll show an alternative design, which I hope you will accept as relational, normalized, etc... Note that I am not claiming that it is better than (or equivalent to) yours in any sense nor that it is the only possible alternative. Sorry for using just textual form:

Session(DateTime, Course)
Key: {DateTime, Course}
FK: {DateTime} refers to Time
FK: {Course} refers to Course

ExamLocation(DateTime, Room, Course)
Key: {DateTime, Room}
FK: {DateTime, Course} refers to Session FK: {Room} refers to Room

ExamAppointment(Student, Course, DateTime) Keys: {Course, Student}, {Student, DateTime} FK: {Course, Student} refers to StudentEnrolment FK: {DateTime, Course} refers to Session

StudentExamination(Student, DateTime, Room) Key: {Student, DateTime}
FK: {DateTime, Room} refers to ExamLocation FK: {Student, DateTime} refers to ExamAppointment

ExaminationChapter(Student, Course, Chapter) Key: {Student, Course, Chapter}
FK: {Student, Course} refers to StudentEnrolment

This is a sample instance (I omit instances of Course, Student, etc... for simplicity):

Session
DateTime Course

10/6     Networks
 7/7     Networks
10/6     Security
 1/7     Security
 

10/6     101  Networks
10/6     102  Networks
 7/7     101  Networks

ExaminationChapter
Student Course Chapter

Two remarks only:

1. I have deliberately associated ExaminationChapter to StudentEnrolment to make the choice of chapters independent of the exams (imagining that students are told which chapters they have to study during the course). This is totally arbitrary of course: you could make {Student,Course} a foreign key referring to ExamAppointment, for example. Or do something else.
2. StudentExamination is needed because in general an exam may take place in more than one room, and we want to know where each student must go on the day of the exam.

> > > no Update Anomalies
> >
> > Yes, that may well be the case, with the proper inter-relational
> > constraints in place.
>
> I am saying that that is the case with the constraints that are in the model.

I was not denying that. Just I hadn't checked carefully enough as to be sure. Indeed, that seems the case.  

> > For example, the redundancy mentioned above does
> > not cause update anomalies by virtue of a referential integrity
> > constraint.
>
> Gibberish.
>
> Name the constraint you are talking about.

I mean, you cannot update this:

Student Course DateTime Room

to this:

Student Course DateTime Room

which would create an inconsistency, because of the foreign key on {Course, DateTime, Room}. If you remove the foreign key, no constraint is violated.

Nicola

• news://freenews.netfront.net/ - complaints: news_at_netfront.net ---