Unit - II

17-Nov-20 1
  Concept of relations
 Relation schema & instance
 Keys
 Relational algebra operators
o Selection
o Projection
o Cross product
o Joins
o Division
o Example queries
 Tuple relation calculus
 Domain relational calculus
 Converting the database specification in E/R notation to the relational
schema.

2 17-Nov-20
  It was proposed by E.F. Codd to model data in the form of relations
or tables.
 After designing the conceptual model of Database using ER
diagram, we need to convert the conceptual model in the relational
model which can be implemented using any RDMBS languages like
Oracle SQL, MySQL etc.
 What is Relational Model?
◦ Relational Model represents how data is stored in Relational
Databases.
◦ A relational database stores data in the form of relations (tables).

3 17-Nov-20
 Consider a relation
STUDENT
with attributes
ROLL_NO, NAME, ADDRESS, PHONE and AGE
shown in Table 1.

STUDENT

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
4 SURESH DELHI 18

4 17-Nov-20
  Attribute: Attributes are the properties that define a relation.
e.g.; ROLL_NO, NAME
 Relation Schema: A relation schema represents name of the relation
with its attributes.
e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and
AGE) is relation schema for STUDENT.
 Tuple: Each row in the relation is known as tuple. The above
relation contains 4 tuples, one of which is shown as:

1 RAM DELHI 9455123451 18

5 17-Nov-20
  Each attribute of a relation has a name
 The set of allowed values for each attribute is called the domain of
the attribute
 Attribute values are (normally) required to be atomic, that is,
indivisible
 E.g. multivalued attribute values are not atomic
 E.g. composite attribute values are not atomic
 The special value null is a member of every domain

6 17-Nov-20
  A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema
E.g. Customer-schema =
(customer-name, customer-street, customer-city)
 r(R) is a relation on the relation schema R
E.g. customer (Customer-schema)

7 17-Nov-20
  The set of tuples of a relation at a particular instance of time is called as
relation instance.
 Table shows the relation instance of STUDENT at a particular time.

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
4 SURESH DELHI 18

 It can change whenever there is insertion, deletion or updation in the

database.

8 17-Nov-20
  Order of tuples is irrelevant (tuples may be stored in an arbitrary
order)
 E.g. student relation with unordered tuples

ROLL_NO NAME ADDRESS PHONE AGE

4 SURESH DELHI 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
1 RAM DELHI 9455123451 18

9 17-Nov-20
  A database consists of multiple relations
 Information about an enterprise is broken up into parts, with each relation
storing one part of the information

E.g.: account : stores information about accounts

depositor : stores information about which customer
owns which account
customer : stores information about customers
 Storing all information as a single relation such as
bank(account-number, balance, customer-name, ..)
results in
 repetition of information (e.g. two customers own an account)
 the need for null values (e.g. represent a customer without an account)

10 17-Nov-20
  Keys are defined to easily identify any row of data in a table.
 Let K  R
 K is a superkey of R if values for K are sufficient to identify a unique
tuple of each possible relation r(R) by “possible r” we mean a relation r
that could exist in the enterprise we are modeling.
Example: {customer-name, customer-street} and
{customer-name}
are both superkeys of Customer, if no two customers can possibly have
the same name.
 K is a candidate key if K is minimal
Example: {customer-name} is a candidate key for Customer, since it is
a superkey {assuming no two customers can possibly have the same
name), and no subset of it is a superkey.

11 17-Nov-20
 Primary Key
 Primary key is a candidate key that is most appropriate to become
the main key for any table. It is a key that can uniquely identify each
record in a table.
 For the relation Student we can make the student_id column as the
primary key.

12 17-Nov-20
 Composite Key
 Key that consists of two or more attributes that uniquely identify
any record in a table is called Composite key.
 But the attributes which together form the Composite key are not a
key independentely or individually.
 For the relation Student we can make the student_id column as the
primary key.

In this table student_id and

subject_id together will form the
primary key, hence it is a composite
key.

13 17-Nov-20
 Foreign Key
 A relation schema may have an attribute that corresponds to the
primary key of another relation. The attribute is called a foreign
key.
 For example, a typical foreign key constraint might state that every
employee in the EMPLOYEE table must be a member of an existing
department, as defined in the DEPARTMENT table.
 Only values occurring in the primary key attribute of the
referenced relation may occur in the foreign key attribute of the
referencing relation.

14 17-Nov-20
 Referential integrity
 Referential integrity is the state of a database in which all values of
all foreign keys are valid.
 A foreign key is a column or a set of columns in a table whose
values are required to match at least one primary key or unique key
value of a row in its parent table.
 A referential constraint is the rule that the values of the foreign key
are valid only if one of the following conditions is true:
 They appear as values of a parent key.
 Some component of the foreign key is null.

15 17-Nov-20
 Foreign Key
Example

16 17-Nov-20
  Procedural language
 Six basic operators
 select: 
 project: 
 union: 
 set difference: –
 Cartesian product: x
 rename: 
 The operators take one or two relations as inputs and produce a new
relation as a result.

17 17-Nov-20
  Notation:  p(r)
 p is called the selection predicate
 Defined as:

p(r) = {t | t  r and p(t)}

Where p is a formula in propositional calculus consisting of terms

connected by :  (and),  (or),  (not)
Each term is one of:
<attribute> op <attribute> or <constant>
where op is one of: =, , >, . <. 

 Example of selection:

 city=“Nellore”(students)

18 17-Nov-20
  Relation r
A B C D

  1 7
  5 7
  12 3
  23 10

 A=B ^ D > 5 (r)

A B C D

  1 7
  23 10

19 17-Nov-20
  Notation:
 A1, A2 ,, Ak
(r )
where A1, A2 are attribute names and r is a relation name.
 The result is defined as the relation of k columns obtained by erasing the
columns that are not listed
 Duplicate rows removed from result, since relations are sets
 Example: To display name, city and phno from the relation student

name,city,phno(student)

20 17-Nov-20
  Relation r A B C

 10 1
 20 1
 30 1
 40 2

A C A C
 A,C (r)
 1  1
 1 =  1
 1  2
 2

21 17-Nov-20
  Notation: r  s
 Defined as:
r  s = {t | t  r or t  s}
 For r  s to be valid.
1. r, s must have the same arity (same number of attributes)
2. The attribute domains must be compatible (example: 2nd column
of r deals with the same type of values as does the 2nd
column of s)
 Example: to find all customers with either an account or a loan
customer_name (depositor)  customer_name (borrower)

22 17-Nov-20
  Relation r A B A B

 1  2
 2  3
 1 s
r

A B
 r  s:  1
 2
 1
 3

23 17-Nov-20
  Notation r – s
 Defined as:
r – s = {t | t  r and t  s}

 Set differences must be taken between compatible relations.

 r and s must have the same arity
 attribute domains of r and s must be compatible

24 17-Nov-20
  Relation r A B A B

 1  2
 2  3
 1 s
r

 r – s: A B

 1
 1

25 17-Nov-20
  Notation r x s
 Defined as:
r x s = {t q | t  r and q  s}

 Assume that attributes of r(R) and s(S) are disjoint. (That is, R  S = ).
 If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

26 17-Nov-20
  Relations r, s:
A B C D E

 1  10 a
 10 a
 2
 20 b
r  10 b
s
 r x s:
A B C D E
 1  10 a
 1  10 a
 1  20 b
 1  10 b
 2  10 a
 2  10 a
 2  20 b
 2  10 b

27 17-Nov-20
  Can build expressions using multiple operations
 Example: A=C(r x s)
 rxs
A B C D E
 1  10 a
 1  10 a
 1  20 b
 1  10 b
 2  10 a
 2  10 a
 2  20 b
 2  10 b

 A=C(r x s)
A B C D E
 1  10 a
 2  10 a
 2  20 b
28 17-Nov-20
  Let r and s be relations on schemas R and S respectively.
Then, r s is a relation on schema R  S obtained as follows:
 Consider each pair of tuples tr from r and ts from s.
 If tr and ts have the same value on each of the attributes in R  S, add
a tuple t to the result, where
 t has the same value as tr on r
 t has the same value as ts on s
 Example:
R = (A, B, C, D)
S = (E, B, D)
 Result schema = (A, B, C, D, E)
 r s is defined as:
r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))

29 17-Nov-20
  Relations r, s:

A B C D B D E

 1  a 1 a 
 2  a 3 a 
 4  b 1 a 
 1  a 2 b 
 2  b 3 b 
r s

 r s
A B C D E
 1  a 
 1  a 
 1  a 
 1  a 
 2  b 
30 17-Nov-20
  Notation: r  s
 Suited to queries that include the phrase “for all”.
 Let r and s be relations on schemas R and S respectively where
 R = (A1, …, Am , B1, …, Bn )
 S = (B1, …, Bn)
The result of r  s is a relation on schema
R – S = (A1, …, Am)
r  s = { t | t   R-S (r)   u  s ( tu  r ) }
Where tu means the concatenation of tuples t and u to produce a single
tuple

31 17-Nov-20
  Relations r, s:
A B B
 1 1
 2
 3 2
 1 s
 1
 1
 3
 4
 6
 1
 2
 r  s: A
r



32 17-Nov-20
 Example Queries
 Find the names of all customers who have a loan at the Perryridge
branch.

customer_name (branch_name=“Perryridge”
(borrower.loan_number = loan.loan_number(borrower x loan)))

 Find the names of all customers who have a loan at the

Perryridge branch but do not have an account at any branch of
the bank.

customer_name (branch_name = “Perryridge”

(borrower.loan_number = loan.loan_number(borrower x loan))) –

customer_name(depositor)

Database System Concepts - 5th Edition, Oct 5, 2006 2.33 ©Silberschatz, Korth and Sudarshan
 branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)

account (account_number, branch_name, balance)

loan (loan_number, branch_name, amount)

depositor (customer_name, account_number)

borrower (customer_name, loan_number)

34 17-Nov-20
  Find all loans of over $1200

amount > 1200 (loan)

 Find the loan number for each loan of an amount greater than
$1200

loan_number (amount > 1200 (loan))

 Find the names of all customers who have a loan, an account, or both, from
the bank

customer_name (borrower)  customer_name (depositor)

35 17-Nov-20
  Find the names of all customers who have a loan at the Perryridge
branch.
customer_name (branch_name=“Perryridge”
(borrower.loan_number = loan.loan_number(borrower x loan)))

 Find the names of all customers who have a loan at the

Perryridge branch but do not have an account at any branch of
the bank.
customer_name (branch_name = “Perryridge”
(borrower.loan_number = loan.loan_number(borrower x loan))) –
customer_name(depositor)

36 17-Nov-20
  Relational calculus is a non-procedural query language. In the
non-procedural query language, the user is concerned with the
details of how to obtain the end results.
 The relational calculus tells what to do but never explains how to
do
Types of Relational calculus:

37 17-Nov-20
  The tuple relational calculus is specified to select the tuples in a
relation. In TRC, filtering variable uses the tuples of a relation.
 The result of the relation can have one or more tuples.
 Notation:
{T | P (T)} or {T | Condition (T)}
Where
 T is the resulting tuples.
 P(T) is the condition used to fetch T.
 For example:
{ T.name | Author(T) AND T.article = 'database' }

 Output: This query selects the tuples from the AUTHOR relation.
It returns a tuple with 'name' from Author who has written an
article on 'database'.

38 17-Nov-20
  TRC (tuple relation calculus) can be quantified. In TRC, we can
use Existential (∃) and Universal Quantifiers (∀).

 For example:
{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

 Output: This query selects the tuples from the AUTHOR relation.
It returns a tuple with 'name' from Author who has written an
article on 'database'.

39 17-Nov-20
  The second form of relation is known as Domain relational
calculus. In domain relational calculus, filtering variable uses the
domain of attributes.
 Domain relational calculus uses the same operators as tuple
calculus. It uses logical connectives ∧ (and), ∨ (or) and ┓ (not).
 It uses Existential (∃) and Universal Quantifiers (∀) to bind the
variable.
 Notation:
{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where
 a1, a2 are attributes
 P stands for formula built by inner attributes

40 17-Nov-20
  For example:
{< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}

 Output:
This query will yield the article, page, and subject from the
relational javatpoint, where the subject is a database.

41 17-Nov-20
  The database can be represented using the notations, and these notations
can be reduced to a collection of tables.
 In the database, every entity set or relationship set can be represented in
tabular form.
 The ER diagram is given below:

42 17-Nov-20
 There are some points for converting the ER diagram to the table:
 Entity type becomes a table.
 In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE
forms individual tables.
 All single-valued attribute becomes a column for the table.
 In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the
column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID
form the column of COURSE table and so on.
 A key attribute of the entity type represented by the primary key.
 In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and
LECTURE_ID are the key attribute of the entity.
 The multivalued attribute is represented by a separate table.
 In the student table, a hobby is a multivalued attribute. So it is not possible
to represent multiple values in a single column of STUDENT table. Hence
we create a table STUD_HOBBY with column name STUDENT_ID and
HOBBY. Using both the column, we create a composite key.

43 17-Nov-20
 There are some points for converting the ER diagram to the table:
 Composite attribute represented by components.
 In the given ER diagram, student address is a composite attribute. It contains
CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these
attributes can merge as an individual column.
 Derived attributes are not considered in the table.
 In the STUDENT table, Age is the derived attribute. It can be calculated at
any point of time by calculating the difference between current date and
Date of Birth.
 Using these rules, we can convert the ER diagram to tables and columns and
assign the mapping between the tables.

44 17-Nov-20
 Table structure for the given ER diagram is as below:

45 17-Nov-20
 1. Explain Relational Data model and its concepts.
2. Briefly explain different types of keys in Relational data model
3. Discuss various Relation Algebra Operators in detail.
4. Convert the banking application ER diagram to relational schema.

46 17-Nov-20