1 / 29

Relational Data Model

Relational Data Model. Overview. Relational data model; Tuples and relations; Schemas and instances; Named vs. unnamed perspective; Relational algebra;. Table. Observations. The rows of the table contain pairs occurring in the relation player .

yukio
Download Presentation

Relational Data Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related