Database administration the complete guide to practices and procedures
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

Database Administration: The Complete Guide to Practices and Procedures PowerPoint PPT Presentation


  • 151 Views
  • Uploaded on
  • Presentation posted in: General

Database Administration: The Complete Guide to Practices and Procedures. Chapter 4 Database Design. Agenda. From Logical Model to Physical Database Database Performance Design Denormalization Views Data Definition Language Temporal Data Support Questions. Terminology Summary. Rela-

Download Presentation

Database Administration: The Complete Guide to Practices and Procedures

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database administration the complete guide to practices and procedures

Database Administration:The Complete Guide to Practices and Procedures

Chapter 4

Database Design


Agenda

Agenda

  • From Logical Model to Physical Database

  • Database Performance Design

  • Denormalization

  • Views

  • Data Definition Language

  • Temporal Data Support

  • Questions


Terminology summary

Terminology Summary

Rela-

tional

Term

Object-

Oriented

Term

Common

Term

Design

Term

Graphic

Term

DP

Term

Type,

ADT,

Class

File

Cabinet

Relation,

Table

Table

File

Entity

File

Folder

or Record

Occur-

rence

Tuple,

Row

Instance,

Object

Row

Record

Field

Data Item

Data

Element

Column

(Domain)

Fact

Column

Attribute

Property

Record

Key

Primary

Key

Primary

Key

Object

Identifier

Index

Identifier


Physical database design requirements

Physical Database Design Requirements

  • In-depth knowledge of the database objects supported by the DBMS and the physical structures and files required to support those objects

  • Details regarding the manner in which the DBMS supports indexing, referential integrity, constraints, data types, and other features that augment the functionality of database objects

  • Detailed knowledge of new and obsolete features for particular versions or releases of the DBMS

  • Knowledge of the DBMS configuration parameters that are in place

  • Data definition language (DDL) skills to translate the physical design into actual database objects


Basic physical rots

Basic Physical ROTs

  • Avoid using default settings

    • They are rarely the best setting

    • It is better to know and explicitly state the actual setting you desire in each case

  • Synchronize the logical and physical models

    • Always map changes in one to the other

  • Performance before aesthetics

    • Meaning: prefer fully normalizedbut deviate when necessary to achieve performance goals

  • Almost never say alwaysor never


Transforming logical to physical

Transforming Logical to Physical

  • Translation of Logical Model to Physical Database

    • Create DDL

    • Entities to Tables, Attributes to Columns, Relationships and Keys to DB2 RI and Indexes, etc.

    • …but differences CAN and WILL occur

  • Create Storage Structures for Database

    • Files for data and indexes

    • Partitioning

    • Clustering

    • Placement

    • Order of columns

http://datatechnologytoday.wordpress.com/2011/11/19/an-introduction-to-database-design-from-logical-to-physical/


Transform entities to tables

Transform Entities to Tables

  • First general step:

    • Map each entity in the logical data model to a table in the database

  • Things may, or may not, be that easy

    • Denormalization?

PAYMENT

Payment Transaction Num

Type

Amount

PaymentDate

Status


Transform attributes to columns

Transform Attributes to Columns

  • Attributes become columns

  • Transform Domains to Data Types

    • Commercial DBMSes do not support domains

    • Date Type and Length

      • Variable or Fixed Length

      • Choose wisely; impacts data quality

    • Constraints

    • Null

http://craigsmullins.com/dbta_072.htm


Data types

Data Types

  • CHAR / VARCHAR

  • CLOB

  • DBCLOB

  • BLOB

  • GRAPHIC / VARGRAPHIC

  • DATE

  • TIME

  • DATETIME / TIMESTAMP

  • XML

  • BIGINT

  • INTEGER

  • SMALLINT

  • MONEY

  • BINARY

  • DECIMAL

  • FLOAT

    • REAL

    • DOUBLE


Database administration the complete guide to practices and procedures

Nulls

http://craigsmullins.com/dbta_043.htm


Database administration the complete guide to practices and procedures

Default


Column ordering

Column Ordering

  • Sequence columns based on logging. For example:

    • Infrequently updated non-variable columns first

    • Static (infrequently updated) variable columns

    • Frequently updated columns last

    • Frequently modified together, place next to each other

CUST

ID

FIRST

NAME

LAST

NAME

ACCT

BAL

ADDRESS

Static,

infrequently

updated

Frequently updated at

the same time (marriage)

…but infrequently updated.

Frequently

updated


Database administration the complete guide to practices and procedures

Determine Row Size


Relationships and keys

Relationships and Keys

  • Use the primary key as assigned in the logical modeling phase for the physical PK

  • Other considerations:

    • Length of the key

    • Surrogate key

    • ROWID / SEQUENCE / Identity

  • Build referential constraints for all relationships

    • Foreign keys


Build physical structures

Build Physical Structures

  • Table Spaces

  • DBSpaces

  • Data Spaces

  • Filegroups


Storage planning

Storage Planning

  • Start by determining how many rows are required

  • Calculate the row size (discussed earlier)

  • Figure out the number of rows per block/page

  • Multiple #rows/page by the page size

  • This gives you the size of the object

  • Except for free space…

http://craigsmullins.com/dbta_110.htm


Database administration the complete guide to practices and procedures

Free Space


Type of files

Type of Files

  • Data / Index

    • Both require storage

  • Raw Files

    • Can be used to bypass the O/S

  • Solid State Devices

    • For performance-critical objects


Database performance design

Database Performance Design

  • Designing Indexes

    • Partitioning

    • Clustering

  • Hashing

  • Interleaving Data


Database administration the complete guide to practices and procedures

Designing Indexes

Index Advantages

Optimize data access:

  • DBMS decides whether or not to use an index

  • DBMS maintains all indexes (modifications incur cost)

  • Table scans can be avoided through index usage

  • Recommended on foreign key columns to speed RI access

  • Indexes can minimize sorting

  • There can be multiple indexes per table to suit the way data is processed

  • Create indexes based on workload (not tables)

  • If all columns are in the index you can get index-only access (IXO)

Guarantee uniqueness:

  • Can be used to ensure uniqueness of column values

  • Required on primary key column as part of referential integrity implementation

Implement clustering:

  • Indexes can be used for clustering; that is, maintaining the rows physically on disk in the sequence of the column values in the index


Database administration the complete guide to practices and procedures

B-Tree Index

Level 1

Root Page

98 : 302

Nonleaf

Page

Nonleaf

Page

Level 2

53 : 98

108 : 302

Nonleaf

Page

Nonleaf

Page

Nonleaf

Page

Level 3

11 : 53

59 : 98

Level 4

Leaf Page

Leaf Page

Leaf Page

Leaf Page

… 59/Ptr

… 11/Ptr

… 53/Ptr

… 98/Ptr

…to the data in the table.


Bitmap index

Bitmap Index


Other types of indexes

Other Types of Indexes

  • Reverse Key Index

    • a b-tree index where the order of bytes of each indexed column is reversed; helps with hot spots

  • Partitioned Index

    • a b-tree index specifying how to break up the index (and perhaps the underlying table) into separate chunks, or partitions; to enhance performance and availability

  • Ordered Index


Database administration the complete guide to practices and procedures

Partitioning


Database administration the complete guide to practices and procedures

Clustering


Database administration the complete guide to practices and procedures

Hashing

Keys

(e.g. LAST_NAME)

Hash

Algorithm

BLAKE

Storage Locations

JACKSON

JOHNSON

JOHNSON

JACKSON

MULLINS

BLAKE

MULLINS

NEELD

Overflow

NEELD


Database administration the complete guide to practices and procedures

Interleaving Data

Disk Drive

Database File

Legend

Table 1

Table 2


Denormalization

Denormalization

  • Prejoined Tables - when the cost of joining is prohibitive

  • Report Tables - for specialized critical reports (e.g. CEO)

  • Mirror Tables - when two types of environments require concurrent access to the same data (OLTP vs DSS)

  • Split Tables - when distinct groups/apps use different parts of the same table

    • Splitting columns across two tables for long variable character columns.

  • Combined Tables - to eliminate one-to-one relationships

  • Redundant Data - to reduce the number of joins for a single column (e.g. definitional, CA to California)

  • Repeating Groups - to reduce overall I/O (& possibly DASD)

  • Derivable Data - to eliminate calculations & aggregations

  • Speed Tables - to support hierarchies

  • Physical Implementation Needs – e.g.) to reduce page size

http://www.tdan.com/view-articles/4142


When to denormalize

When to Denormalize

The only reason to denormalize, ever:

  • To achieve optimal performance!

  • If the database design achieve satisfactory performance fully normalized, then there is no need to denormalize.

    You should always consider the following issues before denormalizing.

  • Can the system achieve acceptable performance withoutdenormalizing?

  • Will the performance of the system afterdenormalizing still be unacceptable?

  • Will the system be less reliable due to denormalization?


Denormalization administration

Denormalization Administration

The decision to denormalize should never be made lightly, because it can cause integrity problems and involve a lot of administration.

Additional administration tasks include:

  • Documenting every denormalization decision

  • Ensuring that all data remains valid and accurate

  • Scheduling data migration and propagation jobs

  • Keeping end users informed about the state of the tables

  • Analyzing the database periodically to decide whether denormalization is still required


Database administration the complete guide to practices and procedures

Normalized vs. Denormalized

The Goal!


Views

Views

TABLE 1

TABLE 2

VIEW 3

VIEW


Database administration the complete guide to practices and procedures

Views


View usage rules

View Usage Rules

  • Security - row and column level

  • Access - efficient access paths

  • Data Derivation - put the calculations in the view

  • Mask Complexity - hide complex SQL from users

  • Rename a Table

  • Column Renaming - table with better column names (easier to use than AS)

  • Synchronize all views with base tables...

DO NOT USE ONE VIEW PER BASE TABLE!

http://craigsmullins.com/dbta_115.htm


Types of sql

Types of SQL

Control

Definition

Manipulation


Database administration the complete guide to practices and procedures

Temporal Data Support

  • Many types of data change over time, and different users and applications have requirements to access the data at different points in time.

    • Instead of creating separate history tables, using triggers, and/or implementing snapshot tables, a DBMS with temporal features can manage the time aspect of data.

  • There are two types of temporal data supported:

    • Business Time

    • System Time


Database administration the complete guide to practices and procedures

Temporal Data: Business Time vs. System Time

  • Business Time (aka application time or valid time)

    • Specifies when the facts stored in the database are true with respect to the real world.

    • These are the dates of interest to the business user interacting with the data.

    • Business time is useful for only certain types of data that change over time and the validity of the data is relevant to the application and users.

  • System Time (aka transaction time)

    • Denotes the time when the fact became current in the database.

    • System time can be used to track the insertion and modification history of the data.

    • Unlike business time, transaction time may be associated with any database entity.


Database administration the complete guide to practices and procedures

A DBMS Can Support Both Business Time and System Time

  • Both are implemented via a time period specification

    • Business Time is tracked in a single table.

    • Beginning and Ending time periods indicate which rows apply to which time period

    • System Time is tracked using two tables.

    • One table contains the current data.

    • Another, history table, contains the non-current data.

    • Still requires Beginning and Ending times to indicate which rows apply to which time period

  • A single “logical” table can be setup for both business and system time


Database administration the complete guide to practices and procedures

A Temporal Example

  • Why would you need temporal data management?

    • Consider an INSURANCE company example

      • The terms of any specific insurance policy are valid over a period of time.

      • After that period of time, customers can choose to decline further coverage, continue with the existing coverage, or modify the terms of their coverage.

      • So at any specific point in time, the terms of the customers’ policy can differ.

    • Over time, customers make claims against their policies. This claim information needs to be stored, managed, and analyzed.

      • Accident histories for customers are also important pieces of data with a temporal element.

    • Consider the complexity of trying to develop not only a database design that accommodates changing policies, claims, and historical details, but also enables queries such that a user might access a customer’s coverage at a given point in time.

      • Example: what policies were in effect for that customer as of, say, April 15, 2012? Or any other date during which the customer had coverage?


Questions

Questions


  • Login