Oracle for B.sc Student part 4

*Relation calculus: Relational calculus is non procedural language for manipulating relations. Relational calculus defines the solution to query as a relation set. The relation definition consists of target list, the attribute in a solution and quantifying statement. Relation calculus uses symbolic logic and Boolean connectives (and or not) to link condition which may be true or false. The existential quantifiers are used to an instance of some type exist and universal quantifiers are used if the condition is true for every instance of specified type. There are two types of relational calculus.
(1) Tuple Relation calculus: The tuple relational calculus is based on specifying a number of tuple variables. Each tuple variable usually exist over a particular data base relation i.e. the variable may take as its value any individual tuple from that relation. A simple tuple relational calculus query is of the form: {t / cond (t)} where t = tuple variable, cond (t) = conditional expression involving.
The result of above query is the set of all tuples (t) that satisfy cond (t).
For example: If we want to find all teachers whose salary is above 10000 then we can write following tuple calculation expression.

{|t| Teacher (t) And t. salary > 10000}
In above example, the condition specifies that the range relation of tuple variable t is Teacher. Each Teacher tuple t that satisfies the condition t. salary > 10000 will be retrieve. In tuple calculus expression we must specify the following information. (i) For each tuple variable t, the range Relation R(t). (ii) A condition to select perticualr combination of tuples. (iii) A set of attribute which are to be retrieved known as requested attributes.
(2) Domain relation calculus: It differs in the type of variable rather than having variable range. Top form a relation in domain relation calculus of degree n for a query result we must have n domain variable, one for each attribute. An expression of the domain is as shown below: {X1,X2,---,Xn | COND(X1,X2---,Xn,Xn+1, Xn+2,---Xn+m)}
where, X1, X2,-----Xn and Xn+1, Xn+2, -----Xn+m ARE domain variables. And COND is a condition of domain relational calculus. The formula is made up of atom. Atoms evaluate to specific set of values, all the truth values of the atoms.

* Decomposition: It’s refers to breaking down of one table into multiple tables. There are two types of decomposition. (1) Loss decomposition. (2) Lossless or non loss decomposition.
To understand the importance of decomposition we should know the idea of redundancy means unwanted, uncontrolled duplication of data. Redundancy has also other side of effects such as loss of data integrity and data consistency. {Consistency: constant data. Integrity: correct data}

We can see here some redundancy i.e. student name appears two times. Thus it is not better idea to store student name in a database. This creates the problem in decomposition of table. Now we decomposed above table into two separate tables having names student table and sub table with primary key Roll no. & subject code respectively.

Here the problem of redundancy is reduced and hence the loss of data. Thus the loss of data due to decomposition is called loss decomposition i.e. our main target is that decomposition with reassemble the data into original form called decompositions we must obtain original form. in above example the decomposition is lossy decomposition since it lost the data while decomposition. If we created three table as result of decomposition instead of two as follows.

If we recomposed all above three tables we obtained the original database design. Hence the decomposition in non loss.

* Modification of database: In this modification, we address how to add, remove or change information in the database. We express database modification by using the assignment operation.
(1) Deletion: We express a delete request in much the same way as a query. However, instead of displaying tuples to the user, we remove the selected tuples from the database. We can delete only whole tuples, we cannot delete values on only particular attributes. In relational algebra a deletion is expressed by, r ← r-E.
where r is a relation and E is a relational algebra query. Example: Delete all of smiths account records.
Depositor ← depositor – S customer – name = “smith” (depositor).
(2) Insertion: To insert data into relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted. Obliviously, the attribute values for inserted tuples must be member of the attribute domain. Similarly, tuples inserted must be of the correct entity. The relational algebra expresses as insertion by
r ← r U E.
where r is a relation and E is a relation algebra expression. Example: suppose that we wish to insert the fact that neha 
has 1000 Rs. in account A – 1986 at the wardha branch then we write,
account ← account U {(A—1986, “wardha”, 1000)}. Depositor ←  depositor U {(“neha”, A-1986)}.
(3) Updating: In certain situations we may wish to change a value in a tuple without changing all values in a tuples. We can use the generalized projection operator to do this task. R ← ∏ f1, f2, ----f(r).
where each fi is either the ith attribute of r, if the ith attribute is not updated, or of the attribute is to be updated. Fi is an expression, involving only constants and the attributes or r, that gives the new values for the attribute. Example: Suppose that interest payments are being made and that all balances are to be increased by 5 parallel. We write, account ←  ∏ account – number, branch – name, balance * 1.05 (account).
* Extended Relational Algebra Operations: The basic relational algebra operations have been extended in several ways. A simple extension is to allow arithmetic operation as part of projection. An important extension is to allow aggregate operations such as computing the sum of the elements of a set or their average. Another important extension is the outer join operation which allows relational algebra expression to deal with null values which model missing information.
* Normal forms:
We try to solve the problem confronting a database designer. He is given a conceptual design of a system and he has to translate that design into a set of relations which satisfy the objectives of database management i.e. which have as many desirable features as possible. Normalization is a technique for translating a conceptual design into a set a of well designed tables. It’s a step by step process for converting complex data structures into simpler well designed relations (tables). Depending on the characteristic of tables they are classified into various normal forms.
First normal form (1NF): A Table relation R is in 1NF if, no two rows are identical, and each table entry is single valued. For ex: the relation employee is in 1NF.

                                                  <<< Previous page  Next Page >>>