数据库系统概论 -范式课件

Database Systems

--Unt6. the Relational Theorem •苏向阳

6. the Relational Theorem

知识点5 Normalization Based

on FD

☞A relational schema R is in first normal form(1NF)if the domains of all attributes of R are atomic.

NO composite attributes, such as:

customer( customer-id, name(first-name, middle-initial, last-

name), date-of-birth )

Each attribute as an unit, even they have several part that have individual information.

A tuple has only one value at each attribute.

☞A schema R not in 1NF, then it’s NOT a relational schema. ☞A relation R is in 1NF is not ‘good’ enough.

For relation:

Employee( emp_id, emp_name, emp_phone, dept_name,

dept_phone, dept_mgrname, skill_id, skill_name, skill_date, skill_lvl)

➢Is in 1NF

➢Has Insert Anomaly, Delete Anomaly, Update Anomaly

and Data Redundancy .

☞A relational schema R is in second normal form (2NF) if there are NO non-prime attributes dependent on Candidate Key partially.

☞Example：R(A, B,C,D), F = {AB →C, AC →BD} Candidate Key : AB, AC

non-prime attributes: D

AB →D, AC→D are all fully dependency

R∈2NF

F c = { emp_id→(emp_name, epm_phone, dept_name),

dept_name→( dept_phone, dept_mgrname) ,

skill_id→skill_name,

(emp_id, skill_id, skill_date)→skill_lvl}

emp_info( emp_id, emp_name, epm_phone, dept_name, dept_phone, dept_mgrname, skill_id, skill_name, skill_date,

skill_lvl)

F c = { emp_id→(emp_name, epm_phone, dept_name),

dept_name→( dept_phone, dept_mgrname) ,

skill_id→skill_name,

(emp_id, skill_id, skill_date)→skill_lvl}

emp_info ∈2NF CK: (emp_id, skill_id, skill_date)

(emp_id, skill_id, skill_date) →emp_name is partially.

Decomposition(模式分解):

⚫emp(emp_id, emp_name, epm_phone, dept_name,

dept_phone, dept_mgrname)

F c = {emp_id→(emp_name, epm_phone, dept_name),

dept_name→( dept_phone, dept_mgrname) }∈2NF

⚫skill ( skill_id, skill_name)

F c = {skill_id→skill_name}∈2NF

Decomposition(模式分解):

⚫emp_skill( emp_id, skill_id, skill_date, skill_lvl)

F c = { (emp_id, skill_id, skill_date)→skill_lvl}∈2NF

☞A relation R is in 2NF is not ‘good’ enough.

⚫emp(emp_id, emp_name, epm_phone, dept_name,

dept_phone, dept_mgrname)

F c = {emp_id→(emp_name, epm_phone, dept_name),

dept_name→( dept_phone, dept_mgrname) }∈2NF ➢Has Insert Anomaly, Delete Anomaly, Update Anomaly

and Data Redundancy .

☞A relational schema R is in third normal form (3NF) if there are no nonprime attributes which transitively dependent on

a key for R.

☞Example：R(A, B, C, D), F = {AB →C, C →D}

Candidate Key : AB

non-prime attributes: C, D

D is transitively dependent on Key AB by attributes C.

R∈3NF