# Relations by wulinqing

VIEWS: 18 PAGES: 78

• pg 1
```									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 :
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}
types TT1 and TT2

www.techstudent.co.cc
Tuples

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

<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

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
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
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
NOT NULL constraint is not allowed

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

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

ALTER TABLE DEPARTMENT ALTER

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

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
modified_date date not null,
….. );