1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database Design Concepts-Roadmap

Discussion in 'SQL PL/SQL' started by SBH, Jan 3, 2011.

  1. SBH

    SBH Forum Expert

    Likes Received:
    Trophy Points:
    1. Overview of Database Design basics

    An application skeleton consists of Data and Process. Both are mutually dependent on each other. Even a slightest weakness in any of the two can end up in application defects. In the article, we shall discuss the Data side of an application. We shall see through the designing phases of Database and its implementation.

    2. Introduction to Database

    A Database is a collection of data or information in an organized way. Database Design Phase is the most important stage of Database. It selects the data model, logically formulates the schema structure and sets the optimal tone for data stabilization, fluent maintenance and accuracy and data entity relationships in valid and secured manner. As stated earlier, any shortcoming in the design phase might worsen over the period of time and end up in a data crash.

    3. Database Design Phases

    Database design requires multiple rounds of data and logical brainstorming. Below are the phases which constitute database design cycle.

    Description of the above design phases

    Conceptual Design– It is the preliminary phase of designing which has High Level Database Design and the application objective. It sets roadmap for database design using nature of data, business requirements upon the data, forecasts the potential benefits of database and also comments on the effort estimation. It defines and sets various dimensions of designing in regards to business and objectives

    Logical Design– It is the phase where schema design is documented and necessary database elements are identified based on the database requirement and specifications. Summary of tasks involved in this phase.
    • Data Analysis –It includes nature and source of data, its relationship with other entities, its dependencies and references.
    • Selection of Data Model
    • Entity definition and Entity Relationship Diagramming,

    Physical Design– In this phase, Logical DB Design is analyzed in parallel with the technical scope of RDBMS. It is an attempt to foresee the compatibility of Logical DB design with the RDBMS features. Summary of tasks involved in this phase.
    • Selection of appropriate coding techniques and formulating the application standards
    • Selection of most feasible DBMS features (Sequences, Primary key, Data validation features, Indexing, Partitioning, Constraints, Views, Materialized Views, GTT) to be implemented;
    • Conduct POC on advanced features like VPD, LOB Storage, and Oracle Label Security
    • Prototyping Access Privilege for Users

    It is in this phase that actual coding is done using SQL to create the schema objects. DDL scripts are

    Practical Design– This is the testing phase of DB design cycle where Physical design proposal is tested in various business scenarios. However, minor design changes can still be made as result of testing. Changes can be with respect to performance, data validations, and script errors. Below are the Testing phases involved in this stage:

    1. Alpha Test
    a.Unit Testing
    b.Integration Testing
    c.Security Testing
    d.Backup and Recovery Testing
    e.End to End Testing

    2. Beta Test
    a.Load Testing
    b.Stress Testing
    c.Performance Testing
    d.User Acceptance Testing

    This phase greatly helps in design validation, data accuracy and most importantly grinding alternatives of a problem in better way.

    Attached Files: