Database Management/E-R Diagrams

This lesson introduces entity-relationship diagrams.

Objectives and Skills
Objectives and skills for this lesson include:
 * Understand entity-relationship diagram concepts.
 * Create entity-relationship diagrams to support a logical database design.

Readings

 * 1)  Entity–relationship model
 * 2)  Cardinality (data modeling)

Multimedia

 * 1) YouTube: Entity Relationship Diagram (ERD) Tutorial - Part 1
 * 2) YouTube: Entity Relationship Diagram (ERD) Tutorial - Part 2
 * 3) YouTube: Entity Relationship Diagram (ERD) Training Video
 * 4) YouTube: Manage and Create Diagrams of Your SQL Database for Free with DBeaver
 * 5) YouTube: How to Make and Use an ERD/EER Diagram in MYSQL Workbench
 * 6) YouTube: How to Create a Database Diagram Using SQL Server Management Studio

GUI Design Tool

 * 1) Review the E-R diagram for the Northwind Database.
 * 2) Select a graphical database design tool to use for this lesson. The GUI design tool you select should create something similar to the Northwind E-R diagram layout. Be sure to select a database diagramming tool, not a relationship modeling tool.
 * 3) * DBMS tools include Microsoft SQL Server Management Studio, MySQL Workbench, Oracle SQL Developer, Postgre GUI tools, and DBeaver.
 * 4) * Stand-alone graphical design tools include Creately, LibreOffice Draw, LucidChart, Microsoft Visio, and Visual Paradigm. Typical drawing programs (Microsoft Paint or similar) are not effective tools for this activity. Select a design tool rather than a drawing program.

E-R Diagram
Create a fully normalized E-R diagram for one or more of the following datasets.
 * 1) A car dealership wants a database to track sales by customer, vehicle, and sales person.
 * 2) A college wants a database to track enrollment by student, course, and instructor.
 * 3) A company wants a database to track their organizational structure by department, employee, and location.
 * 4) A computer repair company wants a database to track repairs by customer, computer, and technician.
 * 5) A doctor's office wants a database to track patient visits by doctor, patient, and visit.
 * 6) A library wants a database to track books by title, author, and genre.
 * 7) A YouTube subscriber wants a database to track videos by subject, YouTuber, and playlist.
 * 8) Choose your own dataset similar to the above with at least three related entities.

Lesson Summary

 * An entity–relationship model (or ER model) describes interrelated things of interest in a specific domain of knowledge.
 * A basic ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between entities (instances of those entity types).
 * An entity may be defined as a thing capable of an independent existence that can be uniquely identified. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem, etc.
 * A relationship captures how entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns. Examples: an owns relationship between a company and a computer, a supervises relationship between an employee and a department, a performs relationship between an artist and a song, a proves relationship between a mathematician and a conjecture, etc.
 * Within data modeling, the cardinality of a join between two tables is the numerical relationship between rows of one table and rows in the other. Common cardinalities include one-to-one, one-to-many, and many-to-many.
 * Crow's foot diagrams represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship.
 * A Crow's foot shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship. Cardinality (data modeling)
 * With a Crow's foot diagram, three symbols are used to represent cardinality:
 * a ring represents "zero"
 * a dash represents "one"
 * a crow's foot represents "many" or "infinite"
 * These symbols are used in pairs to represent the four types of cardinality that an entity may have in a relationship. The inner component of the notation represents the minimum, and the outer component represents the maximum.
 * ring and dash → minimum zero, maximum one (optional)
 * dash and dash → minimum one, maximum one (mandatory)
 * ring and crow's foot → minimum zero, maximum many (optional)
 * dash and crow's foot → minimum one, maximum many (mandatory)

Key Terms

 * cardinality
 * The numerical relationship between rows of one table and rows in the other.


 * entity
 * A thing capable of an independent existence that can be uniquely identified.


 * relationship
 * Describes how entities are related to one another.