up

2020-02-11 - Normalization

• Homework 3 is due tonight at midnight
• Homework 4 is due next tuesday, the 18th, at midnight.
  • There may be no late submissions on HW4

Normalization

• Conceptual
  • Entity - an ER Diagram
  • Entity Instance
  • Attribute
• Representational
  • Relation - a relation
  • Tuple
  • Attribute
• Physical
  • Table
  • Record
  • Field / Attribute

Oh my goodness we’re finna hit the normal forms

Normalization: reduce anomalies

Anomolies:

• Redundancy
  • information repeated in multiple places
• Update
  • Failure to change all instances of a specific value
• Delete
  • Delete data and lose other values as a side effect
• Insert
  • A requirement to add data about multiple themes

Functional Dependencies

A functional attribute shows a connection between attributes within a relation.

If A determines the value of B, aka B depends on A, write this:

A → B

If we know the value of A, then we can determine the value of B and only B.

Example: EMPLOYEE(EmployeeID, firstname, lastname)

EmployeeID → Firstname, Lastname

In the above example, EmployeeID is a determinant.

Functional dependencies can require a set or determine a set.

A → B, C

or

A, B → C

Consider:

Name	DOB	Dept
Joe	12/15/60	Acct
Bill	4/11/71	Acct
Joe	12/15/60	Mgt
Tom	8/23/73	Ship
Al	12/15/60	Acct

In the above table, Name → DOB. However, Name does not determine Dept. DOB does not determine Name or dept, and dept does not determine name or dob.

However, it can be said that Name, Dept → DOB.

Normalizing these shenanigans

Identify all functional dependencies

This is step 1. Find all the ones you can.

Determine Candidate Keys

All the Determinants that could be primary keys are called candidate keys.

We must pick among the candidates which key shall become the primary key.

Composite determinants, candidate keys, and primary keys are all allowed.

Confirm or modify the proposed candidate key

Then it becomes your actual primary key

Upgrade the Normalization level

MyCourses has a ‘normalization process reference’ in week5

Second Normal Form

“A relation is in second normal form if it is in first normal form and all of its non-key attributes are dependent on the entire primary key”

• Non-key attribute
  • It’s an attribute not being used as a key
• Dependent
  • On the right side of a functional dependency
• Partial dependency
  • A non-key attribute that it dependent on only a part of a primary key

Foreign Keys

When the primary key of a relation appears in another relation, it is a foreign key. This forms relationships between relations.

In type, foreign keys are shown in italics. When hand-writing, use a dashed underline.

Here’s my attempt at a dashed underline in markdown

Referential Integrity

The values of foreign key attributes must reference a valid, existing primary key.