Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Create table,Relationship And Query

    Hello everyone.
    I need to complete my assigment and the same time i'm newbie in Microsoft Access. Can't you all guide me. I open this thread to make some discussion. I try to make it, if anything wrong with it, just reply my mistake and i try to make it.

    This is my question :>

    DEPT(DeptNo, DeptName,Location,NumOfStaff)
    EMP(EmpNo,EmpName,Salary,SupervisorNo,DeptNo)
    WORKS(EmpNo,ProjNo,Role)
    PROJ(ProjNo,ProjName,ProjType,Budget)

    Using microsoft access,create the tables and enter sample (7-8 record per table). Make sure create the relationship between tables. Then write SQL queries.

    sorie my english not good.

    For table > i already create it, this is my sample

    http://lulzimg.com/i23/8c5e81.jpg

    My first problem is my relationship

    I edit for primary key is underline (28/6/2011)
    Last edited by bdkminang; 06-27-11 at 20:59.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it appears that you have a relationship between DEPT and WORKS, which is wrong

    and you are missing the other relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats odd
    you've done the tables, the coluns are there and you haven't completed the relationships
    to create a relationship, in the relationship view / window

    click on the column you want to be the 'parent', and then drag to the child column
    check the properties for the relationship are correct

    So I'd suggest you try to define the relationships on your own and see what happens. To start off with an employee belongs to a department, so there should be a realtionship between the DeptNo in EMP and the DeptNo in DEPT

    as a Supervisor is almost certainly an employee, there (probably) should be a realtionship between SupervisorNo and EmpNo within the table EMP

    incicentally Im suspicious of the design, where you store 'NumOfStaff' in Dept, as thjis is derived data and can be got from the EMP table at any time.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2011
    Posts
    8
    thanks for reply.. i try to understand and make some update.

  5. #5
    Join Date
    Jun 2011
    Posts
    8
    how about this?

    Relationship

    EMP to DEPT = one to one
    DeptName to ProjNo = one to many
    PronNo to EmpNo = one to one

    If wrong can we discusss about this. hope you all can guide me on this

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdkminang View Post
    EMP to DEPT = one to one
    you're suggesting a department can have oly one employee?

    whoa, that sounds incorrect to me

    it's an unusual assignment that would restrict departments to only one employee each
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Posts
    8
    Thanks to reply r937

    you're right. one department must have many employee. i repair it right now.
    How about this

    Relationship

    EmpNo to DeptName : one to many
    DeptNo to ProjNo : one to many

    How about this? it's ok?

    If dont have anything wrong i want to continued next step for query.
    Last edited by bdkminang; 06-27-11 at 23:18.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdkminang View Post
    How about this? it's ok?
    no, not yet

    exactly how are department and project related?

    also, works isn't related to employee, and it should be
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2011
    Posts
    8
    exactly how are department and project related?

    also, works isn't related to employee, and it should be
    How about this,

    DEPTt have many Works, and one works have one ProjNo.

    So it become like this

    Department to Works = one to many
    Works to PROJ = one to one

    My Relationship like this.

    Relationship

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdkminang View Post
    Department to Works = one to many
    nope, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    apply the same process as to how you identified that a department could have multiple employees.

    work through each table and:-
    decide if there is a relationship with another table, and if so:-
    what is common between the two
    what type of realtionship it is (one to one, one to many, one to none..many, indetfying or non identfying (ie is the foreign key forms part fo the primary key in the 'child' table
    which side is the 'parent' and which side is the 'child'
    and so on.

    if you are struggling there are some good references out there, not least of which is
    Fundamentals of Relational Database Design -- r937.com

    ..still I suppose you could also buy that site owners book Simply SQL (see above for details)
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2011
    Posts
    8
    still not get what the solution, i try to understand it properly.

    Originally Posted by r937
    nope, sorry
    One department have one works (one EmpNo) so that mean = one to one.

    how about this. I really dont understand to create a relationship.

    Relationship

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdkminang View Post
    I really dont understand to create a relationship.
    keep in mind that in order to have a relationship, a column value from one table has to match a column value from another table

    these are the join columns when you write a query

    you do not have this kind of a columnar relationship between between dept and works, and yet you have a line on the diagram

    methinks the diagramming tool doesn't understand relationships either

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2011
    Posts
    8
    can you write a simple sample to make me understand it.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    FWIW the diagramming tool looks like the 'relationships' view in Access
    I don't think the problem neccessarilly lies with the diagramming tool, more to do with the way the relationships are being defined. Access permits relationships to be defined in multiple ways, not all of which apply to any accepted modelling approach.

    what efforts have you made to understand relationships
    did you read the article by Paul Litwin referred to above

    in the wild one to one relationships are very very rare, they exist but are more to do with system limits or security concerns.
    most relationships are one to many ie there is one row in the parent table and any (0 to multiple) in the child table
    there is another situation where there is a many to many relationship, in practice this is usually modelled as an intersection table which acts as an intermediary between the two many's.

    ferinstance
    consider a manufacturer of cars
    a car maker makes many models / designs of cars
    each car design can have multiple styles, trim levels
    each trim level can have multiple colours...

    so there would be a one to many realtionship between
    Cars and Models
    Models and TrimLevels

    ..however we have the problem of how to model the colour and trim level which logically is a many to many relationship but that is tricky to implement in the real world. a way round that problems is to create an intersection table which acts as a one to many relationship to both sides of the many to many relationship.

    in this case say a table called, say modelbuilds

    Colours and ModelBuilds a one to many relationship identifying what colours are used
    TrimLevels and ModelBuilds a one to many relationship identfying what Trim levels are used
    ..combining the two elements in an intersection table identifies what colours and trim level combinations are available

    intersection tabel - Google Search

    you cna expand the car maker model further by defining a specific list of accessories the manufacturer
    then you can associate those accesories in two ways
    stuff that is part of a specific trim level (ie the base trim level may have a cheap(ish) radio, the next level up in the same range may be a CD Radio, the highest spec may be an integrated DVD/CD/Radio and GPS.
    equally there may be a list of options available on the model
    a customer for the base range in the model could add the integrated DVD/CD/Radio and GPS. some accessories will not be available to all trim levels, some accessories may be available across all products made by that manufacturer, some not

    so in that case you could have a table for accessories
    off that an intersection table which identifies what accessories are available to a specific trimlevel, in much the same way as you associate a colour with a trim level. bear in mind that you may actually have to identify the association as a 'child' of modlebuilds as accesories may be specific to a particular colour of a particular trimlevel or a model of car.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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