The Relational DBMS

Document Sample
The Relational DBMS Powered By Docstoc
					IT 20303

   • The Relational DBMS
Relational Database Theory

   • Building a Database
     – Begin by developing a conceptual model
       of the application domain
         • Based on requirements document
         • Described in terms of
            –Entities
            –Data items
            –Relationships between entities
Relational Database Theory

     – Example: a mail-order catalog
       database


                  supplies
     SUPPLIER                   PRODUCT
Relational Database Theory

     – Build the database, using the
       conceptual model as a blueprint
        • Information about entities, data
          items, and relationships stored in
          data dictionary
Relational Database Theory

   • A database includes
      – Data Tables
      – Data Dictionary Tables
   • Creating a table defines the table
     structure & characteristics in the data
     dictionary
Relational Database Theory

   • Data Dictionary
     – A collection of system tables,
       sometimes called the catalog
     – Describes the data structures, data
       items, & constraints
Relational Database Theory

   • Data Dictionary
     – Tightly integrated with the RDBMS
        • Data definition commands create,
          drop, & alter data structures
           –Update the dictionary definitions
        • Data manipulation commands query &
          update the data
           –Access the dictionary first, then the
             data
Relational Database Theory

   • Example of a dictionary table:


        USER_TABLES
        TABLE_NAME CREATER ***
        Supplier      User1   ***

        Product       User1   ***

        Customer      user2   ***

        ***           ***     ***
Relational Database Theory

   • Evolving a Database
     – Change characteristics of the
       database as real world,
       requirements change
        • Without impacting the
          application(s)
Relational Database Theory

     – Requirements do Change
        • Add new entities, new data items
        • Change format, size of a data item
        • Change relationships
     – Tune the database to address
       performance concerns
        • Add or drop an index
        • Change the storage allocation on disk
Relational Database Theory

   • Manipulating Data
     – Data manipulation verbs
        • Operate on a set of rows at a time
        • Based on the relational algebra
        • Support querying and updating
          data
Relational Database Theory

   • Manipulating Data
     – Manipulating data includes the
       CRUD actions on data
        • Create a new supplier: Insert one
          (or more) rows in the table
        • Read data about a supplier:
          Select data from the table
Relational Database Theory

       • Update data about a supplier: Update
         data in one or more fields
       • Delete a supplier: Delete one (or more)
         rows from the table

          Create   Insert
          Read     Select
          Update   Update
          Delete   Delete
Relational Database Theory

   • SQL: A Standard Database Access
     Language
      – SQL is a command-driven database
        access language
         • Can be used interactively or
           programmatically
      – SQL is a nonprocedural language
         • State what you want rather than how to
           get it
Relational Database Theory

        – ANSI 1992, 1999, & ISO Standards
           • Describe concepts, verbs, & actions
             taken for each verb
  Data Definition – DBAs   Data Manipulation – User   Data Control - DBAs
  CREATE                   SELECT                     GRANT
  DROP                     INSERT                     REVOKE
  ALTER                    UPDATE
                           DELETE
  Database objects:        Data in tables             Permissions on tables
  tables, indexes, views,                             & users
  constraints, tablespaces
Relational Database Theory

   • Concurrency & Security
     – Support access to the database by
       multiple users simultaneously
        • Concurrency control is usually
          implemented using locks
     – Provide data security by restricting
       access to authorized users
        • Control who can access the data
        • Control type of access: SELECT,
          INSERT, UPDATE, DELETE
Relational Database Theory

   • Data Integrity
     – Data model reflects some part of the
       real world of the enterprise
     – Integrity means the data accurately
       represents the enterprise
Relational Database Theory

   • Data Integrity
     – Support for integrity requires support
       for rules about data
        • Business rules about the format,
          use, & meaning of the data
        • Action to be taken when rules are
          violated
Relational Database Theory

   • Data Integrity
     – Rules can also be called Semantic
       Integrity Constraints
     – An RDBMS enforces user-defined
       constraints
        • We define business rules to RDBMS
          as constraints
        • RDBMS prohibits changes to data that
          violate a constraint
Relational Database Theory

   • Integrity Constraints: Example
      Empno Ename Dob Startdate Job Mgr Sal deptno


      – Constraint involving one attribute
         • Salary must be a positive number
            – Check (sal >0)
         • Dob and startdate must be valid dates
      – Constraint involving two attributes in a table
         • Startdate cannot be less than birthdate
            – Check (startdate > dob)
Relational Database Theory

   • Integrity Constraints: Example
      Empno Ename Dob Startdate Job Mgr Sal deptno



     – Constraint involving two or more rows in
       the same table
        • Department number of employee must
          be the same as the department number
          of the manager - Trigger
        • Salary of employee must be less than
          salary of manager - Trigger
Ended 09-07-05

  • End Section
Relational Database Theory

   • An RDBMS supports two types of Constraints
      – Declarative Constraints – in ANSI SQL
         • Stored in the data dictionary
         • Inherent in any fully relational RDBMS
             – Defined when a table is created or altered
                » Not Null
                » Primary Key
                » Foreign Key, w/On Delete option
                » Check, for simple domain constraints
Relational Database Theory

   • An RDBMS supports two types of
     Constraints
     – Enterprise-Specific Rules
        • Implemented as database programs
        • Triggers & Stored Procedures (reusable
          code)
Relational Database Theory

   • Triggers & Stored Procedures
      – A Stored Procedure is a named module of
        code
         • Stored in the database
         • Registered in the dictionary
         • Contains SQL as well as program logic
         • Can be invoked by any program that
           can issue a request to the RDBMS,
           including another stored procedure or
           trigger
Relational Database Theory

   • Triggers & Stored Procedures
      – A Trigger is a special kind of stored procedure
         • Defined on one table
         • Fires when there is a specified SQL request
             – INSERT, UPDATE, and/or DELETE
         • Used to implement rules requiring logic
             – Example: if qty_on_hand is less than
               reorder_level, call a procedure to order
               more products
Relational Database Theory

   • A Database includes Dictionary Tables, Data
     Tables, & Stored Programs
      – The RDBMS can enforce the rules of the
        enterprise
         • Thereby protecting the integrity of the
           data
Relational Database Theory

   • An RDBMS supports Data Recovery
     – Failure occurs while program is executing
        • After updating some data items
        • Before updating related data items
     – Solution is Transaction Processing
Relational Database Theory

   • Transaction Processing
      – A transaction is defined to be a logical unit
        of work
         • Contains several accesses to the
           database
         • Transforms database from one
           consistent state to another
Relational Database Theory

   • Transaction Processing
      – Purpose of transaction processing is
        atomicity of transaction
         • Ensures transaction executes in its
           entirety or
         • Leave database as if nothing had ever
           happened
      – The DBMS manages transactions and
        keeps a log of all activities
Relational Database Theory

   • Transaction Processing
      – User program signals end of transaction
         • COMMIT signals successful end of transaction
             – Makes all changes permanent
             – Releases all locks
         • ROLLBACK signals unsuccessful end of
           transactions
             – Discards all changes since last commit
             – Releases all locks
Relational Database Theory

   • Recovery
     – Recovery from program aborts
        • Do Not use this if possible
        • Program detects error & issues
          ROLLBACK
        • DBMS erases changes back to last
          COMMIT
Relational Database Theory

   • Recovery
     – Recovery from software or hardware
       failure
        • When DBMS is initiated, it examines
          transaction log
        • DBMS effects ROLLBACK for all
          incomplete transactions
Relational Database Theory

   • Recovery
     – Recovery from media failure (disk
       information destroyed)
        • Database must be restored from backup
          copy
        • DBMS must reprocess transactions on
          log file since the backup
          (ROLLFORWARD)
Relational Database Theory

   • Performance
      – DBMS software is complex & consumes
        resources
         • Disk input/output, the amount of data
           transferred between the disk and memory
      – Performance measured by
         • Number of transactions per minute (second)
         • Time (CPU, elapsed) required to perform a
           function
         • Number of users supported, response time
           experienced by users
Relational Database Theory

   • Performance
     – Factors that impact performance
        • Path length, the number of CPU
          instructions executed
Relational Database Theory

   • Performance-Oriented Features
      – Provide efficient storage & access techniques
         • Storage & access techniques are usually
           proprietary
             – Based on indexing
      – Provide query optimization
         • Translate nonprocedural SQL request into an
           optimized query plan
         • Using computer science optimization
           techniques for relational algebra
         • Using statistics in the data dictionary about the
           tables & indexes
Relational Database Theory

   • Program/Data Structure Independence
      – Ability to change data structures without
        impacting applications
      – Accomplished by hiding details of physical
        structures
          • Uses concept of three-level information
            architecture & Data Dictionary
      – User or application requests manipulation of data
        items by name
          • DBMS uses data dictionary
          • Maps from attribute name to fields in records in
            physical file
Relational Database Theory

   • Three – Level Information Architecture
      – External View
         • Describes one user’s view of the
           database
         • Several users or programs can share
           the same view
         • User
         • Programmers
Relational Database Theory

   • Three – Level Information Architecture
      – Conceptual Schema
         • Describes all of the enterprise’s data in
           the database
         • Describes all of the constraints that
           apply
         • Describes logical organization & layout
           of the database
         • Designer
Relational Database Theory

   • Three – Level Information Architecture
      – Internal Schema
         • Describes how the data is stored
         • Describes how the data is accessed
         • DBA
      – Described in the Data Dictionary
         • Transparent to user
Relational Database Theory

   • Three-Level Architecture Supports
     Program/Data Independence
      – Change in external view
         • Add a new external view for a user
         • Existing application programs need no
           change
         • Change in a user’s external view
           impacts only that user
Relational Database Theory

   • Three-Level Architecture Supports
     Program/Data Independence
      – Change in conceptual schema
         • Add a data item or a new entity
         • Existing application programs need no
           change
         • An external view may need to be
           redefined
Relational Database Theory

   • Three-Level Architecture Supports
     Program/Data Independence
      – Change in internal schema
         • Changing patterns of usage necessitate
           tuning
         • Can create/drop indexes, change
           storage media
         • Existing application programs need no
           change
Relational Database Theory

   • Three-Level Architecture Supports
     Program/Data Independence
      – Information hiding
         • Because physical storage details are
           not exposed to user, user is immune to
           changes in the physical storage
Relational Database Theory

   • How does an RDBMS work?
     – A user issues some request, using a
       particular data language
     – The RDBMS parses the request and
       analyzes it
     – Using the data dictionary, the RDBMS
       inspects
        • The external view of that user
        • The corresponding external/conceptual
          mapping
End 09-09-05
Relational Database Theory

   • How does an RDBMS work?
        • The conceptual schema
        • The conceptual/internal mapping
        • The storage structure definition
     – The RDBMS generates an optimized
       query plan & executes it
     – Returns the requested data to the user
Relational Database Theory

   • DBMSs have Evolved
     – DBMS began as a computerized record-
       keeping system
     – More specialized, user-oriented packages
       have been added
     – Evolved to a DBMS engine & toolkit
       concept
        • Supports client/server & internet
          architectures
Relational Database Theory

   • The RDBMS engine & the toolkit functions
      – RDBMS engine stores, retrieves, &
        protects the data
         • Manages the data structure
         • Handles transaction processing,
           concurrency
         • Manages security
         • Supports one standard interface: SQL
Relational Database Theory

   • The RDBMS engine & the toolkit functions
      – RDBMS toolkit manipulates & presents
        data
         • Composed of a set of products &
           utilities
         • Provides a wide variety of user
           interfaces
         • Query languages, report writers, CASE
           tools, application development tools
Relational Database Theory

   • Client/Server Architecture
     – Client/Server architecture implements
        engine – toolkit in a network
         • RDBMS engine executes on server
           system
         • Toolkit products & utilities execute on
           client workstations
Relational Database Theory

   • Client/Server Architecture
     – Client/Server architecture supports “open
        systems”
         • RDMBS engine interfaces with toolkit
           products from multiple vendors
         • Leads to increased emphasis on
           standards: SQL
Relational Database Theory

   • Client/Server Architecture
      – Client/Server architecture made possible by
        current technology
          • Networking
          • Standards for communications & SQL
          • Proliferation of software for workstations &
            servers
      – Internet architecture is multi-tiered client/server
          • Client/Server interface is through a browser
Relational Database Theory

   • Problems an RDBMS Does Not Solve
     – Coordination among departments of
       groups of users
     – Inadequate training
     – Missing, incomplete, or out-of-date
       requirements
     – Insufficient time for thorough analysis &
       design
     – Poor application program design
Relational Database Theory

   • Problems an RDBMS Does Not Solve
     – Existing errors in the data
        • “Dirty” data, dead data
     – Duplicate data
        • Inconsistent definitions, formats, values
     – Determining who is responsible for the
       data
Relational Database Theory

   • Consequences of using an RDBMS
     – Need for skilled design & management
     – Training of programmers & users
     – Changes to existing procedures
     – Changes to job functions; DBA
     – Conversion to RDBMS
        • Data
        • Programs
Relational Database Theory

   • Consequences of using an RDBMS
     – Additional or different hardware
       configuration
        • Server in a network
     – May be incompatible with other DBMSs
     – Expensive
Relational Database Theory

   • End Section
Relational Database Theory

   • See you next time…

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:52
posted:8/5/2011
language:English
pages:60