It is my first time using forum. I have been struggling a lot with the following problems i was wondering whether anybody can help me, I really appreciate your helps. I have been doing a database design for a building society; i need to use bottom-up technique extracting attributes from a form. the list of attributes and the three processes of normalisation are as follows:
Unnormalised attributes Applicant Name Date of Application Tel No Applicant-House-Number/Name Applicant-Street-Name Applicant-City/Town Applic ant-County Applicant-Post-Code Property-House-Number Property-Street-Name Proper ty-City-Town Property-Country Property-Post-Code Property-Value Cost-of-Rebuild
1st Normal Form Applicant-Name(PK) Date of Application(PK) Tel No Applicant-House-Number/Name Applicant-Street-Name Applicant-City/Town Applic ant-County Applicant-Post-Code
Applicant-Name(PK) Date of Application(PK) Property-Post-Code(PK) Property-House-Number Property-Street-Na me Property-City-Town Property-Country Property-Value Cost-of-Rebuild
2nd normal form Applicant-Name(PK) Date of Application(PK) Tel No Applicant-House-Number/Name Applicant-Street-Name Applicant-City/Town Applic ant-County Applicant-Post-Code
Applicant-Name(PK) Date of Application(PK) Property-Post-Code(PK)
Property-Post-Code(PK) Property-House-N umber Property-Street-Name Property-City-Town Property-Country Property-Value Co st-of-Rebuild
3rd normal form Applicant-Name(PK) Date of Application(PK) Tel No
Applicant-Post-Code(PK) Applicant-House-Number/Name Applicant-Street-Name A pplicant-City/Town Applicant-County
Applicant-Name(PK) Property-Post-Code(PK) D ate of Application(PK)
I would like to know whether these three stages of normalisation are correct? Furthermore, I was wondering how to draw the entity relationship diagram that the ERD can adapt the following conditions: 1-The applicant applying for buying insurance for a property that he/she is living in it therefore, his/her contact address is the same as property address.therefore it is only required to store the property address.
2-however, it is required to consider the situations where the applicant is buying insurance for a property but he/she is living in another property so his/her property address and his/her post address are different.
However, I thought about many ways to avoiding redundancy and null values for the address such as supertype/subtype or recursive but none of them do not seems the correct way to approach this scenario.
I look forward to hearing from you soon.
In advance i would like to thank you for your help. Thanks ctxms
Hi, from what i could see i would be tempted to solve you problem with the following tables and attributes : ----------------------------------------- Table insurance Insurance-Code(PK) Date of Application Property-Code
Table Applicants Applicant-Code(PK) Applicant-Name Applicant-House-Number/Name Applic ant-Street-Name Applicant-City-Town-Code(tbl cities pk) Applicant-Country-Code(tbl countries pk) Applicant-Post-Code Tel No
Table Cities City-Town-Code(PK) Country Code City-Town-Name ----------------------------------------- NOtes: - Added a code for every single table. These will be the relationships - I suppose Tel No is the applicant's phone number
Doubts :
> 1-The applicant applying for buying insurance for a > property that he/she is living in it therefore, his/her > contact address is the same as property address.therefore > it is only required to store the property address.
Can see 2 variant solutions for this : 1- In your application you can by default set the property fields to have the same values as the applicant's home address, then if needed they will be changed. 2- Create a radio box where the operator clicks saying meaning that the applicant is applying for buying insurance for a property that he/she is living.These is similar to point 1 anyway.
> 2-however, it is required to consider the situations where > the applicant is buying insurance for a property but > he/she is living in another property so his/her property > address and his/her post address are different.
That is why in my design a property has details on the applicant.We first enter applicant details and then we enter property details... Entering property detail we will choose an existing Applicant(the one created).
Previous Topic
