Re: candidate keys in abstract parent relations

OK. First off I want to thank everyone for their input, which has been very useful to me. I feel like I have the core of the model worked out, although some work remains to be done in splitting Releases from Albums
(I have belatedly realized that Discs and Sides have some
Release-specific attributes, so Release Discs and Release Sides exist alongside Album Discs and Album Sides, with each Release Disc being associated with an Album Disc, but with the potential that no Release Discs exist for a given Album Disc. You're all just going to have to trust me (for now) that this makes sense within my schema.)

I do want to circle back to my original posting, though. I'm working my way through _Database in Depth_, and aside from finding it a little lacking in precision and rigor in places (says the guy who had to take discrete mathematics three times, because he kept getting bored halfway through the semester), it's a total rush. Date makes all this stuff make *sense*. Also, I've read "Double Trouble, Double Trouble" and its associated discussion of identity, and found it very enlightening in terms of the problems I've been encountering.

In the object-oriented model, it makes sense to define the identity of objects in terms of the collections they aggregate. A parent object with four children can be defined to be different from an object with a different four children, even if all the other attributes of that object are equal. I was gratified to see that Date's conception of the relational model agrees with the OO model, because it makes instant intuitive sense to me.

Here's a greatly simplified version of the Album table in Tutorial D:

VAR ALBUM BASE RELATION
  { TITLE NAME, ARTIST NAME,
    TRACKS RELATION
    { TRACKNAME NAME, INDEX INDEX, LENGTH DURATION } } KEY { TITLE, ARTIST, TRACKS } which implies:

   TUPLE {TITLE "Peter Gabriel", ARTIST "Peter Gabriel", TRACKS RELATION {TRACKNAME CHAR, INDEX INTEGER, LENGTH INTEGER} {          TUPLE {INDEX 1, LENGTH 240, TRACKNAME "Intruder"}}}

is not equal to

   TUPLE {TITLE "Peter Gabriel", ARTIST "Peter Gabriel", TRACKS RELATION {TRACKNAME CHAR, INDEX INTEGER, LENGTH INTEGER} {          TUPLE {INDEX 1, LENGTH 315, TRACKNAME "On the Air"}}}}

because both tuples fit into the relation ALBUM, with its natural key, with no integrity violations.

(BTW, Rel is a nifty tool to have available for these sorts of modeling
problems, as is Tutorial D)

So the relational model does provide a general solution to my problem. Allowing relational values to be assigned to attributes within tuples of enclosing relation variables makes clear that the album's identity
(and therefore uniqueness) is defined by including the set of tracks
contained by the album.My question is: is there a convention, technique, or nifty hack that would allow me to do the same thing in SQL without resorting to surrogate keys?

My (non-exhaustive) reading of SQL 92 says no, which sucks, but it makes me feel better about defining my Album table's row uniqueness via a human readable version identifier and a surrogate key. *I* know what my Album's definition of identity is, even if SQL doesn't. ;)

Tutorial D definition of my actual current Album relation:

VAR ALBUM2 BASE RELATION
  { TITLE CHAR, ARTIST CHAR, VERSION CHAR } KEY { TITLE, ARTIST, VERSION }; in which

   TUPLE {TITLE "Peter Gabriel", ARTIST "Peter Gabriel", VERSION "Peter Gabriel [2]"}

is not equal to

   TUPLE {TITLE "Peter Gabriel", ARTIST "Peter Gabriel", VERSION "Peter Gabriel [3]"}

Or, the actual PostgreSQL DDL:

CREATE TABLE albums
(

    id SERIAL,
    primary_performer_id INTEGER NOT NULL,     name VARCHAR(255) NOT NULL,
    version VARCHAR(255) NOT NULL,
    musicbrainz_guid CHAR(36) NOT NULL DEFAULT '000000-0000-0000-0000-000000000000',

    CONSTRAINT surrogate_albums PRIMARY KEY(id),     CONSTRAINT natural_albums UNIQUE(primary_performer_id, name, version),

    CONSTRAINT albums_artists_fk FOREIGN KEY(primary_performer_id)

      REFERENCES artists
      ON UPDATE RESTRICT
      ON DELETE CASCADE
      IMMEDIATE INITIALLY IMMEDIATE,