Chapter 9 Conceptual Database Design Transparencies 1 Objectives

Chapter 9 Conceptual Database Design Transparencies 1

Objectives What a design methodology is. Database design has three main phases: conceptual, logical, and physical design. Critical success factors in database design. About a step-by-step methodology for database design. The steps involved in conceptual database design. The documentation produced during conceptual database design. ©Pearson Education 2009 2

Introduction to the database design methodology If the database is reasonably complex, a systematic approach is needed to design and build the database to ensure that it satisfies users’ requirements and achieves stated performance requirements. This systematic approach is called a database design methodology. ©Pearson Education 2009 3

What is a design methodology? A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. ©Pearson Education 2009 4

Phases of database design Three main phases: conceptual database design, logical database design, physical database design. Conceptual database design The process of constructing a model of the data used in an organization independent of all physical considerations. ©Pearson Education 2009 5

Phases of database design Logical database design Process of constructing a model of data used in an organization based on a specific data model, but independent of a particular DBMS and other physical considerations. ©Pearson Education 2009 6

Phases of database design Physical database design Process of producing a description of the implementation of the database on secondary storage; it describes the base tables, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security restrictions. ©Pearson Education 2009 7

Critical success factors in database design Work interactively with the 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 normalization and transaction validation techniques in the methodology. ©Pearson Education 2009 8

Critical success factors in database design Use diagrams to represent as much of the data models as possible. Use a database design language (DBDL). Critical success factors in database design Build a data dictionary to supplement the data model diagrams. Be willing to repeat steps. ©Pearson Education 2009 9

Overview of the database design methodology ©Pearson Education 2009 10

Step 1: Conceptual database design (Create ER model) Step 1. 1 Identify entities Step 1. 2 Identify relationships Step 1. 3 Identify and associate attributes with entities or relationships Step 1. 4 Determine attribute domains Step 1. 5 Determine candidate, primary, and alternate key attributes ©Pearson Education 2009 11

Step 1: Conceptual database design methodology (Create ER model) Step 1. 6 Specialize/Generalize entities (optional step) Step 1. 7 Check model for redundancy Step 1. 8 Check model supports user transactions Step 1. 9 Review conceptual database design with users ©Pearson Education 2009 12

Extract from the data dictionary ©Pearson Education 2009 13

Main relationships for Stay. Home Online Rentals ©Pearson Education 2009 14

First draft ER diagram ©Pearson Education 2009 15

Multiplicity constraints for the relationships ©Pearson Education 2009 16

Adding multiplicity constraints to the ER diagram ©Pearson Education 2009 17

Extract from the data dictionary ©Pearson Education 2009 18

ER diagram for Stay. Home Online Rentals showing primary keys ©Pearson Education 2009 19

(a) Distribution. Centre is a synonym for Warehouse (b) merging entities ©Pearson Education 2009 20

Remove the redundant Drives relationship ©Pearson Education 2009 21

Check ER model supports data requirements ©Pearson Education 2009 22

ER diagram showing all entities, relationships, and attributes ©Pearson Education 2009 23