Database Design Considerations - PowerPoint PPT Presentation

database design considerations n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design Considerations PowerPoint Presentation
Download Presentation
Database Design Considerations

play fullscreen
1 / 24
Database Design Considerations
213 Views
Download Presentation
armani
Download Presentation

Database Design Considerations

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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