1 / 18

Database Design Design method

Database Design Design method. John Wordsworth Department of Computer Science The University of Reading J.B.Wordsworth@rdg.ac.uk Room 129, Ext 6544. Lecture objectives. To provide an overview of the design process

rosenfeld
Download Presentation

Database Design Design method

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 DesignDesign method John Wordsworth Department of Computer Science The University of Reading J.B.Wordsworth@rdg.ac.uk Room 129, Ext 6544 2/CS/3X

  2. Lecture objectives • To provide an overview of the design process • To explain the terms conceptual design, logical design and physical design as applied to database 2/CS/3X

  3. Overview of the design process • Step 1: Conceptual database design • Understand what’s there; for the moment don’t worry about database theory or the technology • Steps 2 and 3: Logical database design • Transform the conceptual design into one that is theoretically sound • Step 4 to 9: Physical database design • Transform the logical design into one that works with the chosen technology 2/CS/3X

  4. Step 1: Conceptual database design • 1.1 Identify entity types. • 1.2 Identify relationship types. • 1.3 Identify and associate attributes with entity types. • 1.4 Determine attribute domains. • 1.5 Determine candidate keys and primary keys. • 1.6 Consider use of enhanced modelling concepts. • 1.7 Check model for redundancy. • 1.8 Validate local conceptual model against user transactions. • 1.9 Review conceptual data model with users. 2/CS/3X

  5. Steps 2 and 3: Logical database design • Step 2: Build and validate logical data models for each view. 2/CS/3X

  6. Step 2.1 Remove features not compatible with the relational model (1) Remove many-many binary relationships. (2) Remove many-many recursive relationships. (3) Remove complex relationships. (4) Remove multivalued attributes. 2/CS/3X

  7. Step 2.2 Derive relations for logical data model (1) Strong entity types. (2) Weak entity types. (3) One-many binary relationship types. (4) One-one binary relationship types. (5) One-one recursive relationships (6) Super/subclass relationship types. (7) Many-many binary relationship types. (8) Complex relationship types. (9) Multivalued attributes 2/CS/3X

  8. Step 2.3 Validate by normalisation • First normal form removes repeating groups. • Second normal form removes partial dependencies on the primary key. • Third normal form removes transitive dependencies. • Boyce-Codd normal form removes remaining anomalies from functional dependencies. 2/CS/3X

  9. Validation of local LDM • Step 2.4 Validate relations against user transactions • Step 2.5 Define integrity constraints • Required data • Attribute domain constraints • Entity integrity • Referential integrity • Enterprise constraints (= business rules) • Step 2.6 Review local logical data model with user 2/CS/3X

  10. Referential integrity • (1) Insert a tuple into a child relation. • (2) Delete tuple from child relation. • (3) Update foreign key of child tuple. • (4) Insert tuple into parent relation. • (5) Delete tuple from parent relation • No action • Cascade • Set null • Set default • No check • (6) Update primary key of parent relations 2/CS/3X

  11. Step 3.1 Merge local LDMs in to Global LDM • Review names and contents of entities (relations) and their candidate keys. • Review names and contents of relationships and foreign keys. • Merge entities from local models. • Include entities unique to each local model. • Merge relationships from local models. • Include relationships unique to each local model. • Check for missing entities and relationships. • Check foreign keys. • Check integrity constraints. • Draw the global E-R diagram. • Update the documentation. 2/CS/3X

  12. Rest of step 3 • 3.2 Validate global logical data model • 3.3 Check for future growth • 3.4 Review global logical data model with users. 2/CS/3X

  13. Steps 4 to 9Physical database design • Step 4: Translate global LDM for target DBMS • Step 5: Design physical representation • Step 6: Design user views • Step 7: Design security mechanisms • Step 8: Consider introducing controlled redundancy • Step 9: Monitor and tune the operational system. 2/CS/3X

  14. Step 4: Translate LDM for target DBMS • Step 4.1 Design base relations • Step 4.2 Design representation of derived data. • Step 4.3 Design the enterprise constraints for target DBMS 2/CS/3X

  15. Step 5: Design physical representation (1) • Step 5.1 Analyze transactions • frequency of transaction • what relations and attributes are accessed • what is the type of access (query, insert, update, delete) • Note: if an attribute is updated, take care if it is to be included in a secondary index) • care with attributes used in predicates (WHERE), and the type of predicates (possible candidate for access structures (indexes)) • care with attributes used in joins (candidates for access structures) • time constraints 2/CS/3X

  16. Design physical representation: 2 • Step 5.2 Choose file organizations • DMBS may or may not give you authority in this matter • Step 5.3 Choose secondary indexes • DBMS should give you authority here • Step 5.4 Estimate disk space requirements 2/CS/3X

  17. Security and tuning • Step 6: Design user views • Step 7: Design security mechanisms • Step 6.1 Design user views • Step 6.2 Design access rules • Step 8: Consider the introduction of controlled redundancy • Step 9: Monitor and tune the operational system 2/CS/3X

  18. Key points • Conceptual design creates a number of entity-relationship models of the data. • Logical design merges the conceptual models, and creates a relational model. • Physical design organises the data on physical hardware devices. 2/CS/3X

More Related