Hi everyone,

It is my first time using forum. I have been struggling a lot with the follwing problems i was wondering whether anybody can help me, I really appreciate your helps.I have been doing a database design for a buidling 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
Applicant-County
Applicant-Post-Code
Property-House-Number
Property-Street-Name
Property-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
Applicant-County
Applicant-Post-Code

Applicant-Name(PK)
Date of Application(PK)
Property-Post-Code(PK)
Property-House-Number
Property-Street-Name
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
Applicant-County
Applicant-Post-Code

Applicant-Name(PK)
Date of Application(PK)
Property-Post-Code(PK)

Property-Post-Code(PK)
Property-House-Number
Property-Street-Name
Property-City-Town
Property-Country
Property-Value
Cost-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
Applicant-City/Town
Applicant-County

Applicant-Name(PK)
Property-Post-Code(PK)
Date of Application(PK)

Property-Post-Code(PK)
Property-House-Number
Property-Street-Name
Property-City-Town
Property-Country
Property-Value
Cost-of-Rebuild

I am not sure that these normalisations are right and my choose of primary keys(compound primary keys) are correct, however, i should mention that i am not allowed to introduce automatics keys( i means such as applicant-number or applicant-address-number).Inaddition, i would like to know whether property-postcode can be considered as candidate key for our future primary key.

Furthermore, i do not know how to draw the entity relationship diagram considering 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.

I look forward to hearing from you soon.

In advance i would like to thank you for your help.
Thanks
ctxms

Not being allowed to use surrogate keys in this situation is ludicrous.

Put your address information in one table. Make all your address columns except your postal code primary keys. Create a property table with the address information as the a foreign key. Create an applicant table with the address information as a foreign key. Create an application table to relate applicant and property.

No surrogate keys. What a mess.

Certus

(since I am learning that people here have different definitions for terms), what is your definition of 'surrogate key' ?

Cheers

Thanks a lot for you replies. I really appreciate your helps.

I think a surrogate key is an attribute which its value is unique such as customerID which allocate a unique ID number for each customer so each customer can be identified from other customers by its CustomerID .

Actually after reading the replies i really agree that not using surrogate keys will create a big mess. However, i should have mentioned that i am not allowed to use surrogate key at the first stage which it is required to normalise the extracted attributes from the existing form so i will be able to discuss what is the main issues regarding this form.So i would like to know considering not being allowed to use the surrogate keys do you think that my normalisations are correct and whether i am using the correct primary keys considering the situation?

However, in the second stage i am allowed to introduce surrogate keys to change and improve the normalisation and then desing a database system which will be able to store both the property address and applicant address if they are the same or they are not the same.

Many Thanks
ctxms

Your first and second normal form are okay. The third is missing something.

DerekA. I'm cutting this from http://www.bcarter.com/intsurr1.htm

Intelligent Versus Surrogate Keys

Should I use business columns as primary key fields for tables in the database, or generate artificial primary key values?

When business-related columns are used as primary keys they are often called intelligent or natural keys. For example, if the user gives each customer a unique customer number that value might naturally serve as the primary key for the customer table.

A child table called order might have an intelligent primary key consisting of two columns: customer_number to act as a foreign key pointer to the customer table plus order_date_time to identify different orders for one customer.

An alternative is to use system-generated artificial primary key values. These are often called surrogate keys because they are replacements for the intelligent keys, or blind keys because the user doesn't see them.

In the example above, the primary key to customer would be a surrogate customer_id column whose value would be set to 1, 2, 3 as new rows are inserted. The customer_number column would still be included in the table, with a unique index, but it would not be part of the primary key.

The primary key to the order table would now consist of a single surrogate order_id column. The customer_id column would be included as a foreign key pointer to the parent table but would not be part of the primary key. Order_date_time would also be included but it too would be an ordinary data field. The customer_number column would be dropped from the order table.

Intelligent/Surrogate Keys

(as per definition). My real world, High Performance position is:
- strive to Intelligent Natural keys
- avoid Surrogate keys unless you have to use them (which means I have them only in the parent tables that are heavily used for dependencies)
- Mostly, I use (eg) operator initials "DA", plus next-sequential within that, for index distribution (contention) reasons
- where I have to use a single-column Surrogate Key, I use and Integers and Bigints, as they are the fastest
- select max(key) is no problem for Sybase (the last index page is pretty much guaranteed to be in the data cache), but it must be coded properly
- I do not use the Surrogate Key table as per B Carter or the like
- If select max(key) is not enough for the level of HP I need, I have two additional mehods (which I cannot publish)
- I flog anyone trying to use an IDENTITY column and transport them to Siberia

Certus. I end up with generally the same thing as your post, plus additional methods for HP

Ctxms. Therefore, I agree it is a Good Thing to disallow Surrogate Keys in Stage I, in order to force you to seriously think about Intelligent or Natural Keys, and that Surrogate Keys are allowed in the actual implementation.

Your Normal Forms

Ctxms. No, you have not performed basic normalisation as per any method that I can recognise; your second is just ok; your third is broken. Please try again by definition and post. IDEF1X is the most straight-forward (for me!).

It's absolutely bogus not to use surrogate keys in a situation like this.
Surrogate keys are a well accepted and encouraged part of database design, Derek's personal preferences not withstanding.
Surrogate keys should be considered whenever you have composite natural keys that are also foreign keys on child tables. In situations where you have mulitiple layers of relationships, they are much preferable to natural keys.
Surrogate key use is so ubiquitous that I personally use them by default on all of my tables (except the most basic lookup and list tables) to allow consistency in sql coding across the database.
ctxms, is this a class assignment? Who exactly is preventing you from using surrogate keys?