Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: why administrator refuse to give permission on PLUSTRACE

Re: why administrator refuse to give permission on PLUSTRACE

From: Peter J. Holzer <>
Date: Mon, 5 Nov 2007 16:26:04 +0100
Message-ID: <>

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

> Peter J. Holzer wrote:

>> On 2007-11-04 19:54, DA Morgan <> wrote:
>>> Peter J. Holzer wrote:
>>>> On 2007-11-03 20:20, DA Morgan <> wrote:
>>>>> Peter J. Holzer wrote:
>>>>>> On 2007-11-03 14:09, DA Morgan <> 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.


   _  | Peter J. Holzer    | It took a genius to create [TeX],
|_|_) | Sysadmin WSR       | and it takes a genius to maintain it.
| |   |         | That's not engineering, that's art.
__/   | |	-- David Kastrup in comp.text.tex
Received on Mon Nov 05 2007 - 09:26:04 CST

Original text of this message