Question

Consider the following data about some musicians irth_year country instr instrument_category genre name USA USA USA Scotland voice Mexico USA Australia guitar, voice string, wind Sth Korea voice Pat Metheny Taylor Swift Yo-Yo Ma Susan Boyle Antonio Sanchez 1971 Wynton Marsalis 1961 Keith Urban Kwon Ji-yo 1954 1989 1955 1961 guitar guitar, voice string, wind cello string azz pop, country classical classical string wind percussion brass drums azz trumpet azz 1967 ng1989 countrv wind pop Design a database, in third normalised form (3NF), which would be suitable for storing this data. You should specify: i) How many tables you will have; ii) What the columns are in each table; iii) Which columns contain primary keys and foreign keys. When you have a foreign key, it should be clear from the column name which primary key it refers to. If it's not obvious, you can state it somewhere. I recommend that you use integer values for the primary keys. Make sure your design takes into account the following features of the data: . There are two many-to-many relationships in the data There is a correspondence between instruments and their categories . There should be a constraint on instrument categories, so they can only be string, wind, percussion, or brass. Type up your design in a plain text file with extension .txt, that the marker can read. Do not be surprised if you have quite a few tablesit's overkill for such a small dataset, but it's important to use 3NF for large databases

Answer 1

First we convert table to 1NF

Name	Birth_year	country	instrument	Instrument_category	Genre
Pat metheny	1954	USA	Guitar	String	Jazz
Taylor swift	1989	USA	Guitar	String	Pop
Taylor swift	1989	USA	Voice	Wind	Country
Yo-yo ma	1955	USA	Cello	String	Classical
Susan boyle	1961	Scotland	Voice	Wind	Classical
Antonio Sanchez	1971	Mexico	Drums	Percussion	Jazz
Wynton marsalis	1961	USA	Trumpet	Brass	Jazz
Keith urban	1967	Australia	Guitar	String	Country
Keith urban	1967	Australia	Voice	Wind	Country
Kwon Ji- yong	1989	Sth Korea	Voice	wind	pop

Table in 3NF

Musician Table

Musician_ID(PK)	Name	Birth_year	country
1	Pat metheny	1954	USA
2	Taylor swift	1989	USA
3	Yo-yo ma	1955	USA
4	Susan boyle	1961	Scotland
5	Antonio Sanchez	1971	Mexico
6	Wynton marsalis	1961	USA
7	Keith urban	1967	Australia
8	Kwon Ji- yong	1989	Sth Korea

Instrument table

Instrument_ID(PK)	Instrument	Instrument_category
1	Guitar	String
2	Voice	Wind
3	Cello	string
4	Drums	percussion
5	Trumpet	brass

Plays table (musician plays Instrument, many-to-many relationship)

Musician_ID(FK)	Instrument_ID(FK)
1	1
2	1
2	2
3	3
4	2
5	4
6	5
7	1
7	2
8	2

Genre table

genre_ID(PK)	genre
1	Jazz
2	Pop
3	Country
4	Classical

Has Table(musician has genre, many-to-many relationship)

Musician_ID(FK)	Genre
1	1
2	2
2	3
3	4
4	4
5	1
6	1
7	3
7	3
8	2

Relational Schema

Genre Musician genre ID genre musician id Instrument ame birth_Year country instrument ID instrument nstrument_Category has instrument ID (F) musician id (FK) Чmusician id (FK) genre ID FK)