Download
1 / 14
140 likes | 150 Views
Learn about relation types, tuples, relation values, relationship variables, and SQL facilities in advanced database systems.
E N D
6.1 Introduction • Relation types, values, and variables 6.2 Tuples • A set of ordered triples of the form <Ai, Ti, vi> • Degree, attribute, heading Advanced Database System
6.2 Tuples (Cont.) • 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. • The tuple type generator e.g. Var Addr Tuple { Street Char, City Char, State Char, Zip Char}; Advanced Database System
6.2 Tuples (Cont.) • Operators on tuples • the tuple selector, assignment, equality comparison • tuple projection, tuple join • Wrap, Unwrap e.g. Naddr1:= Naddr2 Wrap {Street, City, State, Zip} As Addr; Naddr2:= Naddr1 Unwrap Addr; Advanced Database System
6.3 Relation Types • A relation value consists of a heading and a body. Relation [<heading>] {<tuple exp commalist>} • The relation type generator e.g. Var Part_Structure … Relation {Major_P# P#, Minor_P# P#, Qty Qty} … ; Advanced Database System
6.4 Relation Values • 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. • Relations vs. Tables Advanced Database System
6.4 Relation Values (Cont.) • Relation-valued attributes (See Fig. 6.2) Advanced Database System
6.4 Relation Values (Cont.) • 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 { } { } • Operators on relations • Relational comparisons • Other operators: • Order By Advanced Database System
6.5 Relation Variables • 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 Qty} Primary key {S#, P#} Foreign key {S#} References S Foreign key {P#} References P; • Drop Var <relvar name>; Advanced Database System
6.5 Relation Variables (Cont.) • 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
6.6 SQL Facilities • Rows • A left-to-right ordering [Row] (<exp commalist>) • Table Types • A left-to-right ordering • Duplicated rows Values <row value constructor commalist> Advanced Database System
6.6 SQL Facilities (Cont.) • 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
6.6 SQL Facilities (Cont.) • 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
The End. Advanced Database System
