Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    hello everyone, I need ur help with database design!

    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

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Certus

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

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  4. #4
    Join Date
    Aug 2006
    Posts
    7

    No surrogate key, a big mess

    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

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  6. #6
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    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!).
    Last edited by DerekA; 08-16-06 at 04:23.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •