1 / 45

Physical Design Patterns in Information Systems

Karim Ali & Sarah Nadi CS848 – Spring 2010 July 14 th , 2010. Physical Design Patterns in Information Systems. Outline. Stages of Design Elements of Physical Design in Information Systems Different Physical Designs Disk Based Relational Database Systems (DRDB)

shawn
Download Presentation

Physical Design Patterns in Information Systems

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Karim Ali & Sarah Nadi CS848 – Spring 2010 July 14th, 2010 Physical Design Patterns in Information Systems

  2. Outline • Stages of Design • Elements of Physical Design in Information Systems • Different Physical Designs • Disk Based Relational Database Systems (DRDB) • Memory Based Relational Database Systems (MMDB) • XML Databases • Data Warehouses • Future Work • Open Problems • Summary & Conclusions Karim Ali & Sarah Nadi

  3. Stages of Design • Describes the intended behavior Karim Ali & Sarah Nadi

  4. Elements of Physical Design Karim Ali & Sarah Nadi

  5. Indexes • Data needs to be organized for quick searching • I/O operations are expensive --> need to minimize Karim Ali & Sarah Nadi

  6. Materialized Views • Repeated complicated queries should not have to be executed every time • Save execution time, and I/O reads by pre-computing the results & storing them • Materialized views are store on disk Karim Ali & Sarah Nadi

  7. Paritioning • Divides the data into related partitions • Horizontal Partitioning: divides tables into sets of rows according to a specific attribute (E.g. Date ranges) • Vertical Partitioning: divides table into the sets of attributes that are usually accessed together • Reduces table scan time • Improves performance Karim Ali & Sarah Nadi

  8. Clustering • Records that are accessed together are physically located together • Reduces the number of pages to be queried • Can have multi-dimensional clustering based on more than one criteria Karim Ali & Sarah Nadi

  9. Data Compression Karim Ali & Sarah Nadi

  10. Sriping, Mirroring, Denormalization Karim Ali & Sarah Nadi

  11. Physical Design of Different Information Systems Karim Ali & Sarah Nadi

  12. Disk Based Relational Database Systems (DRDB) Karim Ali & Sarah Nadi

  13. DRDB: Indexes Karim Ali & Sarah Nadi

  14. DRDB: Materialized Views Karim Ali & Sarah Nadi

  15. DRDB: Paritioning Karim Ali & Sarah Nadi

  16. DRDB: Clustering Karim Ali & Sarah Nadi

  17. DRDB: Summary • Summary table/figure Karim Ali & Sarah Nadi

  18. Main Memory Database Systems (MMDB) • Data resides in main memory • Cheaper to access main memory Karim Ali & Sarah Nadi

  19. MMDB: Indexes • Factors to consider: • I/O operations are cheaper • Should be cache conscious • Types of indexes used: • B+trees • T Trees • Cache Sensitive Search Trees • Cache Sensitive B+ Trees • Prefetching B+ Trees • J+ Trees and pJ+ trees Karim Ali & Sarah Nadi

  20. MMDB: Materialized Views Karim Ali & Sarah Nadi

  21. MMDB: Partioning Karim Ali & Sarah Nadi

  22. MMDB: Clustering Karim Ali & Sarah Nadi

  23. MMDB: Summary • Summary table/figure Karim Ali & Sarah Nadi

  24. Data Warehouses • Collection of data and decision support technologies • Used in: • Retail: user profiling • Finance: claims analysis, risk analysis, credit card analysis, and fraud detection • Healthcare: outcomes analysis Karim Ali & Sarah Nadi

  25. DW: Challenges • Data is usually • Extremely large • Multi-dimensional • Priority for aggregated and summarized data • Ad-hoc and complex queries • Expensive operations: aggregation, and joins • the fact table participates in every join • Figure ?? Karim Ali & Sarah Nadi

  26. DW: Design • ROLAP • Relational implementation of DW • Multidimensional view of data is achieved through star scheme Karim Ali & Sarah Nadi

  27. DW: Indexes Karim Ali & Sarah Nadi

  28. DW: Materialized Views Karim Ali & Sarah Nadi

  29. DW: Partitioning Karim Ali & Sarah Nadi

  30. DW: Clustering Karim Ali & Sarah Nadi

  31. DW: Summary Karim Ali & Sarah Nadi

  32. XML Databases • XML-enabled DBs: • Maps XML documents to relational tables • Native XML DBs: • Data structures store actual XML Karim Ali & Sarah Nadi

  33. XML DBs: Indexes • Same index structures can be used • Need adjustments • Need a numbering schema for the XML nodes Karim Ali & Sarah Nadi

  34. XML DBs: Materialized Views Karim Ali & Sarah Nadi

  35. XML DBs: Paritioning Karim Ali & Sarah Nadi

  36. XML DBs: Clustering Karim Ali & Sarah Nadi

  37. XML DBs: Summary Karim Ali & Sarah Nadi

  38. Future Work & Open Problems Karim Ali & Sarah Nadi

  39. Future Work • Looking at automating physical design (put some examples of work here and say its time permitting) Karim Ali & Sarah Nadi

  40. Open Problems in Physical Design Karim Ali & Sarah Nadi

  41. Summary & Conclusions Karim Ali & Sarah Nadi

  42. Big summary table(s) Karim Ali & Sarah Nadi

  43. Conclusions Karim Ali & Sarah Nadi

  44. Thank you Karim Ali & Sarah Nadi

  45. References Karim Ali & Sarah Nadi

More Related