1 / 28

Relational data objects

Relational data objects. Lecture 6. Answer to last lecture’s activity. Example used throughout the course. Suppliers s_id (string); s_name (string); status (integer); city (string) Parts p_id (string); p_name (string); colour (string); weight (real); city (string) Contracted

deon
Download Presentation

Relational data objects

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 objects Lecture 6

  2. Answer to last lecture’s activity

  3. Example used throughout the course • Suppliers • s_id (string); s_name (string); status (integer); city (string) • Parts • p_id (string); p_name (string); colour (string); weight (real); city (string) • Contracted • s_id (string); p_id (string); qty (integer)

  4. Diagrammatic representation

  5. Relation Tuples Cardinality Attributes Degree Terminology

  6. Domains

  7. Why domains? • comparisons • price with price OK • weight with weight OK • weight with price !!! • POSSIBLE (they are both numbers) • BUT WRONG, i.e. MEANINGLESS • integrity of the DB

  8. Why domains? - example Suppose the P_id and S_id fields are numeric in all tables SELECT P_name, Qty FROM Parts, Contracted WHERE Parts.P_id = Contracted.P_id SELECT P_name, Qty FROM Parts, Contracted WHERE Parts.P_id = Contracted.S_id meaningless

  9. Domains • domains • are not explicitly stored in the DB • are specified as part of the DB definition (where?) • each attribute - defined on a certain domain

  10. Scalars • the smallest semantic unit of data • atomic values - no internal structure with respect to the relational model • NOTE: they may have internal structure • comparison with a basic data type (Pascal) • non-deterministic definition (“slippery”)

  11. Domain = data + operators • integer: +, -, *, integer-division • real • char: concatenate, find string, … • date: • more complex data types • spatial operators: all regions crossed by a line • images

  12. Domains and data types • domain (in the relational model) is the same with data type in a programming language • analyse the extent to which domain / data type definition can be supported • primitive support for domains in relational databases (INTEGER, CHAR(n), ...)

  13. Data definition • domains • are not explicitly stored in the DB • are specified as part of the DB definition (where?) • each attribute - defined on a certain domain

  14. Conventions • domains • unique in the DB • named relations • unique in the DB • attributes • unique within a relation

  15. Domain based queries • if domains exist • which relations contain information related to… a certain domain (e.g. related to suppliers - S_id) • if domains don’t exist • naming convention + • attribute interrogation

  16. Domains - advantages • domain constrained operators • increased representational power • spatial primitives for GIS; image processing features … • domain based queries

  17. Relations

  18. Variable vs value • variable - named object whose values can change • value - element of a type DECLARE VARIABLE example OF TYPE INTEGER // ... example := 1; // variable has certain value //... example := example * 2; // variable has certain value //...

  19. Relation variable vs relation value • relation variable • named object whose value at a given time is a relation value • its values change with time • relation value CREATE BASE RELATION example //... // insert operations SELECT //... FROM example // the variable has a certain value WHERE //...

  20. Relation value • a relation value on a collection of domains (not necessarily distinct) consists of a heading and a body

  21. Relation heading • a fixed set of attribute_name:domain_name pairs • { <A1:D1>, <A2:D2>, ..., <An:Dn>} • each Ak corresponds to only one Dk • A1, ..., An are all distinct • degree (arity) - n

  22. Relation body • a set of (n-)tuples • n is the same for all the tuples in the set • n-tuple • a set of attribute_name:attribute_value pairs • { <A1:vi1>, <A2:vi2>, ..., <An:vin> } (tuple i) • vik must be from Dk • one and only one pair for each attribute • cardinality - no of tuples

  23. Example - the relation Suppliers heading {<s_id:STRING>, <S_name:STRING>, <Status: INT>, <City:STRING>} body { {<s_id:’s1’>, <s_name:’Smith’>, <status:20>, <city:’London’>}, {<s_id:’s2’>, <s_name:’John’>, <status:30>, <city:’Leeds’>}, {<s_id:’s3’>, <s_name:’Stella’>, <status:70>, <city:’Paris’>} }

  24. Generate some incorrect examples

  25. Tables as relations • relation table • a table can be interpreted as a relation • there are some underlying domains • each column corresponds to a certain domain • the table’s heading represents the relation’s heading • each row represents a tuple, etc.

  26. Properties of relations • no duplicate tuples • SQL!!! • consequence - primary key • tuples are un-ordered • attributes are un-ordered • all attributes are atomic • 1NF; why do we need 1NF? • unnormalised relation - example

  27. Kinds of relations named relation base relation derived relation expressible relation query result intermediate result view (virtual) snapshot (real) stored relation

  28. Relational database • “a database that is perceived by the user as a collection of normalised relations of assorted degrees” (Codd, 1983)

More Related