Distributed_Database by srirammadhav


									       Distributed Databases

                  Lecture 2
       Introduction to Distributed DB’s

                        Distributed Databases                        1

     Distributed Database

• A single logical DB that is
  – spread physically across computers
     • in multiple locations (sites) that are
     • connected by data communication link(s)

                        Distributed Databases                        2

       Distributed Database

• Each site is a full Database System in its
  own right and
• The sites are organised to work together
• so that
  – a user at any site can
     • access data from anywhere in the network
        – exactly as if the data was stored at the user’s own site

                        Distributed Databases                        3
         Distributed Database

• Objective –
  – to provide ease of access to data in the
    DataBase for users at different locations
• Original definition of DB involved
  – "A Centralised collection of stored Operational
    Data ......." &
  – this remains the LOGICAL view of the database
    whether it is physically distributed or not

                            Distributed Databases                           4

           Advantages of DDB’s
 • Advantages of distributed over centralised Databases
    – Increased reliability & availability –
       • If centralised system fails –
           – whole DB is unavailable to ALL users
           – A Distributed system will continue to function at some reduced
             level after some component failure
    – Local Control –
       • encourages local groups to exercise control over "their" local
       • helps Data Integrity & Data Administration
       • But local users have access to non-local data &
       • H/W can be chosen to suit local not overall requirements

                            Distributed Databases                           5

     Advantages of DDB’s (cont)
    – Modular growth
       • as organisation expands a new local computer can be added
         to network rather than upgrade existing computers
           – cheaper & less disruption
    – Lower communication costs
       • Data can be located closer to point of use
    – Faster Response
       • Speed depends on how data is distributed but
           – usually faster than centralised
           – Query optimisation ( what processing is performed where) can
             also improve response

                            Distributed Databases                           6
     Disadvantages of DDB’s
  – Software costs & complexity
     • more complex S/W (esp DBMS) required for distributed DB
     • often has implications for user Hardware
  – Processing Overhead
     • Various sites must exchange messages & perform extra
       processing to ensure proper co-ordination
  – Data Integrity
     • Depending on how data is distributed there may be UPDATE
  – Slow Response
     • If data not distributed properly according to usage, or queries
       not optimised the response can be very slow

                         Distributed Databases                       7

  Distributed Databases - Implementation

• Simplest Implementation – Partitioning
  – Tables in Database divided over 2 servers
     • Each table stored entirely on one server
  – User is connected to 1 server (Local)
     (Not necessarily on the user’s PC)
  – User not connected to other server (Remote)
  – Server to server communications provide user
    with access to data on both servers

                         Distributed Databases                       8

            Simple Partitioning

                         Distributed Databases                       9
            Simple Partitioning
 • Store Student & Result tables on 1 server
 • Store Course & Unit Tables on other
   – Note – if tables are distributed over servers in this
     fashion – we cannot implement FOREIGN keys
     between servers (in an Oracle DB)
   – Can implement foreign key (student_id) between
     student & result tables – on same server – BUT
   – Cannot implement foreign key (unitcode) between
     result & unit tables!!
      • Or foreign key between Course & Student

                        Distributed Databases                10

            Simple Partitioning
 • User is connected to
   1 server

                        Distributed Databases                11

   Server to Server Communications

• Every database in an Oracle network has a
  Global database name (service Name)
• Usually made up of
• E.g. server9a.bell.ac.uk
           DBName     . Domainname
        Domain name follows normal internet conventions

                        Distributed Databases                12
 Server to Server Communications
• Database Links
  – Defines a path to a remote database (From
    Local DB)
  – Transparent to users (set up by DBA)
  – NAME of the Database Link MUST BE THE

                     Distributed Databases                13

             Database Links
  – PUBLIC DB links –
     • available for use by all users of the local DB
     • if not a public DB Link - only available to user
       who created the link
  – DB link may specify user/schema details on
    remote DB
     • In this case ALL users connecting to the remote
       server using this DB Link will connect to the
       specified user account

                     Distributed Databases                14

             Database Links
  – if user/schema details are not specified in the
    DB Link - a user will connect to the remote
    schema with the same
    name/password/privileges as user on local
     • e.g. User dm on local server must connect to user
            dm on remote server

                     Distributed Databases                15
              Database Links
• Create Database Link command
  – General Syntax
    • Create [Public] database link {dblinkname}
      [connect to user identified by password] using
      ‘Database Alias’;
        – Database Alias created with Oracle Net Configuration
    • E.g.
    • Create Public database link server9a.bell.ac.uk
      connect to scott identified by tiger using
                       Distributed Databases                     16

              Database Links
• This Database link
  – is PUBLIC
     • ALL users on local server can use this link

  – specifies connection to USER Scott
     • ALL users will connect to user Scott on remote

                       Distributed Databases                     17

              Database Links
• However
  – Create Public database link Server9a.bell.ac.uk using
     • will allow all users access to the DB link BUT
     • Each user must connect to their own schema on remote server

                       Distributed Databases                     18
          Using Database Links
• Can now use SQL commands e.g.
   – If a user is logged into Server9a
     the SQL command
       • Select * from course@server9b.bell.ac.uk ;

       • will list the contents of
       • the table course using the Database link

                          Distributed Databases            19

            Using Database Links
• We said earlier that
• Database Link name = Database name
• So in SQL we can refer to a remote Database
  using the database link name
• we can refer to a local database by either the default (no
  name) or by using the database name
   – So for user logged into server9a
   – Select * from student;    gives the same result as
   – Select * from student@server9a.bell.ac.uk;

                          Distributed Databases            20

To top