Jump to content

Database Management/E-R Diagrams

Add links
From Wikiversity
Database Management
  1. Introduction
  2. Query by Example
  3. SQL
  4. Advanced SQL
  5. Data Definition Language
  6. Data Control Language
  7. Normalization
  8. E-R Diagrams
  9. Database Design
  10. Database Functions
  11. Database Administration
  12. NoSQL
  13. ODBC

This lesson introduces entity-relationship diagrams.

Objectives and Skills

[edit | edit source]

Objectives and skills for this lesson include:

  • Understand entity-relationship diagram concepts.
  • Create entity-relationship diagrams to support a logical database design.

Readings

[edit | edit source]
  1. Wikipedia: Entity–relationship model
  2. Wikipedia: Cardinality (data modeling)

Multimedia

[edit | edit source]
  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

Activities

[edit | edit source]

GUI Design Tool

[edit | edit source]
  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.
    • 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

[edit | edit source]

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

[edit | edit source]
  • An entity–relationship model (or ER model) describes interrelated things of interest in a specific domain of knowledge.[1]
  • 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).[2]
  • 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.[3]
  • 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.[4]
  • 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.[5]
  • 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.[6]
  • 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:[7]
    • 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.[8]
    • 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

[edit | edit source]
cardinality
The numerical relationship between rows of one table and rows in the other.[9]
entity
A thing capable of an independent existence that can be uniquely identified.[10]
relationship
Describes how entities are related to one another.[11]

See Also

[edit | edit source]

References

[edit | edit source]
  1. ↑ Wikipedia: Entity-relationship model
  2. ↑ Wikipedia: Entity-relationship model
  3. ↑ Wikipedia: Entity-relationship model
  4. ↑ Wikipedia: Entity-relationship model
  5. ↑ Wikipedia: Cardinality (data modeling)
  6. ↑ Wikipedia: Entity-relationship model
  7. ↑ Wikipedia: Entity-relationship model
  8. ↑ Wikipedia: Entity-relationship model
  9. ↑ Wikipedia: Cardinality (data modeling)
  10. ↑ Wikipedia: Entity-relationship model
  11. ↑ Wikipedia: Entity-relationship model
Retrieved from "https://en.wikiversity.org/w/index.php?title=Database_Management/E-R_Diagrams&oldid=2381734"