Chapter 2 Introduction to Relational Model n Structure

Chapter 2: Introduction to Relational Model n Structure of Relational Databases n Fundamental Relational-Algebra-Operations Database System Concepts - 6 th Edition 2. 1

Example of the instructor Relation attributes (or columns) tuples (or rows) Database System Concepts - 6 th Edition 2. 2

Attribute Types n The term attribute (屬性) 一般指稱物件的特性, 或欲處理的資料. n The set of allowed values for each attribute is called the domain of the attribute l For example, the domain of ID = {1, 2, 3, 4} n Attribute values are (normally) required to be atomic; that is, indivisible (分 割後沒有意義) l multivalued attribute values are not atomic (see page 1. 17) For example: the domain of author = {{Smith, Jones}, {Jones, Frick}} l composite attribute values are not atomic For example: the domain of publisher = {(Mc. Graw-Hill, New York), (Oxford, London)} n The special value null is a member of every domain, which signifies that the value is unknown or does not exist. l The null value causes complications in the definition of many operations, and will be discussed later. Database System Concepts - 6 th Edition 2. 3

Relation Schema and Instance n A 1, A 2, …, An are attributes n R = (A 1, A 2, …, An ) is a relation schema Example: instructor = (ID, name, dept_name, salary) n Formally, given sets D 1, D 2, …. Dn, a relation r is a subset of D 1 x D 2 x … x Dn Thus, a relation is a set of n-tuples (a 1, a 2, …, an) where each ai Di n Example: D 1 = {a, b, c}, D 2 = {1, 2}, D 1 XD 2 = {(a, 1), (a, 2), (b, 1), (b, 2), (c, 1), (c, 2)} r = {(a, 1), (a, 2), (b, 2)} A 1 A 2 n The current values of a relation is called the relation instance. a A relation (instance) is represented as a table. l An attribute refers to a column of a table. l An element t of r is a tuple, represented by a row in a table. Database System Concepts - 6 th Edition 2. 4 1 a 2 b 2

Relations are Unordered n Order of tuples is irrelevant (tuples may be stored in an arbitrary order) n資料的排序方式屬physical level, 非logical level n寫query時不知資料如何排序; n Example: instructor relation with unordered tuples Database System Concepts - 6 th Edition 2. 5

Database n A database consists of multiple relations n Information about an enterprise is broken up into parts, where each relation storing one part of the information n The university database example: l instructor (ID, name, dept_name, salary) l department (dept_name, building, budget) l student (ID, name, dept_name, tot_cred) l course (course_id, title, dept_name, credits) l prereq (course_id, prereq_id) n 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 - 6 th Edition 2. 6

Keys n Let K R n K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) l Example: {ID}, {name}, and {ID, dept_name} are all superkeys of instructor. (see page 2. 5) SK n Superkey K is a candidate key if K is minimal l “minimal” means that no subset of K is also a superkey. Example: {ID} , {name} are both candidate keys for Instructor CK PK n One of the candidate keys is selected to be the primary key. l which one? 通常依一般使用習慣, 找最有代表性的. l Another example: {系別, 年級, 班級, 座號}, {學號}, {身分證字號} 都是 classmate表格的candidate key, 選 {學號}做為PK. (See the next slide). n 注意: l Key通常做為物件的代表性屬性 l 屬性值的唯一性會隨表格表示的資料不同而改變. , 如下二頁的dept_name Database System Concepts - 6 th Edition 2. 7

Example of Table classmate 學號 身分證字號 系別 年級 班級 座號 姓名 0995701 A 123456780 CS 3 A 1 張三 0995711 A 123456781 CS 3 B 1 李四 0985701 A 123456782 CS 4 A 1 王五 0995601 A 123456783 EE 3 A 1 趙六 Database System Concepts - 6 th 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. n department n instructor Database System Concepts - 6 th Edition 2. 9

Schema Diagram for University Database 注意：PK用底線, FK用箭頭 Database System Concepts - 6 th Edition 2. 10

Relational Query Languages n Language in which user requests information from the database. n Categories of languages l Procedural l non-procedural, or declarative n “Pure” languages: l Relational algebra: procedural l Tuple (Domain) relational calculus: declarative n “Algebra” is based on operators. l Example of arithmetic algebra: 1 + 5*3 n How to write a query l Determine which relations to use l Determine which operators to use Database System Concepts - 6 th Edition 2. 11

Relational Algebra n Relational operators l select: l project: l Natural join: l Cartesian product: x l union: l Intersection: l set difference: – n The operators take one or two relations as inputs and produce a new relation as a result. Database System Concepts - 6 th Edition 2. 12

Selection of tuples n Relation r n Selection σ 限制式(r) l 兩個屬性值相比， 或屬性值和常數比 l 多個比較式用and或 or連接起來 n Select tuples with A=B and D > 5 l σ A=B ^ D > 5 (r) Database System Concepts - 6 th Edition 2. 13

Selection of Columns (Attributes) n Relation r: n Projection 屬性名稱 (r) n Select columns A and C l A, C (r) -> duplicates are removed Database System Concepts - 6 th Edition 2. 14

More Examples n Return those instructors whose salaries are more than 85000. page 2. 5, page 2. 6) σ salary>=85000 (instructor) n Output the attributes ID and Salary of instructors. Π ID, salary (instructor) n Find the ID and salary for those instructors who have salary greater than $85000. Π ID, salary (σ salary>=85000 (instructor)) <- composition Database System Concepts - 6 th Edition 2. 15 (see

Joining two relations – Cartesian Product n Relations r, s: n r x s: n Example: instructor X department => 12*7 = 84 tuples (see page 2. ９) Database System Concepts - 6 th Edition <-會希望同一列的dept_name要一樣! 2. 16

Joining two relations – Natural Join n 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: l Consider each pair of tuples tr from r and ts from s. l If tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, where 4 t has the same value as tr on r 4 t has the same value as ts on s n Example: R = (A, B, C, D) S = (E, B, D) l Result schema = (A, B, C, D, E) l 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 - 6 th Edition 2. 17

Natural Join Example n Relations r, s: n Natural Join l r s Database System Concepts - 6 th Edition 2. 18

Example n Instructor department (c. f. , page 2. 8) n Example: Output the attributes ID and building of instructors. l ID, building (Instructor Database System Concepts - 6 th Edition department) 2. 19

Practice n Find the titles of courses in the Comp. Sci. department Answer: n For all instructors who have taught courses, find their names and the course ID of the courses they taught. Answer: Database System Concepts - 6 th Edition 2. 20

Union of two relations n Relations r, s: n r s: n Find the names of instructors and students. name (instructor) Database System Concepts - 6 th Edition name (student) 2. 21

Set difference of two relations n Relations r, s: n r – s: n Find the departments which do not hire instructors. dept _ name (department) Database System Concepts - 6 th Edition – dept _ name (instructor) 2. 22

Set Intersection of two relations n Relation r, s: n r s n Find the instructors who are also students. name (instructor) Database System Concepts - 6 th Edition name (student) 2. 23