Introduction to Schema Refinement

Introduction to Schema Refinement• Different problems may arise when converting

a relation into standard form• They are

• Data redundancy• Update Anomalies• Deletion Anomalies• Insertion Anomalies

Introduction to Schema Refinement• Data Redundancy• Storing the information repeatedly, that is, in

more than one place within a database, can lead to several problems

• Redundancy leads to inconsistency

• Inconsistency generate problem in insertion, deletion and updating

Introduction to Schema Refinement

• Problems Caused by Redundancy• Update Anomalies: If one copy of such repeated

data is updated, an inconsistency is created unless all copies similarly updated.

• Insertion Anomalies: It is not be possible to store certain information unless some other, unrelated, information is stored as well.

• Deletion Anomalies: It may not be possible to delete certain information without losing other, unrelated, information as well.

Introduction to Schema Refinement• Data Redundancy• In the student relation there are information is repeated

several times

• Update Anomalies:• If we change the name ‘Ravi’, it affects all 2 rows having

sname information too• Deletion Anomalies:• Deleting row 2 result in loss of AAA college from the

whole relation• Insertion Anomalies:• Cannot add a row which does not have value

Sid Sname Course Colleg100 Ravi CSE ABC101 Vijay MCA AAA102 Saji EEE BBB103 Ravi EC CCC

Introduction to Schema RefinementNull ValuesNull value leads to wastage of memory spaceNull value have multiple interpretations, such as• The attribute does not apply to this tuple.• The attribute value for this tuple is unknown.• The value is known but absent; that is, it has not

been recorded yet.

Introduction to Schema Refinement

• The Process of Normalisation• Normalisation is a data analysis technique to design a

database system. • It allows the database designer to understand the

current data structures in an organisation. • Furthermore, it aids any future changes and

enhancements to the system.• Normalisation is a technique for producing relational

schema with the following properties:• No Information Redundancy• No Update Anomalies

Functional dependency• It play a main role in designing good database design

from bad database design• A functional dependency (FD) is a constraint between

two sets of attributes in a relation• Describes the relationship between attributes in a

relation. • If A and B are attributes of a relation R, • B is functionally dependent on A • (denoted. A → B), if each value of A in R is associated

with exactly one value of B in R.

Functional dependency• A → B• Determinant: attribute or set of attributes on

the left hand side of the arrow.

• In the above example A is determinant

• Determinant may be attribute or group of attribute

Functional dependency• From the relation Customer

• Cid Cname

Because Cid is the primary key of the tableIt is always uniqueSo Cid uniquely determines the customer name even in the case of duplicate

CnameSo Cname is functionally dependent to Cid

CnamecidIt is not always trueBecause name of the customer may be same for different CidSo Cname not uniquely determines the customer

Cid age trueCid salary trueAgeCid false

Cid Cname Age Salary101 Jeet 65 7000102 Seet 44 8000103 Swet 34 6000104 Abc 23 5000

Functional dependency• Unnormalized form (UNF): A table that contains one or

more repeating groups.

• Repeating group: an attribute or group of attributes within a table that occurs with multiple values in a single row

• An unnormalized relation contains non atomic values

• Example• The row corresponding to Jeet• Have more than one phone no• So this table is unnormalized • relation

Cid Cname Phone

101 Jeet 233567234568

102 Seet 44103 Swet 34104 Abc 23

Functional dependency• First normal form (1NF): A relation in which the

each row and column contains one and only one value.

• Is does not contain multivalued attribute• Every attribute value is atomic• Ie all cells are single values

• A relation is in 1NF if and only if all underlying domains contain atomic values only

• Or• One value is associated with each attribute

Functional dependency• Converting UNF to 1NF• Remove repeating groups(multivalue) • Entering appropriate data in the empty columns

of rows. • For each repeating field value, create a new

tuple

Cid Cname Phone101 Jeet 233567101 Jeet 234568102 Seet 44103 Swet 34104 Abc 23

Cid Cname Phone

101 Jeet 233567234568

102 Seet 44103 Swet 34104 Abc 23

Functional dependency• Types of FD• Full FD

• Partial Dependency

• Transitive Dependency

• Trival and Non-Trival Dependencies

Functional dependency• Types of FD• Full FD• For a relation schema R and FD• X Y, Y is fully functional dependent on X if

there is no Z, where Z is the proper subset of A, such that ZY

• Or• XY is a full FD if the removal of any attribute A

from X removes the dependency

Functional dependency• Types of FD• Full FD• An attribute is fully functionally dependent on a

set of attributes X if it is• Functionally dependent on X, and• Not functionally dependent on any proper

subset of X.

Functional dependency• Types of FD• Partial Dependency• A FD XY is partial dependency if some attribute A can

be removed from X and the dependency sill hold for some attribute, then that dependency is called partial dependency

• Or• if there is some attribute that can be removed from A

and the dependency still holds. • Example• {Cid, Phone} Cname• Is partial because• Cid Cname• Is full FD

Cid Cname Phone101 Jeet 233567101 Jeet 234568102 Seet 44103 Swet 34104 Abc 23

Functional dependency• Types of FD• Transitive dependency: • A condition where A, B and C are attributes of a

relation such that • if A → B and B → C, then C is transitively dependent

on A via B • (provided that A is not functionally dependent on B

or C). • Is • XY• YZ• Then• XZ

Functional dependency• Types of FD• Trival & Non Trival Dependency• Some FD are said to be trival, because they are satisfied by all

relations• Example• A is satisfied by all relations involving attribute A• Similarly ABA is satisfied by all relations involving attribute A• FD is trival if right hand side is a subset of the left hand side

• Non-trival dependency are one that is not trival• XY is non trival if and only if Y X• Example• Car(carno,carname,color,weight) FD of the relation car is• Carnocarname• Carnocolor