Wiele do wielu i słabe podmioty

Mam byt, który nie może istnieć bez zdefiniowania go przez inny, i chcę, aby ten byt uczestniczył w relacji wiele do wielu.

Przykład: artysta ma album (album nie może istnieć bez artysty), album ma także wiele utworów, ale ten sam utwór może istnieć w wielu albumach.

Mamy więc relację wiele do wielu między albumem a utworami.

Jeśli album jest słabym podmiotem, to jego klucz podstawowy jest kluczem obcym odnoszącym się do artysty, a zatem nie może być kluczem obcym do innej tabeli reprezentującej relację wiele do wielu.

Pytanie brzmi: czy możliwe jest posiadanie tego rodzaju relacji w SQL, a jeśli tak, to jak to wyrazić?

Myślę, że możesz, korzystając ze schematu relacji „diament”:

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
) ;

Niestety nie mam wystarczającej liczby przedstawicieli, aby skomentować odpowiedź ypercubeᵀᴹ , więc zamiast tego opublikuję alternatywną odpowiedź - zgadzam się z tą odpowiedzią w ogóle, ale uważam, że główny klucz i unikalne ograniczenia AlbumTrack są nieprawidłowe, biorąc pod uwagę, że zarówno albumy, jak i utwory są słabe podmioty. Na przykład następujące prawidłowe dane, z określonymi ograniczeniami, zostałyby niedozwolone:

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

Zamiast tego ustawiłbym PRIMARY KEY (artistID, albumID, trackID)i upuścił unikalne ograniczenie, w wyniku czego:

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)
) ;

Utwory są nadal ograniczone co najwyżej raz na album.

Ponadto pytanie nie określa, że ​​ścieżki są słabymi elementami (tylko albumy) - jeśli ścieżki faktycznie mogą istnieć niezależnie od wykonawców, tabele Tracki AlbumTracksą zdefiniowane nieco inaczej:

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)
) ;