0.6 Entity Relationship Diagram Lab

1.Activate Visio 2019 on your virtual lab.

A Follow the instructions in this link to activate Visio 2019.

Activate Visio 2019

2.Download and open the beginning diagram.

A Rename the file by changing LastName to your last name.

3.Explore the existing diagram.

This diagram is called an Entity Relationship Diagram (ERD) and shows how different tables and data are related to one another. You will learn about how to create these diagrams yourself in Data Concepts but for now we want to be familiar with the different parts of the diagram and how to use Lucidchart.

A These diagrams are visual representations of tables, columns, and their relationships to one another. These parts are shown below.

Diagram showing five numbered parts of an Entity Relationship Diagram — diagramparts.png

This is an Entity node and what would be the equivalent of a table.
These are the Attributes of an Entity and what would be the columns of a table.
These are the data types of the the Attributes.
These signify the Primary Keys and Foreign Keys in the tables.
These connectors represent the relationships between the tables. We use Crow's Foot Notation to signify relationships such as 1 to 1, 1 to Many and Many to Many.

4.Add a new Entity to the diagram.

We would like a way to represent different Terms in this diagram so let's add an entity (table) for that.

A Select the Entity from the menu on the left.

Entity in the menu on the left — addentity.png

B Click and drag the Entity from the menu to the workspace. An entity "shell" should appear.

Entity shell in the workspace — entityshell.png

C Right click on the new Entity and click "Show attribute types". A new column in the entity will appear.

Show attribute types option — showattributetypes.png

D Click the Entity's Name and rename it to Term. This would be the name of our table as well.

E Click the first attribute name and rename it to TermId.

F Click the remaining attribute names and rename them to Code and Semester.

Code and Semester attributes — renamemorecolumns.png

5.Add an attribute to the Term entity

A Click and drag an attribute from the left menu to the Term entity.

Attribute in the left menu — clickonattribute.png

New attribute in the Term entity — addnewattributetoterm.png

B Click the new attribute and rename it to Year.

6.Give the attributes data types.

A Click twice on the data type for Code to select it and type nvarchar(20)

Nvarchar is the type and the (20) shows the limit of the length. Not all data types will have a limit.

When you click on the Entity, you can drag the yellow circle that lines up with the vertical blue line to change the size of the columns to make text fit in one line.

B Change the Semester attribute data type to varchar(30)

Semester data type — semesterdatatype.png

7.Make a relationship between the EnrollmentDetail and the Term.

A Add a new attribute to the EnrollmentDetail entity and name it TermId.

TermId added to EnrollmentDetail — addtermidfk.png

B Make the new TermId attribute a foreign key by right clicking on the name of the attribute and clicking on "Set Foreign Key"

Set Foreign Key option — setforeignkey.png

C Connect the two entities by clicking and dragging a Relationship item from the menu on the left onto the canvas.

You can also use 'Ctrl + 3' and click and drag the relationship line from TermId in the Term entity to TermId in the EnrollmentDetail entity.

1 Connect one point of the relationship line to TermId in the Term entity and the other point to TermId in the EnrollmentDetail entity.

2 Change the ends of the lines by right clicking on the relationship lines and changing the begin and the end symbols. The symbol pointing to TermId in Term should be "One and only one" (two vertical lines), and the symbol pointing to TermId in EnrollmentDetail should be "Zero or more" (a crow's foot with a circle).

The two vertical lines stand for "one and only one" so we can read the relationship as "An enrollment detail can have one and only one term ID". The crow's stand for "many" so we can read the relationship as "term identities can be used by many enrollment details".

3 Double click the relationship line and type in "1 to M" on the label.

EnrollmentDetail and Term relationship — enrollmenttermrelationship.png

8.Save and export the diagram.

A Save the diagram by clicking on the save icon in the top left corner or using "Ctrl + s"

B To export the diagram as a PDF, click on File ----> Export ----> Create PDF/XPS

Create PDF/XPS export screen — export.png

C Keep the file name the same and click on publish.

D Upload the PDF to Canvas