Database Administration: The Complete Guide to Practices and Procedures

1. Database Administration:The Complete Guide to Practices and Procedures Chapter 4 Database Design

2. Agenda • From Logical Model to Physical Database • Database Performance Design • Denormalization • Views • Data Definition Language • Temporal Data Support • Questions

3. 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

4. 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

5. 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

6. 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/

7. 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

8. 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

9. Data Types • CHAR / VARCHAR • CLOB • DBCLOB • BLOB • GRAPHIC / VARGRAPHIC • DATE • TIME • DATETIME / TIMESTAMP • XML • BIGINT • INTEGER • SMALLINT • MONEY • BINARY • DECIMAL • FLOAT • REAL • DOUBLE

10. Nulls http://craigsmullins.com/dbta_043.htm

11. Default

12. 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

13. Determine Row Size

14. 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

15. Build Physical Structures • Table Spaces • DBSpaces • Data Spaces • Filegroups

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

17. Free Space

18. 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

19. Database Performance Design • Designing Indexes • Partitioning • Clustering • Hashing • Interleaving Data

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

21. 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.

22. Bitmap Index

23. 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

24. Partitioning

25. Clustering

26. Hashing Keys (e.g. LAST_NAME) Hash Algorithm BLAKE Storage Locations JACKSON JOHNSON JOHNSON JACKSON MULLINS BLAKE MULLINS NEELD Overflow NEELD

27. Interleaving Data Disk Drive Database File Legend Table 1 Table 2

28. 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

29. 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?

30. 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

31. Normalized vs. Denormalized The Goal!

32. Views TABLE 1 TABLE 2 VIEW 3 VIEW

33. Views

34. 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

35. Types of SQL Control Definition Manipulation

36. 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

37. 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.

38. 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

39. 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?

40. Questions