data model assignment

5 Examples of Conceptual Data Models

Shamal is a senior software consultant and educator with 20+ years of experience. He has an MBA from London Metropolitan University and a graduate diploma in IT from the British Computer Society. He also holds professional certification from the Australian Computer Society. Shamal hails from Sri Lanka and loves listening to English, Spanish, and Sinhalese songs.

• conceptual data model
• ER Diagram tool
• example data model

Are you a student or a beginn ing data modeler who wants to learn more about data modeling? Let’s start learning with the conceptual data model – the least detailed of the three models.

In this article, we’ll discuss the conceptual data model using some real-world examples.

As you know, data modeling is the process of describing real-world data – its relationships, features, and attributes – in a structured document like an ER (entity-relationship) diagram. The data model acts as a blueprint that the data architect will use to convert all the data involved in business processes into a database.

In data modeling, we go through three different stages:

• Conceptual data model: The most abstract data model that describes the data elements without much detail.
• Logical data model: A conceptual model with more technical details.
• Physical data model: A logical model with all the details of the physical database (data types, constraints, indexes, schemas, etc.) added.

If you are interested in reading more about these data models, this detailed article is for you.

What Is the Conceptual Data Model? What Is It Used For?

The conceptual data model is the very first and the most abstract data model in the data modeling process. It is a high-level diagram that we use to define, describe, organize, and present data elements and their relationships with relatively few details. Conceptual data models contain only:

• The real-world entities that are our main data elements.
• Their relationships.

This model does not have technical details, such as attributes, data types, etc.

We use the conceptual data model to communicate with the different business people when defining the business requirements of the database and presenting concepts (e.g. for their feedback). We do not use these models to communicate with technical teams. Thus, we use simple terms in a conceptual data model. 

How to Implement a Conceptual Data Model in Vertabelo

Let’s use the Vertabelo online data modeler to demonstrate how to implement a conceptual data model. Its easy-to-use interface and modern features make creating an ER diagram for a basic purchasing model quite simple.

Vertabelo offers two types of diagrams: a logical diagram and a physical diagram. You can create both conceptual and logical models in the logical diagram because there is no clear line between those two models. When we add the technical details, we’ll have a logical data model, which we could use to create the physical data model – but we’ll save that for another article.

First, go to my.vertabelo.com and log in. Next, click on the Create New Document button.

You’ll see the popup menu pictured below. Click on the Create button next to Logical data model :

In the next screen, enter the name for your new logical data model.

I entered my database model name as “Database Model for Purchasing System”. After you enter your model’s name, click START MODELIN G.

Now you’re in Vertabelo’s user-friendly design environment. There are many shortcuts, buttons, and tools that make data modeling easier. We won’t go into all of them here, but I’ll show you just what you need to create a conceptual data model. If you want a detailed look, see this article explaining Vertabelo’s data modeling features .

What will I store in my purchasing system? The entities below are the real-world objects that I’ll need to track in my database. I think you’ll quickly understand what each entity represents:

• Purchasing Order
• Purchasing Order Item
• Good Received Note

And these are the relationships between these entities:

• A Purchasing Order has many Purchasing Order Item s A Purchasing Order Item can belong to only one Purchasing Order . That means the Purchasing Order and Purchasing Order Item entities have a one-to-many relationship.
• Many Purchasing Order s can be sent to one Supplier . Thus, there will be a one-to-many relationship between the Supplier and Purchasing Order And having a Supplier should be mandatory for all Purchasing Order s.
• One Purchasing Order Item should have one Stock Item, but one Stock Item may have zero or many Purchasing Order Item s. That means that between Stock Item and Purchasing Order Item there is a one-mandatory-to-many-optional relationship.
• When ordered items are received for a Purchasing Order , we create a Good Received Note (GRN) for each Purchasing Order Item . Assuming ordered items for a Purchasing Order can be delivered by the supplier either at once or as split deliveries, a Purchasing Order Item can have zero or many Good Received Note s. However, one Good Received Note can belong to only one mandatory Purchasing Order Item . Thus, Purchasing Order Item and Good Received Note have a one-mandatory-to-many-optional relationship.

Now, I can add these entities and relationships to my conceptual diagram using Vertabelo. When you model a conceptual diagram with Vertabelo’s logical diagram tool, you need to disable errors and warnings related to leaving entities’ attributes and identifiers empty. To do that, go to Model Properties . Under Entity , disable the options No attributes (errors) and No identifier (warning) , as shown below.

All done. Here is the conceptual data model for my purchasing system:

Let me show you a few examples of conceptual data models with a brief explanation of their business requirements.

Conceptual Data Model Examples, with ER Diagrams

1. simple employee management system.

This is an ER diagram of a conceptual data model for a simple employee management system. In this company, there are many departments and many employees in each department. Employees are assigned to different projects and can only work on one project at a time; some employees may not have been assigned to any projects. Each employee has a role (i.e. a job role); many employees can have the same role. Also, there may be vacant roles. Each employee has a login to access the company’s common network, but there are different user privileges.

To support these business requirements, I have designed the conceptual data model shown above. It has five entities (Employee, Department, Project, Role, and Login) that reflect the real-world entity information in the physical database. There are four relationships that support the described business scenario:

• Department_Employee : A one-mandatory-to-many-mandatory relationship.
• Project_Employee : A one-optional-to-many-mandatory relationship.
• Role_Employee : A one-mandatory-to-many-optional relationship.
• Employee_Login : A one-mandatory-to-one-mandatory relationship.

2.  Simple Order Management System

This is a conceptual data model for a simple order management system for a wholesale store. Customers send orders to the store, although some customers may not have sent any orders yet. An order has many order items, and each order item is for one product. There may be products that do not have an order. After gathering the products into an order, an invoice will be prepared for that order. An invoice has many invoice items, with one product in each invoice item.

This ER diagram shows how we’ll support the above business requirements. The model has six entities (Customer, Order, Order Item, Product, Invoice, and Invoice Item) that represent the real-world information in our physical database.

There are six relationships in this data model:

• Customer_Order : A one-mandatory-to-many-optional relationship.
• Order_Order Item : A one-mandatory-to-many-mandatory relationship.
• Product_Order Item : A one-mandatory-to-many-optional relationship.
• Order_Invoice : A one-mandatory-to-one-optional relationship.
• Invoice_Invoice Item : A one-mandatory-to-many-mandatory relationship.
• Product_Invoice Item : A one-mandatory-to-many-optional relationship.

It takes only a few minutes to create this conceptual data model in Vertabelo.

3.  Online Shopping App

This is an ER diagram for an online shopping app data model. Customers can sign up for the app, log into the system using their credentials, and shop online. When a customer starts looking for items, the app opens a shopping cart and adds a shopping cart item for each product that the customer adds to their cart. When the customer checks out, the app reads each shopping cart item’s details, updates the shopping cart header, and generates the final bill using the same information. The app also stores each customer’s shopping history.

This ER diagram contains four entities that reflect real-world object information in our physical database – Online Customer , Shopping Cart , Shopping Cart Item , and Product .

There are three relationships required to support the above business scenario:

• Online Customer_Shopping Cart : A one-mandatory-to-many-optional relationship.
• Shopping Cart_Shopping Cart Item : A one-mandatory-to-many-optional relationship.
• Product_Shopping Cart Item : A one-mandatory-to-many-optional relationship.

4.  Simple Library System

This conceptual diagram has been modeled for a Simple Library System, such as you might find in a school library. Students can register as members of the library. Members can borrow books. Each borrowed book is considered a loan. One loan can have only one book. A book can be in zero or one active loans and in zero or many past loans. A member can have zero or many loans (including active loans and past loans).

This is a simple conceptual diagram that has only three entities: Member, Loan, and Book. There are only two relationships:

• Member_Loan: A one-mandatory-to-many-optional relationship.
• Book_Loan: A one-mandatory-to-many-optional relationship.

5.  Hotel Reservation System

This is an ER diagram for a hotel reservation system. In this business scenario, hotels can be registered in the system and customers can reserve rooms in their preferred hotels. Each hotel has many rooms. A customer can make many reservations, and one reservation should belong to one customer. During their stay, a customer can get many services, which will be added to that reservation. When the customer checks out, the final bill is prepared based on their reservation records.

This conceptual data model has six entities ( Hotel , Room , Reservation , Customer , Service , and Bill ) and five relationships, as shown below:

• Hotel_Room : A one-mandatory-to-many-mandatory relationship.
• Room_Reservation : Each room can be in many reservations, including one active reservation and many past reservations. Each reservation can have one or many rooms. Therefore we have a many-mandatory-to-many-optional relationship between the Room and Reservation This can be simplified by creating an association between these entities in the logical diagram stage.
• Reservation_Service : A one-mandatory-to-many-optional relationship.
• Customer_Reservation : A one-mandatory-to-many-optional relationship.
• Customer_Bill : A one-mandatory-to-many-optional relationship.

What Did You Learn About Conceptual Data Modeling?

Data Modeling is the process of organizing real-world data under the scope of a business requirement in a structured manner. The various stages of data modeling – conceptual, logical, and physical models – are represented on documents used in the successive stages of the business system development process. The conceptual model is the least detailed; it is the very first model and the most abstract part of our data modeling process. We use this high-level model to start defining our concept and to communicate with non-technical user groups and business owners.

The logical model adds more technical detail (like data types) and the physical model adds database-specific information. In Vertabelo, you can generate a database from a physical model automatically. If you are interested in discovering more about what Vertabelo can do, see its documentation .

You can apply the concepts we talked about in this article to start your data modeling journey. We’d be happy to hear about your experiences in data modeling, so please share your thoughts in the comments below.

You may also like

How to draw an er diagram online, how to implement a conceptual, logical, and physical data model in vertabelo, explaining an er diagram, with steps and use cases, vertabelo features: logical diagrams, common er diagram mistakes, a data model for a freelancers collective, a public opinion agency data model.

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy .

• Engineering Mathematics
• Discrete Mathematics
• Operating System
• Computer Networks
• Digital Logic and Design
• C Programming
• Data Structures
• Theory of Computation
• Compiler Design
• Computer Org and Architecture

Data Models in DBMS

• Data Mining Models
• Database Objects in DBMS
• Dimensional Data Modeling
• Data Isolation in DBMS
• How DBMS Stores Data?
• Data Replication in DBMS
• Database Languages in DBMS
• Network Model in DBMS
• Candidate Key in DBMS
• Base Properties in DBMS
• Codd's Rules in DBMS
• Database Design in DBMS
• Access Types in DBMS
• Design of Parallel Databases | DBMS
• Designing models in Cassandra
• Relational Model in DBMS
• Data Modeling in System Design
• Declaring Models in Flask
• Django Models

A Data Model in Database Management System (DBMS)  is the concept of tools that are developed to summarize the description of the database. Data Models provide us with a transparent picture of data which helps us in creating an actual database. It shows us from the design of the data to its proper implementation of data.

Types of Relational Models

• Conceptual Data Model
• Representational Data Model
• Physical Data Model

It is basically classified into 3 types:-

1. Conceptual Data Model

The conceptual data model describes the database at a very high level and is useful to understand the needs or requirements of the database. It is this model, that is used in the requirement-gathering process i.e. before the Database Designers start making a particular database. One such popular model is the entity/relationship model (ER model) . The E/R model specializes in entities, relationships, and even attributes that are used by database designers. In terms of this concept, a discussion can be made even with non-computer science(non-technical) users and stakeholders, and their requirements can be understood.

Entity-Relationship Model( ER Model): It is a high-level data model which is used to define the data and the relationships between them. It is basically a conceptual design of any database which is easy to design the view of data.

Components of ER Model:

• Entity: An entity is referred to as a real-world object. It can be a name, place, object, class, etc. These are represented by a rectangle in an ER Diagram.
• Attributes: An attribute can be defined as the description of the entity. These are represented by Ellipse in an ER Diagram. It can be Age, Roll Number, or Marks for a Student.
• Relationship: Relationships are used to define relations among different entities. Diamonds and Rhombus are used to show Relationships.

Characteristics of a conceptual data model

• Offers Organization-wide coverage of the business concepts.
• This type of Data Models are designed and developed for a business audience.
• The conceptual model is developed independently of hardware specifications like data storage capacity, location or software specifications like DBMS vendor and technology. The focus is to represent data as a user will see it in the “real world.”

Conceptual data models known as Domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope

2. Representational Data Model

This type of data model is used to represent only the logical part of the database and does not represent the physical structure of the database. The representational data model allows us to focus primarily, on the design part of the database. A popular representational model is a Relational model . The relational Model consists of Relational Algebra and Relational Calculus . In the Relational Model, we basically use tables to represent our data and the relationships between them. It is a theoretical concept whose practical implementation is done in Physical Data Model. 

The advantage of using a Representational data model is to provide a foundation to form the base for the Physical model

3. Physical Data Model

 The physical Data Model is used to practically implement Relational Data Model. Ultimately, all data in a database is stored physically on a secondary storage device such as discs and tapes. This is stored in the form of files, records, and certain other data structures. It has all the information on the format in which the files are present and the structure of the databases, the presence of external data structures, and their relation to each other. Here, we basically save tables in memory so they can be accessed efficiently. In order to come up with a good physical model, we have to work on the relational model in a better way. Structured Query Language (SQL) is used to practically implement Relational Algebra.

This Data Model describes HOW the system will be implemented using a specific DBMS system. This model is typically created by DBA and developers. The purpose is actual implementation of the database.

Characteristics of a physical data model:

• The physical data model describes data need for a single project or application though it maybe integrated with other physical data models based on project scope.
• Data Model contains relationships between tables that which addresses cardinality and nullability of the relationships.
• Developed for a specific version of a DBMS, location, data storage or technology to be used in the project.
• Columns should have exact datatypes, lengths assigned and default values.
• Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are defined

Some Other Data Models

1. hierarchical model.

The hierarchical Model is one of the oldest models in the data model which was developed by IBM, in the 1950s. In a hierarchical model, data are viewed as a collection of tables, or we can say segments that form a hierarchical relation. In this, the data is organized into a tree-like structure where each record consists of one parent record and many children. Even if the segments are connected as a chain-like structure by logical associations, then the instant structure can be a fan structure with multiple branches. We call the illogical associations as directional associations.

2. Network Model

The Network Model was formalized by the Database Task group in the 1960s. This model is the generalization of the hierarchical model. This model can consist of multiple parent segments and these segments are grouped as levels but there exists a logical association between the segments belonging to any level. Mostly, there exists a many-to-many logical association between any of the two segments.

3. Object-Oriented Data Model

In the Object-Oriented Data Model , data and their relationships are contained in a single structure which is referred to as an object in this data model. In this, real-world problems are represented as objects with different attributes. All objects have multiple relationships between them. Basically, it is a combination of Object Oriented programming and a Relational Database Model.

4. Float Data Model

The float data model basically consists of a two-dimensional array of data models that do not contain any duplicate elements in the array. This data model has one drawback it cannot store a large amount of data that is the tables can not be of large size.

5. Context Data Model

The Context data model is simply a data model which consists of more than one data model. For example, the Context data model consists of ER Model, Object-Oriented Data Model, etc. This model allows users to do more than one thing which each individual data model can do.

6. Semi-Structured Data Model

Semi-Structured data models deal with the data in a flexible way. Some entities may have extra attributes and some entities may have some missing attributes. Basically, you can represent data here in a flexible way.

Advantages of Data Models

• Data Models help us in representing data accurately.
•  It helps us in finding the missing data and also in minimizing Data Redundancy.
• Data Model provides data security in a better way.
• The data model should be detailed enough to be used for building the physical database.
• The information in the data model can be used for defining the relationship between tables, primary and foreign keys, and stored procedures.

Disadvantages of Data Models

• In the case of a vast database, sometimes it becomes difficult to understand the data model.
• You must have the proper knowledge of SQL to use physical models.
• Even smaller change made in structure require modification in the entire application.
• There is no set data manipulation language in DBMS.
• To develop Data model one should know physical data stored characteristics.
• Data modeling is the process of developing data model for the data to be stored in a Database.
• Data Models ensure consistency in naming conventions, default values, semantics, security while ensuring quality of the data.
• Data Model structure helps to define the relational tables, primary and foreign keys and stored procedures.
• There are three types of conceptual, logical, and physical.
• The main aim of conceptual model is to establish the entities, their attributes, and their relationships.
• Logical data model defines the structure of the data elements and set the relationships between them.
• A Physical Data Model describes the database specific implementation of the data model.
• The main goal of a designing data model is to make certain that data objects offered by the functional team are represented accurately.
• The biggest drawback is that even smaller change made in structure require modification in the entire application.
• Reading this Data Modeling tutorial, you will learn from the basic concepts such as What is Data Model? Introduction to different types of Data Model, advantages, disadvantages, and data model example.

Please Login to comment...

Similar reads.

• DBMS-ER model
• DBMS-Relational Model

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

Data & Finance for Work & Life

What is data modeling? A Visual Introduction with Examples

In their simplest form, data models are diagrams that show 3 dimensions: 1. what data an organization collects, 2. in which section of the organization it is collected, and 3. how each section’s data relates to others. Data modeling (modelling) is the process of creating those data models.

But for most people, this description isn’t very helpful, and it makes things seem more complex than they really are. I believe data models are easiest to understand in context, so let’s consider a business.

Businesses consist of three fundamental units: suppliers , products , and customers . Each of these units consists of data collected independently . For example, the sales team records customer name, location, and sale amount , whereas the production teams records product name, price, and size .

Businesses usually want to compile this data in a company database , which allows them to analyze it and better serve their customers.

To do so, they develop data models to understand what data they have, where it is, and how it all relates. Only then are they confident enough to put it in database. Since every business is different, data modeling is the process of creating a data model that meets the specific structure of the business in question.

Data models: data tables, data objects, & databases

To understand data models and how to model them, you need to know about data tables , data objects and databases .

A data table is a table of columns and rows in which the left most column is a unique ID (aka primary key ), and the columns to its right are characteristics of that unique ID. Data tables are what most people think of when they hear “data.” For example, look at this data table of vendor data:

A database consists of many data tables either compiled into one big table or stored individually. The reason we consider multiple individual tables as part of one database is that they relate to each other. If not, they’re just different tables in space.

Don’t forget, you can get the free Intro to Data Analysis eBook , which covers data fundamentals (including models, tables, and objects).

The important thing to see here is that one database can also have multiple data models . As we said, a data model is just a combination of data tables that relate to each other. Once you store them together, they’re a model. And since databases can store a huge number of data tables, it can store multiple models. This sounds complicated, but it’s easy to understand with a picture:

Data object is another name for data tables within a database. The reason we use a separate names is that, at more advanced levels, data models contain objects other than tables (bit that’s outside the scope of this article). You will often hear or read data analysts refer to “data objects.” Nine time out of ten, they’re referring to tables, but you should be aware that there are others as you progress as a data analyst.

Types of Data Models with Real-World Example

So, we know that data models are pictorial representation of the contents and relationships of data tables. But there is not a on-size-fits-all data model, especially in a business.

C-level executives don’t want to see the gritty details behind the model. They just want to see the high-level relationships. At the same time, a database analyst wants as much detail as possible to ensure the relationships are correct.

The need for different views has led to three primary types of data models:

• Conceptual Data Models. High-level.
• Logical Data Models. Mid-level.
• Physical Data Models. Low-level.

Though they differ, each of these models consists of at least one of these elements:

• Entities. Entities represent data tables (or more generally, data objects) that contain data relevant for comparison.
• Primary keys . Primary keys are another word for the unique ID of the data table within an entity.
• Attributes . Attributes show additional information stored under each unique ID in an entity.
• Relationships . Relationships are shown by lines and symbols and explain how entities interact. The most common are “one to many,” “one to one,” and “many to many.” Relationships are also referred to as cardinality . The notation for these relationships is called crows foot notation , and it’s very simple. Here are the most important examples:

Real-World Example

Imagine you own a wholesale e-commerce company that sells watches, and its called Batch Watch . Your three business units are vendors, products, and customers. You buy metal and glass from the vendors to build your high-quality watches, then sell them to boutiques and other retail stores. And let me tell you… people love your watches!

Let’s look at conceptual, logical, and physical data models using this example.

Conceptual Data Model

Conceptual models are the most general of the three. You don’t need to be a data analyst to understand them. Conceptual data models show the high-level business units that collect data, but do not show any information about the contents . They sometimes include pictures to more easily communicate their structure.

Using our Batch Watch example, a conceptual model may look as simple as this:

Logical Data Model

Logical models go a step further than conceptual models to show the primary key and attributes within each entity, as well as the relationships between them. Logical data models are the most common type of data model.

To understand logical data models, let’s look at this example of our three entities in Batch Watch to understand their primary keys and attributes. Then we can see how they’re related with crows feet notation.

As you can see, the primary keys (PKs) for each entity are a unique ID of the key component. The attributes under them provide a view on what data is stored within these data tables.

We saw a simplistic view of relationships in the conceptual model, and now we’ll add more detail with the crows feet notation . What will this help us understand? It will show how many primary keys in each entity links into primary keys in the other entities. Remember, the most common of these is “one to many.”

In the case of Batch Watch , each vendor supplies general materials for many of our watches ( product ), OR only one of our watches. This is because one watch requires a special kind of glass. Each product then sells to many retailers .

Using crows feet notation, it looks like this in a data model:

The logical model thus helps us understand that “one and only one” Vendor ID (along with their attributes) links into “one or many” Product IDs. Then, one and only one Product IDs link into many Retailer IDs.

Note: “many to many” relationships do not exist in data models

When analysts learn about crows feet relationships, they often get stuck on the idea of “many to many” relationships. After all, if one PK in Entity A links to many PKs in Entity B, aren’t there already “many” combinations? The answer is yes, “many to many” relationships exist, but they’re already accounted for in multiple “one to many” relationships .

Don’t let this confuse you (most professionals have a hard time explaining it). Just know that in data modeling, we do not use “many to many” relationships. Instead, whenever more than one PK links into other PKs, we simplify. We use two entity relationships of one to many.

From Logical Model to Database

The logical model is great, but it’s difficult to understand without seeing what it looks like once all of the entity’s data tables are linked together in a database .

Imagine we have only two vendors , three products , and four retail partners . Even with so few players, the database becomes complex, and quickly.

Here’s what it would look like using our example. I leave out the attributes in this picture so it’s easier to understand how this database is compiled:

This complexity is arguably the most important reason for using data models. They simplify the relationships between business units and entities so that it’s digestible, and easy to act on. Without them, it would be difficult to work with databases at all!

Why use a data model?

If they’re so complex, why even bother with databases and data models at all? The most obvious reason is that data is key to extracting insights and improving a company’s competitive edge .

When companies ignore data, they miss out on opportunities to understand their operations, markets, and customers better.

Moreover, data is becoming a compliance necessity . Businesses must be able to show how their company operates through data to be compliant with growing government data regulations.

Physical Data Model

Once you understand logical models, physical data models are easy. Physical data model entities are exactly the same as logical model entities, but they add in the types of data that each PK an attribute uses, as well as the number of characters . Here’s an example:

As you can see, the added value of physical models is the detail they provide on data in its tables. Experienced data modelers are able to quickly understand how the data model translates to the database and make decisions based on this knowledge .

Types of data are seemingly endless. The most common types include text , numeric , and boolean (true/false), but they can be as complex as the following list:

• Integer – any number that is not a decimal. Examples include -11, 34, 0, 100.
• Tinyint – an integer, but only numbers from 0 to 255
• Bigint – an integer bigger than 1  trillion
• Float – numbers too big to write out, and the scientific method is needed
• Real – any fixed point on a line
• Date – the date sorted in different forms, including “mm/dd/yyyy” (US), “dd/mm/yyyy” (Europe), “mmmm dd, yyyy”, and “mm-dd-yy” among many more.
• Time – the time of day, broken down as far as milliseconds
• Date time – the date and time value of an event
• Timestamp – stores number of  seconds passes since  1970-01-01 00:00:00’ UTC
• Year – stores years ranging from 1901 to 2155 in two-digit or four-digit ranges
• Char – fixed length of characters, with a maximum of 8,000
• Varchar – max of 8,000 characters like char, but each entry can differ in length (variable)
• Text – similar to varchar, but the maximum is 2GB instead of a specific length
• nchar – fixed length with maximum length of 8,000 characters
• nvarchar – variable length with maximum of 8,000 characters
• ntext – variable length storage, only now the maximum is 1GB rather than a specific length
• binary – fixed length with maximum of 8,000  bytes
• varbinary – variable length storage with maximum bytes, topped at 8,000
• clob – also known as  C haracter  L arge  O bject, is a type of sub-character that carries Unicode texts up to 2GB
• blob – carries big binary objects
• xml – a specific data type that stores XML data. XML stands for extensible markups language, and is common in data bases

Data Modeling tools

To build all three types of data models, you will need a tool. The most important one to have in your toolbox is Microsoft Powerpoint. While it is heavily manual, its available in almost every professional setting. Especially as a a consultant, you need to be flexible.

With that said, the best tools for data models are ERP modeling tools and UML modeling language . They’re common among systems administrators and software engineering, where structures similar to data models are an everyday event.

• Entity Relationship (E-R) Model programs. As you can imagine, structural models are not unique to data models. In fact, the idea of entities and relationships is a driving principle in engineering . That’s where E-R models surfaced, as well as the programs to automate them. These programs are user friendly and require minimal coding skills that you can learn as you go . While a tutorial on E-R technology is outside the scope of this article, you can check out a trial account with Lucidchart for free if you want to get your feet wet. It’s common in big companies.
• UML (Unified Modeling Language). UML is a “coding” language for entity relationship models. I put “coding” in quotes because it’s actually called a mockup language , but the principle is the same. You use a program to write code that becomes the model. For many people, coding separately to build a model that represents an underlying database feels like overkill. For this reason, UML is considered an advanced technique.

Data modeling steps

We’ve talked a lot about types of data models, their content, and why we should use them, but what about actually building one ? What are the steps needed to build a data model, or “do” data modeling?

To answer this question, let’s take the perspective of an external data consultant rather than an internal analyst (since internal specialists can sometimes be biased).

The most important and first step is understanding the organization and its data collection capabilities and desires . Without data, there isn’t much to model.

Then, the consultant must understand the goals of the organization and set up a data collection plan to be approved by business decision-makers. That’s the bird’s eye view.

More specifically, 12 steps to data modeling are:

• Understand what kinds of data analysis and data insights the company is looking for . This is a crucial step. It consists of speaking with decision-makers to better understand
• Identify key business units. This step consists of identifying the most important business units. These are not necessarily departments, as products are a key unit as well. A good test is to ask yourself: which units do business units usually refer to when they ask “why”?
• Perform a data collection audit. This step consists of identifying which business units need data in order to build the business model. In almost all cases, they will be the units identified in step one, but not always. Business units without data collection, or without sufficient data collection, should be noted in a text document. A good test is to ask yourself: what data dimensions will this unit need to answer the “why” questions from business decision-makers?
• Perform a data collection GAP analysis. This step consists of identifying what technical and non-technical changes must be made in order to execute the data collection requirements identified in step 3.
• Build a draft conceptual data model. This step is the first model you build. It’s the conceptual model mentioned earlier in the article .
• Get feedback for this structure from management. In this step you get a critical view on how well your work responds to the decision-maker requirements identified in step 1.
• Adapt to feedback from management. Make changes to you conceptual model based on feedback from decision-makers.
• Build logical data model. This step consists of building a logical model with the information gathered and feedback. We move on from the conception model even without managerial approval because it’s better to adapt progressively than get stuck on the conceptual model.
• Get feedback and make adjustments. Repeat step six, but with the logical model. While decision makers may not want to see PKs and attributes, the conceptual structure remains, and it’s useful to get additional feedback and approval.
• Create physical data model to share with database management teams and BI. Using either an E-R modeling program or UML, build a physical model to share with relevant teams.
• Implement data collection improvements. This step consists of implementing the technical and non-technical requirements identified in step 4’s GAP analysis. You may need to work with external providers for this.
• Build dashboard. Build a dashboard to show the conceptual, logical, and physical models in a user-friendly framework for everyone in the company. Dashboard creation is often possible through the database management system the company stores its data in.

Techniques and best practices

Data modeling best practices include the following items:

• Where possible, use a single Enterprise Resource Planning (ERP) program to ensure ongoing integrity of data collection and modeling process.
• Always document decision-maker requirements to ensure coherence throughout the implementation process.
• Do not use both UML and E-R model programs. Choose one and stick with it. Since data collection and data modeling are an ongoing effort, you want it to keep it as user friendly as possible.

Advantages and disadvantages

While data modeling is an industry standard, it has its disadvantages. We’ve spoken a lot about the good parts, but here’s an overview of the advantages and limitations of data modeling:

Advantages:

• Easy to access
• Creates structure for an organization
• Flexible to the needs of any organization
• Ensures the integrity of data by splitting

Disadvantages:

• Compounding complexity. As company entities and the data stored in them become more complex, so does the data model. In fact, it can become so complex that it looses its simplistic appeal.
• Rigidity . Once a data model is put in place, it is incredibly difficult to modify. This article described the steps to set up a data model, but maintaining and modifying one is another story.
• Dependance with growth . Just like any web of logic, any change to one element has an impact on many others. While the purpose of the model is to limit this risk, as the business units grow, so too does the difficulty of maintaining entity independence.

Data modeling is a must-know technique for any good data analyst. It’s a window into the complex database that hosts any company’s data. While it may seem intimidating at first, you’ll quickly adjust to the logic as you spend more time with different materials.

At AnalystAnswers.com, I believe that data analysis is becoming more and more critical in our digital world. Learning it shouldn’t break the bank, and everyone should have access to understanding the data that’s growing under our fingertips every day.

To learn more about data analysis, check out the Understand Data Analysis tab!

About the Author

Noah is the founder & Editor-in-Chief at AnalystAnswers. He is a transatlantic professional and entrepreneur with 5+ years of corporate finance and data analytics experience, as well as 3+ years in consumer financial products and business software. He started AnalystAnswers to provide aspiring professionals with accessible explanations of otherwise dense finance and data concepts. Noah believes everyone can benefit from an analytical mindset in growing digital world. When he's not busy at work, Noah likes to explore new European cities, exercise, and spend time with friends and family.

File available immediately.

Notice: JavaScript is required for this content.

Want to create or adapt books like this? Learn more about how Pressbooks supports open publishing practices.

Chapter 5 Data Modelling

Adrienne Watt

Data modelling is the first step in the process of database design. This step is sometimes considered to be a high-level and abstract design phase, also referred to as conceptual design . The aim of this phase is to describe:

• The data contained in the database (e.g., entities: students, lecturers, courses, subjects)
• The relationships between data items (e.g., students are supervised by lecturers; lecturers teach courses)
• The constraints on data (e.g., student number has exactly eight digits; a subject has four or six units of credit only)

In the second step , the data items, the relationships and the constraints are all expressed using the concepts provided by the high-level data model. Because these concmepts do not include the implementation details, the result of the data modelling process is a (semi) formal representation of the database structure. This result is quite easy to understand so it is used as reference to make sure that all the user’s requirements are met.

The third step is database design . During this step, we might have two sub-steps: one called database logical design , which defines a database in a data model of a specific DBMS, and another called database physical design , which defines the internal database storage structure, file organization or indexing techniques. These two sub-steps are database implementation and operations/user interfaces building steps.

In the database design phases, data are represented using a certain data model. The data model is a collection of concepts or notations for describing data, data relationships, data semantics and data constraints. Most data models also include a set of basic operations for manipulating data in the database.

Degrees of Data Abstraction

In this section we will look at the database design process in terms of specificity. Just as any design starts at a high level and proceeds to an ever-increasing level of detail, so does database design. For example, when building a home, you start with how many bedrooms and bathrooms the home will have, whether it will be on one level or multiple levels, etc. The next step is to get an architect to design the home from a more structured perspective. This level gets more detailed with respect to actual room sizes, how the home will be wired, where the plumbing fixtures will be placed, etc. The last step is to hire a contractor to build the home. That’s looking at the design from a high level of abstraction to an increasing level of detail.

The database design is very much like that. It starts with users identifying the business rules; then the database designers and analysts create the database design; and then the database administrator implements the design using a DBMS.

The following subsections summarize the models in order of decreasing level of abstraction.

External models

• Represent the user’s view of the database
• Contain multiple different external views
• Are closely related to the real world as perceived by each user

Conceptual models

• Provide flexible data-structuring capabilities
• Present a “community view”: the logical structure of the entire database
• Contain data stored in the database
• Constraints
• Semantic information (e.g., business rules)
• Security and integrity information
• Consider a database as a collection of entities (objects) of various kinds
• Are the basis for identification and high-level description of main data objects; they avoid details
• Are database independent regardless of the database you will be using

Internal models

The three best-known models of this kind are the relational data model, the network data model and the hierarchical data model. These internal models:

• Consider a database as a collection of fixed-size records
• Are closer to the physical level or file structure
• Are a representation of the database as seen by the DBMS.
• Require the designer to match the conceptual model’s characteristics and constraints to those of the selected implementation model
• Involve mapping the entities in the conceptual model to the tables in the relational model

Physical models

• Are the physical representation of the database
• Have the lowest level of abstractions
• Run-time performance
• Storage utilization and compression
• File organization and access methods
• Data encryption
• Are the physical level – managed by the operating system  (OS)
• Provide concepts that describe the details of how data are stored in the computer’s memory

Data Abstraction Layer

In a pictorial view, you can see how the different models work together. Let’s look at this from the highest level, the external model.

The external model is the end user’s view of the data. Typically a database is an enterprise system that serves the needs of multiple departments. However, one department is not interested in seeing other departments’ data (e.g., the human resources (HR) department does not care to view the sales department’s data). Therefore, one user view will differ from another.

The external model requires that the designer subdivide a set of requirements and constraints into functional modules that can be examined within the framework of their external models (e.g., human resources versus sales).

As a data designer, you need to understand all the data so that you can build an enterprise-wide database. Based on the needs of various departments, the conceptual model is the first model created.

At this stage, the conceptual model is independent of both software and hardware. It does not depend on the DBMS software used to implement the model. It does not depend on the hardware used in the implementation of the model. Changes in either hardware or DBMS software have no effect on the database design at the conceptual level.

Once a DBMS is selected, you can then implement it. This is the internal model. Here you create all the tables, constraints, keys, rules, etc.  This is often referred to as the logical design .

The physical model is simply the way the data is stored on disk. Each database vendor has its own way of storing the data.

A schema is an overall description of a database, and it is usually represented by the  entity relationship diagram (ERD) . There are many subschemas that represent external models and thus display external views of the data. Below is a list of items to consider during the design process of a database.

• External schemas: there are multiple
• Multiple subschemas: these display multiple external views of the data
• Conceptual schema: there is only one. This schema includes data items, relationships and constraints, all represented in an ERD.
• Physical schema: there is only one

Logical and Physical Data Independence

Data independence refers to the immunity of user applications to changes made in the definition and organization of data. Data abstractions expose only those items that are important or pertinent to the user. Complexity is hidden from the database user.

Data independence and operation independence together form the feature of data abstraction. There are two types of data independence: logical and physical.

Logical data independence

A logical schema is a conceptual design of the database done on paper or a whiteboard, much like architectural drawings for a house. The ability to change the logical schema, without changing the external schema  or user view,   is called logical data independence . For example, the addition or removal of new entities, attributes or relationships to this conceptual schema should be possible without having to change existing external schemas or rewrite existing application programs.   

In other words, changes to the logical schema (e.g., alterations to the structure of the database like adding a column or other tables) should not affect the function of the application (external views).

Physical data independence

Physical data independence refers to the immunity of the internal model to changes in the physical model. The logical schema stays unchanged even though changes are made to file organization or storage structures, storage devices or indexing strategy.

Physical data independence deals with hiding the details of the storage structure from user applications. The applications should not be involved with these issues, since there is no difference in the operation carried out against the data.

conceptual schema : another term for logical schema

data independence : the immunity of user applications to changes made in the definition and organization of data

data model :a collection of concepts or notations for describing data, data relationships, data semantics and data constraints

data modelling : the first step in the process of database design

database logical design :  defines a database in a data model of a specific database management system

database physical design :  defines the internal database storage structure, file organization or indexing techniques

entity relationship diagram (ERD) : a data model describing the database showing tables, attributes and relationships

external model :  represents the user’s view of the database

external schema : user view

internal model:   a representation of the database as seen by the DBMS

logical data independence : the ability to change the logical schema without changing the external schema

logical design : where you create all the tables, constraints, keys, rules, etc.

logical schema : a conceptual design of the database done on paper or a whiteboard, much like architectural drawings for a house

operating system (OS) : manages the physical level of the physical model

physical data independence : the immunity of the internal model to changes in the physical model

physical model :  the physical representation of the database

schema : an overall description of a database

• Describe the purpose of a conceptual design.
• How is a conceptual design different from a logical design?
• What is an external model?
• What is a conceptual model?
• What is an internal model?
• What is a physical model?
• Which model does the database administrator work with?
• Which model does the end user work with?
• What is logical data independence?
• What is physical data independence?

Also see Appendix A: University Registration Data Model Example

Attribution

This chapter of  Database Design  is a derivative copy of  Database System Concepts  by Nguyen Kim Anh licensed under  Creative Commons Attribution License 3.0 license

The following material was written by Adrienne Watt:

• Some or all of the introduction, degrees of data abstraction, data abstraction layer

Database Design - 2nd Edition Copyright © 2014 by Adrienne Watt is licensed under a Creative Commons Attribution 4.0 International License , except where otherwise noted.

Share This Book

Browse Course Material

Course info, instructors.

• Prof. Robert Freund
• Prof. Cynthia Rudin
• Juan Pablo Vielma Centeno

Departments

• Sloan School of Management

As Taught In

• Operations Management
• Supply Chain Management
• Data Mining
• Systems Optimization
• Probability and Statistics

Learning Resource Types

Data, models, and decisions, assignments, homework assignments.

Homework 1: Probability (PDF)

Homework 2: Simulation (PDF)

Homework 3: Regression I (PDF) ; EXEC-PAY Spreadsheet (XLS) ( PDF )

Homework 4: Regression II (PDF) ; Hubbard Brewing Company Spreadsheet (XLS) ( PDF ); this assignment was optional.

Homework 5: Linear Optimization (PDF)

Homework 6: Production Management (PDF) ; Jordon Alloy Table 7.38 (XLS) ( PDF )

Homework 7: Discrete Optimization (PDF) ; Dream Team Spreadsheet (XLS) ( PDF )

Team and Individual Case Assignments

[DMD] = Bertsimas, Dimitris, and Robert Freund. Data, Models, and Decisions: The Fundamentals of Management Science . Dynamic Ideas, 2004. ISBN: 9780975914601.

Team Assignments

“Time-Critical Management of Airline on Ground (AOG) at Latin Airlines.” Analyze and hand in your analysis of the case (one case per team).

Case Write-up: Read the case “Vermont City Electric.” Analyze and hand in your analysis of the case (one case per team).

Individual Assignments

Ontario Gateway Case: Read the Ontario Gateway Case (at the end of Chapter 5 in [DMD]). Perform the case analysis modeling assignment for the Ontario Gateway Case described at the end of the case. Hand in a management memorandum presenting your conclusions with appropriate but concise back-up enclosures to support your recommendations.

Case Write-up: Read the case “Eurotel 3G License Valuation.” Perform the analyses outlined at the end of the case and hand in a memorandum presenting your findings and conclusions. Your memorandum should include appropriate but concise exhibits to support your recommendations.

Filatoi Riuniti Case: Read and analyze the Filatoi Riuniti case at the end of Chapter 7 in [DMD], and (as described in Homework 6) construct a linear optimization model. Write up and hand in your answers to questions (a), (b), (c), (f), (g), (h), and (i), at the end of the case.

Case Write-up: Read the case “Endurance Investors” at the end of Chapter 8 in [DMD]. Perform the analyses outlined at the end of the case and hand in your answers to questions (a) through (j) in the assignment section of the case.

International Industries Case: Read (but do not hand in) the case “International Industries, Inc.” at the end of Chapter 9 in [DMD]. We will discuss the International Industries Case in class.

You are leaving MIT OpenCourseWare