Database Design Considerations

1. Database Design Considerations

2. Overview • Oracle specific Design Considerations • Data Integrity Issues • Performance Considerations • Storage Issues

3. Why Adapt Data Design? • User Expectations • Volumes • Hardware • Network • O.S. Adapted Physical Design Initial design • Oracle specifics

4. Oracle Data Types • Depending on: • Domains • Storage issue • Performance • Use • Select a data type for columns: • Character • Number • Date • Large Objects

5. Suggested Column Sequence • Primary key columns • Unique Key columns • Foreign key columns • Mandatory columns • Optional columnsLarge object columns always at the end

6. Primary Keys CREATE TABLE countries ( code NUMBER(6) NOT NULL , name VARCHAR2(25) NOT NULL , currency NUMBER (10,2) NOT NULL ); ALTER TABLE countries ADD CONSTRAINT cty_pk PRIMARY KEY (code); Constraint and Index name

7. Primary Keys • Choosing the Right Key • Simplicity • Ease of use • Performance • Size • Meaningless • Stability

8. AS (A) BS (B) CS (C) pk * Id * C1 pk * Id * C2 pk * Id * C3 uuu fk * D_id Artificial Keys fk1 = d_a_fk fk2 = d_b_fk fk3 = d_c_fk DS (D) pkpkpk ,fk1,fk2,fk3 **** A_idB_idC_idC4 XS (X) pkfk1fk1fk1 IdD_a_idD_b_idD_c_idC5 ****o pk * Id fk = x_d_fk

9. 225 224 223 Sequences CREATE SEQUENCE sequence_name INCREMENT BY number START WITH number MINVALUE number MAXVALUE number CACHE number | NOCACHE CYCLE | NOCYCLE;

10. Supported by Oracle through declaration Foreign Key Behavior Delete Update Restrict Cascade Default Nullify

11. b c d ef gh ij kl m no pq rs tu vw xyz Indexes • Performance Name Phone ALBERT 2655 ALFRED 3544 ALICE 7593 ALLISON 3456 ALVIN 8642 ALPHONSO 2841 • Uniqueness

12. Choosing Indexes B*tree Bitmap X01010 Y10000 Z00101 aba .1.2.5abb .1.4.5bba .1.3.5cba .1.1.5... Reverse aba .1.2.5abb .1.3.5abc .1.1.5bba .1.4.5. . . C1 C2 C1 C2 I.O.Table abcabaabbbbabbc YXZXZ abaabbabcbbabbc XZYZX

13. ! Which Columns to Index? Avoid indexing: • Small tables • Columns frequently updated • Primary key columns and Unique Key columns (Up to Version 6) • Foreign Key columns • When significant better performance can be observed in SELECT statements

14. When Can Indexes be Used? • When referenced in a Where clause or Order By • When the Where clause does not include some operators • When the optimizer decides • With hints in the SQL statement

15. Partitioning Tables and Indexes CUSTOMERS Col1 Col2 Col3 Region CUSTOMERS_R1 Col1 Col2 Col3 Region CUSTOMERS_R2 Col1 Col2 Col3 Region

16. Views T1 T2 T3 T4 V1 V2 V3 V4 • Restricting access • Presentation of data • Isolate applications from data structure • Save complex queries • Simplify user commands

17. Reasons for Views • Advantages • Dynamic views • Present denormalized data from normalized tables • Simplify SQL statements • Disadvantages • May affect performances • Restricted DML in some cases

18. Old Fashioned Design • Unique index • Views with “Check option” clause • Generic Arc implementation

19. Generic Arc Implementation X# Id* Name A# Id* Name Y# Id* Name AS (A) . . . Table_nameFk_id (X or Y) **

20. Distributed Database Different physical databases appear as one logical database.

21. Benefits of Distributed Databases • Resilience • Reduced line traffic • Location transparency • Local autonomy • Easier growth path • but • Increased, distributed, complexity

22. FREE Database Structure DATABASE consists of part of TABLESPACE consists of resides in part of container of SEGMENT OTHERSEGMENT TABLESEGMENT INDEXSEGMENT consistsof sliced in sliced in residence of located in part of part of TABLE OR INDEX PARTITION part of DATA FILE EXTENT USED resides in consists of residence of part of DATA BLOCK

23. Summary • Data Types • Primary, Foreign, and Artificial Keys • Indexes • Partitioning • Views • Distributed design

24. Practices • Data Types • Artificial Keys • Product Pictures