Transcript Chapter 1: Introduction

Chapter 2: Introduction to Relational
Model
 Structure of Relational Databases
 Fundamental Relational-Algebra-Operations
Database System Concepts - 6th Edition
2.1
Example of the instructor Relation
attributes
(or columns)
tuples
(or rows)
Database System Concepts - 6th Edition
2.2
Attribute Types
 The term attribute (屬性) 一般指稱物件的特性, 或欲處理的資料.
 The set of allowed values for each attribute is called the domain of the
attribute

For example, the domain of ID = {1, 2, 3, 4}
 Attribute values are (normally) required to be atomic; that is, indivisible (分
割後沒有意義)

multivalued attribute values are not atomic (see page 1.17)
For example: the domain of author = {{Smith, Jones}, {Jones, Frick}}

composite attribute values are not atomic
For example: the domain of publisher = {(McGraw-Hill, New York),
(Oxford, London)}
 The special value null is a member of every domain, which signifies that the
value is unknown or does not exist.

The null value causes complications in the definition of many operations,
and will be discussed later.
Database System Concepts - 6th Edition
2.3
Relation Schema and Instance
 A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema
Example:
instructor = (ID, name, dept_name, salary)
 Formally, given sets D1, D2, …. Dn, a relation r is a subset of
D1 x D2 x … x Dn
Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai  Di
 Example:
D1 = {a, b, c}, D2 = {1, 2}, D1XD2 = {(a, 1), (a, 2), (b, 1), (b, 2), (c, 1), (c, 2)}
r = {(a, 1), (a, 2), (b, 2)}
 The current values of a relation is called the relation instance. A
relation (instance) is represented as a table.

A attribute refers to a column of a table.

An element t of r is a tuple, represented by a row in a table.
Database System Concepts - 6th Edition
2.4
Relations are Unordered
 Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
資料的排序方式屬physical level, 非logical level
寫query時不知資料如何排序;
 Example: instructor relation with unordered tuples
Database System Concepts - 6th Edition
2.5
Database
 A database consists of multiple relations
 Information about an enterprise is broken up into parts, where each
relation storing one part of the information
 The university database example:

instructor (ID, name, dept_name, salary)

department (dept_name, building, budget)

student (ID, name, dept_name, tot_cred)

course (course_id, title, dept_name, credits)

prereq (course_id, prereq_id)
 Bad design: (c.f. page 1.15)
univ (instructor_ID, name, dept_name, salary, student_Id, ..)
Normalization theory (Chapter 8) deals with how to design “good”
relational schemas.
Database System Concepts - 6th Edition
2.6
Keys
 Let K  R
 K is a superkey of R if values for K are sufficient to identify a unique tuple of
each possible relation r(R)

Example: {ID}, {name}, and {ID,dept_name} are all superkeys of
instructor. (see page 2.5)
SK
 Superkey K is a candidate key if K is minimal

“minimal” means that no subset of K is also a superkey.
Example: {ID} , {name} are both candidate keys for Instructor
CK
PK
 One of the candidate keys is selected to be the primary key.

which one? 通常依一般使用習慣,找最有代表性的.

Another example: {系別, 年級,班級,座號}, {學號}, {身分證字號} 都是
classmate表格的candidate key, 選 {學號}做為PK. (See the next slide).
 注意:

Key通常做為物件的代表性屬性

屬性值的唯一性會隨表格表示的資料不同而改變.,如下二頁的dept_name
Database System Concepts - 6th Edition
2.7
Example of Table classmate
學號
身分證字號
系別
年級
班級
座號
姓名
0995701
A123456780
CS
3
A
1
張三
0995711
A123456781
CS
3
B
1
李四
0985701
A123456782
CS
4
A
1
王五
0995601
A123456783
EE
3
A
1
趙六
Database System Concepts - 6th Edition
2.8
Foreign Keys
•Foreign key constraint: Value in one relation must appear in another
•Referencing relation: e.g., instructor
•Referenced relation: e.g., department
Will discuss this again in Chapter 3 and Chapter 4.
 department
 instructor
Database System Concepts - 6th Edition
2.9
Schema Diagram for University Database
注意：PK用底線, FK用箭頭
Database System Concepts - 6th Edition
2.10
Relational Query Languages
 Language in which user requests information from the database.
 Categories of languages

Procedural

non-procedural, or declarative
 “Pure” languages:

Relational algebra: procedural

Tuple (Domain) relational calculus: declarative
 “Algebra” is based on operators.

Example of arithmetic algebra: 1 + 5*3
 How to write a query

Determine which relations to use

Determine which operators to use
Database System Concepts - 6th Edition
2.11
Relational Algebra
 Relational operators

select: 

project: 

Natural join:

Cartesian product: x

union: 

Intersection: 

set difference: –
 The operators take one or two relations as inputs and produce a new
relation as a result.
Database System Concepts - 6th Edition
2.12
Selection of tuples
 Relation r
 Selection
σ 限制式(r)
 Select tuples with
A=B and D > 5

σ A=B ^ D > 5 (r)
Database System Concepts - 6th Edition
2.13
Selection of Columns (Attributes)
 Relation r:
 Projection
 屬性名稱 (r)
 Select columns A and C
  A, C
(r)
-> duplicates are removed
Database System Concepts - 6th Edition
2.14
More Examples
 Return those instructors whose salaries are more than 85000.
(see page 2.5, page2.6)
σ salary>=85000 (instructor)
 Output the attributes ID and Salary of instructors.
Π ID, salary (instructor)
 Find the ID and salary for those instructors
who have salary greater than $85000.
Π ID, salary (σ salary>=85000 (instructor))
<- composition
Database System Concepts - 6th Edition
2.15
Joining two relations – Cartesian Product
 Relations r, s:
 r x s:
 Example: instructor X department => 12*7 = 84 tuples
(see page 2.8)
Database System Concepts - 6th Edition
<-會希望同一列的dept_name要一樣!
2.16
Joining two relations – Natural Join
 Let r and s be relations on schemas R and S respectively.
Then, the “natural join” of relations r and s is a relation on
schema R  S obtained as follows:

Consider each pair of tuples tr from r and ts from s.

If tr and ts have the same value on each of the attributes
in R  S, add a tuple t to the result, where

t has the same value as tr on r

t has the same value as ts on s
 Example:
R = (A, B, C, D)
S = (E, B, D)

Result schema = (A, B, C, D, E)

r
s is defined as:
r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))
Database System Concepts - 6th Edition
2.17
Natural Join Example
 Relations r, s:
 Natural Join

r
s
Database System Concepts - 6th Edition
2.18
Example
 Instructor
department (c.f., page 2.8)
 Example: Output the attributes ID and building of instructors.

 ID, building (Instructor
Database System Concepts - 6th Edition
department)
2.19
Practice
 Find the titles of courses in the Comp. Sci. department
Answer:
 For all instructors who have taught courses, find their names and the
course ID of the courses they taught.
Answer:
Database System Concepts - 6th Edition
2.20
Union of two relations
 Relations r, s:
 r  s:
 Find the names of instructors and students.

name (instructor)
Database System Concepts - 6th Edition
  name (student)
2.21
Set difference of two relations
 Relations r, s:
 r – s:
 Find the departments which do not hire instructors.

dept _ name (department)
Database System Concepts - 6th Edition
–  dept _ name (instructor)
2.22
Set Intersection of two relations
 Relation r, s:
 rs
 Find the instructors who are also students.

name (instructor)
Database System Concepts - 6th Edition
  name (student)
2.23