Access Relationship Help

[germ-X]

Hi, I am having trouble working with Access.
Here is my scenerio:

I am suppose to create an Entity Relationship Diagram for a specific problem.
The categories for creating an ERD are:
Song Title, Artist, Album, Genre, Duration, Location, and Tempo

I came up with a relation:

Artist -> Album -> Song -> Duration, Location, Tempo
Artist -> Genre

I tried to create this relationship with the Table Wizard; however, I do not
know exactly how to create this. How many tables do I need? Do I need to
all 7 tables, or do I need only 3 main tables (and put the others in
subcategories)?

 

[Maelinar]

Using these fields, I suspect your ERD should be a single box with all the
fields contained within - there's no reason apparent why these can't all be
used on the one table.

Beware that using Artist as the unique key will limit you to using Artist's
only once. That'll be a bit of a bugger when attempting to load in the
Carpenter's Greatest Hits, the Return of the Carpenters, the Carpenters 10"
singles, the Carpenters best of british charts, etc...

Mæl

 

[germ-X]

I still don't get it. I need to Normalize the tables, and after that, I have
to create queries. For example, after creating the ERD, I have to create
queries such as "the second most popular artist". What unique key should I
use so I can easily create the queries?
Thanks.

 

[Beetle]

IMO this information would not belong all in one table. It seems to me that
the key question is whether an Artist can belong to more than one genre.
Let's say for the sake of argument that an artist can only belong to one
genre. Then you would basically have the following scenario;

A genre can have many artists

An artist can have many albums

An album can have many songs

Each song has specific information related to it.

So you have a series of One-to-Many relationships starting with genre at the
top. Your tables might look something like the following;

tblGenre
******
GenreID (Primary Key)
GenreName

tblArtists
******
ArtistID (Primary Key)
GenreID (Foreign Key)
ArtistName
(other fields related to the artist)

tblAlbums
*******
AlbumID (PK)
ArtistID (FK)
AlbumName
ReleaseDate
NumberOfSales
(other fields related to the album)

tblSongs
*******
SongID (PK)
AlbumID (FK)
SongTitle
RecordingDate
WrittenBy
(other fields related to the song)

Then you could use queries to determine things like which artist sold the
most albums, which album was the top seller, etc. You may need to add some
other fields also. For example, if you needed to know which album had the
most sales in a specific time frame (say January 2006) then you would need to
add some date fields in order to track this (possibly even another table,
depending on how in depth your queries are going to be).

If an artist can belong to more than one genre, then you have a Many-to-Many
relationship between genre and artist, so you would need another table to
define that relatioship.

It's hard to tell you exactly how to proceed without knowing more about your
circumstances, but hopefully this will at least give you some food for
thought.

HTH

 

[germ-X]

Sorry, I am new to Microsoft Access, and I am still having problems =/
Actually, an Artist can belong to more than one genre. So would I have to
remove the GenreID(FK) out of tblArtist and include it in a newly created
tblAlbum (which is attached to tblGenre?

Like this?

tblGenre --- tblAlbum
|
tblArtist
|
tblSongs

Here is more information about my project:
Song
Artist
Album
Genre
Duration
MyRating(ranked from 1-5)
PlayCount
Location(3 locations)
Tempo
Mood

In this case...Duration, MyRating, PlayCount, Location, Tempo, and Mood
would be included in tblSongs (since they are related to the song)?

I am still not sure how to approach this problem.

Thanks in advance.

 

[Beetle]

That would be one way you could approach it. Your table design might look
something like this;

tblGenre
******
GenreID (PK)
GenreName

tblArtists
*******
ArtistID (PK)
ArtistName
(other fields related to artist)

tblAlbums
********
AlbumID (PK)
GenreID (FK)
ArtistID (FK)
AlbumName
(other fields related to Album)

tblSongs
*******
SongID (PK)
AlbumID (FK)
SongName
Duration
MyRating(ranked from 1-5)
PlayCount
Tempo
Mood

tblLocations
*********
LocationID (PK)
SongID(FK)
LocationName

I put Location in a separate table only because it SEEMS like the type of
info that would normally have a separate table. If you ever wanted to add
additional locations, or run a query to see what is in a particular location,
it would be a little easier if you had it in it's own table. If none of this
would apply to your "location" data, then maybe it could just be a field in
your Songs table.

Under this scenario, there would be no direct link between Genre and Artist
- they would only be related via particular Album. In other words, a Genre
doesn't have "artists" it only has albums. Likewise, an Artist doesn't have
"genres", only albums.

Again, it's really up to you to decide since it's your DB. If you have no
need for a more direct link between artist and genre, then this design (or
something similar) may work for you. Try to think of all the ways you may
want to view the info contained in your DB (via queries, reports, etc.), and
that should help to determine how you want things to be related.

HTH

 

[germ-X]

I think I know how I am going to approach it now...Thanks for your help! You
are the best!!