Unsure if my ER Diagram to relational model is correct

Question

Ok, I'm brand new to SQL etc so apologies if this is totally wrong..

I have designed an ER Model which I feel is right and I am trying to convert it to relational model and want any advice as to where I am gone wrong on converting it or any tips. Racking my brain.

As I believe it..

1-1 relationships entities are either combined or the primary key of one entity type is placed as a foreign key in the other relation.

1-m relationships The primary key from the `one side' is placed as a foreign key in the many side.

m-n relationships A new relation is created with the primary keys from each entity forming a composite key.

multivalue attributes a new table is created, primary key used from 1st table and attribute used in the second table alognside primary key.

So here's my go at Relational model, PK in bold, FK in italics

USER: USERID FNAME LNAME USERNAME PASSWORD USERTYPE EMAIL

CUSTOMER: USERID, CUST_ID, BIO

ADMIN: USERID ADMIN_ID

ARTIST USERID, ARTIST_ID, BIO REC_ID

PRODUCER: PROD_ID, Name, Email

RECORD LABEL: RECORD_ID , NAME, DESCRIPTION

ALBUM: ALBUMID NAME , COST, TITLE, NOOFSONGS

TRACK: TRACK ID, NAME, COST, TITLE, DESCRIPTION

TRACK REVIEW: DEPENDENT on TRACK SO TRACK ID comes into this table = REVIEW_ID(PK), TRK_ID(PK) NAME

TRACK PURCHASE TABLE (USER id comes into this table as foreign key) TrackPuchaseID user_id, date

ALBUM PURCHASE TABLE AlbumPuchaseID user_id, date, quantity

GENRE TABLE?: Not sure??

BPM: is mutli value atribute so becoems seperate table so it.s GenreID BPM

I know this all might be wrong. but any help would be great.. with explanation which should be FK or composite PK etc or what tables I am missing..

Answer 1

• On album purchase you should have: user_id, date, album_id.
• At track purchase I don't see why you care about the quantity and you're missing the track_id.
• review, track, album, user should all include a date. Might be as well interesting to add an last_update_date to the artist and customer.
• review should include an user_id.

I guess there's other thing left. You will need an Invoice / Invoice_Purchase table to be able to say "customer xyz bought the items 1,2,3,4...", it should be like:

Table Invoice

• invoice_id
• user_id
• date
• status

Table Invoice_Purchase

• invoice_id // Id of the invoice
• purchase_type // the type of the purchase (for eg. 0 means track and 1 means album)

Maybe you should also add a status to your albuns and tracks, this way you can set if a item can be bought or not. And yes, you shouldn't delete them because you'll have the old invoices relaying on it...

Anyways, you should consider later using a software like Navicat do draw the relation part and deploy your DB.