Find albums by genre released after a given year

Assignment Help Computer Engineering

Reference no: EM132088447

Part a. Design a MySQL database to support a Spotify-like media cataloguing application. The application should catalogue music, including artists, their albums, songs in each album, playlists, users that created/shared/played various playlists, etc. The conceptual model describing the application's data is shown below.

Your database must store data described by every entity, relationship, and attribute type shown in the conceptual data model. Your database schema must be in the 3NF.

Your answer should consist of the CREATE TABLE statements that represent your database schema.

Assume the following about the application: every album was released by some artist. Every song belongs to some album, every album was released by an artist, and every playlist was created by some user. In other words, for each 1:n relationship type, assume mandatory participation for the entity type on the "n" side.

Part b. Write the following queries in SQL:

Q1. Find albums by a given performer. Order results by year (DESC). Display all the information about each album (its id, year, title, etc), and performer (i.e. country and style).

Q2. Find albums by genre released after a given year. Order results by year (DESC). Display all information about each album.

Q3. Find albums of a given genre released in a given country after a given year. Order results by year (DESC).

Q4. Find playlists by a given album.

Q5. Find a user who created a given playlist.

Q6. Find users who shared a given playlist.

Reference no: EM132088447

Questions Cloud

Deciding which queue acustomer enters : Identify which of the authors' classes used for their Average Waiting Time (AWT) model is responsible for each of the seven items identified in #42.

Create a new function randomadd : Create a new function randomAdd(), which adds nodes in random places in the tree, and use that function instead.

Energy performance and thermal comfort : ENEM20003 - Thermofluids Engineering Applications - Development of strategies for efficient and effective energy management in buildings is essential

Write a program that multiplies two square arrays : A function that has an integer as a parameter and returns a pointer to square array of integers (i.e. both dimensions should be equal).

Find albums by genre released after a given year : Find albums by genre released after a given year. Order results by year (DESC). Display all information about each album.

What are the possible consequences if you fail to identify : What does a physical DFD represent? How do data stores in the physical DFD differ from the data stores in the logical DFD? Give an example.

Define autoimmunity and immunodeficiency : Define autoimmunity and immunodeficiency. Which of these conditions is likely to occur in a HIV positive person

Describe the layers of the small intestines : Please Describe the Layers of the Small Intestines please be specific to include how such complex structure of the intestinal wall ensure

Compute the sha-256 checksum of the ms-word file : Mallory builds a WiFi jammer using plans she found on the Internet and jams wireless signals over a large part of her apartment building.

User Account

All Pages