Database keys, Normalization and vocab

Learning outcomes:

  • Define terms used in databases such as rows, columns, relation, key
  • Describe the process of normalization
  • List the advantages and disadvantages of normalizing a database
  • Differentiate primary keys form foreign keys

Would you like to download my PowerPoint to follow along?

  • Normalization
    • What is Normalization
      • Data formatted the same
      • Organizing the data
      • Think of functionality not attractiveness of data
      • Also can be the process of creating the relationship between tables
      • It's the process of creating or fixing the rules about the database to ensure they are being followed
      • Will likely take your data and create multiple tables to organize it
      • Normalization may also reference "Normal Forms" to indicate how well the database has been normalized (note: anything beyond 4NF is unlikely outside academia)
      • Step by step process with examples
    • Why normalization is important
      • Database will be more efficient including queries that are run
      • Makes it easier to reduce duplicate and incorrectly entered information
      • Makes the database smaller because it helps data to be in one place only
      • Makes sure the data is updated everywhere it needs to be
    • Normalization Levels Example
      • To get to 1NF: single value per field
        • Example: Library book with 2 authors, you can't have both in the author field, so you'd need another deal with the second author in another way because you can't have more than 1 value in the field
      • To get to 2NF, you must have already completed 1NF, remove duplicate data and also setup a candidate key
        • Example: If the data with the library books includes location of the book we would split out the location key to another table to ensure it's consistent
      • Wikipedia actually has a nice article and table for illustrating this!
    • Example: Authors
      • Book authors :
        • JRR Tolkien
        • J.R.R. Tolkien
        • JRRTolkein
        • Tolkien, JRR
        • Tolkien, J.R.R.
      • Pennames?
      • Multiple Authors?
      • How can we split the data about books out to be normalized?
      • What other data besides Author do you think could get split out?
  • Primary Keys
    • Identifier
    • Used to be Numeric only
    • Unique
    • NOT data (i.e not social security numbers)
    • Microsoft refers to primary keys as ID
    • Modern trend has been to make Primary keys guid instead of numeric to make parallelism easier
      • Example of a guid: e2bd848b-3472-49ab-8836-9ff21aaf8c56
      • Used to make sure if multiple systems are entering in data they don't use the same key
      • Won't create chokepoint the way incremental key generation might
  • Foreign keys
    • Also called functionally dependent keys
    • Abbreviated FD
    • Point to table with the data
    • Always point to a primary key
    • In other words is a way to link data between tables in your database
    • Some databases call both foreign key AND primary key the ID (<--BADBAD)
  • Example:
    • One table with books, that has author name
    • Normalize that table to make it easier to use (take one column and split into its own table)
    • Also helps data integrity because then you have author name in one place that is called to rather than authors everywhere with different layouts
    • Two tables, one with the books, one with the authors
    • The way to connect them together is the book table has a foreign key to the author table primary key
  • Entity
    • Each table needs the data for one entity
    • Every entity has attributes (each book has information saved about it such as Authors and Titles)
    • Each table needs the data for one entity (such as authors or Genres)
    • Basic way to split up the data
    • Example: Entity is a book, attributes are author, publisher and title
  • Entity relationship table
    • Shows how entities are connected by the foreign key to primary key link
    • Diagram of the stuff in the database
    • If you say book is connected to the author, you're giving a map of the database
    • ER Diagram is the first thing we look at to see the data we have and how it's connected
      • ER is Entity Relationship
    • Abstract map of the database
      • What data you have
      • What tables you have
      • How they are linked together
    • Entity Relationship Data model Chapter 8 Database Design

Suggested Activities and Discussion Topics:

  • Discussion: Data Normalization. In pairs or groups please discuss the following questions:
    • What is the primary objective of data normalization in database management?
    • How does normalization contribute to the efficiency and reliability of databases?
    • How does data normalization influence the design of a database?
  • Activity: Create an entity relationship diagram for your data. (Some examples of data you could use are books, movies, video games, or a collectable such as Magic The Gathering)
  • Activity:Take the data (Some examples of data you could use are books, movies, video games, or a collectable such as Magic The Gathering) you have collected about your chosen topic and normalize it to be 2NF
  • Complete this PDF

Would you like to see some more classes? Click here