Understanding Database Design and Modeling: From ER Diagrams to UML and Class Structures

Nathan KaduruNathan Kaduru
6 min read

If you’re stepping into database design and modeling for the first time, concepts like Entity-Relationship diagrams, Normalization, UML, and Class Diagrams might sound intimidating. But once you understand the basics, these tools will make organizing, visualizing, and structuring data much easier! Here’s an overview of each topic, explained as simply as possible with some real-world examples.


1. Entity-Relationship Models and Relational Databases

When designing a database, it’s essential to understand how different parts of your data relate. Entity-Relationship (ER) models provide a simple way to visualize how essential parts in your database are connected to each other. Think of ER models as a map that shows you the structure of your data before you create actual database tables.

Breaking Down ER Models

  • Entities: An entity is anything we want to keep data on, like Student, Teacher, or Course. Each entity has attributes, which are the characteristics we want to track. For example:

    • A Student entity could have attributes like StudentID, Name, and DOB (Date of Birth).

    • A Course entity might have attributes like CourseID, Title, and Credits.

  • Relationships: Relationships describe how entities are connected. For example:

    • A Student “enrolls in” a Course.

    • A Teacher “teaches” a Course.

Relationships also have cardinality, which indicates whether an entity in a relationship can be associated with one or many instances of another entity. For example:

  • One Teacher may teach multiple courses (one-to-many).

  • Multiple Students can enroll in the same Course (many-to-many).

Example Scenario

Imagine a school’s database where we track students, teachers, and courses:

  • Entities: Student, Teacher, Course

  • Attributes:

    • Student: StudentID, Name, DOB

    • Teacher: TeacherID, Name, Department

    • Course: CourseID, Title, Credits

  • Relationships:

    • Student “enrolls in” Course (many-to-many)

    • Teacher “teaches” Course (one-to-many)

These relationships help us build tables in a relational database (like MySQL or PostgreSQL) where each entity becomes a table. The primary key (e.g., StudentID or CourseID) uniquely identifies each record, and foreign keys link related tables together, making it easy to retrieve data and maintain integrity.


2. Normalization: Streamlining Your Data

When building a database, you’ll often find that some data is repeated. Normalization is the process of structuring your tables to avoid redundancy and ensure data consistency. It consists of several stages called normal forms, each with specific rules to make your database as efficient as possible.

The Steps of Normalization

  • First Normal Form (1NF): Ensure that each column in a table contains only atomic values (indivisible data). For example, instead of having a “PhoneNumbers” column with multiple phone numbers in one cell, we’d split it so each phone number is in a separate row.

  • Second Normal Form (2NF): Remove partial dependencies—every attribute must depend on the whole primary key, not just part of it. If CourseID and StudentID together form a composite primary key in a Enrollment table, then other attributes like EnrollmentDate should depend on both and not just CourseID or StudentID alone.

  • Third Normal Form (3NF): Remove transitive dependencies, which means no non-key attributes should depend on other non-key attributes. If we have a Student table with StudentID, Name, Department, and DepartmentLocation, DepartmentLocation shouldn’t be stored here; instead, it should go into a separate Department table, linked to Student by a foreign key.

Example Scenario

In our school example:

  • Suppose we have a Course table with columns CourseID, Title, and TeacherName. If we move TeacherName to a Teacher table with TeacherID, and link TeacherID as a foreign key in Course, we’re normalizing the data and avoiding repetition of teacher details in every Course record.

Normalization not only keeps the database clean and easy to manage but also improves performance when querying data.


3. UML (Unified Modeling Language): A Visual Blueprint

When designing software, especially in object-oriented programming, UML (Unified Modeling Language) helps us visualize how different parts of the system interact. UML diagrams are like a blueprint of the system, showing how different entities (or objects) relate and behave.

Common UML Diagrams

  • Class Diagrams: Show the structure of a system by illustrating the classes, their attributes, and relationships. For example, in our school system, we might have a Student class, Teacher class, and Course class, each with their own attributes and actions.

  • Use Case Diagrams: Illustrate the various “use cases” (functions or activities) that a user can perform in the system. For instance, a Student can “enroll in a course” or “view grades,” while a Teacher can “create assignments” or “grade submissions.”

  • Sequence Diagrams: Show the sequence of interactions between objects over time. This is useful for understanding workflows, like the process of a Student enrolling in a Course, with steps showing how the system checks prerequisites, confirms enrollment, and updates records.


4. Class Diagrams in UML: Defining Structure and Relationships

Class Diagrams are a type of UML diagram that shows the structure of classes in a system and how they relate. Each class represents an object with attributes (data it holds) and methods (actions it can perform). Let’s go back to our school system example to illustrate this.

Example of Class Diagrams

  • Class: Student

    • Attributes: studentID, name, age, email

    • Methods: enrollInCourse(), dropCourse(), viewGrades()

  • Class: Course

    • Attributes: courseID, title, credits

    • Methods: addStudent(), removeStudent(), getRoster()

  • Class: Teacher

    • Attributes: teacherID, name, department

    • Methods: assignCourse(), gradeStudent()

Understanding Aggregation and Composition in Class Diagrams

Aggregation and Composition are two ways to show “has-a” relationships between classes:

  • Aggregation: A weaker association where one class uses or contains another class, but they don’t depend on each other’s existence. For example, a Teacher “belongs to” a Department, but if the Teacher were removed, the Department would still exist.

  • Composition: A stronger association where one class cannot exist without the other. For example, a Car has an Engine. If the Car is deleted, the Engine doesn’t exist independently.

In our school system:

  • Aggregation Example: Teacher is associated with Department through aggregation because even if there are no teachers, the Department entity still has relevance.

  • Composition Example: If a Course has Assignments, they’re in a composition relationship—if the Course is deleted, its Assignments should also be deleted as they are part of the course.

Why Class Diagrams Are Useful

Class diagrams help developers understand how different classes in a system work together, which can streamline both coding and maintenance. They act as a reference to understand what objects exist in the system and how they connect and interact.


Wrapping It Up

Understanding ER models, relational databases, normalization, UML, and class diagrams may take some time, but they are incredibly valuable for creating efficient, maintainable, and scalable databases and applications. With ER models, we can map out data and its relationships; normalization keeps the data clean and organized; and UML diagrams, especially class diagrams, give us a clear structure and understanding of the system.

With these tools, you’ll be equipped to tackle more complex projects, keeping data organized, systems clear, and your applications running smoothly! It may feel like a lot at first, but once you start using them, everything will start to make sense.

0
Subscribe to my newsletter

Read articles from Nathan Kaduru directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Nathan Kaduru
Nathan Kaduru

I am a self-taught Front-end developer skilled in HTML, CSS, JavaScript, React, Typescript, and Nextjs, also a technical writer looking to expand my portfolio