Lesson 2: Data Modeling with ER Diagrams

Data modeling is the process of defining how real-world entities (people, places, things) and their relationships are represented in a database. The goal is to create a conceptual blueprint of the data. At this level, we identify entities (for example, Student, Course, Instructor), attributes of those entities (a student's name, ID, program), and relationships between entities (a student enrolls in a course). We use diagrams to map out these ideas before writing any SQL. A conceptual model is independent of the database system – it focuses on business rules (e.g. each student can enroll in many courses).

An Entity-Relationship Diagram (ERD) visually shows entities as boxes and relationships as lines (often with diamonds). Attributes are listed in or near the entity boxes, with a primary key underlined (it uniquely identifies each entity). Cardinality (one-to-one, one-to-many, many-to-many) is marked on relationship lines. For example, in a school database, Student and Course might be entities, and the relationship "enrolls in" connects them. Each course can have many students (one-to-many) and each student can take many courses (so the relationship is many-to-many, usually implemented via an Enrollment table).

A typical ERD for this would model three tables:

• STUDENT — student_id (PK), name, program, year
• ENROLLMENT — enrollment_id (PK), student_id (FK), course_id (FK), semester, year, grade
• COURSE — course_id (PK), course_name, credits

A student can enroll in many courses, and each course can have many students; the ENROLLMENT table breaks the many-to-many relationship into two one-to-many links.