1 / 19

IFS180: Intro. to Data Management

IFS180: Intro. to Data Management. Chapter 2 Ensuring your database structure is sound. Intro. to Data Management Brief Review from last week. Function of PK Function of FK One – to – One Relationship One – to – Many Relationship Many – to – Many Relationship.

stu
Télécharger la présentation

IFS180: Intro. to Data Management

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IFS180: Intro. to Data Management Chapter 2 Ensuring your database structure is sound

  2. Intro. to Data Management Brief Review from last week • Function of PK • Function of FK • One – to – One Relationship • One – to – Many Relationship • Many – to – Many Relationship

  3. IFS180: Intro. to Data ManagementFunctional Dependencies and Normalization • Functional Dependency: A functional dependency occurs when one attribute in a relation uniquely determines another attribute. This can be written A -> B which would be the same as stating "B is functionally dependent upon A." • BudsCost = Boxes Buds * $2.25 • BudsCost functionally dependant on Number of Boxes • Number of Boxes  BudsCost Determines Determinant

  4. IFS180: Intro. to Data ManagementFunctional Dependencies and Normalization • Examples: • PretzelCost = Bags of Pretzels * $1.39 • TotalCost = Quantity * Price

  5. IFS180: Intro. to Data ManagementFunctional Dependencies and Normalization Object Color  Weight Color  Zone Object Color  (Weight, Shape) Color  (Zone, Price)

  6. IFS180: Intro. to Data ManagementCharacteristics of a Relation • Rows contain data about an entity • Columns contain data about attributes of entity • Columns in table hold a single value • All entries in columns are of the same kind • Each column has a unique name • Column order is not important • Row order is not important • No row duplicates

  7. IFS180: Intro. to Data ManagementData Normalization @ 10,000 Feet • First Normalized Form (1NF) • No Composite attributes in a relation • Second Normalized Form (2NF) • 1NF and every column is dependent on PK • Third Normalized Form (3NF) • 2NF and all columns (non Keyed) are not related (mutually independent)

  8. IFS180: Intro. to Data Management1NF – No composite Attributes • Identify all candidate keys Candidate Key Candidate Key

  9. IFS180: Intro. to Data Management2NF All Columns dependent on PK • Identify all functional dependencies A  B Determinant Determinant Dependency Dependency

  10. IFS180: Intro. to Data Management3NF All columns are Mutual Exclusive • Review Determinates (from 2NF) and if any are not candidate keys relation is not normalized. PED-9283 Cinnamon 746538 Jim’s Candies Dallas Karen

  11. IFS180: Intro. to Data ManagementValidate 3NF • Place functional dependency columns into their own relationship • New relationship will have a PK = determinant of functional dependency • Original relationship will contain the determinate • New relation and Original relation will have a referential integrity constraint

  12. IFS180: Intro. to Data ManagementEnsuring a sound database structure • Column (Field Names) • Descriptive and meaningful to entire organization • Clear and unambiguous • Use of acronyms or abbreviation • Implicitly or explicitly identify more than one characteristic

  13. Order Number Gender Address City State Zip Code Phone Number Name Quantity Cost PARTNO ACTNO SSID UoM / UoP / UoS BoL CofA BOM Order DT SAL ST IFS180: Intro. to Data Management Why were these shortcuts taken ???

  14. IFS180: Intro. to Data ManagementAdditional Considerations • Singular form column names • Column Name identifies characteristics of table (3NF violation) • Column only contains a single value 641 W. Main St Ephrata PA 17522 55 Northview Dr. Lancaster PA 17601 Helps to assure data integrity and accuracy How would these single values be resolved?

  15. IFS180: Intro. to Data ManagementAdditional Considerations • Make sure field does not store results of calculations • Many violations occur here in the name of performance.

  16. IFS180: Intro. to Data ManagementAdditional Considerations • Multipart Column values (pg 26) • Instrument ID GUIT2201 • (From experience) Part Number 21-1000 • Resolving multi-value columns • Break into separate fields in separate tables (and possibly) add a linking table (pg 28)

  17. IFS180: Intro. to Data ManagementFine tuning tables • Table should represent a single subject • Unique name and descriptive to entire org. • Is table name accurate, clear and unambiguous • Does table name convey physical characteristics • Does table name contain acronyms or abbreviations • Does table name identify more than one subject

  18. IFS180: Intro. to Data ManagementEnsuring sound table structure • (Again) Table represents a single subject (object or event) • Each table has a PK • No duplicate values (violation of 3NF). Table contains only functionally dependent columns

  19. IFS180: Intro. to Data ManagementKey Field Considerations • Simple PK vs Composite PK • Uniquely ID’s each row • Unique values enforce integrity • Will the field ever contain duplicates • Can field value be optional • Multipart field (should have been eliminated) • Can the value be modified ** Remain Static **

More Related