Database Normalization Flashcards

Understanding Normalization

• Normalization is organizing data by separating it into multiple tables.
• It aims to:
  • Reduce data duplication.
  • Increase data integrity.
• Instead of storing all sales information for a customer in one table, normalization separates the data.
• Data duplication increases database size.
• Normalization increases data integrity.
• Instead of storing customer state information in every customer record, it's split out into a separate state table.
• Tables are linked using foreign key and primary key relationships.
• Changing the state in the state table automatically updates it everywhere else via the link.

Normalization Levels

• There are five available normalization levels.
• First Normal Form (1NF).
• Second Normal Form (2NF).
• Third Normal Form (3NF).
• Fourth Normal Form (4NF).
• Fifth Normal Form (5NF).
• Most database administrators use up to the Third Normal Form.
• Higher levels add complexity to the database design and data storage considerations.
• The first three levels effectively reduce data duplication and maintain data integrity.

First Normal Form (1NF)

• A table should contain no repeating groups or columns.
• Example: Customer table with address one and address two columns. While seemingly repetitive, the data stored is different (street address vs. suite number).
• SQL Server Example: Sales.Customer table has specific fields like CustomerID, PersonID, StoreID, TerritoryID, AccountNumber, RowGuid, and ModifiedDate.
• Sales.SalesOrderHeader table contains sales information like RevisionNumber, OrderDate, DueDate, ShipDate, and Status.
• Storing SalesOrderHeader fields in the Customer table would lead to data repetition.
• Instead, the tables are separated and linked through the CustomerID.
• Achieving First Normal Form involves iterative data design.

Second Normal Form (2NF)

• All attributes in a table must depend on the primary key.
• The primary key ensures uniqueness across each row.
• Values in different columns depend on each other and the primary key.
• Person table example in AdventureWorks database.
• BusinessEntityID is the primary key for the person table.
• Other fields include PersonType, NameStyle, Title, etc.
• All fields in the Person table depend on the BusinessEntityID.
• State is not listed in the Person table as it's more related to a StateProvince table.
• In Second Normal Form, every field in the table must have a direct relationship and dependency on the primary key.

Third Normal Form (3NF)

• No attributes in a table should depend on non-key attributes.
• Example: Placing a state attribute directly in a person table violates this rule, as state depends on the address, not the person directly.
• Avoid repeating groups of the same thing.
• Example: Do not include item name, price, and quantity in a customer table, as those values can be repeated across multiple sales for that customer and are not directly dependent on the customer.
• Again, we looked at that Person.Person table as an example. You wouldn't put the state in that person table.
• A person does have a state in their address.
• Person.Address table breaks down address information with AddressID and StateProvinceID.
• Person.AddressType table contains more detailed address information.
• Drilling down into these tables reveals how a person's address maps to a state.
• Normalization splits data across multiple tables to achieve Third Normal Form.
• The AdventureWorks database applies normalization to avoid data duplication.
• Data integrity is improved by maintaining data in appropriate, separate tables.
• This approach prepares us for multi-table queries.