Online College Courses for Credit

Conceptual Design

Conceptual Design

Author: Sophia Tutorial

List the main steps of the conceptual design.

See More

what's covered
This tutorial explores the steps in the conceptual model design in two parts:
  1. Getting Started
  2. Running A Query

1. Getting Started

In a relational model, we have three different types of data models with the conceptual, logical, and physical data models. Each of them has its purpose and builds on the next data model. The conceptual data model defines what the database will contain. Typically, this model would be created by the business stakeholders and the data architects. Their main goal with this data model is to organize the data, define the scope of the database and define what the business concepts and rules will be.

The conceptual model‘s purpose is to establish the entities, their attributes, and the relationships between the entities. In this data modeling level, there is not a lot of detail that is added to the actual database structure. There are some basic design steps involved in this process that are performed during this phase as this will be the foundation to the logical and physical data models.

The first step will be to discover the characteristics of the data elements. We have to start posing some questions about what the reports and queries would need to contain. We also need to know who the end-users are and how that information will be used. Knowing what the various end-user data views are going to be will also help. We’ll also need to know where the information is to be found and how the information is meant to be extracted when it is. Using those criteria, we can then determine what data elements are needed to produce the information along with what the data attributes will be. We’ll also define the relationships that would exist in the data, how frequently the data is to be used and what the data transformations may need to be to generate some of that information.

In order to answer some of the criteria about the characteristics, it can be useful to have the database design and the end-users create a description of what the end-user data views are going to be as that will help identity what the main data elements are. If there’s an existing system in place whether it is a manual process or an application, this can be very useful to identify the data and its characteristics. By taking a look at the various forms and files, we can determine what the data types may need to be, and the volume associated with it. If there’s already an automated system in place, we can also take a look at what the current reports look like as well determine what the designed reports would consist of. If an application is being developed in conjunction with the database design, it would also be useful to review the application designs for insight into the data elements.

The business rules in particular are important to determine the entities, attributes, relationships, connectivity, cardinalities, and constraints in the database. These business rules need to be simple and precise. In looking at our database, we could define some of the following business rules:

  • An artist can record more than one album.
  • An album can only list one artist.
  • An album can store multiple tracks.
  • A track can only belong to a single genre.
  • A track can only be sold under a single media type.
  • An employee can be the main support representative of many customers.
  • A customer can only have one main support representative.
As you can see, these business rules start to show what the entities are and what the key attributes and relationships will be. They also in many cases start to define the cardinality between the entities. It is important that the business rules are accurate and are vetted by the business end-user and stakeholders. Having incorrect business rules can create underlying problems in the database that will affect the applications that rely on the database.

2. Running A Query

Let us take a look at a sample of what a conceptual model may look like for a simple eCommerce site. The business rules are as follows:

  • A customer can place many orders.
  • A customer uses a credit card to pay for the orders.
  • An order can only belong to a single customer.
  • An order can consist of many products.
  • A product can be purchased by many customers.
  • A product can belong to a single category.
  • A category can consist of many products.
A sample conceptual model would look like the following:

Sample Conceptual Model

Notice the simplicity of the entities where the focus is on the entities, the top-level attributes, and the cardinality. Between a customer and order is a one-to-many relationship. Between order and product is a many-to-many relationship. A category to a product is a one-to-many relationship.

The conceptual model is used to gather business requirements. It is the simplest model across the logical and physical.

Source: Authored by Vincent Tran