$12$

Figure $1.$

If you cannot see above figure $1,$ download it here darr.

Q$1-1(2$ points$)$ We would like to create MySQL$\text{'}$s codes for seven $(7)$ entities and relationships, as the ERD presents in figure $1.$ Please specify the order to complete this entire ERD.

Step $1$: make Table

Step $2$: make $($ Table

Step $3$: make L $$ Table

Step $4$: make $(q,$ Table Step $5$: make $($ TableQ$1-2.(3$ points$)$ Create SQL codes to create the following four entities: Album, Artist, Label, & Song.

$($Hint: You should consider the order of entity creations as you answered $1.(1))$

$($Hint: You can find the "type" information of attributes in figure $1.)$

Write your SQL code:

Edit View Insert Format Tools Table

$$

p

O words

Q$1-3.(3$ points$)$ Now, consider we want to track new information $-$ Star Rate and Genre. Therefore, you need to create two new entities: "StarRate" and "Genre."

In the "StarRate" entity, we will keep two attributes StarID as PK and StarRate. $($PK should be char$(5),$ and Rate$-$related attribute should be INT.$)$ We want to track the star rate for albums and songs as well. Therefore, we want to link the "StarRate" entity with "Song" and "Album" entities.

In the "Genre" entity, we want to save two attributes GID as PK and GenreName $($PK should be char$(5)$ and GenreName should be varchar$(20).)$ In "Song" entity, we want to track its genre. Therefore, we want to make a relationship between "Song" and "Genre" entities.

Using ALTER and Create, please complete creating "StarRate" and "Genre" entities, modifying required entities, and linking the entities among "Song", "Album", "StarRate", and "Genre".

Write your SQL code:

Edit View Insert Format Tools Table

$12$pt

ParagraphQuestion $4$

$1$ pts

Q$1-4.(1$ point$)$ List all the information in Artist. When you list the information, you must sort the result by ArtistName in descending order.

Write your SQL code:

Edit View Insert Format Tools Table

$12ptvv$ Paragraph $,B,I{?}_{,}Avv{Q}_{v}v{T}^{2}vv|vdots||$

p

$0$ words

Q$1-5.(1$ point$)$ List all the information on the song and genre entities, but not GID. In addition, when you retrieve such information, the length of each song should be longer than $5$ minutes.

Write your SQL code:

Edit View Insert Format Tools Table

$$

p

$0$ words

Q$1-6.(2$ points$)$ List all the information on the album with the star rate of the album where the album was released between $2020-01-01$ and $2022-12-31.$

$($Hint; if you want to retrieve date after $2020-01-01,$ you can use date $>$ #$2020-01-01$#$)$

Write your SQL code:

Edit View Insert Format Tools Table

$12$pt $$ Paragraph $$

B

$I$

$U_{?}$

$$

p

$0$ words

Q$1-7.(2$ points$)$ List unique artist names, each of their DOBs, and each label name. $($We assume that each artist belongs to only one label.$)$ When you retrieve such information, you only want to see an artist whose name has $"$me$"$ or $"$DJ$."$

Write your SQL code:

Edit View Insert Format Tools Table

$12$pt $$ Paragraph $$

$$

p

$0$ words

Q$1-8.(2$ points$)$ Consider the groups of labels where each label has multiple artists. You want to see unique label names and aggregate information such as how many artists each label has and the CEO's name $($we assume that each CEO belongs to only one label.$).$

Write your SQL code:

Edit

View

Insert

Format

Tools

Table

$12$pt

Paragraph

$$

p

$0$ words

Q$1-9.(2$ points$)$ Consider the groups of albums where each album has multiple songs. You want to see the unique album name and aggregate information such as the total number of songs and the average length of the songs where each album has more than $3$ stars as well as each song has more than and equal to $4$ stars.

Write your SQL code:

Edit View Insert Format Tools Table

$12$pt $vv$ Paragraph

B I

$$

p

$0$ words

Q$1-10.(2$ points$)$

Finally, retrieve information such as the artist's name, the total number of albums, and the average star rating of the albums, specifically for artists who have contracts with SONY Music or EMI Music.

$($EXTRA CREDITS: IF YOU USE A SUBQUERY, $"$ IN $($SELECT $3\mathrm{.}5$ points instead of $2$ points$).$

Write your SQL code:

Edit View Insert Format Tools Table

$12ptvv$ Paragraph $vv,B,U_{v}v{Q}_{v}v{T}^{2}vv|vdots||$

p

$0$ words