Agenda and objectives
Download
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 in that entity 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 in that entity (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.


Resulting 1nf table
Resulting 1NF Table in that entity


How to correct multivalued or repeating attributes
How to Correct in that entityMultivalued or Repeating Attributes

  • Create new entity

  • New entity could be weak/dependent/identifying

  • New entity could independent


Multivalued attributes table
Multivalued in that entity Attributes: Table

  • Add rows, not columns



Resulting table
Resulting Table in that entity



Resulting table1
Resulting Table in that entity


Is this a good solution
Is This a Good Solution? in that entity


Resulting table2
Resulting Table in that entity


How would you solve this problem
How would you Solve This Problem? in that entity

What about employees with more than 3 children?


Second normal form 2nf

Entities meet 1NF requirements in that entityPLUS

All attributes are functionally dependent upon the entire primary key.

Second Normal Form (2NF)



Resulting table3
Resulting Table in that entity


Third normal form 3nf violation

Entities meet 2NF requirements in that entityBUT

One non-key attribute determines another non-key attribute

Third Normal Form (3NF) Violation


Completed 3nf erd
Completed in that entity3NF ERD



Resulting relations
Resulting Relations in that entity


Review

Know the difference between 1NF, 2NF and 3NF in that entity

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 in that entity

  • 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 in that entity

  • 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.