Question

reate a 3NF entity relationship data model based on the scenario presented below Read the following paragraph and create a data model for the business under discussion. “My partner and I own an independent record store. Well, I guess record store is really the wrong term these days since we really don’t sell a lot of records anymore. We sell mostly CDs, but also some tapes. And yes, we do stock vinyl records, too. Regardless of format (that is, CD, tape, or record), we sell long-playing albums as well as singles. Every item we sell is priced, and we keep track of the number of each individual title in stock on a daily basis. We like to track sales by both title and recording artist. The sales information by title needs to be tallied weekly to send to external ratings services so they can compile their best-seller charts. We often get customers who ask us to find certain songs for them. For singles, this is fairly easy because the title of the single is the title of the song. Some singles have multiple songs on them, though. Albums, on the other hand, comprise multiple songs. We need the ability to search for titles by song to let customers know which titles (both singles and albums) contain the songs they are looking for.”

Answer 1

Create a main table named "Records" for all type of records and a another table named "Items" that has linked with main table.

Table Name: Records

Column:

id (Unique id of every record)

title (Title of the record)

format (CD,tape,Record),

releasedate (Release date of record)

price (Price of record)

numofitemsonrecord (Total no of items on record line a cd have multiple songs)

rating (Rating of record)

Record table linked with Items table with rid column(2nd NF)

Table Name: Items

Column:

id (Unique id of item)

rid (Record it, linked with record table)

name (Perticuler item name like song name)

raiting (Every item individual rating)

A song have multiple artist so table "ItemArtist" have item id and artist id (3 NF)

Table Name: ItemArtist

Column:

id (Unique id)

iid (Item id)

aid (Artist id)

Table Name: Artist

Column:

id

name

age

dob