Lecture Fourteen Methodology Conceptual Database Design Based on
![Lecture Fourteen Methodology Conceptual Database Design Based on Chapter Fourteen of this book: Database Lecture Fourteen Methodology Conceptual Database Design Based on Chapter Fourteen of this book: Database](https://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-1.jpg)
Lecture Fourteen Methodology Conceptual Database Design Based on Chapter Fourteen of this book: Database Systems: A Practical Approach to Design, Implementation and Management International Computer Science S. Carolyn Begg, Thomas Connolly 1
![Lecture 14 - Objectives u Purpose of a design methodology. u Database design has Lecture 14 - Objectives u Purpose of a design methodology. u Database design has](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-2.jpg)
Lecture 14 - Objectives u Purpose of a design methodology. u Database design has three main phases: conceptual, logical, and physical design. u How to decompose the scope of the design into specific users’ views of the enterprise. u How to use ER modeling to build a local conceptual data model based on information given in a view of the enterprise. 2
![Lecture 14 - Objectives u How to validate resultant conceptual model to ensure it Lecture 14 - Objectives u How to validate resultant conceptual model to ensure it](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-3.jpg)
Lecture 14 - Objectives u How to validate resultant conceptual model to ensure it is a true and accurate representation of a view of the enterprise. u How to document process of conceptual database design. u End-users play an integral role throughout process of conceptual database design. 3
![Design Methodology u Structured approach that uses procedures, techniques, tools, and documentation aids to Design Methodology u Structured approach that uses procedures, techniques, tools, and documentation aids to](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-4.jpg)
Design Methodology u Structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. u Database design methodology has 3 main phases: – Conceptual database design – Logical database design – Physical database design. 4
![Conceptual/Logical Database Design u Conceptual database design – Process of constructing a model of Conceptual/Logical Database Design u Conceptual database design – Process of constructing a model of](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-5.jpg)
Conceptual/Logical Database Design u Conceptual database design – Process of constructing a model of information used in an enterprise, independent of all physical considerations. u 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. 5
![Physical Database Design u Process of producing a description of the implementation of the Physical Database Design u Process of producing a description of the implementation of the](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-6.jpg)
Physical Database Design u Process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes design used to achieve efficient access to the data, and any associated integrity constraints and security measures. 6
![Critical Success Factors in Database Design u Work interactively with users as much as Critical Success Factors in Database Design u Work interactively with users as much as](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-7.jpg)
Critical Success Factors in Database Design u Work interactively with users as much as possible. u Follow a structured methodology throughout the data modeling process. u Employ a data-driven approach. u Incorporate structural and integrity considerations into the data models. u Combine conceptualization, normalization, and transaction validation techniques into the data modeling methodology. 7
![Critical Success Factors in Database Design u Use diagrams to represent as much of Critical Success Factors in Database Design u Use diagrams to represent as much of](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-8.jpg)
Critical Success Factors in Database Design u Use diagrams to represent as much of the data models as possible. u Use a Database Design Language (DBDL) to represent additional data semantics. u Build a data dictionary to supplement the data model diagrams. u Be willing to repeat steps. 8
![Methodology Overview - Conceptual Database Design u Step 1 Build local conceptual data model Methodology Overview - Conceptual Database Design u Step 1 Build local conceptual data model](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-9.jpg)
Methodology Overview - Conceptual Database Design u 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 (optional step) – 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 9
![Step 1 Build local conceptual data model for each view To build a local Step 1 Build local conceptual data model for each view To build a local](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-10.jpg)
Step 1 Build local conceptual data model for each view To build a local conceptual data model of an enterprise for each specific view. u Step 1. 1 Identify entity types – To identify the main entity types that are required by the view. u Step 1. 2 Identify relationship types – To identify the important relationships that exist between the entity types that have been identified. 10
![First-cut ER diagram for Staff view of Dream. Home 11 First-cut ER diagram for Staff view of Dream. Home 11](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-11.jpg)
First-cut ER diagram for Staff view of Dream. Home 11
![Extract from data dictionary for Staff view of Dream. Home showing description of entities Extract from data dictionary for Staff view of Dream. Home showing description of entities](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-12.jpg)
Extract from data dictionary for Staff view of Dream. Home showing description of entities 12
![Extract from data dictionary for Staff view of Dream. Home showing description of relationships Extract from data dictionary for Staff view of Dream. Home showing description of relationships](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-13.jpg)
Extract from data dictionary for Staff view of Dream. Home showing description of relationships 13
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 3 Step 1 Build Local Conceptual Data Model from User View u Step 1. 3](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-14.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 3 Identify and associate attributes with entity or relationship types – To identify and associate attributes with the appropriate entity or relationship types and document the details of each attribute. u Step 1. 4 Determine attribute domains – To determine domains for the attributes in the local conceptual model and document the details of each domain. 14
![Extract from data dictionary for Staff view of Dream. Home showing description of attributes Extract from data dictionary for Staff view of Dream. Home showing description of attributes](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-15.jpg)
Extract from data dictionary for Staff view of Dream. Home showing description of attributes 15
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 5 Step 1 Build Local Conceptual Data Model from User View u Step 1. 5](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-16.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 5 Determine candidate and primary key attributes – To identify the candidate key(s) for each entity and if there is more than one candidate key, to choose one to be the primary key. 16
![ER diagram for Staff view of Dream. Home with primary keys added 17 ER diagram for Staff view of Dream. Home with primary keys added 17](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-17.jpg)
ER diagram for Staff view of Dream. Home with primary keys added 17
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 6 Step 1 Build Local Conceptual Data Model from User View u Step 1. 6](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-18.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 6 Consider use of enhanced modeling concepts (optional step) – To consider the use of enhanced modeling concepts, such as specialization / generalization, aggregation, and composition. 18
![Revised ER diagram for Staff view of Dream. Home with specialization / generalization 19 Revised ER diagram for Staff view of Dream. Home with specialization / generalization 19](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-19.jpg)
Revised ER diagram for Staff view of Dream. Home with specialization / generalization 19
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 7 Step 1 Build Local Conceptual Data Model from User View u Step 1. 7](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-20.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 7 Check model for redundancy – To check for the presence of any redundancy in the model. 20
![Example of a redundant relationship Employs 21 Example of a redundant relationship Employs 21](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-21.jpg)
Example of a redundant relationship Employs 21
![Example of a non-redundant relationship Father. Of 22 Example of a non-redundant relationship Father. Of 22](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-22.jpg)
Example of a non-redundant relationship Father. Of 22
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 8 Step 1 Build Local Conceptual Data Model from User View u Step 1. 8](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-23.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 8 Validate local conceptual model against user transactions – To ensure that the local conceptual model supports the transactions required by the view. 23
![Example: Describing the transaction u Appendix A Transaction (d) – List the details of Example: Describing the transaction u Appendix A Transaction (d) – List the details of](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-24.jpg)
Example: Describing the transaction u Appendix A Transaction (d) – List the details of properties managed by a named member of staff u Transaction description – Details of properties on Property. For. Rent entity, details of staff on Staff entity. Use relationship Staff Manges Property. For. Rent to produce required list. 24
![Using pathways to check that the conceptual model supports the user transactions 25 Using pathways to check that the conceptual model supports the user transactions 25](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-25.jpg)
Using pathways to check that the conceptual model supports the user transactions 25
![Step 1 Build Local Conceptual Data Model from User View u Step 1. 9 Step 1 Build Local Conceptual Data Model from User View u Step 1. 9](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-26.jpg)
Step 1 Build Local Conceptual Data Model from User View u Step 1. 9 Review local conceptual data model with user – To review the local conceptual data model with the user to ensure that the model is a ‘true’ representation of the user’s view of the enterprise. 26
![Methodology Overview - Conceptual Database Design u Step 1 Build local conceptual data model Methodology Overview - Conceptual Database Design u Step 1 Build local conceptual data model](http://slidetodoc.com/presentation_image_h/2d8877529baeea86e62af517a8e1c74c/image-27.jpg)
Methodology Overview - Conceptual Database Design u 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 (optional step) – 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 27
- Slides: 27