any database administrators here?

Discussion in 'OT Technology' started by HardTech, Dec 11, 2003.

  1. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    I have a final exam on an ERD (just one of many parts on the final) and would like to know if what I'm doing is remotely correct.

    I've already completed the relational model, just looking to make sure it's correct before converting it into a diagram.

    thanks
     
  2. so you want to know if your relational model makes sense and looks efficient? what are you doing your charts in?
     
  3. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    excel.. we don't have visio in the school labs

    I just want to know if it looks good or not. I'm pretty sure I have the basics down, but I don't feel fantastic about the whole relational model as a whole.

    even though the prof asked us to make it in third normal form, I'm only going to make it in second because we have to make tables about instructors and students, with zip code as an attribute.
     
  4. so your model is currently described in plain text or as an excel spreadsheet?
     
  5. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    my current model with assumptions is in an excel spreadsheet
     
  6. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    post it, you should be able to attach i think
     
  7. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    here it is...

    the Form worksheet is the actual form I have to create the ERD from
     
  8. why does there look to be so much overlap between the bill table and the student table? can't you just stick a student ID in the billing info isntead of a full student name/address profile?

    otherwise, so far, so good ... I'm still looking.
     
  9. ok and why describe their class in the billing table but not in the student table, again related to above?
     
  10. 3NF seems to make a bit more sense than 2NF, to me at least ... and the last section looks a bit messy? is that supposed to be a book table?
     
  11. nm that last part, sry, I see it's all part of the bill table, which again seems redundant ... I'm using openoffice to read your xls, it's not quite as good as original.
     
  12. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    well, the Form worksheet shows the entire bill. All of the information that I put in there shows up on the bill (student name, address, class, major, school, books, courses, etc. etc.)

    do I just need the primary keys from all the tables to show the information? I thought I had to explicitly put those fields in the Bill table to make them show up.
     
  13. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    thank you very much for looking at my relational model, btw
     
  14. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    If I want to make it 3NF, I'd have to create a new table which has just the zip codes and respective states and another table to house all the times, days, and rooms that are available
     
  15. a) the billing table should only store the keys it needs to look info up from other tables
    b) no problem
    c) given the nature of the data, breaking it up like such would make more sense, wouldn't it? unless you have high-volume transactions against your billing table where it makes more sense from a resource perspective to have information duplicated ...
     
  16. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    then you'd have to figure out which classes meet in which room for how long..

    like KSOB200 would be from 8:00-9:15, 9:30-10:45, 11:00-12:15, 12:30-1:00, 1:15-2:30

    and then KSOB201 would be from 8:00-8:50, 9:00-9:50, 10:00-10:50, 11:00-11:50, 12:00-12:50, 1:00-1:50, etc. etc.

    I think it just makes sense to put down the time, days, and room when a particular class will meet.

    I'll see if I can do what you're telling me, though. Thanks a bunch
     
  17. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    how's this?

    sorry about the lines going everywhere
     
  18. looks much better to me. you'll find yourself using those associative/lookup type tables quite a bit in most DB designs.
     
  19. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    I'm going to add another table for the section (Index #) and the time, day, and room it meets in

    that should just about do it, but it's still going to be in a very normalized 2NF due to the zip code.

    this is for a systems analysis and design class, and the teacher stresses that in database design, you can have associative relationships, but you can't have them in an actual database
     
  20. so what does he/she recommend instead? you have a ton of many-to-many relationships IRL, and short of massively duplicative table structure or a DB that implements table-to-table m-m, what's the answer?
     
  21. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    well, say for instance in a transactional database, in theory you can have a customer optionally order many items, but in real life, a customer must order at least one item

    that's my understanding of it, anyway
     
  22. but, for instance, how about in a content management system, which is what I deal with these days, 3-4 different systems actually, 2 written by myself, 2 foisted upon me by coporate dictate, what about a situation where you have an article that fits in multiple categories? you could have a concatenated field in the article row with multiple category IDs delimited and parsed later, but you can't JOIN on that ... so what do you do instead of a lookup table?
     
  23. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    I'd shoot myself

    these are just introductory database courses, and I have no intention on becoming a DBA... I don't know if my professor knows a lot about databases since he's a systems analyst at heart

    you must get paid a lot of money to do what you do
     
  24. :rofl: both on your shooting yourself and on me making bling

    I used to make more than I do now, but these days after a lay-off and the job market, I'm down to 53k :(
     
  25. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    53k is still a respectable sum

    but if you consider how much you actually know and contribute to a company, them paying 53k a year for what you do is a rip-off

    I want to start my own business, but since I've never really worked for a company before, I wouldn't know how to operate one. My plan is to work for a few years while saving up some money and then working my balls off to start a company.

    too bad that's what everybody who is in college wants too
     

Share This Page