STRUCTURE OF PRESENTATION 1 Basic database concepts Entracte

STRUCTURE OF PRESENTATION : 1. Basic database concepts ** Entr’acte 2. Relations 8. SQL tables 3. Keys and foreign keys 9. SQL operators I 4. Relational operators I 10. SQL operators II 5. Relational operators II 11. SQL constraints SQL vs. the relational model A. References 6. Constraints and predicates 12. 7. The relational model Copyright C. J. Date 2013 page 28

THE SUPPLIERS-AND-PARTS DATABASE : SNO SNAME STATUS S 1 S 2 S 3 S 4 S 5 Smith Jones Blake Clark Adams 20 10 30 20 30 P PNO PNAME P 1 P 2 P 3 P 4 P 5 P 6 Nut Bolt Screw Cam Cog S Copyright C. J. Date 2013 COLOR Red Green Blue Red SP CITY London Paris London Athens WEIGHT 12. 0 17. 0 14. 0 12. 0 19. 0 CITY London Paris Oslo London Paris London SNO PNO QTY S 1 S 1 S 1 S 2 S 3 S 4 S 4 P 1 P 2 P 3 P 4 P 5 P 6 P 1 P 2 P 2 P 4 P 5 300 200 400 200 100 300 400 200 300 400 page 29

“DATA LOOKS RELATIONAL” : The suppliers-and-parts DB contains three relations A relation is a mathematical construct and has a precise and formal definition—but it can be thought of, and depicted, as a certain kind of table Every relation has a heading and a body • Heading = set of attributes /* “columns” */ • Body = set of tuples /* “rows” */ Note the terminology! Copyright C. J. Date 2013 page 30

ATTRIBUTES : Every attribute is declared to be of some type aka “domain” /* typically not shown in pictures of relations, though */ E. g. , for suppliers, we might have: SNO SNAME STATUS CITY : : SNO NAME INTEGER CHAR /* UDT - supplier numbers */ /* UDT - names */ /* system defined */ But for simplicity I’ll assume the only types we have to deal with are all system defined … In particular, I’ll assume SNO and SNAME are both of type CHAR No. of attributes in heading = degree … If degree = n, relation is n-ary (unary, binary, ternary, …) Copyright C. J. Date 2013 page 31

TUPLES : Every tuple in the body of a given relation conforms to the heading of that relation E. g. , every tuple in body of S has: SNO SNAME STATUS CITY : : value of type CHAR value of type INTEGER value of type CHAR No. of tuples in body = cardinality If cardinality = 0, relation is empty Copyright C. J. Date 2013 page 32

PROPERTIES OF RELATIONS : • Relations never contain duplicate tuples /* formal reason: body is a mathematical set */ • The tuples of a relation are unordered, top to bottom /* formal reason: body is a mathematical set */ • The attributes of a relation are unordered, left to right /* formal reason: heading is a mathematical set */ • Relations are always normalized (i. e. , in “ 1 NF”) /* which just means every tuple in the body conforms */ /* to the heading … */ Copyright C. J. Date 2013 page 33

RELATION VARIABLES : S-SP-P picture shows the relation values existing in the database at a particular time … If we looked at a different time, we’d probably see different values Thus S, SP, and P are really relation variables … … which means they can be updated (i. e. , assigned to) For example: Copyright C. J. Date 2013 page 34

S relation variable SNO SNAME STATUS S 1 S 2 S 3 Smith Jones Blake 20 10 30 CITY London Paris current relation value Possible assignment: S : = S WHERE CITY ≠ 'Paris' ; S relation variable Copyright C. J. Date 2013 SNO SNAME STATUS S 1 Smith 20 CITY London current relation value page 35

OR EQUIVALENTLY : S relation variable SNO SNAME STATUS S 1 S 2 S 3 Smith Jones Blake 20 10 30 CITY London Paris current relation value Possible assignment: DELETE S WHERE CITY = 'Paris' ; S relation variable Copyright C. J. Date 2013 SNO SNAME STATUS S 1 Smith 20 CITY London current relation value page 36

IN OTHER WORDS : In practice, DBMSs always support explicit DELETE, INSERT, and UPDATE operators on relation variables /* examples to follow */ … but conceptually, at least, these are all just shorthand for certain relational assignments Copyright C. J. Date 2013 page 37

FOR EXAMPLE : DELETE SP WHERE QTY < 150 ; INSERT SP RELATION { TUPLE { SNO 'S 5' , PNO 'P 1' , QTY 250 } , TUPLE { SNO 'S 5' , PNO 'P 3' , QTY 450 } } ; UPDATE SP WHERE SNO = 'S 2' : { QTY : = 2 * QTY } ; Copyright C. J. Date 2013 page 38

To repeat: In practice, DBMSs always support explicit DELETE, INSERT, and UPDATE operators on relation variables … but conceptually, at least, these are all just shorthand for certain relational assignments DELETE, INSERT, and UPDATE (and “: =”) require a relation variable as their target … Read-only relational operators (e. g. , restrict, project) operate on relation values From this point forward: Relation means relation value Relvar means relation variable Copyright C. J. Date 2013 page 39

EXERCISES : Which of the following statements are true? a. Relations (and hence relvars) have no ordering to their tuples b. Relations (and hence relvars) have no ordering to their attributes c. Relations (and hence relvars) never have any unnamed attributes d. Relations (and hence relvars) never have two or more attributes with the same name Copyright C. J. Date 2013 page 40

EXERCISES (cont. ) : Which of the following statements are true? e. Relations (and hence relvars) never contain duplicate tuples f. Relations (and hence relvars) are always in 1 NF g. The types over which relational attributes are defined can be arbitrarily complex h. Relations (and hence relvars) themselves have types Copyright C. J. Date 2013 page 41