Re: How to normalize this?

On Mon, 11 Feb 2013 21:36:11 +0000
Eric <eric_at_deptj.eu> wrote:

> > I can formulate the requirement that would be violated in terms of
> > the example (if a band and singer perform on the same date, they
> > must be in the same location on that date).
>
> The scenario is plausible, but I don't feel any more comfortable with
> it than I did with the abstract example. I think (Band, Date) -->
> Location and (Singer, Date) --> Location are correct (they correspond
> to performer itineraries), but all I can get to after that is
>
> (Band, Singer, Date, Location) --> Song

	{Band, Singer, Date} -> Location
	{Band, Singer, Date, Song}

because "If a band plays multiple songs on the same date, they all have to be in the same location."

That leaves

1. (Band, Date) --> Location
2. (Singer, Date) --> Location
3. (Band, Singer, Date, Song)

with the constraints that

4.  every {Band, Date} in (3) appears in (1) 
5.  every {Singer, Date} in (3) appears in (2) 
6.  every {Band, Singer, Date} in (3) appears in the 
	join of {(1),(2)} on {Date, Location}

It is perfectly possible that

7. there are dates for which a band or singer is not available (has no location, per CWA), or
8. a band/singer is at a location but won't sing (no song)

> > ... if ... I change the location for Placido Domingo to New York,
> > I have to change it for Fun too - otherwise they won't be
> > performing together!

Yes. Why does that bother you? Two facts must change -- place and performance -- else there is no song.

Codd referred to "time-varying relations", and this is illustrative. Let us say on Tuesday Placido's agent agrees on the New York location, but Fun has not. Update (2), delete from (3) because, as of Tuesday, that's all you've got. Wednesday Fun's agent calls to commit to New York. Update (1), insert into (3).

> > In the original, single-table version, this is not a problem. It's
> > one single update. But that violates 2NF.

It violates 2NF or not, depending on what you're keeping track of. If you're only tracking performances (not bands and singers), the relationship

        {Band, Singer, Date, Song} --> {Location}

suffices, provided these are unique:

	{Singer, Date, Song}
	{Band, Date, Song}
	{Band, Singer, Location, Song} 
	

to prevent a singer or band from appearing in two places at once, and to prevent a singer from singing with two bands on the same night.

HTH. --jkl Received on Tue Feb 12 2013 - 05:58:10 CET