Music Rental Agency Relational Database Schema

client$($clientID$,$ firstName, lastName, phone, address$)$

cd$($cdCode$,$ cdTitle, numberSold, year, groupCode$)$

availableCopy$($cdCode$,$ seqNum$)$

rentedBy$($cdCode$,$ seqNum, clientID, fromDate, endDate, pricePerDay$)$

song$($songCode$,$ songTitle$)$

musicalGroup$($groupCode$,$ groupName$)$

artist$($artistiD$,$ firstName, lastName, yearBorn$)$

topCDs$($odCode$,$ year, rating$)$

composedOf $($cdCode$,$ songCode, trackNumber$)$

topSongs$($songCod$,$ year, rating$)$

member$($groupCode$,$ artistiD, fromDate, toDate$)$

writtenBy$($songCode$,$ artistlD$)$

Queries

The output schema for the query is given in parentheses following the query specification.

Which songs appeared on the CD rated number one in $2003?$

Order the results in ascending order on track number.

$($songCode$,$ songTitle, trackNumber$)$

For each group in the database, find the number of $CDs$ rated in the top $10.$

Order the results in descending order of the number of top $10CDs.$

$($groupCode$,$ groupName, numberOfTop$10$CDs$)$

What is the yearly maximum, the minimum, and average number of tracks on CDs published since the year $2000?$ Order the results in chronological order by year.

$($year$,$ maxNumber, minNumber, avgNumber$)$

Find the list of musical groups such that every client in Detroit rented at least one of their CD$\text{'}$s$.($The same division query as appeared in HW$1).$

$($groupCode$,$ groupName$)$

Which artists that have written a top $5$ song are currently not a member of any group?

Order the results alphabetically by last name and first name.

$($artistID$,$ firstName, lastName, yearBorn$)$