Many to Many and Weak Entities

Question

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.

Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums.

So we have a many-to-many relationship between the album and the tracks.

If the album is a weak entity, then its primary key is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing a many-to-many relationship.

The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?

Answer 1

I think you can, using a "diamond" relationship diagram:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;

Answer 2

Unfortunately I don't have enough rep to comment on ypercubeᵀᴹ's answer, so I'll post an alternate answer instead - I agree with that answer in general but think the primary key and unique contraints on AlbumTrack are incorrect given that albums and tracks are both weak entities. For example, the following valid data would, with the prescribed constraints, be disallowed:

 artistID | albumID | trackID | trackNo 
----------+---------+---------+---------
        1 |       1 |       1 |       1
        2 |       1 |       1 |       1

Instead I would set PRIMARY KEY (artistID, albumID, trackID) and drop the unique constraint, resulting in:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (artistID, albumID, trackID)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
) ;

Tracks are still constrained to occur at most once per album.

Also, the question doesn't actually specify that tracks are weak entities (only that albums are) - if tracks can in fact exist independent of artists, the Track and AlbumTrack tables are defined slightly differently:

CREATE TABLE Track
( trackID INT NOT NULL
, artistID INT
, title VARCHAR(100) NOT NULL
, PRIMARY KEY trackID
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (artistID, albumID, trackID)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (trackID)
    REFERENCES Track (trackID)
) ;