Data Modeling

Data Modeling

• Data Modeling

• Keys

• Normalization

Data Modeling

Data modeling is the process of describing how data is organized, connected, and constrained before building a database.

Welcome students and frame data modeling as the planning step before implementation. Emphasize that a good model reduces confusion, duplication, and future redesign.

Why Data Modeling Matters

A data model helps teams agree on:

• What data must be stored
• How different concepts relate
• Which rules protect data quality
• How the database supports real questions

Ask students to imagine building a database without a plan. Use examples like duplicate customer records, missing orders, or unclear relationships between students and courses.

From Real World to Database

Data modeling translates real-world objects and processes into database structures.

flowchart LR
  A[Real-world process] --> B[Conceptual model]
  B --> C[Logical model]
  C --> D[Physical database]

  classDef blue fill:#0072B2,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef orange fill:#E69F00,color:#000000,stroke:#111111,stroke-width:2px
  classDef green fill:#009E73,color:#000000,stroke:#111111,stroke-width:2px
  classDef purple fill:#CC79A7,color:#000000,stroke:#111111,stroke-width:2px

  class A blue
  class B orange
  class C green
  class D purple

Explain that models become more detailed over time. Conceptual models focus on ideas, logical models focus on structure, and physical models focus on implementation details.

Three Levels of Data Models

Level	Main question	Typical output
Conceptual	What does the organization need to track?	Entities and relationships
Logical	How should the data be structured?	Tables, columns, keys, and constraints
Physical	How will the DBMS store and access it?	Data types, indexes, and storage choices

Use this slide to separate planning from implementation. Students often jump straight to tables, but conceptual thinking helps them understand the domain first.

Entity-Relationship Diagrams

An Entity-Relationship diagram, or ER diagram, visually represents:

• Entities
• Attributes
• Relationships
• Cardinality rules

Define ER diagrams as a communication tool. They help non-programmers and technical users discuss the structure of a database before creating tables.

Entities

An entity is a thing about which we want to store data.

Examples:

• Student
• Course
• Instructor
• Enrollment
• Product
• Order

Emphasize that entities often become tables in a relational database. Ask students to name entities from a familiar system such as a library, store, or university.

Attributes

An attribute is a fact or property stored about an entity.

For a Student entity, attributes might include:

• student_id
• first_name
• last_name
• email
• major

Point out that attributes often become columns. Encourage students to distinguish attributes from entities. For example, a student’s name is usually an attribute, while student is the entity.

Activity: Identify Entities and Attributes

Scenario: A college wants to track students, courses, instructors, departments, and course enrollments.

Time: 8 minutes
Deliverable: A labeled list containing:

• At least 3 entities
• At least 2 attributes per entity

Give students 8 to 10 minutes. Walk around and check whether they are mixing up entities and attributes. Bring the class back by asking for one example entity and its attributes.

Relationships

A relationship describes how entities are connected.

Examples:

• A student enrolls in a course
• An instructor teaches a course
• A department offers many courses
• An order contains products

Explain that relationships are usually verbs or actions connecting nouns. Encourage students to write relationships as short sentences.

Cardinality

Cardinality describes how many records can participate in a relationship.

Common types:

• One-to-one
• One-to-many
• Many-to-many

Introduce cardinality as the rules of participation. Use practical wording: one student can enroll in many courses; one course can have many students.

One-to-One Relationship

A one-to-one relationship means one record in Entity A is associated with one record in Entity B.

erDiagram
  PERSON ||--|| PASSPORT : has
  PERSON {
    int person_id
    string full_name
  }
  PASSPORT {
    int passport_id
    string passport_number
  }

Explain that one-to-one relationships are less common than one-to-many. Ask students why person and passport might be separated, such as security or optional details.

One-to-Many Relationship

A one-to-many relationship means one record in Entity A can be associated with many records in Entity B.

erDiagram
  DEPARTMENT ||--o{ COURSE : offers
  DEPARTMENT {
    int department_id
    string department_name
  }
  COURSE {
    int course_id
    string course_name
  }

State that one-to-many is very common in relational databases. The many side often contains a foreign key pointing back to the one side.

Many-to-Many Relationship

A many-to-many relationship means many records in Entity A can connect to many records in Entity B.

Example:

• Many students can enroll in many courses
• Many products can appear in many orders

Explain that many-to-many relationships cannot usually be implemented directly as two tables in a relational database. They need an associative table.

Activity: Draw Cardinality

For each statement, decide the cardinality:

1. A department has many instructors.
2. A student has one university ID card.
3. A student enrolls in many courses, and a course has many students.
4. A customer places many orders.

Then sketch a simple ER diagram for each. Label each relationship with a verb.

Give students 10 minutes. Encourage them to label relationships clearly. Afterward, review each statement and ask students how they knew the cardinality.

Resolving Many-to-Many Relationships

Many-to-many relationships are usually resolved with a bridge table, also called an associative table.

flowchart LR
  S[Student] --> E[Enrollment]
  E --> C[Course]

  classDef blue fill:#0072B2,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef orange fill:#E69F00,color:#000000,stroke:#111111,stroke-width:2px
  classDef green fill:#009E73,color:#000000,stroke:#111111,stroke-width:2px

  class S blue
  class E orange
  class C green

Explain that Enrollment stores the connection between a student and a course. It can also store relationship-specific attributes such as enrollment date or grade.

Example ER Diagram: Course Enrollment

erDiagram
  DEPARTMENT ||--o{ COURSE : offers
  INSTRUCTOR ||--o{ COURSE : teaches
  STUDENT ||--o{ ENROLLMENT : completes
  COURSE ||--o{ ENROLLMENT : includes

  DEPARTMENT {
    int department_id
    string department_name
  }
  INSTRUCTOR {
    int instructor_id
    string full_name
  }
  STUDENT {
    int student_id
    string full_name
  }
  COURSE {
    int course_id
    string course_name
  }
  ENROLLMENT {
    int enrollment_id
    date enrolled_on
    string final_grade
  }

Walk through the diagram one relationship at a time. Ask students which table resolves the many-to-many relationship between students and courses.

Keys

• Data Modeling

• Keys

• Normalization

Keys in Relational Databases

Keys help identify records and connect tables.

Key type	Purpose	Example
Primary key	Uniquely identifies a row	student_id in Student
Foreign key	References a row in another table	student_id in Enrollment
Composite key	Uses multiple columns for uniqueness	(student_id, course_id, term)
Candidate key	Could serve as the primary key	A verified, unique email address

Transition from diagrams to implementation. Explain that keys are what make relationships enforceable in a relational database.

Primary Keys

A primary key uniquely identifies each row in a table.

A good primary key should be:

• Unique
• Not missing
• Stable over time
• Simple to reference

Explain that primary keys prevent ambiguity. Mention that IDs are often used because names and emails can change or repeat.

Primary Key Example

student_id	first_name	last_name	email
101	Ana	Rivera	ana@example.edu
102	Jamal	Lee	jamal@example.edu
103	Mei	Chen	mei@example.edu

student_id is the primary key because it uniquely identifies each student.

Ask students why first name or last name alone would not work as a primary key. Then ask whether email is always a safe choice.

Foreign Keys

A foreign key is a field in one table that refers to the primary key in another table.

Foreign keys are used to:

• Connect related tables
• Enforce valid relationships
• Prevent orphan records

Use the phrase parent table and child table if helpful. Explain that a foreign key makes sure a course references a real department or an enrollment references a real student.

Primary and Foreign Keys Together

flowchart LR
  D[Department table\ndepartment_id is primary key] --> C[Course table\ndepartment_id is foreign key]

  classDef blue fill:#0072B2,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef orange fill:#E69F00,color:#000000,stroke:#111111,stroke-width:2px

  class D blue
  class C orange

Point out that the same value can be a primary key in one table and a foreign key in another. The role depends on the table where the column appears.

Activity: Find the Keys

Given these tables, identify the primary keys and foreign keys.

Student(student_id, first_name, last_name)

Course(course_id, course_name, department_id)

Enrollment(enrollment_id, student_id, course_id, enrolled_on)

Questions:

• Which fields uniquely identify rows?
• Which fields connect tables?
• Which table resolves a many-to-many relationship?

Give students 8 minutes. Ask them to mark primary keys with PK and foreign keys with FK. Review that Enrollment connects Student and Course.

Composite Keys

A composite key uses two or more columns together to uniquely identify a row.

Example:

Enrollment(student_id, course_id, term)

Together, these fields can identify one student’s enrollment in one course during one term.

Explain that composite keys are useful when no single field is enough. Also mention that some designers still add a separate enrollment_id for convenience.

Normalization

• Data Modeling

• Keys

• Normalization

Why Normalize Data?

Normalization stores each fact in the table where it belongs.

Problem	Example	Risk
Repeated data	A customer’s name appears on every order line	Updates can disagree
Insert anomaly	A product cannot be recorded until someone orders it	Valid facts cannot be stored
Delete anomaly	Deleting the final order removes the only product record	Valid facts disappear

Connect each anomaly to a business consequence. A normalized design reduces these risks by storing customer facts once, product facts once, and transactions separately.

Normal Forms at a Glance

Normal form	Requirement	Question to ask
1NF	Atomic values and no repeating groups	Does each cell contain one value?
2NF	1NF plus no partial dependencies	Does each non-key field depend on the whole key?
3NF	2NF plus no transitive dependencies	Does a non-key field depend on another non-key field?

Emphasize that normalization is cumulative. A table must satisfy 1NF before it can satisfy 2NF, and it must satisfy 2NF before it can satisfy 3NF.

First Normal Form

A table satisfies first normal form when:

• Each cell contains one value
• Each column describes one type of fact
• Each row has a unique identifier
• Repeating column groups do not appear

flowchart LR
  A[Product 1 and Product 2 columns] --> B[One product per row]
  B --> C[Composite row key]

  classDef red fill:#D55E00,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef yellow fill:#F0E442,color:#000000,stroke:#111111,stroke-width:2px
  classDef green fill:#009E73,color:#000000,stroke:#111111,stroke-width:2px

  class A red
  class B yellow
  class C green

Explain that 1NF changes repeated columns or lists into separate rows. In the working example, each row represents one product on one order, identified by the combination of order_id and product_id.

Order Dataset Normalization

An online store records customers, orders, products, and quantities.

Business rules

• One customer can place many orders
• One order can contain many products
• A product can appear on many orders
• Quantity describes one product on one order

Use the business rules to identify likely entities. Customer, Order, and Product are entities. Order Item represents the many-to-many relationship between orders and products.

Normalization Process

flowchart LR
  A[Unnormalized dataset] --> B[1NF: one value per cell]
  B --> C[2NF: remove partial dependencies]
  C --> D[3NF: remove transitive dependencies]

  classDef red fill:#D55E00,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef yellow fill:#F0E442,color:#000000,stroke:#111111,stroke-width:2px
  classDef blue fill:#0072B2,color:#FFFFFF,stroke:#111111,stroke-width:2px
  classDef green fill:#009E73,color:#000000,stroke:#111111,stroke-width:2px

  class A red
  class B yellow
  class C blue
  class D green

Preview the four versions of the same data. Each step solves a different dependency problem while preserving the original facts.

Unnormalized Dataset

order_id	order_date	customer	products
1001	2026-09-01	C01, Ana Rivera	P10, USB-C Hub, 29.99, qty 1 and P20, Wireless Mouse, 24.50, qty 2
1002	2026-09-02	C02, Jamal Lee	P10, USB-C Hub, 29.99, qty 1
1003	2026-09-03	C01, Ana Rivera	P30, Laptop Stand, 39.00, qty 1 and P20, Wireless Mouse, 24.50, qty 1

Problems: customer contains two values, and products contains a list of repeating product groups.

Ask students to count how many values appear in the customer and products cells. Point out that searching, sorting, or updating one product would require parsing a list inside a cell.

Step 1: Dataset in 1NF

order_id	order_date	customer_id	customer_name	product_id	product_name	unit_price	quantity
1001	2026-09-01	C01	Ana Rivera	P10	USB-C Hub	29.99	1
1001	2026-09-01	C01	Ana Rivera	P20	Wireless Mouse	24.50	2
1002	2026-09-02	C02	Jamal Lee	P10	USB-C Hub	29.99	1
1003	2026-09-03	C01	Ana Rivera	P30	Laptop Stand	39.00	1
1003	2026-09-03	C01	Ana Rivera	P20	Wireless Mouse	24.50	1

Composite primary key: (order_id, product_id)

The conversion separates customer details into individual columns and creates one row per product on an order. Every cell now contains one value. The combination of order_id and product_id identifies each row.

Dependencies in the 1NF Table

order_id                  -> order_date, customer_id
customer_id               -> customer_name
product_id                -> product_name, unit_price
(order_id, product_id)    -> quantity

quantity depends on the complete key. Several other fields depend on only part of the key.

Read each line as “determines.” One order_id determines its date and customer. One product_id determines its name and current unit price. The full order-product key determines quantity.

Partial Dependencies in the 1NF Table

The key is (order_id, product_id).

• order_date, customer_id, and customer_name depend on order_id
• product_name and unit_price depend on product_id
• quantity depends on both key columns

Separating order facts and product facts removes the partial dependencies.

Define a partial dependency as dependence on one part of a composite key. Ask which field genuinely describes the relationship between an order and a product. The answer is quantity.

Step 2: Orders Table in 2NF

Orders_2NF

order_id (PK)	order_date	customer_id	customer_name
1001	2026-09-01	C01	Ana Rivera
1002	2026-09-02	C02	Jamal Lee
1003	2026-09-03	C01	Ana Rivera

All fields depend on the complete primary key, order_id.

The order fields no longer repeat for every product. This table satisfies 2NF because it has a single-column key, so partial dependency cannot occur. Customer name still repeats, which becomes the focus of 3NF.

Step 2: Products Table in 2NF

Products

product_id (PK)	product_name	unit_price
P10	USB-C Hub	29.99
P20	Wireless Mouse	24.50
P30	Laptop Stand	39.00

Each product name and price now appears once.

Product name and unit price depend on product_id, so they move to Products. State that this example treats unit_price as the product’s current price. A system that preserves sale prices could store that price with each order item.

Step 2: Order Items Table in 2NF

Order_Items

order_id (PK, FK)	product_id (PK, FK)	quantity
1001	P10	1
1001	P20	2
1002	P10	1
1003	P30	1
1003	P20	1

The composite primary key is (order_id, product_id).

Order_Items keeps the fact that depends on the full composite key: quantity. The two columns in the key also serve as foreign keys that connect each row to an order and a product.

Transitive Dependency in the 2NF Design

Inside Orders_2NF:

order_id -> customer_id -> customer_name

customer_name depends on customer_id, a non-key field.

Moving customer details into a separate table removes this transitive dependency.

Explain transitive dependency as an indirect path. order_id identifies customer_id, and customer_id identifies customer_name. Customer name belongs in a table where customer_id serves as the key.

Step 3: Customers Table in 3NF

Customers

customer_id (PK)	customer_name
C01	Ana Rivera
C02	Jamal Lee

The table stores each customer name once.

The new Customers table removes customer_name from Orders. If Ana changes her name, the database now requires one update rather than an update to every order.

Step 3: Orders Table in 3NF

Orders

order_id (PK)	order_date	customer_id (FK)
1001	2026-09-01	C01
1002	2026-09-02	C02
1003	2026-09-03	C01

Every non-key field depends directly on order_id.

Orders retains customer_id because it records which customer placed the order. The foreign key connects the order to the descriptive customer information without repeating the customer name.

Remaining Tables in 3NF

Products

product_id (PK)	product_name	unit_price
P10	USB-C Hub	29.99
P20	Wireless Mouse	24.50
P30	Laptop Stand	39.00

Order_Items

order_id (PK, FK)	product_id (PK, FK)	quantity
1001	P10	1
1001	P20	2
1002	P10	1
1003	P30	1
1003	P20	1

Products and Order_Items did not change between 2NF and 3NF because they contained no transitive dependencies. Review how the two foreign keys in Order_Items connect the many-to-many relationship.

Complete 3NF Schema

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_ITEMS : contains
  PRODUCTS ||--o{ ORDER_ITEMS : appears_in

  CUSTOMERS {
    text customer_id PK
    text customer_name
  }
  ORDERS {
    int order_id PK
    text order_date
    text customer_id FK
  }
  PRODUCTS {
    text product_id PK
    text product_name
    real unit_price
  }
  ORDER_ITEMS {
    int order_id PK, FK
    text product_id PK, FK
    int quantity
  }

Walk through the foreign keys. Orders connects to Customers. Order_Items connects to Orders and Products. Each descriptive fact has one home.

Dataset Changes Across the Normal Forms

Stage	Structure	Main improvement
Unnormalized	One table with lists inside cells	Starting point
1NF	One flat table with one product per row	Atomic values
2NF	Orders, Products, and Order_Items	No partial dependencies
3NF	Customers, Orders, Products, and Order_Items	No transitive dependencies

The five order-item facts remain present at every stage.

Normalization changes how the database stores the facts. The final design still represents the five original order-product combinations, but customer, order, and product descriptions no longer repeat unnecessarily.

Checking the 3NF Conversion

Check	Expected result
Unique customers in 1NF	2
Rows in Customers	2
Unique orders in 1NF	3
Rows in Orders	3
Unique products in 1NF	3
Rows in Products	3
Order-product rows in 1NF and Order_Items	5 in each

Matching counts help confirm that the split preserved every entity and relationship.

Ask students to verify each count by looking back at the 1NF table. Explain that matching order-product rows gives a simple classroom check for a lossless split.

Benefits of the 3NF Design

Earlier problem	Improvement in 3NF
Ana’s name repeats on several rows	Customers stores it once
Product details repeat across orders	Products stores them once
Deleting an order could remove product details	Products remains independent
A product needs an order before it can be recorded	Products accepts it independently

Connect these improvements to update, delete, and insert anomalies. Ask students which final table contains each type of fact.

Normalization Depends on Business Rules

The example assumes that:

• Each product ID has one current price
• One product appears at most once per order
• Each order belongs to one customer

Different rules can require a different schema. For example, storing the sale price in Order_Items would preserve historical prices.

Functional dependencies come from business rules. Designers should confirm these assumptions with domain experts before finalizing the schema.

Activity: Normalize Student Enrollment Data

Unnormalized Dataset

student_id	student_name	email	major	courses
S101	Ana Rivera	ana@college.edu	M01, Data Science	C101, Databases, Dr. Kim, A; C202, Statistics, Dr. Patel, B+
S102	Jamal Lee	jamal@college.edu	M02, Computer Science	C101, Databases, Dr. Kim, B
S103	Mei Chen	mei@college.edu	M01, Data Science	C202, Statistics, Dr. Patel, A; C303, Programming, Dr. Gomez, A-

The major and courses columns contain multiple values. Therefore, the dataset does not satisfy first normal form.

Instructions

Part 1: Convert the Dataset to 1NF

• Store only one value in each cell.
• Create one row for each student-course combination.
• Separate the values currently stored in the major and courses columns.
• Use (student_id, course_id) as the composite primary key.

Part 2: Convert the Dataset to 2NF

Use the following functional dependencies:

student_id                -> student_name, email, major_id, major_name
course_id                 -> course_name, instructor_name
(student_id, course_id)   -> grade

• Identify fields that depend on only student_id.
• Identify fields that depend on only course_id.
• Identify fields that depend on the complete composite key.
• Split the 1NF table into appropriate 2NF tables.

Part 3: Convert the Dataset to 3NF

Consider the following transitive dependency:

student_id -> major_id -> major_name

• Identify the non-key field that determines another non-key field.
• Create a separate table for majors.
• Update the student table so that it references the new major table.

Part 4: Identify the Keys

For every table in your final 3NF design:

• Label each primary key with PK.
• Label each foreign key with FK.
• Identify any composite primary keys.
• Draw the relationships between the tables.

Give students 15 to 20 minutes. Remind them that 1NF removes multi-value cells, 2NF removes partial dependencies, and 3NF removes transitive dependencies.

Key Takeaways

• ER diagrams make entities and relationship rules visible
• Primary keys identify rows, while foreign keys connect tables
• 1NF removes repeating groups and multi-value cells
• 2NF removes partial dependencies from composite keys
• 3NF removes dependencies between non-key fields
• Row and key checks help confirm that normalization preserved the data

Close by connecting the three lesson sections. A strong database design starts with a model, makes relationships enforceable with keys, and stores each fact in the table determined by its key.