Database Applications GIT 150

1. Database Applications GIT 150 Information Modeling Instructor: Rick BsharahSpring 2008

2. Overview Methodology and Documentation Modeling Procedures Model Translation Object Orientation Product Data Exchange Standard Enterprise Model Utilization & Summary

3. Information Modeling • Definition: • A structured (primarily graphical) set of rules for representing the file and database information and business process requirements. • Purpose: • To provide a “template” for managing and integrating the information used and/or produced in support of a business and/or personal need(s). • Benefit: • The information model provides the design foundation for implementing a physical database(s). This “template” is essential to avoid constant redesign of a physical database(s) as the scope is extended.

4. Sample Information Model

5. Why Information Modeling Is Needed for Designing Database Applications • Traditional information system development approaches contain inherent weaknesses. • Increasingly complex information systems are being developed. • Complexity of modern systems increases development risks.

6. The Problem: Timely Access to Critical Information Is this the current version of the Design? What is the failure rate of this Part? Who is responsible?Who can I call?

7. The Problem: Managing Diverse, Complex Technology • The multitude of hardware and software vendors has lead to widespread incompatibilities. • Separate and often proprietary data storage leads to data integrity problems of keeping data current. Application data life cycle largely dependent on a programs duration. • Rapid pace of marketplace makes flexibility and growth accommodation very challenging. • Increasingly complex information systems are required, leading to increased development risks. APPLICATION-A APPLICATION-B APPLICATION-n SAME DATA DIFFERENT FORMAT FILE FILE FILE FILE FILE 1-A n-A 2-B 1-B n-B

8. Traditional Approach Does Not Easily Support Change MARGINAL COST INVEST- MENT ($/CHG) TIME Database Applications always have add-ons either for additional functionality or for integration with other systems.

9. Information Modeling (Systemic Requirements Analysis and Design) Provides the Foundation for Modular System Development Functionality This enables a controlled plan of investment expenses Marginal Investment (Cost/Change) Reqmts Design Development Operation Reqmts Design Development Operation Reqmts Design Development Operation 4 - 6Months TIME

10. EXTERNAL SCHEMA** CONCEPTUAL SCHEMA** INTERNAL SCHEMA** -USER VIEW- -LOGICAL VIEW- -COMPUTER VIEW- Good Information Modeling Supports the Three View Architecture Methodology* * ANSI SPARC 3 Schema Concept **Schema = Model

11. Conceptual Information Model • Also known as a logical information model. • A neutral description of what is used and/or produced in the support of an enterprise's activities. • Provides a template for common data base design. • Establishes data management procedures and supports existing and emerging standards.

12. APPLICATION APPLICATION APPLICATION 1 2 n INFORMATION MODEL DATABASES Information Modeling - the Key to Successful Data Integration • Based upon the data driven approach - treating data as a separate shareable resource • Focus is on individual data elements and their relationships to one another • Supports multiple levels of exchange

13. Translators Have to Be Written to Each Program That Is to Be Interfaced - Causing High Application Maintenance Costs - Only a Short Term Solution Levels of Information Exchange: Point To Point Translators TRANSLATOR TRANSLATORS TRANSLATOR TRANSLATOR TRANSLATOR

14. Levels of Information Exchange: Generic Exchange Capability Based On Common Model Specification TRANSLATOR TRANSLATOR Enterprise Information Model TRANSLATOR TRANSLATOR Only one translator needs to be written for each application - but the same information is being redundantly stored, requiring extensive data configuration management.

15. Starts with good business process or workflow activity information needs analysis. - Hardware - Software - Database - Network - Platforms - Budget - Schedule How Is An Information Model Built? Database and Application Design User Requirements Analysis Integrate-able Database Applications Application Specific Information Model Business Process RequirementsAnalysis Application Interpreted Information Model (Design Specification) Local to Enterprise Mapping Information Standards Enterprise Information Model Corporate Constraints(Standards)

16. How Is An Information Model Built?(continued) • From document, report and database decomposition. CHANGE ORDERS GEOMETRIC CHANGE CONFIGURATION REVISION NOTES PART SHAPE PART PART LIST (BOM) PART INSTRUCTIONS PART APPROVAL TASK PART PART RELEASE EFFECTIVITY

17. Overview Methodology and Documentation Modeling Procedures Model Translation Object Orientation Product Data Exchange Standard Enterprise Model Utilization & Summary

18. What Is an Information Model? • A structured, primarily graphical, set of rules for representing information of interest to a community. • Specifies information needed to support the activities of a business. • Depicts real or abstract objects, their characteristics and relationships to one another. • Two types: • Logical models/schemas • Physical models/schemas.

19. Logical and Physical Information Models • Logical • Defines information requirements in the form of a set of non-redundant technical and management data declarations, but does not include the design considerations and physical storage parameters. • A logical schema contains entities made up of attributes, and connected by relations. • Physical • Contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a data definition language, which can then be used to create a database.

20. The Modeling Languages • Several techniques/languages have been developed for the design of information models. While these methodologies guide modelers in their work, two different people using the same methodology will often come up with different results. • Some are based upon a Relational paradigm, others on Object-Oriented paradigm, and some are hybrids. Examples include: • Entity-relationship diagrams (R) • IDEF-1x (R), IDEF-4 (O) • Object Role Modeling (ORM) or Nijssen's Information Analysis Method (NIAM) (H) • EXPRESS, EXPRESS-G (O) • RM/T (R) • UML (O)

21. Sample Information Model (Relational - IDEF 1X) (SET TYPE)

22. INTEGER Sample Information Model (Object Oriented - EXPRESS G) hair hair_type children S[0:?] (INV) parents S[0:2] birth_date A[1:3] date INTEGER person (DER) age first_name last_name STRING nickname 1 *husband *maiden_name male female wife

23. How Is an Information Model Documented? • With a modeling language/methodology in conjunction with a data dictionary repository. • A data dictionary is a set of metadata that contains definitions and representations of data elements. • A data dictionary holds the following information: • Precise definition of the information elements • Usernames, roles and privileges • Integrity constraints • Stored procedures and triggers • General database structure • Space allocations • One benefit of a well-prepared data dictionary is a consistency between data items across different tables. For example, several tables may hold telephone numbers; using a data dictionary the format of this telephone number field will be consistent. • Both the methodology (e.g., IDEF1x) and data dictionary are required to fully understand the content of the information models.

24. Information Models Contain • Entities/Objects: • A generalization of a real or abstract thing. • Example : part, system, or person • MS Access  Table • Attributes/Characteristics: • A fact or property about an object. • Example : part name, or system number • MS Access  Field • Relationships/Relations: • The association between two entities/objects. • Example : a system contains many parts • MS Access  Relationship

25. Entity • A class of objects about which one wishes to retain data • Identifies places, things or events that have common characteristics • Uniquely identifiable • Named with a noun or noun phrase • Represented by a box on the IDEF1x diagram

26. Entity Rules • Each entity must have a unique name and same meaning must always apply to same name • An entity must have one or more characteristics which uniquely identify every instance of the entity • Every entity must have one or more characteristics which are either owned by the entity or inherited through a relationship

27. Entity Instance • An occurrence of an entity • Entity instances are the information that goes into a database • They are not represented in an information model • An information model captures the structure of the instance meanings • - Characteristics = Information Model • - Characteristics Values = Data Base

28. Entity Example Entity Characteristics MOVIE Movie Number Name Rating Rental Rate 12345345 Die Hard PG13 $3 23456781 Wings PG $2 65656565 Black Beauty G $2 Instances CUSTOMER Status Code Cust Number Name Address 123-345 Tom Jones 12 Oak St OK 789-789 Mary Sullivan 456 Hill Ave Pend 567-342 Bob Waters 7676 Scutter Rd OK

29. Attributes • The characteristics of entities • A common property for all entity instances • Identified by a noun phrase that describes the characteristics being depicted • Represented as text inside the entity box on the IDEF1x diagram • May be either identifying (key) or non-identifying (non-key)

30. Attribute Rules • Each attribute must have a unique name and same meaning must always apply to the same name • Every attribute is owned by exactly one entity but can be related to many others • Every instance of an entity must have a value for every attribute (no-null rule) • No instance of an entity can have more than one value for an attribute (no-repeat rule)

31. Key Attributes • An attribute or set of attributes whose values uniquely identify instances of an entity • Placed at the top of the attribute list within an entity box ENTITY MOVIE KEY ATTRIBUTE Movie Number

32. Non-Key Attributes • A property or characteristic of an entity that is not part of the key • Placed after the key attributes and separated by a horizontal line crossing the entity box ENTITY MOVIE KEY ATTRIBUTE Movie Number Name NON-KEY ATTRIBUTE Rating Rental Rate

33. Relationships • Identify associations between pairs of entities • Two types of relationships • Category • Connection • Represented by lines drawn between entity boxes

34. Category Relationships • Used to identify 'subtype relationships' when some instances of a generic entity have an attribute that others do not have • Composed of one generic entity and one or more category entities • Both entities describe the same object • Generic entity contains attributes that are common to all subtypes • Category entities contain attributes that apply only to subtypes • Category entities are mutually exclusive • Example: • Movie= generic entity • Western, Suspense, Comedy = category entities

35. Category Syntax Generic Entity Double Line for Complete Set of (Discriminator) Category Entities Single Line for Category Category Category Incomplete Set of Category Entities

36. Category Rules • A category entity in one categorization relationship may be a generic entity in another categorization relationship • A generic entity can have multiple categorization relationships • The key attributes of a category entity must be the same as the generic entity • All instances of a category entity have the same discriminator value

37. Categorization Example WESTERN SUSPENSE COMEDY MALE FEMALE (MOVIETYPE) (SEX) MOVIE PERSON INCOMPLETE CATEGORIZATION (there are other movie types such as Documentary) COMPLETE CATEGORIZATION

38. Connection Relationships • Represent an association that exists between two different entities • Captures that one entity instance may be related to zero, one, or many ( 0,1,M ) instances of another entity e.g., Customer rents ( 0,1,M ) Movies • Can be either a Specific or Non-Specific instance identification

39. Connection Syntax • Line with solid circle at end point • Relationship is described with a verb phrase placed next to connection line - Circle : represents zero, one or many (0,1,M) - Circle with Letter P: represents one or more (1,M) P - Circle with Letter Z: represents zero or one (0,1) Z - Circle with Number: represents an exact number (n) n

40. Non-Specific ConnectionRelationships • A relationship where one instance of an entity may relate to 0, 1, M instances of a second entity; and one instance of the second entity may relate to 0, 1, M instances of the first entity • For Example: • A Customer Rents at 0,1,M Movies, and Each Movie is Rented by 0, 1, M Customers MOVIE CUSTOMER Movie Number Cust Number rents/is rented by Name Name Rating Address Rental Rate Status Code

41. Specific Connection Relationships • A Parent - Child relationship that resolves a non-specific relationship to capture additional detail • A relationship where one instance of an entity (Parent) may relate to 0, 1, M instances of the second entity (Child), and the Child entity is related to one and only one instance of the Parent entity • For Example: • A CUSTOMER Rents a specific copy of a MOVIE Movie Rental Record Customer Movie Number Movie Copy Id Rental Date Cust Number (FK) Cust Number rents according to Name Address Status Code

42. Attribute Migration • In all Specific relationships the key attributes of the parent (first) entity migrate to the child (second) entitye.g., Become attributes of the child entity • These attributes are known as Foreign Key attributes • The non-key attributes of the parent entity never migrate • A migrated key may migrate to either a key or non-key position in the child entity

43. Attribute Migration Syntax • A solid line is used to depict a parent-child relationship where, foreign key migrates to key position in child entity • An identifying relationship • Child entity box has rounded corners • A dashed line is used to depict a parent-child relationship where foreign key migrates to non-key position in child entity • An non-identifying relationship • Child entity box has square corners

44. Identifying Relationships AIRLINE { Airline-Name Key "Migrates" issues to Key Position AIRLINE-TICKET Airline-Ticket-No Airline-Name (FK)

45. Non-Identifying Relationships TRAVEL-AGENCY-BRANCH { Travel-Agency-Br-ID-No Key "Migrates" employs to Non-Key Position EMPLOYEE Social Security Number Travel-Agency-Br-ID-No (FK)

46. Entity Dependency ZZ PARENT - EXISTENCE INDEPENDENT, IDENTIFIER INDEPENDENT ENTITY YY XX (FK1) CHILD - EXISTENCE DEPENDENT, IDENTIFIER INDEPENDENT ENTITY XX (FK1) CHILD - EXISTENCE DEPENDENT, IDENTIFIER DEPENDENT ENTITY YY ZZ XX YY (FK1) XX (FK1) CHILD - EXISTENCE DEPENDENT, IDENTIFIER DEPENDENT ENTITY

47. Attribute Role Names • Role names are noun phrases assigned to attributes to help convey a characteristic's (attribute's) meaning • Required when a single attribute is inherited more than once, distinguishes multiple attributes occurrences • May be used (optional) for single attribute occurrence migrations

48. Attribute Role Name PERSON PERSON Name Name is is Parent in is Child in Z MINOR FAMILY TREE Parent.Name MinorPerson.Name Child.Name REQUIRED ROLE NAMES OPTIONAL ROLE NAME

49. Levels of Modeling Detail • DATA PLANNING MODEL(ENTITY RELATIONSHIP MODEL) • The Highest or Most Generalized Level of Abstraction - Cannot Make Precise Business Rule Statements G R DATA PLANNING E E N F MODEL E I R N A KEY - BASED MODEL E L M I E Z N A T FULLY ATTRIBUTED MODEL T I O N N U M B E R O F E N T I T I E S

50. Planning Model Example MOVIE CUSTOMER Is Rented By / Rents Is Produced In/ Produces (TYPE) STUDIO WESTERN