Lec 7 Normalization 1

7/30/2019 Lec 7 Normalization 1

1/26

Database

Lecture NotesNormalization 1 Functional

Dependencies

Dr. Meg Murray

[email protected]


2/26

How Many Tables?

Should we store these two tables as they are, or should we combine them

into one table in our new database?


3/26

Important Relational Model Terms

Entity

Relation

Functional Dependency

Determinant

Candidate Key Composite Key

Primary Key

Surrogate Key

Foreign Key Referential integrity constraint Normal Form

Multivalued Dependency


4/26

Relation

Relational DBMS products store data about entities in relations,which are a special type of table

A relation is a two-dimensional table that has the followingcharacteristics:

Rows contain data about an entity

Columns contain data about attributes of the entity

All entries in a column are of the same kind

Each column has a unique name

Cells of the table hold a single value

The order of the columns is unimportant

The order of the rows is unimportant No two rows may be identical

KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)

2008 Pearson Prentice Hall


5/26

A Relation

A relation is a two-dimensional table that has the following characteristics:Rows contain data about an entityColumns contain data about attributes of the entity

All entries in a column are of the same kindEach column has a unique nameCells of the table hold a single valueThe order of the columns is unimportantThe order of the rows is unimportantNo two rows may be identical




6/26

Is this a Relation?

A relation is a two-dimensional table that has the following characteristics:

Rows contain data about an entityColumns contain data about attributes of the entityAll entries in a column are of the same kindEach column has a unique nameCells of the table hold a single valueThe order of the columns is unimportantThe order of the rows is unimportantNo two rows may be identical


7/26

Is this a Relation?

A relation is a two-dimensional table that has the following characteristics:Rows contain data about an entityColumns contain data about attributes of the entity

All entries in a column are of the same kindEach column has a unique nameCells of the table hold a single valueThe order of the columns is unimportantThe order of the rows is unimportantNo two rows may be identical

EmployeeNumber Phone LastName

100 335-6421,

454-9744

Abernathy

101 215-7789 Cadley

104 610-9850 Copley

107 299-9090 Jackson


8/26

Functional Dependencies


9/26


A functional dependency occurs whenthe value of one (a set of) attribute(s)

determines the value of a second (set of)

attribute(s) in the same table:

StudentID StudentName

StudentID (DormName, DormRoom, Fee)

KROENKE and AUER - DATABASE CONCEPTS (3rd

Edition) 2008 Pearson Prentice Hall


10/26


Functional dependencies may be based onequations:

ExtendedPrice = Quantity X UnitPrice

(Quantity, UnitPrice) ExtendedPrice

Illustration The price of one cookie can determine the price of a

box of 12 cookies

But functional dependencies are not equations!

(CookiePrice, Qty) BoxPrice

KROENKE and AUER - DATABASE CONCEPTS (3

rd



11/26

Determinants

The attribute (or attributes) that we use as

the starting point (the variable on the left

side of the equation) is called a

determinant

(CookiePrice, Qty) BoxPrice

Determinant


rd



12/26


ObjectColor WeightObjectColor Shape

ObjectColor (Weight, Shape)


rd



13/26


14/26

Composite Determinants

Composite determinant: A determinantof a functional dependency that consists of

more than one attribute

(StudentName, ClassName) (Grade)




15/26

Candidate/Primary Keys and


By definition

A candidate key of a relation will

functionally determine all other attributes

in the row

Likewise, by definition

A primary key of a relation will functionally

determine all other attributes in the row




16/26


17/26


Another way to think about it: Functional Dependencies = a single-valued fact

Is Y a fact related to X?

Is SKU_Description related to SKU

Or Does X determine Y?

Does SKU determine SKU_Description




18/26

Functional Dependencies in the

SKU_DATA Table

Y is "functionally dependent" on X if it is invalid to have two

records with the same X-value but different Y-values. An X-value must always occur with the same Y-value.




19/26


SKU_DATA Table

SKU (SKU_Description, Department, Buyer)

SKU_Description (SKU, Department, Buyer)

Buyer Department




20/26


ORDER_ITEM Table




21/26


ORDER_ITEM Table

(OrderNumber, SKU)

(Quantity, Price, ExtendedPrice)

(Quantity, Price) (ExtendedPrice)




22/26

How to Discover Determinants

Often business rules

We have been discovering determinants

from a table of attributes

Often the Functional Dependencies are

assessed before a Table is constructed


23/26

More on FDs and Keys

A candidate key is a key that determines all ofthe other columns in a relation

It may be a determinant key

However NOT all determinant keys are candidate keys

SKU (SKU_Description, Department, Buyer)

SKU_Description (SKU, Department, Buyer)

Buyer Department

Which are candidate keys? Which is a

determinate key but not a candidate key?




24/26

So

When X is a key, then all fields are by definition

functionally dependent on X in a trivial way, since there

can't be two records having the same X value.

3-24


25/26


26/26

In- class

http://adbc.kennesaw.edu

Database Design

http://adbc.kennesaw.edu/http://adbc.kennesaw.edu/

Documents

Lec 7 Normalization 1