【考试不慌】ISS615 Database Mgt学习笔记(2) Modeling Data in Organization

Good business rules

Database Analysis

• Identify and understand those rules that govern data

• Represent those rules so that they can be unambiguously understood by information systems dethosevelopers and users

• Implement rules in database technology

 The E-R Model:

An Overview An entity-relationship model (E-R model) is a detailed, logical representation of the data for an organization or for a business area.

The E-R model is expressed in terms of entities in the business environment, the relationships (or associations) among those entities, and the attributes (or properties) of both the entities and their relationships.

An E-R model is normally expressed as an entity-relationship diagram (E-R diagram, or ERD), which is a graphical representation of an E-R model.

• Entity-Relationship model (E-R model) : a logical representation of the data for an organization or for a business area.

• Entity-Relationship diagram (E-R diagram) : a graphical representation of an entity-relationship model.

Entity

Sample E-R Diagram

Examples of business rules captured by the above ER model.

• A  customer  may submit any number of  order s​.

• An  order  is submitted by one and only one  customer ​.

• An  order  requests for one or more  product s​.

• A  product  may or may not be requested by some  order s.

Attributes

Each entity type has a set of attributes associated with it. An attribute is a property or characteristic of an entity type that is of interest to the organization. (Later, we will see that some types of relationships may also have attributes.)

Thus, an attribute has a noun name. Following are some typical entity types and their associated attributes: •  Classification of attributes:

– Simple vs . Composite Attribute

– Single-Valued vs. Multi-valued Attribute

– Stored vs. Derived Attributes

•  Assumption: To standardize the requirement, all the derived attributes must be included into ER models

– Identifier Attributes

•  Attribute Symbol

– Attribute names inside the entity rectangle

Simple versus composite attributes

A composite attribute is an attribute, such as Address, that has meaningful component parts, which are more detailed attributes.

A simple (or atomic) attribute is an attribute that cannot be broken down into smaller components that are meaningful for the organization. For example, all the attributes associated with AUTOMOBILE are simple: Vehicle ID, Color, Weight, and Horsepower.

Multivalued attribute: An attribute that may take on more than one value for a given entity (or relationship) instance.

Stored Versus Derived Attributes

Some attribute values that are of interest to users can be calculated or derived from other related attribute values that are stored in the database. For example, suppose that for an organization, the EMPLOYEE entity type has a Date Employed attribute. If users need to know how many years a person has been employed, that value can be calculated using Date Employed and today’s date.

A derived attribute is an attribute whose values can be calculated from related attribute values (plus possibly data not in the database, such as today’s date, the current time, or a security code provided by a system user).

Identifier Attribute

An identifier is an attribute (or combination of attributes) whose value distinguishes individual instances of an entity type. That is, no two instances of the entity type may have the same value for the identifier attribute.

A composite identifier is an identifier that consists of a composite attribute.

In Class Exercise 2.3 

 In Class Exercise 2.4

• The entity type STUDENT has the following attributes: Id (unique), Name, Phone, Age, Activity, and No_of_Year

– Activity: represents a campus-based co-curriculum activity (CCA)

– No_of_Year: the number of years the student may have engaged in this CCA

• An assumption: a given student may engage in more than one activity

• Create a single entity model for this scenario

Relationship

a relationship is an association representing an interaction among the instances of one or more entity types that is of interest to the organization. Thus, a relationship has a verb phrase name. Relationships and their characteristics (degree and cardinality) represent business rules, and usually relationships represent the most complex business rules shown in an ERD. In other words, this is where data modeling gets really interesting and fun, as well as crucial for controlling the integrity of a database. 

Relationship instance

An association between (or among) entity instances where each relationship instance associates exactly one entity instance from each participating entity type.

Associative Entity

An associative entity is an entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.

• A unary or binary relationship could be modelled as associative entity only if it is many-to-many AND it has at least one attribute

• Important notes about associative entity:

–The associative entity may or may not have an identifier

–The associative entity may have its own relationships

 • Associative entity symbol: –Rounded angle rectangle

(a) Each employee has a common Complete_Date and a common Grade for all the courses he/she completes

(b) All the employees completing a course share the same Grade and same Complete_Date

(c) Each employee can complete different courses at different Complete_Date  with different Grade. Each course can be taken by different  employees at different Complete_Date  with different Grade

Associative entity involves a rounded angle rectangle.

Note that the many-to-many cardinality symbols face toward the associative entity and not toward the other entities

Degree of a relationship

The degree of a relationship is the number of entity types that participate in that relationship. 

Unary Relationship

A unary relationship is a relationship between the instances of a single entity type.

Binary Relationship

Ternary Relationship

A ternary relationship is a simultaneous relationship among the instances of three entity types.

Cardinality of relationship

–Number of instances of one entity that can or must be associated with each instance of another entity.

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 (one vs. many)         

How to Read Cardinality 

Strong vs. Weak Entities, and Identifying Relationships

Strong entities

– exist independently of other types of entities

– has its own unique identifier

– represented with single-line rectangle

Weak entity

– cannot form an identifier by its own attributes alone

– it is identified by (one or more of) its own attributes plus the identifier from another entity (also named as owner of the weak entity)

– represented with double-line rectangle

Identifying relationship

– relationship between a weak entity and its owner

– represented with double line

An instance of weak entity is identified by

1. The identifier from the owner entity
2. The partial identifier from itself (represented by the double-line)

 An example: a dependent is identified by the Employee_ID of its owner entity EMPLOYEE and the partial identifier Dependent_Name.​​​​​​​ 

Associative Entities

• It seems like a relationship: it links entities together

• Yet it seems like an entity: it has attributes

• When should a relationship with attributes instead be an associative entity?

- All relationships for the associative entity should be many

- The associative entity could have meaning independent of the other entities

- The associative entity preferably has a unique identifier (but not necessarily), and should also have other attributes

- Ternary relationships should be converted to associative entities

- (The associative entity may participate in other relationships other than the entities of the associated relationship)