Database Design Overview

1. Database Design Overview

2. Overview of Database Design Purpose of Data Modeling • Assist in understanding of the semantics of data • Facilitate the communication about information requirements Expressability Simplicity Extensibility Integrity Nonredundancy Diagrammatic Representation Structural Validity Shareability Criteria for Optimal Data Models

3. Database Design Methodology • A structured approach that uses procedures, techniques, tools and documentation aids to support and facilitate the process of design Interaction with users Data-driven approach DBDL diagrams validate Structured methodology Data dictionary Repeat Structural and integrity considerations

4. Broad Goals of Database Development • Develop a common vocabulary • Define data meaning • Ensure data quality • Provide efficient implementation

5. Develop a Common Vocabulary • Diverse groups of users • Difficult to obtain acceptance of a common vocabulary • Compromise to find least objectionable solution • Unify organization by establishing a common vocabulary

6. Define Meaning of Data • Business rules support organizational policies • Restrictiveness of business rules • Too restrictive: reject valid business interactions • Too loose: allow erroneous business interactions • Exceptions allow flexibility Example: - Faculty assignment to courses: timing issue - Prerequisite check: allow prerequisites to be violated

7. Data Quality • Poor data quality leads to poor decision making • Difficult customer communication • Inventory shortages • Cost-benefit tradeoff to achieve desired level of data quality • Long-term effects of poor data quality

8. Data Quality Measures • Completeness • Lack of ambiguity • Timeliness • Correctness • Consistency • Reliability

9. Data Quality Measures • Completeness: • database represents all important parts of an information system • Lack of ambiguity: • each part of a database has only one meaning • Timeliness: • business changes are posted to a database without excessive delays • Correctness: • database contains values perceived by the user • Consistency: • different parts of a database do not conflict • Reliability: • failures or interference do not corrupt database • Importance of measure depends on the database, system, and organization • Each measure can be quantified

10. Efficient Implementation • Supersedes other goals • Optimization problem • Maximize performance • Subject to constraints of data quality, data meaning, and resource usage • Difficult problem: • Number of choices • Relationships among choices • DBMS specific

11. Conceptual Data Modeling Data requirements ERD Logical Database Design Tables Distributed Database Design Distribution Schema Physical Database Design Internal Schema, Populated DB Database Development Phases OPTIONAL

12. Database Design • Conceptual database design - the process of constructing a model of the information used in an organization, independent of all physical considerations Step 1 Build local conceptual data model for each user view

13. Database Design • Logical database design for the relational model - the process of constructing a model of the info used in an organization based on a specific data model, but independent of a particular DBMS and other physical considerations Step 2 Build and validate local data model for each user view Step 3 Build and validate global logical data model

14. Database Design • Physical database design for relational databases - the process of producing a description of the implementation of the database on secondary storage. Step 4 Translate global data model for target DBMS Step 5 Design physical representation Step 6 Design security mechanisms Step 7 Monitor and tune the operational system

15. Phases of Database Design

16. Conceptual Logical Physical Overview of Database Design Build local conceptual data model for each user view Build and Validate local logical data model for each user view Build and validate global logical Model Translate global logical model for target DBMS Design Physical representation Design Security Mechanisms Monitor and Tune operational system

17. Conceptual Database Design 1. Build local logical data model for each user view • 1.1 • Identify entity types • 1.2 • Identify relationship types • 1.3 • Identify and associate attributes with entity or relationship types • 1.4 • Determine Attribute Domains • 1.5 • Determine candidate and primary key attributes • 1.6 • Specialize/generalize entity types • 1.7 • Draw Entity-Relationship Diagram • 1.8 • Review local conceptual data model with user

18. Logical Database Design 2. Build and validate local logical data model • 2.1 • Map local Conceptual data model to local logical data model • 2.2 • Derive relations from local logical data model • 2.3 • Validate model using normalization • 2.4 • Validate model against user transactions • 2.5 • Draw Entity relationship Diagram • 2.6 • Define integrity constraints • 2.7 • Review Local logical data model with user

19. Logical Database Design 3. Build and Validate Global Logical data model • 3.1 • Merge local logical data models into global model • 3.2 • Validate global logical data model • 3.3 • Check for future growth • 3.4 • Draw final Entity Relationship diagram • 3.5 • Review global logical data model with users

20. Physical Database Design • Translate Global Logical Data Model for target DBMS 4.1 Design base relations for target DBMS 4.2 Design enterprise constraints for target DBMS • Design Physical Representations 5.1 Analyze transactions 5.2 Choose file organizations

21. Physical Database design 5.3 Choose secondary indexes 5.4 Consider introduction of controlled redundancy • Design Security Mechanisms 6.1 Design user views 6.2 Design access rules • Monitor and tune operational system