D111-Data Fundamentals

D111-Data Fundamentals Normalisation Second Normal Form (2NF)

Objectives • Introduction to Database Normalization • Second Normal Form • Database Forms with MS Access • Database Queries with MS Access

What is Normalisation? un- normalised tables Normalisation Process The ideal table structures The process of decomposing large, inefficiently structured tables into smaller, more efficiently structured tables without losing any data in the process.

The Normalization Process Remove repeating and multi-valued attributes Remove partially dependent attributes Remove transitively dependent attributes Apply additional normal forms (?)

First Normal Form (1NF) Rules of 1NF: • No Repeating Groups • Data item cannot be broken down any further (atomic) • Each column has an unique name • Each row must have a unique identifier (Primary Key).

Our Example 1Name: Jim Hicks 1.6

Invoice in Spreadsheet format

What are Repeating Groups? Row Repeating Group

Another examples of Repeating data

Atomic Data? • A piece of information that is broken down into the smallest pieces of data. • For example a date is divisible into day, month and year components • Split your information in as small parts as you think are convenient for your use.

Atomic Teach-ICT.com

Primary Key • The purpose of a primary key field is to provide a value that will can be used to uniquely identify each row in a table. • Each non primary key value is then dependant on that primary key to be found.

Each row must have a unique identifier (Primary Key). Look for a field/s as a possible candidate/s to use for the Primary Key (CANDIDATE KEYS) What is the main purpose for this table? To hold invoice details - so InvoiceNo would be a good start Obviously on its own InvoiceNo cannot be the PK. You may need more than one field to locate the exact record

Each row must have a unique identifier (Primary Key). What other field/s are a candidate for a concatenated PK ProductID and Description Why would we choose the ProductID? Smaller size and easy to maintain

Check for Unique Column Names InvoiceNopk + ProductIDpk

Remove the Repeating Group • Place the repeating group data in its own table but take the entire primary key with it. (WHY?) We need the InvoiceNo to link back to the main invoice table Notice the smaller table? Repeating group data

The ERD as it stands at this point INVOICE INVOICE_PRODUCT

Second Normal Form (2NF) • For a table to be in Second Normal Form: • Every non-key field must depend on the entire primary key, not part of a concatenated primary key. • If a database has only single-field primary keys, it is automatically in Second Normal Form.

Partial Dependancies Already in 2NF Partial dependencies?

Finding the Partial Dependencies Would only a single Description value be returned by search the table using InvoiceID 1234? SELECT Description FROM Table WHERE InvoiceNo = 1234 No, because it would return;

Finding the Partial Dependencies Would only a single Description value be returned by search the table using ProductID 1? SELECT Description FROM Table WHERE ProductID = 1 Yes, because it would only return;

Finding the Partial Dependencies • Remove partial dependencies to there own table but take a copy of the primary key it depends on with it. (Why?) Notice the reduced redundancy?

Finding the Partial Dependencies • What about the rest?

Tables should look like the following: INVOICE INVOICE_PRODUCT Both unique Qty and SalePrice values depend on the Concatenated Primary Key. LineTotal is eliminated because it is a derived column made by calculating the total of Qty * SalePrice PRODUCT

The ERD as it stands at this point INVOICE INVOICE_PRODUCT PRODUCT

See if you can get this table to meet the rules of 2NF CONCERT • Every non-key field must depend on the entire primary key, • not on part of a concatenated primary key.

Answer CONCERT BAND

References http://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/normalisation/miniweb/index.htm

D111-Data Fundamentals