Chapter 2

1. Chapter 2 Adapted from Silberschatz, et al. CHECK SLIDE 16

2. Chapter 2: • Introduction to databases • Terminology • Basic concepts

3. Example of a Relation attributes (or columns) tuples (or rows)

4. Attribute Types • 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 • The special valuenull is a member of every domain • The null value causes complications in the definition of many operations

5. Relation Schema and Instance • A1, A2, …, Anare attributes • R= (A1, A2, …, An ) is a relation schema instructor = (ID (as A1), name (as A2), … dept_name, salary)

6. Relations are Unordered • Order of tuples is irrelevant (tuples may be stored in an arbitrary order) • Example: instructor relation with unordered tuples

7. Database • A database consists of multiple relations • Information about an enterprise is broken up into parts instructorstudentadvisor • Why is it a bad design to define as one relation?univ(instructor -ID, name, dept_name, salary, student_Id, advisor, ..)

8. Database Bad design: univ(instructor -ID, name, dept_name, salary, student_Id, advisor ..) • Duplicate information (two students have the same instructor) • The need for null values (representing a student with no advisor) • Makes it easy to create data inconsistency problems. (How would that occur?)

9. Schema Diagram for University Database

10. Keys • You have used keys before – e.g. sorting, hash tables • Identify key (attributes) to uniquely identify specific tuples • See next

11. Keys • Let K  R (The key(s) K must be a subset of the attributes) • K is a primary keyof R if values for K are sufficient to identify a unique tuple of each possible relation r(R) • Example: {ID} and {ID,name} are both primary keys of instructor. • Foreign key constraint: Value in one relation must appear in another • Referencing relation • Referenced relation

12. Relational Notation • Chapter 2 introduces relational algebra notation (more in Chapter 6)

13. Selection of tuples • Relation r • Select tuples with A=B and D > 5 • σ A=B and D > 5 (r) Use sigma to denote selection; (r) indicates the relation

14. Selection of Columns (Attributes) • Relation r: • Select A and C • Projection • ΠA, C (r) Projection: output specified attributes; remove duplicates

15. Joining two relations – Cartesian Product (“join” later) • Relations r, s: • r xs: Cartesian product combines tuples from 2 relations; result contains all pairs of tuples, regardless of whether attribute values match α1 with each instance of C/D/E Same for β2

16. Union of two relations • Relations r, s: • r  s: Relations are sets so we can perform standard set operations. Must be “similarly structured” tables.

17. Set difference of two relations • Relations r, s: • r – s: What is in relation r that is not in relation s?

18. Set Intersection of two relations • Relation r, s: • r s What’s in r and also in s?

19. Joining two relations – Natural Join • Let r and s be relations on schemas R and S respectively. • 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

20. Natural Join • r s Natural Join Example • Relations r, s: Attributes B and D are common to both relations. Result of the natural join is all tuples merged based on these attributes (default)

21. Relations • Sometimes helps see how to structure the SQL queries if you write out the relation first • These (given in Chapter 2) are simple uses and not exhaustive • More later in the course

23. End of Chapter 2

24. Figure 2.01

25. Figure 2.02

26. Figure 2.03

27. Figure 2.04

28. Figure 2.05

29. Figure 2.06

30. Figure 2.07

31. Figure 2.10

32. Figure 2.11

33. Figure 2.12

34. Figure 2.13