The brass band association has asked for a database.

Question

They need to manage bands and their members, as well as the pieces played by members. Consider the scenario below and answer the questions in part a) and b) below.

The following rules describe the scenario:

• A band has a name and a location (e.g. the town they are based in).

• A band has one or more members.

• Band association rules prescribe that each member can only play in one band.

• A member has a name and a date when they joined the band.

• Each member plays one or more instruments.

• Each instrument is played by one or more members (e.g. there could be a number of tubas in the band).

• Each instrument has a name and a key (in the musical sense: e.g. Bb).

• Music is organised in pieces, where each piece has a name and composer.

• Each piece needs a number of instruments, and obviously each instrument can be used in a number of pieces.

• A piece might have a single solo instrument.

• An instrument can occur in many pieces as a solo.

a) Create an entity relationship diagram for the scenario provided using a suitable notation. Your answer must show entities with their attributes and their relations (including cardinality and optionality).

b) Using your diagram from part a), design a set of tables. Clearly identify all primary and foreign keys.

Leave an answer

Sorry, you do not have permission to answer to this question .