Transcript Chapter 3 - Relational Model

Chapter 3 : Relational Model
•
•
•
•
•
•
Structure of Relational Databases
Fundamental Relational-Algebra Operations
Additional Relational-Algebra Operations
Extended Relational-Algebra Operations
Null Values
Modification of Database
Terminologies related To
Relational Model
• Relation – Defined as a table with collection of rows
and columns. Data can be stored in the form of a 2D
table. Rows will be records and columns will be
attributes
• Attribute – Defined as a named column of a relation.
The entities are relations and their attributes are
columns of the relation
• Domain – Defined as a set of allowed values for one
or more attributes
• Tuple – It is a row of a relation. Tuple can appear in
any order and the relation will still remain the same
relation
• Extension (state) of a relation – Defined as the set of
tuples that appear in that relation at any given instant
of time. Extension is same as view of the table
Terminologies related To
Relational Model
• Intension of a relation – A permanent part of the
relation, independent of time.
• Degree of a relation – Defined as the number of
attributes it contains. It is property of the intension of
a relation
• Cardinality of a relation – Defined as the number of
tuples it contains. It is the property of the extension
of the relation
• Relational database – Defined as the collection of
normalized / structured relations with distinct relation
names
• N-ary relation – A relation with degree N
Structure of Relational Databases
• A relational database consist of collection
of Tables, each is assigned a unique name
• A row in a table represents a relationship
among a set of values
Basic Structure
Let us consider the “account” table. It has three
column headers,
account_number, branch_number, balance
• These headers are attributes
• For each, there is a set of permitted values,
called the DOMAIN of that attribute.
– For attribute branch_name the domain is the set of all
branch names. At physical level the domain of branch
name is string of characters
Let,
• D1 - set of all account no.s
• D2 – set of all branch_names
• D3 - set of all balances
Basic Structure
Any row of “Account” table must consist of a 3tuples(v1, v2, v3)
v1 – account no. (v1 is in D1)
v2 – branch name (v2 is in D2)
v3 – balance (v3 is in D3)
i.e. “Account” is a subset of
D1 x D2x D3
•
A Relation is a subset of Cartesian product of
list of domains
Basic Structure
•
•
•
•
•
•
A TUPLE variable is a variable whose domain is
the set of all tuples
i.e. tuple variable t[account_number] denotes
the value of t on account number attribute
t[account_number] = “A-101” or
t[1] = “A-101”
t is in relation r
For all relations r, the domains of all attributes
of r must be atomic. A domain is Atomic if
elements of the domain are individual units
Null attributes should be eliminated if at all
possible
Database Schema
•
•
•
We must differentiate between
Database Schema
Database instance
A relation corresponds to the programming
language notation of a variable
•
A relational schema corresponds to the
programming language notation type definition
•
A relation instance corresponds to the
programming language notation of a value of a
variable
e.g. Account_schema(account_number,
branch_name, balance)
“Account” is a relation on Account_schema
account(Account_schema) i.e. r(R)
Relational Model Notation
•
•
A relation schema R of degree n is denoted by R(A1, A2,
…, An)
An n-tuple t in a relation r(R) is denoted by t =<v1, v2,
…, vn> where vi is the value corresponding to attribute Ai
For component value of a tuple
–
T[Ai], t.Ai and t[i] refers to the value vi in t for attribute Ai
–
T[Au, Aw, …, Az] and t.(Au, Aw, …, Az) where Au, Aw, …, Az is a
list of attributes from R, refers to the subtuple of values
<vu, vw, …, vz) from t corresponding to the attributes
specified in the list
•
•
•
•
The letter Q, R, S denote relation names
The letter q, r, s denote relation states
The letter t, u, v denote tuples
Name of relation indicates the current set of tuples in
that relation whereas STUDENT(name, rollno,…) refers
relation schema only
Relational algebra
•
•
•
Relational algebra and relational
calculus are formal languages
associated with the relational model
Both operands and results are
relations, so output from one
operation can become input to
another operation
Allows expressions to be nested,
just as in arithmetic. This property
is called closure.
Relational Algebra
•
•
•
Five basic operations in relational
algebra are Selection, Projection,
Cartesian product, Union, and Set
Difference
These perform most of the data
retrieval operations needed
Also have Join, Intersection, and
Division operations, which can be
expressed in terms of 5 basic
operations
Selection (or Restriction)
Assignment Operation
•
•
•
Used to write relational-algebra
expression in parts using
assignment to a temporary relation
variable
Denoted by
Similar to assignment in
programming language
Modification of the Database
•
•
How to add, remove, or change
information in the database
We express database modification
using the assignment operation
Modification of the DB - Deletion
•
•
•
•
Deletion in relational-algebra is much same as
a query
Instead of displaying tuples to the user,
selected tuples are removed from the database
We can delete only whole tuples; we cannot
delete values on only particular attributes
Deletion is expressed by
r
r–E
Where,
r – relation
E – relational-algebra query
Modification of the DB - Insertion
•
•
•
•
To insert data into a relation, we specify a tuple
to be inserted or write a query whose result is
a set of tuples to be inserted
The attribute values for inserted tuples must be
members of the attribute’s domain
Tuples inserted must be of correct arity
Insertion is expressed by
r
rUE
Where,
r – relation
E – relational-algebra expression
Modification of the DB - Updation
•
•
•
•
In some situations we wish to change a value
in a tuple without changing all values in the
tuple
Uses generalized projection for updation
Insertion is expressed by
r
П F1, F2, . . ., Fn(r)
Where,
Fi – ith attribute of r, if the ith attribute is not
updated , or if the attribute is to be updated Fi
is an expression, involving only constants and
the attributes of r, which gives new value to r
To update selected tuples from r
r
П F1, F2, . . ., Fn(бp (r)) U (r - бp (r))
Extended Relational-algebra
operations
•
•
Generalized projection
Outer join
– Left
– Right
– Full
•
Aggregation Functions
Generalized Projection
•
•
It extends the projection operation
by allowing arithmetic functions to
be used in the projection list
Denoted by
П F1, F2, . . ., Fn(E)
Where,
E – relational-algebra expression
F1, F2, . . ., Fn – arithmetic expressions
involving constants and attributes in the
schema of E
Rename Operation
•
•
Used to give a name (alias) to relationalalgebra expressions or relation
Denoted by Greek letter rho
X (E)
•
•
•
E – relational algebra expression
X name given to relational-algebra query
expression
How to Read it
Result of relational-algebra query expression E
under the name X
Rename Operation
•
Assume relational algebra expression E
has n-arity then
X(A1, A2, . . . , An)(E)
•
How to Read it
Result of relational-algebra query
expression E under the name X, and with
the attributes renamed to A1, A2, . . . , An
Aggregate Functions
•
It take collection of values and return a
single value as a result
List of aggregate functions
• Sum – returns the sum of values
• Avg – returns the average of values
• Count – returns no. of elements in the collection
• Min – returns the minimum value in the collection
• Max - returns the maximum value in the collection
Note - To eliminate duplicates, same function
names with the addition of the hyphenated
string distinct
Aggregate Functions
•
To find out the total sum of salaries of all
employees
sum
•
salary
(employee)
Find the number of departments in the
employee relation
count-distinct
departmentno
(employee)
Aggregate Functions on Groups
•
•
•
In some circumstances we would like to apply
aggregate function not only to the single set of
tuples but to several groups, where each group
is set of tuples
Can be done by using an operation called
Grouping
Denoted by aggregation operator Ç
G1, G2, . . ., Gn ÇF1 A1, F2 A2, . . ., Fm Am ( E )
Where,
E – relational-algebra expression
G1, G2, …, Gn – list of attributes on which to
group
Fi – an aggregate function
Ai – an attribute name
Aggregate Functions on Groups
•
The tuples in result of expression E are
partitioned into groups such that
– All tuples in a group have same values for
G1, G2, . . . , Gn
– All tuples in different groups have
different values for G1, G2, . . . , Gn
• For each group (g1, g2, . . ., gn) the result
has a tuple (g1, g2, . . ., gn, a1, a2, . . .,
an)
• Where for each i, ai is the result of applying
aggregate function Fi on the multiset of
values for attribute Ai in the group
View Definition
•
A view is defined using the create
view statement. To define a view, we
must give the view name, and must
state the query that computes the
view. The create view statement is
CREATE VIEW v as <query-expression>
Where
query-expression is any relational-algebra
query expression
View Definition
•
•
A clerk who needs to see all loan data
in the loan relation except loan-amount
CREATE VIEW branch-loan as
Пbranch_name, loan_number(loan)
Insertion using view in loan relation
branch-loan
branch-loan U {(“new panvel”, “L-37”)}
Views Defined Using Other
Views
CREATE VIEW branch-loanno as
Пloan_number(branch-loan)
Types of Keys