Chapter 2 Database Management

What should an entity NOT be? (2)

-A user of the database system, - an output of the database system (report)

Identifying owner

The entity type on which the weak entity type depends.

Guideline for Ternary relationships

convert all ternary (or higher) relationships to associative entities.

The purpose of data modeling is to

document business rules.

If an entity is the result of extracting data from the database,

don't represent as it's own entity (ex://= figure 2-4)

Time Stamp

simply a time value, such as date and time, that is associated with a data value. A time stamp may be associated with any data value that changes over time when we need to maintain a history of those data values.

Ternary Relationship

simultaneous relationship among the instances of three entity types.

Entity type is ALWAYS

singular

Business rules are expressed in terms

familiar to end users

Fact

is an association between two or more terms. Documented as a simple declarative statement that relates terms.

Two entities can have

more than one type of relationship between them.

Four conditions to concert a relationship to an associative entity type:

1. All relationships for participating entity types are "many" 2. the resulting associative entity type has independent meaning to end users and (preferably) can be identified with a single-attribute identifier. 3. The associative entity has one or more attributes in addition to the identifier. 4. The associative entity participates in one or more relationships independent of the entities related in the associated relationship.

Business rules are automated through

DBMS software

Data Definitions

Explanation of a term or fact.

Typically a weak entity type has an

attribute that services as a partial identifier.

Term

word or phrase that has specific meaning for the business. Key words used to form data names. ex:// course, section, rental car, flight.

Atomic

A business rule marks one statement, not several.

Composite Attribute, how to indicate, example

An attribute that has meaningful component parts that can be broken down. (Ex:// employee address, employee name) Use parenthesis to indicate.

Optional Attribute

An attribute that may not have a value for every entity (or relationship) instance with which it is associated. (notified by normal font)

Multivalued Attribute, Example, how indicate

An attribute that may take on more than on value for a given entity (or relationship) instance. Use curly brackets to indicate. Ex:/ skill

Identifier, how to indicate

An attribute whose value distinguishes instances of an entity type. Underline to indicate.

Composite Identifier

An identifier that consists of a composite attribute. Ex:// Flight ID needs Flight Number, and Date) composite attributes to uniquely identify the entity.

What should entity be? (3)

An object that will have many instances in the database, an object that will be composed of multiple attributes, an object that we are trying to model.

Simple Attribute

an attribute that cannot be broken down into smaller components that are meaningful to the organization. Ex:// Vehicle ID

Candidate Key

an attribute that could be a key.. satisfies the requirements of being a key

Relationships can have attributes that describe

features pertaining to the association between the entities in the relationship.

Consistent

Business Rule must be internally consistent and externally consistent and can not contradict other rules

Strong Entity Type

is one that exists independently of other entity types. ex:// STUDENT, EMPLOYEE, AUTOMOBILE, COURSE

An attribute wihtout a value is said to be

nul

Degree of a relationship

the number of entity types that participate in a relationship.

Guidelines for naming relationships

1. Relationship name is verb phrase 2. avoid vague names

Partial Identifier is indicated with a

double underline

When should an attribute be linked to an entity type via a relationship?

When the attribute is the identifier or some other characteristic of an entity type in the data model and multiple entity instances need to share thee same attributes.

Relationship Type

a meaningful association between (or among) entity types. Implies that the relationship allows us to answer questions that could not be answered given only the entity types. Denoted with line and labeled with the name of the relationship. ex:// completes, calls, hires etc.

Associative Entity

combination of relationship and entity. ** the relationship name is usually converted to an entity name that is a noun. No relationship on lines between association because the associative entity represents the relationship. EX:// certificate

Examples of basic business rule are (2)

data names and definitions, constraints on data objects

Declarative

it is a statement of the policy, it is what not HOW

Data objects must be ____ before

named, before they can be used unambiguously in a model of organizational data

A business rule is intended to...

assert business structure or to control or influence the behavior of the business.

Relationship, what type of name

is an association representing an interaction among the instances of one or more entity type that is of interest to the organization. Has a verb phrase name**. Relationships and cardinalities represent business rules.

Business rule are highly

maintainable, stored in central repository, each rule expressed only once.

Maximum Cardinality

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

The ER model is most used as a

tool for communications between database designers and end users during the ANALYSIS phase of database development

Distinct

Business rules are not redundant

Levels of Database Schemas

1. External level; combines external vews 2. Conceptual Schema 3. Internal Schema

Guidelines for good data definition (4)

1. Gathered in conjunction with system requirements 2. Accompanied by diagrams 3. Concise description of essential data meaning 4. Achieved by consensus, and iteratively refined

A good data name is (7)

1. Related to business 2. meaningful and self-documenting 3. Unique 4. Readable 5. Composed of words from an approved list 6. Repeatable 7. Written in standard syntax

Guidelines for defining relationships:

1. Relationship definition explains what action is being taken a possibly why it is important. Not important to explain how the action is taken. 2. Relationship should explain optional participation,

Three most common relationship degrees:

1. Unary (degree 1) 2. Binary (degree 2) 3. Ternary (degree 3)

Criteria for selecting identifiers(4)

1. Will not change in value over the life of each instance of the entity type 2. Will not be NULL 3. No intelligent Identifiers (containing locations or people that might change) 4. Substitute new, simple keys for long, composite keys

Good business rules are...(7)

1. declarative 2. precise 3. atomic 4. consistent 5. expressible 6. distinct 7. business-oriented

Time stamps may be recorded when:

1. indicate the time the value was entered 2. the time the value becomes valid or stops being valid 3. time when critical actions were performed, such as updates, corrections, or audits.

Difference between multivalued and composite attribute

A multivalued attribute may occur one or multiple times, a composite attribute each has same number of components.

E-R model mostly expressed as a

Entity-relationship diagram

Business rules are important in data modeling because they...

Govern how data are handled and stored, and govern the people, places, events, processes, networks, and objectives of the organization

Entity Type

a collection of entities that share common properties or characteristics. (often corresponds to a table). use capital letters for names of entity types.

Entity-relationship model (E-R model)

a detailed, logical representation of the data for an organization or for a business area.

Entities

a person, place, object, event, or a concept in the under environment about which the organization wishes to maintain data. Entity has a noun name.

Attribute

a property or characteristic of an entity type that is of interest to the organization

Unary Relationship

a relationship between the instances of a single entity type.

Binary Relationship

a relationship between the instances of two entity types and is the most common type of relationship encountered in data modeling.

Entity Instance

a single occurrence of an entity type. ex:// there may be one EMPLOYEE entity type in most organizations, but there may be hundreds of instances of this entity type stored in the database.

Business Rule (definition)

a statement that defines or constrains some aspect of the business.

Instances of a strong entity type always have

a unique characteristic (an identifier)

Relationship Instance

an association between or among entity instances, where each relationship instance associates exactly one entity instance from each participating entity type.

Required Attribute & ex

an attribute that must have a value for every entity (or relationship) instance with which it is associated. Ex:// student ID or student address (boldface, or notified with symbol)

Derived Attribute, how to indicate

an attribute whose values can be calculated from related attribute values. Indicate by using square brackets around attribute name. Ex:// want to know how many years employed, use Date Employed and today's date to find column value.

Weak Entity Type

an entity whose existence depends on some other entity type. Has no business meaning in an E-R diagram without the entity on which it depends. Does not have its own identifier.

Expressible

be stated in natural, structured language

Minimum Cardinality

minimum number of instances of entity B that may be associated with each instance of entity A. if minimum is one, participation is mandatory. If minimum is zero, participation is optional.

Entities often correspond to a

row in a table

Cardinality constriant

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

Business-Oriented

stated in terms that business people can understand

Identifying relationship

the relationship between a weak entity type and its owner.

Precise

the rule must have only one agreed-upon meaning and interpretation

No need to define common terms like person, month, television, because

there terms are understood without ambiguity by most persons.

Whenever have MULTIVALUED attributes or COMPOSITE ATTRIBUTES =

they can be turned into associative entities. Turn composite attributes into new entities!!!

Business rules and policies are NOT

universal.