Chapter 9 ClientServer Database Systems by coronanlime


									Chapter 7: Physical
 Database Design
      Physical Database Design
 “Many physical database design decisions
  are implicit or eliminated when you choose
  the database management technology.”
 Primary goal: data processing efficiency
    – minimize access time
    – less important: minimize storage space
       Physical Database Design
   Information needed
    – normalized relations, including volume
    – attribute definitions
    – where and when and how often: create, read,
      update, delete
    – response time expectations
    – security requirements: backup, recovery,
    – technologies to be used
       Physical Database Design
   Key decisions
    – storage format (data type) for each attribute
    – grouping attributes into physical records
    – arrangement of records in secondary memory:
      file organization
    – structures for storing and connecting: indexes
      and database architectures
    – strategies for handling queries
         Designing Physical Fields
   Data types
    –   minimize storage space
    –   represent all possible values
    –   improve data integrity
    –   support all data manipulations
   Coding and compression
   Data integrity
    –   default value
    –   range control
    –   null value control
    –   referential integrity
   Missing Data
    – estimate; resolve; sensitivity test
        Designing Physical Records
   Page: the unit of storage on disks
    –   some power of 2, e.g., 211=2048
    –   216=65,536 … 230=1,073,741,824
    –   1,073,741,824 ÷ 65,536 = 16,384
    –   FAT16 vs. FAT32
 Blocking factor: number of records per page
 Fixed-length field: exact location can be
 Variable-length field: in separate physical
    (part of physical record design)
   Denormalization
    – denormalize 1:1 when optional side usually
    – denormalize M:N if frequent joins needed
    – denormalize 1:M if 1 side is reference data
    – horizontal partitioning if processed by group
    – vertical partitioning if certain fields processed
    – replicate to various locations
       Designing Physical Files

 Sequential access or Pointer to address
 Access methods: sequential or relative
 File organization:
    – sequential
    – indexed
       » indexed sequential
       » indexed nonsequential
       » bitmap index
    – hashed
 Primary key index (unique identifier)
 Secondary key index (clustering)
 When to index:
    – larger tables
    – primary key (mandatory)
    – fields used for selecting, grouping, or sorting
    – variety in attribute values
    – don’t exceed limit
    – fields with null values may not work

 Redundant Array of Inexpensive Disks
 Stripe – concurrently read data
 RAID-0: fast access, no backup
 RAID-1: disk mirror
 RAID-2: error-correction or parity codes
 RAID-3: one disk for ECC with single reads
 RAID-4: one disk for ECC but parallel reads
 RAID-5: no dedicated parity drive
      Part IV: Implementation

 8: Client/Server and Middleware
 9: SQL
 10: Database Access from Client Applications
 11: Distributed Databases
 12: Object-Oriented Database Development
Chapter 8: Client/Server
   and Middleware

 “Client/server systems operate in a
  networked environment, splitting the
  processing of an application between a
  front-end client and a back-end processor.”
 client and server may reside on same
 both are intelligent and programmable
    Application Logic Components
   Presentation logic
    – input
    – output
   Processing logic
    – I/O processing
    – business rules
    – data management
   Storage logic
    – data storage and retrieval
    – DBMS functions
        File Server Architecture

 “A file server is a device that manages file
  operations and is shared by each of the
  client PCs.”
 Fat client: does most processing
 Limitations:
    – whole file or table transferred to client
    – client must have full version of DBMS
    – each client DBMS must manage database
     Database Server Architecture

   Client workstation:
    – user interface, presentation logic, data
      processing logic, business rules logic
   Database server:
    – database storage, access, and processing
   Advantages
    – less traffic, more control over data
   Stored procedures: first use of business
    logic at database server
          Three-Tier Architectures
   Application server in addition to client and
    database server
   Thin clients: do less processing
   Application server contains “standard” programs
   Benefits:
    –   scalability
    –   technological flexibility
    –   lower long-term costs
    –   better match business needs
    –   improved customer service
    –   competitive advantage
    –   reduced risk
        Application Partitioning

   Where should modules be placed: on the
    client or on the application server?
         Mainframes and
Parallel Computer Architectures
   Mainframes hold legacy data and systems
    – good for multiuser, massive data access
   Parallel architectures
    –   parallel transaction (two users)
    –   parallel query (one user, split processing)
    –   symmetric multiprocessing (SMP) (share memory)
    –   massively parallel processor (MPP) (share nothing)
   Consider
    – large amounts of data, complex queries, large number
      of users
    – cost: analyze as part of initiative that it supports
    – can current technology do the job?
   Software that allows interoperability
    –   asynchronous remote procedure call (RPC)
    –   publish/subscribe
    –   message oriented middleware (MOM)
    –   object request brokers (ORB)
    –   SQL-oriented data access
    –   synchronous RPC
   Client/server middleware
    –   application program interface (API)
    –   open database connectivity (ODBC)
    –   java database connectivity (JDBC)
    –   common object request broker architecture (CORBA)
    –   Distributed Component Object Model (DCOM)

   System-level password
    – who can be on the system?
   Database-level password
    – who can use the data? (or a given program?)
   Client/server communication
    – encrypted data transfer

To top