[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Automatic Normalisation Tool

Posted on 2009-12-28
12
Medium Priority
?
463 Views
Last Modified: 2012-06-22
Hi,
Is there a free tool that will normalistion a relation up to a given normal form that is free..

If I give the tool a set of attributes, funtional dependencies, and the canidate keys would a tool be able to normalise up to a given normal form say BCNF..?

Thanks Ian
0
Comment
Question by:ISC
  • 7
  • 4
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26131971
None that I am aware of...the tool would have to know of the relationships between entities and attributes to create something like that.  If you are able to feed this to the tool, then you've already normalized it yourself.  :)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26132024
Not heard of any such tools till now..

Its better not to trust those tools even if any exists as it might not cater your Business Requirement and Functionality..
And recommended to do it on your own if you are good in DB design..
0
 
LVL 1

Author Comment

by:ISC
ID: 26139207
Hi,

This is  a class exercise in DB normalisation and i need to check if my normalised solution is correct...

Even given a set of attributes for just one table and functional dependencies (FD's) this does not nessecarily mean the table has the 'goodness' properties i.e., is the table say in BCNF that eliminates update, insertion, and deletion problems...

Using the FD's i have calcuated there are two candidate keys on the relation I have normalised up to BCNF i now want to understand if what i have done is correct..

Thanks Ian  

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:ISC
ID: 26139216
that you have said "i now want to check what i have done is correct"...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26140096
>> This is  a class exercise in DB normalisation and i need to check if my normalised solution is correct

Kindly post your exercise and the solution so that we can give our comments on it..
Ideally we wont help for class exercises but can guide / comment on your solution done to tune it better..
0
 
LVL 1

Author Comment

by:ISC
ID: 26140385
Thank you. Correct I do not want anyone to tell me the answer but I just want guidance or comments if you think I have made errors....

Given a relation with the following attributes: R = { M, O, C, L, S, Y, W, P, R, N, T}
And the following functional dependencies these hold on the relation R...
M=>O,C,L
MSY=>W,P,R,N,T
WPRSY=>T,M

The first queston was to indentitfy all candidate keys (these must be mininmal) on the above relation using the FD's provided. I have identified two..
1) MSY
2) WPRSY

The second part was to normalise relation R up to and including BCNF using the attributes, FD's and candidate keys above..

Step0) I arbitralriy chose MSY as my primary key.

Step1) R = { M, O, C, L, S, Y, W, P, R, N, T} - The relation is in 1NF.

Step2) 2NF=all non-primary key attributes are fully functionally dependent on the primary key.
R is not in 2NF because attributes O,C,L are functional determined by M (only part on candidate key MSY) i then removed these attributes O,C,L to a new relation R1 and included in the new relation there determinate M.  

    R = { M, S, Y, W, P, R, N, T}
    R1={M,O,C,L}

I think both R and R1 are now in 2NF.

Step 3) 3NF. I think both R and R1 are in 3NF as they have no transitive dependencies on the primary key.

Step4) BCNF-All determinates are candidate keys.

    The determinates...

  M=>O,C,L
  MSY=>W,P,R,N,T
  WPRSY=>T,M

    The relations are...

    R = { M, S, Y, W, P, R, N, T}
    R1={M,O,C,L}
   
   The determinates above are all candidate keys in the above relations therefore the relations are in BCNF so I can stop....

I just feel I have missed something,,,,

Thanks Ian

   
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26143579
>> Correct I do not want anyone to tell me the answer but I just want guidance or comments if you think I have made errors....

Appreciate your intention..
Can you post the primary key candidates chosen for R and R1 along with the Relationships ie., Foreign key attributes so that we can know better.

>> BCNF-All determinates are candidate keys.

If all determinates can be chosen as candidate keys then you can split the entities R and R1 further into sub-entities.

* Columns chosen as Candidate keys should have lesser no. of columns to represent an optimal design. You can either have a Identity column or autoincrement column to handle it better..
0
 
LVL 1

Author Comment

by:ISC
ID: 26144432
Hi,

At the start the relation R = { M, O, C, L, S, Y, W, P, R, N, T} I identified two candidate keys MSY and WPRSY. For the 2NF step and the 3NF step I chose MSY as the primary key.

After going to BCNF there are now two tables R and R1...

For R1 the key is M  
R1={M,O,C,L}

For R there are two keys MSY and WPRSY...
R = { M, S, Y, W, P, R, N, T}

* I think there is only one foreign key M in R....

Thanks Ian
   
0
 
LVL 1

Author Comment

by:ISC
ID: 26144493
To add...

BCNF states that if all determinates are candidate keys the the relation(s) are in BCNF..

The determinates for R and R1 are all candidiate keys so I THINK i have completed the excercise but a gut feeling thinks there is more to do (i.e., more decomposition to do)...

  M  => R
  MSY  => R1
WPRSY => R1

Thanks Ian
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26159790
You have "R = { M, O, C, L, S, Y, W, P, R, N, T}
And the following functional dependencies these hold on the relation R...
M=>O,C,L
MSY=>W,P,R,N,T
WPRSY=>T,M"

First Normal Form:

    * Eliminate repeating groups in individual tables.
    * Create a separate table for each set of related data.
    * Identify each set of related data with a primary key.

and so as per the information provided.

    R1 = {M,O,C,L}
    R2 = { M, S, Y, W, P, R, N, T}

M is Primary key in R1
And let me know the Primary key for R2

Second Normal Form:

    * Create separate tables for sets of values that apply to multiple records.
    * Relate these tables with a foreign key.

We need to create Foreign Keys now

R1 = { M,O,C,L}
    R2 = { M, S, Y, W, P, R, N, T}

M in R2 is referenced from M in R1 as a Foreign Key.

Third Normal Form:

* Eliminate fields that do not depend on the key.

Based on the dependencies you provided above, I believe T and M are dependent on WPRSY and W,P,R,N,T are dependent on MSY
Kindly clarify..
If so, then we need to create those as separate tables / entities and link with Appropriate Foreign keys like

R3 = {xx,  W, P, R } where xx is the candidate key for this entity
R4 = { xx, N, T }
R5 = { xx, S, Y }

And identify the Primary keys and Foreign keys for R2, R3, R4 and R5 and include the Foreign keys of R3,R4 and R5 in R2 which should help you normalize it effectively..

If I have understood anything wrongly, kindly  revert with more detail so that I can help you out on that..

Kindly work on the Underlined statements.
0
 
LVL 1

Author Comment

by:ISC
ID: 26161063
Hi,
Thanks for the comments...

For R2 = { M, S, Y, W, P, R, N, T} there are two candidate keys for R2 because..

*Functionally Determines =FD

CK1
MSY=>MSY (MSY always itself)
MSY=>M,S,Y,W,P,R,N,T (as given in the scenario MSY FD W,P,R,N,T)
MSY=>M,S,Y,W,P,R,N,T,O,C,L (M FD O,C,L as given in the scenario)

Because MSY FD ALL attributes in R it it a candidate key = CK1...

CK2
WPRSY=>W,P,R,S,Y  (WPRSY always funcitonal determine (FD) itself)
WPRSY=>W,P,R,S,Y,T,M (from the scenario WPRSY FD T,M)
WPRSY=>W,P,R,S,Y,T,M,O,C,L (from the scenario M FD O,C,L)
WPRSY=>W,P,R,S,Y,T,M,O,C,L,N (because we now have MSY we know this FD W,P,R,N,T we now know T)

Because WPRSY FD ALL attributes in R it it a candidate key = CK2...

If I need to chose either CK1 or CK2 to be the primary key for R2 it would be CK1.

=====================================================================

"Based on the dependencies you provided above, I believe T and M are dependent on WPRSY and W,P,R,N,T are dependent on MSY"

"I believe T and M are dependent on WPRSY " - This is correct WPRSY FD T,M (and we now know from my statements above WPRSY is a CK and FD all attributes of R).

"W,P,R,N,T are dependent on MSY" - again your understanding is correct, MSY FD W,P,R,N,T (and we now know from my statements above MSY is a CK and our chosen PK for R as MSY FD all attributes of R).

Thanks


 
0
 
LVL 1

Author Comment

by:ISC
ID: 26165176
R = { M, O, C, L, T, S, Y, W, P, R, N}

Create three new tables based on the minimal cover FD given above. Keys are underlined.

The candidate keys for the initial relation R are: MSY,  WPRSY

R1 = { M, O, C, L }

      M=>O, C, L

R2 = { M, S, Y, W, P, R, N }

MSY=>W, P, R, N

WPRSY=>M

R3 = { W, P, R, S, Y, T, M }


      MSY=>W,P,R
      
WPRSY=>M, T

Because all determinates of each relation are candidate keys then this is in BCNF.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question