Resolving the Many-to-Many Relationship
When we have a many-to-many relationship as we do between Movies and Actors, it must be resolved using an associative entity before we can build a database. We’ll do that now. Right-click on the line for the many-to-many relationship and select Cut to remove it. Drag the Actors entity over to make room for an associative entity. Drag a new Entity shape onto the diagram and name it Appearances. Add new relationship lines between Actors and Appearances and between Movies and Appearances. Label the lines to say that Actors “make” Appearances and Movies “feature” appearances. According to our business rules, each Movie features one or more Appearances by an Actor, so set the Beginning symbol for that relationship line to 1 or More. Each Actor can make zero or more Appearances in a Movie, so that relationship line doesn’t need to change. We have now resolved the many-to-many relationship.
Adding Attributes, Data Types, Keys, and Normalization
We will now fill in the remaining details of our logical data model diagram. Each Entity shape already includes inner shapes for some attributes, including a primary key. Click the attribute name labeled PK in the Directors entity and type the primary key name for this entity; we’ll use DirectorID. Click and type to name the other attributes, such as DirectorLastName and DirectorFirstName. If you need to add more attributes in an entity, the easiest way is to right-click on an existing attribute and select either Insert Attribute Before or Insert Attribute After. We’ll do this to add a DirectorBirthYear.
Repeat this process to name primary keys and other attributes in the other entities.
In the Movies entity we need to add a foreign key to implement the relationship. Recall that in a one-to-many relationship, as this one is, the primary key from the “one” side becomes a foreign key on the “many” side. So we’ll copy the primary key from Directors, DirectorID, as a foreign key in Movies. Change the attribute name in Movies to DirectorID, click away to de-select, then, right-click on this attribute and choose Set Foreign Key.
For the associative entity Appearances, we need two foreign keys, one from Movies and one from Actors. Each of these will also be part of a composite primary key. Name the first two attributes in Appearances ActorID and MovieID. Right-click on ActorID and set it as a foreign key in addition to being a primary key. Right-click on MovieID and set it as also part of the primary key, then right-click again and set it as a foreign key also. Finally, remove the extra attribute in Appearances that we don’t need by right-clicking and selecting Cut.
To add data types, right-click on the name heading at the top of an entity and select Show Attribute Types. By default all types are set to int. Click on any type and enter a new type such as “text.” Repeat this process for the other entities.
Rearrange the entities as needed to neaten up the page. Review your diagram to ensure your database design is normalized, that is, that it complies with the guidelines for at least first normal, second normal, and third normal form. Finally, don’t forget to save your complete diagram. Visio 2013, like other Office 2013 applications, lets you save to multiple locations. You will probably want to save to your own computer. Select Computer and click Browse to navigate to where you want to save your diagram, and to name your file.
You’ve now completed an Entity Relationship Diagram in Microsoft Visio 2013. Good luck with all your database designs!
[End of Audio]