Advanced Data Modeling Relational Data Model continued

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.