Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: Anothe newbie Q on DB design

    Hi all

    I am designing a database for a vehicle maintenance repair centre (theoretically) and have the following relational schema:

    Relation-PK

    • Branch-branchNo
    • Employee-empNo
    • Vehicle-regNo
    • Repairs-empNo and regNo as PK (formed from (*:*))
    • Customer-custNo
    • Buys-custNo and itemNo as Pk (Formed from (*:*))
    • Item-itemNo
    • Stores-branchNo and itemNo (Formed from (*:*))
    • EmployeeQualifications-empNo and qualifications as PK (Formed from multi-value attribute in Employee conceptual relation)

    Once I had the relational schema from the conceptual mapping I entered it into MS Access-set all PK and relationships then started doing queries.

    The first 2 queries worked fine but then seemed to have problem with Employee-Branch tables and it seems that no data shows when I have the 2 tables in the query design. When I put 'Branch' table in the query and extract fields it shows data in datasheet OK but when I put Employee table in then run the query no data shows on the datasheet?

    The Employee relation has a recursive relationship, which when viewed in the query the tables have a (1:*) and an also the recursive line.

    Could it be the recursive relationship in Employee causing the query problem? I'm not 100% sure that the recursive is right in the access design (although I have never done a DB with recursive relationship). I have defined relationship in access as per relational schema, which is OK.

    I haven't included every step in this description as it would be too long but if you know what I'm trying to do and think you know where the problem might lie then i would be greatful to hear any advice.

    Thanks

    Andrew

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i've seen this before, it's probably from some book, it's a homework assignment

    the employee recursive relationship does not affect whether the branch table is properly joined to the employee-branch table

    the relationships which access maintains in the relationship diagram can be used to construct the query join, but you can also do it yourself in query view

    if the data doesn't match, though, then you might get no results

    open both tables, and look at the actual data values in the columns that the query is joinin the tables on (look in query view / sql view to see the JOIN statements)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    4
    Ok will do that. Something must have gone wrong because my first 2 queries, which were initially fine, now have wrong data in them. I have superviser empNo in Employee table as foreign key with null values in it-I thought this might be causing problems?

    Andrew

Posting Permissions

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