The conceptual data model

Overview

This model is used for a general view of the data and is independent of a specific DBMS. The Object-based Logical Model represents this model.

Conceptual data model is a mental image of a familiar physical object and are not specific to a database.

To design a conceptual data model, you will follow steps:

• Draw an Entity-Relationship Diagram: To identify attributes and to establish the proper relationship sets.
• Define integrity constraints: Identify and document integriry constraints
• Review the final model: Remove M:N relationships, recursive relationships, super types and so on.

Constraints

A constraint is a requirement that entity sets must satisfy in a relationship.

Types of constraints

• one-to-one(1:1)
• one-to-many(1:M)
• many-to-many(M:M)

Many-to-many relationships are not supported by the relational model and must be resolved by splitting the original M:M relationship set into two 1:M relationship sets. Usually, the unique identifiers of the two entity sets participate in building the unique identifier of the third entity set.

The importance of reviewing the conceptual model

Developing the conceptual data model involves understanding the needs and requirements of the database, and a wide range of technical and business concepts and rules are discussed to set the parameters and scope thereof (TIBCO, n.d.).

The importance of reviewing the conceptual model can be understood by recognizing the next steps you will follow after finishing this phase. First, a logical model will be developed based on the conceptual model, and it creates a highly technical map of underlying rules and data structures (TIBCO, n.d.). The logical model is also involved in validating the conceptual model using the technique of normalization and will remove redundancies (Sharma et al., 2010). Finally, the physical data model will define how the relationships are implemented into the database management system.

Suppose you found some issues with the conceptual data model when your team is working on the next steps. Therefore, the overall architecture or system will be affected, and it can cause a high cost to be modified as the conceptual data model consists of defining the business concepts and rules.

Problems of M:N relatinships

Let's consider a many-to-many relationship between "CUSTOMER" and "INVOICE" entities. customers can have many invoices, and invoices can be obtained by many customers. To successfully manage this relationship, we need to make sure to a unique customer ID refers to another unique invoice ID.

How they should be resolved

The efficient way to realize this, you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table (FileMaker, n.d.).

Each record in a join table (say "ORDER") includes a match field that contains the value of the primary keys of the CUSTOMER and INVOICE table.

Developing the conceptual model

Step 1 - Identify entities

No.	Entity set
1	LIBRARY
2	BOOKS
3	AUTHORS
4	BORROWERS
5	CLIENTS
6	LOANERS

Step 2 - Remove duplicate entities

No.	Entity set	Entity set
1	BOOK	Strong
2	AUTHOR	Strong
3	BORROWER	Strong

Step 3 - List the attributes of each entity set

BORROWER entity set

No.	Entity set	Entity set	Optional
1	BORROWER_ID	Strong	No
2	NAME	Strong	No
3	ADDRESS	Strong	Yes
..	..	..	..

Same for other entity sets.

Step 4 - Choose a unique identifier
BORROWER entity set > BORROWER_ID
..

Step 5 - Define the relationship sets

	Relationship set	Identifying	Left verb	Right verb	Cardinality	Optionally
1	BORROWER -> No BOOK	No	Borrowers	Be borrowered	Many-to-many	May
2	AUTHOR - > BOOK	No	Write	Is written	Many-to-many	May
3	AUTHOR - > BOOK	No	Write	Is written	Many-to-many	May

1. BORROWER - > BOOK.
   The COPY entity set is created as the intermediate entity set to remove the many-to-many relationship set between BORROWER and BOOK. You don't borrow a book, but a copy of it. The new COPY entity set will look like this:

Attribute name	Type	Domain	Optional
COPY_ID	Unique identifier	Text	No
STATUS	Unique identifier	Text	No

The new updated table with relationship sets is:

	Relationship set	Identifying	Left verb	Right verb	Cardinality	Optionally
1	BORROWER -> COPY	No	Borrowers	Be borrowered	One-to-many	May
2	BOOK - > COPY	No	Has	Is created	One-to-many	May
3	AUTHOR - > AUTHOR_LIST	No	Appear	Has	One-to-many	May
..	..

…

Step 6 - Define business rules

Business rules have to be implemented by an application program. In our example, we consider the following rules:

1. Only system administrators can change data
2. Allow the system to handle book reservations. Each borrower can see his position in the waiting list for all books of interest.
3. The application program sends e-mail reminders to the borrower to indicate a book is to be returned within 3 days of sending the e-mail.
4. If the borrower does not return the book on time, he will be fined 0.1% of the book's price per day.

Unary relationship (recursive)

A unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles. See Figure 8.9 for an example.

For some entities in a unary relationship, a separate column can be created that refers to the primary key of the same entity set.

References

TICIBO. (n.d.). What is a Logical Data Model?
https://www.tibco.com/reference-center/what-is-a-logical-data-model

FileMaker. (n.d.). Many-to-many relationships.
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help/many-to-many-relationships.html

Sharma, N., Perniu, L., Chong, R. F., Iyer, A., Nandan, C., Mitea, A. C., Nonvinkere, M. & Danubianu, M. (2010). Database fundamentals. IBM Canada.

ADRIENNE WATT. (n.d.). Chapter 8 The Entity Relationship Data Model: DATABASE DESIGN. BCcampus Open Publishing.,

https://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/

tags: Database