Download - Database Applications GIT 150
Data Modeling and Analysis - Bsharah
Database Applications GIT 150
Information Modeling
Instructor: Rick BsharahSpring 2008
Data Modeling and Analysis - Bsharah
Overview
Methodology and Documentation
Modeling Procedures
Model Translation
Object Orientation
Product Data Exchange Standard
Enterprise Model Utilization & Summary
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
Sample Information Model
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
What is the failure rate of
this Part?
Is this the current version of the Design?
The Problem: Timely Access to Critical Information
Who is responsible?
Who can I call?
Data Modeling and Analysis - Bsharah
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.
• FILE1-A FILE
1-BFILE2-B
SAMEDATA
DIFFERENTFORMAT FILE
n-A FILEn-B
APPLICATION-A APPLICATION-B APPLICATION-n
Data Modeling and Analysis - Bsharah
Traditional Approach Does Not Easily Support Change
INVEST-MENT
($/CHG)
MARGINAL COST
Database Applications always have add-ons either for additional functionality or for integration with other systems.
TIME
Data Modeling and Analysis - Bsharah
Information Modeling (Systemic Requirements Analysis and Design) Provides the Foundation for Modular System Development
This enables a controlled plan of investment expenses
OperationReqmts Design Development Operation Design Development OperationReqmts Design Development Reqmts
Functionality
Marginal Investment(Cost/Change)
4 - 6Months
TIME
Data Modeling and Analysis - Bsharah
Good Information Modeling Supports the Three View Architecture Methodology*
EXTERNAL SCHEMA** CONCEPTUAL SCHEMA** INTERNAL SCHEMA** -USER VIEW- -LOGICAL VIEW- -COMPUTER VIEW-
P
Z
* ANSI SPARC 3 Schema Concept **Schema = Model
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
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
INFORMATION MODEL
DATABASES
APPLICATION 2
APPLICATION n
APPLICATION 1
Data Modeling and Analysis - Bsharah
Levels of Information Exchange: Point To Point Translators
Translators Have to Be Written to Each Program That Is to Be Interfaced - Causing High Application Maintenance Costs - Only a Short Term Solution
TRANSLATOR
TRANSLATOR
TRANSLATOR
TRANSLATORS TRANSLATOR
Data Modeling and Analysis - BsharahLevels of Information Exchange: Generic Exchange Capability Based On Common Model Specification
Enterprise Information
ModelTRANSLATOR
TRANSLATOR
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.
Data Modeling and Analysis - Bsharah
How Is An Information Model Built? • Starts with good business process or workflow activity information needs analysis.
User Requirements Analysis
Business Process RequirementsAnalysis
Integrate-able Database Applications
Database and Application Design
USED AT : AUTHOR : DATE : PROJECT, IDS REV:
NOTES : 1 2 3 4 5 6 7 8 9 10
WORKING READER DATE CONTEXT : DRAFTX RECOMMENDED PUBLICATON
NODE : TITLE : NUMBER DETERMINE REPAIR DISPOSITION REQUIREDA142
CONTRACT AND MISSION REQMTS
NONCONFORMANCEDOCUMENTATION
DISCREPANT PART
DISCREPANT PART
ENGR DWGS AND ANALYSIS
DISCREPANT PART
ENGR DWGS AND ANALYSIS
DISCREPANT PART W / DOC. REFERING PART BACK TO MFG FOR REWORK
NONREWORKABLE DISCREPANT PART
DISCREPANT PART W / DOC REFERING PARTFOR STANDARD REPAIR
NON STANDARD REPAIRABLE PART
DISCREPANT PARTW / DOC REFERINGDISPOSITIONTO MR ACTION
DISCREPANTPART NOTDISPOSITIONEDBY MR ACTION
ANALYZE FORDISPOSITIONBY UNIQUEREPAIRPROCEDURES
A1423
ANALYZE FORSTANDARDDISPOSITIONING
A1422
ANALYZE FORREPAIRDISPOSITIONING
A1421
Connectivity_Allocation
Functional_ConnectivityDefintion
requirement
Physica l_ConnectivityDefin tion
implem entation
BOOLEAN
restricted
Path_Element
Physica l_Unit_OccurrenceUsage
descendent
elementL[1:?]
Functional_Domain_Por tAllocation
26,6 Function_DomainPort
source
Application Specific Information Model
Local to EnterpriseMapping
Application Interpreted Information Model(Design Specification)
Enterprise Information
ModelCorporate Constraints
(Standards)
- Hardware- Software- Database- Network- Platforms- Budget- Schedule
InformationStandards
Data Modeling and Analysis - Bsharah
How Is An Information Model Built?(continued)
• From document, report and database decomposition.
PARTRELEASE
GEOMETRICCONFIGURATION
PART
TASK
CHANGEREVISION
NOTES
PARTLIST
(BOM)
CHANGEORDERS
PARTSHAPE
PARTINSTRUCTIONS
PARTAPPROVAL
PARTEFFECTIVITY
Data Modeling and Analysis - Bsharah
Overview
Methodology and Documentation
Modeling Procedures
Model Translation
Object Orientation
Product Data Exchange Standard
Enterprise Model Utilization & Summary
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
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)
Data Modeling and Analysis - Bsharah
Sample Information Model (Relational - IDEF 1X)
(SET TYPE)
Data Modeling and Analysis - Bsharah
Sample Information Model (Object Oriented - EXPRESS G)
INTEGER
STRING
male
1
INTEGER
A[1:3]person
hair_type
date
hair
birth_date
first_name
last_name
nickname
(DER) age
children S[0:?](INV) parents
S[0:2]
*maiden_name*husband
femalewife
Data Modeling and Analysis - Bsharah
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.
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
Entity Example
Entity Characteristics
Instances
MOVIEMovie Number Name Rating Rental Rate
12345345 Die Hard PG13 $323456781 Wings PG $265656565 Black Beauty G $2
CUSTOMERCust Number Name Address Status
Code123-345 Tom Jones 12 Oak St OK789-789 Mary Sullivan 456 Hill Ave Pend567-342 Bob Waters 7676 Scutter Rd OK
Data Modeling and Analysis - Bsharah
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)
Data Modeling and Analysis - Bsharah
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)
Data Modeling and Analysis - Bsharah
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
ENTITYKEY ATTRIBUTE
MOVIEMovie Number
Data Modeling and Analysis - Bsharah
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
ENTITYKEY ATTRIBUTE
NON-KEY ATTRIBUTE
MOVIEMovie NumberNameRatingRental Rate
Data Modeling and Analysis - Bsharah
Relationships
• Identify associations between pairs of entities• Two types of relationships
– Category– Connection
• Represented by lines drawn between entity boxes
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
Category Syntax
Generic Entity
(Discriminator)
Category Category Category
Double Line for Complete Set of Category Entities Single Line for Incomplete Set of Category Entities
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
Categorization Example
COMPLETE CATEGORIZATION
WESTERN SUSPENSE COMEDY MALE FEMALE
(SEX)
MOVIE PERSON
INCOMPLETE CATEGORIZATION(there are other movie types such as Documentary)
(MOVIETYPE)
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
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)
- Circle with Letter Z: represents zero or one (0,1)
- Circle with Number: represents an exact number (n)n
Z
P
Data Modeling and Analysis - Bsharah
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
rents/is rented by
MOVIE
Movie NumberNameRatingRental Rate
CUSTOMER
Cust NumberNameAddressStatus Code
Data Modeling and Analysis - Bsharah
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
rents according to
Customer
Cust NumberNameAddressStatus Code
Movie Rental RecordMovie NumberMovie Copy IdRental DateCust Number (FK)
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
Identifying Relationships
AIRLINEAirline-Name
AIRLINE-TICKETAirline-Ticket-No Airline-Name (FK)
Key "Migrates" to Key Position
{
issues
Data Modeling and Analysis - Bsharah
Non-Identifying Relationships
Key "Migrates" to Non-Key Position
TRAVEL-AGENCY-BRANCHTravel-Agency-Br-ID-No
EMPLOYEE
Travel-Agency-Br-ID-No (FK)
employs
Social Security Number
{
Data Modeling and Analysis - Bsharah
Entity Dependency
YY XX (FK1)
PARENT - EXISTENCE INDEPENDENT, IDENTIFIER INDEPENDENT ENTITY
CHILD - EXISTENCE DEPENDENT, IDENTIFIER INDEPENDENT ENTITY
ZZ
CHILD - EXISTENCE DEPENDENT, IDENTIFIER DEPENDENT ENTITY
XX (FK1)
CHILD - EXISTENCE DEPENDENT, IDENTIFIER DEPENDENT ENTITY
ZZ YY (FK1) XX (FK1)
YY XX
Data Modeling and Analysis - Bsharah
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
Data Modeling and Analysis - Bsharah
Attribute Role Name
PERSON PERSON
FAMILY TREE MINOR
Name Name
is Parent in is Child in
REQUIRED ROLE NAMES OPTIONAL ROLE NAME
Z
is
Parent.Name Child.Name
MinorPerson.Name
Data Modeling and Analysis - Bsharah
Levels of Modeling Detail
• DATA PLANNING MODEL(ENTITY RELATIONSHIP MODEL)– The Highest or Most Generalized Level of Abstraction -
Cannot Make Precise Business Rule StatementsG E N E R A L I Z A T I O N
DATA PLANNING MODEL
KEY - BASED MODEL
FULLY ATTRIBUTED MODEL
R E F I N E M E N T
N U M B E R O F E N T I T I E S
Data Modeling and Analysis - Bsharah
Planning Model Example
Is Rented By/ Rents
MOVIE CUSTOMER
STUDIO
Is Produced In/ Produces
WESTERN
(TYPE)
Data Modeling and Analysis - Bsharah
Levels of Modeling Detail
• Key - Based Model– Adds Unique Identifiers or Key Attributes To the Entities
of the Planning Model
G E N E R A L I Z A T I O N
DATA PLANNING MODEL
KEY - BASED MODEL
FULLY ATTRIBUTED MODEL
R E F I N E M E N T
N U M B E R O F E N T I T I E S
Data Modeling and Analysis - Bsharah
Key-Based Model Example
Funds
MOVIE CUSTOMER
STUDIO
Is Produced by
MOVIE # CUSTOMER #
STUDIO ID
MOVIE # (FK1)CUSTOMER # (FK2)DATE
MOVIERENTAL
Is Rented By Rents
STUDIO ID (FK2)MOVIE # (FK1)
MOVIEPRODUCTION
WESTERN
(TYPE)
MOVIE #
Data Modeling and Analysis - Bsharah
Levels of Modeling Detail
• FULLY ATTRIBUTED MODEL(FULLY REFINED MODEL)– Completely Defines Characteristics And– Relationships of All the Entities Within A– Given Scope - Greatest Level of Refinement
G E N E R A L I Z A T I O N
DATA PLANNING MODEL
KEY - BASED MODEL
FULLY ATTRIBUTED MODEL
R E F I N E M E N T
N U M B E R O F E N T I T I E S
Data Modeling and Analysis - Bsharah
Fully Attributed/Refined Model Example
Funds
MOVIE CUSTOMER
STUDIO
Is Produced by
MOVIE #
TITLELENGTH
CUSTOMER #
NAMEADDRESS
STUDIO ID
STUDIO NAMEADDRESS
MOVIE # (FK1)CUSTOMER # (FK2)DATE
LATE STATUS
MOVIERENTAL
Is Rented By Rents
STUDIO ID (FK2)MOVIE # (FK1)
BUDGET
MOVIEPRODUCTION
WESTERN
(TYPE)
MOVIE #
LOCATIONASPCA RATING
Data Modeling and Analysis - Bsharah
• Overview• Methodology and Documentation• Modeling Procedures• Model Translation • Object Orientation• Product Data Exchange Standard• Enterprise Model Utilization & Summary
Data Modeling and Analysis - Bsharah
Model Building Procedures
1. SCOPE PROJECT 2. ENTITY DEFINITION 3. RELATIONSHIP DEFINITION 4. KEY ATTRIBUTE DEFINITION 5. NON-KEY ATTRIBUTE DEFINITION
DATA PLANNING
MODEL
KEY BASED MODEL
FULLY ATTRIBUTED
MODEL
Data Modeling and Analysis - Bsharah
Step 1 : Scope Project
• Organize Team – Manager– Subject Experts– Modeling Expert
• Build, Analyze, and Optimize Process Model *• Identify Functional Requirements and Data Objects• Collect Source Material
– Data Object Examples– Interview Results– Policy / Procedure Manual– Data Base / File Specifications for Existing Systems
*Not a trivial exercise
Data Modeling and Analysis - Bsharah
Reasons for Process Evaluation and Measurement
• Process Measurement And Evaluation Helps To:
1)Focus attention on the real drivers behind achieving the organization's mission
2)Determine how to use the organization's resources effectively3)Set goals and monitor trends4)Analyze root causes and sources of errors5)Identify opportunities for improvement6)Give employees a sense of accomplishment7)Provide a means of knowing whether you're "Winning Or Losing"
• Process Re-Design
Data Modeling and Analysis - Bsharah
End Customer = Key to Process Re-Design
• Start workflow evaluation by understanding the process’s end customer(s).
• An end customer is one who pays for the results of the process.– Product– Service
• Determine "What" is being paid for.• Intermediate or internal customers (quality circle) are
typically an expense rather than a revenue generator.
Data Modeling and Analysis - Bsharah
AS-IS Process to TO-BE Process
• Identify the activities and objects within the AS-IS process that the end customer pays for.
• Question all others.• Start at the end of the process and work backward
adding only those activities and objects that are on the critical path.
Data Modeling and Analysis - Bsharah
Process Defined:
• A process or workflow is any activity or group of activities that takes an input, adds value to it, and provides an output to an internal or external customer.
Data Modeling and Analysis - Bsharah
Definition of a Process Flow Model
• A graphical workflow model and glossary that describes what an organization or system actually does.
• Depicts the time phased actions that are performed by a person, object, or system component in the performance of a given action or scenario.
• Views an environment from a process centered view - many objects at various points within their life cycle.
Data Modeling and Analysis - Bsharah
Purpose of a Process Flow Model
• Drive out the requirements for automation by capturing the objects, access reasons, approvers, notifications, organizations, and decision points for an event.
• Defines the process rulebase for the PDM process flow engine (i.e., enables the PDM to automate the environment’s policies and procedures).
• Define configuration management and control requirements.
• Can help optimize the entire process cycle time by exposing nonessential or ill-timed tasks.
Data Modeling and Analysis - Bsharah
Graphical process model depicting activities to be supported by a Student Registration Database Application
CaptureStudent
Registration Data
IdentifyTextbook
Requirements
Manage Instructor
Data
1.21.6 1.8
DevelopClass
Catalog
1.7
Manage Course Data
1.3
Manage Student
Data
1.1
Manage Facility
Data
1.4
IdentifyClass
Offerings
1.5
Instructor Information
CourseInformation
Facility Information
Student Information
Class Details
Catalog of Classes
Textbook Details
Reg Report
Data Modeling and Analysis - Bsharah
Manage Airline Meal Catering Process Model Example
ManageCustomers (Airlines)
ManageAirline Meal
Delivery
TrackSpecial Meal
Requests 1.1 1.2 1.3
Special Requests
Airline Information including Ticket Sales
Understand Flight
Schedules
Determine Flight
Passenger Counts
Identify Departure
Gate Location and Plan Delivery
Manage Flight Delays
and Cancellations
Airline Information
Schedules
TicketSales
PassengerFlight Details
Schedules
1.2.11.2.2
1.2.3
1.2.4
Meal Delivery Plan
Flight Changes
Data Modeling and Analysis - Bsharah
Process Flow Diagram Example
CONDUCTCREDIT CHECK
1 A11
MAKE LOAN DETERMINATION
2 A12
BOOKLOAN
3 A13
received*loanapplication
approved*loan file
Oin-work*
loanapplication
X
reviewed* loanapplication
generated*internal order
rejected* loan applicationwith information request
qualified*branch
manager trained*notedepartment
approved*applicationprocedures
approved*qualificationprocedures
approved*loan bookingprocedures
approved*loan dollars
unapproved* loan application
approved loan*application
O
qualified*
qualified*
Commercial Loan Process
loan officer
loan officer
new*loan evaluation worksheet
completed*loan
evaluationworksheet
Data Modeling and Analysis - Bsharah
Process Flow Model Elements
• A process flow model is composed of the following elements:– Activities and Events (Units Of Behavior)– Inputs and Outputs (Object & State Links)– Constraints and Rules (Controls)– Resources (Mechanisms)– Decision Points (Junctions)– Notifications and Messages
Data Modeling and Analysis - Bsharah
Process Model Information Interview Form
MODELER PROFILE
NAME: JOB TITLE: LOCATION: ORG/DEPT/SECTION/GROUP: ADDRESS: MAIL STOP: PHONE#: SKILLS (optional) SKILL NAME CODE
EXPERIENCE (optional) a) PROGRAM/PRODUCT: JOB DESCRIPTION PFROM TO
b) PROGRAM/PRODUCT: JOB DESCRIPTION PFROM TO
c) PROGRAM/PRODUCT: JOB DESCRIPTION PFROM TO
MAJOR GOALS/OBJECTIVES (optional):
ACTIVITY INFORMATION PAGE (2)
ACTIVITY NAME: NODE#
ACTIVITY DESCRIPTION:
PERFORMING ORG/DEPT/SECTION/GROUP:
AUTHORIZING ORG/DEPT/SECTION/GROUP:
APPROVING ORG/DEPT/SECTION/GROUP:
EQUIPMENT REQUIRED TO PERFORM ACTIVITY (INCLUDING HW/SW):
ACTIVITY SUCCESS CRITERIA:
PROBLEMS ASSOCIATED WITH ACTIVITY:
NEEDS FOR PROBLEM SOLUTIONS:
Data Modeling and Analysis - Bsharah
Process Model Information Interview Form
ACTIVITY INFORMATION PAGE (3)
ACTIVITY NAME: NODE#
TYPE OF QUESTIONS ASKED IN PERFORMING THE ACTIVITY: 1)
2)
3)
4)
5)
6)
7)
8)
9)
INPUT & CONTROL INFORMATION FLOW PAGE (4)ACTIVITY NAME: NODE#
a) INPUT/CONTROL NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) RECEIVED PFROM: (INPUT) HOW MODIFIED:
(CONTROL) HOW USED:
b) INPUT/CONTROL NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) RECEIVED PFROM: (INPUT) HOW MODIFIED:
(CONTROL) HOW USED:
c) INPUT/CONTROL NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) RECEIVED PFROM: (INPUT) HOW MODIFIED:
(CONTROL) HOW USED:
d) INPUT/CONTROL NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) RECEIVED PFROM: (INPUT) HOW MODIFIED:
(CONTROL) HOW USED:
Data Modeling and Analysis - Bsharah
Process Model Information Interview Form
OUTPUT INFORMATION FLOW PAGE (5)ACTIVITY NAME: NODE#
a) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
b) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
c) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
d) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
e) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
f) OUTPUT NAME: MEDIA: SIZE: LIFECYCLE STATE: NODE#(s) GOING TO:
NOTIFICATION INFORMATION FLOWS PAGE (6)
ACTIVITY NAME: NODE#
a) NOTIFICATION NAME: PURPOSE: MEDIA: SIZE: ORG/DEPT/SEC/GROUP/PERSON(s) SENDING MESSAGE:
ORG/DEPT/SEC/GROUP/PERSON(s) RECEIVING MESSAGE:
NODE#(s) GOING TO:
b) NOTIFICATION NAME: PURPOSE: MEDIA: SIZE: ORG/DEPT/SEC/GROUP/PERSON(s) SENDING MESSAGE:
ORG/DEPT/SEC/GROUP/PERSON(s) RECEIVING MESSAGE:
NODE#(s) GOING TO:
c) NOTIFICATION NAME: PURPOSE: MEDIA: SIZE: ORG/DEPT/SEC/GROUP/PERSON(s) SENDING MESSAGE:
ORG/DEPT/SEC/GROUP/PERSON(s) RECEIVING MESSAGE:
NODE#(s) GOING TO:
Data Modeling and Analysis - Bsharah
Project Functional Requirements (PFR)
• A mechanism for translating a process vision (i.e., Should Maps, To-Be Process Models) into information system and database application requirements.
• Used to organize and summarize one or more application concepts into reusable capabilities.
• Provides a technique for maintaining traceability between user requirements and the application system design specifications.
• The set of PFRs bound the scope of the information model vision.
Data Modeling and Analysis - Bsharah
Each PFR Contains
• A Capability Statement– A description of “What” function or concept of operation the PFR is providing.
This is extracted from user process analysis.
• A Rationale Statement– A description of “Why” the PFR is needed. Describes what process(s) is being
supported.
• A Presentation Statement– A description of “How” the capability(s) defined by the PFR is to be provided or
accomplished. Identifies the implementation approach (i.e., query on-line, report).
• A Data Assertion Statement– A description of the information needs of the PFR. Identifies the entity/object
classes and their attributes needed to initiate the capability and the entity/object classes and associated attributes provided by the completion of the capability.
• A Players Statement– A description of “Who” desires and “Who” utilizes the capability identified in the
PFR.
Data Modeling and Analysis - Bsharah
Example PFR for Responsible Personnel for a Part
• Capability Statement:– Be able to retrieve responsible personnel or part ownership information
for a given part and optionally include most current system requirements information as well.
• Rationale Statement:– To facilitate and make team communication more efficient. – To support issue resolution by providing the immediate association of
parts that are the subject of or that are affected by an issue and the people who need to be contacted to achieve team resolution (during both informal change control and formal issue management).
• Presentation Statement:– Multi-level screens, using Part, System, Requirement, Responsible
Person as selection to get to secondary screens. Reports not required but limited printing based upon screen context. Most critical requirements always shown first based upon area (part) criticality.
Data Modeling and Analysis - Bsharah
Part Responsibility PFR Example (Con’t)
• Data Assertion Statement:– The Part Prefix, Base, with Suffix is the input information
for returning Personnel information that includes Person’s Name, Title, Phone Number, Department Name, Alternate Extension, FAX Phone Number. There is a single set of the Personnel information for each Part Number input.
– Optional Requirements information would include Requirements text, Product Line, Series Name, Unit Cost, Total Program Invest, Weight, Quality Assessment, . . .
• Players Statement– Desired by: Designer, Program Office– Utilized by: Program Office, CAE Personnel
Data Modeling and Analysis - Bsharah
Step 2 : Entity/Object Definition
• Use the Process Flow Model Links and the Functional Requirement’s Data Assertion Statements to create an Entity/Object Pool – list of "things" that have data associated with them
• Test Pool for validity– Can each entity be described?– Are there several instances?– Can one instance be separated from another?
• Define entities in Pool and identify synonyms
Data Modeling and Analysis - Bsharah
Step 3 : Relationship Definition
• Build entity relationship matrix– Identify related entities– Define connection relationships– Define category relationships
• Identify dependencies• Capture narrative statements about the relationship -
business rules• Build data planning model
Data Modeling and Analysis - Bsharah
Entity Relationship Matrix
MOVIE
STUDIO
CUSTOMER
X
WESTERN
MOVIE
STUD
IO
CUST
OMER
WES
TERN
X X
X
X
X
Data Modeling and Analysis - Bsharah
Entity Relationship Matrix
MOVIE
STUDIO
CUSTOMER
Could Be A
WESTERN
MOVIE
STUD
IO
CUST
OMER
WES
TERN
Is Prod-uced By
Is Rented By
Is A Type Of
Pro-duces
Rents
Data Modeling and Analysis - Bsharah
Planning Model Example
Is Rented By/ Rents
MOVIE CUSTOMER
STUDIO
Is Produced In/ Produces
WESTERN
(TYPE)
Data Modeling and Analysis - Bsharah
Step 4 : Key Attribute Definition
• Resolve non-specific relationships• Determine primary and alternate keys• Define key attributes• Migrate key attributes• Determine identifying (solid line) and non-identifying
(dashed line) relationships• Perform no-null, no-repeat, and smallest-key testing
Data Modeling and Analysis - Bsharah
Attribute Testing
• No-null rule: for each instance of an entity, all attributes must have a value
• No-repeat rule: no entity instance can have more than one value for a given entity key
• Smallest key test: entities with compound keys must require whole key set for unique identification
Data Modeling and Analysis - Bsharah
Key-Based Model Example
Funds
MOVIE CUSTOMER
STUDIO
Is Produced by
MOVIE # CUSTOMER #
STUDIO ID
MOVIE # (FK1)CUSTOMER # (FK2)DATE
MOVIERENTAL
Is Rented By Rents
STUDIO ID (FK2)MOVIE # (FK1)
MOVIEPRODUCTION
WESTERN
(TYPE)
MOVIE #
Data Modeling and Analysis - Bsharah
Step 5 : Non-Key Attribute Definition
• Determine non-key attributes• Define non-key attributes• Specify precise cardinalities and other information
constraints• Validate single entity ownership• Apply no null, no repeat, and smallest key set rules
Data Modeling and Analysis - Bsharah
Attribute Ownership
• Each and every attribute must be "owned" by one and only one entity
• A non-key attribute can only be found within a single entity unless the attribute is a foreign key (migrated as a non-key attribute)
PASSPORTPASSPORT #
SSN (FK1)Zhas
PERSONSSN
Data Modeling and Analysis - Bsharah
Fully Attributed/Refined Model Example
Funds
MOVIE CUSTOMER
STUDIO
Is Produced by
MOVIE #
TITLELENGTH
CUSTOMER #
NAMEADDRESS
STUDIO ID
STUDIO NAMEADDRESS
MOVIE # (FK1)CUSTOMER # (FK2)DATE
LATE STATUS
MOVIERENTAL
Is Rented By Rents
STUDIO ID (FK2)MOVIE # (FK1)
BUDGET
MOVIEPRODUCTION
WESTERN
(TYPE)
MOVIE #
LOCATIONASPCA RATING
Data Modeling and Analysis - Bsharah
Normalization
• A set of rules for analyzing the attributes of an information model– Eliminate model redundancy– Ensure model consistency – Verify structural correctness– Maximize stability
• However, normalization cannot validate a model's accuracy in reflecting the business meaning of the information
Data Modeling and Analysis - Bsharah
Normal Forms
• Sequential steps for achieving an optimized and logically desirable information model
• Provides a common foundation from which an efficient physical database design can be created
• There are six degrees of normal form - the first three are usually sufficient for most modeling applications
• First normal form• Second normal form• Third normal form• Boyce/Codd normal form• Fourth normal form• Fifth normal form
Data Modeling and Analysis - Bsharah
First Normal Form - (1NF)
• Every key and non-key attribute of an entity must be single valued
• No entity instance can have multiple values for a given attribute
• i.e., The No Repeat Rule
• A violating entity is corrected by removing repeating or multivalued attributes to another, dependent (child) entity
Data Modeling and Analysis - Bsharah
First Normal Form - ExampleRESTAURANTREST NAME ADDRESS PHONE # EMPLOYEE NAME
BURGER KING TACO HOUSE FISH COMPANY
123 NORTH ST 345 126TH PLACE 77 SUNSET AVE
123-2345 765-8907 395-5682
JOHN, SUE, LISA MARY, BILL ED, SAM, JOSE, RICK
REST NAME ADDRESS PHONE # EMPLOYEE NAME
RESTAURANTREST NAME ADDRESS PHONE #
EMPLOYEEEMPLOYEE NAMEREST NAME POSITION
employs
Data Modeling and Analysis - Bsharah
Second Normal Form - (2NF)
• An entity that is in first normal form and each non-key attribute is dependent on the entire primary key
• No non-key attribute instance can be determined by knowing just part of an entity instances key
• A violating entity is corrected by removing to a parent entity any attributes that depend on only a subset of the primary key
Data Modeling and Analysis - Bsharah
Second Normal Form - ExampleRESTAURANT ORDERREST NAME SUPPLIER NAME ORDER ITEM SUPPLIER PHONE #
BURGER KING TACO HOUSE FISH COMPANY
SAM'S PRODUCE SALSA INC. SAM'S PRODUCE
BEEF PEPPERS SNAPPER
123-2345 765-8907 123-2345
REST NAME SUPPLIER NAME ORDER ITEM SUPPLIER PHONE #
fills
RESTAURANT ORDERREST NAME ORDER ITEM SUPPLIER NAME (FK1)
SUPPLIERSUPPLIER NAME PHONE #
Data Modeling and Analysis - Bsharah
Third Normal Form - (3NF)
• An entity that is in second normal form and each non-key attribute is only dependent on the entire primary key and nothing other than the key
• No non-key attribute instance can be determined by knowing the value of another non-key attribute for the same instance
• A violating entity is corrected by removing to a parent entity any attributes exhibiting transitive dependencies (non-key attributes that not only depend on the whole key but also on other non-key attributes)
Data Modeling and Analysis - Bsharah
Third Normal Form - ExampleRESTAURANT RESERVATION
REST NAME RESERVATION # CUSTOMER NAME CUSTOMER PHONE # TIME # IN PARTY
BURGER KING TACO HOUSE FISH COMPANY
12 234 88
11:00 AM 2:30 PM 8:15 PM
123-2345 765-8907 123-2345
REST NAME RES # CUST NAME CUST PH # TIME # IN PARTY
makes
F. JONES R. SMITH F. JONES
4 4 6
CUSTOMERCUSTOMER NAME PHONE #
RESTAURANT RESERVATIONREST NAME RESERVATION # CUSTOMER NAME (FK1) TIME # IN PARTY
Data Modeling and Analysis - Bsharah
Boyce/Codd Normal Form - (BCNF)
• An entity that is in third normal form and all attributes depend on only the entire primary or alternate keys (candidate keys) and do not depend on any subset of the candidate keys.
• No non-key attribute instance can be determined by knowing the value of another non-key or partial key attribute for an alternate key entity instance
• A violating alternate key instance entity is corrected by removing to a parent entity any attributes that depend on only a subset of the primary key or those exhibiting transitive dependencies
Data Modeling and Analysis - Bsharah
Fourth Normal Form - (4NF)
• An entity that is in boyce/codd normal form and the primary and alternate keys do not contain any independently multivalued attributes
• No entity instance should contain two or more independent facts about an entity unless inherited through relationships
• Don't mix apples and oranges• A violating entity is corrected by removing any
independently multivalued components of the primary key to two or more new (possibly) parent entities
(APPLIES ONLY TO COMPOUND/COMPOSITE KEYS)
Data Modeling and Analysis - Bsharah
Fifth Normal Form - (5NF)
• An entity that is in fourth normal form and the primary key does not contain any pairwise cyclic redundancies
• No entity instance should contain information which can be reconstructed from smaller pieces of information which can be maintained with less redundancy
• A violating entity is corrected by simultaneous decomposition into three or more new parent entities
(APPLIES ONLY TO COMPOUND/COMPOSITE KEYS)
Data Modeling and Analysis - Bsharah
• Overview• Methodology and Documentation• Modeling Procedures• Model Translation • Object Orientation• Product Data Exchange Standard• Enterprise Model Utilization & Summary
Data Modeling and Analysis - Bsharah
Model Translation
• Logical to Physical, Relational Database• Converting IDEF-1X (ER Models) to EXPRESS (OO
Models)
Data Modeling and Analysis - Bsharah
Translation of a Logical Model into a Physical, Relational Database
• Identify Tables• Identify Columns• Adapt Data Structure To Product Environment• Design Databases For Business Rules About Entities • Design For Business Rules About Relationships • Design For Business Rules About Attributes
Data Modeling and Analysis - Bsharah
STEP 1 : Identify Tables
• In general, identify one table for each AIM entity.• Define each bottom level Subtype entity as a table
rolling all Supertype attributes down into the Subtypes. Or, define top level Supertype entity as a table rolling all Subtype attributes into Supertype as optional class dependent attributes. Write triggers to enforce inheritance rules (ONEOF etc.).
• Document tables in a data dictionary.
Data Modeling and Analysis - Bsharah
STEP 2 : Identify Columns
• In general, identify one column for each attribute of an AIM entity.
• In general, don't define composite columns (multiple AIM attributes per column).
• Define triggers to enforce integrity and cardinality rules.
• Document table columns in data dictionary.• Create ER diagram of global internal schema by
depicting tables as ER entities and columns as ER entity attributes.
Data Modeling and Analysis - Bsharah
STEP 3 : Adapt Data Structure To Product Environment
• Define optimum sequencing of columns for physical device storage and performance
• If feasible, for each table allocate enough primary storage space to contain the entire table - try to minimize use of secondary space
• If feasible, for each table allocate enough free storage space to accommodate all anticipated row inserts and updates that may occur after initial table load or after table reorganization
Data Modeling and Analysis - Bsharah
STEP 3 : Adapt Data Structure To Product Environment (con't)
• Define database(s) to facilitate greater concurrency/data availability and to avoid physical size limitations.
• Group common business meaning entity/tables together into separate databases.
• Group commonly accessed entity/tables together into separate databases.
• Evaluate and design databases to utilize locking parameters which optimize data record locking for minimal size and time.
• Document database meta data in data dictionary.• Create ER subset diagrams of global internal schema for each
database.
Data Modeling and Analysis - Bsharah
STEP 4 : Design Databases For Business Rules About Entities i.e., Uniqueness, Minimality, And Key Null Value Disallowance
• Enforce entity uniqueness key (and alternate key) concepts through DBMS data definition language (DDL) and/or DBMS domain definitions.
• Document database table key and alternate key concepts and enforcement methods in data dictionary.
Data Modeling and Analysis - Bsharah
STEP 5 : Design For Business Rules About Relationships i.e., Key Attribute Insert, Delete, And Update Constraints
• Enforce entity referential integrity concepts through DBMS data definition language (DDL) foreign key designation and/or DBMS triggering techniques.
• Document database table foreign key concepts and enforcement methods in data dictionary.
Data Modeling and Analysis - Bsharah
STEP 6 : Design For Business Rules About Attributes (Domain Constraints & Events)
• Enforce entity attribute domain and action event concepts through DBMS data definition language (DDL), indexes, and/or standard maintenance routines. – Domain constraints = data type, length, format/mask,
allowable value definition, uniqueness, null support, and/or default value specification.
– Action events = triggering mechanisms to enforce object methods and algorithms specified in aim.
• Document database domain and action event concepts and enforcement techniques in data dictionary.
Data Modeling and Analysis - Bsharah
• Overview• Methodology and Documentation• Modeling Procedures• Model Translation • Object Orientation• Product Data Exchange Standard• Enterprise Model Utilization & Summary