The Entity-Relationship Model: Diagrams Made Simple, Explained

UPI Study › Courses › System Analysis and Design › Free lessons › Lesson 6.4 Free with a free account

The Entity-Relationship Model: Diagrams Made Simple

System Analysis and Design · Chapter 6: Database Design, Normalization, and Business IntelligenceSection 6.4 · lesson 42 of 698 min · free with a free account

← Approaches to Designing Database SchemasNext lesson: Normalization in Databases: Types and Practical Illustrations →

Earn 3 college credits for System Analysis and DesignRecommended for credit by NCCRS · Computer Science 390 · self-paced · one payment, lifetime access
Enroll in System Analysis and Design · $250 →Download the syllabus (PDF)

In this lesson

Entities, attributes, relationships, two notations, and mapping to tables.

  • Define an entity and the types of attribute it can have
  • Explain relationships, cardinality, and participation
  • Explain weak entities, and compare Chen and Crow's Foot notation
  • Map an entity-relationship diagram to a set of tables

Transcript

Everything said in the video, section by section (1,145 words). Read along, or search it.

Database Design, Normalization, and Business Intelligence

The Entity-Relationship Model

Before tables are designed, the data is modeled. And the standard tool for that is the entity-relationship diagram. This section covers entities and their attributes, relationships and how to describe them, and weak entities. Then two notations for drawing all of this, Chen and Crow's Foot. And finally, a worked example, and the rules for turning a diagram into real database tables.

Entities

Things the Database Tracks

An entity-relationship diagram, or E R D, models a database around entities. An entity is a distinct real-world thing the database needs to store data about, such as a customer, or a product. The idea of type and instance works just like class and object from earlier in the course. Customer is the entity type. And one particular customer is a specific instance of it.

Describing Entities

6 Types of Attribute

Each entity is described by attributes. And not all attributes work the same way. A simple attribute cannot be meaningfully divided into smaller parts. G P A, for example. A composite attribute can be broken into smaller, meaningful parts. An address has a street, city, state, and zip. A single-valued attribute holds exactly one value for each instance, like a date of birth. A multi-valued attribute can hold more than one value, like a set of phone numbers. A derived attribute is calculated from another attribute, rather than stored. Age can be derived from the date of birth. And a key attribute uniquely identifies each instance, like a student I D. These distinctions matter later, when the diagram becomes tables. A multi-valued attribute, for instance, cannot simply become one column.

Relationships

How Entities Associate

A relationship describes how two entities are associated. It is usually named with a verb phrase. A customer places an order. An instructor teaches a course. A relationship can even connect an entity to itself, as when one employee manages another employee. Cardinality describes how many instances of one entity can relate to instances of the other. In one-to-one, one instance of A relates to at most one of B. One employee has one employee badge. In one-to-many, one instance of A relates to many of B. One customer places many orders. And in many-to-many, many of A relate to many of B. Many students enroll in many courses.

Participation

Required or Optional?

Participation adds one more detail. Is an entity's involvement required? Mandatory, or total, participation means every instance must participate. An order must have a customer. An order with no customer makes no sense. Optional, or partial, participation means an instance may or may not participate. A customer might not have placed any order yet, and that is perfectly valid. Cardinality tells you how many. Participation tells you whether at all. Together, they describe a relationship fully.

Weak Entities

Identity Borrowed From an Owner

Most entities can be identified using their own key alone. A weak entity cannot. It depends on a related owner entity for its identity, and only makes sense in the context of that owner. Take an order line. A line item has no identity of its own, separate from the order it belongs to. It is usually identified by its owning order, plus a partial key, such as a line number. Line three means nothing on its own. Line three of order one hundred means something. The relationship connecting a weak entity to its owner is called an identifying relationship.

2 Notations

Chen vs. Crow's Foot

The same concepts can be drawn in different notations. Two are especially common. In both, an entity is drawn as a rectangle. The difference is in how relationships appear. Chen notation gives the relationship its own shape, a diamond, labeled with the verb phrase, such as places. Cardinality is written as a label beside the line. One on the customer side, and M, for many, on the order side. Crow's Foot notation has no separate shape. The line itself carries the relationship name. Cardinality is drawn right onto the ends of the line. A single tick across the line means one. A three-pronged fork, the crow's foot, means many. Both notations can say exactly the same thing. One customer places many orders. Crow's Foot has become more common in modern modeling tools. But Chen notation is still widely taught, because its diamond makes the relationship itself easy to see.

Figure 6.2

Worked ERD: Order Processing

Here is a worked E R D, in Crow's Foot notation, for a small order-processing database. Read each line by its ends. Between customer and order, there is a tick at customer, and a fork at order. So one customer places many orders. Each order consists of many order lines. And each product can appear in many order lines. Order line has a double border, marking it as a weak entity. A line item means nothing apart from its order. Now notice something clever. Order and product have a many-to-many relationship. An order holds many products, and a product appears on many orders. An E R D cannot represent many-to-many directly, so the relationship is routed through order line. Order line is exactly the entity that resolves it.

From Diagram to Database

Mapping an ERD to Tables

Once an E R D is complete, it can be translated into actual tables with a few consistent rules. Each entity becomes a table. Customer, order, order line, and product each become their own table. Each attribute becomes a column. An order's date and status become columns in the order table. The key attribute becomes the primary key. Customer I D becomes the primary key of the customer table. A one-to-many relationship posts the one side's key onto the many side, as a foreign key. So customer I D is added as a foreign key column in the order table. A many-to-many relationship becomes its own junction table. Order line resolves order and product, by holding both an order I D and a product I D. And a weak entity's primary key includes its owner's key. Order line's primary key is the combination of order I D, inherited from order, and a line number.

Key Terms

The Vocabulary of This Section

Let's lock in the key terms. An entity is a real-world thing the database tracks. An attribute is a property describing it. Cardinality is how many instances relate. One-to-one, one-to-many, or many-to-many. Participation is whether involvement in a relationship is mandatory or optional. A weak entity is identified only through an owner, via an identifying relationship. Chen notation uses a labeled diamond, with one and M. Crow's Foot uses a tick and a fork on the line. And a junction table is the table that resolves a many-to-many relationship.

Key Takeaways

What to Carry Forward

To pull this together. An entity's attributes can be simple, composite, single-valued, multi-valued, derived, or a key. A relationship is described by its cardinality, and by participation, mandatory or optional. A weak entity depends on an owner entity, connected through an identifying relationship. Chen notation uses a labeled diamond for each relationship. Crow's Foot draws cardinality directly on the line. And when mapping to tables, entities become tables, keys become primary keys, one-to-many relationships use foreign keys, and many-to-many relationships use junction tables.

About System Analysis and Design at UPI Study

System Analysis and Design (Computer Science 390) is a self-paced online course of 69 video lessons in 9 chapters, from the analyst's role and requirements to UML modeling, interface and database design, testing, maintenance and project management. It carries a NCCRS credit recommendation for 3 semester credits.

Every lesson transcript is free on pages like this one, and the videos are free with a free UPI Study account. Enrolling adds a practice quiz after each lesson, the graded work and final exam that earn the credit, lifetime access, and an official transcript on completion. UPI Study has helped over 130,000 students with college credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Enroll now → Course details Download the syllabus All Computer Science courses Every course with PRO →

Compare: Software Engineering vs Systems Analysis

Why take it here

Why students take System Analysis and Design with UPI Study

From business problem to working systemSystem Analysis and Design teaches how analysts study a business need and turn it into a system design: requirements, UML and data-flow models, interface and database design, implementation and project management. It is a core course in information systems and IT degrees.
Credit at a fraction of the priceThe same 3 credits can cost $750 or more at a US college. Here it is one payment, self-paced, with lifetime access.
Finish your degree soonerTransfer credits reduce the courses you still owe, so you can graduate earlier or lighten a heavy semester.
Strengthen an applicationA completed college-level course on an official transcript shows admissions and scholarship committees you can do the work.
Study on your own scheduleNo start dates, no deadlines. Watch the lessons, practice, and take the final when you are ready.
130,000+students helped with college creditsACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Where the credit goes

Universities that work with NCCRS

System Analysis and Design carries a NCCRS credit recommendation. These universities work with NCCRS; some are UPI Study's signed partners. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Thomas Edison State UniversityThomas Edison State UniversitySigned partnerUniversity of the PeopleUniversity of the PeopleSigned partnerCharter Oak State CollegeCharter Oak State CollegeSigned partnerStrayer UniversityStrayer UniversitySigned partnerColumbia Southern UniversityColumbia Southern UniversitySigned partnerIndiana TechIndiana TechSigned partnerPenn StatePenn StateWorks with NCCRSSouthern New Hampshire UniversitySouthern New Hampshire UniversityWorks with NCCRSSUNYSUNYWorks with NCCRS +many more cooperating universitiesFind my college →

Find my college → Not listed? Ask Athena in the corner of this page, or book a 15-minute call.

FAQ

Questions students ask us

About The Entity-Relationship Model: Diagrams Made Simple

What are the different types of attributes in an ER diagram?
Attributes in an ER diagram can be simple, composite, single-valued, multi-valued, derived, or key attributes. A simple attribute, like GPA, cannot be meaningfully divided, while a composite attribute, like an address, breaks into street, city, state, and zip. A multi-valued attribute, such as several phone numbers, holds more than one value. A derived attribute, like age, is calculated from another, and a key attribute uniquely identifies each instance.
What is the difference between cardinality and participation in an ER diagram?
Cardinality states how many instances of one entity can relate to another, while participation states whether involvement in the relationship is required at all. Cardinality can be one-to-one, one-to-many, or many-to-many, such as one customer placing many orders. Participation is mandatory (total) when every instance must take part, as every order needs a customer, or optional (partial), as a customer may not have ordered yet.
What is a weak entity in an entity-relationship diagram?
A weak entity is one that cannot be identified by its own attributes alone and depends on a related owner entity for its identity. An order line is a common example: line three means nothing alone, but line three of order 100 does. It is identified by the owner's key plus a partial key, such as a line number. The relationship linking a weak entity to its owner is called an identifying relationship.
How do you convert an ER diagram into database tables?
To convert an ER diagram into tables, make each entity a table, each attribute a column, and each key attribute a primary key. For a one-to-many relationship, add the one side's key to the many side as a foreign key, such as customer ID in the order table. A many-to-many relationship becomes a junction table holding both keys, and a weak entity's primary key includes its owner's key.

About the System Analysis and Design course

How many college credits is System Analysis and Design?
System Analysis and Design (Computer Science 390) carries a NCCRS credit recommendation for 3 semester credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.
How much does System Analysis and Design cost and what is included?
$250, paid once, with lifetime access: all 69 video lessons, a practice quiz after each, the graded work and final exam, and an official transcript when you finish. Students who want several courses choose UPI Study PRO instead.
Which universities accept UPI Study credits?
UPI Study has signed partner agreements with universities such as Thomas Edison State University, University of the People and Charter Oak State College, and works with universities such as Penn State, SNHU and SUNY. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities. Use "Find my college" on this page to check yours.
How long does System Analysis and Design take?
It is self-paced with no deadlines. The 69 lessons total about 9 hours of video; most students add reading and practice time and finish in a few weeks to a few months, on their own schedule.
How are the exams done?
Quizzes and assignments are online in the course. The final exam is taken online with proctoring; a person on the UPI Study team checks assignments, usually within two days.
How do my credits reach my college?
When the course shows 100% complete, request the official transcript from your dashboard. It is sent to the college you name, and you can send it to more than one. Check your college first with "Find my college" on this page, or ask Athena.

Ready for the full course? Earn 3 credits in System Analysis and Design.

69 video lessons, a quiz after each one, and the graded work that earns the credit. Recommended for credit by NCCRS.

Enroll in System Analysis and Design →Download the syllabusEvery course with PRO →
System Analysis and Design
3 credits · NCCRSEnroll →