1 / 14

Advanced Database System

Learn about relation types, tuples, relation values, relationship variables, and SQL facilities in advanced database systems.

rowles
Télécharger la présentation

Advanced Database System

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 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

  2. 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

  3. 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

  4. 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

  5. 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. 6.4 Relation Values (Cont.) • Relation-valued attributes (See Fig. 6.2) Advanced Database System

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. The End. Advanced Database System

More Related