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
  • Activity: Listen to This Podcast That was created using AI from these materials. Transcript for the Podcast What are your thoughts? Did the AI do a good job representing the materials? Did you find any mistakes?
  • Go through This AI generated study guide, what do you think? Did it capture the week materials well? How did you do on the self quiz? Do you know all the vocab used?

Would you like to see some more classes? Click here