清华大学数据库原理第七章(1)分析解析
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库原理
Chapter 7 Relational Database Design
王建民 清华大学软件学院 2003年/秋
Relational-database Design
The goal of relational-database design is to generate a set of relation schemas to store information without unnecessary redundancy and to retrieve information easily Relation Schema
– Set of accounts stored with each customer, and set of
owners stored with each account
We assume all relations are in first normal form
6
Main Contents
– CS0012 or EE1127 Students roll numbers – If the first two characters are extracted to find the
department, the domain of roll numbers is not atomic – Doing so is a bad idea
leads to encoding of information in application program rather than in the database
5
Non-atomic Values
Non-atomic values complicate storage and encourage redundant (repeated) storage of data
First Normal Form Pitfalls in Relational Database Design Functional Dependencies Decomposition
7
Pitfalls in RDD
Relational database design requires that we find a “good” collection of relation schemas A bad design may lead to
Cannot store information about a branch if no loans exist
– Can use null values, but they are difficult to handle
10
Decomposition
Decompose the relation schema Lending-schema into
Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount)
11
Design Goals
wk.baidu.com
Avoid redundant data Ensure that relationships among attributes are represented Facilitate the checking of modifications for violation of database integrity constraints
A relational schema R is in first normal form(第一范式) if the domains of all attributes of R are atomic
4
Atomicity
Atomicity is actually a property of how the elements of the domain are used Strings would normally be considered indivisible
3
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 Identification numbers like CS101 that can be broken up into parts
– Repetition of Information
– Inability to represent certain information
8
Example of Bad Design
Consider the relation schema
Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount)
R (A, D, dom, F)
Normal form(范式) is a criteria(准则) for us to judge whether the relation schemas are “good”?
2
Main Contents
First Normal Form Pitfalls in Relational Database Design Functional Dependencies Decomposition
9
Problems in Lending-schema
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
Chapter 7 Relational Database Design
王建民 清华大学软件学院 2003年/秋
Relational-database Design
The goal of relational-database design is to generate a set of relation schemas to store information without unnecessary redundancy and to retrieve information easily Relation Schema
– Set of accounts stored with each customer, and set of
owners stored with each account
We assume all relations are in first normal form
6
Main Contents
– CS0012 or EE1127 Students roll numbers – If the first two characters are extracted to find the
department, the domain of roll numbers is not atomic – Doing so is a bad idea
leads to encoding of information in application program rather than in the database
5
Non-atomic Values
Non-atomic values complicate storage and encourage redundant (repeated) storage of data
First Normal Form Pitfalls in Relational Database Design Functional Dependencies Decomposition
7
Pitfalls in RDD
Relational database design requires that we find a “good” collection of relation schemas A bad design may lead to
Cannot store information about a branch if no loans exist
– Can use null values, but they are difficult to handle
10
Decomposition
Decompose the relation schema Lending-schema into
Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount)
11
Design Goals
wk.baidu.com
Avoid redundant data Ensure that relationships among attributes are represented Facilitate the checking of modifications for violation of database integrity constraints
A relational schema R is in first normal form(第一范式) if the domains of all attributes of R are atomic
4
Atomicity
Atomicity is actually a property of how the elements of the domain are used Strings would normally be considered indivisible
3
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 Identification numbers like CS101 that can be broken up into parts
– Repetition of Information
– Inability to represent certain information
8
Example of Bad Design
Consider the relation schema
Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount)
R (A, D, dom, F)
Normal form(范式) is a criteria(准则) for us to judge whether the relation schemas are “good”?
2
Main Contents
First Normal Form Pitfalls in Relational Database Design Functional Dependencies Decomposition
9
Problems in Lending-schema
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