Starting from:
$34.99
CSE202 Pair-wise Discussion and Take Home Exam Solution
Instructions:
• It is an OPEN book exam. However, we trust you will not take cross-group help and will not use ChatGPT for this exam.
• The final evaluation will be based on the demonstration of your submitted script running on MySQL.
• List your assumptions, if any. We will accept all reasonable assumptions.
Let us design a DB for the following requirements:
• A highly popular ‘McM Sangeet company’ plans to organize a competition for searching the right talents to train them for creating music albums. The company advertises their recruitment requirements on different channels (both print and digital media) for inviting talents by submitting their personal information, prior experience, and a 2-5 minutes media file, which is of either an audio file (for songs) or a video (for songs and/or playing music instruments), to a given URL in the advertisement. The company writes in the advertisement that the shortlisted candidate will be informed by Phone and/or Email.
• A candidate can submit more than one entry and has an option to provide more than one phone number in the submission, which are unique to him/her.
• Different music groups (pop, classic, leisure, evergreen, …) are formed to create the music albums (audio/video). Every member belongs to one or more music group which is moderated by a director who himself/herself is a member of the group. Each member has a different role to play in each album.
…).
• The McM decides the price of the album after analysing the data collected from its trailer release, and then the album is released to distributors who eventually will sell it online.
1
Design and draw an ER diagram. Your E-R diagram should clearly --
• Identify all entities (Sole/Generalized/Weak…) and their attributes (atomic, multivalued, derived)
Identify relationships between these entities and attributes of relationship
• Identify and underline the primary key of each entity
• Identify entity relationship participation and cardinality constraints
Question 2: Convert your above E-R diagram into relational schemas and implement these tables in MySQL with proper primary keys and other constraints. Populate each relation with a few data instances (say 8-10 tuples in each relation). (10)
B. List all participants who have submitted both Audio and Video files.
C. List all members who have been the member of more than one album
D. List all members of ‘Pop’ music group who are not part of any other music group.
E. List all distributors who sold all types of albums
Question 4: (You need to submit the script for these queries as like question-2 above.) Write and execute the following SQL statement on relational schemas of Question 2 implemented in MySQL.
B. List all members who have been the member of more than one group. (2)
C. List all members of ‘Pop’ music group who are not part of any other music group. (2)
D. List all participants who have submitted both Audio and Video files. (2)
E. The McM company would like to analyze the data for “Which advertisement channel has been effective that attracted maximum number of entry submissions?” (2)
2
• It is an OPEN book exam. However, we trust you will not take cross-group help and will not use ChatGPT for this exam.
• The final evaluation will be based on the demonstration of your submitted script running on MySQL.
• List your assumptions, if any. We will accept all reasonable assumptions.
Let us design a DB for the following requirements:
• A highly popular ‘McM Sangeet company’ plans to organize a competition for searching the right talents to train them for creating music albums. The company advertises their recruitment requirements on different channels (both print and digital media) for inviting talents by submitting their personal information, prior experience, and a 2-5 minutes media file, which is of either an audio file (for songs) or a video (for songs and/or playing music instruments), to a given URL in the advertisement. The company writes in the advertisement that the shortlisted candidate will be informed by Phone and/or Email.
• A candidate can submit more than one entry and has an option to provide more than one phone number in the submission, which are unique to him/her.
• Different music groups (pop, classic, leisure, evergreen, …) are formed to create the music albums (audio/video). Every member belongs to one or more music group which is moderated by a director who himself/herself is a member of the group. Each member has a different role to play in each album.
…).
• The McM decides the price of the album after analysing the data collected from its trailer release, and then the album is released to distributors who eventually will sell it online.
1
Design and draw an ER diagram. Your E-R diagram should clearly --
• Identify all entities (Sole/Generalized/Weak…) and their attributes (atomic, multivalued, derived)
Identify relationships between these entities and attributes of relationship
• Identify and underline the primary key of each entity
• Identify entity relationship participation and cardinality constraints
Question 2: Convert your above E-R diagram into relational schemas and implement these tables in MySQL with proper primary keys and other constraints. Populate each relation with a few data instances (say 8-10 tuples in each relation). (10)
B. List all participants who have submitted both Audio and Video files.
C. List all members who have been the member of more than one album
D. List all members of ‘Pop’ music group who are not part of any other music group.
E. List all distributors who sold all types of albums
Question 4: (You need to submit the script for these queries as like question-2 above.) Write and execute the following SQL statement on relational schemas of Question 2 implemented in MySQL.
B. List all members who have been the member of more than one group. (2)
C. List all members of ‘Pop’ music group who are not part of any other music group. (2)
D. List all participants who have submitted both Audio and Video files. (2)
E. The McM company would like to analyze the data for “Which advertisement channel has been effective that attracted maximum number of entry submissions?” (2)
2
1 file (104.7KB)
