1. Advanced Data ModelingRelational Data Model continued Steffen Staab with Simon Schenk TexPoint fonts used in EMF. Read the TexPoint manual before you delete this box.: AAAAA

2. Overview • Relational algebra, named perspective • Aggregates and grouping • SQL • Integrity constraints

3. Constant • { { a1 = v11, … , an = v1n }, • … … … • { a1 = vk1, … , an = vkn } }

4. Union • Let R1, R2 be relations with the same attributes. • R1 [ R2 = { t | t 2 R1 or t2 R2 }

5. Union, example R1 R2 R1[ R2

6. Renaming • Let R be a relation whose set of attributes is a1,…,an, c1,…,cm • Let b1, … , bn be distinct attributes such that • {b1, … , bn} Å { c1, … , cm} = ; • Then • ½a1!b1, … , an!bn(R) = • {{b1 = v1, … , bn=vn , c1=w1 , … , cm=wm} | • {a1 = v1, … , an=vn , c1=w1 , … , cm=wm} 2 R}

7. Set difference • Let R1, R2 be relatations with the same attributes. • R1– R2 = { t | t 2 R1 and t  R2}

8. Set difference, example R1 R2 R1 - R2

9. SQL • SQL is based on set and relational operations with certain modifications and enhancements • A typical SQL query has the form: select a_1, … , a_n from R_1, … , R_m where P • This query is equivalent to relational algebra expression: • ¼a1,…,an (¾P (R1£ … £ Rm)) • The result of an SQL query is a relation. • Exceptions?

10. Integrity constraints • Domain constraints. • Key constraints. • Foreign key constraints. • More general, defined constraints.

11. Query language • Allow one to define: • Relation and database schemas; • Relations through our relations; • Integrity constraints; • Updates.