Lec 4 Practical Database Design Methodology and Use

Chapter Outline n n n Information System Life Cycle Phases of Database Design UML Diagrams n n n Rational Rose Other tools Design Tools Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 2

Organizational Context for using Database Systems n n Consolidation and integration of data across organization Maintenance of complex data Simplicity of developing new applications Data independence n n Protecting application programs from changes in the underlying logical organization and in the physical access paths and storage structures External Schemas n Allow the same data to be used for multiple applications with each application having its own view of the data Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 3

Information System n n Information System includes all resources involved in the collection, management, use and dissemination of the information resources of the organization We consider two systems life cycles: n Macro Life Cycle n n Information System Life Cycle Micro Life Cycle n Database System Life Cycle Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 4

Phases of Information System Life Cycle n Feasibility Analysis n n n Requirement Collection and Analysis n n n Analyzing potential application areas Identifying the economics of information gathering and dissemination Performing cost benefit studies Setting up priorities among applications Detailed Requirements Collection Interaction with Users Design n n Design of Database System Design of programs that use and process the database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 5

Phases of Information System Life Cycle (contd. ) n Implementation n Validation and Acceptance Testing n n n Information system is implemented Database is loaded & its transactions are implemented and tested Testing against user’s requirements Testing against performance criteria Deployment, Operation and Maintenance n n n Data conversion Training System maintenance Performance monitoring Database tuning Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 6

Database System Life Cycle n System definition n n Database Design n n Defining scope of database system, its users and applications Logical and physical design of the database system on the chosen DBMS Database implementation n Specifying conceptual, external and internal database definitions Creating empty database files Implementing software applications Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 7

Database System Life Cycle (contd. ) n Loading or data conversion n n Application conversion n Converting applications to the new system Testing and validation Operation n n Populating the database Running the new system Monitoring and maintenance n n System maintenance Performance monitoring Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 8

Database Design Process n Problem n n Design the logical and physical structure of one or more databases to accommodate the information needs of the users in an organization for a defined set of applications. Goals n n n Satisfy the content requirements Provide easy structuring of information Support processing requirements and performance objectives Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 9

Phases of database design and implementation for large databases. Copyright © 2007 Ramez Elmasri

Phases of Database Design and Implementation Process n n n Requirements Collections and Analysis Conceptual Database Design Choice of a DBMS Data Model Mapping (Logical Database Design) Physical Database Design Database System Implementation and Tuning Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 11

Phases of Database Design and Implementation Process (contd. ) n Requirements Collections and Analysis n n n Identifying Users Interacting with users to gather requirements Time consuming BUT very important n n Very expensive to fix requirements error Conceptual Database Design n n Produce a conceptual schema for the database Involves two parallel activities n n Conceptual Schema Design Transaction and Application Design Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 12

Conceptual Schema Design n Goal n n Complete understanding of the database structure, semantics, interrelationships and constraints Serves as a stable description of the database contents Good understanding crucial for the users and designers Diagrammatic description serves as an excellent communication tool Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 13

Desired Characteristics of Conceptual Data Model n Expressiveness n n Simplicity and Understandability n n Small number of distinct basic concepts Diagrammatic Representation n n Easy to understand Minimality n n Able to distinguish different types of data, relationships and constraints Diagrammatic notation for representing conceptual schema Formality n Formal unambiguous specification of data Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 14

Approaches to Conceptual Schema Design n Centralized Schema Design Approach n n Also known as one-shot approach Requirements of different applications and user groups are merged into a single set of requirements and a single schema is designed Time consuming, places the burden on DBA to reconcile conflicts View Integration Approach n n n Schema is designed for each user group or application These schemas are then merged into a global conceptual schema during the view integration phase More practical Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 15

Strategies for Schema Design n Top Down Strategy n Start with a schema containing highlevel abstractions and then apply successive topdown refinements Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 16

Strategies for Schema Design (contd. ) n Bottom-Up Strategy n Start with a schema containing basics abstractions and then combine or add to these abstractions Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 17

Strategies for Schema Design (contd. ) n Inside-out Strategy n n Start with central set of concepts and then spread outward by considering new concepts in the vicinity of existing ones Mixed Strategy n Use a combination of top-down and bottom-up strategies Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 18

Schema Integration n Identifying correspondence and conflict among different schemas n Naming conflicts n Synonyms: The same concept but different names n n Homonyms: Different concepts but same n n e. g. DEPARTMENT may be an entity type and an attribute Domain Conflicts: Attribute has different domains n n n e. g. entity type PART as computer parts and furniture parts Type Conflicts: Representing the same concept by different modeling constructs n n e. g. entity types CUSTOMER and CLIENT Also known as value set conflicts e. g. SSN as an integer and as a character string Conflict among constraints: Two schemas impose different constraints n e. g. different key of an entity type in different schemas Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 19

Schema Integration (contd. ) n Modifying views to conform to one another n n Modifying schemas to conform to one another Merging of views n n Merging Schemas to create a global schema Specifying mappings between views and global schema n n Time consuming and difficult Restructuring n Simplifying and restructuring to remove any redundancies Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 20

Modifying views to conform before integration. Copyright © 2007 Ramez Elmasri and Shamkant B.

(continued) Modifying views to conform before integration. Copyright © 2007 Ramez Elmasri and Shamkant

Integrated schema after merging views 1 and 2 Copyright © 2007 Ramez Elmasri and

View Integration Strategies n Binary Ladder Integration n Two similar schemas are integrated first and the resulting schema is then integrated with another schema The process is repeated until all schemas are integrated N-ary Integration n All views are integrated in one procedure after analysis and specification of their correspondences n Requires computerized tools for large designs Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 24

View Integration Strategies (contd. ) n Binary Balanced Strategy n n n Pairs of schemas are integrated first and the resulting schemas are then paired for further integration. This process is repeated until a final global schema Mixed Strategy n n Schemas are partitioned into groups based on their similarity and each group is integrated separately. This process is repeated until a final global schema Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 25

View Integration Strategies (contd. ) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Transaction Design n n Design characteristics of known database transactions in a DBMS Types of Transactions n Retrieval Transactions n n Update data Mixed Transactions n n Used to retrieve data Combination of update and retrieval Techniques for Specifying Transactions n n Input/output Functional Behavior Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 27

Choice of DBMS n Many factors to consider n Technical Factors n n Economic Factors n n n Type of DBMS: Relational, object-relational, object etc. Storage Structures Architectural options Acquisition, maintenance, training and operating costs Database creation and conversion cost Organizational Factors n Organizational philosophy n n Relational or Object Oriented Vendor Preference Familiarity of staff with the system Availability of vendor services Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 28

Logical Database Design n Transform the Schema from high-level data model into the data model of the selected DBMS. Design of external schemas for specific applications Two stages 1. System-independent mapping n 2. Tailoring the schemas to a specific DBMS n n DBMS independent mapping Adjusting the schemas obtained in step 1 to conform to the specific implementation features of the data model used in the selected DBMS Result n DDL statements in the language of the chosen DBMS Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 29

Physical Database Design n n Design the specifications for the stored database in terms of physical storage structures, record placements and indexes. Design Criteria n Response Time n n Space Utilization n n Storage space used by database files and their access path structures Transaction throughput n n n Elapsed Time between submitting a database transaction for execution and receiving a response Average number of transactions/minute Must be measured under peak conditions Result n Initial determination of storage structures and access paths for database files Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 30

Database System Implementation and Tuning n n During this phase database and application programs are implemented, tested and deployed Database Tuning n n System and Performance Monitoring Data indexing Reorganization Tuning is a continuous process Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 31

UML Diagrams n Class Diagrams n n n Object Diagrams n n Show a set of objects and their relationships Component Diagrams n n Capture the static structure of the system Represent classes, Interfaces, dependencies, generalizations and other relationships Show the organizations and dependencies among software components Deployment Diagrams n Represent the distribution of components across the hardware topology Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 32

Class diagram Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

Object Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

Component Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

Deployment Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

UML Diagrams (contd. ) n Use Case Diagrams n n n Model the functional interactions between users and system Describe scenarios of use Serve as a communication tool between users and developers Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 37

Use Case Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12

Example of Use Case Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

UML Diagrams (contd. ) n Sequence Diagrams n n Represent interactions between various objects over time Relate uses cases and class diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 40

Sequence Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

Example of Sequence Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide

UML Diagrams (contd. ) n Collaboration Diagrams n n Represent interactions between objects as a series of sequenced messages Statechart Diagram n n Describe how an object’s state changes in response to external events Consist of states, transitions, activities and events Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 43

Collaboration Diagrams Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

Statechart Diagram Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 -

UML Diagrams (contd. ) n Activity Diagrams n n Model the flow of control

Salient Features of Rational Rose Data Modeler n n UML based modeling tool for designing databases Reverse Engineering n n Generate a conceptual data model from an existing DBMS database or DDL Forward Engineering n n Create application/data model Generate DDL from data model Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 47

Salient Features of Rational Rose Data Modeler (contd. ) Copyright © 2007 Ramez Elmasri

Salient Features of Rational Rose Data Modeler (contd. ) n Modeling ER diagrams in UML n ER schema from the company example in chapter 3 can be drawn in Rational Rose using UML notation Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 49

Salient Features of Rational Rose Data Modeler (contd. ) n Keeps the data model and database synchronized n n Provides Extensive Domain Support n n n Gives the option of updating the model or changing the database Allows database designers to create a standard set of userdefined types Allows Converting between logical and object model design Allows easy communication between various developing and design teams n n Provides a common tool and platform to designers and developers Rational Rose Web Publisher Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 12 - 50