Laboratory Exercise $2$

Creating and Using a New Database in Access $2010$ or higher versions

Section B:

In this section create a New Music Database in Access $10$ or higher version, given the following

schema:

CD $($CDNumber$,$ Title, Year, Price$)$

Track $($CDNumber$,$PerformanceCode, trackNo$)$

Recording $($PerformanceCode$,$ SongTitle, Year$)$

Composer $($CompName$,$ SongTitle, PerformanceCode$)$

Singer $($SingerName$,$ PerformanceCode$)$

Write SQL queries $($and verify by checking the results of your$)$ that it will find correctly:

$1)$ The people who have written and sung a particular song and whose name is begin with $$D$.$

$2)$ The titles of the CDs that contain songs of which the year of recording is not known.

$3)$ The tracks on the CDs with the serial number $78574.$ Provide these in numerical order,

indicating the performers for the track having a singer$.$

$4)$ The exclusive composers and singers. That is$,$ composers who have never recorded a song and

singers who have never written a song.

$5)$ The singer on the CD that contains the largest number of songs.

$6)($Optional$)$ The CDs on which all the songs are by a single singer and on which at least three

recordings are from years preceding the release year of the CD$.$

$7)($Optional$)$ The singers who have never recorded a song as soloist.

$8)($Optional$)$ The singer who has never made a CD in which appears as the only singer$.$

$9)($Optional$)$ The singer who has always recorded songs as soloist.

Submit the followings for this section individually to Blackboard.

a$.$ Submit the ER$/$EER diagram for the New Music database.

b$.$ Provide a mapping of the created ER$/$EER into a set of relations. Specify all primary keys and

foreign keys.

c$.$ Submit in a file the data that you have used to populate your database.

d$.$ Submit the SQL queries and screenshots of the results from the Access Database for the above

queries