Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: College database Final

    I enrolled into a database class and now im worried about getting out with a decent grade.Here is the link to a zip file of what i have so far. If some one could point out some of my mistakes. I probably sound like a complete ass but regret taking this class now. I had to take another computer class to fufill the requirements for the degree im working towards. This class fit so i figured what the hell. Any advise would be great if you have experince with Access and are willing to do the project i would be willing to paypal you some money. I feel so bad for asking that. My name is Patrick Monroe in the database.


    Thank you so much in advance

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    First tip: linking by Last Name is not wise. What will happen if you have Jane Smith and John Smith? Your relationships will fail.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    3
    That is a good point thanks for looking at it

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome

    I also don't remember seeing any forms in your db, so you might want to consider making some... unless I'm mistaken and they were just hidden from view.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Check out the following rules for constructing a good data base:
    1- Let your tables always have a Primary key and let it to be an auto number
    2-dont use the fields which will be edited as primary key.
    3-dont use the fields which need data entry as primary key.
    4-make those fields which will be used for search or sorting as indexed field to speed up your searches and sorting.
    5-make relationships between tables between primary key field and a filed with same type and same data in the other table which let you to enforce the referential integrity.
    If you choose not to enforce referential integrity, you can add new records, change key fields, or delete related records without warnings about referential integrity violations—thus making it possible to change critical fields and damaging the application’s data.
    With no integrity active, you can create tables that have orphans (Sales without a Contact). With normal operations (such as data entry or changing information), referential integrity rules should be enforced.
    Enforcing referential integrity also enables two other options (cascading updates and cascading deletes) that you may find useful.
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What was I thinking... scratch post 4's advice.... don't make any forms until you're sure your table structure is correct!!

    And yes, always enforce your relationships!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2008
    Posts
    3
    Thanks for your help guys was up all night reading dont know how well this is going turn out ill see how it goes

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm going to contradict Aran - 1 and 3 are not universal truths - they are specific to using surrogate keys. I would suspect that if you are studying the principles of database design that there will be a preference for natural keys or at least some indifference. I also consider thinking in terms of natural natural keys (where possible) to be more conducive to learning relational design - a lot of the mistakes we see here are due to a poor understanding of surrogate keys.

    I would also not consider indexes - again I suspect that you are being tested on your logical design not your physical design. Indexes (other than those created by the RDBMS to enforce constraints) are best considered in the context of the SQL acting on the database.

    Both the above points you will need to take in context of your course - if your studies so far in any way contradict these then completely ignore me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Aran1
    Check out the following rules for constructing a good data base:
    1- Let your tables always have a Primary key and let it to be an auto number No a primary key doesn't have to be an autonumber column at all, use autonumber only if you have to (because there is no immediately obvious natural key
    2-dont use the fields which will be edited as primary key.
    3-dont use the fields which need data entry as primary key.
    4-make those fields which will be used for search or sorting as indexed field to speed up your searches and sorting., only if the cost of doing so doenst' adversely affect performance. you dont need to index evry possible field, you do need to index every possible field which is used frequently in searches. the more indexes you have the slower the performance asn the db hgas to do more work rebuilding indexes
    5-make relationships between tables between primary key field and a filed with same type and same data in the other table which let you to enforce the referential integrity.in my books there is virtually never any reason to use a realtionship which isn't enforced by RI rules. Thats the whole point of using a relational DB.... its to enforce data integrity
    If you choose not to enforce referential integrity, you can add new records, change key fields, or delete related records without warnings about referential integrity violations—thus making it possible to change critical fields and damaging the application’s data.
    With no integrity active, you can create tables that have orphans (Sales without a Contact). With normal operations (such as data entry or changing information), referential integrity rules should be enforced.
    Enforcing referential integrity also enables two other options (cascading updates and cascading deletes) that you may find useful. Cascading update fine, cascading deelte is a powerfull weapon, but used inapprpriately a dangerous weapon. Unless you have very good reasons Id suggest you NEVER use cascade delete on masterfiles data, its too easy for soemone to delete a code and nuke masses of information elsechere. used selectively its fine.. say you need to delete an order.. you can then delete all references to that order at the same time.. personally I wouldn't do it Id merely flag the order as cancelled/delete
    Cheers
    ..........
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Aran1
    Check out the following rules for constructing a good data base:
    1- Let your tables always have a Primary key and let it to be an auto number No a primary key doesn't have to be an autonumber column at all, use autonumber only if you have to (because there is no immediately obvious natural key
    The decision as to whether or not to use a surrogate key has nothing to do with whether you can identify a natural key or not. Frankly, if you can't identify a natural key in your table you have screwed up your design.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    The decision as to whether or not to use a surrogate key has nothing to do with whether you can identify a natural key or not. Frankly, if you can't identify a natural key in your table you have screwed up your design.
    Blimey - what attracted a Great White to come swimming with us goldfish?

    I think unless the OP has covered surrogates in his\ her course then we are best leaving the finer (or arguably foundation - take your pick) points well alone.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I saw the thread title "College database final" and smelled chum in the water.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't understand why some developers don't feel the need to create an autonumber field in a table (especially data tables which are updated frequently). Not having an autonumber field just leaves the table prone to updating problems (and it's not horribly difficult to create an autonumber field.) I have to wonder and ask if there is a justifiable reason for not creating an autonumber field in a table? (other than the need to challenge updating data problems.)
    Last edited by pkstormy; 05-06-08 at 15:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pkstormy
    I don't understand why some developers don't feel the need to create an autonumber field in a table (especially data tables which are updated frequently). Not having an autonumber field just leaves the table prone to updating problems (and it's not horribly difficult to create an autonumber field.) I have to wonder and ask if there is a justifiable reason for not creating an autonumber field in a table? (other than the need to challenge updating data problems.)
    'cos sometimes there are better alternatives to using an autonumber column...
    ferisntance an order number, (order No + Detail No), GRN, Cheque No, a VIN.... sometimes "the answer is out there"

    but Id agree autonumber columns are often the right choice, but I don't think they should be regarded as the only choice for a PK
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Agreed - not always for a PK but I'd still have an autonumber field even if I had another field which had some sort of order numbering. It's just not worth the hassle of troubleshooting why data isn't updating on a form and there's no autonumber field (I think Allen Browne has an example of an mdb with a form that doesn't update the table and if you add an autonumber field to the table, vamish, the problems of not updating go away - otherwise you had to issue a docmd.saverecord somewhere on the form.)
    Last edited by pkstormy; 05-06-08 at 16:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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