# Presentation: 491182

## DATA MODEL NOTATION

- Michael Hamsa

## DATA MODEL COMPONENTS

- Entity
- Attribute
- Relationship

## ENTITY

- An abstraction of a real world thing
- Examples
- Customer
- Pole
- Pipe Segment
- Tax Lot
- School
- ???

## ATTRIBUTE

- An individual piece of data about an entity
- Example:
- “credit rating” for a customer
- “house number” for an address
- Oftentimes, what we initially perceive to be an attribute may in reality be an entity itself
- e.g., address

## ENTITY / ATTRIBUTE

**PRODUCT**

*ENTITY / ATTRIBUTE*

## DATA INSTANCE

_**“**__**Instances” of CUSTOMER**_
**Mr. Jones, credit rating AAA**
**Ms. Smith, credit rating AAA**
**Mr. Brown, credit rating D-**

## DATA MODEL NOTATION

- Notation is a visual depiction of the information requirements and the business rules
- Multiple notations / methodologies are in use
- IDEF1X
- James Martin
- UML

## CAPTURING THE MODEL

- SCENARIO
- ACME, Inc., offers many products for sale
- Any one customer may buy many products
- Any one product may be sold to many customers

## IDENTIFYING ENTITIES / RELATIONSHIPS

- Entities
- PRODUCT
- CUSTOMER
- Implied relationships
- CUSTOMERs may buy many PRODUCTs
- PRODUCTs may be sold to many CUSTOMERs

## USING “NOTATION”

## ENTITY RELATIONSHIPS

**CUSTOMER**

**PRODUCT**

- Any one **CUSTOMER** may buy many **PRODUCT**s
- Any one **PRODUCT **may be bought by many **CUSTOMER**s

***Note:  this is an example of a “many-to-many”***
***relationship (a “non-specific” relationship).***

## MODEL EXTENSION

**CUSTOMER**

**PRODUCT**

**CONTRACT**

**PRODUCT**s are sold to **CUSTOMER**s via “**CONTRACT**”

- Each **CONTRACT** is with one, and only one, **CUSTOMER**, but a **CUSTOMER** may have many **CONTRACT**s

- Multiple **PRODUCT**s may be sold on a single **CONTRACT**, and a single **PRODUCT** may be sold on multiple **CONTRACT**s

## NON-SPECIFIC (MANY-TO-MANY) RELATIONSHIPS

**PRODUCT**

**CONTRACT**

- A  relationship in which many instances of one entity (PRODUCT) are related to many instances of another entity (CONTRACT)

- only valid in ERD (Entity Relationship Diagram)

- must be “resolved” before progressing to lower levels of the model

## DEPICTING ATTRIBUTES	

- Attribute depiction accommodates showing the entity’s “data areas”
- key area
- non-key area
- Attributes appearing in the key area become the “primary key” of the entity
- Attribute depiction implies a lower level of the model
- key-based model
- fully attributed model

## ENTITY / ATTRIBUTE DEPICTION

**CUSTOMER**

**Used for ERD only**

**Used for key-based and **
**fully attributed models**

- customer-number
- customer-name
- credit rating

**CUSTOMER**

## KEYS

*KEYS*

- A *key*  is a set of attributes that uniquely identifies an entity
- “Primary keys” become “foreign keys” for the enforcement of referential integrity
- Remember:  “No entity without identity.”
- Keys are the “key” to identity and uniqueness

## KEY BEHAVIOR IN RELATIONSHIPS

- Keys “migrate” to establish relationships between entities

- customer-nbr
- customer-name
- credit rating

**CUSTOMER**

- contract-nbr
- contract-date

**CONTRACT**

## KEY MIGRATION 

- contract-nbr
- contract-date

- customer-nbr
- customer-name
- credit rating

- customer-nbr (FK)

- customer-nbr

- customer-nbr

- customer-nbr

- customer-nbr

- customer-nbr

*KEY MIGRATION *

**CUSTOMER**

**CONTRACT**

## KEY BEHAVIOR IN RELATIONSHIPS

- Keys “migrate” to establish relationships between entities

- contract-nbr
- contract-date

- customer-nbr
- customer-name
- credit rating

**CUSTOMER**

**CONTRACT**

- customer-nbr (FK)

## IDENTIFYING / NON-IDENTIFYING RELATIONSHIPS

- In an *identifying relationship*, the primary key of the parent becomes part of the primary key of the child
- In a *non-identifying relationship*, the key of the parent migrates to the “non-key area” of the child

## KEY MIGRATION

*“**customer-number”* migrates to the “non-key” area of CONTRACT (*does not* identify the instance of CONTRACT)
*“**contract-number”* migrates to the key area of CONTRACT-DETAIL (*does** *identify an instance of CONTRACT-DETAIL)

## NON-SPECIFIC RELATIONSHIPS

- A non-specific (“many-to-many”) relationship cannot migrate keys
- therefore used only in ERD (Entity Relationship Diagram)

**CONTRACT**

## RESOLVING NON-SPECIFIC RELATIONSHIPS

- An “associative entity” is used to resolve non-specific (many-to-many) relationships
- Associative entity (CONTRACT-PRODUCT-DETAIL) *results in a pair*  of one-to-many relationships

## VALUE OF DATA MODELING

- Value of defining relationships is realized in the actual database implementation
- influences table design
- which tables
- how the tables are linked
- sets the stage for enforcing “Referential Integrity”

## NOTATION SUMMARY

- Notation is the method for visually depicting the data model
- Working knowledge of information modeling techniques and methodologies required
- Tools which support and enforce methodologies facilitate modeling and database design