DBMS: Data Models

"The Data Model is defined as an abstract model that organizes data description, data semantics, and consistency constraints of data."

The data model emphasizes on what data is needed and how it should be organized instead of what operations will be performed on data. Data Model is like an architect's building plan, which helps to build conceptual models and set a relationship between data items. 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. 

Three perspectives: A data model instance may be one of three kinds according to ANSI in 1975.

Each one has a specific purpose. The data models are used to represent the data and how it is stored in the database and to set the relationship between data items.

1.) Conceptual Data Model

This Data Model defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope and define business concepts and rules. It is an organized view of database concepts and their relationships. The purpose of creating a conceptual data model is to establish entities, their attributes, and relationships. In this data modeling level, there is hardly any detail available on the actual database structure. Business stakeholders and data architects typically create a conceptual data model.

• Provide flexible data-structuring capabilities
• Present a “community view”: the logical structure of the entire database
• Contain data stored in the database
• Show relationships among data including:
  • 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

Characteristics of a conceptual data model

• Represents a global view of the entire database by the entire organization.
• Conceptual schema: Basis for the identification and high-level description of the main data objects.
• Has a macro-level view of data environment.
• Is software and hardware independent.
• Logical design: Task of creating a conceptual data model

The 3 basic tenants of Conceptual Data Model are 

1. Entity: A real-world thing 
2. Attribute: Characteristics or properties of an entity 
3. Relationship: Dependency or association between two entities

2.) Logical Data Model

Defines HOW the system should be implemented regardless of the DBMS. This model is typically created by Data Architects and Business Analysts. The purpose is to developed technical map of rules and data structures. It is used to define the structure of data elements and to set relationships between them. The logical data model adds further information to the conceptual data model elements. The advantage of using a Logical data model is to provide a foundation to form the base for the Physical model. However, the modeling structure remains generic.

Characteristics of a Logical data model

• Describes data needs for a single project but could integrate with other logical data models based on the scope of the project.
• Designed and developed independently from the DBMS.
• Data attributes will have datatypes with exact precisions and length. 
• Normalization processes to the model is applied typically till 3NF.

3.) Physical Data Model

A Physical Data Model describes a database-specific implementation of the data model. It offers database abstraction and helps generate the schema. This is because of the richness of meta-data offered by a Physical Data Model. The physical data model also helps in visualizing database structure by replicating database column keys, constraints, indexes, triggers, and other RDBMS features. 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.  

• Are the physical representation of the database
• Have the lowest level of abstractions
• Are how the data is stored; they deal with
  • 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

Operates at lowest level of abstraction. Describes the way data are saved on storage media such as disks or tapes. Requires the definition of physical storage and data access methods. Relational model aimed at logical level. Does not require physical-level details. Physical independence: Changes in physical model do not affect internal model

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. 

"A database model is a specification describing how a database is structured and used. Several such models have been suggested."

Common models include:--

1. Hierarchical model
2. Network model
3. Relational model
4. Entity relational model
5. Object oriented model
6. Object relational model
7. NoSQL Databases

1. Hierarchical model

Manage large amounts of data for complex manufacturing projects. Represented by an upside down tree which contains segments i.e., Equivalent of a file system’s record type. Depicts a set of one-to-many (1:M) relationships

2. Network model

Represent complex data relationships. Improve database performance and impose a database standard. Depicts both one-to-many (1:M) and many-to-many (M:N) relationships

3. Relational model

Produced an automatic transmission database that replaced standard transmission databases.

Based on a relation. 

• Relation or table: Matrix composed of intersecting tuple and attribute. 
• Tuple: Rows. 
• Attribute: Columns. 

Describes a precise set of data manipulation constructs

4. Entity relational model

Graphical representation of entities and their relationships in a database structure

Entity relationship diagram (ERD)

•  Uses graphic representations to model database components

Entity instance or entity occurrence

• Rows in the relational table

Connectivity: Term used to label the relationship types

5. Object oriented model

Object-oriented database management system(OODBMS)

• Based on OODM

Object: Contains data and their relationships with operations that are performed on it

• Basic building block for autonomous structures
• Abstraction of real-world entity

Attributes - Describe the properties of an object

Class: Collection of similar objects with shared structure and behavior organized in a class hierarchy

• Class hierarchy: Resembles an upside-down tree in which each class has only one parent

Inheritance: Object inherits methods and attributes of parent class

Unified Modeling Language (UML)

• Describes sets of diagrams and symbols to graphically model a system

6. Object relational database

Extended relational data model (ERDM). Supports OO features and complex data representation. Object/Relational Database Management System (O/R DBMS). Based on ERDM, focuses on better data management. Extensible Markup Language (XML). Manages unstructured data for efficient and effective exchange of all data types

7. NoSQL Databases

Not based on the relational model. Support distributed database architectures. Provide high scalability, high availability, and fault tolerance. Support large amounts of sparse data. Geared toward performance rather than transaction consistency. Store data in key-value stores