# 67-262: Database Project

## To-Do
- [x] Update user stories and conceptual model (10 pts)
- [x] Conceptual model

- [x] User stories:

| Type | User | I want to... |
|-|-|-|
| Simple | Listener | Change the name of a playlist. |
| Simple | Listener | Create a new playlist so I can group my favorite songs together. |
| Simple | Artist | Upload an album so listeners can listen to my latest music. |
| Analytical | Listener | See the duration of watch time for an advertisement. |
| Analytical | Artist | To see which of my songs are played the most, so I can make better music. |
| Analytical | Advertiser | To know how many users watch my ads, so I can judge their performance. |
| Complex | Listener | Get duration of playlist|
| Complex | Listener | Upgrade account |
| Complex | Artist | See my most popular song |
| Complex | Advertiser | To send ads to users who listen to a certain artist, because the artists’ message aligns with my company’s message. |

- [x] Relational model (20 pts)
* `User`(**id**, username, email, country, date_of_birth, profile_pic)
* `Paid_User`(<ins>**id**</ins>, credit_card)
* `Free_User`(<ins>**id**</ins>)
* `Playlist`(**id**, name, <ins>user.id</ins>)
* `Feature`(<ins>**playlist_id**</ins>, <ins>**track_id**</ins>)
* `Plays`(<ins>**user_id**</ins>, <ins>**track_id**</ins>, count)
* `Track`(**id**, title, duration, <ins>album_id</ins>)
* `Song`(**id**)
* `Podcast`(**id**)
* `Artist`(**id**, name)
* `Album`(**id**, name, genre, album_art, release_date, label)
* `Release`(<ins>**artist_id**</ins>, <ins>**album_id**</ins>)
* `Advertiser`(**id**, name)
* `Advertisement`(**id**, product, link, <ins>advertiser_id</ins>)
* `Views`(<ins>**user_id**</ins>, <ins>**advertisement_id**</ins>, watch_time)

Relations absorbed by entities:
* `Contains` (by `Album`)
* `Creates` (by `Playlist`)
* `Provided_By` (by `Advertisement`)

- [ ] Functional dependencies (20 pts)
* `User`
id-> username, email, country, date_of_birth, profile_pic
User(id, username, email, country, date_of_birth, profile_pic) is in BCNF
* `Free_User`
All functional dependencies are captured by the relation “User” so this entity is purely for identifying purposes.
Free_User(id) is in BCNF

* `Paid_User`
id -> credit_card
Paid_User(id, credit_card) is in BCNF

* `Playlist`
playlist id -> playlist name, user id
Playlist(playlist id, name, user id) is in BCNF

* `Feature`
No FDs
Feature(playlist id, track id) is in BCNF

* `Plays`
user id, track id -> count
Play(user id, track id, count) is in BCNF

* `Track`
track id -> title, duration, album id
Track(id, title, duration, album id) is in BCNF

* `Song`
All functional dependencies are captured by the relation “Track”.
Song(id) is in BCNF

* `Podcast`
All functional dependencies are captured by the relation “Track”.
Podcast(id) is in BCNF

* `Artist`
artist id -> name
Artist(artist id, name) is in BCNF

* `Album`
album id -> name, genre, album art, release_date, label
Album(album id, name, genre, album_art, release_date, label) is in BCNF

* `Release`
No FDs
Release(artist.id, album.id) is in BCNF

* `Advertiser`
advertiser id -> name
Advertiser(advertiser id, name) is in BCNF

* `Advertisement`
advertisement id -> product, link, advertiser id
Advertisement(advertisement id, product, link, advertiser.id) is in BCNF

* `Views`
user id, advertisement id -> watch time
Views(user id, advertisement id, watch_time) is in BCNF

- [ ] Normalization (20 pts)
* `User`
Email and id both uniquely determine the other fields, however, we picked id as the determinant since a user can change their email, but the id is generated by Spotify, meaning it should never need to be changed. This is an appealing property of a primary key and fits the business constraints.

* `Free_User`
Since there are no functional dependencies, this relation is vacuously normalized, and thus, is in BCNF.

* `Paid_User`
User id uniquely identifies credit card, as each user has their own credit card on file. It is possible to have the same credit card on two different accounts (e.g. parent paying for kids’ accounts), so credit card does not uniquely identify user.

* `Playlist`
Playlist id uniquely identifies both the name and the user who created the playlist.

* `Feature`
Since there are no functional dependencies, this relation is vacuously normalized, and thus, is in BCNF.

* `Plays`
Count is uniquely identified by the user’s id, as well as the track’s id. As a result, this relation is already normalized, and thus, is in BCNF.

* `Track`
Track ID uniquely identifies the title of the track, its duration, and the album id. Since there are no bad dependencies, this relation is normalized.

* `Song`
Since there are no dependencies, the relation is vacuously normalized, and thus, is in BCNF

* `Podcast`
Since there are no dependencies, the relation is vacuously normalized, and thus, is in BCNF

* `Artist`
Artist ID uniquely identifies the name of the artist. As a result, this relation is already normalized, and thus, is in BCNF.

* `Album`
An album id uniquely identifies the name of the album, its genre, the cover for the album (album_art), the release_date, and the label (if there is one).

* `Release`
Since there are no functional dependencies, this relation is vacuously normalized, and thus, is in BCNF.

* `Advertiser`
The advertiser id uniquely identifies the name of the advertiser. As a result, the relation is already normalized and thus, is in BCNF

* `Advertisement`
Advertisement id uniquely identifies the product, the link to the product, and the advertiser’s id. As a result, the relation is already normalized and thus, is in BCNF

* `Views`
User id and advertisement id uniquely determines the watch time of the user on the ad. As a result, the relation is already normalized and thus, is in BCNF

- [x] Physical model (50 pts)

- [ ] Queries (120 pts)
- [ ] Simple queries
- [ ] Complex queries