Description

Total Marks: 20
Instructions:
• This is a group (of 2 students) discussion and take home exam. One submission (both hard copy and script) per group is required and both students will get the equal marks.
• 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 answer sheet (hard copy) and the script MUST be submitted by Thursday (February 22) 12:30 PM. The hardcopy needs to be submitted in the DROPBOX and the script needs to be uploaded on the link on GC. We shall tell you the location of the DROPBOX on GC. Any late submission will NOT be accepted.
• 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.
Question 1: Hard copy needs to be submitted for this question. This question carries ZERO marks. However, the subsequent questions depend on the answer of this question 1. If you will not submit the answer of this question, you will not get any marks in subsequent questions.
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.
• These shortlisted candidates are then invited to perform a ‘Live’ show at IIITD and finally top ‘n’ candidates are selected in each album category (i.e. audio and video). These candidates are called as the member of McM-2024.
• 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
Note, each distributor may be charged a different price per unit depending on the negotiation between the McM and distributor.
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)
(The rubric of this question is based on correctly creating tables in MYSQL and identifying the constraints as per the application scenario. Any variation between the implemented relational schema and the ER diagram will attract -1 marks per table. You need to upload the script that creates all the tables and populate the data in the tables as done in the assignment. The link for the same will be shared on GC. You may like to see an example script on GC for the format.)
Question 3: (These answers have to be submitted on the answer sheet given to you. Non submission of each question shall attract -1 marks.) Write the Relational Algebraic Expression (RAE) for each of the following query on relational schemas of Question 2. Illustrate each of the relation algebraic query through relations instances that you created in Question-2. Please follow the notations as discussed in the class.
A. List all ‘Audio’ albums released in 2020.
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.
A. List all ‘Audio’ albums released in 2020. (2)
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