Docstoc

Relations

Document Sample
Relations Powered By Docstoc
					Relations




 www.techstudent.co.cc
                     Tuples
• Given a collection of types Ti (i=1,2,….n), a
  tuple value on those types is a set of ordered
  triples of the form <Ai, Ti, vi> where
    Ai is attribute name
    Ti is type name
    vi is a value of type Ti       and




                     www.techstudent.co.cc
                  Tuples
– The value n is the degree or arity of t
– ordered triple <Ai, Ti,vi> is a component of t
– The ordered pair <Ai, Ti> is an attribute of t and
  identified by attribute name Ai
– The complete set of attributes is the heading of t
– The tuple type of t is determined by the heading of
  t; heading and tuple type both have the same
  attributes and the same degree as t does


                  www.techstudent.co.cc
                      Tuples
• Tuple type name is
  TUPLE { A1 T1, A2 T2,…. An Tn}
• Example
  MAJOR_PART_NUMBER   MINOR_PART_NUMBER:P QUANTITY :
  : PART NUMBER       ART NUMBER          QUANTITY

           P2                        P4         7



Degree :
Heading
Type
                      www.techstudent.co.cc
                     Tuples
• Properties of tuples
  – Each 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

  – A tuple of degree n is said to be n-ary
  – Tuple of degree 0 is called nullary
                     www.techstudent.co.cc
                      Tuples
• The TUPLE type generator:

  –General Form

    TUPLE { < attribute comma list> }

    Where each <attribute> consists of an <attribute name>
      followed by a <type name>



                      www.techstudent.co.cc
                Tuples
• Example
     VAR ADDR TUPLE { STREET CHAR,
                          CITY     CHAR,
                          STATE CHAR,
                          ZIP CHAR }
Tuple Selector operator
TUPLE { STREET `14 Park View`, CITY
  `TVM`, STATE ‘Kerala`, ZIP `695 002’}

                www.techstudent.co.cc
                   Tuples
• Operators:
  – All operators of relational algebra
  – Candidate keys
  – Foreign keys
  – Functional and other dependencies
  – Tuple equality
  – Tuple type inference
  – WRAP and UNWRAP

                  www.techstudent.co.cc
                     Tuples
• Tuple equality
  – Tuples t1 and t2 are equal if and only if they have
    the same set of attributes A1, A2, …An and for all
    i (i=1,2,..n) the value of v1 of Ai is equal to the
    value of v2 of Ai in t2

  – t1 and t2 are duplicates of each other if and only
    if they are equal


                    www.techstudent.co.cc
                  Tuples
• Tuple projection
  – ADDR { CITY, ZIP }
• WRAP and UNWRAP
  – Let
     • TT1 = TUPLE {NAME NAME, ADDR TUPLE {
                      STREET CHAR, CITY CHAR,
                      STATE CHAR, ZIP CHAR} }
     • TT2 = TUPLE {NAME NAME, ADDR TUPLE {
                      STREET CHAR, CITY CHAR,
                      STATE CHAR, ZIP CHAR} }
                 www.techstudent.co.cc
                  Tuples
• Let
  – TT1 = TUPLE {NAME NAME, ADDR TUPLE
                { STREET CHAR, CITY
                CHAR, STATE CHAR, ZIP CHAR} }
  – TT1 = TUPLE {NAME NAME, STREET CHAR, CITY
                   CHAR, STATE CHAR, ZIP CHAR}
• Let NADDR1 and NADDR2 be tuple variables of
  types TT1 and TT2



                 www.techstudent.co.cc
                Tuples
• NADDR1 := NADDR2 WRAP { STREET, CITY,
                  STATE, ZIP } AS ADDR;

• NADDR1 UNWRAP ADDR

• NADDR2 := NADDR1 UNWRAP ADDR;




                www.techstudent.co.cc
                Relation types
• Definition

A relation value r consists of a heading and a body
  where :
The heading of r is a tuple heading. relation r has the
  same attributes and the same degree as that heading
  has
the body of r is a set of tuples, all having the same
  heading; cardinality of that set is said to be the
  cardinality of r.

                      www.techstudent.co.cc
               Relation types
• Relation type of r is determined by the heading
  of r
• Relation type name is
   RELATION { A1 T1, A2 T2,…An Tn }
• General form of relation selector is
     RELATION [<heading>] {tuple exp comalist>}

  <heading> is comalist of <attribute>s enclosed in
  braces
                     www.techstudent.co.cc
             Relation values
• Relations are normalized
• There is no left-to-right ordering to atttributes
• There is no top-to-bottom ordering to the
  tuples
• There are no duplicate tuples




                    www.techstudent.co.cc
            Relation values
RELATIONS                     TABLES

Each heading involves a       Type names are omitted
type name
Each component of a           Type names and
tuple has a type name         attribute names are
and an attribute name         omitted
Value is of applicable        Values are in
type                          abbreviated form

                  www.techstudent.co.cc
RELATIONS                       TABLES

No left-to-right ordering       Have a left-to-right
of attributes                   ordering
No ordering of tuples           Rows have a top-to-
                                bottom ordering
Does not contain                May contain duplicate
duplicate tuples                rows



                     www.techstudent.co.cc
             Relation values
• Relations with no attributes
  – A relation can have an empty set of attributes or in
    other words no attribute at all
  – Such a relation can have atmost one tuple 0-tuple
  – Thus there are two relations of degree 0
     • One which contains one tuple (TABLE_DEE)
     • That contains no tuples at all (TABLE_DUM)
     • DEE means TRUE and DUM means false


                     www.techstudent.co.cc
           Relation values
• Relation selector

     RELATION { } { TUPLE { } }

     RELATION { } { }




                 www.techstudent.co.cc
               Relation values
• Operators on relations
  – Relational comparison
  <relation exp> <relation comp op> <relation exp>
  Relation comp op
    =        equals
    ≠        not equals
            subset of
            proper subset of
            superset of
            proper superset of
                      www.techstudent.co.cc
            Relation values
• To find if a relation is equal to an empty
  relation
  – IS_EMPTY ( < relation exp> )
• To find whether a given tuple is found in a
  relation r or not
  –tr



                  www.techstudent.co.cc
          Relation variables
• Base Relvar Definition

 VAR <relvar name> BASE <relation type>
                  <candidate key def list>
                [ <foreign key def list> ];

 <relation type> takes the form
    RELATION { <attribute comalist> }
                 www.techstudent.co.cc
        Relation variables
• Example

  VAR SUPPLIERS BASE RELATION
        { SUP_NUMBER SUPPLIER_NUMBER,
          SUP_NAME     NAME,
          STATUS       INTEGER,
          CITY         CHAR }
        PRIMARY KEY { SUP_NUMBER} ;

              www.techstudent.co.cc
              Relation variables
•    Explanation
    1) The base relation has the type
       RELATION { SUP_NUMBER SUPPLIER_NUMBER,
         SUP_NAME NAME, STATUS INTEGER, CITY
         CHAR}

    2) The terms heading, body, attribute, tuple, degree
       applies to relvars also
    3) All possible values of any given relvar are of the
       same relation type and hence have the same
       heading

                        www.techstudent.co.cc
Relational Algebra




     www.techstudent.co.cc
              Introduction
• Original eight operators:
  – Union
  – Intersection
  – Difference
  – Cartesian product
  – Select
  – Project
  – Join
  – divide
                  www.techstudent.co.cc
                  Closure
• Relational closure property
  – The output from any relational operation is
    another relation
• To achieve closure property, all relations
  should have proper attribute names in
  order to use those attributes in the
  subsequent operations


                   www.techstudent.co.cc
                   Closure
• RENAME operator
  – To rename attributes within a specified relation
  – Eg: SUPPLIERS RENAME CITY AS SCITY
      Gives the same heading and body as the relation that
   is the current value of SUPPLIERS except that the city
   attribute is named SCITY instead of city

  Multiple renaming
  PARTS RENAME (PART_NAME AS PN, WEIGHT AS
   WT

                    www.techstudent.co.cc
          Relational Operators
• Union
  – Special type of mathematical union in which both
    the relations should be of the same type
  – This is referred to as union compatibility
  – Given two relations a and b of the same type,
    the union of those two relations a union b, is a
    relation of the same type, with the body
    consisting of all tuples t such that t appears in
    a or b or both

                    www.techstudent.co.cc
        Relational Operators
• Intersect
  – Given two relations a and b of the same type,
    the intersection of those two relations a
    INTERSECT b, is a relation of the same type,
    with the body consisting of all tuples t such
    that t appears in both a and b




                  www.techstudent.co.cc
        Relational Operators
• Difference
  – Given two relations a and b of the same type,
    the difference of those two relations a MINUS
    b, is a relation of the same type, with the body
    consisting of all tuples t such that t appears in
    a and not b
  – A MINUS B is not the same as B MINUS A



                   www.techstudent.co.cc
        Relational Operators
A
Supplier_number Supplier_name Status City
S1              Sarala                    20   Mumbai
S4              Priya                     20   Mumbai

B
Supplier_number Supplier_name Status City
S1              Sarala                    20   Mumbai
S2              Uma                       10   Chennai
                  www.techstudent.co.cc
         Relational Operators
• Product
  – The cartesian product of two relations a and
    b, a TIMES b, where a and b have no
    common attribute names, to be a relation
    with a heading that is the union of the
    headings of a and b and with a body
    consisting of the set of all tuples t such that t
    is the union of a tuple appearing in a and a
    tuple appearing in b

                    www.techstudent.co.cc
        Relational Operators
• Given the tuples
     {A1 a1, A2 a2,….Am am}
And
     {B1 b1, B2 b2, …Bn bn}

The union of the two is the single tuple

{A1 a1, A2 a2,….Am am,B1 b1, B2 b2, …Bn bn }

                  www.techstudent.co.cc
          Relational Operators
• Select
• Used to select a subset of tuples in a relation
  that satisfy a selection condition
• Denoted by
             σ<selection condition>(<relation name>)

   σ – SELECT operator

   Selection Condition – boolean expression specified on
    relation attributes using the comparison operators { =,
    <, <=, >, >=, }
                        www.techstudent.co.cc
  Relational Operators(Select)
• <selection operation> is applied to each
  tuple t in relation R specified by <relation
  name>
• If condition is satisfied,tuple t is
  SELECTED
• Boolean operators AND, OR, NOT can be
  used to connect the conditions


                  www.techstudent.co.cc
   Relational Operators(Select)
• Is applied on a single relation

• Degree of relation resulting from SELECT is the
  same as the original relation

• Fraction of tuples selected is called as
  selectivity of the condition

• Is commutative
                    www.techstudent.co.cc
  Relational Operators(Project)
• Selects certain columns from the relation
• General Form
          π<attribute list>(<relation name>)

     π – project operator

• Degree is equal to the number of
  attributes in <attribute list>
                 www.techstudent.co.cc
  Relational Operators(Project)
• Πx, y,….z(A) is a relation with
   – A heading derived from the heading of a by
     removing all attributes not mentioned in the
     set {X, Y, …., Z }

   – A body consisting of all tuples{X x,Y y,…, Z z}
     such that a tuple appears in a with X value x,
     Y value y, … and Z value z.


                    www.techstudent.co.cc
  Relational Operators(Project)
• PROJECT implicitly removes any duplicate
  tuples

• Whenever there are two identical tuples, only
  one is kept in the result.This is called duplicate
  elimination

• Commutativity does not hold on PROJECT


                    www.techstudent.co.cc
    Relational Operators(Join)
• Denoted by
• Combine related tuples from two relations
• General form
    R <join condition>S

The resultant relation Q has one tuple for
 each combination of tuples whenever the
 combination satisfies the join condition
                   www.techstudent.co.cc
     Relational Operators(Join)
• common join – joins with equality condition.

• A join where only the = comparison is used is
  called Equi Join

• Equi join always have one or more pairs of
  attributes that have identical values.

• To get rid of the second attribute, natural join is
  used
                     www.techstudent.co.cc
    Relational Operator(Divide)
• Let relations a and b have attributes
       X1, X2, …..Xm
   and
       Y1,Y2,….Yn
Division of a by b
    a divideby b
 is a relation with heading {X} and body consisting
   of all tuples {X x} appearing in a such that a
   tuple {X x, Y y} appears in c for all tuples {Y y}
   appearing in b.
                     www.techstudent.co.cc
Integrity




www.techstudent.co.cc
                 Integrity
• An integrity constraint is a boolean
  expression that is associated with some
  database and is required to evaluate at all
  times to TRUE.
• Constraints should be formally declared to
  the DBMS and DBMS enforces these
  constraints


                 www.techstudent.co.cc
 Internal Vs External Predicates
• Internal predicates: what the data means
  to the system
• External predicates: What the data means
  to the user
• A given internal predicate is the system‟s
  approximation to the corresponding
  external predicate


                 www.techstudent.co.cc
 Internal Vs External Predicates
• External predicate for a given relvar is
  basically what the relvar means to the user
  – The EMPLOYEE with the specified employee
    number(EMPNO) has the specified
    name(ENAME) , is working for the department
    with the specified number (DNO), and gets a
    salary specified(SALARY)
• S is a tuple of the form
  (EMPNO, ENAME, DNO, SALARY)
                 www.techstudent.co.cc
• A given tuple appears in a given relvar at a
  given time if and only if that tuple makes
  that relvar‟s external predicate evaluate to
  TRUE at that time.




                 www.techstudent.co.cc
                   Keys
• Candidate Key
• Primary Key and Alternate Key
• Foreign Key




                www.techstudent.co.cc
             Candidate Key
• The set of all attributes of R having the
  uniqueness property
• Let K be a set of attributes of relvar R.
  Then K is a candidate key for R if and only
  if it has both of the following properties
  – Uniqueness: No legal value of R ever
    contains two distinct tuples with the same
    value for K
  – Irreducibility: No proper subset of K has the
    uniqueness property
                   www.techstudent.co.cc
   Primary Keys and alternate
             keys
• One of the candidate key is chosen as the
  Primary Key
• Others are Alternate Keys
• If there is only one candidate key, then
  that is chosen as the primary key.


                www.techstudent.co.cc
                  Foreign Key
• A set of attributes FK in relation Schema R1 is a
  foreign key of R1 that references R2 if it satisfies
  the following rules
   – The attributes in FK have the same domain as the
     primary key attributes PK of R2; the attributes of FK
     are said to refer to the relation R2
   – A value of FK in a tuple t1 of the current state r1(R1)
     either occurs as a value of PK for some tuple t2 in the
     current state r2(R2) or is null. In the former case
     t1[FK]= t2[FK]

                       www.techstudent.co.cc
                  Foreign key
• Let R1 be a relvar. Then a foreign key in R2 is a
  set of attributes ofR2 , say FK such that
  – There exists a relvar R1 with a candidate key CK
  – It is possible to rename some subset of the attributes
    of FK, such that FK becomes FK‟ and FK‟ and CK are
    of the same type.
  – For all time, each value of FK in the current value of
    R2 yields a value for FK‟ that is identical to the value
    of CK in some tuple in the current value of R1



                      www.techstudent.co.cc
                Foreign key
• Every value of FK must appear as a value of
  CK, but the converse is not a requirement; i.e.,
  R1 might contain a CK value that does not
  currently appear as an FK value in R2
• FK can be simple or composite
• The constraint that values of FK must match
  values of CK is known as the referential
  constraint.

                   www.techstudent.co.cc
               Foreign Key
• The problem of ensuring that the database does
  not include any invalid foreign key values is
  the referential integrity problem
• Referential integrity rule:
  The database must not contain any unmatched
  foreign key values



                  www.techstudent.co.cc
    Data Definition (DDL)

• CREATE – specify a new relation
• ALTER – change the definition of a
  table
• DROP – delete a table




              www.techstudent.co.cc
          CREATE TABLE
• Specifies a new relation by giving it a
  name and specifying the attributes and
  initial constraints
• Attributes have : name
                     data type
                     constraints such as
                                  NOT NULL

                 www.techstudent.co.cc
            CREATE TABLE
 CREATE TABLE table_name(
     field1 data type [NOT NULL],
     field2 datatype [NOT NULL]
  PRIMARY KEY (<column name commalist>)
  UNIQUE ( <column name commalist>)
FOREIGN KEY (<column name commalist>)
  REFERENCES < base table name> [ column name
                                           commalist]
[ON DELETE < referential action>]
[ON UPDATE <referential action>]
CHECK (<column name> IS NOT NULL) )
                     www.techstudent.co.cc
      Attribute data types

• Numeric
  – Integer number s of various sizes
    • INTEGER or INT, SMALLINT
  – Floating point numbers
    • FLOAT, REAL and DOUBLE PRECISION




               www.techstudent.co.cc
         Attribute data types
• Character- string
  – Fixed length CHAR(n) or CHARACTER(n)
  – Varying length VARCHAR(n)
• String is placed within single quotes




                 www.techstudent.co.cc
         Attribute data types
• Bit-String
  – Fixed length – BIT(n) or variable length
    BITVARYING(n) n – max. number of bits
• Boolean
  – TRUE or FALSE




                  www.techstudent.co.cc
      Attribute data types

• DATE
  – Ten positions – YEAR, MONTH and DAY
  – Format : YYYY-MM-DD
• TIME
  – Atleast 8 positions HOUR, MINUTE,
    SECOND
  – Format HH:MM:SS

              www.techstudent.co.cc
         Attribute data types
• TimeStamp
  – Includes both date and time fields
  – Plus minimum of 6 positions for decimal
    fraction of seconds
  – Eg:
    • TIMESTAMP „2002-09-27 09:12:47 648302‟




                  www.techstudent.co.cc
          Attribute constraints
•   NOT NULL
•   DEFAULT <value>
•   CHECK
•   Primary key, foreign key
•   constraint




                   www.techstudent.co.cc
         DELETE command
• Removes tuples from a relation
• Tuples are deleted from only one table at
  a time
• Missing WHERE clause deletes all tuples




                 www.techstudent.co.cc
      DELETE command
• DELETE FROM EMPLOYEE
  where name = „Brown‟
• Delete from employee where DNO in
  (select dnumber from department where
  dname = „Research‟)
• DELETE FROM Employee



                www.techstudent.co.cc
        UPDATE command
• Used to modify values of one or more
  selected values
• UPDATE "table_name"
  SET "column_1" = [new value]
  WHERE {condition}
• SET – specifies attributes to be modified
  and their new values


                 www.techstudent.co.cc
      UPDATE command+
• UPDATE PROJECT
  SET PLOC = „DELHI‟, DNUM = 5
  WHERE PNUM = 10




              www.techstudent.co.cc
        Referential Actions
• CASCADE: operations will cascade to
  delete all matching tuples
• RESTRICT : operations are restricted to
  the case where there are no matching
  keys.
• NO ACTION : Delete is performed exactly
  as requested


                www.techstudent.co.cc
          ALTER TABLE
• To change the definition of the database
  table
• Possible actions
  – Adding or dropping a column
  – Changing a column definition
  – Adding or dropping table constraints




                  www.techstudent.co.cc
           ALTER Table
• To add an attribute JOB in EMPLOYEE,
     ALTER TABLE EMPLOYEE
         ADD JOB VARCHAR(15);
NOT NULL constraint is not allowed




               www.techstudent.co.cc
          ALTER TABLE
• To drop we must choose either CASCADE
  or RESTRICT
     ALTER TABLE EMPLOYEE
     DROP ADDRESS CASCADE




               www.techstudent.co.cc
           ALTER TABLE
• Dropping an existing default clause or
  defining new clause
  ALTER TABLE DEPARTMENT
     ALTER MGRSSN DROP DEFAULT;

ALTER TABLE DEPARTMENT ALTER
 MGRSSN SET DEFAULT “12223333”;

                www.techstudent.co.cc
         ALTER TABLE
• Adding or dropping a constraint
    ALTER TABLE EMPLOYEE
    DROP CONSTRAINT
             EMPSUPERPK CASCADE




             www.techstudent.co.cc
         Update statements
• Modifying the data base
  – INSERT
  – DELETE
  – UPDATE




                www.techstudent.co.cc
         INSERT command
• Used to add a single tuple to a relation
• Values should be listed in the same order
  in which they are placed in the CREATE
  TABLE command
• INSERT INTO tablename
  VALUES (list of attribute values)



                 www.techstudent.co.cc
                    Triggers
• Triggered procedures are precompiled
  procedures that are stored along with the
  database and invoked automatically whenever
  some specified event occurs
• To design a trigger mechanism, we must:
  – Specify the conditions under which the trigger is to
    be executed.
  – Specify the actions to be taken when the trigger
    executes.
                     www.techstudent.co.cc
                 Triggers
create table general_comments ( ….
      modified_date date not null,
      ….. );

create trigger general_comments_modified before
  insert or update on general_comments
  for each row
      begin
             :new.modified_date := sysdate;

     end;
                  www.techstudent.co.cc

				
DOCUMENT INFO