Database Management System

(DBMS)

Avinash V. Gondal
email: [email protected]

1
 Module 6
Relational–Database
Design

2
 6. Relational–Database Design
Contents :
• Design guidelines for relational schema
• Functional dependencies
• Normal Forms :
1NF
2NF
3NF
BCNF and
4NF
3
 Informal Design
Guidelines for Relational
Databases

4
 1 Informal Design Guidelines for Relational
Databases (1)
• What is relational database design?
– The grouping of attributes to form "good" relation
schemas.
• Two levels of relation schemas
– The logical "user view" level.
– The storage "base relation" level.
• Design is concerned mainly with base relations.
• What are the criteria for "good" base relations?

5
 Informal Design Guidelines for Relational
Databases (2)
• We first discuss informal guidelines for good relational design.
• Then we discuss formal concepts of functional dependencies and
normal forms:
– 1NF (First Normal Form)
– 2NF (Second Normal Form)
– 3NF (Third Normal Form)
– BCNF (Boyce-Codd Normal Form)
• Additional types of dependencies, further normal forms :
– 4NF (Fourth Normal Form)

6
1.1 Semantics of the Relation Attributes
• GUIDELINE 1: Informally, each tuple in a relation
should represent one entity or relationship instance.
(Applies to individual relations and their attributes).
– Attributes of different entities (EMPLOYEEs, DEPARTMENTs,
PROJECTs) should not be mixed in the same relation.
– Only foreign keys should be used to refer to other entities.
– Entity and relationship attributes should be kept apart as much as
possible.
• Bottom Line: Design a schema that can be explained easily
relation by relation. The semantics of attributes should be easy to
interpret.

7
A simplified COMPANY relational database schema

8
9
1.2 Redundant Information in Tuples and
Update Anomalies
• Information is stored redundantly
– Wastes storage
– Causes problems with update anomalies
• Insertion anomalies
• Deletion anomalies
• Modification anomalies

10
EXAMPLE OF AN UPDATE ANOMALY

• Consider the relation:

– EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
• Update Anomaly:
– Changing the name of project number P1 from
―Billing‖ to ―Customer-Accounting‖ may cause this
update to be made for all 100 employees working on
project P1.

11
EXAMPLE OF AN INSERT ANOMALY

• Consider the relation:

– EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
• Insert Anomaly:
– Cannot insert a project unless an employee is
assigned to it.
• Conversely
– Cannot insert an employee unless a he/she is
assigned to a project.

12
EXAMPLE OF AN DELETE ANOMALY

• Consider the relation:

– EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
• Delete Anomaly:
– When a project is deleted, it will result in deleting all
the employees who work on that project.
– Alternately, if an employee is the sole employee on a
project, deleting that employee would result in
deleting the corresponding project.

13
Two relation schemas suffering from
update anomalies

14
Base Relations EMP_DEPT and EMP_PROJ formed
after a Natural Join : with redundant information

15
Guideline to Redundant Information in Tuples
and Update Anomalies
• GUIDELINE 2:
– Design a schema that does not suffer from the
insertion, deletion and update anomalies.
– If there are any anomalies present, then note them so
that applications can be made to take them into
account.

16
 1.3 Null Values in Tuples
• GUIDELINE 3:
– Relations should be designed such that their tuples
will have as few NULL values as possible.
– Attributes that are NULL frequently could be placed
in separate relations (with the primary key).
• Reasons for nulls:
– Attribute not applicable or invalid.
– Attribute value unknown (may exist).
– Value known to exist, but unavailable.

17
 1.4 Spurious Tuples
• Bad designs for a relational database may result in
erroneous results for certain JOIN operations.
• The "lossless join" property is used to guarantee
meaningful results for join operations.

• GUIDELINE 4:
– The relations should be designed to satisfy the
lossless join condition.
– No spurious tuples should be generated by doing a
natural-join of any relations.

18
 Spurious Tuples (2)
• There are two important properties of decompositions:
a) Non-additive or losslessness of the corresponding join
b) Preservation of the functional dependencies.

• Note that:
– Property (a) is extremely important and cannot be sacrificed.
– Property (b) is less stringent and may be sacrificed.

19
Pitfalls in Relational
Database Design

20
Pitfalls in Relational Database Design
• The bad database design may lead to :
– Repetition of information.
– Inability to represent certain information.
• The design goals for a relational database are :
– Avoid redundant data.
– Ensure the relationships among attributes are
represented.
– Facilitate the checking of updates for violation of
database integrity constraints.

21
 Problems caused by Redundancy
• Storing the same information redundantly, that is in
more than one place within a database can lead to
several problems:
– Redundant Storage: Some information is stored
repeatedly.
– Update Anomalies: If one copy of such repeated data is
updated, an inconsistency is created unless all copies are
similarly updated.
– Insertion Anomalies: It may 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 some other, unrelated,
information as well. 22
 Problems caused by Redundancy
• Consider a relation :
Hourly_Emps (Ssn, name, lot, rating, hourly_wages, hourly_worked)
– Key is Ssn
– Hourly_wages attribute is determined by the rating
attribute. That is for a given rating value, there is only
one permissible hourly_wages value.
– This integrity constraint (IC) is an example of functional
dependency.
Ssn Name Lot Rating Hourly_wages Hours_worked
12345 Nikit 48 8 10 40
12346 Aksahy 22 8 10 30
12347 Mayur 35 5 7 30
12348 Vikas 35 5 7 32
12349 Nitin 35 8 10 40 23
 Problems caused by Redundancy
• The redundancy has some negative consequences :
– Redundant Storage: The rating value 8 corresponds to the
hourly wage 10, and this association is repeated three
times.
– Update Anomalies: The hourly_wage in the first tuple
could be updated without making a similar change in the
second tuple.
– Insertion Anomalies: We cannot insert a tuple for an
employee unless we know the hourly wage for the
employee's rating value.
– Deletion Anomalies: If we delete all tuples with a given
rating value (e.g., we delete the tuples for Mayur and
Vikas) we lose the association between that rating value
and its hourly wage value. 24
 Can null values help?
• Waste of storage space due to NULLs and the difficulty
of performing selections, aggregation operations, and
joins due to NULL values.
• Null values cannot help eliminate redundant storage or
update anomalies.
• The insertion anomaly in the previous slide may be
solved by using null values, null values cannot address
all insertion anomalies: we cannot record the hourly
wages for a rating unless there is an employee with that
rating (primary key field cannot store null).
• Null values can not solve deletion anomalies either.
25
 Use of Decompositions
• Redundancy arises when a relational schema forces an
association between attributes that is not natural.
• Functional dependencies (and, for that matter, other
ICs) can be used to identify such situations and suggest
refinements to the schema.
• The essential idea is that many problems arising from
redundancy can be addressed by replacing a relation
with a collection of ‗smaller‘ relations.
• A decomposition of a relation schema R consists of
replacing the relation schema by two (or more) relation
schemas that each contain a subset of the attributes of R
and together include all attributes in R. 26
 Use of Decompositions
• Example : we can decompose the Hourly_Emps relation
into two relations :
Hourly_Emp2 (Ssn, name, lot, rating, hours_worked)
Wages (rating, hourly_wages)
Ssn Name Lot Rating Hours_worked
12345 Nikit 48 8 40
12346 Aksahy 22 8 30
12347 Mayur 35 5 30
12348 Vikas 35 5 32
12349 Nitin 35 8 40

Rating Hourly_wages
8 10
5 7
27
 Problems Related to Decomposition
• Unless we are careful, decomposing a relation schema
can create more problems than it solves.
• Two Important questions must be asked repeatedly:
– Do we need to decompose a relation?
– What problems (if any) does the decomposition cause?
• To help with the first question, several normal forms
have been proposed for relations. If a relation schema is
in one of these normal forms, we know that certain
kinds of problems cannot arise.
• Considering the normal form of a given relation schema
can help us to decide whether or not to decompose it
further. 28
 Problems Related to Decomposition
• With respect to the second question, two properties of
decompositions are of particular interest :
– The lossless-join property enables us to recover any instance
of the decomposed relation from corresponding instances of
the smaller relations.
– The dependency-preservation property enables us to enforce
any constraint on the original relation by simply enforcing
some constraints on each of the smaller relations. That is, we
need not perform joins of the smaller relations to check
whether a constraint on the original relation is violated.

29
 Relational DB Design Process
• Decide whether a particular relation R is in ―good‖
form.
• In the case that a relation R is not in ―good‖ form,
decompose it into a set of relations {R1, R2, ..., Rn} such
that
– each relation is in good form .
– the decomposition is a lossless-join decomposition.
• Our theory is based on:
– functional dependencies.
– multivalued dependencies.

30
Functional Dependencies

31
 Functional Dependencies
• A functional dependency is a constraint between two
sets of attributes from the database.
• Definition :
• A functional dependency, denoted by X → Y, between
two sets of attributes X and Y that are subsets of R
specifies a constraint on the possible tuples that can
form a relation state r of R.
• The constraint is that, for any two tuples t1 and t2 in r
that have t1[X] = t2[X], they must also have t1[Y] =
t2[Y].

32
 Functional Dependencies
• This means that the values of the Y component of a
tuple in r depend on, or are determined by, the values of
the X component; alternatively, the values of the X
component of a tuple uniquely (or functionally)
determine the values of the Y component.
• We also say that there is a functional dependency from
X to Y, or that Y is functionally dependent on X.
• The abbreviation for functional dependency is FD or
f.d.
• The set of attributes X is called the left-hand side of the
FD, and Y is called the right-hand side.
33
 Functional Dependencies
• Thus, X functionally determines Y in a relation schema
R if, and only if, whenever two tuples of r(R) agree on
their X-value, they must necessarily agree on their Y
value.
• Conditions :
– No two tuples in any legal state r (R) will have the same
value of X.
– If X → Y in R, this does not say whether or not Y → X
in R

34
 Examples of FD constraints
• Social security number determines employee name
– SSN  ENAME
• Project number determines project name and location
– PNUMBER  {PNAME, PLOCATION}
• Employee ssn and project number determines the hours
per week that the employee works on the project
– {SSN, PNUMBER}  HOURS

35
 Functional Dependencies
• An FD is a property of the attributes in the schema R.
• The constraint must hold on every relation instance
r(R).
• In a relation R, an attribute or a set of attributes Y is
said to be functionally dependent on another attribute or
a set of attributes X, denoted by X→ Y, if each value
of X is associated with exactly one value of Y. Hence,
X functionally determines Y.
• If K is a key of R, then K functionally determines all
attributes in R (since we never have two distinct tuples
with t1[K]=t2[K]).
36
 Types of Functional Dependencies
• Full Functional Dependency
• Partial Functional Dependency
• Transitive Functional Dependency
• Multivalued Functional Dependency

37
 Full Functional Dependency
• Definition : A functional dependency of the form
X→ Y is said to be full functional dependency, if any
attribute is removed from X then dependency sustains
no more.
• Example : Consider the relation
EMP_PROJ(Ssn, Pnumber, Hours, Ename, Pname, Plocation)
• {Ssn, Pnumber} → Hours is a full dependency (neither
Ssn → Hours nor Pnumber→Hours holds)
• Hours is fully functionally dependent on both Ssn and
Pnumber.
• The number of hours can not be determined by
Pnumber alone, it needs the Ssn as well.
38
 Partial Functional Dependency
• Definition : A functional dependency of the form
X→ Y is said to be partial functional dependency, if
any attribute is removed from X then functional
dependency persists.
• Example : Consider the relation
EMP_PROJ(Ssn, Pnumber, Hours, Ename, Pname, Plocation)
• {Ssn, Pnumber}→Ename is partial dependency
(because Ssn→Ename holds.)
• Ename can be determined by using Ssn alone even if
Pnumber is removed from the relation.

39
 Transitive Functional Dependency
• Definition : A functional dependency X→Y in a
relation schema R is a transitive dependency if there
exists a set of attributes Z in R that is neither a candidate
key nor a subset of any key of R, and both X→Z and
Z→Y hold.
• Example :

40
 Transitive Functional Dependency
• The dependency Ssn→Dmgr_ssn is transitive through
Dnumber in EMP_DEPT in Figure , because both the
dependencies Ssn → Dnumber and Dnumber →
Dmgr_ssn hold and Dnumber is neither a key itself nor
a subset of the key of EMP_DEPT.
• Intuitively, we can see that the dependency of
Dmgr_ssn on Dnumber is undesirable in EMP_DEPT
since Dnumber is not a key of EMP_DEPT.

41
 Multivalued Functional Dependency
• Definition : A multivalued dependency X ↠ Y
specified on relation schema R, where X and Y are both
subsets of R, specifies the following constraint on any
relation state r of R: If two tuples t1 and t2 exist in r
such that t1[X] = t2[X], then two tuples t3 and t4 should
also exist in r with the following properties, where we
use Z to denote (R – (X ∪ Y)):
– 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].

42
 Multivalued Functional Dependency
• Whenever X ↠ Y holds, we say that X multidetermines
Y. Because of the symmetry in the definition, whenever
X ↠ Y holds in R, so does X ↠ Z. Hence, X ↠ Y
implies X ↠ Z, and therefore it is sometimes written as
X ↠ Y|Z.
• An MVD X ↠ Y in R is called a trivial MVD if
(a) Y is a subset of X, or
(b) X ∪ Y = R.
• An MVD that satisfies neither (a) nor (b) is called a
nontrivial MVD.

43
 Multivalued Functional Dependency
• Example : The figure shows the EMP relation with two MVDs
: Ename ↠ Pname and Ename ↠ Dname

• Constraint : An employee may work on several projects and may

have several dependents and employees projects and dependents
are independent of one another.
• MVDs :
– Ename ↠ Pname and
– Ename ↠ Dname
– ( OR Ename Pname | Dname )
44
 Closure of F
• Given a set F set of functional dependencies,
there are certain other functional dependencies
that are logically implied by F.
• E.g. If A  B and B  C, then we can infer
that A  C
• The set of all functional dependencies
logically implied by F is the closure of F.
• We denote the closure of F by F+.

45
Armstrong Axioms (Inference Rules)
• The following three rules called Armstrong‘s Axioms
(inference rules) can be applied repeatedly to infer all
FDs implied by a set F of FDs.
• Armstrong's inference rules:
– IR1. (Reflexive) If Y subset-of X, then X  Y
– IR2. (Augmentation) If X  Y, then XZ  YZ
• for any Z (Notation: XZ stands for X U Z)
– IR3. (Transitive) If X  Y and Y  Z, then X  Z
• These rules are
– sound (generate only functional dependencies that actually
hold) and
– complete (generate all functional dependencies that hold).
46
Armstrong Axioms (Inference Rules)
• Some additional inference rules (derived from
Armstrong axioms) that are useful:
– Decomposition: If X  YZ, then X  Y and X  Z
– Union: If X  Y and X  Z, then X  YZ
– Psuedotransitivity: If X  Y and WY  Z, then
WX  Z
• The last three inference rules, as well as any
other inference rules, can be deduced from IR1,
IR2, and IR3 (completeness property)

47
Normalization

48
 Normalization
• Normalization is a step-by-step process of replacing a
given relation by a successive collection of relations in
order to achieve simpler and better data representation.
• Objective :
– to eliminate different anomalies that may occur due to
referential integrity constraint.
– to identify a suitable set of relations in database design.
– Prevents redundancy in data.
– Ensures data integrity (entity constraints, domain
constraints, referential integrity constraints).
• Criteria for decomposition in normalization :
– Loseless decomposition – Ensures no loss of information
– Dependency preserving – Ensures no loss of functional
dependencies. 49
 Normalization
• Types of Normal Forms :
– 1NF
– 2NF, 3NF, BCNF
• based on keys and FDs of a relation schema.
– 4NF
• based on keys, multi-valued dependencies : MVDs.
• Additional properties may be needed to ensure a good
relational design (lossless join, dependency
preservation).

50
 Practical Use of Normal Forms
• Normalization is carried out in practice so that the
resulting designs are of high quality and meet the
desirable properties.
• The practical utility of these normal forms becomes
questionable when the constraints on which they are
based are hard to understand or to detect.
• The database designers need not normalize to the
highest possible normal form.
– (usually up to 3NF, BCNF or 4NF).
• Denormalization:
– The process of storing the join of higher normal form
relations as a base relation—which is in a lower
normal form.
51
 Definitions of Keys and Attributes
Participating in Keys
• A superkey of a relation schema R = {A1, A2, ...., An}
is a set of attributes S subset-of R with the property that
no two tuples t1 and t2 in any legal relation state r of R
will have t1[S] = t2[S].

• A key K is a superkey with the additional property that

removal of any attribute from K will cause K not to be a
superkey any more.

52
 Definitions of Keys and Attributes
Participating in Keys
• If a relation schema has more than one key, each is
called a candidate key.
– One of the candidate keys is arbitrarily designated to
be the primary key, and the others are called
secondary keys / alternate keys.
• A Prime attribute must be a member of some
candidate key.
• A Nonprime attribute is not a prime attribute—that is,
it is not a member of any candidate key.

53
 First Normal Form (1NF)
• First normal form (1NF) is now considered to be part of the
formal definition of a relation in the basic (flat) relational
model.
• It states that the domain of an attribute must include only
atomic (simple, indivisible) values and that the value of any
attribute in a tuple must be a single value from the domain of
that attribute.
• Hence 1NF disallows
– composite attributes
– multivalued attributes
– nested relations; attributes whose values for an individual tuple are
non-atomic
• The only attribute values permitted by 1NF are single atomic
(or indivisible) values. 54
Example 1 : First Normal Form (1NF)
• Thus, a relation is in 1NF if it contains no multivalued
attributes.
• Example : Consider the DEPARTMENT relation schema shown
in Figure, whose primary key is Dnumber, and suppose that we
extend it by including the Dlocations attribute as shown in Figure
(a).
• We assume that each department can have a number of locations.
• Figure : A relation schema that is not in 1NF

55
 Example: First Normal Form (1NF)
• As we can see, this is not in 1NF because Dlocations is not an
atomic attribute, as illustrated by the first tuple in Figure (b).
• As it violates the 1NF, we have to normalize the relation

56
 Example: First Normal Form (1NF)
• Solution 1 : Expand the key so that there will be a separate tuple
in the original DEPARTMENT relation for each location of a
DEPARTMENT, as shown in Figure (c).
• In this case, the primary key becomes the combination
{Dnumber, Dlocation}.
• This solution has the disadvantage of introducing redundancy in
the relation.

57
 Example: First Normal Form (1NF)
• Solution 2 : Remove the attribute Dlocations that violates 1NF
and place it in a separate relation DEPT_LOCATIONS along
with the primary key Dnumber of DEPARTMENT.
• The primary key of this relation is the combination {Dnumber,
Dlocation}, as shown in Figure (d).
• A distinct tuple in DEPT_LOCATIONS exists for each location
of a department.
• This decomposes the non-1NF relation into two 1NF relations.

58
 Example: First Normal Form (1NF)
• Solution 2 : Remove the attribute Dlocations that violates 1NF
and place it in a separate relation DEPT_LOCATIONS along
with the primary key Dnumber of DEPARTMENT.
• The primary key of this relation is the combination {Dnumber,
Dlocation}, as shown in Figure (d).
• A distinct tuple in DEPT_LOCATIONS exists for each location
of a department.
• This decomposes the non-1NF relation into two 1NF relations.

59
 Second Normal Form (2NF)
• The redundancy problem introduced by 1NF can be eliminated
by using 2NF.
• Second normal form (2NF) is based on the concept of full
functional dependency.
• Definition : A relation schema R is in 2NF if it is in 1NF and
every non-key (nonprime) attribute A in R is fully functionally
dependent on the primary key of R.
• Conversion from 1NF to 2NF requires removal of all partial
dependencies.
• The test for 2NF involves testing for functional dependencies
whose left hand side attributes are part of primary key.
• If the primary key contains a single attribute, the test need not
be applied at all. 60
Example : Second Normal Form (2NF)
• Consider the EMP_PROJ relation in Figure:

• This relation is in 1NF but is not in 2NF.

• The nonprime attribute Ename violates 2NF because of FD2,
as do the nonprime attributes Pname and Plocation because
of FD3. The functional dependencies FD2 and FD3 make
Ename, Pname, and Plocation partially dependent on the
primary key {Ssn, Pnumber} of EMP_PROJ, thus violating
the 2NF test. 61
Example : Second Normal Form (2NF)
• Solution : If a relation schema is not in 2NF, it can be
second normalized or 2NF normalized into a number of
2NF relations in which nonprime attributes are
associated only with the part of the primary key on
which they are fully functionally dependent.
• Therefore, the functional dependencies FD1, FD2, and
FD3 in Figure lead to the decomposition of EMP_PROJ
into the three relation schemas EP1, EP2, and EP3
shown in Figure (a), each of which is in 2NF.

62
Example : Second Normal Form (2NF)

63
 Third Normal Form (3NF)
• The updation anomalies introduced by 2NF can be
eliminated by using 3NF.
• Third normal form (3NF) is based on the concept of
transitive dependency.
• Definition : a relation schema R is in 3NF if it is in 2NF
and no nonprime attribute of R is transitively dependent
on the primary key.
OR

64
 Third Normal Form (3NF)
• Definition : A relation schema R is in third normal form
(3NF) if whenever a FD X → A holds in R, then either:
(a) X is a superkey of R, or
(b) A is a prime attribute of R
• NOTE: Boyce-Codd normal form disallows condition
(b) above
• Relation should not have a nonkey attribute functionally
determined by another nonkey attribute i.e. there should
be no transitive dependency of a nonkey attribute on the
primary key.

65
Example : Third Normal Form (3NF)
• Consider the relation schema EMP_DEPT as shown in
Figure.

• The relation schema EMP_DEPT in Figure is in 2NF,

since no partial dependencies on a key exist.
• However, EMP_DEPT is not in 3NF because of the
transitive dependency of Dmgr_ssn (and also Dname)
on Ssn via Dnumber.
66
Example : Third Normal Form (3NF)
• We can normalize EMP_DEPT by decomposing it into
the two 3NF relation schemas ED1 and ED2 shown in
Figure (b).
• Intuitively, we see that ED1 and ED2 represent
independent entity facts about employees and
departments.
• A NATURAL JOIN operation on ED1 and ED2 will
recover the original relation EMP_DEPT without
generating spurious tuples.

67
Example : Third Normal Form (3NF)

68
Summary of Normal Forms Based on Primary
Keys and Corresponding Normalization

69
 Boyce-Codd Normal Form (BCNF)
• Boyce-Codd normal form (BCNF) was proposed as a
simpler form of 3NF, but it was found to be stricter than
3NF.
• That is, every relation in BCNF is also in 3NF; however,
a relation in 3NF is not necessarily in BCNF.
• Definition : A relation schema R is in BCNF if
whenever a nontrivial functional dependency X→A
holds in R, then X is a superkey of R.
i.e. X(superkey) → A (not prime)

70
 Example : Boyce-Codd Normal Form (BCNF)
• Consider the relation LOTS1A shown in Figure

• This relation is not in BCNF because of FD5 (Area is

not a superkey of LOTS1A).
• Note that FD5 satisfies 3NF in LOTS1A because
Country_name is a prime attribute, but this condition
does not exist in BCNF.
71
 Example : Boyce-Codd Normal Form (BCNF)
• Solution : We can decompose LOTS1A into two BCNF
relations LOTS1AX and LOTS1AY.
• This decomposition losses the functional dependency
FD2 because its attributes no longer coexist in the same
relation after decomposition.

72
 Fourth Normal Form (4NF)
• Definition : A relation schema R is in 4NF with
respect to a set of dependencies F (that includes
functional dependencies and multi-valued dependencies)
if, for every nontrivial multi-valued dependency X ↠ Y
in F+X is a superkey for R.
• We can state the following points:
– An all-key relation is always in BCNF since it has no FDs.
– An all-key relation such as the EMP relation in Figure (a), which
has no FDs but has the MVD Ename ↠ Pname | Dname, is not in
4NF.
– A relation that is not in 4NF due to a nontrivial MVD must be
decomposed to convert it into a set of relations in 4NF.
– The decomposition removes the redundancy caused by the MVD. 73
 Fourth Normal Form (4NF)
• The process of normalizing a relation involving the
nontrivial MVDs that is not in 4NF consists of
decomposing it so that each MVD is represented by a
separate relation where it becomes a trivial MVD.
• Consider the EMP relation in Figure (a).

• EMP is not in 4NF because in the nontrivial MVDs

Ename ↠ Pname and Ename ↠ Dname, and Ename is
not a superkey of EMP. 74
 Fourth Normal Form (4NF)
• We decompose EMP into EMP_PROJECTS and
EMP_DEPENDENTS, shown in Figure (b).
• Both EMP_PROJECTS and EMP_DEPENDENTS are
in 4NF, because the MVDs Ename ↠ Pname in
EMP_PROJECTS and Ename ↠ Dname in
EMP_DEPENDENTS are trivial MVDs.
• No other nontrivial MVDs hold in either
EMP_PROJECTS or EMP_DEPENDENTS. No FDs
hold in these relation schemas either.

75
Fourth Normal Form (4NF)

76