Logical Data Model
Because the ARTS Data Model is mature, its conceptual view is subsumed into a Fully Attributed Data Model. This is because the broad retail framework is well established and it is in a stage of development where it is being extended and enhanced not created from scratch.
A Logical Data Model data model is composed of:
•Entity Types;
•Attributes;
•Relationships; and
•Domains.
Each instance of these object types are uniquely identified and defined in business terms. The definitions supply the semantic content for a data model. The ARTS Operational Data Model, in technical terms, is a relational data model built using entity relationship modeling notation.
Logical Data Model Components
Entity Types
An entity type is a representation of a person, place, thing, event or concept of interest to a retailer. Examples of entities include Customer, Item, Retail Store, Web Site, Purchase Order, Retail Transaction - and the list can go on to hundreds of nouns. Within the ARTS data model each entity type is defined in business terms. In an entity diagram, entity types are represented as rectangles. Each entity type has a unique, singular noun phrase assigned as its name. In a relational data model, each entity type instance is uniquely identified by a primary key. A primary key is one or more attributes that have values used to uniquely identify and distinguish each entity type instance from each other.
Attributes
An attribute identifies, names and defines a characteristic or property of an entity type. For example an Item entity type will have an ItemID attribute to unique identify it. It will have a Name attribute to use in catalogs and labels. It will have a Description attribute, etc. Attributes are the most atomic parts of a data model. They cannot be decomposed into lower level components. In a relational data model, an attribute cannot exist independently from an entity type. Accordingly all attributes are always identified and shown as part of entity types.
As discussed under entity type, a primary key is composed of one or more attributes and serves as a unique entity type instance identifier. Attributes used to compose a primary key are listed above a horizontal line in the entity rectangle.
Figure 95 - Sample Item Entity
Relationships
A relationship identifies, names and defines an association between two entity types. A relationship always associates two and only two entity types. Relationship names are represented as verb phrases. A relationship verb phrases may be established for both directions of a relation between two entities. The entity types associated through a relationship fulfill two roles: One entity is a parent entity. The second entity is a child entity. The parent entity shares it identity with the child entity. The child entity inherits the primary key of the parent entity type and is referred to as a dependent entity type. The attribute shared from a parent to a child entity type is called a foreign key. In an entity diagram an attribute name that is a foreign key is designated with a "(FK)" suffix.
In a relational data model there are two ways a parent and child entity type may be related. The first way is an identifying relationship. An identifying relationship means that the child entity's primary key inherits its parent entity type's primary key. That means that the child entity type's existence is dependent on its parent entity type's existence. If the parent entity type is deleted in this scenario, this scenario, the child is deleted. Conversely a child entity type cannot be inserted until the parent it references is inserted. In an entity diagram, an identifying relationship is signified by a solid line between the parent and child entity types.
The second way parent and child entity types may be related is through a non-identifying relationship. In a non-identifying relationship, the parent entity primary key is inherited by the child entity as a non-primary key attribute. This means that the child entity references its parent entity type but is not dependent on the parents existence for its own existence. From a relational database point of view this means that the inherited attribute may be null -- that is point to nothing. In an entity diagram, a non-identifying relationship is signified by a dashed line between the parent and child entity types.
Relationships incorporate an additional property between parent and child entity types called cardinality. Cardinality expresses the count of child entity type instances that may exist for a parent entity type. For example a Brand may apply to zero, one or many Item entity type instances. Conversely, an Item may be referred to by zero or one Brand. These cardinalities are illustrated in the diagram below. There are a variety of ways cardinality is used to express the relative counts of parent entity types to children entity types and they are presented in the Data Model Methodology and Notation Topic.
The Sample Entity, Attribute and Simple Relationship diagram shows a NON-IDENTIFYING relationship between Item and Brand. It also illustrates how foreign keys and cardinality are presented in an entity diagram. It introduces Owned Attributes which are attributes that are NOT inherited from another entity type and are not part of an entity type's primary key.
Figure 96 - Sample Entity, Attribute and Simple Relationship
In addition to cardinality, there is a special type of relationship called a subtype that allows several child entity types to inherit a common parent entity type characteristics. This is illustrated in the next diagram. A retail transaction definition is shown in the yellow block. As modeled here, a RetailTransaction may have zero, one or many RetailTransactionLineItem entity instances associated with it. The RetailTransactionLineItem entities are dependent entities because a line item cannot exist without a retail transaction header.
The RetailTransactionLineItem entity type may be extended in ways that make it a SaleReturnLineItem to capture item sales data, a DiscountLineItem to capture discount data for a retail transaction, a TaxLineItem to capture tax data or a TenderLineItem to capture payment data. A retail transaction line item provides a set of attributes (including line item number) that all subtypes share (i.e., inheritable by subtype children). A RetailTransactionLineItem entity type instance may be one and only one subtype. This relational concept of subtype is analogous to the inheritance used to model classes and objects in object oriented design. For this example subtype child entity types efficiently represent a retail transaction and the different kinds of line items needed to capture item, discount, tax and tender data. The sample receipt shows how each subtype of RetailTransactionLineItem reflects different sales receipt line items.
Figure 97 - Entity Subtype Relationship Example
Domains
A domain is a named type of data representation that may apply to one or more attributes. Data representation defines a data type such as integer, string, floating point, date, time or other standard data type or an extended definition that assigns custom properties and constraints to a base data type. Domains enable retail-specific data types to be derived from SQL base data types. For example, ARTS has an IdentityGTIN domain type defined as: An identifier for item at POS - Global Trade Item Number (GTIN) is defined by UCC/EAN. The IdentityGTIN is based on a VARCHAR(14) base data type which is used by most SQL standard databases. The creation of domains can also be used to define constraints that values assigned to an attribute assigned to a domain. For example, Flag domains are limited to two values "YES" or "NO.
Data Model Semantics
Semantics is the branch of linguistics and logic concerned with meaning. Logical models, in addition to identifying entities, attributes, relationships and domains define what each instance of these object means. These definitions provide the semantic content of a data model are are essential to the business relevancy of a relational model. The diagram below illustrates the assignment of a definition to the ItemID attribute of Item. Definitions should be expressed in business terms and reflect the business concepts represented by a data model entity, attribute, relationship, domain and other model objects.
Figure 98 - Sample Attribute Definition
Why Develop Data Models?
Data models are not just for information technologists. Data models are a prerequisite to operating a retail enterprise in today's business climate.
Information as A Currency and Asset
Retailing in the 21st century is as much about managing information as it is about managing cash, merchandise, customers, stores, vendors and other "real world" business assets. Most retail decision makers rely on information to make decisions because they can not personally visit and observe every retail site personally. To be useful, information has to be identified, named, described and organized into a coherent structure so it can be understood by decision makers. Data modeling provides a formal set of tools and procedures to make information useful. The formality and discipline introduced by data modeling is vital in figuring out what retail reports actually are telling decision makers. Consider the terms item, article, product, SKU and merchandise. They each mean different things to different people. The data model by defining each entity type clarifies what each term means. Where some are used as synonyms, they are explicitly referenced as such. This is called a controlled vocabulary and it is a key value-adding feature of data modeling. It establishes a common language for retailer organizations and individuals to communicate using explicitly defined words.
Costs of Misinterpretation and Inconsistent Semantics
Retailers manage a complex set of interactions between customers, vendors, tax authorities, regulators, employees and a wide range of other kinds of parties. Retailers that do not have a single standard way to identify, name, define and describe items, tender types, tax rules, promotions, vendor deals and the like will encounter transaction processing errors that will cost real money. Data accuracy has a direct, unambiguous impact on the bottom line. If an ordered item is not correctly aligned with the vendors catalog product code and the order is placed some party (the customer, retailer, vendor, etc.) is going to pay for it. The data model (particularly a third normal form relational model) reduces this risk by insisting on a consistent representation of each data element in a single place in the data model.
This same issue comes up when developing reports. Retailers without a consistent way of identifying, naming and defining entities, attributes and relationships spend a lot of time and money trying to reconcile conflicting summary reports. In some companies middle and senior managers spend an inordinate amount of time manually reconciling inconsistently defined data. Data models that establish an enterprise-wide controlled vocabulary eliminate one of the root causes of data inconsistency.
Data Model as a Reflection of Business Assumptions, Constraints and Rules
Data models reflect important retail business assumptions and constraints. For example, the relationship between taxation, merchandise and retailer provided services is explicitly represented in the way items, taxes, tax authorities, retail transactions, inventory control documents, etc. are related in a data model. The rules governing the way point of sale discounts are treated by a retailer are likewise reflected in the way price modification rules are related to retail transaction sale return items and promotions. The complex web of relationships that define retailer business rules is explicitly presented through entity relationship models.
Data Modeling & Regulatory Reporting and Accountability
Data models are a prerequisite to navigating the complex regulatory reporting environment retailers work in today. The Sarbanes Oxley Act of 2002 mandates detailed reporting and tracking of business operational and financial controls. That detailed tracking requires that retailers understand how data is organized, populated, stored and protected. Data for compliance is generated by corporate systems for financials, sales, marketing, inventory, purchasing, and related operational systems. Depending on the size and complexity of the organization, these systems are part of a set of applications whose data must be turned into information, then distilled into knowledge on reports for senior executives. The operational and decision support systems are based on data being collected and stored in data structures such as tables and files, and then transformed and moved – to become knowledge in reports that are signed by corporate principals. The business and technical meta data for these systems data constitute an information architecture that can both guide the development of internal controls and give corporate principals the confidence that the reports they sign are valid. The data model concepts discussed here provide the kind of support required to support regulatory reporting compliance. Additional support for data movement and transformation is also required. Data movement-related regulatory information is partially satisfied in the ARTS ODM V7.0/7.1 to ARTS DWM V3.0 extract, transform and load mapping.
Payment Card Industry (PCI) compliance which imposes procedures retailers must implement to protect credit card accounts of their customers requires retailers to map and understand the kinds of data they collect. While focused on payment-related account identification, PCI compliance is pointing the way for further controls on customer identification and personal data. All of these regimes require retailers to inventory and understand their data assets.