1 / 24

Conceptual Database Design

Conceptual Database Design. Dale-Marie Wilson, Ph.D. Database Design Methodology. Design methodology Structured approach that uses procedures, techniques, tools, documentation aids to support and facilitate design process Three main phases Conceptual database design Logical database design

orenda
Télécharger la présentation

Conceptual Database Design

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. Conceptual Database Design Dale-Marie Wilson, Ph.D.

  2. Database Design Methodology • Design methodology • Structured approach that uses procedures, techniques, tools, documentation aids to support and facilitate design process • Three main phases • Conceptual database design • Logical database design • Physical database design

  3. Database Design Methodology • Conceptual database design • Process of constructing model of data used in an enterprise, independent of all physical considerations • Logical database design • Process of constructing model of data used in enterprise based on specific data model (e.g. relational) • Independent of particular DBMS and other physical considerations • Physical database design • Process of producing description of implementation of database on secondary storage • Describes base relations, file organizations, and indexes • Attempts to achieve efficient access to data, integrity constraints and security measures

  4. Critical Success Factors in Database Design • Work interactively with users • Follow structured methodology throughout data modeling process • Employ data-driven approach • Incorporate structural and integrity considerations into data models • Combine conceptualization, normalization, and transaction validation techniques into data modeling methodology

  5. Critical Success Factors in Database Design • Use diagrams to represent data models • Use Database Design Language (DBDL) represent additional data semantics • Build data dictionary to supplement data model diagrams • Be willing to repeat steps

  6. Overview Database Design Methodology Conceptual database design • Step 1 Build conceptual data model • 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, primary, and alternate key attributes

  7. Overview Database Design Methodology • Step 1 Build conceptual data model (continue) • Step 1.6 Consider use of enhanced modeling concepts (optional step) • Step 1.7 Check model for redundancy • Step 1.8 Validate conceptual model against user transactions • Step 1.9 Review conceptual data model with user

  8. Overview Database Design Methodology Logical database design for the relational model • Step 2 Build and validate logical data model • Step 2.1 Derive relations for logical data model • Step 2.2 Validate relations using normalization • Step 2.3 Validate relations against user transactions • Step 2.4 Define integrity constraints

  9. Overview Database Design Methodology • Step 2 Build and validate logical data model (continue) • Step 2.5 Review logical data model with user • Step 2.6 Merge logical data models into global model (optional step) • Step 2.7 Check for future growth

  10. Overview Database Design Methodology Physical database design for relational database • Step 3 Translate logical data model for target DBMS • Step 3.1 Design base relations • Step 3.2 Design representation of derived data • Step 3.3 Design general constraints

  11. Overview Database Design Methodology • Step 4 Design file organizations and indexes • Step 4.1 Analyze transactions • Step 4.2 Choose file organization • Step 4.3 Choose indexes • Step 4.4 Estimate disk space requirements

  12. Overview Database Design Methodology • Step 5 Design user views • Step 6 Design security mechanisms • Step 7 Consider the introduction of controlled redundancy • Step 8 Monitor and tune the operational system

  13. Step 1: Build Conceptual Data • Model comprised of: • Entity types, relationship types, attributes and attribute domains, primary and alternate keys, and integrity constraints • Step 1.1 Identify entity types • To identify required entity types • Step 1.2 Identify relationship types • To identify important relationships that exist between entity types • Step 1.3 Identify and associate attributes with entity or relationship types • To associate attributes with appropriate entity or relationship types and document details of each attribute • Step 1.4 Determine attribute domains • To determine domains for attributes in data model and document details of each domain

  14. Step 1: Build Conceptual Data • Step 1.5 Determine candidate, primary, and alternate key attributes • To identify candidate key(s) for each entity • If more than one candidate key => choose one as primary key • Step 1.6 Consider use of enhanced modeling concepts (optional step) • To consider use of enhanced modeling concepts • Specialization / generalization, aggregation, and composition • Step 1.7 Check model for redundancy • To check for presence of redundancy in model • Remove if exists • Step 1.8 Validate conceptual model against user transactions • To ensure conceptual model supports required transactions • Step1.9 Review conceptual data model with user • To review conceptual data model with user to ensure model is ‘true’ representation of data requirements of enterprise

  15. Extract - data dictionary of entities for Staff user views of DreamHome

  16. 1st Version - ER diagram of Staff user views of DreamHome

  17. Extract - data dictionary of relationships for Staff user views of DreamHome

  18. Extract - data dictionary of attributes for Staff user views of DreamHome

  19. ER diagram of Staff user views of DreamHome

  20. Example – Removal of redundant relationship called Rents

  21. Example of non-redundant relationship FatherOf

  22. Question • Your job is to design a database for the XYZ Company.  They are part suppliers.  Their customers order parts from them and they get the parts, or items, from various suppliers.  An order may consists of several items.  Each item, or part, has an unique identifier

  23. places M 1 M Customer M Order 1 1 Parts M M Supply controls Consists 1 1 Salesperson Suppliers • Suppliers • supplierID • companyName • phoneNo • address1 • address2 • city • state • zip • contactName • Part • partID • price • description • qty • supplierID • Order • orderID • customerID • ssn • totalPrice • orderDate • Customer • customerID • fName • middleI • lName • address1 • address2 • city • state • zip • Salesperson • ssn • fName • middleI • lName • address1 • address2 • city • state • zip • dateOfBirth • Consists • partID • orderID • qty

  24. Example – EasyDrive School of Motoring • See handouts.

More Related