Chapter 2: Introduction to Relational Model

1. Chapter 2: Introduction to Relational Model • Structure of Relational Databases • Fundamental Relational-Algebra-Operations

2. Example of the instructorRelation attributes (or columns) tuples (or rows)

3. Attribute Types • A relation is represented as a table. The term attribute (屬性)refers to a column of a table. • 屬性一般指稱物件的特性, 或欲處理的資料. • The set of allowed values for each attribute is called the domain of the attribute • Attribute values are (normally) required to be atomic; that is, indivisible(分割後沒有意義) • multivalued attribute values are not atomic(see page 1.17) For example: author = {{Smith, Jones}, {Jones, Frick}} • composite attribute values are not atomic For example: publisher = {(McGraw-Hill, New York), (Oxford, London)} • The special valuenull 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.

4. Relation Schema and Instance • A1, A2, …, Anare attributes • R = (A1, A2, …, An ) is a relation schema Example: instructor = (ID, name, dept_name, salary) • Formally, given sets D1, D2, …. Dn, a relationr is a subset of D1 x D2 x … x DnThus, 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)} • The current values (relation instance) of a relation are specified by a table • An element tofr is a tuple, represented by a rowin a table.

5. 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

6. 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.

7. Keys • Let K  R • K is a superkeyof 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) • Superkey K is a candidate key if K is minimalExample: {ID} , {name} are both candidate keys for Instructor • One of the candidate keys is selected to be the primary key. • which one? 通常依一般使用習慣,找最有代表性的. • Another example: {系別,年級,班級,座號}, {學號}, {身分證字號)都是同學的candidate key, 選 {學號}做為primary key. • 注意: • Key通常做為物件的代表性屬性 • 屬性值的唯一性會隨表格表示的資料不同而改變., 如下一頁的dept_name SK CK PK

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

9. Schema Diagram for University Database

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

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.

12. Selection of tuples • Relation r • Selection • σ 限制式(r) • Select tuples with A=B and D > 5 • σ A=B ^ D > 5 (r)

13. Selection of Columns (Attributes) • Relation r: • Projection •  屬性名稱 (r) • Select columns A and C • A, C (r) -> duplicates are removed

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

15. Joining two relations – Cartesian Product • Relations r, s: • r xs: • Example: instructor X department => 12*7 = 84 tuples • (see page 2.8) <-會希望同一列的dept_name要一樣!

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) • rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))

17. Natural Join • r s Natural Join Example • Relations r, s:

18. Example • Instructor department (c.f., page 2.8) • Example: Output the attributes ID and building of instructors. •  ID, building (Instructor department)

19. Union of two relations • Relations r, s: • r  s: • Find the names of instructors and students. • name (instructor) name (student)

20. Set difference of two relations • Relations r, s: • r – s: • Find the departments which do not hire instructors. • dept _ name (department)– dept _ name (instructor)

21. Set Intersection of two relations • Relation r, s: • r s • Find theinstructors who are also students. • name (instructor)name (student)