Chapter 5 Types

1
Chapter 5 Types
2
Outline

• Introduction
• Values vs. Variables
• Types vs. Representations
• Type Definition
• Operators
• Type Generators
• SQL Facilities

3
Formal Definition of Relational Model

• An open-ended collection of scalar types
• A relation type generator and an intended
  interpretation for relations of types generated
  thereby
• Facilities to define relation variables of such
  generated relational types
• A relational assignment operation to assign
  relation values to relation variables
• An open-ended set of relational operators used to
  derive relation values from other relation values
• Integrity constraints represent just one
  application of the relational operators

4
Types or Domains

• A type is a set of values
• Example integer (the set of all integers), S
  (the set of all supplier numbers)
• Types can be system-defined (integer) or
  user-defined (S)
• All types have associated operators
• Formally, this means that the operator can take
  the given type as a parameter
• Example integers can be passed to an addition
  operator but not a sub-string operator

5
Values VS. Variables
6
Values

• A value is an individual constant
• 3, 3.5, Hao-Ren Ke
• A value is represented in memory by encoding,
  which generates its appearance (also called
  representation)
• Ex 3 can be represented as 2s complement, 1s
  complement
• Appearances can occur in different times and
  spaces
• Many variables can have the same value
• A value cannot be updated, for then it would be
  some other value a value is immutable
• Values can be simple or complex
• Simple integer, char
• Complex an XML document, a relation

7
Variables

• A variable is a holder for an appearance of a
  value
• It has location in time or space
• A variable can be updated, that is, it can hold
  another value
• A variable maintains its identity during the
  update it is still the same variable

carry
5
Set the value of carry to 0
carry
5
0
I
4
415
Set the value of I to I1(or II1)
I
4
5
8
Values and Variables are Typed

• Every value has its immutable type
• Every variable has its immutable type, so that
  all its values will be of that type
• Every attribute of every relvar has its immutable
  type
• Operators have a type when operating, but this
  can be polymorphic in different contexts
• e.g. can operate on integers or characters, but
  not both at the same time

9
Types VS. Representations
10
Types and their Representations

• A type per se is idealized, conceptual, a model
• A physical representation of the type is its
  implementation
• Physical representations should be hidden from
  the user
• This logical and physical distinction is an
  aspect of data independence
• Sometimes a type is called an ADT Abstract Data
  Type
• A type is a set of values that satisfy a certain
  type constraint (specified in Tutorial D by a
  POSSREP clause, including an optional CONSTRAINT
  specification)

11
Scalar vs. Non-Scalar Types

• A scalar type is atomic and encapsulated
• Example Integer, char, bool
• A non-scalar type is a type whose values are
  explicitly defined to have a set of user-visible,
  directly accessible components
• Complex and user-visible
• Example Name, address, radiology image,
  relation, point
• Values, variables, attributes, operators,
  parameters, expressions all can be scalar or
  not

12
Possible Representations

• Let T be a scalar type
• The physical representation is hidden from the
  user
• Values of type T must have at least one possible
  representation (declared as part of the
  definition of type T), which is not hidden from
  the user
• The possible representation of a scalar type may
  have components, and if so, then at least one set
  of these must be visible to the user
• TYPE QTY POSSREP INTEGER
• TYPE POINT POSSREP CARTESIAN X
  RATIONAL, Y RATIONAL POSSREP POLAR
  R RATIONAL, ? RATIONAL

13
Possible Representations

• Each type has at least one POSSREP visible to the
  user in its declaration
• Each POSSREP includes two operators
• Selector to specify a value for each
  representation
• Ex. QTY (100), QTY(N1 N2)
• THE_ to access each representation
• Ex. THE_QTY (Q), THE_QTY (Q1 Q2),
• QTY cannot equal 100, because quantity is not an
  integer, if it has been declared as a type
• QTY can equal QTY(100)
• However QTY equals 100 is a convenient shorthand

14
Examples of Selector and THE_

• Selectors have the same name as the corresponding
  possible representation
• THE_ operators have names of the form THE_C,
  where C is the name of the corresponding
  component of the corresponding possible
  representation

15
Examples of Selector and THE_ (Cont.)
16
Type Definition
Type constraint (a definition of the set of
values that make up the type)

• TYPE WEIGHT
• POSSREP D DECIMAL (5,1)
• CONSTRAINT D gt 0.0
  AND D lt 5000.0
• TYPE WEIGHT
• POSSREP LBS L DECIMAL (5,1)
• CONSTRAINT
  L gt 0.0 AND L lt 5000.0
• POSSREP GMS G DECIMAL (7,1)
  CONSTRAINT G gt 0.0 AND G
  lt 2270000.0
• 
  AND MOD (G, 45.4) 0.0

WEIGHT can be measured either in pound or gram
17
Notes for Type Definition

• Defining a new type causes the system to make an
  entry in the catalog to describe that new type
• It is possible to get rid of a user-defined type
  by
• DROP TYPE lttype namegt
• Cause the catalog entry describing the type to be
  deleted
• The operation of defining a type does not
  actually create the corresponding set of values
• Conceptually, those values already exist, and
  always will exist. All the define type
  operations just introduce a name by which that
  set of values can be referenced
• Likewise for the delete type operations

18
Definitions for the scalar types used in the
suppliers-and-parts DB

• TYPE S POSSREP CHAR
• TYPE NAME POSSREP CHAR
• TYPE P POSSREP CHAR
• TYPE COLOR POSSREP CHAR
• TYPE QTY POSSREP INTEGER

19
Operators

• OPERATOR ABS (Z RATIONAL) RETURNS RATIONAL
• RETURN (CASE
• WHEN Z gt 0.0 THEN Z
• WHEN Z lt 0.0 THEN Z
• END CASE)
• END OPERATOR
• OPERATOR REFLECT (P POINT) UPDATES P
• BEGIN
• THE_X (P) - THE_X (P)
• THE_Y (P) - THE_Y (P)
• RETURN
• END
• END OPERATOR
• DROP OPERATOR REFLECT

Read-only operators and update operators
20
Type Conversions

• QTY(100) converts an integer to a quantity
• THE_QTY (Q1) converts a quantity to an integer
• P P2 violates the rule that both sides of an
  assignment must be of the same type
• Compiler uses the P selector implicitly to
  convert P2 from Char to P
• Coercion invoking a conversion operator
  implicitly

21
Type Conversions

• Coercion (implicit type conversion) is not
  permitted in this textbook
• Explicit casting is permitted
• CAST_AS_CHAR (530.00)
• This is called strong typing
• Every value has a type, and the compiler checks
  to verify that operands are of the correct type
  for an operation
• Cant add weight to quantity, but can multiply
  them
• WEIGHT gt QTY
• EVEN gt ODD

22
Type and Domain

• All types are known to the system
• The types in a database are a closed set
• The type of the result of every valid expression
  will be a type that is known to the system
• The system knows which assignments ad comparisons
  are valid
• This closed set of types must include the type
  boolean if comparisons are to be valid
  expressions
• In a database system, a domain is a type, and
  thereby is an object class
• Hence we can speak about relations and objects
  simultaneously

23
Type Generators

• a/k/a parameterized types, or templates
• ARRAY is a classic invocation of a type generator
• ARRAY can take in all sorts of types, and can
  return all sorts of other types
• VAR SALES ARRAY INTEGER 12
• ARRAY operators such as assignment, equality,
  THE_ work equally well with any valid type, i.e.,
  a type known to the system
• Two type generators that are of particular
  importance in the relational world are TUPLE and
  RELATION

24
SQL Facilities
25
Built-In Types

• Note
• CLOB or BLOB for character (or binary) large
  object
• BIT and BIT VARYING will be dropped in SQL2003

26
Built-In Types

• Assignment and equality comparison operators are
  available for all built-in types
• Strong typing is supported, but only to a limited
  extent
• Type checking is based on 10 disjoint type
  categories boolean, bit string, binary,
  character string, numeric, date, time, timestamp,
  year/month interval, day/time interval
• SQL will coerce FLOAT to NUMERIC, for example

27
User-Defined Types Distinct

• CREATE TYPE lttype namegt AS ltrepresentationgt
  FINAL
• Ex CREATE TYPE WEIGHT AS DECIMAL(5, 1) FINAL
• The ltrepresentationgt is the name of another type
  (and the type in question must not be either
  user-defined or generated)
• POSSREP not supported, so WEIGHT is always a
  DECIMAL
• SQL does not support CONSTRAINTs
• For distinct types, SQL supports Selector and
  THE_
• Distinct types are strong, so you cannot use a
  comparison operator between the type and its
  underlying representation
• Additional operators can be defined (and
  subsequently dropped) as required
• DROP TYPE lttype namegt RESTRICT CASCADE

28
User-Defined Types Structured

• CREATE TYPE lttype namegt AS ltrepresentationgt NOT
  FINAL
• Ex CREATE TYPE POINT AS (X FLOAT,Y FLOAT) NOT
  FINAL
• Uses operators in place of Select and THE_
• Observe and mutate methods
• SET Z P.X / Observe X attribute of P /
• SET P.X Z / Mutate X attribute of P /
• Structured types can be ALTERed or DROPped
• Tuples and relations are structured types

29
Type Generators

• SQL includes three type generators (the SQL term
  is type constructor) REF, ROW, ARRAY
• REFERENCE generates a reference
• ROW generates a set of fields
• ARRAY generates an array
• Row type generator

SELECT CX.CUSTFROM CUST AS CXWHERE
CX.ADDR.STATECA
CREATE TABLE CUST( CUST CHAR(3), ADDR ROW
( STREET CHAR(50), CITY
CHAR(25), STATE CHAR(2), ZIP CHAR(5)
) PRIMARY KEY ( CUST ) )
INSERT INTO CUST (CUST, ADDR)VALUES (666,
ROW(1600 Pen Ave., Washington, DC, 20500
) )
UPDATE CUST AS CXSET CX.ADDR.STATETXWHERE
CUST 999
30
Type Generators (Cont.)

• Array type generator

CREATE TABLE ITEM_SALES( ITEM CHAR(5), SALES
INTEGER ARRAY12, PRIMARY KEY ( ITEM ) )
SELECT ITEMFROM ITEM_SALESWHERE SALE3 gt 10
UPDATE ITEM_SALESSET SALES3 10WHERE ITEM
Z0454
INSERT INTO ITEM_SALES(ITEM, SALES)VALUES
(X4320, ARRAY 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0 )