Newsgroups: comp.databases.oracle.server From: "Peter J. Holzer" Subject: Re: why administrator refuse to give permission on PLUSTRACE References: <1193440940.512044.231780@v3g2000hsg.googlegroups.com> <1193443457.574331.155190@d55g2000hsg.googlegroups.com> <1193465505.205361@bubbleator.drizzle.com> <5oggsqFmq935U1@mid.individual.net> <1193490896.740392@bubbleator.drizzle.com> <1193672789.586365@bubbleator.drizzle.com> <1193701194.879928.164270@t8g2000prg.googlegroups.com> <1193708319.219270@bubbleator.drizzle.com> <1194071445.120813@bubbleator.drizzle.com> <1194098964.194429@bubbleator.drizzle.com> <1194121252.374655@bubbleator.drizzle.com> <1194206042.590270@bubbleator.drizzle.com> <1194235354.499838@bubbleator.drizzle.com> Date: Mon, 5 Nov 2007 16:26:04 +0100 User-Agent: slrn/0.9.8.1pl2 (Debian) Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: NNTP-Posting-Host: zeno.hjp.at X-Trace: 5 Nov 2007 15:28:02 GMT, zeno.hjp.at Lines: 91 Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!feeder.news-service.com!transit4.hitnews.eu!transit5.hitnews.eu!news.albasani.net!news.wsr.ac.at!zeno.hjp.at!news Xref: usenetserver.com comp.databases.oracle.server:437301 X-Received-Date: Mon, 05 Nov 2007 10:28:02 EST (text.usenetserver.com) 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. hp -- _ | Peter J. Holzer | It took a genius to create [TeX], |_|_) | Sysadmin WSR | and it takes a genius to maintain it. | | | hjp@hjp.at | That's not engineering, that's art. __/ | http://www.hjp.at/ | -- David Kastrup in comp.text.tex