Appendix C Advanced logical database design Transparencies Pearson
Appendix C Advanced logical database design Transparencies © Pearson Education Limited, 2004 1
Appendix C - Objectives n n How to merge local logical data models into a global logical data model of the organization. How to ensure that the resultant global model is a true and accurate representation of the data requirements of the organization (or part of the organization) being modeled. © Pearson Education Limited, 2004 2
Advanced logical database design n This part of methodology is about what to do when creating a reasonably complex database system with several user views and you have chosen to manage these user views (wholly or partly) using the view integration approach. © Pearson Education Limited, 2004 3
Advanced logical database design n Assumes you have created local data models each representing one or more user views using Steps 1 and 2 of the database design methodology. © Pearson Education Limited, 2004 4
Advanced logical database design n n Stay. Home case study describes 5 user views including: Director, Manager, Supervisor, Assistant, and Buyer. Following analysis of the requirements for each user view, we decided to manage these user views using a mixture of the centralized and view integration approaches. © Pearson Education Limited, 2004 5
Advanced logical database design n We used the centralized approach to merge the requirements for the n n Manager, Supervisor, and Assistant user views into a collection of user views called Branch. Director and Buyer user views into a collection of user views called Business. © Pearson Education Limited, 2004 6
Advanced logical database design n n We now use the local logical data models for the Branch and Business user views to demonstrate how to use the view integration approach to merge data models. Described in Step 2. 6, which is a continuation of Step 2. © Pearson Education Limited, 2004 7
Business user views of Stay. Home - ER diagram n © Pearson Education Limited, 2004 8
Business user views of Stay. Home - Tables © Pearson Education Limited, 2004 9
Step 2. 6 Build and check global logical data model n To combine the individual local logical data models into a single global logical data model that represent the data requirements of the organization (or part of the organization) that is being modeled. © Pearson Education Limited, 2004 10
Step 2. 6 Build and check global logical data model - Tasks n n Step 2. 6. 1 Merge local logical data models into global model Step 2. 6. 2 Check global logical data model Step 2. 6. 3 Check for future growth Step 2. 6. 4 Review global logical data model with users © Pearson Education Limited, 2004 11
Step 2. 6. 1 Merge local logical data models into global model n Some typical tasks of this approach are: n n n Review the names and contents of entities/tables and their primary keys. Review the names and contents of relationships/foreign keys. Merge entities/tables from the local data models. Include (without merging) entities/tables unique to each local data model. Merge relationships/foreign keys from the Pearson Education Limited, local data© models. 2004 12
Step 2. 6. 1 Merge local logical data models into global model n Some typical tasks of this approach (continued) are: n n n Include (without merging) relationships/foreign keys unique to each local data model. Check for missing entities/tables and relationships/foreign keys. Check integrity constraints. Draw the global ER/table diagram. Update the documentation. © Pearson Education Limited, 2004 13
Comparison of tables and primary keys © Pearson Education Limited, 2004 14
Comparison of relationships / foreign keys © Pearson Education Limited, 2004 15
Merge entities/tables from the local data models - Tasks n n n Merge entities/tables with the same name and the same primary key. Merge entities/tables with the same name using different primary keys. Merge entities/tables with different names using the same or different primary keys. © Pearson Education Limited, 2004 16
Include (without merging) entities/tables unique to each local data model n n Previous tasks should identify all entities/tables that are the same. All remaining entities/tables are included in the global model without change. © Pearson Education Limited, 2004 17
Merge relationships/foreign keys from the local data models n n Examine the name and purpose of each relationship/foreign keys in the data models. Before merging it’s important to resolve any conflicts between the relationships such as differences in multiplicity constraints. © Pearson Education Limited, 2004 18
Include (without merging) relationships/foreign keys unique to each local data model n All remaining relationships/foreign keys are included in the global model without change. © Pearson Education Limited, 2004 19
Check for missing entities/tables and relationships/foreign keys n Activities include: n n n Use corporate data model, if exists. Ask users of a specific user view to pay attention to the entities/tables and relationships/foreign keys that also exist in other user views. Examine the attributes/columns of each entity/table and look for references to entities/tables in other local data models. © Pearson Education Limited, 2004 20
Check foreign keys n n During this step, entities/tables and relationships may have been merged, primary keys changed, and new relationships identified. Check that the foreign keys in child tables are still correct, and make any necessary modifications that are required © Pearson Education Limited, 2004 21
Tables for the global logical data model of Stay. Home © Pearson Education Limited, 2004 22
Check integrity constraints and business rules n Check that the integrity constraints and business rules for the global logical data model do not conflict with those originally specified for each user view. © Pearson Education Limited, 2004 23
Check integrity constraints and business rules n n If any new relationships have been identified and new foreign keys have been created, ensure that appropriate referential integrity constraints are specified. Any conflicts must be resolved in consultation with the users. © Pearson Education Limited, 2004 24
Draw the global ER/table diagram © Pearson Education Limited, 2004 25
Update the documentation n Update the documentation to reflect any changes made during the development of the global data model. © Pearson Education Limited, 2004 26
Step 2. 6. 2 Check global logical data model n To check the tables created from the global logical data model are appropriately structured using normalization and support the required transactions, if necessary. © Pearson Education Limited, 2004 27
Step 2. 6. 3 Check for future growth n To determine whethere any significant changes likely in the foreseeable future and to assess whether the global logical data model can accommodate these changes. © Pearson Education Limited, 2004 28
Step 2. 6. 4 Review global logical data model with users n To ensure that the global logical data model is a true representation of the data requirements of an organization. © Pearson Education Limited, 2004 29
- Slides: 29