UNIT-3 B.

Sc KHK

Relational Model
Introduction, CODD Rules, relational data model, concept of key, relational integrity, relational
algebra, relational algebra operations, advantages of relational algebra, limitations of relational
algebra, relational calculus, tuple relational calculus, domain relational Calculus (DRC). QBE

Explain CODD’s Relational Database Rules ?

 In 1985, E.F. CODD has published a list of 12 rules to define a relational database
system.
 Information
The information in a relational database must be logically represented as column
values in rows within the tables.
 Guaranteed Access
Every value in a table is guaranteed to be accessible through a combination of
table name, primary key value and column name.
 Systematic Treatment of Nulls
Nulls can represent and treated in a systematic way independent of the data type.
 Dynamic On-Line Catalog based on relational model
The metadata must be stored and managed as ordinary data i.e., in tables. Such
data must be available to authorized users using the standard database relational
language.
 Comprehensive Data Sublanguage
The relational database supports many languages. However it must support one
well –defined declarative language with support for data definition, view
definition, data manipulation, and transaction management.
 View Updating
Any view that is theoretically updatable must be updatable through the system.
 High-Level Insert, Update and Delete
The database must support set-level inserts, updates and deletes.

VasaviDegreeCollege 1
UNIT-3 B.Sc KHK

 Physical Data Independence

Application programs and ad hoc facilities are logically unaffected when physical
access methods or storage structures are changed.
 Logical Data Independence
Application programs and ad hoc facilities are logically unaffected when changes
made to the table structures that preserve the original table values.
 Integrity Independence
All the relational integrity constraints must be definable in the relational language
and stored in the system catalog not at the application level.
 Distribution Independence
The end users and application programs are unaware and unaffected by the data
location.
 Nonsubversion
If the system supports low-level access to the data, there must not be a way to
bypass the integrity rules of the database.
 Rule Zero
All the 12 rules are based on the notion that in order for a database to be
considered relational, it must use its relational facilities exclusively to manage the
database.

What is Key in relational data model ? Explain different types of Keys ?

Or

Explain the concept of KEYS in Relational Data Model?

KEYS:
 In relational model the Keys are important because they are used to ensure that each row
in a table is uniquely identifiable. These keys are also used to establish relationships
among tables and to ensure the integrity of the data.
 A key consists of one or more attributes that determines other attributes.

VasaviDegreeCollege 2
UNIT-3 B.Sc KHK

 The Key‟s role is based on the concept of Determination. For ex : the statement “A
determines B” means that if you know the value of attribute A, you can look up the value
of attribute B.
 The principle of determination is very important because it is used in the definition of a
central relational database concept known as Functional Dependence which can be
defined as : The attribute B is functionally dependent on A if A determines B.
 Any attribute that is part of a key is known as a Key Attribute.
 There are five types of keys
 Super key
 Candidate key
 Primary key
 Secondary key
 Foreign key
Super Key
Any key that uniquely identifies each row i.e., the super key functionally
determines all of a row‟s attributes. In the STUDENT table, the super key would
be
STU_NUM

STU_NUM, STU_LNAME

STU_NUM, STU_LNAME, STU_INIT

STU_NUM with or without additional attributes can be a super key even when
the additional attributes are redundant.
Candidate Key
It is a minimal super key. It can be described as a super key without unnecessary
attributes i.e., a minimal super key. A super key that does not contain a subset of
attributes that is itself a super key is called as candidate key.
For example two students share same Lname , Fname, if the student‟s PIN had
been included as one of the attributes in the student table the PIN STU_NUM
both have been candidate keys.

VasaviDegreeCollege 3
UNIT-3 B.Sc KHK

Primary Key
A candidate key selected to uniquely identify all other attribute values in any
given row cannot contain null values. Candidate key which is chosen to be the
unique row identifier i.e., each primary key value must be unique to ensure that
each row is uniquely identified by the primary key. In that case the table is said to
exhibit entity integrity. To maintain entity integrity a null value in permitted in the
primary key.
Secondary Key
It is defined as a key that is used strictly for data retrieval purposes. Suppose,
customer data are stored in a CUSTOMER table in which the customer number is
the primary key. The customers doesn‟t remember their numbers. So based on
customer last name and phone number the data is retrieved. So the combination of
customer‟s last name and phone number is the secondary key.
Foreign Key
An attribute or combination of attributes in one table whose values must either
match the primary key in another table.
For example the COUID is the primary key in the COURSE table and it occurs as
a foreign key in the STUDENT table.

Write all about Relational data model Concepts ?

The relational Model of Data is based on the concept of a Relation. A Relation is a
mathematical concept based on the ideas of sets. The model was first proposed by Dr.
E.F. Codd of IBM in 1970 .

VasaviDegreeCollege 4
UNIT-3 B.Sc KHK

Relation:
It is a table which has rows and columns in the data model, where rows represent records
and columns represents the attributes.
Tuples:
A single row of a table, which contains a single record for that relation, is called a tuple.
Attributes:
Columns in a table are called attributes of the relation
Cardinality of a relation:
The number of tuples in a relation determines its cardinality. In this case, the relation has
a cardinality of 4.
Degree of a relation:
Each column in the tuple is called an attribute. The number of attributes in a relation
determines its degree. The relation has a degree of 5.
Domain:
A domain definition specifies the kind of data represented by the attribute.
Properties of a Relation A relation with N columns and M rows (tuples) is said to be of degree N
and cardinality M. This is Student_Table which shows the relation of degree three and
cardinality five.

The characteristic properties of a relation are as follows:

 All entries in a given column are of the same kind or type.
 Attributes are unordered - The order of columns in a relation is immaterial. The display
of a relation in tabular form is free to arrange columns in any order.

VasaviDegreeCollege 5
UNIT-3 B.Sc KHK

 No duplicate tuples. A relation cannot contain two or more tuples which have the same
values for all the attributes. i.e., In any relation, every row is unique.
 There is only one value for each attribute of a tuple. The tuple should have only one
value.
 Tuples are unordered. The order of rows in a relation is immaterial. One is free to
display a relation in any convenient way.

Explain Relational or Referential Integrity?

Referential integrity is a relational database concept in which multiple tables share a
relationship based on the data stored in the tables, and that relationship must remain
consistent. Relational Integrity is also called Referential Integrity. Tables are related
using data values. Usually, these are the values of numeric keys instead of text fields.
Every database relation is involving just two tables, a parent table and a child table.
Relational databases implement RI using primary keys and foreign keys. The parent
table contains the primary key and the child table contains the foreign key which
references (or "points" to, if you prefer) the parent table.
Referential Integrity rule:
The referential integrity rule says that the database must not contain any unmatched
foreign key values. This implies that:
 A row may not be added to a table with a foreign key unless the referenced value exists in
the referenced table.

VasaviDegreeCollege 6
UNIT-3 B.Sc KHK

 If the value in a table that's referenced by a foreign key is changed (or the entire row is
deleted), the rows in the table with the foreign key must not be "leave alone."

In general, there are three options available when a referenced primary key value changes or
a row is deleted. The options are:
 Disallow. The change is completely disallowed.
 Cascade. For updates, the change is cascaded to all dependent tables. For deletions, the
rows in all dependent tables are deleted.
 Nullify. For deletions, the dependent foreign key values are set to Null.

CREATE TABLE child ( columndefinitions,

[constraintname] FOREIGN KEY (fkcolumns) REFERENCES parent [(pkcolumns)]
ON DELETE
CASCADE | SET NULL | SET DEFAULT | NO ACTION
ON UPDATE
CASCADE | SET NULL | SET DEFAULT | NO ACTION
)

create table branch (

bname char(15) not null,
bcity char(30),
assets integer,
primary key (bname)
check (assets >= 0))

create table account (

account# char(10) not null,
bname char(15),
balance integer,
primary key (account#)
foreign key (bname) references branch,

VasaviDegreeCollege 7
UNIT-3 B.Sc KHK

check (balance >= 0))

Relational Algebra
A query language is a language in which user requests to retrieve some information from the
database. The query languages are considered as higher level languages than programming
languages. Query languages are of two types,
 Procedural Language
 Non-Procedural Language
 In procedural language, the user has to describe the specific procedure to retrieve the
information from the database. Example: The Relational Algebra is a procedural
language.
 In non-procedural language, the user retrieves the information from the database without
describing the specific procedure to retrieve it. Example: The Tuple Relational
Calculus(TRC) and the Domain Relational Calculus(DRC) are non-procedural languages.
Limitations Of Relational Algebra
Although relational algebra seems powerful enough for most practical purposes, there are
some simple and natural operators on relations which cannot be expressed by relational
algebra.

Write a short note on relational algebra and its operations?

The relational algebra is a procedural query language. It consists of a set of operations
that take one or two relations (tables) as input and produce a new relation, on the request
of the user to retrieve the specific information, as the output. The relational algebra
contains the following operations,
1) Selection 2) Projection 3) Union
4) Rename 5) Set-Difference 6) Cartesian product
7) Intersection 8) Join 9) Divide
The Selection, Projection and Rename operations are called unary operations because
they operate only on one relation. The other operations operate on pairs of relations and
are therefore called binary operations.

VasaviDegreeCollege 8
UNIT-3 B.Sc KHK

Selection ( ) operation:
The Selection is a relational algebra operation that uses a condition to select rows from a
relation. A new relation (output) is created from another existing relation by selecting
only rows requested by the user that satisfy a specified condition. The lower greek letter
„sigma ‟ is used to denote selection operation.
Syntax: Selection condition ( relation_name )
For example, to list the regno > 102 from Student_Table.
σRegno>102(Student_table)

Projection (π) operation:

The projection is a relational algebra operation that creates a new relation by deleting
columns from an existing relation i.e., a new relation (output) is created from another
existing relation by selecting only those columns requested by the user from projection
and is denoted by letter pi (π )
For example, to get a name from Student_Table.

π Name (Student_Table)

VasaviDegreeCollege 9
UNIT-3 B.Sc KHK

Union
It combines the similar columns from two tables into one resultant table. All columns that
are participating in the UNION operation should be Union Compatible. This operator
combines the records from both the tables into one. If there are duplicate values as a
result, then it eliminates the duplicate. The resulting records will be from both table and
distinct.

πCust-name (Borrower) U π Cust-name (Depositor)

Intersection:
This operator is used to pick the records from both the tables which are common to them.
In other words it picks only the duplicate records from the tables. Even though it selects
duplicate records from the table, each duplicate record will be displayed only once in the
result set. It should have UNION Compatible columns to run the query with this operator.
π Cust-name (Borrower) ∩ π Cust-name (Depositor)

Minus
This operator is used to display the records that are present only in the first table or query,
and doesn‟t present in second table / query. It basically subtracts the first query results
from the second.

VasaviDegreeCollege 10
UNIT-3 B.Sc KHK

πCust-name (Borrower) - πCust-name (Depositor)

PRODUCT
This command would show all possible pairs of rows from both tables being used. This
command can also be referred to as the Cartesian Product. Consider the following two
tables

The Cartesian product will result

JOIN
Join is combination of Cartesian product followed by selection process.This operator
takes two or more tables and combines them into one table. This can be used in
combination with other commands to get specific information. There are several types of
the Join command. The Natural Join, Equijion, Theta Join, Left Outer Join and Right
Outer Join.
DIVIDE
DIVIDE has specific requirements of the table. One of the tables can only have one
column and the other table must have two columns only.

VasaviDegreeCollege 11
UNIT-3 B.Sc KHK

Write all about Relational Calculus?

Relational calculus is an alternative to relational algebra. In contrast to the algebra, which
is procedural, the relational calculus is non-procedural or declarative.
It allows user to describe the set of answers without showing procedure about how they
should be computed. Relational calculus has a big influence on the design of commercial
query languages such as SQL and QBE (Query-by Example).
Relational calculus are of two types,
 Tuple Relational Calculus (TRC)
 Domain Relational Calculus (DRC)
Variables in TRC takes tuples (rows) as values and TRC had strong influence on SQL.
Variables in DRC takes fields (attributes) as values and DRC had strong influence on
QBE.
 Tuple Relational Calculus (TRC)
The tuple relational calculus, is a non-procedural query language because it gives
the desired information without showing procedure about how they should be computed.
A query in Tuple Relational Calculus (TRC) is expressed as { T | p(T) }
Where,
T - tuple variable,
P(T) - „p‟ is a condition or formula that is true for „T‟.
In addition to that we use,
T[A]- to denote the value of tuple T on attribute A and
T. r - to denote that tuple T is in relation r.
Example TRC query
student (rollNo, name, degree, year, sex, deptNo, advisor )
department (deptId, name, hod, phone )
Obtain the details of all girl students in the Maths Dept (deptId = 2)
{s | student(s)^ s.sex=„F‟^ s.deptNo=2}
 Domain Relational Calculus (DRC)
A Domain Relational Calculus (DRC) is a variable that comes in the range of the values
of domain (data types) of some columns (attributes). A Domain Relational Calculus
query has the form,

VasaviDegreeCollege 12
UNIT-3 B.Sc KHK

{ < x1, x2, …., xn > | p( < x1, x2, …., xn > ) }
Where, each xi is either a domain variable or a constant and p(< x1, x2, …., xn >)
denotes a DRC formula.
A DRC formula is defined in a manner that is very similar to the definition of a TRC
formula. The main difference is that the variables are domain variables.
Example:
Find all loan details in loan relation.
{ < N, D, A > | < N, D, A > ∃ loan }
This query gives all loan details such as loan_no, loan_date, loan_amt for all loan table in
a bank. Each column is represented with an initials such as N-loan_no, D –loan_date, A –
loan_amt. The condition < N, D, A > ∃ loan ensures that the domain variables N, D, A
are restricted to the column domain.

 Query By Example (QBE)

Query by example (QBE) is a query method implemented in most database systems, most
notably for relational databases. QBE was created by Moshe Zloof at IBM in the 1970s in
parallel to SQL‟s development. It is a graphical query language where users can input
commands into a table like conditions and example elements. It's a common feature in
most database programs.
The principle of QBE is that it is merely an abstraction between the user and the real
query that the database system will receive. In the background, the user's query is
transformed into a database manipulation language form such as SQL, and it is this SQL
statement that will be executed in the background.

VasaviDegreeCollege 13
UNIT-3 B.Sc KHK

VasaviDegreeCollege 14