文档介绍：Unit 10
Logical Design for
the Relational Model
11/10/2017
1
Unit 10
本單元目的
把 model independent design 結果的資料庫概念架構轉為關聯資料庫管理系統可以描述的關聯資料庫
11/10/2017
2
Unit 10
Outlines
An Introduction to the Relational Model
Schema Mapping from the ER model to the Relational Model
Elimination of external identifier
Elimination posite and multi-value attributes
Translation of entities
Translation of relationships
Mapping the operations to SQL
11/10/2017
3
Unit 10
The Relational Model
Schema : STUDENT(NAME, AGE, SEX)
column
attribute
Primary
key
tuple
Data instances
Degree of a relation means the number of columns.
Cardinality of a relation means the number of tuples.
11/10/2017
4
Unit 10
Mathematical definition for a relation
A domain is a collection of values.
D1={a,b} attribute A1 valued from D1
D2={c,d,e} attribute A2 valued from D2
Relation R
11/10/2017
5
Unit 10
Integrity rules
Entity integrity
No attribute participate in the primary key (PK) of a base relation is allowed to accept null values.
Referential integrity
If base relation R2 includes a foreign key (FK) matching the PK of some base relation R1, then every values of FK in R2 must either
be equal to the value of PK in some tuple of R1 or
be wholly null
R1 and R2 are not necessarily distinct.
PK
R1
R2
FK
11/10/2017
6
Unit 10
Example of a relational data base
STUDENT
EXAM
NAME
SEX
AGE
COURSE
CODE
INSTRUC
TOR
EXAM(COURSE_NUMBER, STUDENT_NAME, GRADE)
STUDENT(NAME, AGE, SEX)
COURSE(CODE, INSTRUCTOR)
CODE 和 COURSE_NUMBER 從同一個 domain 出來
STUDENT_NAME 和 NAME從同一個 domain 出來
GRADE
11/10/2017
7
Unit 10
Schema Mapping from the ER model to the Relational Model
Steps:
Elimination of external identifier
Elimination posite and multi-value attributes
Translation of entities
Translation of relationships
Mapping the operations to SQL
11/10/2017
8
Unit 10
Elimination of external identifier
E2 有 internal identifier
E1 的 primary key 是由一個屬於 E1 的 attribute 加上 E2 形成 identifier, 是為 externa