DATA MODELS FOR BUSINESS DATABASE, Slides of Database Management Systems (DBMS)

Download DATA MODELS FOR BUSINESS DATABASE and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database Systems

Lecture

Topic: Develop Data Models for Business Databases

Instructor: Lecturer Ayesha Naseer

Learning Objectives

• Develop ERDs that are consistent with narrative problems.
• Use transformations to generate alternative ERDs.
• Document design decisions implicit in an ERD.
• Analyze an ERD for common design errors.
• Convert an ERD to a table design using conversion rules.

Guidelines for Analyzing Business Information Needs

Adding Relationships

Relationships often appear as verbs connecting nouns previously identified as entity types.

For example, the sentence, "Students enroll in courses each semester" indicates a relationship between

students and courses.

For relationship cardinality, you should look at the number (singular or plural) of nouns along with other

words that indicate cardinality.

For example, the sentence, "A course offering is taught by an instructor" indicates that there is one

instructor per course offering. You should also look for words such as "collection" and "set" that indicate

a maximum cardinality of more than one.

For example, the sentence, "An order contains a collection of items" indicates that an order is related to

multiple items.

Minimum cardinality can be indicated by words such as "optional" and "required." In the absence of

indication of minimum cardinality, the default should be mandatory.

Summary of Analysis Guidelines

Analysis of the Information Requirements for the Water Utility Database

Refinements to an ERD

Data modeling is usually an iterative or repetitive process. You construct a preliminary data model and then refine

it many times. In refining a data model, you should generate feasible alternatives and evaluate them according to

user requirements. You typically need to gather additional information from users to evaluate alternatives. This

process of refinement and evaluation may continue many times for large databases.

1. Transforming Attributes into Entity Types

A common refinement is to transform an attribute into an entity type. When the database should contain more

than just the identifier of an entity, this transformation is useful. This transformation involves the addition of an

entity type and a 1 - M relationship. In the water utility ERD, the Reading entity type contains the EmpNo attribute.

If other data about an employee are needed, EmpNo can be expanded into an entity type and 1 - M relationship as

in following Figure.

Refinements to an ERD

3. Expanding Entity Types A third transformation is to make an entity type into two entity types and a relationship. This transformation can be useful to record a finer level of detail about an entity. For example, rates in the water utility database apply to all levels of consumption beyond a fixed level. It can be useful to have a more complex rate structure in which the variable amount depends on the consumption level. Figure 6. 5 shows a transformation to the Rate entity type to support a more complex rate structure. The RateSet entity type represents a set of rates approved by the utility's governing commission. The primary key of the Rate entity type borrows from the RateSet entity type. Identification dependency is not required when transforming an entity type into two entity types and a relationship. In this situation, identification dependency is useful, but in other situations, it may not be appropriate.

Refinements to an ERD

4. Transforming a Weak Entity into a Strong Entity A fourth transformation is to make a weak entity into a strong entity and change the associated identifying relationships into non identifying relationships. This transformation can make it easier to reference an entity type after conversion to a table design. After conversion, a reference to a weak entity will involve a combined foreign key with more than one column. This transformation is most useful for associative entity types, especially associative entity types representing M-way relationships.

Refinements to an ERD

A sixth transformation is to make an entity type into a generalization hierarchy. This transformation

should be used sparingly because the generalization hierarchy is a specialized modeling tool. If there are

multiple attributes that do not apply to all entities and there is an accepted classification of entities, a

generalization hierarchy may be useful.

For example, water utility customers can be classified as commercial or residential. The attributes

specific to commercial customers (TaxPayerlD and EnterpriseZone) do not apply to residential customers

and vice versa. In following Figure, the attributes specific to commercial and residential customers have

been moved to the subtypes.

Summary of Transformations

Detecting Common Design Errors Incorrect Cardinalities The typical error involves the usage of a 1 - M relationship instead of an M-N relationship. This error can be caused by an omission in the requirements. For example, if the requirements just indicate that work assignments involve a collection of employees, you should not assume that an employee can be related to just one work assignment. You should gather additional requirements to determine if an employee can be associated with multiple work assignments. Other incorrect cardinality errors that you should consider are reversed cardinalities ( 1 - M relationship should be in the opposite direction) and errors on a minimum cardinality. The error of reversed cardinality is typically an oversight. Overuse of Specialized Data Modeling Constructs Generalization hierarchies and M-way associative entity types are specialized data modeling constructs. A typical novice mistake is to use them inappropriately. You should not use generalization hierarchies just because an entity can exist in multiple states. For example, the requirement that a project task can be started, in process, or complete does not indicate the need for a generalization hierarchy. If there is an established classification and specialized attributes and relationships for subtypes, a generalization hierarchy is an appropriate tool.

Detecting Common Design Errors  Redundant Relationships Cycles in an ERD may indicate redundant relationships. A cycle involves a collection of relationships arranged in a loop starting and ending with the same entity type. For example in Figure 6. 10 , there is a cycle of relationships connecting Customer, Bill, Reading, and Meter. In a cycle, a relationship is redundant if it can be derived from other relationships. For the SentTo relationship, the bills associated with a customer can be derived from the relationships Uses, ReadBy, and Includes. In the opposite direction, the customer associated with a bill can be derived from the Includes, ReadBy, and Uses relationships. Although a bill can be associated with a collection of readings, each associated reading must be associated with the same customer. Because the SentTo relationship can be derived, it is removed in the final ERD.

Converting an ERD to Relational Tables (Logical Design)

1. Entity Type Rule: Each entity type (except subtypes) becomes a table. The primary key of the entity

type (if not weak) becomes the primary key o f the table. The attributes of the entity type become

columns in the table. This rule should be used first before the relationship rules.

2. 1 - M Relationship Rule: Each 1 - M relationship becomes a foreign key in the table corresponding to

the child entity type (the entity type near the Crow's Foot symbol). If the minimum cardinality on the

parent side of the relationship is one, the foreign key cannot accept null values.

3. M-N Relationship Rule: Each M-N relationship becomes a separate table. The primary key of the

table is a combined key consisting of the primary keys of the entity types participating in the M-N

relationship.

4. Identification Dependency Rule: Each identifying relationship (denoted by a solid relationship line)

adds a component to a primary key. The primary key of the table corresponding to the weak entity

consists of (i) the underlined local key (if any) in the weak entity and (ii) the primary key(s) of the entity

type(s) connected by identifying relationship(s).

5. Generalization Hierarchy Rule: Each entity type of a generalization hierarchy becomes a table. The

columns of a table are the attributes of the corresponding entity type plus the primary key o f the

parent entity type. For each table representing a subtype, define a foreign key constraint that

references the table corresponding to the parent entity type.

6. 1- 1 Relationship Rule: Each 1-1 relationship is converted into two foreign keys. If the relationship is

optional with respect to one of the entity types, the corresponding foreign key may be dropped to

eliminate null values.