Re: Fails Relational, Fails Third Normal Form

Nicola

> On Sunday, 8 February 2015 21:39:01 UTC+11, Nicola wrote:
> In article <abc8bebc-4e27-4af4-a16d-dd94ad61f21c_at_googlegroups.com>,
> Derek Asirvadem <derek.asirvadem_at_gmail.com> wrote:

I have to say, I welcome your lucidity.

> > > On Sunday, 8 February 2015 05:39:26 UTC+11, Nicola wrote:
> > >
> > > > > To find the keys, you
> > > > > must determine which functional dependencies hold.
> > > >
> > > > That is a very slow method, but yes. The tables are dead simple, and the
> > > > attributes should be well-known to anyone who has any experience at all.
> > >
> > > Sure. If you find the keys directly, in fact you have defined some
> > > functional dependencies (from the keys). But, in general, you must
> > > ensure that no other relevant semantic constraints (not necessarily only
> > > functional) are missed.
> >
> > Do you have an example of such ?
>
> Do you mean, an example in which finding the keys directly is harder
> that finding FDs first and deriving the keys from them? Well, it depends
> on how clever

Capable.

The RM and 3NF are written in technical English. Finding the Keys doesn't require any special skills other than the normal aptitude for databases (as opposed to say, programming). IQ in the upper half of the average band, ie. 100 to 110. One just needs to follow a few simple steps.

But first, before attempting that, we need to establish the facts.

> you are at "seeing"

I don't have an Ouija board or a crystal ball. I am an Orthodox Catholic. The only candles I light are at the foot of the altar, below a crucifix. I do pray, but I ask for nothing for myself. And every four years I need a stronger prescription for my lenses.

> keys, which in turn depends on how
> much experience you have. For instance (from H. Koehler):
>
> CourseSchedule(Course, Lecturer, Room, Time)
>
> where a course has only one lecturer, each class has a fixed duration,
> and the obvious constraints hold, such as teachers do not have the gift
> of ubiquity.

I couldn't find that. From what I can /see/, it looks too simple anyway.

I found a very similar example in Foundations of Data Heaps, which has one less element, way too simple for our purpose. It is aimed at simple minds.

I found Köhler's DNF paper, it has an example that is very similar, with one more element ("non-simple" in Relational terms), and it is quite fine for me. If you are happy to go with that, I have one tiny question. Given:

>>>>
Domination Normal Form - Decomposing Relational Database Schemas (sic), Henning Köhler

5 An Example

A (sic) university has oral examinations at the end of each semester, and wants to manage related data using a relational database. The relevant attributes to be stored are

____R = {Student, Course, Chapter, Time, Room}

Here Chapter denotes a chapter from the course textbook the student will be examined about. Every student can get examined about multiple chapters, and chapters may vary for each student. Multiple students can get examined at the same time in the same room, but the course must be the same. Further constraints are that a student gets examined for a >course<chapter> only once, and can't be in multiple rooms at the same time. Those conditions can be expressed through functional dependencies as follows: <<<<

1. Is the strike-out and substitution correct ? Otherwise the facts given are incoherent.

   ---

Of course, I didn't read past that point, ie. I did not peruse his non-FDs.

If the answer to [1] is "no", then please explain the contradicting requirements, and skip the rest of this post.

If the answer to [1] is "yes", then please look at this page. At this stage, before I dive into determining the Keys, I would like to make sure that I have gotten the facts right. http://www.softwaregems.com.au/Documents/Article/Normalisation/DNF%20Data%20Model%20A.pdf

Cheers
Derek Received on Sun Feb 08 2015 - 19:19:22 CET