Database Management System



DATABASE

Data: facts that can be recorded. It can be text, numbers, videos, speech, images, audio etc.

Database: collection of related data that represent some real world entities.

Information: meaningful/processed data.

DBMS: software tool to manage data.

[DB + DBMS = Database System]


Flat File - coma-separated(CSV) File

DBMS - table


    DRAWBACKS OF USING FILE SYSTEMS TO STORE DATA    

    1. Data redundancy & inconsistency
    2. Difficulty in accessing data
    3. Data isolation
    4. Integrity Problems
    5. Atomicity(all-or-nothing property) of updates
    6. Concurrent access by multiple users
    7. Security problems

    Data Model    

    Most DBMSs

    • Relational
    No SQL
    • Key/Value
    • Graph
    • Document
    • Column-Family
    Machine Learning
    • Array/Matrix
    Obsolete
    • Hierarchical
    • Network


_______


RELATIONAL DATABASE

    Defines by E.F Codd in 1970(IBM), a relational database is a digital database based on the relational model of data.


    LANGUAGES    

  • SQL(Structured Query Language)
  • MySQL
  • PostgreSQL
  • sqlite3

    PROS    

  • SIMPLICITY
  • DATA ACCURACY
  • EASY ACCESS TO DATA
  • DATA INTEGRITY
  • FLEXIBILITY
  • SECURITY

    CONS    

  • POSSIBLE PERFORMANCE ISSUES
  • SET-UP CAN BE LONG
  • NO SUPPORT OF COMPLEX DATA TYPES

RDBMS - Relational Database Management System
    RDBMS mainly relation between table. In this table columns are called Attributes & rows are called Instances.


_______



NON-RELATIONAL DATABASE

    Non-Relational Databases existed in the late 1960’s, but the term was not used until 1998.

A non-relational database is a database that does not use the tabular schema of rows and columns found in most traditional database systems. Flexible to used for Big-Data & real-time Web Applications.

Examples: Amazon DynamoDB, Mongo DB, Firebase


    PROS    

  • HANDLING UNSTRUCTURED DATA
  • AGILITY
  • READABILITY
  • HIGHLY SCALABLE AT LOW COST

    CONS    

  • DEPENDENCY ON A SPECIFIC DATABASE MANAGEMENT SYSTEM
  • LIMITED FUNCTIONALITY
  • HIRING DIFFICULTIES
  • NOT MATURE


_______


DATA ABSTRACTION

    Data abstraction is a process of hiding the implement details(such as how the data are stored and maintained) and representing only the essential features to simplify user's interaction with the system.


    LEVELS OF ABSTRACTION    

    To simplify user's interaction with the system, the complexity is hidden from the database users through several levels of abstraction.


level_of_abstraction



Physical level:
  • Lowest level of Data abstraction.
  • Describes how the Data is actually stored.
Logical/Conceptual level:
  • Next highest level of Data abstraction.
  • Describes what Data are stored and what relationship are exist among the data entities in relatively simple structures.
  • Database administrator level.
View level:
  • Highest level of Data abstraction.
  • Multiple views of the same Database may exist.
  • Users can just view the data and interact with the Database.


_______


INSTANCES & SCHEMAS

    INSTANCES    

    Instances are the collection of information stored at a particular moment. Instances change regularly but schema remains unchanged.

    SCHEMA    

    Schema is the plan for organizing Data.
Schema is of two types:
  1. Logical Schema-the overall logical structure of the Database.
  2. Physical Schema-the overall physical structure of the Database.

    ANALOGY OF SCHEMA & INSTANCE    

analogy_of_schema_and_instances


_______


DATA DEFINITION LANGUAGE (DDL)

    DDL commands are used to define the database structure or schema.

It includes:

CREATE: To create database instances or objects.

ALTER: To alter the database structure or schema.

DROP: To delete the database instances or objects.

TRUNCATE: To remove all records from a table, including all spaces allocated for the records are removed

RENAME:  To rename a database object or instance.

COMMENT: To add comments to the data dictionary.



_______


Data Manipulation Language (DML)

    DML commands are used for managing data within schema objects.

It includes:

INSERT: To insert data into a table.

UPDATE: To update existing data within a table.

DELETE: To deletes all records from a table.

MERGE: To merging two rows or two tables.



_______


ADDITIONAL NOTES:

    HOW TO CHOOSE A DATABASE?    

        1.    What type of Data will you be analyzing?

                    A lot of factual & numeric Data >> Relational Database
                    "Messy" Data >> Non-Relational Database

        2.    How much Data are you dealing with?

                    Huge amounts of Data >> Non-Relational Database

        3.    Are you ready to invest time and budget in the setup of your Database?

                    Harder to set up, Easier to support >> Relational Database
                    Easier to set up, Harder to support >> Non-Relational Database

        4.    Do you need real-time Data?

                    Real-time Data >> Non-Relational Database

    DB-ENGINES RANKING    

For Latest Update: DB-Engines Ranking
DB-Engine Ranking
DB-Engine Ranking


_______