1 / 26

Database Lecture Notes Normalization 1 – Functional Dependencies

Database Lecture Notes Normalization 1 – Functional Dependencies. Dr. Meg Murray mcmurray@kennesaw.edu. How Many Tables?. Should we store these two tables as they are, or should we combine them into one table in our new database?. Important Relational Model Terms. Entity Relation

issac
Télécharger la présentation

Database Lecture Notes Normalization 1 – Functional Dependencies

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. DatabaseLecture NotesNormalization 1 – Functional Dependencies Dr. Meg Murray mcmurray@kennesaw.edu

  2. How Many Tables? Should we store these two tables as they are, or should we combine them into one table in our new database?

  3. Important Relational Model Terms Entity Relation Functional Dependency Determinant Candidate Key Composite Key Primary Key Surrogate Key Foreign Key Referential integrity constraint Normal Form Multivalued Dependency

  4. Relation Relational DBMS products store data about entities in relations, which are a special type of table A relation is a two-dimensional table that has the following characteristics: Rows contain data about an entity Columns contain data about attributes of the entity All entries in a column are of the same kind Each column has a unique name Cells of the table hold a single value The order of the columns is unimportant The order of the rows is unimportant No two rows may be identical KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  5. A Relation • A relation is a two-dimensional table that has the following characteristics: • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  6. Is this a Relation? • A relation is a two-dimensional table that has the following characteristics: • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical

  7. Is this a Relation? • A relation is a two-dimensional table that has the following characteristics: • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical

  8. Functional Dependencies

  9. Functional Dependency A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s) in the same table: StudentID  StudentName StudentID  (DormName, DormRoom, Fee) KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  10. (CookiePrice, Qty) BoxPrice Functional Dependency • Functional dependencies may be based on equations: ExtendedPrice = Quantity X UnitPrice (Quantity, UnitPrice)  ExtendedPrice • Illustration… • The price of one cookie can determine the price of a box of 12 cookies • But functional dependencies are not equations! KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  11. Determinant (CookiePrice, Qty) BoxPrice Determinants • The attribute (or attributes) that we use as the starting point (the variable on the left side of the equation) is called a determinant KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  12. Functional Dependencies ObjectColor  Weight ObjectColor  Shape ObjectColor (Weight, Shape) KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  13. Functional Dependencies Weight ObjectColor [FD? Yes or No] Shape  ObjectColor [FD? Yes or No] (Weight, Shape)  ObjectColor [FD? Yes or No] KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  14. Composite Determinants Composite determinant: A determinant of a functional dependency that consists of more than one attribute (StudentName, ClassName)  (Grade) KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  15. Candidate/Primary Keys and Functional Dependency • By definition…A candidate key of a relation will functionally determine all other attributes in the row • Likewise, by definition…A primary key of a relation will functionally determine all other attributes in the row KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  16. (EmpLastName, EmpPhone) (EmployeeID) Primary Key and Functional Dependency Example **Remember the Primary Key determines all other attributes KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  17. Functional Dependencies • Another way to think about it: • Functional Dependencies = a single-valued fact • Is Y a fact related to X? • Is SKU_Description related to SKU • Or Does X determine Y? • Does SKU determine SKU_Description KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  18. Functional Dependencies in the SKU_DATA Table • Y is "functionally dependent" on X if it is invalid to have two records with the same X-value but different Y-values. • An X-value must always occur with the same Y-value. KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  19. Functional Dependencies in the SKU_DATA Table SKU  (SKU_Description, Department, Buyer) SKU_Description  (SKU, Department, Buyer) Buyer  Department KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  20. Functional Dependencies in the ORDER_ITEM Table KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  21. Functional Dependencies in the ORDER_ITEM Table (OrderNumber, SKU)  (Quantity, Price, ExtendedPrice) (Quantity, Price)  (ExtendedPrice) KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  22. How to Discover Determinants Often business rules We have been discovering determinants from a table of attributes Often the Functional Dependencies are assessed before a Table is constructed

  23. More on FDs and Keys A candidate key is a key that determines all of the other columns in a relation It may be a determinant key However NOT all determinant keys are candidate keys SKU  (SKU_Description, Department, Buyer) SKU_Description  (SKU, Department, Buyer) Buyer  Department Which are candidate keys? Which is a determinate key but not a candidate key? KROENKE and AUER - DATABASE CONCEPTS (3rd Edition) © 2008 Pearson Prentice Hall

  24. So… • When X is a key, then all fields are by definition functionally dependent on X in a trivial way, since there can't be two records having the same X value. 3-24

  25. One last word on FD In this table, there is not a unique identifier so there is no functional dependency Although each person has a unique address, a given name can appear with several different addresses • Functional dependencies only exist when the things involved have unique and singular identifiers ---------------------------------------------- --- | PERSON | ADDRESS | ----------------+---------------------- | John Smith | 123 Main St., New York | | John Smith | 321 Center St., San Francisco | ------------------------------------------------------------ 3-25

  26. In- class http://adbc.kennesaw.edu Database Design  Functional Dependency

More Related