Relational Database & Relational Algebra

1. Relational Database & Relational Algebra Chapter 5, Elmasri & Navathe (2000)

2. Tuples, Domains

3. Ordering • Usually the tuples must be ordered • It may not be ordered if the attributes are explicit in the tuples

4. Relational Database Schema

5. Relational Database State

6. Naming of attributes • Same names, same thing • Same names, different things • Different names, same thing

7. Entity integrity constraints • Primary key can not be null

8. Referential Integrity Constrains • Maintain consistency between 2 tuples relations • Example: • Every DNO (Dept Number) on each EMPLOYEE must match one of the actual department number • The DNUMBER attribute of DEPARTMENT is the foreign key

9. Foreign Key • Can also refer to its own relation • Example: SSN of Supervisor • Also called state constraints • Transition constraints?

10. Referential

11. Database update constraints • Insert: • No null on primary key, no same primary key, no foreign key • Delete: • when it is referred by another tuple, you can not delete it • Update • No foreign key

12. Select Project Rename Union, Intersection, Difference Join Equijoin Natural Join Cartesian Product Division Aggregate Outer Join Relational Algebra

13. SELECT & PROJECT

14. RENAME

15. UNION

16. INTERSECT & DIFFERENCE

17. CARTESIAN PRODUCT

18. JOIN • DEPT_MGR  DEPARTMENT |X|MGRSSN=SSN EMPLOYEE • RESULT  πDNAME, LNAME, FNAME (DEPT_MGR)

19. NATURAL JOIN • When the attribute of the tuples are the same

20. DIVISIOIN • SMITH LNAME=‘Smith’ (EMPLOYEE) • SMITH_PNOS πPNOS(WORKS_ON |X| ESSN=SSN SMITH) • SSN_PNOS  πESSN, PNO(WORKS_ON) • ???  SSN_PNOS  SMITH_PNOS

22. AGGREGATE

23. LEFT OUTER JOIN • TEMP  EMPLOYEE ]X| SSN=MGRSSN DEPARTMENT • RESULT  πFNAM, MINIT, LNAME, DNAME (TEMP)