SLIDE 2.1 - Example of a Relation Show slide p 39 * Open Chap_2_Relational Model.xlsx * Relational database consists of a collection of tables. What is similar between a spreadsheet and a table? They both have rows and columns What is the difference between a spreadsheet and a table? * EVERY cell in a spreadsheet is independent from every other cell in the spreadsheet The cells have no logical relationship with one another as far as Excel is concerned You can define a name for a cell, a range of cells, or an entire worksheet but name is only shortcut for a particular location within the spreadsheet * Table Has a name * Columns Have a name All the data in a column has the same type of data in it * Rows All the values in a particular row are logically related to one another All the rows in the table are logically related Rows in one table may be logically related to rows in another table * A row represents a relationship between a set of values for the relation's attributes * A table is a COLLECTION of these relationships * Notice the two different sets of semantics used in the slide: * On board - write terminology: Relational Model Database Model Relation Table Attribute Column Tuple Row SLIDE 2.2 - Attribute Types Show slide p 42 * Domain Data type May also further constrain to specific set or range of values * Atomic values Like primitive datatypes in Java Character strings Bit strings Numbers Date / Time What are some examples of NON-atomic values / datatypes ? * NULL Value can mean either 1) Does not exist 2) Is unknown What are some examples of when NULL would be used as a value? * Unfortunately the the text sample databases don't include any null values SLIDE 2.3 - Relation Schema and Instance Show slide p 41 * Relation Schema logical design of a relation; consists of name of the relation , names of the attributes , and domains of the attributes * Relation is a collection of rows (tuples) containing any combination of valid values for each attribute defined in the relation * Relation Instance values of all the tuples in a relation the set of tuples that actually exists in a relation at a specific point in time * Tuple Set of related attribute VALUES for a particular relation. SLIDE 2.5 - Relations are Unordered Show slide p 41 * How are the rows in this table ordered? * NEVER assume an order for the ROWS in a table How will this effect your mulch data? Addresses must be kept in delivery order * Attributes in a relation are also UNORDERED Each value is identified solely by the attribute name and never by its position within the tuple This is NOT true in the database model SLIDE 2.6 - Database Show slide p 42 * The concept of DATABASE bridge between the logical and the physical realms relational instance must be stored somewhere using 1's and 0's on a memory device * Update terminology on the board: Relation Schema Table Definition Relation instance Database Instance * READ SLIDE SLIDE 2.7 - Keys Show slide p 45 * One of the most important concepts to understand in this class is KEYS superkeys candidate keys primary keys foreign keys * READ 1 - K subset of R Key is one or more attributes in a relation such that * POINT 2 - superkey the values of the attributes that make up the key are sufficient to uniquely identify the particular tuple in which they appear for every single tuple in the relation For any and every set of key values that appear an a particular tuple, there is one - and only one - tuple in the entire relation that contains those values Even though my campus id is enough to identify me - it is a key - it is possible and allowable to add other attributes to that key we call that a superkey my campus id and name together form a superkey * READ 3 - candidate key What does minimal mean in this context ? No extraneous attributes in the key * POINT 4 - primary key Primary key MUST be a candidate key There may be more than one candidate key One and only one candidate key can be the primary key How do you figure out which one should be it? * POINT 5 - foreign key Primary key in one relations appears as an attribute in another relation Go on to the next to demonstrate the concept SLIDE 2.8 - Schema Diagram for University Database Show slide p 46 * University database that will be used throughout the text * What do the lines and arrows in this diagram represent? One table may be related to another table * How are the relations in this schema related? What is the relationship between a student and a department? How do we know? What is the relationship between an instructor and a department? What is the relationship between a student and a instructor? * What is the mechanism for hooking these relations together? What is a PK? What is the PK of instructor? Given the ID of any instructor, I can find the rest of that instructor's info What is the PK of department? Given the name of a department, I can find the rest of that departments info * By including the PK of one relation as an attribute in another relation We can hook those rows of information together based on the value of that particular attribute * PK / FK relationships are shown by the underlined attributes and the arrows * Given a student ID, how can we find out what their schedule is, ie, days, times, and buildings ? * Given a instructor ID, how can we find out what their schedule is ? SLIDE 2.9 - Relational Query Languages Show slide p 48 POINT 1 * What is the difference between procedural and non-procedural languages? * Procedural Conditional statements, looping, branching, calling constructs I will tell you exactly what I want and how you have to do it * Non-procedural I'll tell you what I want, and you figure out how to go get it for me, and tell me when you're done POINT 2 You will be responsible for a bare minimum of relational algebra covered in the following slides POINT 3 The result of a relational operator is ALWAYS a relation ! SLIDE 2.10 - Selection of tuples Show slide p 48 * Sigma represents the selection operation * Selection filters rows but keeps all the attributes * Selection Unary operation Takes ONE relation as input Optionally a predicate to filter the output of the selection Returns another relation consisting of all the attributes of all the tuples in the input relation whose values satisfy the predicate * Predicate An boolean expression based on the values of the attributes in each of the tuples The expression is applied to one tuple at a time If the boolean returns TRUE, that tuple is included in the resulting relation If FALSE, that tuple is skipped * Slide example All tuples where the value of A is the same as the value of B and the value of D is > 5 SLIDE 2.11 - Selection of Columns (Attributes) Show slide p 49 * The slide title should really be the PROJECTION of columns * Pi represents the projection operation * Projection Unary operation Takes ONE relation as input A set (one or more) attribute names as input Returns another relation consisting of attributes of the relation that are listed in the set across all the tuples from the input relation duplicate tuples are removed from the resulting relation * Selection filters rows and keeps all the attributes * Projection filters columns and keeps all the rows * Slide example PI (projection operator) get only the values of attributes A and C from ALL the tuples in relation R * Why are there only 3 rows in the final result set? * Do Chap_2_RA_EXAMPLES Example A Is this selection, projection or both ? Both Which one do we do first? It doesn't matter PI(customer_name) ( relation ) PI(customer_name) ( SIGMA (customer_city = "Stamford") (customer) ) SLIDE 2.12 - Joining two relations – Cartesian Product Show slide p 50 * Cartesian or Cross product - Binary operation Takes TWO relations as input Return ONE relation as a result Each tuple in the first relation is combined with every other tuple in the second relation * Slide example * How many attributes are in the result? attributes(r) + attributes(s) * How many tuples are in the result? tuples(r) x tuples(s) SLIDE 2.13 - Union of two relations Show slide p 50 * /JPEGS/Chap_3_SQL_sql_1_union.gif * Union Binary operation Takes TWO relations as input All tuples that appear in R1, R2, or both * How many attributes are in the result? attributes(r) attributes in the relation do NOT have to have the same name just compatible domains attributes of the resulting relation are the attributes from the first relation * How many tuples are in the result? tuples(r) + tuples(s) MINUS DUPLICATES ! SLIDE 2.14 - Set difference of two relations Show slide p 50 * /JPEGS/Chap_3_SQL_2_sql_except_12.gif * Difference Binary operation Takes TWO relations as input All tuples of R that do not appear in S * How many attributes are in the result? attributes(r) * NOT commutative ! ! ! * /JPEGS/Chap_3_SQL_sql_except_21.gif SLIDE 2.15 - Set Intersection of two relations Show slide p 50 * /JPEGS/Chap_4_SQL_sql_intersect.gif * Intersection Binary operation Takes TWO relations as input All tuples of R that also appear in S * How many attributes are in the result? attributes(r) SLIDE 2.16 - Joining two relations – Natural Join Show slide p 49 * What is capital R and S in this slide? The schemas How is a schema defined Relation name and list of attributes R and S is essentially a list of attributes of each of the relations * What is R intersect S ? Only the attributes that appear in BOTH R and S Attributes that are common to both relations * Natural Join * tuples in r are combined with tuples in s IF: * the relations have one or more attributes with the same name * each tuple in r will be conbined with any tuple in s where the values are the same for those attributes * What is R union S? All the attributes of R followed by all the attributes of S * Result of a natural join * All attributes of R and S EXCEPT the duplicate attributes on which the natural join was performed SLIDE 2.17 - Natural Join Example Show slide p 50 * What attributes are the same in both relations? What attributes will it join on? B and D * What attributes will appear in the result? A B C D E * What values do B and D have in the first tuple of relation r? 1 and a * Which tuples in s have those same values for B and D 1st and 3rd * What other tuples from r match with any tuples in s ? * Do Chap_2_RA_EXAMPLES Example B & C Solu SLIDE 2.18 - Figure in in-2.1 (Relational Operators) Show slide p 50