Skip to content

3nf Synthesis Example Essays

Algorithms for Relational Database Schema Design

We now give three algorithms for creating a relational decomposition from a uni-versal relation. Each algorithm has specific properties, as we discuss next.

1. Dependency-Preserving Decomposition into 3NF Schemas

Algorithm 16.4 creates a dependency-preserving decomposition D = {R1, R2, ..., Rm} of a universal relation R based on a set of functional dependencies F, such thateach Ri in D is in 3NF. It guarantees only the dependency-preserving property; it does not guarantee the nonadditive join property. The first step of Algorithm 16.4 is to find a minimal cover G for F; Algorithm 16.2 can be used for this step. Note that multiple minimal covers may exist for a given set F (as we illustrate later in the example after Algorithm 16.4). In such cases the algorithms can potentially yield multiple alternative designs.

Algorithm 16.4. Relational Synthesis into 3NF with Dependency Preservation

Input: A universal relationRand a set of functional dependenciesFon theattributes of R.

        Find a minimal cover G for F (use Algorithm 16.2);

        For each left-hand-side X of a functional dependency that appears in G, cre-ate a relation schema in D with attributes {X∪ {A1} ∪ {A2} ... ∪ {Ak} }, where XA1, XA2, ..., XAk are the only dependencies in G with X as the left-hand-side (X is the key of this relation);

        Place any remaining attributes (that have not been placed in any relation) in a single relation schema to ensure the attribute preservation property.

Example of Algorithm 16.4. Consider the following universal relation:


Emp_ssn, Esal, Ephone refer to the Social Security number, salary, and phone numberof the employee. Pno, Pname, and Plocation refer to the number, name, and location of the project. Dno is department number.

The following dependencies are present:

FD1: Emp_ssn→ {Esal, Ephone, Dno}

FD2: Pno→ { Pname, Plocation}

FD3: Emp_ssn, Pno→ {Esal, Ephone, Dno, Pname, Plocation}

By virtue of FD3, the attribute set {Emp_ssn, Pno} represents a key of the universal relation. Hence F, the set of given FDs includes {Emp_ssn→Esal, Ephone, Dno;

Pno → Pname, Plocation; Emp_ssn, Pno → Esal, Ephone, Dno, Pname, Plocation}.

By applying the minimal cover Algorithm 16.2, in step 3 we see that Pno is a redun-dant attribute in Emp_ssn, Pno→Esal, Ephone, Dno. Moreover, Emp_ssn is redun-dant in Emp_ssn, Pno→Pname, Plocation. Hence the minimal cover consists of FD1 and FD2 only (FD3 being completely redundant) as follows (if we group attributes with the same left-hand side into one FD):

Minimal cover G: {Emp_ssn→Esal, Ephone, Dno; Pno→Pname, Plocation}

By applying Algorithm 16.4 to the above Minimal cover G, we get a 3NF design con-sisting of two relations with keys Emp_ssn and Pno as follows:



An observant reader would notice easily that these two relations have lost the original information contained in the key of the universal relation U (namely, that there are certain employees working on certain projects in a many-to-many relationship). Thus, while the algorithm does preserve the original dependencies, it makes no guarantee of preserving all of the information. Hence, the resulting design is a lossy design.

Claim 3. Every relation schema created by Algorithm 16.4 is in 3NF. (We willnot provide a formal proof here;6 the proof depends on G being a minimal set of dependencies.)

It is obvious that all the dependencies in G are preserved by the algorithm because each dependency appears in one of the relations Ri in the decomposition D. Since G is equivalent to F, all the dependencies in F are either preserved directly in the decomposition or are derivable using the inference rules from Section 16.1.1 from those in the resulting relations, thus ensuring the dependency preservation prop-erty. Algorithm 16.4 is called a relational synthesis algorithm, because each relation schema Ri in the decomposition is synthesized (constructed) from the set of functional dependencies in G with the same left-hand-side X.

2. Nonadditive Join Decomposition into BCNF Schemas

The next algorithm decomposes a universal relation schema R = {A1, A2, ..., An} into a decomposition D = {R1, R2, ..., Rm} such that each Ri is in BCNF and the decomposition D has the lossless join property with respect to F. Algorithm 16.5 utilizes Property NJB and Claim 2 (preservation of nonadditivity in successive decompositions) to create a nonadditive join decomposition D = {R1, R2, ..., Rm} of a universal relation R based on a set of functional dependencies F, such that each Ri in D is in BCNF.

Algorithm 16.5. Relational Decomposition into BCNF with NonadditiveJoin Property

Input: A universal relationRand a set of functional dependenciesFon theattributes of R.

        Set D := {R} ;

        While there is a relation schema Q in D that is not in BCNF do


choose a relation schema Q in D that is not in BCNF;

find a functional dependency XY in Q that violates BCNF; replace Q in D by two relation schemas (QY) and (XY);

} ;

Each time through the loop in Algorithm 16.5, we decompose one relation schema Q that is not in BCNF into two relation schemas. According to Property NJB forbinary decompositions and Claim 2, the decomposition D has the nonadditive join property. At the end of the algorithm, all relation schemas in D will be in BCNF. The reader can check that the normalization example in Figures 15.12 and 15.13 basi-cally follows this algorithm. The functional dependencies FD3, FD4, and later FD5 violate BCNF, so the LOTS relation is decomposed appropriately into BCNF rela-tions, and the decomposition then satisfies the nonadditive join property. Similarly, if we apply the algorithm to the TEACH relation schema from Figure 15.14, it is decomposed into TEACH1(Instructor, Student) and TEACH2(Instructor, Course) because the dependency FD2 Instructor→Course violates BCNF.

In step 2 of Algorithm 16.5, it is necessary to determine whether a relation schema Q is in BCNF or not. One method for doing this is to test, for each functionaldependency XY in Q, whether X+ fails to include all the attributes in Q, thereby determining whether or not X is a (super)key in Q. Another technique is based on an observation that whenever a relation schema Q has a BCNF violation, there exists a pair of attributes A and B in Q such that {Q – {A, B} } →A; by computing the closure {Q – {A, B} }+ for each pair of attributes {A, B} of Q, and checking whether the closure includes A (or B), we can determine whether Q is in BCNF.

3. Dependency-Preserving and Nonadditive (Lossless) Join Decomposition into 3NF Schemas

So far, in Algorithm 16.4 we showed how to achieve a 3NF design with the potential for loss of information and in Algorithm 16.5 we showed how to achieve BCNF design with the potential loss of certain functional dependencies. By now we know that it is not possible to have all three of the following: (1) guaranteed nonlossy design, (2) guaranteed dependency preservation, and (3) all relations in BCNF. As we have said before, the first condition is a must and cannot be compromised. The second condition is desirable, but not a must, and may have to be relaxed if we insist on achieving BCNF. Now we give an alternative algorithm where we achieve conditions 1 and 2 and only guarantee 3NF. A simple modification to Algorithm 16.4, shown as Algorithm 16.6, yields a decomposition D of R that does the following:

        Preserves dependencies

        Has the nonadditive join property

        Is such that each resulting relation schema in the decomposition is in 3NF

Because the Algorithm 16.6 achieves both the desirable properties, rather than only functional dependency preservation as guaranteed by Algorithm 16.4, it is preferred over Algorithm 16.4.

Algorithm 16.6. Relational Synthesis into 3NF with Dependency Preservationand Nonadditive Join Property

Input: A universal relationRand a set of functional dependenciesFon theattributes of R.

        Find a minimal cover G for F (use Algorithm 16.2).

        For each left-hand-side X of a functional dependency that appears in G, cre-ate a relation schema in D with attributes {X∪ {A1} ∪ {A2} ... ∪ {Ak} }, where XA1, XA2, ..., XAk are the only dependencies in G with X as left-hand-side (X is the key of this relation).

        If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R.7 (Algorithm 16.2(a) may be used to find a key.)

        Eliminate redundant relations from the resulting set of relations in the relational database schema. A relation R is considered redundant if R is a projection of another relation S in the schema; alternately, R is subsumed by S.

Step 3 of Algorithm 16.6 involves identifying a key K of R. Algorithm 16.2(a) can be used to identify a key K of R based on the set of given functional dependencies F. Notice that the set of functional dependencies used to determine a key in Algorithm 16.2(a) could be either F or G, since they are equivalent.

Example 1 of Algorithm 16.6. Let us revisit the example given earlier at the endof Algorithm 16.4. The minimal cover G holds as before. The second step produces relations R1 and R2 as before. However, now in step 3, we will generate a relation corresponding to the key {Emp_ssn, Pno}. Hence, the resulting design contains:




From G.A.R.D. Realms

Do you need help with with essay, research paper, homework or even dissertation?

Visit our website - (

=================>>>CLICK HERE<<<=================
=================>>>CLICK HERE<<<=================
=================>>>CLICK HERE<<<=================

=================>>>GET ASSIGNMENT HELP<<<=================
=================>>>GET ASSIGNMENT HELP<<<=================
=================>>>GET ASSIGNMENT HELP<<<=================

extented essay
social construction of sexuality essay
blue code of silence essay
la chambre des morts critique essay
psychological effects of unemployment essay
explication essay template printable
essays chauffeurs
how to write amazing introduction Help With Writing a Thesis Statement for a Research for essays
research paper on science fiction
users assignment
the 8th day book essay examples
mofat korea essay contest house
gene group analysis essay
essay layout fonts
fulbright-hays doctoral dissertation research award guidelines
compare and contrast essay examples two cities
computer studies coursework topics for persuasive speeches
jehovah witness essays
importance of preserving the environment essay
advantages of television essay
product development case study ipod touch
book essay typer unblocked
upenn supplement essay 2015
bibliography vs references pdf reader
process analysis essay examples how to plan a party
medical compare and contrast essay topics
college confidential cornell essay question
small class size benefits essay checker
title ideas for reflective essays on life
sample cover letter for computer teacher job
symbiosis law gd pi topics for persuasive essays
sample isb application essays
character count personal statement ucas word
hilton's transformation case study solution format
nurse lecturer cover letter
cypw level 3 assignment 02370
hesi adhd case study answers
ums coursework stanford
metatron battle essaytyper
my mother knows it all essay topics
necessity law essay question
pos 355 week 2 individual assignment rubric
plan nacional de desarrollo ernesto zedillo analysis essay
cold mountain essay topics
fashion branding dissertation
the homework machine by dan gutman characters of frozen
what is a roadmap in an essay
bvn holiday homework of class
essay on george bush
circuit d essay 618
mensonges et faux Help With Writing a Thesis Statement for a Research semblants critique essay
neomura hypothesis statement
dream house essay ideas for 8th
example complete extended essay
bookrags lord of the flies essay about myself
europe cv latex template assignment
alice munro bibliography
pharmcas personal essay help
ignou mba ms-03 solved assignments
mother tongue amy tan essay summary of globalization
good words for history essays
1967 interaction ritual essays on face-to-face behavior. anchor books
bartender cover letter examples
amazon river essay outlines
title of play in essay
learned english class essay format
medical school personal statement examples accepted for value
mla format bibliography for journals and diaries
the lizards leopard skin douglas stewart essays
brit hotel lessay hotels
psle science intensive coursework
comparison essay cats and dogs
social case study in social work
week 2 kama assignment
edward snowden essay
community health and environment essay topic
pacific homeworks inc
project tiger 150 words essay
online apa bibliography composer
self evaluation writing essay
www dissertations
field hockey research papers
volca sample editorial essay
cover letter job requirements
natalie dessay clochettes
balzac realism essay conclusion
preconceived notions essay scholarships
part iii essays on love
news assignments
wiat 3 essay composition writing
edit essaypreis 2015 gmc
ipinagbabawal na gamot essay writing
dabigatran route of synthesis essay
3rd person perspective essay examples
women's rights activists throughout history essay
lifting graph automorphisms by voltage assignments clip
findings and analysis dissertation topics
in unity there is strength essay example
diabetes mellitus ii case study
jail tour experience essay
kenya airways careers scholarship essay
why is economics important essay
civ 5 best way to start an essay
isaagny essay prompts for high school
what is a bibliography wikianswers q
who to wright an essay
ap biology essay 1991 corvette
find essays on books
samuha madhyamam essaytyper
o zittre nicht natalie dessay children
nous essayons conjugaison mettre
successful application essay harvard
10 essay writing steps for 3rd
hophni and phinehas bibliography format
essay on life's a struggle
it project management dissertation topics
reminiscence work definition essay
gen y in the workforce case study
movies vs plays essays
short essay about dubai city
le code d'hammourabi dissertation
slacker film analysis Help With Writing a Thesis Statement for a Research essay
explain normalization 1nf 2nf 3nf 4nf bcnf with an example of a persuasive essay
2 000 word essay on army values duty
semestral break essay Help With Writing a Thesis Statement for a Research
reading is a good hobby essay
richard paul critical thinking articles psychology
waldo essay
gay marriage in texas essay examples
essay on terri schiavo
assignment of real estate contract illinois
traffic jam problem Help With Writing a Thesis Statement for a Research solution essay ideas
tips to write an effective essay
parts of essay conclusion
hillwoods academy holiday homework 2014 super
free essay on the golden rule
frequency sports definition essay
critical analysis definition essay ideas
term paper on interest rates
the curlew s cry essays
brook no argument definition essay
what Help With Writing a Thesis Statement for a Research does frozen mean on ucas personal statement
pgce personal statement examples secondary science literature
dissertation writing apps for children
help with maths coursework
jack nasher dissertation definition
free 250 word essay
embryonic stem cell research database assignment
sargent florence theory of industrial location assignment
my best friend ever essay examples