boughtonp
asked on
Normalisation Help (3NF -> 5NF)
Okay, a couple of friends asked me for some database help, but after spending 30 minutes on the problem I found that I couldn't help much, but I said I would try and see if I could find someone else that could...
The task is to convert the model below from 3NF to BCNF to 4NF to 5NF.
There is a whole page of explanation about what the model if for, so I'll try to simply summarise it at the end.
I've used CAPS to indicate primary keys because it seems the easiest way on here.
The 3NF set of entities is:
Member (MEMCODE,name,title,addres
Occupation_Category (OCCUPATION,category)
Region (ADDRESS,region)
Member_Response (MEMCODE,SUBJECT,num-sent,
Offer_Details (OFFERCODE,title,subject)
Offer (OFFERCODE,DATE,price,num-
Offer_Target (OFFERCODE,DATE,TARGET-CAT
Supplier (SUPPCODE,name,address,pho
Item (ITEMCODE,description)
Item_in_Offer (OFFERCODE,ITEMCODE,qty)
Item_Supplier (OFFERCODE,ITEMCODE,SUPPCO
Database is for a mail order company. It sends various offers to it's members (total 20,000).
No more than 5,000 leaflets per offer can be afforded, so the offers are targeted based on age, region and socio-economic category.
Each offer is made up of one or more items.
Each offer is initially targeted on one range of people, but if the offer is no very successful, after a gap of six months the offer will be repeated targetting different groups.
Any help is appreciated.
The task is to convert the model below from 3NF to BCNF to 4NF to 5NF.
There is a whole page of explanation about what the model if for, so I'll try to simply summarise it at the end.
I've used CAPS to indicate primary keys because it seems the easiest way on here.
The 3NF set of entities is:
Member (MEMCODE,name,title,addres
Occupation_Category (OCCUPATION,category)
Region (ADDRESS,region)
Member_Response (MEMCODE,SUBJECT,num-sent,
Offer_Details (OFFERCODE,title,subject)
Offer (OFFERCODE,DATE,price,num-
Offer_Target (OFFERCODE,DATE,TARGET-CAT
Supplier (SUPPCODE,name,address,pho
Item (ITEMCODE,description)
Item_in_Offer (OFFERCODE,ITEMCODE,qty)
Item_Supplier (OFFERCODE,ITEMCODE,SUPPCO
Database is for a mail order company. It sends various offers to it's members (total 20,000).
No more than 5,000 leaflets per offer can be afforded, so the offers are targeted based on age, region and socio-economic category.
Each offer is made up of one or more items.
Each offer is initially targeted on one range of people, but if the offer is no very successful, after a gap of six months the offer will be repeated targetting different groups.
Any help is appreciated.
The impications of moving to 5NF (for a large amount of data) are severe in terms of db performance degradation. Is it imperative that they have the data structured in this way ??
ASKER
I forgot to mention that this is a theoretical exercise for teaching how to normalise, rather than an actual company who are restructuring.
Don't you mean its a University assignment ;=)
Cruel assignment if u ask me.
ASKER
In response to both Uncle Festa and RealTime: yes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just closing off an unanswered question... only a B because it was a vague answer rather than a specific one, and although the site was helpful it didn't help much with the problem.