The conceptual view of the database is often called

Conceptual Data Model

The conceptual data model is a structured business view of the data required to support business processes, record business events, and track related performance measures. This model focuses on identifying the data used in the business but not its processing flow or physical characteristics. This model’s perspective is independent of any underlying business applications. For example, it allows business people to view sales data, expense data, customers, and products—business subjects that are in the integrated model and outside of the applications themselves.

The conceptual data model represents the overall structure of data required to support the business requirements independent of any software or data storage structure. The characteristics of the conceptual data model include:

An overall view of the structure of the data in a business context.

Features that are independent of any database or physical storage structure.

Objects that may not ever be implemented in physical databases. There are some concepts and processes that will not find their way into models, but they are needed for the business to understand and explain what is needed in the enterprise.

Data needed to perform business processes or enterprise operations.

The conceptual data model is a tool for business and IT to define:

Data requirements scope.

Business terms and measures across different business units and those that are agreed upon for enterprise-wide usage.

Names, data types, and characteristics of entities and their attributes.

4.1.5.1 Conceptual Data Models

The somewhat less traditional view of data modeling begins with conceptual data modeling. Conceptual data models utilize a standard system of symbols that form a formal, although uncomplicated language that communicates an abundance of knowledge about the information being modeled. This uncomplicated visual language is effective for communicating the business users’ view of the data they work with.

The system of symbols employed in conceptual data model borrows a number of the basic modeling constructs found in entity relationship diagrams (ERDs), containing entities, attributes, and relationships.

The characteristics of conceptual data models that are specific to it include the following:

The objective of the model is to communicate business knowledge to any individuals who are unfamiliar to the business.

The scope of the model is from the perspective of a business subject area of data, as opposed to the scope of an automation project, automation application, automation database, or automation interface.

The names of the objects in the model are strictly restricted to language used within the business, excluding any and all technical terminology related to automation jargon.

Diagramming conventions are that which emphasize what an individual can comfortably view and comprehend on an individual page.

Business data points are simply associated with the data objects they would belong to and are not taken through the data engineering process called “normalization” to separate attributes into code tables.

Data abstractions, such as referring to business objects in a more generic and general way, are not performed as they often lose the business intent and then become less recognizable to the business.

Technical details, frequently found within ERDs, such as optionality and specific numerical cardinalities, are omitted.

The modern approach to conceptual data models is to incorporate them as a natural extension of the LDA. In fact, each conceptual data model should correspond to one business subject area of data and should be developed by business users who have been mentored by information architects to assist in the upkeep of the LDA.

Conceptual Business Model

Explained simply, a conceptual data model (CDM) shows (in graphic and text form) how the business world sees information it uses. It often suppresses or blurs details in order to emphasize on the big picture. Conceptual data modeling is one of the most powerful and effective analytical techniques for understanding and organizing the information required to support any organization. This form of model focuses on the big picture, and the really important strategic objectives that will ensure prosperity for the organization. Data are shared across both functional and organizational boundaries in the business. As a result, this is critical for removing redundant data and process in the conduct of the organization’s processes by increasing shared data use and encouraging process reuse.

There are a number of basic steps involved in conceptual business modeling. It is, of course, an exercise in the gathering of requirements from a user environment. The difference between conceptual models and lower-level models is detail. To put it simply, conceptual models are highly abstracted, architectural-type views of the business area. At their level they capture the major entities and how they might be related together. The conceptual data model is not specific in nature but is generic. The relationships within it are not made explicit as to type or cardinality. They are just present. Domain constraint data (that set of limits placed upon reference domain data or validation data) are not included. This model is only intended to capture the highest level of business use so there is an understanding of what the process is. It is accompanied by a high-level activity hierarchy or functional decomposition diagram that depicts the major functionality that is accomplished in the business problem area.

The functional decomposition diagram is a hierarchical structure that identifies, defines, and logically groups the business functions that are performed by the current system. It isolates the processes; it shows no data inputs, outputs, data stores, or sources of information. The principal objective of the FDD is to show the primitive functions of the system for which logic is to be specified. It will be further examined and analyzed in much greater detail in the next phase of this project: logical model development.

3.1.3 Conceptual Data Model

As with logical data models, there are some differing opinions about what a conceptual data model is. So again, I will state the way that I understand the term and then identify some key variations I have noticed.

A conceptual data model is a model of the things in the business and the relationships among them, rather than a model of the data about those things. So in a conceptual data model, when you see an entity type called car, then you should think about pieces of metal with engines, not records in databases. As a result, conceptual data models usually have few, if any, attributes. What would often be attributes may well be treated as entity types or relationship types in their own right, and where information is considered, it is considered as an object in its own right, rather than as being necessarily about something else. A conceptual data model may still be sufficiently attributed to be fully instantiable, though usually in a somewhat generic way.

Variations in view seem to focus on the level of attribution and therefore whether or not a conceptual data model is instantiable.

A conceptual data model might include some rules, but it would not place limits on the data that can be held about something (whether or not it was instantiable) or include derived data.

The result of this is that it is possible for a conceptual data model and a logical data model to be very similar, or even the same for the same subject area, depending on the approach that is taken with each.

Types of Data Models

Different types of data models depict data at different levels of abstraction. Conceptual data models present the entities (ideas or logical concepts) that are represented in the database and have little if any detail about attributes. Logical data models include detail about attributes (characteristics in columns) needed to represent a concept, such as key structure (the attributes needed to define a unique instance of an entity), and they define details about the relationships within and between data entities. Relationships between entities can be optional or mandatory. They differ in terms of cardinality (one-to-one, one-to-many, many-to-many). Physical data models represent the way that data are physically stored in a database. They describe the physical characteristics of data elements that are required to set up and store actual data about the entities represented. In addition to models that differ by levels of abstraction, there can also be models of data consumer-facing views of the data. Technically, a view is a dataset generated through a query that produces a virtual table. A more mundane definition is that a view is what a data consumer sees. At its simplest, a view can have exactly the same structure as a physical table. Views can also be used to display a subset of data from a table, to aggregate data within a table, or combine data from multiple tables. As with other data models, models of views enable data consumers to understand how data is organized.

The process of data modeling involves a series of decisions about how to represent concepts and relate them to each other. Data modeling uses tools and conventions of representation that convey meaning in a consistent way, regardless of the content of the data being modeled. Like all forms of representation, data models are limited. They can be articulated to different levels of detail for different purposes. They focus on representing those aspects of the things represented that are important to a particular purpose of the representation (West, 2003).

To understand the implications of purpose and representation in data modeling, let’s consider how these choices affect other kinds of modeling. All models are built for particular purposes and must be understood in light of those purposes. A house in a subdivision will be depicted differently in different representations or models built for different purposes. In a street plan for the subdivision, it will be represented as a box on a parcel of land. The purpose of such a plan is to convey information about the size and shape of the subdivision and the location of houses and lots to each other. Such a plan might be shared with a town planning commission charged with making decisions about land use or with potential buyers wanting to understand the general characteristics of a neighborhood. In architectural drawings, the house will be depicted in a set of views showing the size, shape, and details of its structure. The purpose of an architectural drawing is to enable people to see what the house would look like and to make decisions about how it will be built. The accompanying floor plan, another model of the house, contributes to the process of understanding the size and shape of the house and is also necessary input to building the house. It contains details such as room sizes, the number of windows and doors, and the like that will influence the construction of the house. None of these models is the house itself, but all of them depict the house. Each represents a subset of characteristics important to the purpose of the representation. The same idea applies to data models.

When working with data models, it is important to recognize that there is not one-and-only-one way to model any given dataset. Put this way, models present a kind of chicken-and-egg problem: Do data define models, or do models define data? The answer is both. To be understandable at all, data require context and structure. Data models provide a means of understanding this context. In doing so, they also create context. If data stakeholders find that models are understandable representations of data, then they can become a primary means of defining data.

For most databases, especially data warehouses, models are critical to data management. The conceptual and logical models allow data managers to know what data resides in the data asset. The physical model has a direct bearing on how data is moved within the database, as well as how it is accessed.

Partitioning the data model

There are few, if any, vendor products currently on the market that cover the entire scope of this conceptual data model. The IT organization will therefore need to integrate two or more products. These integration points can be understood by simply drawing boxes around the entities, representing systems of record, and then observing where those boxes are crossed by relationship lines – that is where interfaces must be built.

For example, some Incidents may result in Changes, resulting in new Releases of functionality representing new Configuration of Software Systems that support Application Services (see Figure 2.49). This in turn requires some sort of interface between the two systems to handle the relationships between Incident, Change, Release, Configuration, Software System, and Application Service.

The challenges inherent in integrating such data across diverse systems is exactly why larger, more integrated ITSM product suites are becoming the preferred approach – at least half of “ERP for IT,” in essence, not yet well integrated with the product lifecycle. (See the upcoming section “Enterprise Resource Planning (ERP) for IT?” in this chapter.)

Publisher Summary

This chapter illustrates some practical examples of problems that arise with attributes in data models and how the principles for conceptual, integration, and enterprise data models can help overcome or avoid these problems, because they lead to data models that are more stable and regular in their structure. The clue to look for is a relatively large number of attributes or unexpected attributes. This means that a particular business view is being modeled rather than the underlying nature of the problem. The process that is followed when resolving a complex entity type is to examine each attribute in turn, discover what it means, and determine whether it is really an attribute of the entity type in question. A key consequence of this approach to attributes and identifiers is that data is broken down into small elements. This is particularly useful for data models aimed at data integration. One cannot control the granularity of the data models he or she needs to integrate, but if the integration data model has the finest granularity, then the data from other data models is always able to be broken down to that level, and one then has the pieces to reassemble for more coarsely granular data models.

Publisher Summary

This chapter discusses the database life cycle step that is of particular interest when designing relational databases: transformation of the conceptual data model to candidate tables and their definition in SQL. Entities, attributes, and relationships in the ER model and classes, attributes, and associations in UML can be transformed directly into SQL table definitions with some simple rules. Entities are transformed into tables, with all attributes mapped one-to-one to table attributes. Tables representing entities that are the child (“many” side) of a parent–child (one-to-many or one-to-one) relationship must also include, as a foreign key, the primary key of the parent entity. A many-to-many relationship is transformed into a table that contains the primary keys of the associated entities as its composite primary key; the components of that key are also designated as foreign keys in SQL. A ternary or higher-level n-ary relationship is transformed into a table that contains the primary keys of the associated entities; these keys are designated as foreign keys in SQL. A subset of those keys can be designated as the primary key, depending on the functional dependencies associated with the relationship. Rules for generalization require the inheritance of the primary key from the supertype to the subtype entities when transformed into SQL tables. Optionality constraints in the ER or UML diagrams translate into nulls allowed in the relational model when applied to the “one” side of a relationship. In SQL, the lack of an optionality constraint determines the not null designation in the create table definition.

Publisher Summary

This chapter illustrates some examples of traps found with relationship types in data models. It then demonstrates how the principles for conceptual, integration, and enterprise data models can help overcome or avoid these issues. The principles for relationship types are—activities should be represented by entity types (not relationship types), relationship types (in the entity/relationship sense) should only be used to represent things about which there is nothing to say, and cardinality constraints on relationship types should be true always. Applying the principles makes the data models more consistent, and they are more likely to support the data needed, rather than just the data first thought of. Making the data model more general is relatively easy. One simply removes the constraints that may not always be true. Introducing the fudge data to overcome the incorrect cardinalities can have expensive consequences. Sometimes cardinalities are set to one-to-many, meaning one at a time, when the cardinalities are really many-to-many over time because the relationship type is transferable. Imposing restrictions through the data structure means—arbitrary or inappropriate restrictions are placed on the data that can be held, historical data about a relationship cannot be held, the entity type will only work within the context defined, and the resultant system is harder to share.

Information Architecture

The information architecture incorporates the modeling aspects of the data and the way the data sets are used, including:

Modeling metadata, which includes the conceptual data models, logical data models, master data entity descriptions, linkage of data element concepts and data element instances within conceptual and logical models, entity-relationship diagrams, lineage mappings, and information dependencies across business processes;

Transformation metadata, which describes the data sources that feed into the data warehouse, the ultimate data destination, and, for each destination data value, the set of transformations used to materialize the datum and a description of the transformation;

Process metadata, which describes the information flow and sequence of extraction and transformation processing, including data profiling, data cleansing, standardization, and integration;

Supplied data metadata, which, for all supplied data sets, gives the name of the data set, the name of the supplier, the names of individuals responsible for data delivery, the delivery mechanism (including time, location, and method), the expected size of the supplied data, the data sets that are sourced using each supplied data set, and any transformations to be applied upon receiving the data;

Data warehouse metadata, which captures entity-relationship diagrams associated with the data warehouse, dimensional layouts and star join structures, logical data models, and physical data models.