Results 1 to 7 of 7

Thread: Database tables

  1. #1
    Join Date
    Apr 2011
    Posts
    10

    Database tables

    Hi guys,

    I have to create a database in Access and I desperately need some help.

    One of the things I need the database to store is general information about technicians, (things like name address, telephone etc) that's quite easy to do.

    However I also need to store the following information about technicians:

    Qualification level- assume that there are 5 levels of training technicians receive and a corresponding qualification for each. So a technician does a training course and then at a later date does an exam which they either pass or fail. In terms of storing data, the technician can take the course and the exam (or the exam without the course) as many times as they like. Level 1 is the basic training. Also, there are new models course which they have to take regularly. However, there are no exams for it.

    To store all this I was thinking about creating 3 tables.

    1st Table: Qualification Exam
    QualExamID#
    TechnicianID - (foreign key from technician table)
    Level
    ExamDate
    Result

    2nd table: Qualification Course
    QualificationCourseID#
    TechnicianID foreign key
    CourseDate
    Level
    QualificationExamID

    New Models Course table
    NewModelsID#
    TechnicianID foreign key
    CourseCode
    Date

    In my ERD, I have only one qualification entity and that creates problems when I have to write the attributes for it. Should the relationship between technician and qualificiation be many to many or 1 to many?


    Is there a better/more efficient way of doing this? Any suggestions?

    I don't really know how to explain myself clearly so I'm attaching the exact question we received.

    Thanks !

    Fred
    Attached Files Attached Files
    Last edited by AccessDatabase; 04-08-11 at 13:20. Reason: description

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AccessDatabase View Post
    Is there a better/more efficient way of doing this?
    i don't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    10
    I updated my question!

    Still looking for help,

    thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    attaching the actual homework assignment isn't going to help us help you

    we're not going to do the assignment for you

    please try to ask a ~specific~ question, and do not ask us read that monolithic piece of crap (your instructors should be shot, by the way)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    10
    Quote Originally Posted by r937 View Post
    attaching the actual homework assignment isn't going to help us help you

    we're not going to do the assignment for you

    please try to ask a ~specific~ question, and do not ask us read that monolithic piece of crap (your instructors should be shot, by the way)
    My question is it correct for one entity, in this case Qualification, to have two tables?

    At the moment I have one Qualification entity but two tables about it "new course" and "Qualification".

  6. #6
    Join Date
    Apr 2011
    Posts
    10
    Because apparently there is a way I can combine the new models table and qualification table in one. But I just can't see how to do that without having blank spaces in my database...
    Last edited by AccessDatabase; 04-08-11 at 13:39. Reason: spelling

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AccessDatabase View Post
    My question is it correct for one entity, in this case Qualification, to have two tables?
    in general, yes

    however, if there are two tables, and the relationship isn't exactly one-to-zero-or-one, then chances are you should have had two entities

    i say "should have had" because splitting an entity into two tables is only done during the physical design stage of database development, by the way -- long after the logical design is completed

    during the logical design stage, which involves entity-relationship diagrams, there is no such thing as a table, and all keys are natural keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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