1 / 12

Database Design Methodology and Indexing: Improving Performance and Data Management

Learn about de-normalization, views, information-level design, physical-level design, alternative representations, and indexes to optimize database performance and data management.

abustos
Download Presentation

Database Design Methodology and Indexing: Improving Performance and Data Management

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. De-normalize if… • Performance is unsatisfactory • Table has a low update rate • (sacrifice flexibility) • Table has a high query rate • (speed up retrieval)

  2. Views • Snapshot of certain data in the database at a given moment in time • Usually much less involved than the full database, offers simplification. • Provides measure of security, since sensitive tables or columns can be omitted. • Provides data independence

  3. Information-Level Design • User Views • Set of requirements that are necessary to support the operations of a particular user. • General Database Design Methodology • Represent the user view as a collection of tables. • Normalize these tables. • Identify all keys. • Merge the result of the previous steps into the design.

  4. Methodology • Represent the user view as a collection of tables. • Determine the entities involved and create a separate table for each type. • Determine the primary key for each of these tables. • Determine the properties for each of these entities. • Determine relationships among the entities.

  5. Methodology • Normalize tables • Represent all keys • Primary • Alternate • Secondary • Foreign

  6. Physical-Level Design • Undertaken once the information-level design is complete. • Most database management systems support primary, candidate, secondary, and foreign keys. • A scheme may be needed to ensure the uniqueness of primary and secondary keys.

  7. Alternative Representations

  8. Representing Cardinality

  9. Complementary Approaches • Bottom-up (normalization) • Starting from the specific user requirements to ultimately synthesize the design. • Top-down (E-R modeling) • Begins with a general database design that models the overall enterprise and refines the model until a design is achieved.

  10. Top-down Approach • Review data gathered on all views without attempting to create any relations. • Determine the basic entities of interest. • Start a table for each entity. • Determine and fill in a primary key for each table • Add foreign keys as necessary

  11. Indexes • Conceptually the same as an index in a book. • Record numbers are automatically assigned and used by the DBMS.

  12. Advantages and Disadvantages of Indexes • Can be added or dropped at will. • Makes certain types of retrieval more efficient. • Occupies space that can be used for something else. • The DBMS must update the index whenever corresponding data in the database are updated.

More Related