Database Fundamentals- Content Analysis

profilewostinabin2
Week7-Lectureslides-20200617.zip
Home>Information Systems homework help>Database Fundamentals- Content Analysis

Lecture7_ch07.ppt

Database Principles: Fundamentals of Design, Implementations and Management

CHAPTER 7 Normalizing Database Designs

*

Objectives

  • In this chapter, you will learn:
  • What normalization is and what role it plays in the database design process
  • About the normal forms 1NF, 2NF, 3NF, BCNF,
    and 4NF
  • How normal forms can be transformed from lower normal forms to higher normal forms
  • That normalization and ER modeling are used concurrently to produce a good database design
  • That some situations require denormalization to generate information efficiently

*

Database Tables and Normalization

  • Normalization
  • Process for evaluating and correcting table structures to minimize data redundancies
  • Reduces data anomalies
  • Works through a series of stages called normal forms:
  • First normal form (1NF)
  • Second normal form (2NF)
  • Third normal form (3NF)

*

Database Tables and Normalization (cont..)

  • Normalization (cont..)
  • 2NF is better than 1NF; 3NF is better than 2NF
  • For most business database design purposes, 3NF is as high as needed in normalization
  • Highest level of normalization is not always most desirable
  • Denormalization produces a lower normal form
  • Price paid for increased performance is greater data redundancy

*

The Need for Normalization

  • Example: company that manages building projects
  • Charges its clients by billing hours spent on each contract
  • Hourly billing rate is dependent on employee’s position
  • Periodically, report is generated that contains information such as displayed in Table 5.1

The Need for Normalization

*

The Need for Normalization

*

*

The Need for Normalization (cont..)

  • Structure of data set in Figure 7.1 does not handle data very well
  • Table structure appears to work; report generated with ease
  • Unfortunately report may yield different results depending on what data anomaly has occurred
  • Relational database environment suited to help designer avoid data integrity problems

*

The Normalization Process

  • Each table represents a single subject
  • No data item will be unnecessarily stored in more than one table
  • All attributes in a table are dependent on the primary key
  • Each table void of insertion, update, deletion anomalies

Void = depourvu de

*

The Normalization Process (cont..)

*

*

The Normalization Process (cont..)

  • Objective of normalization is to ensure all tables in at least 3NF
  • Higher forms not likely to be encountered in business environment
  • Normalization works one relation at a time
  • Progressively breaks table into new set of relations based on identified dependencies

*

Conversion to First Normal Form

  • Repeating group
  • Derives its name from the fact that a group of multiple entries of same type can exist for any single key attribute occurrence
  • Relational table must not contain repeating groups
  • Normalizing table structure will reduce data redundancies
  • Normalization is three-step procedure

*

Conversion to First Normal Form (cont.)

  • Step 1: Eliminate the Repeating Groups
  • Present data in tabular format, where each cell has single value and there are no repeating groups
  • Eliminate nulls: each repeating group attribute contains an appropriate data value

  • Step 2: Identify the Primary Key
  • Primary key must uniquely identify attribute value
  • New key must be composed

Conversion to First Normal Form (cont..)

*

Conversion to First Normal Form (cont..)

*

Conversion to First Normal Form (cont..)

  • Step 3: Identify All Dependencies
  • Dependencies can be depicted with help of a diagram
  • Dependency diagram:
  • Depicts all dependencies found within given table structure
  • Helpful in getting bird’s-eye view of all relationships among table’s attributes
  • Makes it less likely that will overlook an important dependency

*

Conversion to First Normal Form (cont..)

*

*

Conversion to First Normal Form (cont.)

  • First normal form describes tabular format in which:
  • All key attributes are defined
  • There are no repeating groups in the table
  • All attributes are dependent on primary key
  • All relational tables satisfy 1NF requirements
  • Some tables contain partial dependencies
  • Dependencies based on part of the primary key
  • Sometimes used for performance reasons, but should be used with caution
  • Still subject to data redundancies

Conversion to Second Normal Form

  • Relational database design can be improved by converting the database into second normal form (2NF)
  • Two steps

*

*

Conversion to Second Normal Form (cont..)

  • Step 1: Write Each Key Component
    on a Separate Line
  • Write each key component on separate line, then write original (composite) key on last line
  • Each component will become key in new table

  • Step 2: Assign Corresponding Dependent Attributes
  • Determine those attributes that are dependent on other attributes
  • At this point, most anomalies have been eliminated

Conversion to Second Normal Form (cont..)

*

*

Conversion to Second Normal Form (cont..)

  • Table is in second normal form (2NF) when:
  • It is in 1NF and
  • It includes no partial dependencies:
  • No attribute is dependent on only portion of primary key

*

Conversion to Third Normal Form

  • Data anomalies created are easily eliminated by completing three steps
  • Step 1: Identify Each New Determinant
  • For every transitive dependency, write its determinant as PK for new table
  • Determinant:
  • any attribute whose value determines other values within a row

Conversion to Third Normal Form (cont..)

  • Step 2: Identify the Dependent Attributes
  • Identify attributes dependent on each determinant identified in Step 1 and identify dependency

  • Name table to reflect its contents and function

*

*

Conversion to Third Normal Form (cont.)

  • Step 3: Remove the Dependent Attributes from Transitive Dependencies
  • Eliminate all dependent attributes in transitive relationship(s) from each of the tables
  • Draw new dependency diagram to show all tables defined in Steps 1–3
  • Check new tables as well as tables modified in Step 3 to make sure that:
  • Each table has a determinant and that
  • No table contains inappropriate dependencies

Conversion to Third Normal Form (cont..)

*

*

Conversion to Third Normal Form (cont.)

  • A table is in third normal form (3NF) when both of the following are true:
  • It is in 2NF
  • It contains no transitive dependencies

*

Improving the Design

  • Table structures are cleaned up to eliminate troublesome initial partial and transitive dependencies

  • Normalization cannot, by itself, be relied on to make good designs

  • It is valuable because its use helps eliminate data redundancies

*

Improving the Design (cont..)

  • Issues to address in order to produce a good normalized set of tables:
  • Evaluate PK Assignments
  • Evaluate Naming Conventions
  • Refine Attribute Atomicity
  • Identify New Attributes
  • Identify New Relationships
  • Refine Primary Keys as Required for Data Granularity
  • Maintain Historical Accuracy
  • Evaluate Using Derived Attributes

Improving the Design (cont..)

*

Improving the Design (cont..)

*

Improving the Design (cont..)

*

*

Surrogate Key Considerations

  • When primary key is considered to be unsuitable, designers use surrogate keys
  • Data entries in Table 7.3 are inappropriate because they duplicate existing records
  • Yet there has been no violation of either entity integrity or referential integrity

*

Higher-Level Normal Forms

  • Tables in 3NF perform suitably in business transactional databases
  • Higher order normal forms useful on occasion
  • Two special cases of 3NF:
  • Boyce-Codd normal form (BCNF)
  • Fourth normal form (4NF)

*

The Boyce-Codd Normal Form (BCNF)

  • Every determinant in table is a candidate key
  • Has same characteristics as primary key, but for some reason, not chosen to be primary key
  • When table contains only one candidate key, the 3NF and the BCNF are equivalent
  • BCNF can be violated only when table contains more than one candidate key

*

The Boyce-Codd Normal Form (BCNF) (cont..)

  • Most designers consider the BCNF as special case of 3NF
  • Table is in 3NF when it is in 2NF and there are no transitive dependencies
  • Table can be in 3NF and fails to meet BCNF
  • No partial dependencies, nor does it contain transitive dependencies
  • A nonkey attribute is the determinant of a key attribute

The Boyce-Codd Normal Form (BCNF) (cont...)

*

The Boyce-Codd Normal Form (BCNF) (cont..)

*

The Boyce-Codd Normal Form (BCNF) (cont..)

*

*

Summary

  • Normalization is used to minimize data redundancies
  • First three normal forms (1NF, 2NF, and 3NF) are most commonly encountered
  • Table is in 1NF when:
  • All key attributes are defined
  • All remaining attributes are dependent on primary key

*

Summary (continued)

  • Table is in 2NF when it is in 1NF and contains no partial dependencies
  • Table is in 3NF when it is in 2NF and contains no transitive dependencies
  • Table that is not in 3NF may be split into new tables until all of the tables meet 3NF requirements
  • Normalization is important part—but only part—of the design process

*

Summary (continued)

  • Table in 3NF may contain multivalued dependencies
  • Numerous null values or redundant data
  • Convert 3NF table to 4NF by:
  • Splitting table to remove multivalued dependencies
  • Tables are sometimes denormalized to yield less I/O, which increases processing speed

Additional Slides

  • Please have a look a the following slides

*

*

Fourth Normal Form (4NF)

  • Table is in fourth normal form (4NF) when both of the following are true:
  • It is in 3NF
  • No multiple sets of multivalued dependencies
  • 4NF is largely academic if tables conform to following two rules:
  • All attributes dependent on primary key, independent of each other
  • No row contains two or more multivalued facts about an entity

Fourth Normal Form (4NF) (continued)

*

*

Fourth Normal Form (4NF)

Fourth Normal Form (4NF)

*

*

Normalization and Database Design

  • Normalization should be part of the design process
  • Make sure that proposed entities meet required normal form before table structures are created
  • Many real-world databases have been improperly designed or burdened with anomalies
  • You may be asked to redesign and modify existing databases

*

Normalization and Database Design (cont.)

  • ER diagram
  • Identify relevant entities, their attributes, and their relationships
  • Identify additional entities and attributes
  • Normalization procedures
  • Focus on characteristics of specific entities
  • Micro view of entities within ER diagram
  • Difficult to separate normalization process from ER modeling process
  • Two techniques should be used concurrently

Figure 7.13 in your book

*

Normalization and Database Design (cont.)

Figure 7.13 in your book

*

Normalization and Database Design (cont.)

Figure 7.14 in your book

Figure 7.14 in your book

*

Figure 7.15 in your book

Normalization and Database Design (cont.)

Figure 7.15 in your book

Normalization and Database Design (continued)

*

Normalization and Database Design (continued)

*

Normalization and Database Design (continued)

*

*

*

Void = depourvu de

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*