Re: why administrator refuse to give permission on PLUSTRACE

From: Peter J. Holzer <hjp-usenet2_at_hjp.at>
Date: Mon, 5 Nov 2007 16:26:04 +0100
Message-ID: <slrnfiudgc.er6.hjp-usenet2@zeno.hjp.at>

On 2007-11-05 04:02, DA Morgan <damorgan_at_psoug.org> wrote:

> Peter J. Holzer wrote:

>> On 2007-11-04 19:54, DA Morgan <damorgan_at_psoug.org> wrote:

>>> Peter J. Holzer wrote:
>>>> On 2007-11-03 20:20, DA Morgan <damorgan_at_psoug.org> wrote:
>>>>> Peter J. Holzer wrote:
>>>>>> On 2007-11-03 14:09, DA Morgan <damorgan_at_psoug.org> wrote:
>>>>>>> And just because I'm in the mood here's another one I give my
>>>>>>> students.  Here's the setup.
>>>>>>>
>>>>>>> CREATE TABLE t (
>>>>>>> doc_id    VARCHAR2(10),
>>>>>>> person_id NUMBER(7),
>>>>>>> doc_name  VARCHAR2(45));
>>>>>> That table looks denormalized, which may be good for performance, but
>>>>>> bad for consistency.
>>>>> Nothing about the table description that indicates any
>>>>> denormalization.
>>>> The column names doc_id and doc_name suggest that there's
>>>> functional dependency between them.
>>> Please explain this conclusion to me. Perhaps some of my students are,
>>> by some strange logic, doing the same thing and I would truly like to
>>> understand this.

>>
>> The common prefix "doc_" suggests to me that they refer to the same
>> document, so that for each row, doc_name contains the document name for
>> the document with the Id doc_id.

>
> A valid assumption but that doesn't lead to denormalization.

Not by itself. It is only one of two necessary conditions. You snipped the other one.

>>> In truth the design should be leading you to consider this table to be
>>> an intersecting entity ... possibly between one table with documents
>>> ... pk = doc_id and a second table named person ... pk person_id.

>>
>> Exactly. Now assume that the persons table contains two persons with
>> person_id 1 and 2, and the documents table contains three documents with
>> doc_id A, B, and C. Document A is associated with both persons,
>> Documents B and C are associated with only one person, so our table t
>> looks like this:
>>
>> doc_id person_id doc_name
>> A 1 Document A
>> A 2 Document A
>> B 1 Document B
>> C 2 Document C

>
> Or perhaps each document has one and only one person responsible for it
> being in the table. Perhaps the primary author or the contact person
> that must approve changes.

Yes. I already mentioned that possibility 2 days ago, and I also wrote that the table is in 2NF *if* this is the case.

>> If my earlier assumption that "doc_name" is the name of the document
>> with id doc_id is correct, this table isn't in 2NF - to change the name
>> of document A you need to update two rows. To normalize the design you
>> would have to move the doc_name column to the documents table.

>
> Au contraire. A table of this structure:
>
> doc_id   NUMBER,
> doc_name VARCHAR2
>
> is 4NF.

Yes, of course. It doesn't have the person_id column. Again:

If doc_id is a candidate key for the table, then it is in 2NF.

If doc_id is not a candidate key for the table, but (doc_id, person_id) is (as would be the case if t was an intersection table between a documents table and a persons table) then it is not in 2NF.

[...]
> But irrelevant to tuning a query against it.

Yes. I already said so.

        hp

-- 
   _  | Peter J. Holzer    | It took a genius to create [TeX],
|_|_) | Sysadmin WSR       | and it takes a genius to maintain it.
| |   | hjp_at_hjp.at         | That's not engineering, that's art.
__/   | http://www.hjp.at/ |	-- David Kastrup in comp.text.tex

Received on Mon Nov 05 2007 - 09:26:04 CST