Re: Search on Child Record Set

I was not attempting to indicate that Thomas's code was wrong as it solves the problem but, more to the point, the representation of the data is wrong and therefore the solution that is proposed is irrelevant.

Thomas resolves the data representation thus:
:I have 2 tables:
:
:create table emp( empno number primary key,
: .... other emp data like names and stuff
... );
:
:create table emp_skill ( empno number,
: skill varchar(n),
: primary key(empno, skill_code),
: foreign key(empno) references
emp(empno) );
:
:(seems logical to me given the information above -- and by
the way, this does
:NOT preclude emp_skill from being an intersection table to
a skills table with
:more information about skills does it. rhetorical question)

This may seem logical but we have here an attribute, skill varchar(n), of emp_skill that is not solely dependent on the primary key (empno#,skill_code#) of the entity that contains it. This is not a correct relation. Therefore we should discount this representation and seek one that truly reflects the situation.

Alan's original description of the problem stated: "An employee (hopefully) has many skills."

We can guess from our experience of typical businesses that there is more than one employee and that it is possible (although not mandated) that there will be some overlap in the collective employee skill set. Therefore we have two statements that are true:

1 - An employee can have zero or more skills. 2 - A skill may be attained by more than one employee.

Alan also stated:
"I wish to search for people with a skill set which includes all of a search skill set. The skill set I wish to search on may have one or more matches to achieve."

The final sentence confirms our second proposition that "a skill may be attained by more than one employee" is true.

Therefore we have a many to many relation which is resolved using 3nf intersecting entity.

Thomas's reference to Fermat is very touching:

:then -- you supply NO answer to the question. This is like
Fermat with his
:"little theorem" -- famous mathematician who stated a
complex theorem and said
:the proof was too easy, every one should know it and
besides he was out of room
:on the page so couldn't jot it down.

Yes. 3nf data representation is the bread and butter of relational databases. It requires no explanation.

As for the correctness of this data representation there are two aspects. Firstly it is correct from a relation point of view having satisfied the attribute/primary key dependency, and secondly, the implementation is very efficient requiring a unique index hit and then a non-unique index hit. You don't actually hit a data table until you retrieve the EMP data (using an index).
--
...neil {actually: neil [dot] hulin [at] litech [dot] freeserve [dot] co [dot] uk} Received on Wed Dec 02 1998 - 08:38:16 CST