See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
GATE
GMAT
CBSE
NCERT
Career
Interview
Railway
UPSC
NID
NIFT-UG
NIFT-PG
PHP
AJAX
JavaScript
Node Js
Shell Script
Research
Database Management System 10CS54 VTU unit-7
UNIT-7 Data base design 2
7.1 Properties of relational decomposition
Normalization Algorithms based on FDs to synthesize 3NF and BCNF describe two desirable
properties (known as properties of decomposition).
Dependency Preservation Property
Lossless join property
Dependency Preservation Property enables us to enforce a constraint on the original relation
from corresponding instances in the smaller relations.
Lossless join property enables us to find any instance of the original relation from
corresponding instances in the smaller relations (Both used by the design algorithms to achieve
desirable decompositions).
A property of decomposition, which ensures that no spurious rows are generated when relations
are reunited through a natural join operation.
7.2 Algorithms for Relational Database Schema Design
Individual relations being in higher normal do not guarantee a good deign Database schema must
posses additional properties to guarantee a good design.
Relation Decomposition and Insufficiency of Normal Forms
Suppose R = { A1, A2, …, An} that includes all the attributes of the database. R is a universal
relation schema, which states that every attribute name is unique. Using FDs, the algorithms
decomposes the universal relation schema R into a set of relation schemas
D = {R1, R2, …, Rn} that will become the relational database schema; D is called a
decomposition of R. Each attribute in R will appear in at least one relation schema Ri in the
decomposition so that no attributes are lost; we have
This is called attribute preservation condition of a decomposition.
7.2.1 Decomposition and Dependency Preservation
We want to preserve dependencies because each dependencies in F represents a constraint on the
database.
We would like to check easily that updates to the database do not result in illegal relations being
created
It would be nice if our design allowed us to check updates without having to compute natural
joins. To know whether joins must be computed, we need to determine what functional
dependencies may be tested by checking each relation individually.
Let F be a set of functional dependencies on schema R. Let D = {R1, R2, …, Rn} be a
decomposition of R. Given a set of dependencies F on R, the projection of F on Ri, Ri(F),
where Ri is a subset of R, is the set of all functional dependencies XY such that attributes in
XY are all contained in Ri. Hence the projection of F on each relation schema Ri in the
decomposition D is the set of FDs in F+, such that all their LHS and RHS attributes are in Ri.
Hence, the projection of F on each relation schema Ri in the decomposition D is the set of
functional dependencies in F+.
((R1(F)) (R2(F)) … (Rm(F)))+ = F+
i.e., the union of the dependencies that hold on each Ri belongs to D be equivalent to closure of F (all possible FDs)
/*Decompose relation, R, with functional dependencies, into relations, R1,..., Rn, with associated
functional dependencies,
F1,..., Fk.
The decomposition is dependency preserving iff:
F
+=(F1 k)
+ */
If each functional dependency specified in F either appeared directly in one of the relation
schema R in the decomposition D or could be inferred from the dependencies that appear in
some R.
7.2.2 Lossless-join Dependency
A property of decomposition, which ensures that no spurious rows are generated when relations
are reunited through a natural join operation.
Lossless-join property refers to when we decompose a relation into two relations - we can rejoin
the resulting relations to produce the original relation.
Decompose relation, R, with functional dependencies, F, into relations, R1 and R2, with
attributes, A1 and A2, and associated functional dependencies, F1 and F2.
Decompositions are projections of relational schemas
times there is the requirement to decompose a relation into more than two
relations. Although rare, these cases are managed by join dependency and 5NF.
7.3 Multivalued Dependencies and Fourth Normal Form (4NF)
4NF associated with a dependency called multi-valued dependency (MVD). MVDs in a
relation are due to first normal form (1NF), which disallows an attribute in a row from having a
set of values.
MVD represents a dependency between attributes (for example, A, B, and C) in a relation, such
that for each value of A there is a set of values for B, and a set of values for C. However, the set
of values for B and C are independent of each other.
MVD between attributes A, B, and C in a relation using the following notation
A B (A multidetermines B)
Formal Definition of Multivalued Dependency
A multivalued dependency (MVD) X Y specified on R, where X, and Y are both
subsets of R and Z = (R – (X Y)) specifies the following restrictions on r(R)
t3[X]=t4[X]=t1[X]=t2[X]
t3[Y] = t1[Y] and t4[Y] = t2[Y]
t3[Z] = t2[Z] and t4[Z] = t1 [Z]
7.3.1 Fourth Normal Form (4NF)
A relation that is in Boyce-Codd Normal Form and contains no MVDs. BCNF to 4NF involves
the removal of the MVD from the relation by placing the attribute(s) in a new relation along with a copy of the determinant(s).
A Relation is in 4NF if it is in 3NF and there is no multivalued dependencies.
7.4 Join Dependencies and 5 NF
A join dependency (JD), denoted by JD{R1, R2, …, Rn}, specified on relation schema R,
specifies a constraint on the states r of R. The constraint states that every legal state r of R should
have a lossless join decomposition into R1, R2, …, Rn; that is, for every such r we have
* (R1(r), (R2(r) … (Rn(r)) = r
Lossless-join property refers to when we decompose a relation into two relations - we can rejoin the resulting relations to produce the original relation. However, sometimes there is the
requirement to decompose a relation into more than two relations. Although rare, these cases are managed by join dependency and 5NF.
5NF (or project-join normal form (PJNF))
A relation that has no join dependency.
The idea behind template dependencies is to specify a template—or example—that defines each constraint or dependency. There are two types of templates: tuple-generating templates and constraint-generating templates. A template consists of a number of hypothesis tuples that are meant to show an example
7.5.2 Domain Key Normal Form
The idea behind domain-key normal form (DKNF) is to specify (theoretically, at least) the
"ultimate normal form" that takes into account all possible types of dependencies and constraints.
A relation is said to be in DKNF if all constraints and dependencies that should hold on the
relation can be enforced simply by enforcing the domain constraints and key constraints on the
relation.
However, because of the difficulty of including complex constraints in a DKNF relation, its
practical utility is limited, since it may be quite difficult to specify general integrity constraints.
For example, consider a relation CAR(MAKE, VIN#) (where VIN# is the vehicle identification
number) and another relation MANUFACTURE(VIN#, COUNTRY) (where COUNTRY is the
country of manufacture). A general constraint may be of the following form: "If the MAKE is
either Toyota or Lexus, then the first character of the VIN# is a "J" if the country of manufacture
is Japan; if the MAKE is Honda or Acura, the second character of the VIN# is a "J" if the
country of manufacture is Japan." There is no simplified way to represent such constraints short
of writing a procedure (or general assertions) to test them.
Editors
Checkout our YouTube channel for video tutorials and learn more about Eduladder.
New updates
Similar Questions
Notes
Database Management Systems - 10CS54 VTU notesManagement And Entrepreneurship Unit-1ADVANCED DBMS 10CS751CE2029 MANAGEMENT OF IRRIGATION SYSTEMSDatabase Management SystemDatabase Management System 10CS54 VTU unit-7TeradataSOFTWARE ENGINEERING(10IS51)Computer Applications in Business NotesCE 2026 TRAFFIC ENGINEERING AND MANAGEMENT