1 / 17

Software School of Hunan University 2006.10

Database Systems Design Part III Section 5 Design Methodology. Software School of Hunan University 2006.10. Database Design Flow Diagram. Requirements analyzing. Intelligence. Individual Part 1. Identify Summary,Abstract Deduce, Refine. ER model. Individual Part n.

shelly-chen
Download Presentation

Software School of Hunan University 2006.10

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. Database Systems Design Part III Section 5 Design Methodology • Software School of Hunan University • 2006.10

  2. Database Design Flow Diagram Requirements analyzing Intelligence Individual Part 1 Identify Summary,Abstract Deduce, Refine ER model Individual Part n Transformation User view 1 Relations Rational Relations User view n Create Views Normalization

  3. Design Methodology • Structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. • Database design methodology has 3 main phases: • Conceptual database design; • Logical database design; • Physical database design.

  4. Design Methodology • Conceptual database design Process of constructing a model of information used in an enterprise, independent of all physical considerations. • Logical database design Process of constructing a model of information used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations. • Physical Database Design Process of producing a description of the implementation of the database on persistent storage; it describes the base relations, file organizations, and indexesdesign used to achieve efficient access to the data, and any associated integrity constraints and security measures.

  5. Critical Success Factors in Database Design • Work interactively with users as much as possible. • Follow a structured methodology throughout the data modeling process. • Employ a data-driven approach. • Incorporate structural and integrity considerations into the data models. • Use diagrams to represent as much of the data models as possible. • Use a Database Design Language (DBDL) to represent additional data semantics. • Build a data dictionary to supplement the data model diagrams. • Be willing to repeat steps.

  6. Methodology Overview - Conceptual Database Design • Step 1 Build local conceptual data model for each user view • Step 1.1 Identify entity types • Step 1.2 Identify relationship types • Step 1.3 Identify and associate attributes with entity or relationship types • Step 1.4 Determine attribute domains • Step 1.5 Determine candidate and primary key attributes • Step 1.6 Consider use of enhanced modeling concepts • Step 1.7 Check model for redundancy • Step 1.8 Validate local conceptual model against user transactions • Step 1.9 Review local conceptual data model with user

  7. Logical Database Design for Relational Model • Step 2 Build and validate local logical data model for each view • Step 2.1 Remove features not compatible with the relational model (optional step) • Step 2.2 Derive relations for local logical data model • Step 2.3 Validate relations using normalization • Step 2.4 Validate relations against user transactions • Step 2.5 Define integrity constraints • Step 2.6 Review local logical data model with user

  8. Logical Database Design for Relational Model • Step 3 Build and validate global logical data model • Step 3.1 Merge local logical data models into global model • Step 3.2 Validate global logical data model • Step 3.3 Check for future growth • Step 3.4 Review global logical data model with users

  9. Physical Database Design for Relational Databases • Step 4 Translate global logical data model for target DBMS • Step 4.1 Design base relations • Step 4.2 Design representation of derived data • Step 4.3 Design enterprise constraints • Step 5 Design physical representation • Step 5.1 Analyze transactions • Step 5.2 Choose file organization • Step 5.3 Choose indexes • Step 5.4 Estimate disk space requirements

  10. Physical Database Design for Relational Databases • Step 6 Design user views • Step 7 Design security mechanisms • Step 8 Consider the introduction of controlled redundancy • Step 9 Monitor and tune the operational system

  11. Build and Validate Local Logical Data Model Step 2.1 Remove features not compatible with the relational model (optional step) • To refine the local conceptual data model to remove features that are not compatible with the relational model. This involves: • remove *:* binary relationship types; • remove *:* recursive relationship types; • remove complex relationship types; • remove multi-valued attributes.

  12. Build and Validate Global Logical Data Model To combine the individual local logical data models into a single global logical data model that represents the enterprise. • Typically includes: • (1) Review the names and contents of entities/relations and their candidate keys. • (2) Review the names and contents of relationships/foreign keys. • (3) Merge entities/relations from the local data models. • (4) Include (without merging) entities/relations unique to each local data model. • (5) Merge relationships/foreign keys from the local data models.

  13. Build and Validate Global Logical Data Model • (6) Include (without merging) relationships/foreign keys unique to each local data model. • (7) Check for missing entities/relations and relationships/foreign keys. • (8) Check foreign keys. • (9) Check Integrity Constraints. • (10) Draw the global ER/relation diagram. • (11) Update the documentation.

  14. Logical Database Design versus Physical Database Design • Sources of information for physical design process includesglobal logical data modelanddocumentationthat describes model. • Logical database design is concerned with the what, physical database design is concerned with the how.

  15. Database Performance • Measures: - Transaction throughput:number of transactions processed in given time interval. - Response time:elapsed time for completion of a single transaction. • Disk storage:amount of disk space required to store database files. Analyze Transactions

  16. Choose File Organizations To determine an efficient file organization for each base relation. • File organizations include Heap, Hash, IndexedSequential Access Method (ISAM), B+-Tree, and Clusters.

  17. Choose Indexes To determine whether adding indexes will improve the performance of the system. Guidelines: (1) Do not index small relations. (2) Add secondary index to a FK if it is frequently accessed. (3) Add secondary index on attributes that are involved in: heavily selection or join criteria; ORDER BY; GROUP BY; and other operations involving sorting (such as UNION or DISTINCT). (4) Avoid indexing attributes that consist of long character strings.

More Related