agenda and objectives
Download
Skip this Video
Download Presentation
Agenda and Objectives

Loading in 2 Seconds...

play fullscreen
1 / 26

Agenda and Objectives - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on

Agenda and Objectives. Agenda Examine normalized and non-normalized ERDs/Tables Objectives Given an ERD, you will be able to determine whether or not it is in 3NF. Given a non-normalized ERD (or database table), you will be able to revise it to make it 3NF compliant. Resource

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Agenda and Objectives' - jules


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda and objectives
Agenda and Objectives
  • Agenda
    • Examine normalized and non-normalized ERDs/Tables
  • Objectives
    • Given an ERD, you will be able to determine whether or not it is in 3NF.
    • Given a non-normalized ERD (or database table), you will be able to revise it to make it 3NF compliant.
  • Resource
    • Normalized_ERDs.pptx
normalizing your database model

Normalizing Your Database Model

IS 310Dr. Jean A. Pratt

related terminology
Related Terminology
  • Normalization
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • 1NF: No repeating groups; 1 value/cell
  • 2NF: No partial dependencies
  • 3NF: No transitive dependencies
first normal form 1nf

Only those attributes that describe the entity are included in that entity

Each entity has a primary key (PK)

Each attribute is the smallest unit that would be included in a query

Data is not repeated in different entities

Each attribute would contain a single value in the database table cell

Redundant data is minimized

First Normal Form (1NF)
multivalued attributes avoid
Multivalued Attributes: AVOID
  • Multivalued attribute
    • An attribute that may take on more than one value for each entity instance
      • Examples
        • Skillis a multivaluedattribute of Employee
        • Color may be a multivalued attribute of ToyBlock
        • Dependent is a multivalued attribute of Employee
        • Phone/fax number is a multivalued attribute of Employee
  • Repeating group
    • A set of multivalued attributes that are logically related—associated with the same entity instance
      • Dependent address for each dependent of employee
example of almost 1nf erd
Example of (almost) 1NF ERD

Note that we will still have redundant data (although not multivalued) if students live at the same address. We’ll want to remove that redundancy later.

how to correct multivalued or repeating attributes
How to Correct Multivalued or Repeating Attributes
  • Create new entity
  • New entity could be weak/dependent/identifying
  • New entity could independent
multivalued attributes table
Multivalued Attributes: Table
  • Add rows, not columns
how would you solve this problem
How would you Solve This Problem?

What about employees with more than 3 children?

second normal form 2nf

Entities meet 1NF requirements PLUS

All attributes are functionally dependent upon the entire primary key.

Second Normal Form (2NF)
third normal form 3nf violation

Entities meet 2NF requirements BUT

One non-key attribute determines another non-key attribute

Third Normal Form (3NF) Violation
review

Know the difference between 1NF, 2NF and 3NF

Ensure your ERD (and resulting database) is in at least 2NF

3NF is the standard (with purposeful violations based on performance)

Review
application to your project
Application to Your Project
  • Define the necessary tables from every DFD data store
  • Create an ERD to model the data storage
  • Normalize the ERD to 3NF
  • Create mock-up relations to test your model
  • Include the ERD and mock-up relations in your final project documentation
data modeling practice
Data Modeling Practice
  • A vendor supplies many products.
  • A product is supplied by many vendors.
  • A customer buys one or more products.
  • Each product can be purchased by many customers.
  • An employee can help many customers.
  • A customer is helped by one or more employees.
  • Each employee works in one department.
  • A department has many employees (working in it).
  • Each department is part of many stores.
  • Each store contains many departments.

Scenario: Chain stores (e.g., Target) that contain several departments and sell many products to many customers.

ad