Mastering Data Modeling Fundamentals

A detailed overview of what the course will cover, and how to follow along with each module to get the most out of the course.

Brief introduction to and overview of what you will learn in this section.

Why do we build data models rather than simply write SQL (or equivalent) code to create database objects? We are after a semantic, non-implementation-specific representation of our data as well as a graphical representation to enhance our understanding.

Basic terminology will be introduced as well as the primary object building blocks across various techniques: particularly entities, attributes, and relationships (high-level introduction only at this point).

We are focusing on entity-relationship modeling, but ER will be compared to semantic data modeling, UML, and other modeling approaches.

Database design is implementation-specific, while data modeling is multi-tiered, including physical-level database designs. The two closely related disciplines are explored, and the similarities and differences are explained.

We are focusing on transactional modeling using ER notation, but the same notation can be used for analytical data modeling (i.e., for dimensional modeling a data warehouse) - despite the same notation, the modeling rules are entirely different. My companion course "Introduction to Data Warehousing" covers analytical and dimensional data modeling.

Pull together key concepts from this section before moving on.

Entitles are our primary "objects" while attributes are the "fields" of those objects - these are key building blocks and will be described in detail, beginning with entities in this videos.

Our data model entities are fleshed out through attributes, and this video describes and shows how to add attributes to our entities in two different modeling notations.

A special type of attribute allows more than one possible value (e.g., phone number or e-mail address; we'll see how these are modeled and used, versus single-value attributes.

All ER models are built around relationships among the various entities. Relationships are introduced in this video at an elementary level, building a foundation for the subsequent expansion of complex relationship-oriented topics in future sections.

Most data modeling techniques support hierarchies, which are a natural part of representing our data (example: "Employee" as a parent but subtypes of "Office Worker" "Contractor" and "Manufacturing Worker" with some differences among the subtypes. How do data models represent and use hierarchies? We'll show examples and discuss the principle of "inheritance" within our data models.

Each attribute in a data model has, at a minimum, a constraint of a specific data type (and, if relevant, field size). Additional constraints can be added to limit data instances to specific values.

Entities in a data model can be classified as either "strong" or "weak," and both terms can be used in multiple ways. This video goes beyond the sometimes confusing "strong vs. weak entity" distinction and describes identification and existence dependencies, along with examples of each.

Real-world relationships among objects can be more complex than a single, simple binary relationship. Ternary relationships involving three entities, recursive relationships involving only one entity, and multiple relationships among the same two entities are described and illustrated through examples.

The concept of weak entities, optional relationships, etc. are explored using examples in classic entity-relationship notation.

The "crow's foot" model notation represents maximum and minimum cardinality differently than classic notation. Examples in "crow's foot" notation are presented, and differing notation representations are introduced to guide the viewer through possibly conflicting directions.

To understand some of the transformations that occur between the conceptual and logical/physical levels, we need to take a quick look at relational database normalization rules (e.g., to understand why "intersection entities" come into existence).

The discussion of forward-engineering a data model continues with additional content and examples.

Sometimes we begin with a database schema and want to see what that database looks like conceptually/graphically. We do so by reverse-engineering the database table definitions back into entities, attributes, and relationships and the accompanying constraints.

One way to build a data model is with a general purpose drawing and presentation program, such as Microsoft PowerPoint. There are advantages and disadvantages to this approach, as this video explains.

Special data modeling tools (i.e., ERwin and others) are more expensive than Microsoft Visio, but also provide advanced support for data modeling. Both approaches - general-purpose diagramming software, and special-purpose data modeling software - are explored.

Some final points to conclude this course.