$2.[10$pts$]$ Design relation schemas for storing the following information about music albums, tracks,

songs, music groups, group members $($artists$),$ music genres, and record labels. $($Note you will

implement your design using SQL in Question $3).$ Your relations must support the following

requirements and constraints. For each relation define the primary key, foreign keys, NOT NULL

constraints, additional uniqueness constraints, and any other constratins, as appropriate, in your

design. Your schema should not use any $$surrogate$$ keys. Create a schema diagram of your design

and include it in your homework write$-$up$.$ Be sure to list NOT NULL constraints and any other

constraints in your schema diagram.

$1$

$$ Each album has a title, the year it was recorded, the group that recorded the album, the

tracks on the album, and the album$$s record label. While two albums can have the same

title, you can assume that a group only has one album with a given title.

$$ Each music group can be associated with zero or more music genres. Each genre has a

unique label, e$.$g$.,$jazz$,$rock$,$pop$,$rap$,$ and a longer description of the genre itself.

For instance, the description of $$indie pop$$ is $$Combines guitar pop with DIY ethic in

opposition to the style and tone of mainstream pop music.$$

$$ Each group can be influenced by zero or more other music groups. Each music group has a

name, assumed to be unique across groups, and the year the group was formed.

$$ Music artists can be members of zero or more music groups. An artist is a member of a

group within a certain range of years $($e$.$g$.,$ from $1991$ to $2000).$ It could be the case that the

artist is still a member of a group $($e$.$g$.,$ from $2007$ to the present$).$ Each artist has a name

and a birth year. For simplicity, you can assume no two artists have the same name.

$$ In cases where a record album is recorded under the name of an individual artist, assume

that the album is recorded by a group whose name is the artists$$ name. In this way, both the

artist and the other musicians involved in recording the music for the album are captured.

$$ A music track represents a recorded song. $($You can think of a track as an $$instance$$ of a

song.$)$ Each music track is associated with one or more artists that produced it and the song

it is a recording of$.$ Albums consist of one or more tracks. A particular track can also be

included on multiple albums $($e$.$g$.,$ for compilation albums or re$-$releases$).$ Each track also

has the year it was recorded along with a unique track identifier $($as a number$).$

$$ A song has a title and the year it was written. In addition, each song is associated with the

artists that it was written by$.$ For simplicity, you can assume that no two songs have the

same title.