chapter 6 relations. topics in this chapter tuples relation types relation values relation variables...

Chapter 6

Relations

Topics in this Chapter

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

Tuples

• A tuple is a set of ordered triples each containing an attribute name, attribute type, and a value

Part# Part# P1

attribute name

attribute type

attribute value

attribute triple

Tuples

• The count of ordered triples is the degree or arity of the tuple

• An attribute has a name and a type• The set of attributes is the heading of the

tuple

Part# Part# Name Name Color Color etc.

P1 Nut Red etc.

attribute

attribute value tuple

heading

Tuples and their Properties

• Every tuple contains one value of the appropriate type for each attribute

• Attributes are unordered• Every subset of a tuple is a tuple• Tuples are of n-ary degree (based on

attribute count)• The empty tuple is nullary

Tuple Type Generation – General Form and Example

• General form

TUPLE { < attribute commalist > };• Example

VAR ADDR TUPLE {

STREET CHAR,

CITY CHAR,

STATE CHAR,

ZIP CHAR };

Tuple Type Generation – Selector Example

• Every tuple type has a selector operator• Example

TUPLE {

STREET ‘808 Commonwealth Ave.’,

CITY ‘Boston’,

STATE ‘MA’,

ZIP ‘02115’ };

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 Operators - Equality

• The following well-known topics in relational database theory depend directly on this:

• Relational algebra• Candidate keys• Foreign keys• Functional, and other, dependency

Tuple Operators – Equality –A Formal Definition

• 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, forever and ever

Tuple Operators – Projection

ADDR ( CITY, ZIP )

when operating against the previous tuple results in

ADDR ( 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 )

Tuple Operators – WRAP and UNWRAP – Two example types

TUPLE { NAME NAME,

ADDR TUPLE { STREET CHAR, CITY CHAR, STATE

CHAR, ZIP CHAR } }• Tuple type 1 (TT1) compare to TT2:

TUPLE { NAME NAME,

STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR }

Tuple Operators – WRAP and UNWRAP –Discussion of the two example types

• Tuple type 1 (TT1) includes an attribute that is itself a tuple type

• It’s degree is two (TT2 is 5-ary)• Let NADDR1 and NADDR2 be tuple

variables of type TT1 and TT2, respectively

Tuple Operators – WRAP and UNWRAP –Operators at Work

NADDR2 WRAP { STREET, CITY, STATE, ZIP } AS ADDR

• The result of the wrap is a tuple of TT1, so this assignment is valid:

NADDR1 := NADDR2 WRAP { STREET, CITY, STATE, ZIP } AS ADDR;

• Analogously, this assignment is valid:NADDR2 := NADDR1 UNWRAP ADDR;

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 body of a relation is the set of tuples all having that heading

• The count of tuples is the cardinality of the relation

Relations – A Formal Definition, continued

• A relation does not contain tuples• A relation contains a head and a body• The body of the relation contains tuples• The count of attributes is the degree (just

as was the case with 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

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 RELATION

{MAJOR_P# P#, MINOR_P#, P#,

PQTY QTY}• Comes with a selector operator

Relation Values – a/k/a Relations

• Every tuple contains exactly one value for each attribute

• There is no ordering of the attributes• Likewise with the tuples• There are no duplicate tuples• Since every attribute of every tuple has a

value, a relation is in first normal form, by definition

Relations vs. Tables

• A relation cannot have a duplicate tuple by definition; tables, if ill-managed, can

• Rows and columns are ordered, where tuples and attributes are not

• A table must be created with at least one attribute; a relation needs none

• Tables may contain nulls, a relation cannot

• Tables are “flat” or two-dimensional; relations are of n-dimensions

Relations vs. Tables

• 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”

Relations-Valued Attributes

• Contrary to earlier reports, the death of repeating groups was greatly exaggerated

• It is perfectly relational to use relations as attributes, since a relation can be defined as a type

• Mr. Date humbly prostrates himself before the community, begs forgiveness, and promises it won’t happen again

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• A relation with no attributes may, in the

alternative, have no tuple at all• TABLE_DEE is the one with the tuple;

TABLE_DUM has none • (Also known as “DEE” and “DUM”)

DUM n 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”

Operators on Relations

• Selector, assignment, equality comparison

• Equals and not equals, equally• Subset of, and superset of• Proper subset, and proper superset, of• Greater than and less than do not operate

on relations

Operators on Relations –Continued

• IS_EMPTY • Tuple is included in relation• Extract tuple from a 1-relation• Restrict, project, join• For presentation purposes only, ORDER

BY

Relation Variables –a/k/a Relvars

• Syntax for defining a base relvar:

VAR <relvar name>

BASE <relation type>

<candidate key def list>

[<foreign key def list>];

where relation type takes the form

RELATION {<attribute commalist>}

which invokes the relation type generator

Relation Variables –an example – the Supplier

VAR S BASE RELATION

{ S# S#,

SNAME NAME,

STATUS INTEGER

CITY CHAR }

PRIMARY KEY { S# };

Relation Variables –an example – the Part

VAR S BASE RELATION

{ P# P#,

PNAME NAME,

COLOR COLOR

WEIGHT WEIGHT

CITY CHAR }

PRIMARY KEY { P# };

Relation Variables –an example – the Supplier Part

VAR SP BASE RELATION

{ S# S#,

P# P#,

QTY QTY, }

PRIMARY KEY { S#, P# }

FOREIGN KEY { S# } REFERENCES S

FOREIGN KEY { P# } REFERENCES P;

Relation Variables –The S, P, SP example

• When a base relvar is defined, it is given an an initial value that is the empty relation of its relation type

• 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

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

Interpreting Relvars

• Reminder: 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

SQL Facilities – 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

SQL Facilities – Table types

• SQL supports tables, not relations• SQL table is not a relation of tuples, no, it

is a bag of rows• Bag is defined as an unordered set that

allows duplicates• Recap: in an SQL table, columns are

ordered; rows are not

SQL Facilities – Table Values and Variables

• SQL uses “TABLE” ambiguously• 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

SQL Facilities – Table DROP and ALTER

• SQL supports DROP TABLE• Can CASCADE or RESTRICT• Table structures are ALTER’ed not

updated• Add column, change defaults or

constraints

SQL Facilities – 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