After analyzing the requirements that we’ve covered in the last article, we should design a conceptual data modeling or ERD for the second step in data modeling.
■ What is Conceptual Data Modeling?
The process of conceptually generalizing data to derive structures, types, attributes, relationship constraints, etc. is called conceptual data modeling. In other words, it is a step of deciding what data to store. It prevents errors in which the requirements progressed in the first step are misinterpreted.
A diagram showing data structures and relationships is called the Enterprise Relationship Diagram (ERD). The components of the ER model consist of 1) object 2) relation 3) attributes.
■ Entity
A property that is distinguished from other objects by tangible and intangible objects or concepts existing in the real world is called an Entity. This set of entities is a ‘entity set’.
Let’s take a look at e-commerce site. If a member A sign up, we will have a member DB with information such as name, phone number, address, customer ID, etc.
A entity set called “customers” is created to contain customer information, and information like name, phone number, address, and customer ID becomes an attribute of the entity. In this example, you may have other entities such as “products” and “shipping” for e-commerce site.
■ Relationship
A relationship is literally a relationship between an entity and an entity. For example, there are task of [order/purchese] inbetween “customer entity” and “shipping entity”. The mapping cardinality varies depending on how much relationship each individual entity has.
One-on-one (1:1) vs. One-to-many (1:N) vs. Many-to-one (N:1) vs. Many-to-many (N:N)
1:1 mapping cardinality is where elements are matched 1:1 on each set of entities. When an element in an entity relates to multiple elements in another entity, it is referred to as 1:N mapping cardinality (N:1 mapping cardinality in the opposite case), and the interaction of multiple elements on multiple entities is referred to as N:N mapping cardinality.
In above 1:1 figure, the last square of entity B has nothing to do with the entity A. This is called ‘partial participation’. Entity A is associated with entity B, which is called ‘full participation’. When drawing an ERD, the overall participation is displayed as a double line.
■ Attributes
1) Can we divide it into smaller units? — simple attribute vs. composite attribute
For example, attributes such as customer subscription date can be split smaller by year/month/day, so they are complex attributes.
On the other hand, attributes such as age are simple properties that cannot be split because splitting numbers makes them meaningless.
(2) Can we have more values? — single-value attribute vs.multi-value attribute
If an object can have only one value, we say it as a single value. Attributes such as customer IDs are unique properties of an entity and must be specified as a single value.
On the other hand, customer phone numbers are multi-valued because they can receive multiple values, such as mobile phone numbers, home phone numbers, and company numbers.
In addition, the customer ID is the only value that separates objects, also known as key attributes. Key attributes are underlined when drawing ERD.
(3) Can we infer from other information? — stored attribute vs. derived attribute
Think about the sign up page. If you see lots of blank boxes you have to fill in, you may just press the back button and never return to that site again.
It is important to keep only essential information and keep it concise. This essential information is stored attribute.
Derived attribute is something that can be inffered by other information. For example, you can have an idea on “age” if you have “birthdate”.
■ Special attributes and relationships
A relational set attribute is a attribute that stores values generated by a relationship between two sets of entities. For example, if a “customer” makes an “order”, a set of relationships, ‘order time’, may arise.
A recursive relationship is a relationship in which an entity has a set of relationships with itself. For example, think about ‘1+1 event’. You must purchase at least one product for getting another.
A weak entity is a set in which the existence or absence of entities depends on the entity to which they are related. For example, a customer’s “purchase history” is a weak entity. Because it will be disappeared as soon as the customer leaves the membership.
On the other hand, a strong entity is a general entity set that is associated with a weak entity set. Weak entity sets often have ‘overall participation’ in strong entity sets.
Please refer to the following blog for sample ERD.
The logical ER model contains more detail than the conceptual ER model. In addition to master data entities, operational and transactional data entities are now defined. The details of each data entity are developed and the relationships between these data entities are established.
thank you for the valued comment!