Relational Algebra and Calculus

1. Relational Algebra and Calculus University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management SIMS 257: Database Management -- Ray Larson

2. Physical Database Design RAID Data Integrity Review SIMS 257: Database Management -- Ray Larson

3. Physical Design Decisions • There are several critical decisions that will affect the integrity and performance of the system. • Storage Format • Physical record composition • Data arrangement • Indexes • Query optimization and performance tuning SIMS 257: Database Management -- Ray Larson

4. When to Index • Tradeoff between time and space: • Indexes permit faster processing for searching • But they take up space for the index • They also slow processing for insertions, deletions, and updates, because both the table and the index must be modified • Thus they SHOULD be used for databases where search is the main mode of interaction • The might be skipped if high rates of updating and insertions are expected SIMS 257: Database Management -- Ray Larson

5. When to Use Indexes • Rules of thumb • Indexes are most useful on larger tables • Specify a unique index for the primary key of each table • Indexes are most useful for attributes used as search criteria or for joining tables • Indexes are useful if sorting is often done on the attribute • Most useful when there are many different values for an attribute • Some DBMS limit the number of indexes and the size of the index key values • Some indexed will not retrieve NULL values SIMS 257: Database Management -- Ray Larson

6. One logical disk drive Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 2 3 4 Stripe 5 6 7 8 Stripe 9 10 11 12 Stripe * * * * * * * * * * * * Parallel Reads RAID Technology SIMS 257: Database Management -- Ray Larson

7. One logical disk drive Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 2 3 4 Stripe 5 6 7 8 Stripe 9 10 11 12 Stripe * * * * * * * * * * * * Parallel Reads Raid 0 SIMS 257: Database Management -- Ray Larson

8. RAID-1 Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 1 2 2 Stripe 3 3 4 4 Stripe 5 5 6 6 Stripe * * * * * * * * * * * * Parallel Reads SIMS 257: Database Management -- Ray Larson

9. RAID-2 Writes span all drives Disk 1 Disk 2 Disk 3 Disk 4 1a 1b ecc ecc Stripe 2a 2b ecc ecc Stripe 3a 3b ecc ecc Stripe * * * * * * * * * * * * Reads span all drives SIMS 257: Database Management -- Ray Larson

10. RAID-3 Writes span all drives Disk 1 Disk 2 Disk 3 Disk 4 1a 1b 1c ecc Stripe 2a 2b 2c ecc Stripe 3a 3b 3c ecc Stripe * * * * * * * * * * * * Reads span all drives SIMS 257: Database Management -- Ray Larson

11. Raid-4 Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 2 3 ecc Stripe 4 5 6 ecc Stripe 7 8 9 ecc Stripe * * * * * * * * * * * * Parallel Reads SIMS 257: Database Management -- Ray Larson

12. RAID-5 Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 2 3 4 Stripe 5 6 7 8 Stripe 9 10 11 12 Stripe * * * * * * * * ecc ecc ecc ecc Parallel Reads SIMS 257: Database Management -- Ray Larson

13. Integrity Constraints • The constraints we wish to impose in order to protect the database from becoming inconsistent. • Five types • Required data • attribute domain constraints • entity integrity • referential integrity • enterprise constraints SIMS 257: Database Management -- Ray Larson

14. SIMS 257: Database Management -- Ray Larson

15. Required Data • Some attributes must always contain a value -- they cannot have a null • For example: • Every employee must have a job title. • Every diveshop diveitem must have an order number and an item number. SIMS 257: Database Management -- Ray Larson

16. Attribute Domain Constraints • Every attribute has a domain, that is a set of values that are legal for it to use. • For example: • The domain of sex in the employee relation is “M” or “F” • Domain ranges can be used to validate input to the database. SIMS 257: Database Management -- Ray Larson

17. Entity Integrity • The primary key of any entity cannot be NULL. SIMS 257: Database Management -- Ray Larson

18. Referential Integrity • A “foreign key” links each occurrence in a relation representing a child entity to the occurrence of the parent entity containing the matching candidate key. • Referential Integrity means that if the foreign key contains a value, that value must refer to an existing occurrence in the parent entity. • For example: • Since the Order ID in the diveitem relation refers to a particular diveords item, that item must exist for referential integrity to be satisfied. SIMS 257: Database Management -- Ray Larson

19. Referential Integrity • Referential integrity options are declared when tables are defined (in most systems) • There are many issues having to do with how particular referential integrity constraints are to be implemented to deal with insertions and deletions of data from the parent and child tables. SIMS 257: Database Management -- Ray Larson

20. Insertion rules • A row should not be inserted in the referencing (child) table unless there already exists a matching entry in the referenced table. • Inserting into the parent table should not cause referential integrity problems. • Sometimes a special NULL value may be used to create child entries without a parent or with a “dummy” parent. SIMS 257: Database Management -- Ray Larson

21. Deletion rules • A row should not be deleted from the referenced table (parent) if there are matching rows in the referencing table (child). • Three ways to handle this • Restrict -- disallow the delete • Nullify -- reset the foreign keys in the child to some NULL or dummy value • Cascade -- Delete all rows in the child where there is a foreign key matching the key in the parent row being deleted SIMS 257: Database Management -- Ray Larson

22. Referential Integrity • This can be implemented using external programs that access the database • newer databases implement executable rules or built-in integrity constraints (e.g. Access) SIMS 257: Database Management -- Ray Larson

23. Enterprise Constraints • These are business rule that may affect the database and the data in it • for example, if a manager is only permitted to manage 10 employees then it would violate an enterprise constraint to manage more SIMS 257: Database Management -- Ray Larson

24. Today • Relational Operations • Relational Algebra • Relational Calculus • Introduction to SQL via Access SIMS 257: Database Management -- Ray Larson

25. Relational Algebra Operations • Select • Project • Product • Union • Intersect • Difference • Join • Divide SIMS 257: Database Management -- Ray Larson

26. Select • Extracts specified tuples (rows) from a specified relation (table). SIMS 257: Database Management -- Ray Larson

27. Project • Extracts specified attributes(columns) from a specified relation. SIMS 257: Database Management -- Ray Larson

28. Product • Builds a relation from two specified relations consisting of all possible concatenated pairs of tuples, one from each of the two relations. Product a a b b c c x y x y x y a b c x y SIMS 257: Database Management -- Ray Larson

29. Union • Builds a relation consisting of all tuples appearing in either or both of two specified relations. SIMS 257: Database Management -- Ray Larson

30. Intersect • Builds a relation consisting of all tuples appearing in both of two specified relations SIMS 257: Database Management -- Ray Larson

31. Difference • Builds a relation consisting of all tuples appearing in first relation but not the second. SIMS 257: Database Management -- Ray Larson

32. A1 B1 A2 B1 A3 B2 B1 C1 B2 C2 B3 C3 A1 B1 C1 A2 B1 C1 A3 B2 C2 Join • Builds a relation from two specified relations consisting of all possible concatenated pairs of, one from each of the two relations, such that in each pair the two tuples satisfy some condition. (Natural or Inner) Join SIMS 257: Database Management -- Ray Larson

33. B1 C1 B2 C2 B3 C3 Outer Join A1 B1 C1 A2 B1 C1 A3 B2 C2 A4 * * A1 B1 A2 B1 A3 B2 A4 B7 Outer Join • Outer Joins are similar to PRODUCT -- but will leave NULLs for any row in the first table with no corresponding rows in the second. SIMS 257: Database Management -- Ray Larson

34. a a a b c x y z x y Divide • Takes two relations, one binary and one unary, and builds a relation consisting of all values of one attribute of the binary relation that match (in the other attribute) all values in the unary relation. Divide a x y SIMS 257: Database Management -- Ray Larson

35. Emp# Employee Wage ISA Hourly Sales-Rep Sales Part# Writes Cust# Invoice# Quantity Contains Orders Contains Customer Invoice Line-Item Part Invoice# Rep# Part# Count Cust# Price ER Diagram: Acme Widget Co. SIMS 257: Database Management -- Ray Larson

36. Employee SIMS 257: Database Management -- Ray Larson

37. Part SIMS 257: Database Management -- Ray Larson

38. Sales-Rep Hourly SIMS 257: Database Management -- Ray Larson

39. Customer SIMS 257: Database Management -- Ray Larson

40. Invoice SIMS 257: Database Management -- Ray Larson

41. Line-Item SIMS 257: Database Management -- Ray Larson

42. Join Items SIMS 257: Database Management -- Ray Larson

43. Relational Algebra • What is the name of the customer who ordered Large Red Widgets? • Select “large Red Widgets” from Part as temp1 • Join temp1 with Line-item on Part # as temp2 • Join temp2 with Invoice on Invoice # as temp3 • Join temp3 with customer on cust # as temp4 • Project Name from temp4 SIMS 257: Database Management -- Ray Larson

44. Relational Calculus • Relational Algebra provides a set of explicit operations (select, project, join, etc) that can be used to build some desired relation from the database. • Relational Calculus provides a notation for formulating the definition of that desired relation in terms of the relations in the database without explicitly stating the operations to be performed • SQL is based on the relational calculus. SIMS 257: Database Management -- Ray Larson

45. SQL • Structured Query Language • Database Definition and Querying • Basic language is standardized across relational DBMSs. Each system may have proprietary extensions to standard. • Relational Calculus combines Select, Project and Join operations in a single command. SELECT. SIMS 257: Database Management -- Ray Larson

46. SELECT • Syntax: • SELECT attr1, attr2,…, attr3 FROM rel1 r1, rel2 r2,… rel3 r3 WHERE condition1 {AND | OR} condition2 ORDER BY attr1, attr3 • Examples in Access... SIMS 257: Database Management -- Ray Larson