Practical Database DesignMethodology and Use of UML Diagrams

Practical Database DesignMethodology and Use of UML Diagrams

Chapter 10 Practical Database Design Methodology and Use of UML Diagrams Copyright 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Chapter 10 Outline The Role of Information Systems in Organizations The Database Design and Implementation Process

Use of UML Diagrams as an Aid to Database Design Specification Rational Rose: A UML-Based Design Tool Automated Database Design Tools Copyright 2011 Ramez Elmasri and Shamkant Navathe Practical Database Design Methodology and Use of UML Diagrams Design methodology Target database managed by some type of database management system

Various design methodologies Large database Several dozen gigabytes of data and a schema with more than 30 or 40 distinct entity types Copyright 2011 Ramez Elmasri and Shamkant Navathe The Role of Information Systems in Organizations Organizational context for using database systems

Organizations have created the position of database administrator (DBA) and database administration departments Information technology (IT) and information resource management (IRM) departments Key to successful business management Copyright 2011 Ramez Elmasri and Shamkant Navathe The Role of Information Systems in Organizations (contd.)

Database systems are integral components in computer-based information systems Personal computers and database system-like software products Utilized by users who previously belonged to the category of casual and occasional database users Personal databases gaining popularity Databases are distributed over multiple computer systems Better local control and faster local processing

Copyright 2011 Ramez Elmasri and Shamkant Navathe The Role of Information Systems in Organizations (contd.) Data dictionary systems or information repositories Mini DBMSs Manage meta-data High-performance transaction processing

systems require around-the-clock nonstop operation Performance is critical Copyright 2011 Ramez Elmasri and Shamkant Navathe The Information System Life Cycle Information system (IS) Resources involved in collection, management, use, and dissemination of information resources of organization

Copyright 2011 Ramez Elmasri and Shamkant Navathe The Information System Life Cycle Macro life cycle Feasibility analysis Requirements collection and analysis Design

Implementation Validation and acceptance testing Requirements collection and analysis Copyright 2011 Ramez Elmasri and Shamkant Navathe The Information System Life Cycle (contd.) The database application system life cycle: micro life cycle

System definition Database design Database implementation Loading or data conversion Copyright 2011 Ramez Elmasri and Shamkant Navathe The Information System Life Cycle (contd.) Application conversion

Testing and validation Operation Monitoring and maintenance Copyright 2011 Ramez Elmasri and Shamkant Navathe The Database Design and Implementation Process Design logical and physical structure of one or more databases Accommodate the information needs of the users in an organization for a defined set of applications

Goals of database design Very hard to accomplish and measure Often begins with informal and incomplete requirements Copyright 2011 Ramez Elmasri and Shamkant Navathe The Database Design and Implementation Process (contd.) Main phases of the overall database design and implementation process:

1. Requirements collection and analysis 2. Conceptual database design 3. Choice of a DBMS 4. Data model mapping (also called logical database design) 5. Physical database design 6. Database system implementation and tuning Copyright 2011 Ramez Elmasri and Shamkant Navathe

Copyright 2011 Ramez Elmasri and Shamkant Navathe The Database Design and Implementation Process (contd.) Parallel activities Data content, structure, and constraints of the database Design of database applications Data-driven versus process-driven design Feedback loops among phases and within

phases are common Copyright 2011 Ramez Elmasri and Shamkant Navathe The Database Design and Implementation Process (contd.) Heart of the database design process Conceptual database design (Phase 2) Data model mapping (Phase 4) Physical database design (Phase 5)

Database system implementation and tuning (Phase 6) Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 1: Requirements Collection and Analysis Activities Identify application areas and user groups Study and analyze documentation

Study current operating environment Collect written responses from users Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 1 (contd.) Requirements specification techniques Oriented analysis (OOA) Data flow diagrams (DFDs Refinement of application goals

Computer-aided Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 2: Conceptual Database Design Phase 2a: Conceptual Schema Design Important to use a conceptual high-level data model Approaches to conceptual schema design Centralized (or one shot) schema design approach View integration approach

Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 2: (contd.) Strategies for schema design Top-down strategy

Bottom-up strategy Inside-out strategy Mixed strategy Schema (view) integration Identify correspondences/conflicts among schemas: Naming conflicts, type conflicts, domain (value set) conflicts, conflicts among constraints Modify views to conform to one another Merge of views and restructure Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 2: (contd.)

Strategies for the view integration process Binary ladder integration N-ary integration Binary balanced strategy Mixed strategy Phase 2b: Transaction Design

In parallel with Phase 2a Specify transactions at a conceptual level Identify input/output and functional behavior Notation for specifying processes Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 3: Choice of a DBMS Costs to consider

Software acquisition cost Maintenance cost Hardware acquisition cost Database creation and conversion cost Personnel cost Training cost Operating cost Consider DBMS portability among different types of hardware

Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 4: Data Model Mapping (Logical Database Design) Create a conceptual schema and external schemas In data model of selected DBMS Stages System-independent mapping

Tailoring schemas to a specific DBMS Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 5: Physical Database Design Choose specific file storage structures and access paths for the database files Achieve good performance Criteria used to guide choice of physical database design options:

Response time Space utilization Transaction throughput Copyright 2011 Ramez Elmasri and Shamkant Navathe Phase 6: Database System Implementation and Tuning Typically responsibility of the DBA

Compose DDL Load database Convert data from earlier systems Database programs implemented by application programmers Most systems include monitoring utility to collect performance statistics Copyright 2011 Ramez Elmasri and Shamkant Navathe Use of UML Diagrams as an Aid to Database Design Specification Use UML as a design specification standard

Unified Modeling Language (UML) approach Combines commonly accepted concepts from many object-oriented (O-O) methods and methodologies Includes use case diagrams, sequence diagrams, and statechart diagrams Copyright 2011 Ramez Elmasri and Shamkant Navathe UML for Database Application

Design Advantages of UML Resulting models can be used to design relational, object-oriented, or object-relational databases Brings traditional database modelers, analysts, and designers together with software application developers Copyright 2011 Ramez Elmasri and Shamkant Navathe

Different Types of Diagrams in UML Structural diagrams Class diagrams and package diagrams Object diagrams Component diagrams Deployment diagrams Copyright 2011 Ramez Elmasri and Shamkant Navathe

Different Types of Diagrams in UML (contd.) Behavioral diagrams Use case diagrams Sequence diagrams Collaboration diagrams Statechart diagrams Activity diagrams

Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Different Types of Diagrams in UML (contd.) Copyright 2011 Ramez Elmasri and Shamkant Navathe Different Types of Diagrams in UML (contd.) Copyright 2011 Ramez Elmasri and Shamkant Navathe Modeling and Design Example:

UNIVERSITY Database Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Rational Rose: A UML-Based Design Tool Rational Rose for database design Modeling tool used in the industry to develop information systems

Rational Rose data modeler Visual modeling tool for designing databases Provides capability to: Forward engineer a database Reverse engineer an existing implemented database into conceptual design Copyright 2011 Ramez Elmasri and Shamkant Navathe Data Modeling Using Rational Rose Data Modeler

Reverse engineering Allows the user to create a conceptual data model based on an existing database schema specified in a DDL file Forward engineering and DDL generation Create a data model directly from scratch in Rose Generate DDL for a specific DBMS Copyright 2011 Ramez Elmasri and Shamkant Navathe

Data Modeling Using Rational Rose Data Modeler (contd.) Conceptual design in UML notation Build ER diagrams using class diagrams in Rational Rose Identifying relationships Object in a child class cannot exist without a corresponding parent object

Non-identifying relationships Specify a regular association (relationship) between two independent classes Copyright 2011 Ramez Elmasri and Shamkant Navathe Data Modeling Using Rational Rose Data Modeler (contd.) Converting logical data model to object model and vice versa Logical data model can be converted to an object model

Allows a deep understanding of relationships between conceptual and implementation models Copyright 2011 Ramez Elmasri and Shamkant Navathe Data Modeling Using Rational Rose Data Modeler (contd.) Synchronization between the conceptual design and the actual database Extensive domain support Create a standard set of user-defined data types

Easy communication among design teams Application developer can access both the object and data models Copyright 2011 Ramez Elmasri and Shamkant Navathe Automated Database Design Tools Many CASE (computer-aided software engineering) tools for database design Combination of the following facilities

Diagramming Model mapping Design normalization Copyright 2011 Ramez Elmasri and Shamkant Navathe Automated Database Design Tools (contd.) Characteristics that a good design tool should possess:

Easy-to-use interface Analytical components Heuristic components Trade-off analysis Display of design results Design verification Copyright 2011 Ramez Elmasri and Shamkant Navathe Automated Database Design

Tools (contd.) Variety of products available Some use expert system technology Copyright 2011 Ramez Elmasri and Shamkant Navathe Summary Six phases of the design process Commonly include conceptual design, logical design (data model mapping), physical design

UML diagrams Aid specification of database models and design Rational Rose and the Rose Data Modeler Provide support for the conceptual design and logical design phases of database design Copyright 2011 Ramez Elmasri and Shamkant Navathe

Recently Viewed Presentations

  • Clinical Research Trial Office

    Clinical Research Trial Office

    The President determines in writing that consent: Is not feasible. Is contrary to the best interests of the military member. ... Examples: powered wheelchairs, infusion pumps, and surgical drapes. Device Class III. Most stringent regulatory category for devices.
  • Professional Growth Plans - crowleys.crsc.k12.ar.us

    Professional Growth Plans - crowleys.crsc.k12.ar.us

    Because I am a science teacher and need new approaches to helping my non-readers, I will learn about research-based strategies to improve student learning in reading comprehension. I will incorporate new strategies into my daily instructional practices. Student achievement will...
  • 19Mar02 - University of Chicago

    19Mar02 - University of Chicago

    (see Cheng-Ju's talk) Our goal is still March … Commissioning manpower One thing I am proud of: many people are working on physics analysis, or taking class, or other tasks while commissioning the Pulsar L2 system in the past few...
  • Partnerships, Policies Promotions & Reviews James Berresford Chief

    Partnerships, Policies Promotions & Reviews James Berresford Chief

    In September, we hosted a stand at the World Route Development Forum in Chicago and were joined on our stand by Newcastle, Liverpool John Lennon, Leeds-Bradford, Cambridge and Bristol airports as well as Bath Tourism Plus, Destination Bristol, Leeds and...
  • Narrative Writing - Montgomery Township School District

    Narrative Writing - Montgomery Township School District

    FULL-CIRCLE ENDING: Wrap-up endings; close by repeating a phrase or anecdote you began with The figurative language (similes, metaphors, etc.), sensory imagery, vivid verbs, strong sentences, dialogue, and point of view that makes each author unique.
  • State of the Budget - Colorado

    State of the Budget - Colorado

    Understand, post Amendment 66, that the public's appetite for statewide tax increases is limited. ... My job is consistently throw a wet blanket on the hopes and dreams of policymakers. Now that I've cast my dark shadow over this group,...
  • Warm Up - 10/16/09

    Warm Up - 10/16/09

    Rules for Balancing an Equation: 1. Write out the numbers of each element for each side. 2. Add . coefficients . to balance elements ONE AT A TIME. Keep a running tally of the numbers of each element.
  • National School Nutrition Programme

    National School Nutrition Programme

    NATIONAL SCHOOL NUTRITION PROGRAMME 90 Day Report to the Select Committee on Education and Recreation, NCOP June 2004 INTRODUCTION Programme taken over from Health in April 2004 - 90 days ago A very large programme (R800 million per year), delivered...