Chapter 3

1. Chapter 3 An Introduction to Relational Databases

2. Definitions: A database is a collection of persistent data that is used by the application systems of some given enterprise. A relational database, or relational DBMS, is one that follows the relational model.

3. The relational model of data is one in which: 1) The data in the database is perceived by the user as tables (and nothing but tables). 2) The tables satisfy certain integrity constraints. 3) The operators available for manipulating the data (e.g., retrieval) are ones that derive tables from tables. Structure, Integrity, Manipulation

4. The Relational Model–Informally–Its Three Parts • Structure • Data is perceived by users as tables • Integrity • Data subject to specific integrity requirements • Manipulation • Operators derive tables from other tables • Restrict • Project • Join

5. Manipulation Includes at least: Restrict (or Select–same thing) Project Join

6. The Relational Model–Operators, Informally • Restrict • Extracts specified rows • Project • Extracts specified columns • Join • Combines two tables into one • Based on common values in a common column

7. Fig. 3.1 The departments-and-employees database (sample values)

8. Fig. 3.2 SELECT, PROJECT, AND JOIN (examples)

9. row subset column subset Restrict: Extracts specified rows (horizontal subset). Project: Extracts specified columns (vertical subset). Select and Project

10. omit duplicate Join: Combines two tables into one –based on common values in a common column. Join

11. The Relational Model–Set Property • Tables are sets of rows. • Rows are sets of columns. • All operations are “set-at-a-time.” • No “row at a time” processing

12. The Relational Model–Closure Property Operands are tables, results are tables. For example: Select a subset of rows (a table). Then project a subset of columns of those rows (a table) The “output” of select is an “input” to project.

13. The Relational Model–Materialization, • Intermediate results don’t have to be “materialized.” • Materialized evaluation of operators • Generates tables for all steps • Pipelined evaluation of operators • Piecemeal intermediate steps

14. The Relational Model–Logical/Physical, Informally • Data is perceived by the user as tables • DBMS can store the data on disk in other formats • Sequential files, indexes, pointer chains, hashing • The Information Principle: Information represented by rows and columns, only • No user-detected pointers • Tables are joined logically based on user understood column values

15. The Information Principle: The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in rows in tables. ·This is the only method available. ·There are no pointers.

16. The Relational Model–Integrity, Informally • Every table has a “primary key” • Column whose value implies values in the other columns • Some tables have a “foreign key” • References primary key of another table • Used to maintain links between tables • Column whose value implies values in columns in another table

17. The Relational Model–More Formally • Why “relations”? • Why “relational theory” • Codd, “the paper,” System R • Relations vs. tables, records, etc.

18. Relations • Relation is a mathematical term • A table is a relation, mathematically speaking • Relations have tuples or rows, not records • Relations have attributes or columns, not fields • Codd was the first to promulgate this • “In Codd we trust”

20. The Relational Model–More Formally • An open-ended collection of scalar types • A relation type generator • Facilities to define relation variables in generated types • A relation assignment operator to assign values to relation variables • An open-ended set of relational operators used to derive relation values from other relation values

21. Relations and Relvars • Relation is a mathematical term • A relation is inherently a specific set of values • A relation variable, or relvar, is the structure into which values are set • Relvars can have different values at different times • Most writers use “relation” (or “table’) to mean both the structure and the instantiated values • “But not from this Date forward”

22. Relations and Relvars The relation/table EMP from the departments-and-employees database: +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | E1 | Lopez | D1 | 40000 | | E2 | Cheng | D1 | 42000 | | E3 | Finzi | D2 | 30000 | | E4 | Saito | D2 | 35000 | +-------+-------+--------+--------+ EMP

23. Relations and Relvars +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | | | | || | | | | | | | | || | | | | +-------+-------+--------+--------+ EMP The relation variable EMP (relvar) +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | E1 | Lopez | D1 | 40000 | | E2 | Cheng | D1 | 42000 | | E3 | Finzi | D2 | 30000 | | E4 | Saito | D2 | 35000 | +-------+-------+--------+--------+ EMP The relation value of EMP (relation)

24. Relations and Relvars +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | E1 | Lopez | D1 | 40000 | | E2 | Cheng | D1 | 42000 | | E3 | Finzi | D2 | 30000 | | E4 | Saito | D2 | 35000 | +-------+-------+--------+--------+ EMP relation variable EMP has this value DELETE EMP WHERE EMP# = EMP# (‘E4’) ; +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | E1 | Lopez | D1 | 40000 | | E2 | Cheng | D1 | 42000 | | E3 | Finzi | D2 | 30000 | +-------+-------+--------+--------+ EMP relation variable EMP now has this value

25. The relational operation: DELETE EMP WHERE EMP# = EMP# (‘E4’) ; is really just shorthand for the relational assignment: EMP := EMP WHERE NOT ( ( EMP# = EMP# (‘E4’) ); and similarly, INSERT and UPDATE also involve assigning a new value to a relvar.

26. DELETE EMP WHERE EMP# = EMP# (‘E4’) ; EMP := EMP WHERE NOT ( ( EMP# = EMP# (‘E4’) ); Both of the above are statements in “Tutorial D”

27. Tutorial D • A language for describing/defining the relational model and its behaviors • “Pascal-like” ( “Java-like”) • “Self-explanatory” • Free of the restrictions/implications of SQL

28. Formally, What Relations Mean:Relations vs. Types • Relational model includes an open-ended set of types • i.e. users can define their own types • A type can be regarded as the set of all its possible instances • e.g. Emp# as a type is the set of all possible employee numbers

29. What is meant by “type”?

30. The type of a data object determines: 1) What values is can assume. 2) What operations can be performed on it.

31. Formally, What Relations Mean:Types and their Predicates • Every relation–that is to say every relation value–is divided into two parts: head and body • Head has name and type for the column • Body has rows that conform to the head • e.g. Emp# is the name of the column, and could also be its type, if we have defined such a type; otherwise the type could be NUM

32. heading body Fig. 3.4 Sample EMP relation value, with column types shown

33. Formally, What Relations Mean:Types and their Predicates (continued) • For any relation, the head denotes a predicate • A predicate is a truth-valued function that can take (as any function can) parameters • For any relation, each row of the body denotes a true proposition • A true proposition is obtained from the predicate by instantiating it (sending in arguments in place of the parameters)

34. Formally, What Relations Mean:Types and their Predicates (continued) • Predicate example: • Employee EMP# is named ENAME, works in department DEPT#, and earns salary SALARY • EMP#, ENAME, DEPT#, and SALARY are parameters as well as table column headings • True proposition example: • Employee E1 is named Lopez, works in department D1, and earns salary 40k • E1, Lopez, D1, and 40k are arguments as well as table atomic values

35. Formally, What Relations Mean:Types and their Predicates (continued) • Types are sets of things we can talk about • Relations are sets of things we say about the things we can talk about • A relvar is a predicate • A relation is a set of true propositions

36. Optimization • Relational operators are set operators • Relational languages are less procedural than procedural languages • Relational languages function at a higher level of abstraction than do procedural languages • Relational Database Management implementations require an optimizer • Optimizer handles the “how” after the user specifies the “what result”

37. Automatic Navigation Consequence of the non-procedural nature of relational systems–user specifies what, DBMS determines how Example of an SQL operation compared to the equivalent code in a particular network database

38. “automatic” “manual” Fig. 3.5 Automatic vs. manual navigation

39. The Catalog • System catalog is required to keep track of all database objects • Can be thought of as a dictionary • Implemented in relvars (known to the DBMS as tables) that can be queried

40. Fig. 3.6 Catalog for the departments and employees database (in outline)

41. Base Relvars and Views • Base relvars • Created in SQL via CREATE TABLE • Views can be derived from base relvars • Created in SQL via CREATE VIEW • View relvars are stored in the catalog • View values do not exist separately • View values are whatever populates the base relation at the time the user queries the view • The user perceives the view as a real relation

42. Example: CREATE VIEW TOPEMP AS (EMP WHERE SALARY > 33000 ) { EMPNO, ENAME, SALARY} row subset column subset +-------+-------+--------+--------+ | EMPNO | ENAME | DEPTNO | SALARY | +-------+-------+--------+--------+ | E1 | Lopez | D1 | 40000 | | E2 | Cheng | D1 | 42000 | | E3 | Finzi | D2 | 30000 | | E4 | Saito | D2 | 35000 | +-------+-------+--------+--------+ EMP +-------+-------+--------+ | EMPNO | ENAME | SALARY | +-------+-------+--------+ | E1 | Lopez | 40000 | | E2 | Cheng | 42000 | | E4 | Saito | 35000 | +-------+-------+--------+ TOPEMP doesn’t really exist

43. Base Relvars and Views • Base relvars • Declared, named, stored • Views • Declared, named, not stored • Both base relvars(tables) and views are in the catalog (their metadata). • Base relations “really exist,” views do not.

44. Transactions • A transaction is a logical unit of work • May encompass one or many operations • SQL uses BEGIN TRANSACTION, COMMIT, and ROLLBACK to support transactions • Transactions are atomic, durable, isolated, and serializable • More detail on this to come (ch 15-16)

45. Transactions:  Example: move money from account A to account B BEGIN TRANSACTION; UPDATE account A; (take money out) UPDATE account B; (put money in) IF everything worked fine THEN COMMIT; ELSE ROLLBACK; ENDIF;

46. atomic the “all or nothing” principle—even if failure occurs during execution durable once COMMITed, guaranteed to be applied to the database (results become persistent) isolated effect of a transaction not seen by other transactions until COMMITed serializable a set of transactions can be executed concurrently (therefore in any order)

47. S +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ SP +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P1 | 300 | | S1 | P2 | 200 | | S1 | P3 | 400 | | S1 | P4 | 200 | | S1 | P5 | 100 | | S1 | P6 | 100 | | S2 | P1 | 300 | | S2 | P2 | 400 | | S3 | P2 | 200 | | S4 | P2 | 200 | | S4 | P4 | 300 | | S4 | P5 | 400 | +------+------+------+ P +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ The suppliers and parts database (sample values)

48. Relationship Parts Suppliers supply Entity Entity snum sname status city pnum pname color weight city which supplier supplies which parts and how many? need data about the relationship (shipments)

49. primary key foreign keys +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P1 | 300 | | S1 | P2 | 200 | | S1 | P3 | 400 | | S1 | P4 | 200 | | S1 | P5 | 100 | | S1 | P6 | 100 | | S2 | P1 | 300 | | S2 | P2 | 400 | | S3 | P2 | 200 | | S4 | P2 | 200 | | S4 | P4 | 300 | | S4 | P5 | 400 | +------+------+------+ primary key S entity SP primary key +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ P relationship entity Tables for entities, tables for relationships

50. Figure 3.9 The suppliers and parts database (data definition) (in Tutorial D)