# 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

* Wikipedia: Entity–relationship model
* Wikipedia: Cardinality (data modeling)

## Multimedia

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

## Activities
### GUI Design Tool

* Review the E-R diagram for the Northwind Database.
* 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.
** DBMS tools include Microsoft SQL Server Management Studio, MySQL Workbench, Oracle SQL Developer, Postgre GUI tools, and DBeaver.
** 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.
* A car dealership wants a database to track sales by customer, vehicle, and sales person.
* A college wants a database to track enrollment by student, course, and instructor.
* A company wants a database to track their organizational structure by department, employee, and location.
* A computer repair company wants a database to track repairs by customer, computer, and technician.
* A doctor's office wants a database to track patient visits by doctor, patient, and visit.
* A library wants a database to track books by title, author, and genre.
* A YouTube subscriber wants a database to track videos by subject, YouTuber, and playlist.
* 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.Wikipedia: Cardinality (data modeling)</ref>
* 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.

## See Also

* Wikibooks: Database Design/The Entity Relationship Data Model
* Wikibooks: Databases: Entity Relationships
* McFadyen: Relational Databases and Microsoft Access - Chapter 7 (Entity Relationship Modeling)
* SmartDraw: Entity Relationship Diagrams
* Watt: Database Design (2nd edition) - Chapter 8 (The Entity Relationship Data Model)
* Microsoft: Designing Diagrams
* Beginners Book: Entity Relationship Diagram – ER Diagram in DBMS
* Visual Paradigm: What is Entity Relationship Diagram?