Chapter 6 Relations Prof YinFu Huang CSIE NYUST

Chapter 6 Relations Prof. Yin-Fu Huang CSIE, NYUST

6. 1 Introduction n Relation types, values, and variables 6. 2 Tuples n A set of ordered triples of the form <Ai, Ti, vi> n Degree, attribute, heading Major_P# : P# Minor_P# : P# P 2 P 4 Qty : Qty Advanced Database System 7 2

6. 2 Tuples (Cont. ) n Properties of tuples Ø Every tuple contains exactly one value for each of its attributes. Ø There is no left-to–right ordering to the components of a tuple. Ø Every subset of a tuple is a tuple. n The tuple type generator e. g. Var Addr Tuple { Street City State Zip Advanced Database System Char, Char}; 3

6. 2 Tuples (Cont. ) n Operators on tuples Ø the tuple selector, assignment, equality comparison Ø tuple projection, tuple join Ø Wrap, Unwrap e. g. Naddr 1: = Naddr 2 Wrap {Street, City, State, Zip} As Addr; Naddr 2: = Naddr 1 Unwrap Addr; Advanced Database System 4

6. 3 Relation Types n A relation value consists of a heading and a body. Relation [<heading>] {<tuple exp commalist>} n The relation type generator e. g. Var Part_Structure … Relation {Major_P# P#, Minor_P# P#, Qty} … ; Advanced Database System 5

6. 4 Relation Values n Properties of relations (See Fig. 6. 1) Ø Relations are normalized. ⇒ first normal form Ø Attributes are unordered, left to right. Ø Tuples are unordered, top to bottom. Ø There are no duplicate tuples. n Relations vs. Tables Advanced Database System 6

6. 4 Relation Values (Cont. ) n Relation-valued attributes (See Fig. 6. 2) Advanced Database System 7

6. 4 Relation Values (Cont. ) n Relations with no attributes Ø There are precisely two relations of degree zero-one that contains just one tuple, and one that contains no tuples at all. i. e. Table_Dee: Relation { } { Tuple{ } } Table_Dum: Relation { } n Operators on relations Ø Relational comparisons Ø Other operators: Ø Order By Advanced Database System 8

6. 5 Relation Variables n Base Relvar definition Ø Var <relvar name> Base <relation type> <candidate key def list> [<foreign key def list>]; e. g. Var SP Base Relation {S# S#, P# P#, Qty} Primary key {S#, P#} Foreign key {S#} References S Foreign key {P#} References P; Ø Drop Var <relvar name>; Advanced Database System 9

6. 5 Relation Variables (Cont. ) n Updating Relvars <relation assignment>: : =<relation assign commalist>; <relation assign>: : = <relvar name>: =<relation exp>; e. g. S’: =S Where Not (City=’Paris’); Insert <relvar name> <relation exp>; Delete <relvar name> [Where <bool exp>]; Update <relvar name> [Where <bool exp>] {attribute update commalist>}; Advanced Database System 10

6. 6 SQL Facilities n Rows Ø A left-to-right ordering [Row] (<exp commalist>) n Table Types Ø A left-to-right ordering Ø Duplicated rows Values <row value constructor commalist> Advanced Database System 11

6. 6 SQL Facilities (Cont. ) n Table values and variables Ø Create Table <base table name> (<base table element commalist>); • <base table element>: 1. <column definition> 2. <constraint> • <column definition>: <column name> <type name> [<default spec>] Ø Drop Table <base table name> <behavior> • <behavior>: Restrict or Cascade Ø Alter Table e. g. Alter Table S Add Column Discount Integer Default -1; Advanced Database System 12

6. 6 SQL Facilities (Cont. ) n Structured Types e. g. Create Type Point As (X Float, Y Float) Not Final Ref Is System Generated; Create Table Points Of Point (Ref Is Point# System Generated …); In the example, base table Points actually has three columns (Point#, X, and Y, in that order). Advanced Database System 13

The End. Advanced Database System 14