Re: why administrator refuse to give permission on PLUSTRACE

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.

> 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

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.

> But given that it contains the doc_name, which would normally be in the
> documents table I think that is a leap not necessarily justified.

As you say: "which would normally be in the documents table". So this raises the question why it isn't. I realize of course that this is totally irrelevant for the question and I did notice that doc_id is unique for the test data, so it might be the primary key. But it is something that struck me as odd.

>> Since there are no constraints on the table we
>> can only guess what the primary key is: If it is either doc_id or
>> doc_name, then the table is in 2nd NF. But if it is the combination of
>> either with person_id, then it isn't. The name "t" isn't descriptive
>> enough to tell which is the case, and real-world experience tells me
>> that there are often several persons associated with a single document.
>
> Perhaps it is a DW table. <g>

Yep, perhaps ;-).

        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