Showing Page:
1/5
Assignment
Database Management Systems
Department Information Technology, Division of Engineering,
Directorate of Distance Learning Education
G.C University, Faisalabad
Showing Page:
2/5
Q : What is Functional Dependencies? Explain different types of functional dependencies
used in normal forms? Also give the examples of each with tables
A functional dependency (FD) is a kind of Ie that generalizes the concept of a key. Let R be a
relation scherna and let ..¥" and Y be nonernpty sets of attributes in R. We say that an instance r
of R satisfies the FDX ~ }i 1 if the following holds for every pair of tuples tl and t2 in r-.
If t1.X = t2 ..X, then tl.}T = t2.Y'".
W (~ use the notation tl.X to refer to the projection of tuple t1 onto the attributes
in .<\'", in a natural extension of our TIlC notation (see Chapter 4) t.a
for referring to attribute a of tuple t. An FD X ----7 Yessentially says that if two
tuples agree on the values in attributes X, they 111Ust also agree on the values
in attributes Y. illustrates the renaming of the FD AB ----7 C by showing an instance
that satisfies this dependency. The first two tuples show that an FD is not the
same as a key constraint: Although the FD is not violated, AB is clearly not
a key for the relation. The third and fourth tuples illustrate that if two tuples
differ in either the A field or the B field, they can differ in the C field without
violating the FD. On the other hand, if we add a tuple (aI, bl, c2, dl) to the
instance shown in this figure, the resulting instance would violate the FD; to
see this violation, compare the first tuple in the figure with the new tuple.
A
B
C
D
A1
B1
C1
D2
A1
B2
C2
D1
A2
B1
C3
E1
An Instance that Satisfies AB -Jo C
Ilecall that a legal instance of a relation nUlst satisfy all specified les, including
all specified FDs. As noted in Section 3.2, Ies rIlust be identified and specified
ba...sed on the sernantics of the real-world enterprise being n1odeled. By looking
at an instance of a relation, we rnight be able to tell that a certain FD does not
hold. I-Iowever; we C<-:l.Tl never deduce that an FD docs hold by looking at one
or 1I10re instances of the relation, beca,use an FD, like other les, is a staternent
about all possible legal instances of the relation.
Dependency Diagrams
A dependency diagram consists of the attribute names and all functional dependencies in a
given table. The dependency diagram of Supplier table is.
Showing Page:
3/5
Types of Dependence:
Fully Functional Dependence (FFD)
Fully Functional Dependence (FFD) is defined, as Attribute Y is FFD on attribute" X, if it is FD on
X and not FD on any proper subset of X. For example, in relation Supplier, different cities may
have the same status. It may be possible that cities like Amritsar, Jalandhar may have the same
status 10.
So, the City is not FD on Status.
But, the combination of Sno, Status can give only one corresponding City ,because Sno" is unique.
Thus,
(Sno, Status) à City
It means city is FD on composite attribute (Sno, Status) however City is not fully functional
dependent on this composite attribute, which is explained below:
(Sno, Status) à City
X Y
Here Y is FD on X, but X has two proper subsets Sno and Status; city is· FD .on one proper subset
.of X i.e. Sno
Sno à City
According to 'FFD definition Y must not be FD .on any proper subset of X, but here City is FD in
one subset .of X i.e. Sno, so City is not FFD on (Sno, Status)
Consider another case of SP table:
Here, Qty is FD on combination of Sna, Pno.
(Sno, Pno) à Qty
X Y
Here, X has two proper subsets Sno and Pna
Qty is not FD on Sno, because one Sna can supply mare than .one quantity.
Showing Page:
4/5
Qty is also not FD on Pno, because .one Pna may be supplied many times by different suppliers
with different .or same quantities.
So, Qty is FFD and composite attribute of (Sno, Pno) à Qty.
Other Functional Dependencies
There are same rather types of functional dependencies, which play a vital rule during the process
.of normalization of data.
Candidate Functional Dependency
A candidate functional dependency is a functional dependency that includes all attributes of the
table. It should also be noted that a well-fanned dependency diagram must have at least one
candidate functional dependency, and that there can be more than .one candidate functional
dependency for a given dependency diagram.
Q4: What is indexing and what are the different kinds of indexing in detail?
We know that the data is stored as records. Each record has a key field, which helps make it
uniquely recognized.
Indexing is a technique of data structure to efficiently retrieve records database files based on
some of the attributes that have made indexing data. Indexing systems database is similar to what
we see in books.
Indexing is defined based on their attributes indexing. Indexing can be of the following types -
Index primary - primary index is defined in a data file sorted. The data file is ordered on a key
field. The key field is usually the primary key of the relationship.
Index secondary - secondary index can be generated from a field that is a candidate key and has a
unique value for each record, or a non-key with duplicate values.
The group index - Index clustering is defined in a data file sorted. The data file is ordered on a
non-key field.
The orderly indexing is of two types -
dense index
Index low
Dense Index
In dense index, there is an index record for every search key value in the database. This makes
searching faster but requires more space to store index records itself. Index records contain search
key value and a pointer to the actual record on the disk.
Sparse Index
In the sparse index, index records are not created for each search key. An index register here
contains a search key and a current pointer to the data on the disk. To find a record, it is first
necessary by the index register and get to the actual location of the data. If the data we are looking
Showing Page:
5/5