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: DA Morgan <>
Date: Sun, 04 Nov 2007 20:02:37 -0800
Message-ID: <>

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.

>> 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.

> 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.

The only possible confusion might be the business rule that puts a person_id into the table.

> Yep, perhaps ;-).
> hp

Truly perhaps. But irrelevant to tuning a query against it.

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Nov 04 2007 - 22:02:37 CST

Original text of this message