Relational Data Model

1. Relational Data Model

2. Overview • Relational data model; • Tuples and relations; • Schemas and instances; • Named vs. unnamed perspective; • Relational algebra;

3. Table

4. Observations • The rows of the table contain pairs occurring in the relation player. • There are two columns, labeled respectively by “name” and “birth year”. • The values in each column belong to different domains of possible values.

5. How to specify a relation • specifying the names of the columns (also called fields or attributes); 2. specifying a domain of possible values for each column; 3. enumerate all tuples in the relation. (1)–(2) refer to the schema of this relation, while (3) to an instance.

6. Domains and attributes • A set of domains (sets of values); • A set of corresponding domain names d1, d2, … • A set of attributesa1, a2, …

7. Relation Schema and Instances Tuple: any finite sequence (v1, … , vn). n is the arity of this tuple. Relation schema: r(a1:d1, …, an:dn) where n ¸0, r is a relation name, a1, …, anare distinct attributes, d1, …, dnare domain names. Relation instance:finite set of tuples (v1, …, vn) of arity n such that vi2Difor all i.

8. Observation • The attributes in each column must be unique. 2. A relation is a set. Therefore, when we represent a relation by a table, the order of rows in the table does not matter. Let us add to this: 3. The order of attributes does not matter.

9. New notation for tuples • A tuple is a set of pairs { (a1, v1), …,(an, vn) } denoted by { a1=v1, …, an=vn } , • Let d1, …, dnbe domain names and be the corresponding domains. • The tuple conforms to a relation schema r(a1:d1, …, an:dn) if vi2Difor all i.

10. Relational data are structured Note that in the relational data model tuples stored in a table are structured: • all tuples conform to the same relation schema; • the values in the same column belong to the same domain. Untyped perspective: there is a single domain, so the second condition can be dropped.

11. Typed or untyped? • Consider the relation admire:

12. Database schema and instance • Relational database schema: a collection of relation schemas with distinct relation names. • Relational database instance conforming to a relational database schema S: • a mapping I from the relation names of S to relation instances such that • for every relation schema r(a1:d1, … , an:dn) in S the relation instance I(r) conforms to this relation schema.

13. Unnamed perspective • No attributes • a tuple is simply a sequence (v_1, … , v_n) of values. • The components of tuples can therefore be identified by their position in the tuple.

14. From Unnamed to Named Perspective • Introduce a collection of attributes #1,#2, …, • identify tuple (v1, … , vn) with the tuple { #1 = v1, … ,#n = vn }. • Likewise, identify relation schema r(d1, … , dn) with r(#1:d1, … ,#n:dn).

15. Relational Algebra and SQL 1. Can define new relations from existing ones; 2. Uses a collection of operations on relations to do so.

16. Constant { (v11, … , v1n), ….. (vk1, … , vkn) }

17. Union R1[ R2 = {(c1, … , ck) | (c1, … , ck) 2 R1 or (c1, … , ck) 2 R2 }

18. Set difference R_1 - R2 = {(c1, … , ck) | (c1, … , ck) 2 R1 and (c1, … , ck) 2 R2 }

19. Cartesian product R1 £ R2 = {(c1, … , ck, d1, … , dm) | (c1, … , ck) 2 R1 and (d1, … , dm) 2 R2 }.

20. Projection Let now R be a relation of arity k and i1, … , imbe numbers in {1, … , k}. ¼i1, … ,im (R) = {(ci1, … , cim) |(c1, … , ck) 2 R }. We say that ¼i1, … ,im(R) is obtained from R by projection (on arguments i1, … , im).

21. Selection Assume formulas on domains with “variables” #1, #2, …. For example, #1 = #2. ¾F(R) = {(c1, …, ck) | (c1, …, ck) 2 R and F holds on (c1, …, ck)}.

22. Overview • Relational algebra, named perspective • SQL • Integrity constraints • (Aggregates and grouping)

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

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

25. Union, example R1 R2 R1[ R2

26. 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}

27. SQL SQL is based on set and relational operations with certain modifications and enhancements A typical SQL query has the form: select a1, … , an from R1, … , Rm 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?

28. Integrity constraints • Domain constraints. • Key constraints. • Foreign key constraints. • More general, defined constraints. • How to translate them?

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