This is my TV Episode Guide database. Although I plan to populate it
with episode information about Star Trek, it can
serve to hold information about any series.
The first entity set is Studios. The only information of interest that
I wanted to keep was its name, address, and website URL.
Originally I had the name and address as my keys, but then I realized that in this era of multinational companies, an address can
be difficult to pin down, but every company has a website. Perhaps this will be the start of a new trend in database key design? Further, although many companies have multiple domain names (http://motorola.com or http://mot.com/), there is generally only one domain that they use in advertising or put on their Annual Report, so that is the domain that would serve as the key.
Next, there is the one to many Produces relationship between Studios and Series. A studio may produce many tv series.
The Series entity set is uniquely identified by Title and Creator, since
it is possible that two Series were created with the same
name, but very unlikely with the same creator (an all Star Trek series have different subtitles).
There is a many to many relationship “Starring” between Series and Actors,
since there can be many actors in a series, and an
actor can be in many different series.
There is a one to many relationship from Series to Characters and Episodes.
A series has many characters as well as many
episodes. Both are weak entity sets, because a character is uniquely defined by both his/her name AND associated series,
especially because multiples series can have the same character name. The same argument is true for making Episodes a weak
There is a many-to-many relationship between Episodes and Characters
because it is helpful to know which characters were
featured in a given episode, especially for an ensemble cast like Star Trek, and I can see this kind of
relationship as playing a part in some interesting queries later on.
There is a many to many relationship between Actors and Characters, since an actor can play multiple characters and a character can be played by multiple actors.
A note about the Episodes entity set: it was true for the data that I was modeling that every episode had a unique title. However, it is feasible that two episodes in the same series could have the same name. In that case, a key such as air date could work, or potentially the episode number if such information were present. However, simply using title as a key should work for 99% of the cases.
Finally, I decided to just use the name attribute as the key of
actors. Someone suggested that all actors register with the Actors Guild,
so that screen names are essentially unique. Since I don't foresee any
data conflicts in the real world data I will be modeling, and because of
this Screen Actors Guild restriction, I don't use an additional ID attribute.