Class Will Start Momentarily…

1. Relational Model & Relational Algebra IS8080 – Data Application, Design and Implementation • Class • Will • Start • Momentarily…

2. Relational 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).

3. Terminology • 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.

4. Instances Instances of Branch and Staff (part) Relations

5. Database Relations Relation 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 attribute

6. Keys • 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 != 0

7. Integrity 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.

8. Views • View Virtual Table Example: Query in MS-Access Provides simplicity and security Will be discussed further in Chapter 6

9. Relational 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.

10. Relational Algebra Operations

11. More Relational Algebra

12. Restriction (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 notation R = STAFF Where Salary > 10000 -- RA Dr. G notation SELECT * FROM Staff Where Salary > 10000; -- SQL notation

13. Projection • 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 notation SELECT staffNo, fName, lName, salary FROM Staff; -- SQL notation

14. Union • 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 notation

15. Difference (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 notation

16. Intersection • 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 notation

17. Cartesian 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)) -- Codd Client [clientNo, fName, lName] x Viewing [clientNo, propertyNo, comment ] -- Dr. G notation SELECT clientNo, fName, lName, clientNo, propertyNo, comment FROM Client, Viewing; -- SQL

18. Join • Join is a derivative of Cartesian product. • It contains a restriction and a Cartesian Product • One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems.

19. Join • Join is a derivative of Cartesian product. • Equivalent to performing a Selection, using join predicate as selection formula, over Cartesian product of the two operand relations. • One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems. • Various forms of join operation • Natural join (defined by Codd) • Outer join • Theta join • Equijoin (a particular type of Theta join) • Semijoin

20. Natural Join • List the names and comments of all clients who have viewed a property for rent. • 1) (clientNo, fName, lName(Client)) Join (clientNo, propertyNo, comment(Viewing)) 2) Client [clientNo, fName, lName] Join Viewing [clientNo, propertyNo, comment ] • 3) SELECT clientNo, fName, lName, clientNo, propertyNo, comment FROM Client, Viewing WHERE CLIENT.clientNo = Viewing.ClientNo

21. Outer Join • To display rows in the result that do not have matching values in the join column, use Outer join. • R Left Outer Join S • (Left) outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation. Example: • Produce a status report on property viewings. propertyNo, street, city(PropertyForRent) Left Outer Join Viewing • SELECT propertyNo, street, city, ClientNo, ViewDate, Comment FROM Client, Viewing WHERE CLIENT.clientNo = Viewing.ClientNo (+);

22. Division • Identify all clients who have viewed all properties with three rooms. (clientNo, propertyNo(Viewing))  (propertyNo(rooms = 3 (PropertyForRent))) -- codd notation Viewing [clientNo, propertyNo] / PropertyForRent [propertyNo]

23. Assignment # 2

24. SQL and Relational Algebra

25. More Relational Algebra and SQL • I) Go to SQL and Relational Algebra • II) Go to Database Courseware • SQL (module) Interactive SQL (sub-module) • III) WinRDBI from Arizona State • IV) www3 Schools • V) SQL Tutorial

26. Constructing SQL

27. Animating SQL (pseudo-code)

28. Animating SQL (Rel.Algebra)

29. www.w3schools.com -> Learn SQL

30. www.w3schools.com -> Learn SQL

31. Assignments • Assingment # 0 – should be done with it • Assignment # 1 – Due next Tuesday • Assignment # 2 – Due next Thursday • Extra-credit assignment • Evaluate DB coursewares – will send specifications (post on Moodle) on Friday

32. End of Lecture End Of Today’s Lecture.