ppt

Document Sample

```					RELATIONAL CALCULUS:
- It is a formal query language where the queries are expressed as variables
and formulas on these varaibles.
- The formula describes the properties required result relation without
specifying the method of evaluating it..

The relational calculus comes in two ways :
1. Tuple relational calculus (TRC)
2. Domain relational calculus(DRC)

1. Tuple relational calculus (TRC)
- It is a non procedural query language . It describes the desired
information without giving a specific procedure for obtaining that information.
A query in the tuple relational calculus is expressed as
{t | P (t) }
- It is the set of all tuples t such that predicate P is true for t
- t is a tuple variable, t[A] denotes the value of tuple t on attribute A
- t  r denotes that tuple t is in relation r
Formal Definition of tuple relational calculus :
A tuple relational calculus is expressed as
{t | P (t) }
where
P - is a formula, several tuple variable may appear in a formula.
- A tuple variable is said to be free variable unless it is quantified by a or  (
there exits,  : for all ).

- Thus in
t loan   s  Customer [t[Branch name]=s[branch name]
- t is a free variable.
- s – is a bound variable.
.
•      A tuple relational formula is built up out of atoms. An atom has one of
the following forms
1. s  r , where s is a tuple variable and r is a relation.
2. s[x] θ u[y], where s and u are tuple , x is an attribute on which s is
defined , y is an attribute on which u is defined,
θ - is a comparison operator. (, , , , , ); we require that
attribute x and y have domains whose member can be compared by θ.

3. s[x] θ c, where s is a tuple variable , x is an attribute on which s is
defined, θ is a comparison operator , and c is a constant in the domain
of attributes x.
we build up formula from atoms by using the following rules.
1. an atom is a formula.
2. if P1 is a formula, then so are  P1 and P2.
3. if P1 and P2 are formulae then so are P1 v P2, P1 P2 and P1 P2.
4. IF P1(s) IS A FORMULA CONTAINING A FREE TUPLE VARIABLE S AND R IS
A RELATION, t r (Q(t)) and t r (Q(t)) .
eg: 1. find all tuples from the depositor relation.
{t | t depositor}
2. Find the loan-number, branch-name, and amount for loans of over \$1200
{t | t  loan  t [amount] >1200}
3. Find the loan number for each loan of an amount greater than \$1200
{t | s  loan (t[loan-number] = s[loan-number]  s [amount] > 1200)}

Safety of Expressions :
- A tuple relation calculus expression may generate an infinite relation.
consider the expression,
{t | t loan} - means, tuples that are not in loan. There are infinite tuples that are not in loan
relation. Hence such tuples of expression should not allowed.

•     To guard against the problem, we restrict the set of allowable expressions to safe
expressions.

•     An expression {t | P(t)} in the tuple relational calculus is safe if every component
of t appears in one of the relations, tuples, or constants that appear in P
– NOTE: this is more than just a syntax condition.
• E.g. { t | t[A]=5  true } is not safe --- it defines an infinite set with
attribute values that do not appear in any relation or tuples or constants
in P.
expressive power of language :
- it is restricted to safe expression is equivalent to the basic relational algebra
operation.( with the operator u,-,x).

2. Domain relational calculus(DRC) .
- It is uses domain variables that take on values from an attributes domain ,
rather than for an entire tuple.

FORMAL DEFINITION :
- An expression in the domain relational calculus is of the form.
{ <x1,x2…..,xn> | P(x1,x2…..,xn)}.
Where x1,x2…..,xn - represent domain variables.
P – formula composed of atom.
An atom in the domain relational calculus has one of the following forms.
1. <x1,x2…..,xn>  r , where r is a relation on n attributes and x1,x2…..,xn are
domain variables or domain constants.
2. X Θ Y , where x and y are domain variables and Θ is a comparision operator
(, , , , , ). Attributes x and y have domains that can be compared by Θ.
3. X Θ C , where x are domain variables and Θ is a comparision operator and C
is a constant in the domain of the attribute for which x is a domain variable.
we build up formula from atoms by using the following rules.
1. an atom is a formula.
2. if P1 is a formula, then so are  P1 and P2.
3. if P1 and P2 are formulae then so are P1 v P2, P1 P2 and P1 P2.
4. IF P1(s) IS A FORMULA CONTAINING A FREE TUPLE VARIABLE S AND R IS A
RELATION, t r (Q(t)) and t r (Q(t).
eg :
1. find the loan number , branch and amount for loans of over \$ 1200 .
{<l,b,a> | <l,b,a>  loan  a>1200}
2.find the laon number for loans with an amount greater than \$1200.
{<1> |  b,a (<l,b,a>  loan a > 1200}
TRIGGERS :
- A triggers is a statement that the system executes automatically as a side
effect of a modification to the database.
To design a trigger mechanism we must meet two requirements.
1. Specify when a trigger is to executed. This is broken up in to an event that
causes the trigger to be checked and as condition that must be satisfied for
trigger execution to proceed .
2. specify the actions to be taken when the trigger executes.

- The database stores triggers just as if they were regular data , so that they are
persistant and are accessible to all database operation.
- once we enter a trigger in to the database , the database system takes on the
resposibility of executing it whenever the specified event occurs and the
corresponding condition is satisfied.
NEED FOR TRIGGER :
- Triggers are useful mechanism for altering humans for starting certain tasks
automatically when certain condition are met .
eg : 1. instead of allowing negative account balance , bank deals with
overdrafts by setting the account balance to zero .
2. creating a loan in the amount of the overdraft . The bank gives this loan
a loan no identical to the account number of the overdrawn account .
3. the condition for executing the trigger is an update to the account
relation that result in a negative balance value.

eg: - suppose that jones withdrawal of some money from an account made the
account balance negative.
- Let t denote the account tuple with a negative balance value . The action
are taken .
1. insert a new tuples s in the loan relation with
s[loan no]       =     t[account no]
s[branch name] =       [branch name]
s[amount]         = - t[balance]
2. insert a new tuple u in the borrower relation with .
u[customer name] = jones
u[loan no] = t[account no]
3. Set t[balance] to 0 .
TRIGGERS IN SQL :
Eg of SQL trigger is given below.
Create trigger overdraft_trigger after update on account new row as n row
for each row
when nrow. Balance <0
begin atomic
insert in to borrower
(select custname, acc_no from depositor where nrow.account_no =
depositor.account _no);
Insert in to loan values
(nrow.account_no, nrow.branch name –nrow . Balance);
update account set balance =0 where account. Account no =nrow.account no;
end
When Not to Use Trigger :
- Trigger should be written with great care, since a trigger error detected at run
time causes the failure of the inset/delete/update statement that set off the
trigger.
- The action of one trigger can set off another trigger .
in worst case , this could lead to an infinite chain of triggering.

STORED PROCEDURES :
- PL/SQL supports two types of subprograms. They are:
1. Procedures
2. Functions.
1. Procedures
- It is a subprogram that performs a specific action.
syntax : create or replace procedure<proc_name>[parameters list] is
<local declaration>
begin
(executable statement)
[exception](exception handlers)
End.
Procedure has two parts :
1. Specification
2. Body
- PROCEDURE SPECIFICATION BEGINS WITH THE KEYWORD PROCEDUREW AND
ENDS WITH THE PROCEDURE NAME OR PARAMETER LIST .
- The procedure body begins and ends with the keyword end.
syntax : sql>exec<proce_name>(parameters);
Types of parameters passed to subprogram.
1. In parameter - it is used to pass values to the subprograms when invoked.
2.out parameter - used to return values to the caller of a subprogram.
3. in out parameter – used to pass initial values to subprogram when invoked
and it also returns updated values to the caller.
FUNCTIONS:
- It is a subprogram that computes a value .
<local declaration>
begin
(executable statement)
[exception](exception handlers)
End.
SECURITY :
- It is a protection from malicious attempts to steal or modify data .
SECURITY SHOULD BE PROVIDED AT FOLLOWING LEVELS :
1. Database system level :
- Use authentication and authorization mechanisms to allow specific users
access only to required data.
2. Operating system level :
- operating system super users can do anything they want to the database
good operating system level security is required.
3. Network level :
- Use encryption to prevent eavesdropping(unauthorized reading of
messages )
- masquerading (pretending to be authorized user)
4. Physical level :
- physical access to computer allows destruction of data by intruders;
traditional lock and key security is needed.
- Computer must also be protected from floods , fire, etc.
Human level :
- Users must be screened to ensure that an authorized users do not give
- users should be trained on password selection and secreacy.

DYNAMIC SQL :
- The dynamic SQL components of SQL allows program to construct and
submit SQL queries at run time.
- Using dynamic SQL , programs can create Sql Queries as strings at run time
and can either have them executed immediately. Or have them prepared for
susequent use.
TYPES OF CLIENT/SERVER ARCHITECTURE :
There are two types of client/server architectures.
1. Two tiered architecture.
2. Three tiered architecture.

1. Two tiered architecture.
- Client /server environment that divides the application in to the graphical
user interface(client) and the Data(server) .
- The GUI can be developed using a product like Visual basic or power
builder and the DATA can be setup using the a database management system.
1. The front end (client) of the client/server system consists of tools such as
forms, report writer and GUI facilities.
2. The back end(server) manages access to structure,query evaluation and
optimization, concurrency control and recovery.
3. The interface between the client and the server is through SQL or through an
application program.
4. In two tiered architecure , business rules are either stored at client side or at
server side.
2. THREE TIERED ARCHITECTURE :
- Today the most widely accepted form of client/server system is the
three – tiered architecture. The component of this architecture are.
1. The presentation (GUI) or user services.
2. Busineess rule or business services.
3.Data server or Data services.

1. All layers interact with each other . The client works as query head and
rules define how the query ought to be addressed and worked on.
2. The database provides the answer and solutions to the client's query as
determined by the rule base and the content of the database.
INTEGRITY :
- Integrity constraints guard against accidental damage to the database , by
ensuring that authorized changes to the database do not result in a loss of
data consistency.
Eg: 1. An account balance cannot be null .
2. No two students can have same roll no.
TYPES OF CONSTRAINTS :
1. Null value concept :
- While creating table, if a row lacks a data value for a particular column
,that values is said to be null.
- column of any data types may contain null values unless the column
was defined as not null when the table was created.
PRINCIPLES OF NULL VALUES :
1. Setting a null value is appropriate when the actual value is unknown , or
when a value would not be meaningful .
2. A null value is not equivalent to a value of zero.
3. A null value will evaluate to null in any expression . Eg: null multiplied by 10
is null .
4. When a column name is defined as not null, then that column becomes a
mandatory column. It implies that the user is forced to enter data into that
column.
PRIMARY KEY CONCEPT :
- A primary key is one or more column in atable used to uniquely identify
each row in the table.
- primary key values must not be null and must be unique across the
column.
- A multicolumn primary key is called a composite primary key .
eg: create table student (roll no number(5) primary key, name varchar(25) not
null,address varchar(25) not null, ph no varchar(15));

UNIQUE KEY CONCEPT :
- Unique key is used to ensure that the information in the column for each
record is unique as with licence number.
- A table may have many unique keys.
eg: create table special_customer(customer_code number(5) Primary key,
customer_name varcahr(25) NOT NULL, customer_address varchar(30) Not
null,License _no varchar(15) constraint uk_license _no UNIQUE);
Default value concept :
- At the time of column creation a default value can be assigned to it. When
the user is loading a record with values and leaves this column empty.
- The DBA will automatically load this column with the default value
specified. The data type of the default value should match the data type of
the column.
- use can use the default clause to specify any default value you want.
eg: Create table special_customer(emp_code number(5) Primary key,
emp_name varcahr(25) NOT NULL, emp_address varchar(30) ph_no varchar
(15), married char (1) DEFAULT ‘M’; PRIMARY KEY (Emp _code));

FOREIGN KEY CONCEPT :
- Foreign key represents relationship between tables.
- The existence of a foreign key implies that the table with the foreign key is
related to the primary key table from which the foreign key is derived.
CHECK INTEGRITY CONSTRAINTS :
- It defines a condition that every row must satisfy . There can be more
than one CHECK constraint on a column and the CHECK constraint can be
defined at column as well as the table level.
At the column level, the constraints is defined by
eg: DeptID Number (2) CONSTRAINT Ck-deptID
CHECK((DeptID>=10) and(DeptID<=99));
and at the table level by
CONSTRAINT ck –DepID
CHECK((DepID>=10)and (DepID<=99));

RESTRICTION ON CHECK CONSTRAINTS :
- The condition must be a boolean expression that can be evaluated using
the values in the row being inserted or updated.
- The condition cannot contain subqueries or sequences.
- The condition cannot include the SYS DATE, UID,USER or USER ENV SQL
function .
REFERENTIAL INTEGRITY :
-A value that appears in one relation for a given set of attributes also appears for
a certain set of attributes in another relation. Called referential integrity.

REFERENTIAL INTEGRITY in the E –R model :
- Referential integrity constraints arise frequently. If we derive our relational
database scheme by constructing tables from E-R diagrams then every relation
arising from a relationship set has referential integrity constraint.

E1
E2

R

EN-1
EN
N – ARY RELATIONSHIP SET :
- N - ARY relationship set R, relating entity sets E1,E2,…. EN.
- Let ki denote the primary key of Ei . The attribute of the relation scheme for
relationship set R include K1 U K2 U ….. Ukn.
- Each Ki in the scheme for R is a foreign key that leads to a refential integrity
constraint.
-Another source of referential integrity constraint are weak entity sets . The
relation scheme for a weak entity set must include the primary key of the
entity set on which it depends.
- Thus the relation scheme for each weak entity set includes a foreign key that
leads to a refential integrity constraint .
Referential integrity in Sql.
- Using SQL , primary key, candidate key, and foreign key are defined as part
of the create table statement .
create table deposit(branch_name char(15), acc_no char(10), cust_ name
char(20),not null, balance integer, primary key (acc_no, cust_name), foreign
(branch name )reference branch, foreign key (cust_name) reference
customer );
Views
- A view is object that gives the user a logical view of data from an
underlying table or tables.
- you can restrict what users can view by allowing them to see only a few
attributes/column from a table.
Views may be created for the following reasons:
1. simplifies queries .
2. can be queried as a base table.
3. provides data security.
Creation of views :
syn : CREATE VIEW viewname as SELECT Columnname, column FROM table
name WHERE columnname = EXPRESSION LIST;
EG : create view V_Book as select title , author_name from book
view created
SELECTING DATA FROM A VIEW :
EG: sql>select title from v_book where author_name =basu
UPDATABLE VIEWS :
- Views can also be used for data manipulation. i.e. the user can perform
insert, update, and the delete operations on the view.
- The view on which data manipulation can be done called updatable views.
For the view to be updatable , it should meet following criteria.
1. The view must be created on a single table.
2. The primary key column of the table should be included in the view.
3. The select statement used for creating a view should not include
Dinstint,Group by, or Having CLAUSE
4. The select statement used for creating a view should not include subqueries.

DESTROYING A VIEW :
- A view can be dropped by using the DROP VIEW command.
syntax : DROP VIEW viewname;
eg : DROP VIEW V_BOOK;
JOIN :
- Join is a query in which data is retrieved from two or more table. A join
matches data from two or more table ,based on the values of one or more
columns in each table.
DIFFERENT TYPES OF JOIN :
1. INNER JOIN
2. OUTER JOIN
3. NATURAL JOIN

1. INNER JOIN:
- Inner join returns the matching rows from the tables that are being
joined.
UNIT -3
NORMALIZATION :
- It is an essential part of database design. A good understanding of the
semantics of data helps the designer to build efficient design using the concept
of normalization .

Purpose of Normalization :
- Minimize redundancy in data.
- Remove insert, delete and update anamoly during database activities.
- Reduce the need to reorganize data when it is modified or enhanced.
- Normalization reduces a complex user view to a set of small and stable
subgroups of fields/relations.
- This purpose helps to design a logical data model knowh as conceptual data
model .
Normalization forms :
Different normalization forms are
1) First normal form (1NF): A relation is said to be in the first normal form if it is
already in unnormalized form and it has no repeating group.
2). Second normal form (2NF) : A relation is said to be in the second normal form if
it is already in the first normal form and it has no partial dependency.
3) Third normal form (3NF) : A relation is said to be in the THIRD normal form if it
is already in second normal form and it has no transitive dependency .

4) Boyce –codd normal form (BCNF) : A relation is said to be in boyce-codd normal
form if it is already in the third normal form and every deteminant is a candidate
key. It is a stronger version of 3NF.

5. Fourth normal form (4NF): A relation is said to be in the fourth normal form if
it is already in BCNF and it has no multivalued dependency .
6. Fifth normal form (5nf): A relation is said to be in 5NF if it is already in 3NF and
it has no join dependency.
TERMINOLOGIES USED IN NORMAL FORMS :
The different technologies used in various normal form are
1. Partial dependency :
- In the relation having more than one key field . A subset of non –key fields
may depend on all the key fields but another subset/ a particular non – key
field may depend on only one of the key fields (i.e. may not depend on all the
key fields). Such dependency called partial dependency.

2. Transitive :
- In a relation , there may be dependency among non-key fields .such
dependency called transitive dependency.
3. Determinant :
- A determinant is any field (simple field or composite field) on which
some other field is fully functionally dependent.
Case problem :
Let us consider the invoice report of alpha book house
ALPHA BOOK HOUSE
PUNE - 413001
CUSTOMER NO    : 1052
CUSTOMER NAME : BETA SCHOOL OF COMPUTER SCIENCE
ADDRESS        : SHIVAJI NAGAR , PUNE -01
ISBN      BOOK     Author    Author_ QTY       Price(Rs   Amount
TITLE    _name     country           )
81-203-   DOS      PK.SINH   INDIA     5       250        1250
5                  A
0-112-6   DBMS     KORTH     U.S.A     6       300        1800
1-213-9   SIMULA   GORDO     USA       5       100        500
TION     N
GRAND TOTAL        3550
Normalization of invoice report upto 3NF.
- The invoice report is represented in a form of relation The relation is named
as invoice .this is in unnormalized form.
- 1) invoice (cust_no,cust_name,cust_add, ISBN, title, authorname,
author_country ,qty, unit price).
FIRST NORMAL FORM (1NF) :
- The relation is in INF if it has no repeating groups.
- In the invoice relation , the fields in the inner most of parentheses put
together is known as a repeating group.
- This will result in redundancy of data for the first three fields . This
redundancy willl lead to inconsistency of data.
- hence it is divided in to two sub relation shown below to remove the
repeating group.
3). Customer_Book (Cust_No,ISBN, Title, Author_Name,
Author_Country,Qty,Unit_Price)
- Now each of the above relations (i.e. relatio 2 and relation 3)is in 1NF.
SECOND NORMAL FORM(2NF):
- 2NF removes partial dependency among attributes of relation .
- In relation 2, the number of key fields is only one and hence there is no
scope for partial dependency.
- The absence of partial dependency in relation 2 takes it into 2NF without
any modification.
- In relation 3, the number of key fields are two.

CUST_NO                          TiTLE

QTY
Author_
name

Author_
ISBN                          country

DEPENDENCY DIAGRAM OF RELATION 3                      Unit_price
- QTY depends on Cust_no and ISBN , But remaining non-key fields
(Title,author_name,author_country, unit_price)depend only on ISBN . Thus
there exits partial dependency.
- The existence of partial dependency will result in to insertion update, and
deletion anomaly.
1. insertion anomaly :
- In relation 3 , if we want to insert data of a new book
(ISBN,TITLE,AUTHOR_NAME,AUTHOR_COUNTRY,UNIT_PRICE) there must be
atleast one customer.
- This means that the data of a new book can be entered into relation 3
only when the first customer buys the book.
2. Update anomaly :
- In relation 3, if we want to change any of the non-key fields , like Title
Author_name, it will result into inconsistency because the same is available
in more than one record.
Deletion anomaly :
- In relation 3, if book is purchased by only one customer, then the book data
will be lost when we delete that record after fully satisfying that customer
order.
Hence relation 3 is divided in to two relations.
4. Sales (Cust_no,ISBN,QTY)
5. Book_Author(ISBN,TITLE,AUTHOR_NAME,AUTHOR_COUNTRY,UNIT_PRICE)
- Above two relations (i.e.relation 4 and 5) are now in 2NF.

THIRD NORMAL FORM :
- Third normal form removes transitive dependency among attributes of
relation .
DEPENDENCY DIAGRAM FOR RELATION 5
Author
Author_
ISBN                     TITLE                                  _count
name
ry

UNIT_PRICE
- In relation 4, there is only one non key fields . So there is no question of
dependency between non key fields.
- There is no transitive dependency hence relation 4 is in 3NF.
- In relation 2, There is no dependency between the non-key fields . This means
that it has no transitive dependency , hence relation 2 is also in 3nf.
- In relation 5, author countery depends on author name. This means that
relation 5 has transitive dependency.
- The existence of transitive dependency will result into insert, update and delete
anomaly.
1. insertion anomaly:
- Consider the book company has resident authors who are in the process of
developing new books.
- It is difficult to include the author details in relation 5. this means that there
should be atleast one published book to insert the details of a resident author.
Update anomaly :
- If author country is to be modified, then it is necessary to modify number
of tuples as the same data is in number of tuples.

Deltion anomaly :
- If the only one book of a resident author is not reprinted , then the
respective author’s data will be lost. Hence to over come all these anomalies,
relation 5 is subdivided into two relations.
6.) book (ISBN.title ,unit_price.author_name)
7) Author (Author_ Name, Author_country.)
- In relation 6, Author_Name is underlined with dotted line to indicate that it is
a foreign key.
BOYCE – CODD NORMAL FORM (BCNF) :
- It is deals with relational tables which has multiple candidate keys,
composite candidate keys, and candidate keys that overlapped.
- BCNF is based on the concept of determinant. A Determinant is a column
on which some of the columns are fully functional dependent.
- A relational table is in BCNF IF EVERY DETERMINANT IS CANDIDATE KEY.

Case 1 : Multiple candidate keys.
- Consider following relational table.

A#       Aname Aqualif Astatus titled      royalty
ication
100      Arora    PHD      10      T1      3000
110      Sharma M.TEC      20      T2      4000
H
CASE 1 : Multiple candidate keys.
- Suppose author’s status(Astatus) depends on his/her qualification .if
author is phd his status (A status) is 10.
- If M.TECH it is 20 and so on. This relation has three determinants: A#,
Titled, Aqualification. But only (A# , Titled) combination is a candidate key;
- so this relation is not in BCNF . For a relation to be in BCNF each
determinants must be a candidate key.

CASE 2 : Composite candidate keys.
-

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 31 posted: 9/2/2011 language: English pages: 38