Evolution of Database Since 1960

							DATABASE
EVOLUTION &
ENVIRONMENT


Chandra S. Amaravadi
IN PREVIOUS DISCUSSION
       Database concept
       Database importance
       Development cycle
       Example application
IN THIS DISCUSSION
     Evolution
     The database approach
     Definitions and terminology
     Database architecture
     DBMS architecture
DBMS EVOLUTION
EVOLUTION OF DBMS

Introduction of business computers (1950‟s & 1960‟s)
Use in TP applications (file processing)
Problems of data management and data quality
Academic formulations of DBMS concepts
Standardization of DBMS concepts (by CODASYL 1971)
EVOLUTION OF DBMS.. (FYI)
 Codd‟s paper (1970)
 System R (early „70‟s)
 Ingres formed in 1971
 Oracle formed in 1977  ORACLE system
 Britton Lee formed from Ingres team in 1979
 “Database machine”, DB2 in 1983
 Sybase, OODBMS 1986
     THE FILE PROCESSING ERA

A period of time following the introduction of computers into
business, during which application programs managed their own
files. All changes to the data and structure of the files such as the
addition of a field or changing the account balances of customers
were carried out by utilities within the application program.
THE FILE PROCESSING ERA

  During the DP era
  Informal handling of data
  In files (flat files) rather than database
  Caused many problems
      THE FILE PROCESSING ERA..
     PROBLEMS OF DATA REDUNDANCY



CUSTOMER ACCTS.               LOANS




    Acct#                  Acct#
    Name                   Name
    Address                Address
    Acct. info.            loan info.
THE FILE PROCESSING ERA..
PROBLEMS OF PROGRAM DATA DEPENDENCE

   01 Customer-file
        05 Acct# Pic 9(4).
        05 Name Pic X(15).      APPLICATION
   ...........                  PROGRAM
   Read customer file...


   4785 Jim Smith 568697997..
                                FILE
   5676 Jane Fedo 686887997..
                                (“FLAT FILE”)
   5678 Maryann 890874432..
   3215 Jeff Zeeb 980056887..
THE FILE PROCESSING ERA..
 PROBLEMS OF REPORTING


           CROSS REFERENCE
               REPORT




 CUSTOMER ACCTS.             LOANS




    ACCT. DATA           LOAN DATA
EVOLUTION OF DBMS..
PROBLEMS CAUSED BY FILE PROCESSING

        Uncontrolled redundancy
        Program data dependence
        Program maintenance
        Poor data quality
        Inability to get reports
        Application backlog
   EVOLUTION OF DBMS..
   PROBLEMS CAUSED BY FILE PROCESSING
Uncontrolled redundancy – Same data is duplicated in multiple
    files
Program data dependence – Application programs dependent on
   structure of the data
Program maintenance – The effort spent in changing programs
   to accommodate changes in file structures.
Poor data quality – Redundancy can often lead to inconsistent
   updates of the data, leading in turn to problems of data quality.
Inability to get reports – Since each file was tied to a particular
   program, it was hard to get reports involving multiple files
Application backlog – Managers‟ requests for report led application
  programmers to write new applications to fulfill the request. They
  were typically behind in fulfilling the requests, hence
  the backlog.
EVOLUTION OF DBMS..


  Application +   Applications    Applications
Data management

                  File handling
                                    DBMS
                     routines



     Files            Files          Files
EVOLUTION OF DBMS..
ACADEMIC FORMULATIONS OF
DBMS CONCEPTS (FYI)

1959             1965           1968          1971
McGee            Dobbs          Fry           CODASYL

                File defn.     File gen.      Data defn.
                Creation       Creation       Storage struc.
                Maintenance    Maintenance    Maintenance

File mainten.   Retrieval      Retrieval      Interrogation
Report gen.     Presentation   Presentation   Prog. lang.
Record gen.
                                              Data admin.
THE DBMS/DB APPROACH
       THE DATA BASE APPROACH
Entity classes
                                  File 1



      cust.
                                   File 2
     emp.




       Enterprise   Data model   Data base
THE DBMS APPROACH
   Integrated conceptualization of the data
   Organized design of database
   Centralized management of data
   System controlled access
   Checks on data quality
   Ad-hoc query capability
   Ad-hoc reporting
DEFINITIONS AND
TERMINOLOGY
              DEFINITIONS

 Database - A shared collection of logically related data
               designed to meet the needs of multiple users in
               an organization.

 DBMS      - Software used to create, maintain and provide
              controlled access to the database
                      TERMINOLOGY

      schema/structure/definition
                                                                attribute/field name
                         Customer

Record/tuple               Cust#    Name            Address          Bal.
                           100      Gordon         10 Oak st.        $40
                           200      Prasad         20 Birch.         $50
                           300      ............   ...............   ......



                                                                     File/table/relation
                      attribute/field value
GETTING INFORMATION
  FROM A DATABASE
GETTING INFORMATION
  FROM A DATABASE
      Reports
      Queries
      Views [forms that display]
GETTING INFORMATION
  FROM A DATABASE

          CROSS REFERENCE
              REPORT




CUSTOMER ACCTS.             LOANS




   ACCT. DATA           LOAN DATA
         GETTING INFORMATION
           FROM A DATABASE
ACCT

 ACCT#       NAME     DT. OPENED   BALANCE
                                              How can
8895        Smith    4/16/05        $35,000   we get
8896        Farley   4/22/04          $300
                                              Acct &
8897        Gomez    1/10/00         $2,000
                                              Loan
                                              info. in
LOAN                                          one rpt?
 LID      AMOUNT     INT. RATE     BALANCE
  9978    $6,000     6.0%          $2,440
  9979    $5,000     7.1%          $5,000
  9992    $1,000     8.5%          $ 400
   GETTING INFORMATION FROM
        MULTIPLE TABLES

Two rules for SQL
1. In the Select part, precede each attribute name by
   name of table e.g. Acct.name, Loan.LID
2. In the Where part, equate values of common key from
   both tables e.g. Acct.Acct# = Loan.Acct#

Write a query to list Name, balance, loan amt and balance
for all customers.
DATABASE & DBMS
ARCHITECTURE
 THREE-SCHEMA ARCHITECTURE
             An architecture for databases
             Introduced by ANSI/SPARC*
             A prescription for how data should be stored
              (in a macro sense)
             Provides benefits of logical and physical
              independence
             Lacking in file processing approach




*Standards Planning and Requirements Committee
        THE THREE FORMS OF DATA
           Cust#         Bal.
           100           $40                      External
           200           $50


Cust#   Name            Address          Bal.     Conceptual/
100     Gordon         10 Oak st.        $40
200     Prasad         20 Birch.         $50
                                                  Base table
300     ............   ...............   ......



                                                  Internal/
                                                  Hardware level
THREE-SCHEMA ARCHITECTURE..

 External                      View2
                       View1




 Conceptual/   Base    Base    Base
 Logical       Table   Table   Table



 Physical/
 Internal
THREE-SCHEMA ARCHITECTURE..
     Basic concept is that of a view
     A view is the way data is presented
     It is a subset of the data
     The data resides in base tables
     A base table contains information about an Eclass

     Applns. access data via views
     Views are created in SQL or by forms/reports
     THE THREE FORMS OF DATA..

   External/   --- The view of data as seen by a user/
                    application program (views).
   Conceptual/ --- The view as seen by a database
                    designer (base table)
   Internal    --- The view of data as it is stored
                    internally

These three levels provide logical and physical data independence, the
ability to change the structure of the data and the ability to change the
internal storage structure, independently of the application program.
 THE THREE TYPES OF MODELS
                             Create view
 External    Views
                             Drop view


Conceptual   Schemas         Create table
                             Alter table

             File            Create index
 Internal
             Organizations   drop index

              Models           Facilities
        Major Components of DBMS


                 D B M S Kernel


                Prog.      Data      Screen/
Data                                            Appln.
        SQL   Language    Diction-   Report
Defn.                                            Gen.
              Interface     ary       Gen.


                 D B M S Kernel



                                     Export/Import
DBMS COMPONENTS..
Data definition – the facility through which schema is defined.
                  (how new tables are created).
SQL interface – the facility through which SQL commands are typed in.
Programming language interface – the facility which processes SQL
                  commands embedded in application program. Also known
                   as the host language interface.
Data dictionary – the facility that records details about the schema,
                  reports, data entry forms etc.
Screen & reports- the facility through which data entry screens and
                  reports are created.
Appln. Generation- the facility through which applications are created.
Export/Import -- the facility through which files can be imported/exported
                   in different DBMS formats.
DBMS Kernel -- the actual programs which interact with the O/S and
                   carry out data I/O.
ODBC --            Open Database Connectivity – middleware to take SQL
                   commands & return data.
THE DIFFERENT CLASSES OF USERS
IN A DBMS




 Administrators   System developers
                                      End Users




                         DBMS
                         An integrated database environment

    Data         Application
                                       End Users
Administration   Developers




    Data         Database             Enterprise
 Dictionary       Client             Applications



    Dir.             SQL
   Server                               Database
                    Server
             DISCUSSION
What organization standardized DBMS concepts?
Who was the chief architect of relational systems?
What data-related functions were performed in the
file processing approach?
What are some of the basic features of a DBMS?
What is the smallest unit of data in a database?
 Define the terms: schema, view, database, three-schema
 architecture.
 How is redundancy minimized in the database approach?
What are advantages of the 3-schema architecture?