Understanding Relational Database Concepts

1. Ch. 5? Each attribute implies a domain (data type, set of values) Types do not imply physical representation (encap…) But at least one possible physical representation is suggested Specific data types are orthogonal to the relational model. Scalar types have visible components (non-scalars don’t). The relational model is strongly typed. Some operators ( selector = < > := CAST AS ) Domains, Relations & Base RelVars (Ch. 5 Overview)Relational Algebra & Calculus,, & SQL (Ch. 6,7)Preview – Design Issues & Integrity Constraints (Ch.8)A Design Review

2. Relations (relation values) A table is a concrete representation of a relation. Tuple – a set of ordered pairs, no duplicates, unordered. Relations have cardinality & degree. RelVars & Views define relations The system catalog maintains the relvars. SQL is used to create RelVars – CREATE DOMAIN, CREATE TABLE Relations – INSERT, DELETE, UPDATE (tuples) Relations & RelVars

3. J.CITY = P.CITY JNAME | | PNAME QTY * 100 QTY + 100 STATUS = 5 J.CITY < S.CITY COLOR = P.CITY J.CITY = P.CITY | | ‘burg’ Type Checking

4. Operates on relations, gives relations as results Restrict (Select)  -- subset of tuples Project  -- subset of columns Product -- all possible combinations of two tuples Relational Algebra A X A Y B X B Y A B X Y (Natural) Join: connecting tuples based on common value in same-named columns A1 B1 A2 B1 A3 B2 B1 C1 B2 C2 B3 C3 A1 B1 C1 A2 B1 C1 A3 B2 C2

5. Other Joins • Theta Join -- join two relations • but not on the equality operation • ((S RENAME CITY AS SCITY) TIMES • (P RENAME CITY AS PCITY) ) • WHERE SCITY > PCITY • Semi Join -- (natural) join two relations, • but return only attributes of the first • ((S SEMIJOIN (SP WHERE P# = # (‘P2’))

6. rings_at(inv# ext) 12 1118 13 1118 13 1120 14 3400 14 3443 15 3443 16 3439 Standard Set Operations Union, Intersection, Difference treat relations as sets of tuples • old_ext(inv# ext) • 13 1118 • 13 1119 • 14 3443 • 16 3439 (inv# ext) (inv# ext) (inv# ext)

7. Given 2 unary and 1 binary relation -- find tuples in the (first) unary relation matched in the binary relation -- w/ tuples in the other unary relation Division A X A Y A Z B X C Y A B C X Z A

8. Division = (person) Randall Ross  r(ext) 2116 2118 • phone_bk(person ext) • Randall 2116 • Ross 2116 • Ricard 2116 • Randall 2218 • Ross 2218 • Rivers 2218 • Ross 3972 • Ricard 3972 • Rivers 3972 •  s(person) • Ross • Ricard = (ext) 2116 3972 Analogy a  b is the largest q such that b * q  a

9. Associative & Commutative -- UNION, INTERSECTION, TIMES, JOIN ((SP JOIN S) WHERE P# = P# (‘P2’)) {SNAME} (((P WHERE COLOR = COLOR (‘Red’)) JOIN SP) {S#} JOIN S) {SNAME} ((S {S#} DIVIDEBY P {P#} PER SP {S#, P#}) JOIN S {SNAME} S{S#} DIVIDEBY (SP WHERE S# = S# (‘S2’)) {P#} PER SP {S#,P#} (((S RENAME S# AS SA) {SA, CITY} JOIN (S RENAME S# AS SB) {SB, CITY} ) WHERE SA < SB) {SA,SB} Properties & Examples

10. Retrieval Update Integrity constraints Derived relvars (views) Stability requirements (concurrency control) Security constraints (scope of authorization) Transformation rules -> OPTIMIZATION ((SP JOIN S) WHERE P# = P# (‘P2’)) {SNAME} ((SP WHERE P# = P# (‘P2’)) JOIN S) {SNAME} the Algebra enables writing relational expressions

11. Aggregates --COUNT, SUM, AVG, MAX, MIN, ALL, ANY SUMMARIZE SP PER SP {P#} ADD SUM (QTY) AS TOTQTY SUMMARIZE (P JOIN SP) PER P {CITY} ADD COUNT AS NSP Relational Comparisons = equals /= not equals <= subset of (IN) < proper subset of >= superset > proper superset IS_EMPTY GROUP, UNGROUP Miscellaneous

12. The algebra - a language to describe how to construct a new relation. The calculus - a language to write a definition of that new relation. FORALL PX (PX.COLOR = COLOR (‘Red’)) EXISTS SPX (SX.S# = SX.S# AND SPX.P# = P# (‘P2’) Relational Calculus Procedural vs. Non-procedural Relational Completeness

13. 6.13 Get full details of all projects. J JX SELECT * FROM J 6.15 Get supplier numbers for suppliers who supply project J1. ( SPJ WHERE J# = J# (‘J1’) ) {S#} SPJX.S# WHERE SPJX.J# = J# (‘J1’) SELECT DISTINCT SPJ.S# FROM SPJ WHERE SPJ.J# = ‘J1’ 6.18 Get supplier-number/part-number/project-number triples such that supplier part and project are all colocated). (S JOIN P JOIN J) {S#, P#, J#} (SX.S#, PX.P#, JX.J# ) WHERE SX.CITY = PX.CITY AND PX.CITY = JX.CITY AND JX.CITY = SX.CITY SELECT S.S#, P.P#, J.J# FROM S,P,J WHERE S.CITY = P.CITY AND P.CITY = J.CITY. Query Exercises

14. An attribute value must match its type. A primary key must be unique. A tuple’s foreign key must be of the same type as its “matching” primary key. To create a record with a given foreign key, a record in the corresponding table must have that value as its primary key. Many to many relationships cannot be modeled (directly). Find examples to support these in SPJ! Integrity Preview!

15. Which of the relational algebra, tuple calculus and domain calculus is SQL based on? Can any relational query be expressed in a single SQL statement? To what extent do these query languages go beyond the relational model? Why might QueryByExample languages be easier to use than SQL for someone unfamiliar with the database scheme of a database? Study Questions

16. Lab Assignment for Next Week With your partner, finish creating and populating the SPJ database. Run the assigned queries from Chapter 6. SQL BNF http:// cuiwww.unige.ch/~falquet/sdbd/langage/SQL92/BNFindex.html