# 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* = {*R*_{1}, *R*_{2}, ..., *R _{m}*} of a universal relation

*R*based on a set of functional dependencies

*F*, such thateach

*R*in

_{i}*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 relation*R*and a set of functional dependencies*F*on 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*∪ {*A*_{1}} ∪ {*A*_{2}} ... ∪ {*A _{k}*} }, where

*X*→

*A*

_{1},

*X*→

*A*

_{2}, ...,

*X*→

*A*are the only dependencies in

_{k}*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:

*U*(Emp_ssn,Pno,Esal,Ephone,Dno,Pname,Plocation)

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:

*R*_{1}(__Emp_ssn__,Esal,Ephone,Dno)

*R*_{2}(__Pno__,Pname,Plocation)

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 *R _{i}* 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

*R*in the decomposition is synthesized (constructed) from the set of functional dependencies in

_{i}*G*with the same left-hand-side

*X*.

**2. Nonadditive Join Decomposition into BCNF Schemas**

The next algorithm decomposes a universal relation schema *R* = {*A*_{1}, *A*_{2}, ..., *A _{n}*} into a decomposition

*D*= {

*R*

_{1},

*R*

_{2}, ...,

*R*} such that each

_{m}*R*is in BCNF

_{i}*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*= {

*R*

_{1},

*R*

_{2}, ...,

*R*} of a universal relation

_{m}*R*based on a set of functional dependencies

*F*, such that each

*R*in

_{i}*D*is in BCNF.

Algorithm 16.5. Relational Decomposition into BCNF with NonadditiveJoin Property

**Input: **A universal relation*R*and a set of functional dependencies*F*on 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 *X*→*Y* in *Q* that violates BCNF; replace *Q* in *D* by two relation schemas (*Q* – *Y*) and (*X*∪*Y*);

} ;

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 *X*→*Y* 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 relation*R*and a set of functional dependencies*F*on 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*∪ {*A*_{1}} ∪ {*A*_{2}} ... ∪ {*A _{k}*} }, where

*X*→

*A*

_{1},

*X*→

*A*

_{2}, ...,

*X*→

*A*are the only dependencies in

_{k}*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 *R*_{1} and *R*_{2} as before. However, now in step 3, we will generate a relation corresponding to the key {Emp_ssn, Pno}. Hence, the resulting design contains:

*R*_{1}(__Emp_ssn__,Esal,Ephone,Dno)

*R*_{2}(__Pno__,Pname,Plocation)

*R*_{3}(__Emp_ssn__,

From G.A.R.D. Realms

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

Visit our website - https://goo.gl/HKbmHS (assignmenthelp24.com)

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

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

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

goo.gl

goo.gl

=================>>>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