Basic Data Modeling

• Basic data modeling is the process of organizing and structuring data so it can be stored efficiently and used effectively in a database. Good data modeling ensures that information is logically grouped, accurately related, and free from unnecessary duplication. It forms the foundation for building reliable database systems, applications, and reports.

What Is a Schema?

• A schema is the blueprint or structure of a database. It defines how data is organized, including the tables, columns, data types, relationships, and constraints.

• Think of a schema as the architectural plan for how data will be stored and accessed. For example, a school system schema might include tables like students, courses, teachers, and enrollments, each with clearly defined fields and relationships.

ER Diagrams (Entity-Relationship Diagrams)

• An ER diagram is a visual representation of a database structure. It shows entities (tables), their attributes (columns), and the relationships between them.

• ER diagrams help developers and stakeholders understand how data flows and connects:

  • Entities: Student, Course, Order

  • Attributes: student_id, course_name, order_date

  • Relationships: A student enrolls in courses; a customer places orders

• ER diagrams are essential during the planning phase because they prevent design mistakes before building the actual database.

One-to-Many and Many-to-Many Relationships

• Relationships describe how tables in a database relate to each other.

One-to-Many (1:N)

• One record in a table is associated with many records in another.

  • One teacher => Many students.

  • One customer => Many orders.

tip

• This is implemented using a foreign key on the "many" side.

Many-to-Many (M:N)

• Many records in one table relate to many records in another.

  • Students enroll in multiple courses.

  • Courses have multiple students.

tip

• Many to many relationships requires a junction (bridge) table, such as students_courses, which stores pairs of student ids and course ids.

Normal Forms (1NF, 2NF, 3NF)

• Process of structuring data to reduce redundancy and improve data integrity. Normalization keeps data clean, prevents inconsistencies, and helps maintain database performance.

1NF - First Normal Form

• No repeating groups.

• Each column holds one value or an atomic value, this means each value in a column holds one single, indivisible piece of data, not a list or not multiple values crammed together.

2NF - Second Normal Form

• Must meet 1NF.

• Plus:

  • All non-key columns must depend on the entire primary key.

  • Applies mainly to composite keys.

3NF - Third Normal Form

• Must meet 2NF.

• Plus:

  • No column depends on another non-key column.

  • Prevents duplicate or derived data.

Normal forms

• This course will only cover: 1NF, 2NF and 3NF.

• Here is a list of the other normal forms:

  • Higher Normal Forms (More Advanced):

    • Boyce–Codd Normal Form (BCNF)

    • Fourth Normal Form (4NF)

    • Fifth Normal Form (5NF)

    • Sixth Normal Form (6NF)

  • Other Specialized Normal Forms:

    • Domain-Key Normal Form (DKNF)

    • EKNF (Elementary Key Normal Form)

    • ETNF (Essential Tuple Normal Form)

    • ONF (Optimal Normal Form)

Normalizing a Student Course Table

• Suppose we have the following table storing students, courses, and their advisors:

Unnormalized Table (UNF) - Table that has all fields combined, the goal of normalization is to separate the fields with their proper entities and link them via primary / foreign keys.

student_id	student_name	courses	advisor_name
1	Alice	Math, English	Prof. Smith
2	Bob	Math	Prof. Smith
3	Charlie	English	Prof. Jones

• Issue: Courses contains multiple values (Math, English). It violates the atomic values rule and repeating groups exist. This makes it not in 1NF.

Step 1: Convert to 1NF

• Rules for 1NF:

  • Each column must have atomic values.

  • Each row must be unique.

• 1NF Table (Split repeating courses).

student_id	student_name	course	advisor_name
1	Alice	Math	Prof. Smith
1	Alice	English	Prof. Smith
2	Bob	Math	Prof. Smith
3	Charlie	English	Prof. Jones

• Each column now contains a single value.

Step 2: Convert to 2NF

• Rule for 2NF:

  • Must be in 1NF.

  • Remove partial dependencies (non-key attributes must depend on entire primary key).

  • Issue: Composite key: (student_id, course) but advisor_name depends only on course, not the whole key.

• 2NF Tables (Split tables).

Students Table

student_id	student_name
1	Alice
2	Bob
3	Charlie

Courses Table

course_id	course_name	advisor_name
101	Math	Prof. Smith
102	English	Prof. Jones

Enrollments Table (Composite Key: student_id, course_id)

student_id	course_id
1	101
1	102
2	101
3	102

• All non-key attributes now depend on the whole key.

Step 3: Convert to 3NF

• Rule for 3NF:

  • Must be in 2NF.

  • Remove transitive dependencies (non-key attributes depending on other non-key attributes).

  • Issue: advisor_name depends on course_name, not on course_id (the primary key in Courses table).

• 3NF Tables (Split further).

Courses Table

course_id	course_name	advisor_id
101	Math	10
102	English	11

Advisors Table

advisor_id	advisor_name
10	Prof. Smith
11	Prof. Jones

• Now every non-key attribute depends only on the primary key.

Summary

Normal Form	Key Change
1NF	Split repeating courses into separate rows.
2NF	Remove partial dependency: Create separate tables for students, courses, and enrollments.
3NF	Remove transitive dependency: Create separate advisors table.

Result - From 1 Unormalized Table to 4 Normalized Tables

Students Table

student_id	student_name
1	Alice
2	Bob
3	Charlie

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR(50) NOT NULL
);

Courses Table

course_id	course_name	advisor_id
101	Math	10
102	English	11

CREATE TABLE courses (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(50) NOT NULL,
  advisor_id INT NOT NULL,
  FOREIGN KEY (advisor_id) REFERENCES advisors(advisor_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Enrollments Table (Composite Key: student_id, course_id)

student_id	course_id
1	101
1	102
2	101
3	102

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Advisors Table

advisor_id	advisor_name
10	Prof. Smith
11	Prof. Jones

CREATE TABLE advisors (
  advisor_id INT  AUTO_INCREMENT PRIMARY KEY,
  advisor_name VARCHAR(50) NOT NULL
);