DAY 3 TEXTBOOKACE Startup SW/AI Pilot

ACE AI Startup Bootcamp Textbook Series 03

Data Architecture and ERD Design

A self-study textbook covering essential theory, comparative examples, guided practice, quality review, and a capstone exercise. Central question: How do we preserve business rules through data structures and database constraints?

ACE AI Startup Bootcamp | Day 3Study Guide

HOW TO STUDY

Learning Goals and Study Routine

How do we preserve business rules through data structures and database constraints? You complete today’s lesson when you can answer this question in your own words, produce the required artifact, and review its quality.

Theory: 60 minPractice: 90 minReview: 30 min

Extract entities, attributes, and relationships from requirements.

Explain primary and foreign keys.

Apply first, second, and third normal form.

Express cardinality and optionality accurately.

Implement business rules with DDL constraints.

Design indexes from actual query patterns.

Recommended self-study routine

Explain why a problem occurs before memorizing its terminology.
Describe the difference between good and poor examples using observable criteria.
Attempt the capstone before opening the model answer.
Mark missing conditions in a second color and revise your artifact.

ACE AI Startup Bootcamp | Day 3Table of Contents

CONTENTS

Study Guide and Learning GoalsPage 02
Chapter 1. Core Theory and Design PrinciplesPage 04
Chapter 2. Guided Design PracticePage 05
Chapter 3. Case Review and Quality CheckPage 06
Chapter 4. Capstone and Model AnswerPage 07
Glossary and Final ChecklistPage 08
Self-study reference and guided practicePages 09–11

Completion standard

Submit the capstone artifact, score at least 80/100 on the self-review, and write your own answers to the four concept questions.

ACE AI Startup Bootcamp | Day 3Chapter 1 · Core Theory

CHAPTER 01

Core Theory: Data Architecture and ERD Design

Each technical term exists to solve a recurring design problem. Study when and why the concept is needed, not merely its definition.

Core concept	Working definition
Entity	A business object that must be independently identified and stored.
Attribute	An atomic data item that describes an entity.
Primary Key	A stable key that uniquely identifies a row.
Foreign Key	A key that protects referential integrity between entities.
Normalization	The process of reducing duplication and update anomalies.
Index	An auxiliary structure that speeds reads while increasing write and storage cost.

Poor and Effective Approaches

Avoid

Copy the customer name, phone number, and expert name into every reservation row.

Prefer

Separate Customer, Expert, and Reservation, and let Reservation reference each party by foreign key.

Design formula

[Business Noun] → [Stable Identifier] → [Atomic Attributes] → [Relationships] → [Constraints] → [Query-driven Indexes]

ACE AI Startup Bootcamp | Day 3Chapter 2 · Guided Practice

CHAPTER 02

A Six-Step Design Workflow

Define the problem

Collect business nouns as candidate entities.

Extract the structure

Merge synonyms and distinguish entities from events or transient values.

Design the core flow

Choose primary keys, required attributes, and data types.

Add failure conditions

Mark 1:N and N:M relationships and required or optional participation.

Connect policies

Inspect duplication and insertion, update, and deletion anomalies.

Verify and trace

Apply UNIQUE, NOT NULL, CHECK, FK, and justified indexes in DDL.

Worked Example

SQL DDL example

CREATE TABLE reservation ( id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customer(id), seat_id BIGINT NOT NULL REFERENCES seat(id), starts_at TIMESTAMP NOT NULL, ends_at TIMESTAMP NOT NULL, status VARCHAR(20) NOT NULL CHECK (status IN ('PENDING','CONFIRMED','CANCELLED')), UNIQUE (seat_id, starts_at) ); CREATE INDEX idx_reservation_customer ON reservation(customer_id, starts_at DESC);

Questions for reading the example

Are the input and initiating condition explicit?
Are success and failure outcomes observable?
Are duplication, authorization, concurrency, and dependency failure covered as needed?
Can the result be traced back to a requirement?

ACE AI Startup Bootcamp | Day 3Chapter 3 · Review

CHAPTER 03

Concept Check and Quality Review

CONCEPT CHECK

Why might a surrogate key be preferable to a natural key?
What semantic risk does a nullable column create?
Why must a many-to-many relationship use an associative entity?
Why should we not index every column?

Answer each in two or three sentences and add one example that supports your explanation.

Self-Assessment · 100 points

Area	Standard	Points
Accuracy	Concepts and technical choices match the facts and requirements.	25
Completeness	Normal flow, boundaries, failures, and recovery are covered.	25
Consistency	Terms, IDs, states, and interfaces agree across artifacts.	20
Verifiability	Observable outcomes and completion criteria are present.	20
Reasoning	The choice and its tradeoffs can be explained clearly.	10

If your score is below 80

Do not only correct the result. Record which question you failed to ask so your next design process prevents the same omission.

ACE AI Startup Bootcamp | Day 3Chapter 4 · Capstone

CHAPTER 04

Capstone Exercise and Model Answer

SUBMISSION

Create an ERD containing Student, Course, Enrollment, and Payment. Prevent one student from enrolling in the same course twice.

List assumptions and unresolved decisions first.
Produce the main design as a table, diagram, or code block.
Include the normal flow and at least three failures or boundaries.
Score it with the rubric and compare before and after revision.

Open the model answer

Resolve the Student–Course many-to-many relationship with Enrollment. Add UNIQUE(student_id, course_id), then let Payment reference Enrollment so each payment is traceable to one enrollment.

How to use the answer

The model is not the only valid design. If yours differs, explain the requirement, cost, complexity, or risk that justifies your choice.

ACE AI Startup Bootcamp | Day 3Lesson Review

REVIEW

Glossary and Final Checklist

Term	Plain-English meaning
Cardinality	The number relationship between two entities.
Optionality	Whether participation in a relationship is required.
Normalization	Structuring tables to reduce duplication and anomalies.
DDL	SQL that defines tables and constraints.
Composite Key	A key made from more than one column.
Referential Integrity	The guarantee that a referenced row exists.

Eight checks before submission

Can you answer today’s central question in your own words?
Are inputs, conditions, and results explicit?
Did you include failures and recovery, not only the happy path?
Did you review concurrency, duplicate requests, and permissions?
Did you account for dependency failure and timeouts?
Can you explain the disadvantages and alternatives to your choice?
Are terminology and states consistent across artifacts?
Is there an observable or testable completion standard?

Day 3 in one question

How do we preserve business rules through data structures and database constraints? Answer it now using evidence from the artifact you created.

ACE AI Startup Bootcamp | Day 3Self-study reference

SELF-STUDY 01

Key Terms in Context

Learn each term as a decision tool. Read across each row: definition, reason to use it, and the failure it prevents.

Term	Plain definition	Why it matters	Example or caution
Entity	A business object that must be independently identified and stored.	It establishes table boundaries.	Name entities with nouns such as User, Order, and Product.
Primary key (PK)	A value that uniquely identifies each row.	It makes updates and references unambiguous.	Prefer an immutable ID over a changeable email address.
Foreign key (FK)	A value that references another table's primary key.	It preserves relationships and referential integrity.	Prevent orders from referencing a user that does not exist.
Normalization	Structuring data so one fact has one authoritative home.	It reduces duplication and update anomalies.	Separate current profile data from historical order snapshots.
Cardinality	The allowed number relationship between two entities.	It determines 1:1, 1:N, and N:M structures.	Resolve N:M with a junction table.
Index	A data structure that speeds up locating matching rows.	It improves frequent query performance.	Extra indexes increase write and storage cost.

Practice scenario

One user creates many orders, each order contains many products, and the purchase-time price must remain unchanged.

ACE AI Startup Bootcamp | Day 3Guided practice

SELF-STUDY 02

Guided Practice and Troubleshooting

Practice scenario

One user creates many orders, each order contains many products, and the purchase-time price must remain unchanged.

Complete in order

Separate nouns from business rules to identify candidate entities.
Mark the PK and required or optional attributes for each entity.
Define cardinality, optionality, and delete behavior for every relationship.
Write three representative queries and review indexes and history fields.

Required evidence

Save one artifact, three assumptions, and at least three failure cases. A classmate should be able to reproduce your reasoning without asking what you meant.

If the result is wrong, diagnose it

Observed symptom	Likely cause	Next action
Past order total changes	Only the current product price is referenced	Store purchase-time unit price on OrderItem
The same fact disagrees across tables	Duplicated source data	Normalize or name one authoritative source
List query is slow	Missing filter and sort index	Design a composite index from the real query

ACE AI Startup Bootcamp | Day 3Retrieval practice

SELF-STUDY 03

Check Your Understanding

Retrieval check — answer before opening

How do you implement N:M?

Create a junction table that references both primary keys and stores relationship attributes.

Are NULL and an empty string equal?

No. NULL means no value; an empty string is a known value of length zero.

When is the ERD complete?

Keys, relationships, optionality, and delete policies explain the business rules and key queries.

Teach it back in two minutes

Explain the day's main decision, one failure mode, and one verification method without reading the page. If you cannot connect all three, return to the row or diagnostic case you missed.