11 - Rhythm's gonna get you - Complex Data model

[sbettaglio]

Rhythm's gonna get you

For the past few lessons, we have chatted about and worked with data; for this project, we continue our journey into data and modeling a database. We are starting a record label company, and we a place to store our bands, albums, and songs. You are creating a console app that stores our companies information in a database.

Objectives

• Practice working with SQL
• Practice working with ORMs (EF Core)

Requirements

Create a console that allows a user to store and manage the company's bands, albums, and songs.

You can use the template we have been using, but you can also use the one we talked about today. You can install it by using the following command:

 dotnet new --install SDG.templates.Console.Database::1.0.0

Also, feel free to use the package we talked about during the with me at 3 to help with the menu system.

Explorer Mode

• [x] Create a database that stores Albums, Bands, and Songs. They should have at least the following properties, use your best judgment for types. (I have left off the Foreign keys intentionally)

  • [x] Album

  • Id

  • Title

  • IsExplicit

  • ReleaseDate

  • [x] Band

  • Id

  • Name

  • CountryOfOrigin

  • NumberOfMembers

  • Website

  • Style

  • IsSigned

  • PersonOfContact

  • ContactPhoneNumber

  • [x] Song

  • Id

  • Title

  • Lyrics

  • Length

  • Genre

  • For the relationships, add keys are needed to fulfill the following

  • [x] 1 Album has many Songs

  • [x] 1 Band has many Albums

  • [x] Create an interface to let the user :

  • [x] Sign a band (add a new band)

  • [x] Produce and album (add a album, and add a few songs to that album)

  • [x] Let go a band (update isSigned to false)

  • [x] Resign a band (update isSigned to true)

  • [x] View all albums for a band

  • [x] View all the albums, ordered by ReleaseDate

  • [x] View and Albums song

  • [x] View All bands that signed

  • [x] View all bands that are not signed

Adventure Mode

• [x] As you see, our data is not very normalized, Add the following relationships and tables
  • [x] A song can have many genres
  • [x] A band can have many styles
• [x] Not only do you want to store band, as also band members, create a new table called Musicians give it a many to many relationships with a Band

• Add the following queries

  • [ ] View albums in a genre
  • [ ] View all members of a band

Epic Mode

• [ ] Project structure is becoming more important as we progress through the cohort. Challenge yourself to have ReadLine and WriteLine only in your Program.cs
• [ ] (this is a big leap, but a good one)On Monday we are starting to talk about APIs. Add integrations from Last.fm API. This API has a bunch of features. Go crazy; the sky is the limit. hint: use the Httpclient in C#

Additional Resources

• PostgreSQL Documentation
• SQL Notes
• Join Notes

Recommended Practice:

• For more practice, Hackerrank has a SQL Track

[sbettaglio]

https://github.com/sbettaglio/EmpireRecords/commits/master completed explorer mode

[sbettaglio]

https://github.com/sbettaglio/EmpireRecords/commits/master added new menu interface.

[sbettaglio]

added many genres for a song and many styles for a band

[sbettaglio]

many to many band & musician relationship