CS代写|CS-GY 6083 sections A and INET Spring 2023



Part 2

Shortly after Part 1 is due, the TAs will post an ER diagram, schema diagram, and table definitions for FatEar that you should use for the rest of the project. (Previously, I indicated that you would create the schema and table definitions in Part 2, but we have decided to give them to you, so you can begin writing application code (Part 3) sooner).

Write SQL queries (or sequences of SQL queries) for the following tasks:

  1. Show the profile of a particular user, given the user name.
  2. List all friends of a user.
  3. For a given user, list all her followers, and all users she follows herself.
  4. For a given user, list all friendship requests that have been made to that user but have not been answered yet.
  1. For a given user, list all new content that this user should be notified of, that is, reviews by friends or people she follows that were created after her last visit to the site.
  1. List all artists who play Jazz, i.e. play some song for which the genres include Jazz
  2. Insert a new rating given by a user for a song.
  3. List all songs of a particular genre (e.g., Jazz) in the database, sorted in descending order by average rating.

Populate your database with some sample data, and test the queries you have written in part (c).

Make sure to input interesting and meaningful data and to test a number of cases. Limit yourself to a few entries each, but make sure there is enough data to generate interesting test cases. It is suggested that you design your test data very carefully.

What to hand in for Part 2:

Two .txt or .sql files, one of which contains the INSERT statements for populating the database and one which contains the solutions to 1 – 8, labeled by comments.

Preview of Part 3

In part 3 of the project, you will use the table definitions we will post to implement application code for FatEar as a web-based application. You may use Python, PHP, Java, node.js, or Go.

If you’d like to use some other language, check with the TAs by 03/28/2023. You must use prepared statements if your application language supports them.

I have supplied Python code for a sample application that includes most of the constructs you’ll need in order to do this assignment, along with videos that explain the code in detail. If you do not have prior experience with web and/or database application programming, you should study these and you may modify the provided sample code to implement FatEar.

You may continue to work alone or with the one or two others with whom you worked on parts 1 and/or 2 or you may form a team (two or three students, total): Any new teams or additions to a team must be reported to the TAs (by e-mail or on EdStem by March 28.) Everyone on a team is expected to understand all the code.

Go back and read the project description posted with Part 1. In part 3, you’ll write application code to implement FatEar as a web application.

FatEar should support the following use cases:

  1. All users, whether or not registered/logged in should be able to search and browse for music:

.Search for Songs : Find the titles, artists, and albums of songs of a particular genre and/or with an average rating above some threshold and/or by a particular artist, etc.

  1. Login: The user enters her username and password. FatEar will add “salt” to the password, hash it, and check whether the hash of the password matches the stored password for that username. If so, it initiates a session, storing the username and any other relevant data in session variables, then goes to the home page (or provides some mechanism for the user to select her next action.) If the password does not match the stored password for that username (or no such user exists), FatEar informs the user that the login failed and does not initiate the session. We will supply Python/Flask code for this. If you’re using a different implementation language, you’ll need to write this yourself. After successful login, the user should see their profile, the recipes they’ve posted and the groups they belong to. (During initial development and testing, you may use plain-text passwords.)

The remaining use cases require the user to be logged in.

  1. See new items of interest, such as
  2. new reviews posted by friends or followers
  3. New songs by artists the user is a fan of
  4. Rate a song
  5. post a review of a song
  6. Manage friend requests:
  7. Accept or reject a friend request
  8. Issue a friend request to another userWe may also ask you to propose and implement another feature or two. More details later.

If you are working with one or two others, also include the following (or propose something of similar complexity). We’ll expect a little more from 3 person teams than from 2 person teams. More details later.

  1. Add a way for registered users to create playlists. A playlist is a collection of songs.

Each playlist has a name, which is unique for the user who created it, but different users may have playlists with the same name (e.g. user1 and user2 can both have playlists called “goodnight music”, but user1 can’t have two such lists. Add some other useful attributes (creation date ?, description?). Add any needed table definitions.

a.Create a playlist

b.Add songs to a playlist

c.List the songs on a playlist