I have seen while reading Wikipedia the following DB relationship defined between an artist and a song:
http://en.wikipedia.org/wiki/File:ERD-artist-performs-song.svg
From Source: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model
And the text below it states the following:
Two related entities shown using Crow's Foot notation. In this example, an optional relationship is shown between Artist and Song; the symbols closest to the song entity represents "zero, one, or many", whereas a song has "one and only one" Artist. The former is therefore read as, an Artist (can) perform(s) "zero, one, or many" song(s)
Now I have some questions about this relationship as the defined relationship seems unrealistic to me:
1) In real life a song may have 1 to many artists that may perform or even write the song so in my opinion the correct relationship should should show "-IE" from song to artist instead of "-II"
2) How could an artist perform 0 songs "-0E" - that entity should no longer be classified as an artist by the dictionary definition of the entity name if this holds true, correct? (please do not leave comments about having seen people who call themselves artists and cannot perform songs - which I would have to agree with) According to the shown DB relationship the Artist classification is bestowed upon an entity which cannot perform a song so when designing ERDs should the dictionary definition of the entity also dictate the entity relationship? If so is there a particular name for this type of planning in DB relationship modeling to take into consideration the definition of the name given to the entity?
3) In the ERD the "performs" verb is being used to show the relationship action, looking at other ERDs I have seen no place where a verb is also defined for the relationship when the diagram is represented visually. Is it necessary to define and show this verb in ERDs? The reason I am asking is because in the above case the "performs" verb perhaps was a poor choice if instead the "creates" verb was chosen then the relationship could have been correct that the artist may create 0 to * songs but because of the verb picked instead for the relationship, as I mentioned above, it breaks the logical connection that the statement is trying to portray and hence making it an invalid relationship. Based on this should the verbs chosen not be mandatory in DB relationship diagrams?
Question #1
In the real world, we do generally consider a single song to be performable by many different artists. For example, how many times have The Beatles' songs been performed in different ways by different artists.
However in the world of data, it would very likely be considered a separate song entity altogether, as that would allow the ability to record all the differences made in the new version of the song, such as different arrangements, etc. If you modeled your system as you had suggested, there would be no way (or perhaps no easy way) to capture any of that information.
That is my reasonable assumption for why the diagram models the relationship in this manner. There would technically be nothing wrong with modeling the Artist-Song relationship as a many to many, as you suggested, but that depends on the nature and requirements for the system you're building.
Question #2
The cardinality (optional, mandatory, one, many, etc) indicates under what conditions the entity / relationship can exist. In plain english, this diagram should be read more like "An artist can exist, but not have any related songs. A song cannot exist unless it is related to an artist". I'm not sure whether I've misunderstood what you mean by "dictionary definition" and "cannot", so if I have not addressed this point properly, let me know. I can certainly see your confusion based on the verb usage, but remember that its about existence, rather than ability to perform an action.
Question #3
The verbs themselves help clarify the relationship. It makes it easier to understand the components of the system if they can be read in plain language. It also helps you understand the nature of the system itself. That is, this DB is probably meant to record artists and their song performances, not necessarily just a catalog of songs and artists.
In regards to omitting the verbs, I suppose in some cases the author feels it is obvious, or adding the verbs might clutter up the diagram, etc. Basically, if you're solely interested in knowing how to implement the relationship in your database, all you really need to know is the type of relationship (one to many, etc), and the constraints (mandatory, optional, etc). The verb is sugar, to a degree.
In the case of this diagram, even though it does not seem to be modeled this way, lets say this database also recorded who wrote the song. In that way, there might be another relationship from an entity called "Lyricist" with a verb "writes". The person who created / wrote the song may not be the same as the person who performed the song. Now, admittedly we're getting into very deep details, as we could talk about a song being a composite of a number of different entities (lyrics, instruments, arrangement, etc). But that would be beyond the scope of the original diagram.
Let me know whether that clarifies things.
