第六部分 关系数据库设计.ppt

Chapter 7: Relational Database Design
Chapter 7: Relational Database Design
First Normal Form
Pitfalls in Relational Database Design
Functional Dependencies
position
Boyce-Codd Normal Form
Third Normal Form
Multivalued Dependencies and Fourth Normal Form
Overall Database Design Process
First Normal Form
Domain is atomic if its elements are considered to be indivisible units
Examples of non-atomic domains:
Set of names, composite attributes
Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127
A relational schema R is in first normal form(第一范式,1NF) if the domains of all attributes of R are atomic
Pitfalls in Relational Database Design
Relational database design requires that we find a “good” collection of relation schemas. A bad design may lead to
Repetition of Information.
Inability to represent certain information.
Consider the relation schema: Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount)
Example
Redundancy(冗余):
Data for branch-name, branch-city, assets are repeated for each loan that a branch makes
Wastes space
Complicates updating, introducing possibility of inconsistency of assets value
Null values
Cannot store information about a branch if no loans exist
Can use null values, but they are difficult to handle.
position
pose the relation schema Lending-schema into:
Branch-schema = (branch-name, branch-city,assets)
Loan-info-schema = (customer-name, loan-number, branch-name, amount)
All attributes of an original schema (R) must appear in the position (R1, R2):
R = R1  R2
Lossless-join position(无损连接分解).For all possible relations r on schema R
r = R1 (r) R2 (r)
Example of Non Lossless-Join position
position of R = (A, B) R2 = (A) R2 = (B)
A
B



1
2
1
A


B
1
2
r
A(r)
B(r)
A (r) B (r)
A
B




1
2
1
2
Goal — Devise a Theory for the Following
Decide whether a particular relation R is in “good” form.
In the case that a relation R is not