Data Model Fundamentals

Data Model Fundamentals

• In this chapter, one will learn the definition of a data model and why it's important.
• An overview of basic data-modeling building blocks will be provided.
• Another objective is to understand business rules and how they influence database design.
• How the major data models have evolved will be covered.
• How data models can be classified by level of abstraction also will be covered.

Data Modeling and Data Models

• A model is an abstraction of a real-world object or event.
• Models are useful in understanding complexities.
• Data models are relatively simple representations of complex real-world data structures.
• Data modeling is iterative and progressive.
• Data modeling is the first step in the database design, bridging real-world objects and the residing database.
• Importance of data models lies in facilitating designer, programmer, and end-user interaction.
• End users have varied data views and needs.
• Data models consolidate or organize data for users.
• Data models are essentially an abstraction.

Data Model Building Blocks

• An entity is anything about which data must be collected like a person, thing, or event.
• An attribute is a characteristic of an entity.
• A relationship is an association among entities, such as one-to-many, many-to-many, or one-to-one.
• A constraint places restrictions on the data to ensure integrity.

Business Rules

• Business rules outline policies, procedures, or principles within an organization These rules apply to any organization storing and using data to produce information.
• The business rules are derived from an organization's operations and enforce actions.
  • Business Rules should be written, updated, easily understood, and disseminated widely.
• Business rules describe characteristics of data as viewed within an organization.
• Properly written business rules are used to define entities, attributes, relationships, and constraints.
  • E.g "A customer may generate many invoices" or "An invoice is generated by only one customer".
• Nouns translate into entities in data models.
• Verbs are translated into relationships.
• Relationships are considered bidirectional.
• Identifying the relationship type involves asking how many instances of B relate to A, and vice versa.

Naming Conventions

• Naming happens during translation of business rules to data model components.
• Names for objects should uniquely identify and differentiate from others.
• Names should be descriptive and familiar to database users.
• Proper naming enhances communication and promotes self-documentation.
• Follow conventions like singular names for tables and columns, Schema name for table prefixes and Pascal casing.

Evolution of Data Models

• 1960: Hierarchical model developed, which was difficult to represent M:N relationships, was structurally dependent and required a predefined access path
• 1969: The network model, in 1969, allowed for predefined navigational access but lacked ad hoc queries
• 1970: Relational model introduced conceptual simplicity, structural independence and ad hoc queries using SQL.
• 1976: Entity Relationship emerged in 1976 for easier understanding and more semantics, but was limited to conceptual modeling
• 1978: The semantic model allowed for more semantics in data models and complex relationships
• 1985: Object-oriented model gained traction supporting unstructured data and Inheritance (class hierarchy)
• 1990: Extended Relational (O/R DBMS) was developed supporting XML data exchanges.
• 2009: Big Data and NoSQL addresses the Big Data problem with fewer semantics, using schema-less key-value data models suitable for large sparse data stores
  • Relational Model is implemented via a relation data management system (RDBMS) and hides complexity.
  • Relational diagrams represent entities, attributes, and relationships.
  • Relational tables store related entities in rows and columns, related through shared fields.

The Entity Relationship Model

• ER model is a widely accepted standard for data modeling.
• It provides a graphical representation of entities and their relationships within a database.
• Uses graphic representations to model database components
• An entity is mapped to a relational table, with each instance represented as a row.
• An entity set forms a collection of like entities, and connectivity labels the types of relationships.

Data Models - Summary

• Data models provide conceptual simplicity with semantic completeness, and represent the real world as close as possible.
• Real-world transformations must comply with consistency and integrity characteristics.
• Each new data model capitalized on the shortcomings of previous models.
• Some models are better suited for certain tasks.

Degrees of Data Abstraction

• Degrees of abstraction classify data models.
• Abstracted processes start at high level and then proceeds to ever increasing detail.
• Designing a usable database follows this basic process.

Levels of Data Abstraction

• External: End-user view, independent of hardware & software.
• Conceptual: Global data view, independent of database model, hardware & software.
• Internal: Specific database model, independent of hardware.
• Physical: Storage and access methods, dependent on neither hardware nor software.

The External Model

• Represents the end users' view of the data environment via ER diagrams.
• External schema represents an external view including entities, relationships, processes, and constraints.
• Advantages include identification of specific data required, designer feedback, improved security and simplified application development.
• Constraints are used to limit the data in a table. If there is any violation between the constraint and the data action, the action is aborted.

The Conceptual Model

• Represents the global view of the entire database, integrating all external views.
• ER model is widely used, and ERD visually represents the conceptual schema.
• Provides a macro-level view.
• Its is independent of both software and hardware. Therefore software or hardware changes do not affect conceptual database design.

The Internal Model

• The DBMS "view" of the database, mapping the conceptual model to the DBMS, and depends on support to concepts by the DBMS.
• Change in DBMS software requires internal model alterations.
• Logical independence means the internal model can be changed without conceptual model alterations.

Physical Model

• Physical Model operates at the lowest level of abstraction.
• It describes data storage on media and requires definition of physical storage and access methods.
• Physical models are both software and hadward dependant.
• The now Relational model aimed at logical level and does not require details about physical
• Physical independence means changes in the physical model do not affect internal model.

Data Models: Overview

• ERM/ ERD Features
  • External and Conceptual Models: Entity Names, Entity relationships.
  • Logical [Database Design] Model: Attributes, table names, Primary Keys, Foreign keys.
  • Internal [Database Physical] Model: column names, column data types, primary keys and foreign keys.

Big Data & NoSQL

• Big Data is the movement to find methods to manage Web generated data and derive business insights while maintaining high performance and reduced costs.
• Relational database not suitable for challenges such as unstructured data and milliong of rows structured and unstructured data due to unsuitability for mining such information.
• NoSQL is a generation of databases for specific Big Data challenges.
  • It is not based on relational databases
  • Supports distributed architectures.
  • Provides scalability, availability and fault tolerance.
  • It can support very large amounts of sparse data.
  • Is is also performance driven, rather than transaction consistency.