up

2020-02-13

• Feb 25th and 27th will be midterm written and practical
  • Can be normalization
  • Interpreting ER diagrams and / or relations

Class must pick one:

• An 8.5x11 cheat-sheet during the midterm
• Forgiveness on difficult questions

voting will take place next class.

Practice

I will bold the keys instead of underlining them

Normalize this relation:

A(1,2,3,4,5,6,7,8,9)

With these dependencies:

• 1,2,3 → 4,5,6,7,8,9
• 1 → 4
• 4 → 1
• 2 → 6,7,8,9
• 6,7 → 8,9

My Solution

Identify problem dependencies

partial dependencies:

• 4 only depends on 1
• 6,7,8,9 only depend on 2

Shuffle Shuffle

Alright so now we have:

• A(1,2,3,5)
• B(1,4)
• C(2,6,7,8,9)

Solution

Prof has identified the same problem dependencies:

• 1 → 4
• 2 → 6,7,8,9

When moving dependencies to a new relation, the keys of the new relation become foreign keys in the original relation.

italics is used for something but I’ma use it here for foreign keys. Looking back, that’s hard to see. 2 is a foreign key below.

A(1, 2, 3, 4, 5)

B(2, 6, 7, 8, 9)

There must be a reference statement for referential integrity

This is a reference statement:

A(2) mei B(2)

‘mei’ means ‘must exist in’

A is still not in second normal form, because of the relationship between 1 and 4.

In the new relation, C, we use 1 as the PK because it’s already a key in A.

Note because italics are hard: 1 and 2 are foreign keys.

• A(1, 2, 3, 5)
• B(2, 6, 7, 8, 9)
• C(1, 4)
• A(2) mei B(2)
• A(1) mei C(1)

I’m going to try and do the relation with html

A(1,2,3,5)

That actually turned out really well

Alright - turns out we aren’t done. Because 2 determines 6,7, and 6,7 determined 8,9, there is a transitive dependency in relation B.

We fix this by making a new dependency.

Here are the results, in glorious HTML markup:

A(1,2,3,5)

B(2,6,7)

C(1,4)

D(6,7,8,9)

Slides

Kinds of anomalies:

• Redundancy
  • Stuff stored too many times
• update
  • failure to update in all places
• delete
  • deleting too much
• insert
  • adding data about many themes

Second normal form requirements:

• First normal form
• All attributes are dependent on the entire primary key

BCNF

“A relation is in BCNF if all determinants are a candidate key”.

A good data model will require less normalization.

Notes from another example

• First Normal Form
  • idk how to describe this
• Second Normal Form
  • No partial dependencies
• Third Normal Form
  • No transitive dependencies
• Boyce-Codd Normal Form
  • All dependents are candidate or primary keys

Relation names must be in all capitals. Points will be lost because of this.

• underline
  • primary key
• dashed underline
  • foreign key