DATA BASE MANAGEMENT SYSTEMS
UNIT-3
SCHEMA REFINEMENT AND FUNCTIONAL DEPENDENCY
Unit 3 contents at a glance:
1. Introduction to schema refinement
2. functional dependencies
.
1. Schema Refinement:
The Schema Refinement refers to refine the schema by using some technique. The best
technique of schema refinement is decomposition.
Normalisation or Schema Refinement is a technique of organizing the data in
the database. It is a systematic approach of decomposing tables to eliminate data
redundancy and undesirable characteristics like Insertion, Update and Deletion
Anomalies.
Redundancy refers to repetition of same data or duplicate copies of same data stored in
different locations.
Anomalies: Anomalies refers to the problems occurred after poorly planned and
normalised databases where all the data is stored in one table which is sometimes called
a flat file database.
Anomalies or problems facing without normalization(problems due to
redundancy) :
Anomalies refers to the problems occurred after poorly planned and unnormalised
databases where all the data is stored in one table which is sometimes called a flat file
database. Let us consider such type of schema –
Here all the data is stored in a single table which causes redundancy of data or say
anomalies as SID and Sname are repeated once for same CID . Let us discuss
anomalies one by one.
Due to redundancy of data we may get the following problems, those are-
1. insertion anomalies : It may not be possible to store some information unless
some other information is stored as well.
2. redundant storage: some information is stored repeatedly
3. update anomalies: If one copy of redundant data is updated, then inconsistency is
1
DATA BASE MANAGEMENT SYSTEMS
created unless all redundant copies of data are updated.
4. deletion anomalies: It may not be possible to delete some information without
losing some other information as well.
Problem in updation / updation anomaly – If there is updation in the fee from 5000 to
7000, then we have to update FEE column in all the rows, else data will become
inconsistent.
Insertion Anomaly and Deletion Anomaly- These anomalies exist only due to
redundancy, otherwise they do not exist.
2
DATA BASE MANAGEMENT SYSTEMS
InsertionAnomalies: New course is introduced C4, But no student is there who is having
C4 subject.
Because of insertion of some data, It is forced to insert some other dummy data.
Deletion Anomaly :
Deletion of S3 student cause the deletion of course.
Because of deletion of some data forced to delete some other useful data.
Solutions To Anomalies : Decomposition of Tables – Schema Refinement
3
DATA BASE MANAGEMENT SYSTEMS
There are some Anomalies in this again –
What is the Solution ??
Solution : decomposing into relations as shown below
TO AVOID REDUNDANCY and problems due to redundancy, we use refinement
technique called DECOMPOSITION.
Decomposition:- Process of decomposing a larger relation into smaller relations.
Each of smaller relations contain subset of attributes of original relation.
Functional dependencies:
Functional dependency is a relationship that exist when one attribute uniquely
determines another attribute.
Functional dependency is a form of integrity constraint that can identify schema
with redundant storage problems and to suggest refinement.
A functional dependency A B in a relation holds true if two tuples having the same value
of attribute A also have the same value of attribute B
IF t1.X=t2.X then t1.Y=t2.Y where t1,t2 are tuples and X,Y are attributes.
4
DATA BASE MANAGEMENT SYSTEMS
Reasoning about functional dependencies:
Armstrong Axioms :
Armstrong axioms defines the set of rules for reasoning about functional
dependencies and also to infer all the functional dependencies on a relational database.
Various axioms rules or inference rules:
Primary axioms:
secondary or derived axioms:
Attribute closure: Attribute closure of an attribute set can be defined as set of attributes
which can be functionally determined from it.
NOTE:
To find attribute closure of an attribute set-
1) add elements of attribute set to the result set.
2) recursively add elements to the result set which can be functionally determined from
the elements of result set.
5
DATA BASE MANAGEMENT SYSTEMS
Types of functional dependencies:
1) Trivial functional dependency:-If X Y is a functional dependency where Y subset
X, these type of FD’s called as trivial functional dependency.
2) Non-trivial functional dependency:-If X Y and Y is not subset of X then it is called
non-trivial functional dependency.
3) Completely non-trivial functional dependency:-If X Y and X∩Y=Ф(null) then it is
called completely non-trivial functional dependency.
Prime and non-prime attributes
Attributes which are parts of any candidate key of relation are called as prime attribute,
others are non-prime attributes.
Candidate Key:
Candidate Key is minimal set of attributes of a relation which can be used to identify a
tuple uniquely.
Consider student table: student(sno, sname,sphone,age)
we can take sno as candidate key. we can have more than 1 candidate key in a
table. types of candidate keys:
1. simple(having only one attribute)
2. composite(having multiple attributes as candidate key)
Super Key:
Super Key is set of attributes of a relation which can be used to identify a tuple uniquely.
• Adding zero or more attributes to candidate key generates super key.
• A candidate key is a super key but vice versa is not true.
Consider student table: student(sno,
sname,sphone,age) we can take sno, (sno, sname)
as super key
6
DATA BASE MANAGEMENT SYSTEMS
Finding candidate keys problems:
7
DATA BASE MANAGEMENT SYSTEMS
