Chapter 9: ClientServer Database Systems

1
(No Transcript)
2
Chapter 7 Physical Database Design
3
Physical Database Design Process

• 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

4
Physical Database Design Process

• Information needed
• normalized relations, including volume estimates
• attribute definitions
• where and when and how often create, read,
  update, delete
• response time expectations
• security requirements backup, recovery,
  retention
• technologies to be used

5
Physical Database Design Process

• 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

6
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

7
Designing Physical Records

• Page the unit of storage on disks
• some power of 2, e.g., 2112048
• 21665,536 2301,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
  determined
• Variable-length field in separate physical
  record

8
Denormalization (part of physical record design)

• Denormalization
• denormalize 11 when optional side usually
  present
• denormalize MN if frequent joins needed
• denormalize 1M if 1 side is reference data
• horizontal partitioning if processed by group
• vertical partitioning if certain fields processed
  together
• replicate to various locations

9
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

10
Indexes

• Primary key index (unique identifier)
• Secondary key index (clustering)
• When to index
• larger tables
• primary key (mandatory)
• fields used for selecting, grouping, or sorting
  data
• variety in attribute values
• dont exceed limit
• fields with null values may not work

11
RAID

• 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

12
Part IV Implementation

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

13
Chapter 8 Client/Server and Middleware
14
Concepts

• 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 computer
• both are intelligent and programmable

15
Application Logic Components

• Presentation logic
• input
• output
• Processing logic
• I/O processing
• business rules
• data management
• Storage logic
• data storage and retrieval
• DBMS functions

16
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 integrity

17
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

18
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

19
Application Partitioning

• Where should modules be placed on the client or
  on the application server?

20
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?

21
Middleware

• 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)

22
Security

• 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

23
(No Transcript)