Engineers need a visual way to understand how these separate elements are related to each other and how they work together. To visualize this, they build UML diagrams. In both software and non-software systems across multiple business verticals, the use of UML diagrams has played a critical part in facilitating the design, development, and implementation of some successful logical and physical design models.
This Database UML Modeling guide has been prepared for beginners to help teach them the fundamentals of UML Database Modeling. By the end of this guide, you will be able to understand at a moderate level all the basics of database modeling. And more importantly, the concepts you’ll take away will allow you to design UML database models from scratch.
What is Database Modeling?
Image Source
Database Modeling is all about how we represent an application’s data within a database. Often as developers are designing applications that have persistence, they use higher-level data models that are specific for database design. These models are not implemented by the database system, rather they are translated into the model of the database system.
Object-relational database models are one of the most common solutions for providing reliable and efficient object persistence for applications. A typical application is normally made up of an application layer and the database layer. A data model is used to represent the structure of the database layer and the relationships within it. The application model uses object-oriented class models to describe how the application layer maps to the database model.
For example, you have a Relational Database Management System (RDBMS). If you create a database in the relational system, the database is going to consist of relations. However, instead of designing the relations directly, the database designer will use a high-level design model. That model will then go through a translator that will translate it into the relationships that are implemented by the database.
There are two popular higher-level database design models. These are:
- Entity-Relationship Model (E/R).
- Unified Modeling Language (UML).
Both are graphical models and can be translated into relations. For decades, the E/R model was very popular but recently the UML model has gained more popularity. For the purposes of this guide, you will focus more on the data modeling subset of UML.
What is UML?
The Unified Modeling Language (UML), is an industry-standard graphical language for specifying, visualizing, constructing, and documenting the artifacts of software systems. In 1997, the OMG (Object Management Group) established a set of agreed upon shapes, symbols, and notations that would be used to graphically depict each component that makes up a software system, its attributes, and how it relates to other components within the system.
It has proven to be effective in modeling large and complex systems and is a very important part of Object-Oriented design. UML diagrams are very easy to read and understand yet they are still able to simplify the complex process of software and database design. Using more comprehensible and easier to interpret graphical notation, UML diagrams communicate more clearly than natural language which can be imprecise or code which can be too detailed.
While this language was initially intended for software development, it is not dependent on any language or technology. UML can be used to map out processes even for non-technical processes such as business modeling and communication.
Note: One of the strengths of an uml data model is its ability to represent both the logical and physical aspects of a system’s data.
Load Data from MongoDB to Snowflake
Load Data from PostgreSQL to BigQuery
Load Data from MySQL to Databricks
Different Types of UML Database Diagrams
Image Source
A UML database diagram is pivotal in software engineering for visualizing various aspects of software systems. They fall into two primary categories: structural and behavioral diagrams.
Structural Diagrams
Image Source
Structural diagrams illustrate the static aspects of a system, akin to the blueprint of a building showing its components and their relationships. They include:
- Class Diagram: Displays classes, interfaces, collaborations, and their interconnections, typically used in object-oriented modeling.
- Object Diagram: Provides a snapshot of instances in a system at a specific point in time, showing how objects relate and their current state.
- Component Diagram: Outlines the organization of software components and their dependencies, reflecting the system’s modular structure.
- Deployment Diagram: Visualizes the physical deployment of software artifacts across hardware, showing the system’s runtime architecture.
- Package Diagram: Groups related elements into packages, highlighting the system’s namespace structuring and dependencies.
- Composite Structure Diagram: Focuses on the internal structure of a classifier, detailing the interactions and collaborations of its parts.
- Profile Diagram: Extends UML for specific domains by adding custom stereotypes and attributes to UML elements.
Behavioral Diagrams
Image Source
Behavioral diagrams, on the other hand, capture the dynamic aspects of a system, showing how it behaves and interacts over time. These diagrams include:
- Use Case Diagram: Represents the system’s functionalities and interactions with external actors, outlining the system’s requirements.
- Sequence Diagram: Details the order of messages exchanged between objects, illustrating the flow of interactions over time.
- Communication Diagram: Similar to sequence diagrams but emphasizes the relationships and message flow between objects.
- Interaction Overview Diagram: Merges sequence and activity diagrams to provide a high-level view of control flow between interactions.
- Timing Diagram: Shows the behavior of objects within a specified time frame, useful for analyzing time constraints.
- State Machine Diagram: Describes the state transitions of an object in response to events, capturing its behavior across various states.
- Activity Diagram: Depicts the flow of activities within the system, including branching, parallelism, and synchronization.
Structural diagrams are used to document the architecture of software systems, while behavioral diagrams describe the functionality and flow of systems. Each type of UML diagram provides a unique perspective and is chosen based on the specific aspect of the system being modeled.
Note: The UML database relationships diagram is a key component of the system design process, providing a clear blueprint of how database elements interact.
What is UML Database Modeling?
UML diagrams can be used for UML Database Modeling and design. They have constructs that can be used to accurately model tables and relationships. UML database notation method allows for modeling relational databases through UML diagrams. In the data modeling subset of UML, there are 2 basic concepts:
UML Database Modeling: Classes
UML Database Modeling Class diagrams describe the type of objects in the system and the different kinds of static relationships that exist between them. The Class is essentially a template for creating objects as well as representing an object’s initial state, attributes, and behaviors/methods
The Class concept in UML is not specific to data modeling, it’s also used for designing programs. It is the central modeling technique that is used in almost all Object-Oriented methods. Just like in Programming, a Class consists of Name, Attributes, and Methods. However, for data modeling, we also add a private key “PK” and a foreign key “FK”, and we drop the methods that are associated with the Class since our focus is on data modeling and not the operations on the data.
| Class Name |
+ Attribute: string
# Attribute: string
+ Attribute: float
– Attribute: boolean
– Attribute: int |
When representing your Class, you start by writing its name in the upper most partition. This is followed by its attributes, and their data types which follow after the colon.
The public, protected and private attributes of a class are denoted using the preceding +, #, or – sign respectively.
UML Database Modeling: Relationships
The are 6 distinct relationships between UML Classes in UML Database Modeling:
These relationships can be represented using different styles of arrows as shown in the diagram below:
Image Source
Association
Associations capture relationships between objects of two different Classes in UML Database Modeling. If we assume that there are two Classes that interact with each other, a continuous connecting line should be drawn between them indicating the association in the diagram. Often, we can also use a verb above the line conveying its meaning. For example, consider the following Class diagram:
If we want to have a relationship where customers can make orders, we can use a line between the Customer and Order Classes. This says that we have objects in the Customer Class and objects in the Order Class that are associated with each other.
In addition, the diagram also specifies the multiplicity/cardinality i.e., the number of objects that can take part in that relationship. Multiplicity captures how many objects of one Class can be related to an object of another Class in UML Database Modeling. Multiplicity is specified as a comma-separated list of intervals where each interval is represented as a minimum maximum range.
The different possible multiplicities have been summarized in the table below:
| Symbol | Meaning |
| 1 | Exactly 1 |
| 4,7,9 | Exactly 4, exactly 7, or exactly 9 |
| 0..1 | Zero or one |
| 2..9 | A value in the range from 2 to 9 |
| * | Zero or more (an unlimited number) |
| 0..* | Zero or more |
| 1..* | One or more |
Inheritance
Sometimes it’s also referred to as generalization. As the name implies, it’s a schematic representation of the relationship between a parent class and its descendants in UML Database Modeling. The hollow arrow is always pointed to the parent class. For example, in the same diagram, you can see that Payment is the parent class of Cash, Check, and Credit.
Realization
This refers to the relationships between an interface and objects that implement that interface in UML Database Modeling. Therefore, one Class can have some specifications that are implemented in another Class. A classic example is whereby several customers can realize the behavior of a single supplier.
Dependency
When an object of one Class uses an object of another Class in its method, and this object is not stored in any field, then this kind of relationship is modeled as a dependency. Changes in one Class will lead to changes in another in UML Database Modeling.
Aggregation
This is a special type of relationship between Classes that occurs when one Class is part of another in UML Database Modeling. It is shown as a continuous line with an unfilled diamond connected to the Class which represents the aggregate. For example, the Order class has a collection of OrderDetails.
Composition
This is a type of aggregation but in this case, the Classes that form the aggregate Class are destroyed when the aggregate Class is deleted in UML Database Modeling.
How to Map Class Model to the Relational Model?
Let us now see how to translate or map from one domain to the other:
1. Model Classes: Designing a new relational database schema from a class model is straightforward since the models are under our control. In real-world scenarios, layering a class model on top of a legacy data model is more challenging. Focus on the first scenario. The class model should capture associations, inheritance, and aggregation.
2. Identify Persistent Objects: Separate the class model into elements requiring persistence and those that do not. In a Model-View-Controller design pattern, only the model section’s classes require persistent state.
3. Class to Table Mapping: Assume each persistent class maps to one relational table, where a class maps to a table and an instance maps to a table row.
4. Select an Inheritance Strategy: Inheritance mapping is complex due to the flat nature of relational models versus deep class hierarchies. Options include:
- One table for the entire hierarchy.
- Separate tables for each class, including inherited attributes.
- Separate tables for each generation’s attributes. The third option is simpler to maintain but requires multiple database calls to construct objects.
5. Add Unique Object Identifiers: Use OIDs for unique identification in the object model, similar to primary keys in the relational model. OIDs simplify identity management and updates, avoiding issues with business-related primary keys.
6. Map Attributes to Columns: Map simple data attributes to columns. For complex attributes, follow the approach for handling associations and aggregation.
7. Map Associations to Foreign Keys: Model associations (e.g., Person lives at Address) by creating foreign keys from child to parent tables, avoiding redundancy and improving maintainability.
8. Map Aggregation and Composition: Aggregation (weak form) models relationships like a Person having multiple Addresses, implemented via foreign keys or intermediate tables. Composition (strong form) requires that deleting the parent deletes the parts, using foreign keys with deletion constraints.
9. Define Relationship Roles: Specify relationship roles for associations, including primary key and foreign key constraints, as well as additional constraints like {Not NULL} and cardinality constraints.
10. Model Behaviour: Decide whether to implement class behavior in object methods or leverage DBMS triggers and stored procedures. Object methods provide cleaner design and better portability, while DBMS scripting languages offer better performance.
11. Produce a Physical Model: Create a physical model to detail deployment, including hardware, network connectivity, software, OS, and components. Assign packages of tables to DBMS components for data placement.
Exploring Various Database Models
- The Hierarchical database model structures data in a tree-like format with segments forming the branches, characterized by one-to-many relationships and predictable data access.
- The Network model is graph-based, with arcs as relationship types and nodes as object types, offering a flexible schema not limited to lattice or hierarchical structures.
- The Object-oriented database model comprises a collection of objects with attributes and methods, ideal for multimedia databases with non-relational data like images or hypertext databases with linkable objects.
- The Relational model organizes data into relations, essentially tables with rows and columns, facilitating structured data management.
- The Object-relational model combines object-oriented and relational model features, supporting objects, classes, inheritance, data types, and tabular structures.
- The Entity-relationship model consists of entity types and their relationships, providing a logical database structure through ER diagrams.
- The Document model is tailored for document or semi-structured data storage, with a tree structure where each node represents a document part.
- The Entity-attribute-value model (EAV) records data in three columns: the entity being described, the attribute, and the attribute’s value.
- The Star schema is a dimensional model variant, organizing data into dimensions and facts, commonly used in business intelligence and data warehousing for querying large datasets.
Conclusion
UML Database Modeling is not so much about a pretty picture, it is actually a powerful tool in the programmers tool kit. If you can understand and use it properly by getting into the habit of drawing your Class diagram before implementing your database, the time invested in drawing a succinct and clear UML diagram will eventually pay off and can save your project from design flaws and other errors you might encounter without a proper plan in front of you. You can draw them with a pen and paper but it’s a lot easier if you make use of a diagramming tool.
However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!
Visit our Website to Explore Hevo
Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.
You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!
Jeremiah is a specialist in crafting insightful content for the data industry, and his writing offers informative and simplified material on the complexities of data integration and analysis. He enjoys staying updated on the latest trends and uses his knowledge to help businesses succeed.
