Chapter 2 - Modeling Data in the Organization

attributes

The data you are interested in capturing about the entitty

Strong entity

• exists independently of other types of entities

• has its own unique identifier

  • Identifier underlined with a single line

Weak entity

• dependent on a strong entity (identifying owner); cannot exist on its own

• does not have a unique identifier (only a partial identifier)

• Entity box and partial identifier have double lines

Identifying relationship

Links strong entities to weak entities

• Double parallel lines

Criteria for Identifiers

• Choose identifiers that

  • Will not change in value

  • Will not be null

• Has to be unique

• Used underscore and bold

• Avoid intelligent identifiers (e.g., containing locations or people that might change)

• Substitute new, simple keys for long, composite keys

Multiple Relations

Two entities can have more than one type of relationships between them (multipple relations

Associative Entity

Combination of relationship and entity. Serves to link other entities together in a many-to-many relationship.

• All relationships for the associative entity should be many

• Meaningful independent of the other entities

• Unique identifier, and should also have other attributes

• May participate in other relationships other than the entities of the associated relationship

• Convert ternary relationships to associative entities

Instances & Cardinality of Relationship

• One to one

  • Each entity in the relationship will have exactly one related entity

• One to many

  • An entity on one side of the relationship can have many related entities, but an entitty on the other side will have a max of one related entity

• Many-to-Many

  • Entities on both sides of the relationship can have multiple

Guidelines for Naming Entities

• singular noun

• specific to organization

• concise, or abbreviation

• for event entities, the result not the process

• name consistent for all diagrams

• “An X is..”

• describe unique characteristics of each instance

• explicit about what is and is not the entity

• when an instance is created or destroyed

• changes to other entity types

• history that should be kept

Naming Attributes

• Name should be singular noun or noun phrase

• Unique

• Follow a standard format

• Similar attirbutes of different entityt types should use the same qualifers and classes

Defining Attributes

• State source of values

• State whether attribute value can change once set

• Specific whether required or optional

• State min and max number of occurrences allowed

• Indicate relationships with other attributes

Cardinality Constraints

The number of instances of one entity that can or must be associated with each instance of another entity

• Minimum cardinality

  • If zero then optional

  • If one or more then mandatory

• Maximum Cardinality

  • The maximum number

classification of attribute: required vs optional

• Required:

  • Must have a value for every entity instance with which it is associated

• Optional:

  • May not have a value for every entity instance

classification of attribute: simple vs composite

• Composite:

  • Made up of multiple elements. List attribute name and in parentheses list components that make it up

  • (Sub-attributes)

    • Parentheses is composite

    • Employee Address(Street Address, City, State, Postal Code)

• Simple:

  • Cannot be split in to further attributes

  • Atomic attribute

    • ex; SSN (Social Security Number)

classification of attribute: single-valued vs multivalued

• Multivalued: More than one value for a given entity (or relationship)

• An employee can have more than one skill

  • Curly bracket to signify multivalued

  • Data Employedd {Skill}

• Single-valued: Attributes having single value for particular entity

  • Ex: Age

classification of attribute: stored versus derived

• Derived: Values can be calculated from related attribute values (not physically stored in the database)

• Years employed calculated from date employed and current date

  • [Years Employed]

  • Square bracket

• Stored: Attributes from which the values of other attributes are derived

  • Age

classification of attribute: identifier (key)

An attribute (or combo of attributes) that uniquely identifies individual instances of an entity type

simple versus composite identifier

Candidate Identifier

an attribute that could be an identifier; it satisfies the requirements for being an identifier

modeling relationships: relationship types vs. relationship instance

• The relationship type is modeled as lines between entity types. The relationship instance is between specific entity instances.

modeling relationships: relationships can have attributes

• These describe features pertaining to the association between the entities in the relationship

modeling relationships: multiple relationships

two entities can have more than one type of relationship between them

Time stamp

a time value that is associated with a data value, often indicating when some event occurred that affected the data value

The Price History attribute is both multivalued and composite

Enterprise Resource Planing (ERP)

A class of systems that integrated all functions of the enterprise, such as manufacturing, sales, finance, marketing, inventory, accounting, and human resources

data warehouse

an integrated decision support database whose content is derived from the various operational databases

data lake

a large integrated repository for internal and external data that does not follow. predefined schema

enterprise data model

the first step in database development, in which the scope and general contents of organizational database are specified

conceptual data model

a detailed, technology-independent specification of the overall structure of organizational data

logical data model

The representation of data for a particular data management technology (such as the relational model). In the case of a relational data model, elements include tables, columns, rows, primary and foreign keys, as well as constraints.

physical data model

Physical data model. A set of specifications that detail how data from a logical data model (or schema) are stored in a computer’s secondary memory for a specific database management system. There is one physical data model (or schema) for each logical data model.

identifying relationship

The relationship between a weak entity type and its owner

Business Rule

 A statement that defines or constrains some aspect of the business  

associative attribute