If you had a user group, you would do this sort of brainstorming with them
after explaining what entities are. If you can't get a user group,
you can also interview users individually. Write down all the nouns
and verbs they use. The nouns will be candidate entities; the verb
can describe processes or scenarios for using the data. Here is an
example of a brainstorm I'd do for a music database.
Song, Words, History, Chords, Key, Instrument, Author.
Further analysis or questioning discovers that "which instruments I use for which song" is actually called an Arrangement--another candidate entitiy.
I can further analyze these entities and realize a Song has Words but they can be split into verses and choruses and that some song have alternate words and choruses, but I don’t care.
This is a key point. What you have to keep in mind when looking at your data needs is what do you have the will and the means to collect? I could keep the verses separated so I could print them out in any order or assign different authors to different sections of the song or keep track of alternate versions, but my current book of music I’ve been keeping has the words and if there are new words then the author is attached to those words so in my database his name will just fit in with the words. If I didn’t care about Authors at all, I could drop them. If I were interested in tracking how songs have changed through out history, then I would need to tie Author to Verse and then collect all the verses under a Song Title. Any way is valid. Design so you get the information you need in a way that’s easy to collect and manage.
I drop Words (and Chords, too).
We’ve already seen that History is applied to either a Song or an Author so
I won’t have that as a separate entity but will instead incorporate it as
an attribute under Song, Author (and Instrument, too).
The Key is what key I play and/or sing a song in. However, it isn’t
tied directly to the song because it will change depending on the instrument.
And sometimes the key doesn’t matter because I’m not singing and am just
playing on an instrument such as harmonica or tinwhistle which has its own
instrinsic key. But I realize that Key is actually an attribute of
Arrangment which goes between Instrument and Song.
Here is my list of entities so far:
Song w/ Name, History,
Author w/Name, History
Instrument w/Name, History
Arrangement w/Key
Entities are usually represented by boxes. The boxes hold the name of the entity and also show the major attributes.
Here is my first picture of the entities in my music database:
Next I draw the relationship lines and examine them for both Cardinality (one
or many) and Optionality (required or not).
Examine each relationship in both directions.
Song to Author:
A Song MAY have an Author but my database doesn't say it MUST have one.Author to Song:
A Song could have MORE THAN ONE Author
An Author MUST have at least one song--I'm not putting in Authors unless I put in Songs for him or herThere is no relationship between Author and Arrangement or Author and Instrument. If I wanted to track more information about Authors, I suppose I could make a link tracking what instruments they played but 1) I don't care and 2) If I did, I'd just make an entry in the Author's History column.
An Author could write MORE THAN ONE Song.
The relationship between Song and Instrument goes through Arrangement. An Arrangement is a specific way a song is performed with an Instrument.
Song to Arrangement
A Song MAY have an Arrangment but it isn't required.Arrangement to Song
A Song could have MORE THAN ONE arrangement (if I do it on different instruments or do version in deifferent styles: folk, swing, rock)
An Arrangement MUST be for a Song. It cannot exist without a songArrangement to Instrument
Here's an answer that could create a new entity. An Arrangement can include MORE THAN one song. That would be a Medley. Now I have to decide if I want to put Medley's in or not. If I do, there are multiple ways to model them. For now, I'll just say an arrangement is only for one song.
An Arrangement MUST include an Instrument. This wouldn't be the case if I did songs a capella.Instrument to Arrangement
An Arrangement could have MORE THAN ONE Instrument. Sometimes I get out my harmonica holder. I may also switch instruments in the middle of a song. A hammered dulcimer is easy to switch to even with a banjo strapped on.
An Instrument does not have to belong to any Arrangement. I can have instruments without using them for specific songs.
An Instrument can definitely belong to MORE THAN ONE Arrangement.
Here is the first pass at drawing relationships onto my E/RD (Entity/Relationship
Diagram). Next we will condense the 2 lines between relationships into
1.
Reading Relationship Lines
When you read a single relationship line, you read it both directions.
First start from one entity and look at whether the line is solid or dotted.
If the line entering (actually leaving) it is dotted, then that relationship is optional (read Each occurrence of Entity One MAY HAVE)
If the line entering the entity is solid, then that relationship is required (read Each occurrence of Entity One MUST HAVE)Now follow the line to the other end and look at whether the line is singly-ended or has one of those tree branches on it.
If the end is single, then the cardinality of the relationship is One-to-One (finish reading ONE AND ONLY ONE occurrence of Entity Two)
If the end is branched, then the cardinality of the relationship is Many (finish reading ONE OR MORE occurrences of Entity Two)
Here is the diagram redrawn with single relationship lines. Read each
from both ends and see if you match what was entered above.
An Arrangement must Shape One and Only One SongIn Oracle Designer, the reports always say "must be" or "may be" so I'd change the name to "shaping" in one direction and "shaped by" in the other.
An Arrangement must be Shaping One and Only One SongNaming relationships, like naming Entities, is an art. You can merely use Characterizing or Describing, but the more descriptive a name is, the more useful it is for organizing your thoughts. And that's what the design phase of anything is all about. Often I'll name a relationship by ignoring the Associative entity and looking at how the two Base Entities are related. Then I'll use some useless name for the relationship from the Associative Entity out.
A Song may be Shaped By One or More Arrangements
Here is what could be the final E/RD for my Music Database: