Database Design Life Cycle

The database development life cycle has a number of stages that are followed when developing database systems. But on small database systems, the database system development life cycle is usually very simple and does not involve all of the steps.

DD01

Data Modelling

Data Model is like architect’s building plan. Data modeling is the analysis of data objects and their relationships. Data modeling is the first step in database design. Data modeling involves a progression from conceptual model to logical model to physical schema. Process modeling – a technique used to organize and document a system’s processes. It textually describes how the outputs are generated from the inputs. Flow of data through processes Logic, Policies & Procedures. Data model emphasizes on : what data is needed and how it should be organized instead of what operations need to be performed on the data.

Two types of Data Models techniques are :

  • Entity Relationship (E-R) Model
  • UML (Unified Modelling Language)

Data modeling helps in the :

  • Visual representation of data
  • Helpful to identify missing and redundant data.
  • Helps design the database at the conceptual, logical levels and physical.
  • Enforces business rules, regulatory compliances, and government policies on the data.
  • Data models ensure consistency in naming conventions,
  • Default values, semantics, security while ensuring quality of the data.

 

DD02

Conceptual Model

Conceptual data models known as Domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope. The conceptual model is developed independently of hardware specifications like data storage capacity, location or software specifications like DBMS vendor and technology. Conceptual Modeling is Important

  • Effective Communication Tool
  • User involvement
  • Independence from a particular DBMS
  • Documentation

Step 1 :

Discovering and analyzing organizational and users data requirements

What data is important ?

What data should be maintained ?

Constructing a data model (Entity-Relationship Diagram).

Step 2:

The main aim of this model is to establish the entities, their attributes, and their relationships. The 3 basic tenants of Data Model are

Entity: A real-world thing

Attribute: Characteristics or properties of an entity

Relationship: Dependency or association between two entities

DD03

Logical Data Model

Logical data models defines the structure of the data elements and set the relationships between them. At this Data modeling level, need to verify and adjust the connector details that were set earlier for relationships. But no primary or secondary key is defined. Logical design adapts the conceptual design to a specific DBMS implementation model. Logical data model is to provide a foundation to form the base for the Physical model.

The logical design is software-dependent.

  • Logical Models
  • Relational Model
  • Network Model
  • Hierarchical Model

Characteristics of a Logical data model

  • A logical data model will normally be derived conceptual data model.
  • Entities and attributes will have definitions. Data attributes will typically have datatypes with precisions and lengths assigned.
  • Data attributes will have nullability (optionality) assigned.
  • Contains relationships between entities that address cardinality.
  • Describes data requirements for a single project or major subject area.
  • May be integrated with other logical data models via a repository of shared entities

 

DD04

Physical Data Model

A physical data model is a fully-attributed data model that is dependent upon a specific version of a data persistence technology.  The target implementation technology may be a relational DBMS. Physical data model also helps to visualize database structure. It helps to create database columns keys, constraints, indexes, triggers, and other RDBMS features. Characteristics of a physical data model:

Designed and developed are depending on a specific version of a DBMS,

data storage location or technology.

Data Model contains relationships between tables which addresses

cardinality and nullability of the relationships.

Columns should have exact data types, lengths assigned and default values.

Primary and Foreign keys, views, indexes, access profiles, and authorizations,

etc. are defined.

Developed for a specific version of a DBMS, location, data storage or

technology to be used in the project.

DD05

Summary

DD06

Diagrammatic comparison:

Data modeling, conceptual data model, logical data model, and physical data model

DD07

Comparison :

Feature Conceptual Logical Physical
Entity Names ✓ ✓  
Entity Relationships ✓ ✓  
Attributes   ✓  
Primary Keys   ✓ ✓
Foreign Keys   ✓ ✓
Table Names     ✓
Column Names     ✓
Column Data Types     ✓

Best Practice

Documentation

  • SQL development standards
  • Database source controls.
  • Plan for table partition
  • Specific number of columns
  • Avoid sub queries.
  • Not to use ’LIKE’
  • Keep passwords as encrypted for security

Implementation

  • Role based authentication
  • Keep your stored procedure encrypted.
  • Use disaster recovery like failover clustering, auto backups, replication etc.
  • Security and user permissions.
  • Split over a number of files
  • Place data and log files on different drives
  • Enable Auto Grow
  • Disable Auto Shrink
  • Security and user permissions.

Conclusion

  • 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.