Slide 1Relational Databases (Codd, 70)TerminologyInstancesDatabase RelationsKeysIntegrity ConstraintsViewsRelational algebra and Relational calculusRelational Algebra OperationsMore Relational AlgebraRestriction (or Selection)ProjectionUnionDifference (Minus)IntersectionCartesian Product (Multiplication)JoinSlide 19Natural JoinOuter JoinDivisionAssignment # 2SQL and Relational AlgebraMore Relational Algebra and SQLSQL->Interactive SQLSQL -> SQL PseudocodeSlide 28www.w3schools.com -> Learn SQLSlide 30AssignmentsEnd of LectureCS 8630 Database Administration, Dr. GuimaraesClassWill Start Momentarily…CS8630 – Database AdministrationRelational Model & Relational Algebra08-19-2009CS 8630 Database Administration, Dr. GuimaraesRelational Databases (Codd, 70)•Structural Aspect: viewed as Relations (Tables) (Physical structure hidden from user).•Integrity Constraints: Relations satisfy certain integrity constraints.•Manipulative Aspect: Set of Operators that derive a relation from relations (relational algebra, relational calculus).CS 8630 Database Administration, Dr. Guimaraes•Relation = Table•Tuples = Rows•Cardinality = Number of Rows of a Relation•Degree = Number of Columns of a Relation•Attribute = named column of a relation.•Domain = set of allowable values for one or more attributes.•Relational Database = collection of normalized relations with distinct relation names.TerminologyCS 8630 Database Administration, Dr. GuimaraesInstancesInstances of Branch and Staff (part) RelationsCS 8630 Database Administration, Dr. GuimaraesDatabase RelationsRelation is a table that has no multi-valued attributes.For example, Employee [eid, ename, salary, skills]Is a Table, but it is not a relationship because skills is a multi-valued attributeCS 8630 Database Administration, Dr. GuimaraesKeys•Candidate Key = UNIQUE, NOT NULL –Attribute or set of attributes that is unique within a table (relation).•Primary Key ****–Candidate key selected to identify tuples (rows) uniquely within relation (table).•Foreign Key ****–Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.–Null = unknown value != 0CS 8630 Database Administration, Dr. GuimaraesIntegrity Constraints•Entity Integrity–In a base relation, no attribute of a primary key can be null.•Referential Integrity–If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null.•Enterprise Constraints–Additional rules specified by users or database administrators.CS 8630 Database Administration, Dr. GuimaraesViews•ViewVirtual TableExample: Query in MS-AccessProvides simplicity and securityWill be discussed further in Chapter 6CS 8630 Database Administration, Dr. GuimaraesRelational algebra and Relational calculus•Relational algebra and relational calculus are formal languages associated with the relational model.•Both are equivalent to one another.•A language that produces a relation that can be derived using relational calculus is relationally complete.CS 8630 Database Administration, Dr. GuimaraesRelational Algebra OperationsCS 8630 Database Administration, Dr. GuimaraesMore Relational AlgebraCS 8630 Database Administration, Dr. GuimaraesRestriction (or Selection)–Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate).Example: List all staff with a salary greater than US$10,000.salary > 10000 (Staff) -- RA Codd notationR = STAFF Where Salary > 10000 -- RA Dr. G notationSELECT * FROM Staff Where Salary > 10000; -- SQL notationCS 8630 Database Administration, Dr. GuimaraesProjectioncol1, . . . , coln(R)–Works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.•Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details.staffNo, fName, lName, salary(Staff) -- Codd notation Staff [staffNo, fName, lName, salary] -- Dr. G notationSELECT staffNo, fName, lName, salary FROM Staff; -- SQL notationCS 8630 Database Administration, Dr. GuimaraesUnion•R  S–Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. –R and S must be union-compatible.•If R and S have I and J tuples, respectively, union is obtained by concatenating them into one relation with a maximum of (I + J) tuples.Example: List all cities where there is either a branch office or a property for rent.•Pcity(Branch) union Pcity(PropertyForRent) -- Codd notation•R = Branch[city] union PropertyForRent[city] -- Dr. G. notation•SELECT pcity FROM Branch UNION SELECT pcity FROM PropertyForRent; -- SQL notationCS 8630 Database Administration, Dr. GuimaraesDifference (Minus)•R – S–Defines a relation consisting of the tuples that are in relation R, but not in S. –R and S must be union-compatible.•List all cities where there is a branch office but no properties for rent.city(Branch) – city(PropertyForRent)Or R = Branch [city] - PropertyForRent [city]SELECT city FROM Branch MINUS SELECT city FROM PropertyForRent; -- SQL notationCS 8630 Database Administration, Dr. GuimaraesIntersection•R  S–Defines a relation consisting of the set of all tuples that are in both R and S. –R and S must be union-compatible.•Expressed using basic operations:R  S = R – (R – S)Example: List all cities where there is both a branch office and at least one property for rent.city(Branch)  city(PropertyForRent)SELECT city FROM Branch MINUS SELECT city FROM PropertyForRent; -- SQL notationCS 8630 Database Administration, Dr. GuimaraesCartesian Product (Multiplication)•R X S–Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.•List the names and comments of all clients who have viewed a property for rent.(clientNo, fName, lName(Client)) X (clientNo, propertyNo, comment (Viewing)) -- CoddClient [clientNo, fName, lName] x Viewing [clientNo, propertyNo, comment ] -- Dr. G notationSELECT clientNo, fName, lName, clientNo, propertyNo, comment FROM Client, Viewing; -- SQLCS 8630 Database Administration, Dr.