Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!193.201.147.84.MISMATCH!xlned.com!feeder1.xlned.com!news-out1.kabelfoon.nl!newsfeed.kabelfoon.nl!xindi.nntp.kabelfoon.nl!198.186.194.249.MISMATCH!news-out.readnews.com!transit3.readnews.com!postnews.google.com!b6g2000pre.googlegroups.com!not-for-mail From: myotheraccount Newsgroups: comp.databases.theory Subject: Re: normalization question Date: Tue, 28 Apr 2009 12:13:23 -0700 (PDT) Organization: http://groups.google.com Lines: 52 Message-ID: References: <4d142477-3b5d-4e56-a7de-34415d2bb728@u10g2000vbd.googlegroups.com> NNTP-Posting-Host: 209.48.164.194 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1240946003 29632 127.0.0.1 (28 Apr 2009 19:13:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 28 Apr 2009 19:13:23 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: b6g2000pre.googlegroups.com; posting-host=209.48.164.194; posting-account=KxP0fAoAAAAOZwwOfkfCOwbMsXeXrN1D User-Agent: G2/1.0 X-HTTP-UserAgent: Opera/10.00 (Windows NT 6.0; U; en) Presto/2.2.1,gzip(gfe),gzip(gfe) Xref: news.cambrium.nl Brian, Good analysis. I definitely need to dust off my copy of Date's book. I feel like this is getting closer, but I don't think it's quite right yet. The issue with this model is that rule #2 is not enforced. Example: BoxID Date 1 '1/1/2009' CustomerID VendorID ItemID BoxID 1 1 1 1 2 1 2 1 This scenario adheres to all the keys, but same box is being sent to two different customers. On Apr 28, 9:16=A0am, "Brian Selzer" wrote: > Assuming that a Box can contain multiple Items, > > {Customer, Vendor, Item, Box, Date} has irreducible FDs > =A0 =A0 {Customer, Vendor, Item} --> Box., > =A0 =A0 {Customer, Vendor, Item} --> Date, > =A0 =A0 Box --> Customer, > =A0 =A0 Box --> Vendor, and > =A0 =A0 Box --> Date. > > As a consequence, {Customer Vendor, Item, Box, Date} has overlapping keys > =A0 =A0 {Customer, Vendor, Item} and {Box, Item}. > > The following decomposition is in 3NF and enforces all of your rules > > {Box, Date} with key {Box}, and > > {Customer, Vendor, Item, Box} > =A0 =A0 with keys {Box, Item} and {Customer, Vendor, Item} > > Provided the foreign key constraint, > =A0 =A0 {Customer, Vendor, Item, Box}[Box] IN {Box, Date}[Box] > is also in force. > > {Box, Date} is in 5NF. > {Customer Vendor, Item, Box} is in 3NF, but not in BCNF. =A0Unfortunately= , > {Customer Vendor, Item, Box} cannot be decomposed into independent > projections. > > If you have a copy of Date's /An Introduction to Database Systems/, I > suggest you look up dependency preservation. =A0It's section 12.4 in the = 8th > edition.