Normalization to 3NF from un-normalized relation

Question

Given

INSURANCE PORTFOLIO (portfolio id, insurance company name, insurance company phone, ((agent name, agent license number, state of residence, ((policy number, policy description, annual premium, benefit, beneficiary details)), 
number of policies)), number of policies in a portfolio)

I'm trying to get this into 3NF. Am I on the right track?

1NF:

1NF: INSURANCE PORTFOLIO:(portfolio id, insurance company name, insurance company phone,
,agentname, number of policies in a portfolio)

agentdetails: (agent name, agent license number, state of residence, policy number,number of policies in a portfolio#)

policydetails:(agent name#,policy number#, policy description, annual premium, benefit, beneficiary details)

2NF

2NF: INSURANCE PORTFOLIO:( agent name ,portfolio id, insurance company name, number of policies in a portfolio)

Agentdetails: (agent name, agent license number, state of residence, policynumber,number of policies in a portfolio#)

policydetails:(agentname,policy number, policy description, annual premium, benefit, beneficiary details)

3NF:

 INSURANCE PORTFOLIO:( agent name ,portfolio id, phonenumber  , number of policies in a portfolio)


agentdetails:(agent name#, agent license number, state of residence,policynumber,number of policies in a portfolio#)

policydetails:(agent name#,policy number#, policy description, annual premium, benefit, beneficiary details)

Any guidance is appreciated

Normalization to 3NF requires knowing what functional dependencies hold in your various tables. You didn't give them. PS Re 1NF see [this answer and its linked answer](http://stackoverflow.com/a/37483508/3404097). — philipxy, May 29 '16 at 09:26
Please explain what the input is. It doesn't seem to be a relation, because there is not an attribute name where a double parenthesis appears. Also please explain clearly what the double parentheses mean. From your examples, they seem to mean something like a value that is a nested relation, rather than something like a value that is a tuple. Please give a reference for what you have been told what "0NF" and "1NF" mean and for how to get a "1NF" design from it. These terms do not have fixed meaning. — philipxy, May 30 '16 at 00:54

reaanb · Answer 1 · 2016-05-29T11:55:28.733

0

Your 1NF answer skips over 1NF and tries to go to 3NF directly. As a result, you seem at a loss what to do in your 2NF and 3NF answers.

1NF doesn't introduce new relations (unless you have nested tables that can be empty). What it does is eliminate nested rows. Higher normal forms then decompose the relation.

Your original schema has the form:

R = (A, B, C, (D, E, F, (G, H, I, J, K), L), M)

I'm assuming that portfolios can't exist without policies, and policies can't exist without agents. Converting that to 1NF should have the form:

R = (A, B, C, D, E, F, G, H, I, J, K, L, M)

and you need to choose a suitable primary key so that every row in the relation is uniquely identified by the values of the primary key. At worst, the combination of all columns are unique (i.e. there's always a key) but in this case you should be able to find one with 3 columns or less, depending on the associations between the nested levels.

For 2NF, see if you can find any partial functional dependencies in your 1NF schema. If you chose a primary key with a single column, your relation is already in 2NF and you can skip ahead. If there are any partial dependencies, split them off to separate relations.

For 3NF, see if you can find any transitive functional dependencies in your 2NF schema. If there are any, split them off to separate relations.

edited May 29 '16 at 11:55

answered May 29 '16 at 08:38

reaanb

9,806
2
23
37

2

If parentheses indicate a nested table and such a table can be empty then you need to split it off with a key to an additional table. – philipxy May 29 '16 at 09:23
@philipxy Thanks for pointing that out. I updated my answer. – reaanb May 29 '16 at 11:55
Given R = (A pk, B, (C)) where A -> B and A ->> C and C can be empty, I would split it into R1 = (A pk, B) and R2 = (A pk, C pk). Where's the surrogate, or did you have a different scenario in mind? – reaanb May 29 '16 at 13:20
1

Corrected PS: If there is no CK without a nested table attribute then you must introduce a surrogate. Eg {,} with CK {a,b}. Or just a one-attribute relation with a set-valued attribute. – philipxy May 29 '16 at 13:59
PPS Candidate key A and nested table attribute b doesn't mean A ->> b or A ->> unnested b. – philipxy May 30 '16 at 00:35

score 0 · Answer 2 · answered May 30 '16 at 06:42

My college days were more than 3 decades ago, so pardon me if I'm a little rusty or skip some steps. First, list all the attributes and identify what it describes:

Attribute               Entity
=========               ======
portfolio id            Portfolio
insurance company name  Insurance Co.
insurance company phone Insurance Co.
agent name              Agent
agent license number    Agent
state of residence      Agent
policy number           Policy
policy description      Policy
annual premium          Policy
benefit                 Benefit
beneficiary details     Benefit
number of policies      Agent
number of policies      Portfolio

So now we have the new entities Insurance Co, Agent, Policy and Benefit. Now the question is, "Could there be multiple occurrences of any of these attributes?" Yes, there could be many benefits (thus many details) for one policy. And each portfolio can be made up of many policies. So we break out benefits and policies and remove their attributes from Portfolio:

Benefit(ID, Benefit, Beneficiary Details)
Policy (ID, Policy Number, Description, Annual Premium)
Portfolio (...)

There are no more multiple or non-atomic attributes so Portfolio is now in 1nf. Now we pull out the attributes which describe an entity other than Portfolio. The design now looks like this:

Benefit (ID, Benefit, Beneficiary Details)
Policy (ID, Policy Number, Description, Annual Premium)
Insurance Co (ID, Name, Phone)
Agent (ID, Name, License No, State of Residence, Number of policies)
Portfolio (ID, InsuranceCoID, AgentID, Number of policies)

Portfolio is now in 2nf. But there are relationships between the other entities. I'm making assumptions here, one of which is that an agent works for one company rather than like a broker-agent who represents multiple companies. So the Agent would have a 1-n relationship with the Insurance companies (an agent relates to at most one company, a company relates to zero, one or many agents). The same relationship seems to exist between policy and agent, between benefit and policy (assuming each benefit is tailored exclusively for each policy) and between policy and portfolio. Adding these relationships looks like this:

Benefit (ID, PolicyID, Benefit, Beneficiary Details)
Policy (ID, Policy Number, AgentID, PortfolioID, Description, Annual Premium)
Insurance Co (ID, Name, Phone)
Agent (ID, CompanyID, Name, License No, State of Residence, Number of policies)
Portfolio (ID, Number of policies)

Different assumptions will yield different results, but the process is the same.

Now Portfolio is in 3nf. I would be thinking that the coupling between Policy and Portfolio is loose enough to define a 1-n intersection table rather than have the Portfolio FK as part of the Policy tuple, but that is not formally a part of the normalization process. There is also the issue of relating policy directly to company instead of (or as well as) through agent. This would allow an agent to move from one company to another without dragging all the policies with him. But it brings up the question of how to handle the situation where a policy relates to a different company than the agent. These are valid design considerations (and I haven't even addressed the two calculated fields 'Number of Policies') but as far as normalization is concerned, we seem to be done. I make no claim that it is the best that could be done, it's just an example.

You cannot "break out" a multivalued attribute without introducing a surrogate unless, treating multivalued attributes as relation-valued attributes, there is a candidate key that doesn't contain it to use in the new relation variable. (Otherwise you cannot reconstruct the input.) So you need to justify that you have indeed copied a CK along with the attributes of the multivalued/relation-valued attribute that you have "broken out". (See my comment on reaanb's answer.) — philipxy, May 30 '16 at 08:12
I made no claim that my answer would be acceptable as an answer to a homework question. I admitted to skipping steps in the original post. I just hope OP has a clearer conceptual view of the normalization process. — TommCatt, Jun 01 '16 at 01:42
I don't understand why you mention homework. My point is that your procedure doesn't work in the general case, and you haven't justified that this is one of the cases it works in. — philipxy, Jun 01 '16 at 02:25
Well, perhaps I just don't understand the complaint. If I'm missing a surrogate key somewhere, maybe you could point it out and I'll fix it. — TommCatt, Jun 04 '16 at 06:02
I explained in my first comment: If you have a CK and a MVA ("multivalued attribute") not part of that CK then you can drop the MVA from the original and introduce a second table with that CK plus an attribute for an elements of the MVA. But if the CK & MVA are not disjoint then you can't do that because the pair will not reconstruct to the original. In that case you have to introduce a surrogate. I'm not saying that that is the case here. I am saying that if you are not introducing a surrogate then you need to show it's not the case. See my examples in a comment to reaanb's answer. — philipxy, Jun 04 '16 at 06:39

score -1 · Answer 3 · answered May 29 '16 at 08:29

-1

[1] Your database is not having transitive functional dependency, so it falls under category of 3NF

[2] Add one unique column Agent_ID with Agentdetails table

[3] Call Agent_ID in "Insurance Portfolio" and "PolicyDetails" table

[4] While running report, if you wish to Display Agent Name, then make appropriate join with Agentdetails table

answered May 29 '16 at 08:29

Bavishi PN

379
2
6

3

How do you know there are no transitive FDs if the questioner didn't specify any FDs? Also, normalization doesn't introduce surrogate keys. Finally, your answer doesn't explain anything. – reaanb May 29 '16 at 08:50
Here the whole idea is ,with my suggested steps, when step [3] is being performed, then FD will get removed. – Bavishi PN May 30 '16 at 01:58

Normalization to 3NF from un-normalized relation

3 Answers3