Chapter 6 Relations

1
Chapter 6 Relations
2
Outline

• Tuples
• Relation Types
• Relation Values
• Relation Variables
• SQL Facilities

3
Tuples
4
Tuple Example
Heading complete set of attributes Attribute
ltattribute name, attribute typegt Tuple type
TUPLE MAJOR_P P, MINOR_P P, QTY QTY
You can omit the type names from a tuple heading
Tutorial D notation TUPLE MAJOR_P P(P2),
MINOR_P P(P4), QTY QTY(7)
5
Tuples

• A tuple is a set of ordered triples each
  containing an attribute name, attribute type, and
  a value
• The count of ordered triples is the degree or
  arity of the tuple
• An attribute has a name and a type (e.g. MAJOR_P
  P)
• The set of attributes is the heading of the tuple

6
Tuples Properties

• Every tuple contains one value of the appropriate
  type for each attribute
• Attributes are unordered (no left-to-right
  ordering)
• Every subset of a tuple is a tuple
• Tuples are of n-ary degree (based on attribute
  count)
• The empty tuple is nullary

7
Tuple Type Generation General Form and Example

• General form
• TUPLE lt attribute commalist gt
• Example
• VAR ADDR TUPLE
• STREET CHAR,
• CITY CHAR,
• STATE CHAR,
• ZIP CHAR

8
Tuple Type Generation Selector Example

• Every tuple type has a selector operator
• Example
• TUPLE
• STREET 808 Commonwealth Ave.,
• CITY Boston,
• STATE MA,
• ZIP 02115

ADDR ? TUPLE STREET 808 Commonwealth
Ave., CITY Boston, STATE
MA, ZIP 02115
9
(No Transcript)
10
Tuple Type Generation Explanation of Selector
Example

• The preceding tuple can be assigned to the tuple
  variable ADDR
• It can be tested for equality with any other
  tuple of the same type
• To be of the same type it is necessary and
  sufficient that they have the same attributes
• Tuples can have attributes of any type
  whatsoever, including relation types and tuple
  types

TUPLE NAME NAME, ADDR TUPLE
STREET CHAR, CITY CHAR,
STATE CHAR, ZIP CHAR
11
Tuple Operators - Equality

• The following well-known topics in relational
  database theory depend directly on tuple
  equality
• Relational algebra, Candidate keys, Foreign keys,
  Functional, and other, dependency
• Two tuples are equal if and only if they have the
  same attributes and for each attribute in one
  tuple, its value matches the value in the
  corresponding attribute in the other
• Two tuples are duplicates if and only if they are
  equal
• Two equal tuples are in fact the same tuple
• All nullary tuples are equal, so there is but one
  nullary tuple

12
Tuple Equity Test
ADDR1 ? TUPLE STREET 808 Commonwealth
Ave., CITY Boston, STATE
MA, ZIP 02115
ADDR1 ADDR2 ?
ADDR2 ? TUPLE STREET 908 Commonwealth
Ave., CITY Boston, STATE
MA, ZIP 02115
ADDR1 ADDR3 ?
ADDR3 ? TUPLE STREET 808 Commonwealth
Ave., STATE MA, CITY Boston, ZIP
02115
13
Tuple Operators Projection

• Project example
• ADDR CITY, ZIP
• When operating against the previous tuple results
  in TUPLE CITY Boston, ZIP 02115
• Tuple type inference implies that in any
  operation, such as the above projection, the
  types of the attributes adhere to the result
• TUPLE CITY CHAR, ZIP CHAR

ADDR ? TUPLE STREET 808 Commonwealth
Ave., CITY Boston, STATE MA, ZIP
02115
14
Tuple Operators WRAP and UNWRAP

• Tuple 1 (TT1) the degree of TT1 is 2
• TUPLE NAME NAME, ADDR TUPLE
  STREET CHAR, CITY CHAR,
  STATE CHAR, ZIP CHAR
• Tuple 2 (TT2) the degree of TT2 is 5
• TUPLE NAME NAME, STREET CHAR, CITY CHAR,
  STATE CHAR, ZIP
  CHAR
• Let NADDR1 and NADDR2 be tuple variables of TT1
  and TT2
• NADDR1 NADDR2 WRAP STREET, CITY, STATE, ZIP
  AS ADDR
• NADDR2 NADDR1 UNWRAP ADDR

15
Relation Types
16
Relation Example
RELATION MAJOR_P P, MINOR_P P, QTY QTY
17
Relation Example (Cont.)
Tutorial D notation RELATION TUPLE
MAJOR_P P(P1), MINOR_P P(P2), QTY
QTY(5), TUPLE MAJOR_P P(P1), MINOR_P
P(P3), QTY QTY(3), TUPLE MAJOR_P
P(P2), MINOR_P P(P3), QTY QTY(2),
TUPLE MAJOR_P P(P4), MINOR_P
P(P6), QTY QTY(8)
By INSERT INTO
18
Relations A Formal Definition

• A relation is a relation value, strictly speaking
• Every relation consists of a head and a body
• The heading of a relation is a tuple heading,
  i.e., the complete set of attributes, where every
  attribute has a name and a type
• The count of attributes is the degree
• The body of a relation is the set of tuples all
  having that heading
• The count of tuples is the cardinality of the
  relation
• A relation does not contain tuples -- A relation
  contains a head and a body, and the body of the
  relation contains tuples
• Every subset of a head is a head
• Every subset of a body is a body
• The empty head, or body, is a valid subset

19
Relations A Relation Type Generator

• A type generator can be invoked in the definition
  of a relvar
• Takes the same form as the tuple type generator
• VAR PART_STRUCTURE, PART_STRUCTURE2 RELATION
  MAJOR_P P, MINOR_P, P, PQTY QTY
• Every relation type has an associated relation
  selector operator for assignment, comparison

20
Relation Type Generator in SQL
CREATE TABLE PART_STRUCTURE (MAJOR_PNO CHAR(5),
MINOR_PNO CHAR(5), QTY INTEGER )
CREATE TABLE PART_STRUCTURE2 (MAJOR_PNO CHAR(5),
MINOR_PNO CHAR(5), QTY INTEGER )
21
Relation Values
22
Properties of Relation (Values)

• Every tuple contains exactly one value for each
  attribute
• First normal form
• There is no left-to-right ordering to the
  attributes
• There is no top-to-bottom to the tuples
• There are no duplicate tuples

23
Relations vs. Tables

• Each attribute in the heading of a relation
  involves a type name, but those type names are
  usually omitted from tables
• Each component of each tuple in the body of a
  relation involves a type name and attribute name,
  but those type and attribute names are usually
  omitted from tables
• Each attribute value in each tuple in the body of
  a relation is a value of the applicable type, but
  those values are usually shown in some
  abbreviated form in tables (S1 instead of
  S(S1)
• The columns of a table have a left-to-right
  ordering, but the attribute of a relation do not

24
Relations vs. Tables (Cont.)

• Columns might have duplicate names, or even no
  names at all
• The rows of a table have a top-to-bottom
  ordering, but the tuples of a relation do not
• A table might contain duplicate rows, but a
  relation does not
• Tables are usually regarded as having at least
  one column, while relations are not required
• Tables (at least in SQL) are allowed to include
  null, while relations are certainly not
• Tables are flat or two-dimensional, while
  relations are n-dimensional

25
Relations vs. Tables Lets Make a Deal

• A table may be said to represent a relation, if
  the table agrees to some stipulations
• Each column will have an underlying type, and all
  atomic values will be of that type
• Row and column orderings will be irrelevant
• Duplicate rows are forbidden!
• Then and only then may the table sip from the
  purifying stream of relational mathematics

26
Relations-Valued Attributes

• Any type whatsoever can be used as the basis for
  defining relational attribute ? relation type in
  particular

27
Relations without Attributes DUM and DEE

• The empty set is defined as the set with no
  members it is a valid set
• A relation may have no attributes
• A relation with no attributes may have at most
  one tuple, the 0-tuple
• RELATION TUPLE TABLE_DEE (DEE)
• A relation with no attributes may have no tuple
  at all
• RELATION TABLE_DUM (DUM)
• DUM and DEE
• They play a role in relational algebra akin to
  zero in arithmetic
• They are valid relations that do not map well to
  tables
• They are fundamentally important because DEE can
  mean true and DUM can mean false

28

• 1 0 1
• 2 0 2
• 4 1 4
• 5 1 5
• 1, 2 ? 1, 2, 1, 2,

29
Operators on Relations

• Selector, assignment, equality comparison
• Relational comparisons
• Equals and not equals
• Subset of, and superset of
• Proper subset, and proper superset, of
• Greater than and less than do not operate on
  relations
• IS_EMPTY
• Test whether a given tuple is included in a given
  relation
• Extract tuple from a relation of cardinality one
• Restrict, project, join
• For presentation purposes only, ORDER BY

30
Relation Variable
31
Base Relvar Definition

• Two kinds of relvars base relvars and views
• Focus on base relvars here
• Syntax for defining a base relvar
• VAR ltrelvar namegt BASE ltrelation typegt
  ltcandidate key def listgtltforeign key def
  listgt
• where ltrelation typegt takes the formRELATION
  ltattribute commalistgtwhich invokes the
  relation type generator

32
Relation Variables Examples

• Suppliers
• VAR S BASE RELATION S S, SNAME
  NAME, STATUS INTEGER, CITY CHAR
  DEFAULT STATUS 0, CITY PRIMARY KEY S
  
• Parts
• VAR P BASE RELATION P P, PNAME
  NAME, COLOR COLOR, WEIGHT WEIGHT, CITY
  CHAR PRIMARY KEY P

• Supplier Parts
• VAR SP BASE RELATION S S, P P, QTY
  QTY PRIMARY KEY S, P FOREIGN KEY
  S REFERENCES S FOREIGN KEY P
  REFERENCES P

33
Relation Variable in SQL
CREATE TABLE PP (MAJOR_PNO CHAR(5), MINOR_PNO
CHAR(5), QTY INTEGER )
34
Notes

• All possible values of any given relvar are of
  the same relation type
• When a base relvar is defined, it is given an an
  initial value that is the empty relation of its
  relation type
• Defining a new relvar causes the system to make
  entries in the catalog to describe that relvar
• There exists a means to specify default values
  for attributes of base relvars (a value that is
  to be placed in the applicable attribute position
  if the user does not provide an explicit value
  when inserting some tuple)
• E.g. DEFAULT STATUS 0, CITY in S

35
Notes (Cont.)

• Relvars, like relations, define a predicate
• For a relvar it is the predicate that is common
  to all its possible values
• e.g. The supplier with supplier number S is
  named SNAME, has a status STATUS, and is located
  in city CITY
• The heading of a relvar is a predicate, and the
  body of its instantiated relation is the set of
  true propositions
• The Closed World Assumption, a/k/a The Closed
  World Interpretation If an otherwise valid tuple
  does not appear in the body of the relation, then
  the corresponding proposition is false

36
Updating Relvars

• Update uses the relational assignment operator
• Can use WHERE or WHERE NOT to limit result
• INSERT uses assignment to set the relation to a
  union of the old version and the new tuple
• DELETE uses assignment to set the relation to the
  old version without the deleted tuple
• Relational operators are set operators updates
  to individual tuples or attributes assign the
  entire relation during the operation

37
Updating Relvars Examples

• S S, SP SP
• S S WHERE CITY London
• How about S S WHERE CITY London
• S S WHERE NOT (CITY Paris)
• How about S S WHERE NOT (CITY Paris)
• S S UNION RELATION TUPLE S
  S(S6), SNAME NAME(Smith),
  STATUS 50, CITY Rome
• How about UPDATE? See next Chapter

DELETE
DELETE
INSERT
38
SQL Facilities
39
Rows

• SQL uses rows instead of tuples, with ordered
  columns
• Columns can be assigned values positionally
• If two rows are equal, this does not imply they
  are the same row
• Greater than and less than are legal row
  comparison operators
• SQL does not support row relational operators,
  such as row project

40
Table types

• SQL supports tables, not relations
• SQL table is not a relation of tuples, instead it
  is a bag of rows
• Bag is defined as an unordered set that allows
  duplicates
• Recap in a SQL table, columns are ordered rows
  are not

41
Table Values and Variables

• SQL uses TABLE ambiguously for table value and
  table variable
• CREATE TABLE is used to establish a relvar, and
  the empty relation
• The initial INSERT establishes the relation, in
  the sense that it instantiates a table
• No support for table-valued columns
• No support for tables with zero columns
• Default is NULL

42
Table DROP and ALTER

• SQL supports DROP TABLE
• Can CASCADE or RESTRICT
• RESTRICT will fail if the table is currently in
  use anywhere
• CASCADE always succeed and cause an implicit
  DROPCASCADE for everything currently using the
  table
• Table structures are ALTERed not updated
• Add, delete column
• Define, change, delete defaults
• Define, change, delete constraints
• Example ALTER TABLE S ADD COLUMN DISCOUNT
  INTEGER DEFALUT -1
• INSERT, DELETE, UPDATE statements

43
Structured Types

• CREATE TYPE POINT
• AS (X FLOAT,Y FLOAT) NOT FINAL
• This can then be used as a type for columns of a
  table
• Types can be as simple or as complex as needed
• Structured types can be defined in terms of
  simpler types, and they can be named
• SQL support for objects