Chapter 8 Structuring system Data Requirements

Optional many

-----------------0--- Chicken feet

Optional one

---------------0--|--

Mandatory many

------------|-- Chicken feet

Mandatory one

---------||---

Candidate Keys and Identifiers Selection rules for an identifier

Choose a candidate key that will not change its value Choose a candidate key that will never be null. Avoid using intelligent keys (e.g PART Key with location; initials - hard to guarantee uniqueness). Consider substituting single value surrogate (generated by the system) keys for large composite keys (combination of attributes).

Introduction to Entity-Relationship (E-R) Modeling The E-R model is expressed in term of

Data entities in the business environment Relationships or associations among those entities Attributes or properties of both the entities and their relationships

The Conceptual Data Modeling Process

Develop a data model for the current system Develop a new conceptual data model that includes all requirements of the new system In the design stage, the conceptual data model is translated into a physical design Project repository links all design and data modeling steps performed during SDLC

Benefits of Database Patterns and Packaged Data Models

Dramatically reduced implementation times and costs Provides a starting point for asking requirements questions Higher-quality models Represents "best practice" data modeling techniques and data model components whose quality often exceeds that which can be achieved by internal development teams, given typical organizational pressures

Requirements Determination Questions for data Modeling Security controls and understanding who really knows the meaning of data

How do you use these data? That is, are you the source of the data for the organization, do you refer to the data, do you modify it, and do you destroy it? Who is not permitted to use these data? who is responsible for establishing legitimate value for these data?

Requirements Determination Questions for data Modeling integrity rules, minimum and maximum cardinality, time dimensions of data

Is each activity or event always handled the same way or are there special circumstances? Can an event occur with only some of the associated objects, or must all objects be involved? can the associations between objects change over time (for example, employees change departments) Are values for data characteristics limited in any way?

Requirements Determination Questions for data Modeling cardinality and time dimensions of data

Over what period of time are you interested in these data? Do you need historical trends, current "snapshot"value, and/or estimates or projections? If a characteristic of an object changes over time, must you know the obsolete value?

Role of Packaged Conceptual Data Models - Database Patterns

Packaged data models provide generic models that can be customized for a particular organization's business rules

Overlap rule

Specifies that an entity instance can simultaneously be a member of two (or more) subtypes

Mandatory vs. Optional Cardinalities

Specifies whether an instance must exist or can be absent in the relationship.

Entity Types

Strong Weak Associative

Maximum Cardinality

The maximum number of instances of entity B that may be associated with each instance of entity A

Minimum Cardinality

The minimum number of instances of entity B that may be associated with each instance of entity A

Relationship Degrees

Unary, Binary Ternary

Requirements Determination Questions for data Modeling Data entities and their description

What are the subjects/ objects of the business? What types of people,places,things, materials, events, ect., are used or interact in this business, about which data must be maintained? How many instances of each object might exist?

Requirements Determination Questions for data Modeling Attributes and secondary keys

What characteristics describe each objective? On what basis are objects referenced, selected, qualified, sorted, and categorized? what must we know about each object in order to run the business?

Requirements Determination Questions for data Modeling relationship, and their cardinality and degree

What events occur that imply associations among various objects? What natural activities or transactions of the business involve handling data about several objects of the same or a different type.

Requirements Determination Questions for data Modeling Primary Key

What unique characteristic (or Characteristics) distinguishes each object from other objects of the same type? Might this distinguishing feature change over time or is it permanent? Might this characteristic of an object be missing even though we know the object exists

Candidate Keys and Identifiers Identifier

a candidate key that has been selected as the unique, identifying characteristic for an entity type

Introduction to Entity-Relationship (E-R) Modeling Entity-relationship diagram (E-R diagram)

a graphical representation of an E-R model

Binary relationship

a relationship between instances of two entity types Most common type of relationship encountered in data modeling

Unary relationship

a relationship between the instances of one entity type Also called a recursive relationship

Repeating group

a set of two or more multivalued attributes that are logically related

Ternary relationship

a simultaneous relationship among instances of three entity types

Role of Packaged Conceptual Data Models - Database Patterns Industry-specific

data models that are designed to be used by organizations within specific industries these models are based on the premise that data model patterns for organizationsae similar within a particular industry

Universal data models are templates for

one or more core subject area such as: Customer,product,accounts,documents and/or core business functions such as: Purchasing,accounting, recieving, ect.

ENTITY instance (data)

single occurrence of an entity type

Business rules

specifications that preserve the integrity of the logical data model Capture during requirements determination Store in CASE repository as they are documented To be implemented when creating the physical database

Business Rules for Supertype/subtype Relationships: Partial specialization

specifies that an entity instance of the supertype does not have to belong to any subtype, and may or may not be an instance of one of the subtypes (single line)

Business Rules for Supertype/subtype Relationships: Total specialization

specifies that each entity instance of the superype must be a member of some subtype in the relationship (double line)

Disjoint rule

specifies that if an entity instance of the supertype is a member of one subtype, it cannot simultaneously be a member of any other subtype (letter d)

Naming and defining Relationships

A relationship name is a verb phrase; avoid vague names

Introduction to Entity-Relationship (E-R) Modeling An entity type name should be

A singular noun Descriptive and specific to the organization Concise Event entity types should be named for the result of the event, not the activity or process of the event

Subtype

A subgrouping of the entities in an entity type Is meaningful to the organization Shares common attributes or relationships distinct from other subgroupings

triggering operations

An assertion or rule that governs the validity of data manipulation operations such as insert, update and delete Includes the following: User rule: statement of the business rule to be enforced by the trigger Event: data manipulation operaation that initiates the operation Entity name: name of entity being accessed or modefied Condition: condition that causes the operation to be triggered Action: action taken when the operation is triggered

Naming and Defining Attributes

An attribute name is a noun and should be unique To make an attribute name unique and for clarity, each attribute name should follow a standard format Similar attributes of different entity types should use similar but distinguishing names.

Associative Entities

An entity type that associates the instances of one or more entity types and contains atrributes that are peculiar to the relationship between those entity instances Sometimes called a gerund The data modeler chooses to model the relationship as an entity type

Requirements Determination Questions for data Modeling supertypes, subtypes and aggregations

Are all instance of each object the same? That is, are there special kinds of each object that are described or handled differently by the organization? Are some objects summaries or combinations of more detailed objects

Entity (set)

A person,place,object, event or concept in the user environment about which data is to be maintained

Attributes

A named property or characteristic of an entity that is of interest to the organization Naming an attribute : i.e. Vehicle _ID Place its name inside the rectangle for the associated entity in the E-R diagram

Conceptual Data Modeling

A detailed model that captures the overall structure of the data in an organization *Independent of any database management system (DBMS) or other implementation considerations. This is the first cut model. The starting point to building a database *It describes the semantics

Introduction to Entity-Relationship (E-R) Modeling Entity-Relationship data model (E-R model)

A detailed, logical representation of the entities, association or business area

Supertype

A generic entity that has a relationship with one or more subtypes

Four basic types of business rules are

Entity integrity: unique, non-nul identifiers Reference integrity constraints: rules governing relationships between entity types Domains-Constraints on valid values for attributes Triggering operations-other business rules that protect the validity of attribute values

Naming and defining Relationships A relationship definition should

Explain any optional participation Explain the reason for any explicit maximum cardinality other than many Explain any restrictions on participation in the relationship Explain the extent of history that kept in the relationship Explain whether an entity instance involved in a relationship instance can transfer participation to another relationship instance

Naming and defining Relationships Relationship definition

Explains what action is to be taken and possibly why it is important Gives examples to clarify the action

Relationship

an association between the instances of one or more entity types that is of interest to the organization

Candidate Keys and Identifiers Candidate key

an attribute (or combination of attributes) that uniquely identifies each instance of an entity type

Composite attribute

an attribute that has meaningful component parts (e.g. address)

Optional attribute

an attribute that may not have a value for every entity instance

Multivalued attribute

an attribute that may take on more than one value for each entity instance

Required attribute

an attribute that must have a value for every entity instance

Derived attribute

an attribute whose value can be computed from related attribute values (e.g. age calculated from DOB)

Entity Types

are general Are correct entity types Are incorrect entity types

Degree

the number of entity types that participate in a relationship

Cardinality

the number of instances of entity B that can (or must) be associated with each instance of entity A

Domains

the set of all data types and values that an attribute can assume serval advantages Verify that the values for an attribute are valid Ensure that various data manipulation operations are logical Help conserve effort in describing attribute characteristics