8/8/2019 RE2801b55

1/11

ASSIGNMENT NO. 3

SUBMITTED TO:-G.S PUSHKARNA SUBMITTED BY:-

SHIVANI SINGH RE2801 B55

Sol -:1

No we cant achieve 2nd normal form without using the concept of1st normal form.this can be shown in following example.

Second normal form applies only to tables with composite primary

keys, that is, tables with a primary key composed of two or more

columns. A 1NF table with a single column primary key is

automatically in at least 2NF.

A second normal form (2NF) is a table that is already in 1NF

and in which the values in each non-primary-key column can be

worked out from the values in all the columns that makes up the

primary key.

A table in 1NF can be converted into 2NF by removing the

columns that can be worked out from only part of the primary

key. These columns are placed in a new table along with a copy ofthe part of the primary key that they can be worked out from.

Part 2

Ans:- First normal form (1NF). This is the "basic" level of

normalization and generally corresponds to the definition of

8/8/2019 RE2801b55

2/11

database name

* It contains two-dimensional tables with rows and columns.

* Each column corresponds to a sub-object or an attribute of

the object represented by the entire table.

* Each row represents a unique instance of that sub-object or

attribute and must be different in some way from any other row

(that is, no duplicate rows are possible).

* All entries in any column must be of the same kind. For

example, in the column labeled "Customer," only customer namesor numbers are permitted.

Second normal form (2NF). At this level of normalization, each

column in a table that is not a determiner of the contents of

another column must itself be a function of the other columns in

the table. For example, in a table with three columns containing

customer ID, product sold, and price of the product when sold, the

price would be a function of the customer ID (entitled to a

discount) and the specific product.

Sol2If more than one attributes of a single relation have relationshipsthen this is termed as functional dependency because oneattributes depends on the other for providing complete detail. It isassociated with the normalization because normalization is atechnique that is used to remove these functional dependenciesin order to represent the information in simple way or in shape oftables.

8/8/2019 RE2801b55

3/11

Functional Dependencies are fundamental to the process ofNormalization Functional Dependency describes therelationship between attributes(columns) in a table.For example, ifA and B are attributes of a table, B is functionally

dependent on A, if each value ofA is associated with exactly onevalue ofB (so, you can say, 'A functionally determines B').

Functional dependency between A and B

Attribute or group of attributes on the left hand side of the arrowof a functional dependency is refered to as 'determinant'

Simple example would be StaffID functionally determinesPosition in the above tables.

Functional Dependency can be classified as follows:

Full Functional dependency Indicates that ifA and B areattributes(columns)of a table, B is fully functionallydependent on A ifB is functionally dependent on A ,but noton any proper subset ofA.E.g. StaffID---->BranchID

Partial Functional Dependency Indicates that ifA and Bare attributes of a table , B is partially dependent on A ifthere is some attribute that can be removed from A and yet

the dependency still holds.Say for Ex, consider the following functional dependencythat exists in the Tbl_Stafftable:StaffID,Name -------> BranchIDBranchID is functionally dependent on a subset of A(StaffID,Name), namely StaffID.

8/8/2019 RE2801b55

4/11

Transitive Functional Dependency: A condition where A, B and C are attributes of a table such that ifA isfunctionally dependent on B and B is functionally dependenton C then C is Transitively dependent on A via B.

Say for Ex, consider the following functional dependenciesthat exists in the Tbl_Staff_Branch table:StaffID---->Name,Sex,Position,Sal,BranchID,Br_AddressBranchID----->Br_AddressSo, StaffID attribute functionally determines Br_Address viaBranchID attribute.

Normalization:-Database Normalization is a step wise formalprocess that allows us to decompose Database Tables in such away that both Data Redundancy and Update Anomalies(see above

for more info on update anomalies) are minimised.It makes use ofFunctional Dependencies that exist in a table(relation, more formally) and the primary key or Candidate Keysin analysing the tables.

Three normal forms were initially proposed called First normalForm (1NF), Second normal Form (2NF), and Third normal Form(3NF).Subsequently R.Boyce and E.F.Codd introduced a strongerdefinition of 3NF called Boyce-Codd Normal Form(BCNF).

With the exception of 1NF, all these normal forms are based onFunctional dependencies among the attributes of a table. Highernormal forms that go beyond BCNF were introduced later such asFourth Normal Form (4NF) and Fifth Normal Form (5NF). Howeverthese later normal forms deal with situations that are very rare.

First Normal Form (1NF) The only thing that is requiredfor a table to be in 1NF is to contain only atomic values(intersection of each row and column should contain one andonly one value).this is sometimes referred to as : Eliminate

Repeating groups.

Second Normal Form (2NF) A Table is said to be in 2NF ifit is in 1NF and there are no partial dependencies i.e. everynon primary key attribute of the Table is fully functionallydependent on the primary key.

8/8/2019 RE2801b55

5/11

Third Normal Form (3NF) A Table that is in 1NF and 2NFand in which no non primary key attribute is transitivelydependent on primary key. Boyce-codd Normal Form(BCNF) A Table is in BCNF if and only if every

determinant(it is an attribute or a group of attributes onwhich some other attribute is fully functionally dependent,see functional dependency described above) is a candidatekey. BCNF is a stronger form of 3NF.

The difference between 3NF and BCNF is that for aFunctional dependency A--->B, 3NF allows this dependencyin a table if attribute B is a primary key attribute andattribute A is not a candidate key, where as BCNF insiststhat for this dependency to remain in a table, attribute Amust be a candidate key.

Normalization Process

8/8/2019 RE2801b55

6/11

Fourth Normal Form (4NF) 4NF is a stronger normalform than BCNF as it prevents Tables from containingnontrivial Multi-Valued Dependencies (MVDs) and hence dataredendancy.

The Normalization of BCNF Tables to 4NF involves theremoval of MVDs from the Table by placing the attribute(s)in a new Table along with the copy of the determinant(s).

Fifth Normal Form(5NF) 5NF is also called Project-JoinNormal Form(PJRF) and specifies that a 5NF Table has noJoin dependency.

Q3.A relation R(A,B,C,D,E,F) have the following set of functionaldependency:A->CD,B->C,F->DE,F->A.Is the decomposition of R inR1(A,B,C),R2(A,F,D) and R3(E,F) dependency reversing?

Sol- The relation R(A,B,C,D,E,F) have the set of dependency amongthe their attributes as:According to the given functional dependency:

F->CDBecause A->CD and F->AAnd also F->CD and F->DESo that F->D

Therefore the relation R2(A,F,D) is a correct decomposition.And also F->E so we can make the relation R3(E,F).Now the remaining attributes A,B,C and B->C , so there is anotherrelation form that is R1(A,B,C).So all the three decomposition of relation R in R1, R2 and R3 are

correct according to the rule of functio0nal dependency.

Partially

commited

commited

8/8/2019 RE2801b55

7/11

1) Active:-The initial ;the transcation stays in this state while it isexecuting.2) Partially commited:-After the final statement has beenexecuted.

3) Failed:-After the discovery that normal execution can no longerproceed.4) Aborted :-after the transcation has been rolled back and thedata base has been restored to its state of the transcation.5) commited:-After successful completation.For example:-If a transcation added $20 to an account ,the compensatingtranscation would subtract $20 from the account .however ,it isnot always possible to create such a compensating transcation

.therefore ,the responsibility of writing and executing acompensating transcation is left to the user ,and is handled by thedatabase system.

A process is failed in only two case1)it can restart the transcation ,but only transcation was abortedas result of some hardware or software error that was not createdthrough the internal logic of the transcation .A restartedtranscation is considered to be a new transcation.2)it can kill the transcation .it usually does so because of someinternal logical error that can be corrected only by rewriting theapplication program ,or because the input was bad ,or becausethe desired data were not found in the database.Sol-:5If a schedule s can be transformed into a schedule s by a seriesof swap of non conflicting instruction. We say s and s are conflict

active

failed aborted

8/8/2019 RE2801b55

8/11

equivalence.The concept of conflict equivalence lead to the concept of conflictserializability .we say that a schedule s is conflict serializability ifit is conflict equivalent to a serial schedule.

For example Transcation t1 transcation Read (a)A=a-50;Write(a);Read (b);B:=b+50;Write b

Read(a)Temp=a*.1;

A:=a-temp;Write (a);Read (b);B:=b+temp;Write (b)

This transcation is conflict serializable with the transcation Transcation t1 transcatioRead (a)A=a-50;

Write(a);

Read (b);B:=b+50;Write b

Read(a)Temp=a*.1;A:=a-temp;Write (a);

Read (b);B:=b+temp;Write (b)

Because we are able to find a serial transcation similar to the 1st

transcatin by swapping read(a) and write (b) because these arethe swapping of different data item which has no effect ontranscation and then swapping and then read b with read a

8/8/2019 RE2801b55

9/11

because in case of operation on similar data only read operationhas no effect on swapping .so by doing two swapping we showthat table 1 transcation and table 2 transcation are conflictserializable.

NOW VIEW SERIALIZABILITY:-The concept of view equivalence leads to the concept of viewserializable ,we say that a schedule s is view seriazable if its viewequivalent to serial schedule.Basic steps in showing view serializability:-1)for each data item Q ,if transcation Ti,reads the initial value of Qin schedule S then transcation Ti ,in schedule S also read theinitial value of Q.

2)for each data item Q ,if transcation Ti executes read(q) inschedule S ,and if the value was produced by a write(Q) operationexecuted by transcation Tj,

Then the read (Q) operation of transcation Ti ,must in scheduleS ,also read the value of Q that was produced by the same write(q) operation of transcation Tj.

3)for each data item Q ,the transcation (if any) that perform finalwrite(Q) operation in schedule S must perform the final write(Q)

operation in schedule S.

Example

Transcation T3 transcation T4Read(Q)

Write (Q)Write(Q)

Transcation T3 transcation T4transcation T6Read(Q)

Write (Q)Write(Q)

Write(Q)The schedule is view serializable with above transcation .it is view

8/8/2019 RE2801b55

10/11

equivalent to the serial schedule since the oneread(Q) instruction reads the initial value of Q in both scheduleand T6 perform the final write of Q in both schedule.

Transcation T4 and T6 perform write(Q) operation without having

performed a read(Q) operation .write of this sort is called blindwrites .

Sol 6

ACID (atomicity, consistency, isolation, and durability) is anacronym and mnemonic device for learning and remembering thefour primary attributes ensured to any transaction by a

transaction manager (which is also called a transaction monitor).These attributes are:

Atomicity. In a transaction involving two or more discrete piecesof information, either all of the pieces are committed or none are.

Consistency. A transaction either creates a new and valid state ofdata, or, if any failure occurs, returns all data to its state beforethe transaction was started.

Isolation. A transaction in process and not yet committed mustremain isolated from any other transaction.

Durability. Committed data is saved by the system such that,even in the event of a failure and system restart, the data isavailable in its correct state.

Sol 6

The ACID concept is described in ISO/IEC 10026-1:1992 Section 4.Each of these attributes can be measured against a benchmark.In general, however, a transaction manager or monitor isdesigned to realize the ACID concept. In a distributed system, oneway to achieve ACID is to use a two-phase commit (2PC), whichensures that all involved sites must commit to transactioncompletion or none do, and the transaction is rolled back

8/8/2019 RE2801b55

11/11

All Database systems which include transaction supportimplement ACID properties to ensure the integrity of thedatabase. ACID stands for Atomicity, Consistency, Isolation andDurability

Atomicity: Each transaction is said to be atomic. If one part ofthe transaction fails, the entire transaction fails. Modifications onthe data in the database either fail or succeed.Consistency: This property ensures that only valid data will be

written to the database. If, for some reason, a transaction isexecuted that violates the databases consistency rules, theentire transaction will be rolled back and the database will berestored to a state consistent with those rules.Isolation: It requires that multiple transactions occurring at the

same time not impact each others execution.Durability: It ensures that any transaction committed to thedatabase will not be lost.