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
Consider the following data about some musicians irth_year country instr instrument_category genre name USA USA USA...