Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Nov 2007 20:02:37 -0800
Message-ID: <1194235354.499838@bubbleator.drizzle.com>

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.

>> 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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Nov 04 2007 - 22:02:37 CST