Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    database design (A)

    Hi all
    I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database design (A)

    Unless a project can also belong to many companies, you don't have that relationship right. Try this:

    Each company can have many projects: Companies-->Projects = 1:M
    Each Employee can work on many projects: Employees-->Projects= 1:M
    If each project can be worked on by multiple employees, then the relationship becomes Employees-->Projects = M:M. IN other words, each employee can work on multiple projects and each project can be worked on by mulitple employees.

    You didn't make it clear whether an employee can work on more than one load for a project or what the relationships are there. Nor is it clear what the relationship is between companies and employees. Are employees assigned to companies or to projects?
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: database design (A)

    Can a Project have more than one Company? From what you have described it cannot, in fact you say 'A company can have more than one project'. Therefore the
    Companies --> Projects is M:M (??)
    should probably be
    Companies --> Projects is 1:M

    What's the relationship of Load to Projects?

    You probably need an extra table for 'Employees to Projects' .

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database design (A)

    Thank you for your replies
    with the given above ,
    A project belongs to 1 company
    An employee can work on multiple projects but 1 load for each project
    A project can be worked on by multiple employees.
    I think I need to add onother table to
    Employee (ENO, ENAME, POSITION,PRJ-NAME, LOAD)
    Company (CNO, CNAME, PHONE, CITY)
    Project (PNO, PRJ-NAME, BUDGET)
    but for Normal form I think I need to remove PRJNAME & LOAD from Employee table & create a new table ?????
    Thanks again

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (A)

    Employees:Projects is a many-to-many relationship: one employee can work in several projects, several employees can work in the same project. A M:M relationship is implemented by creating an intermediary table that has a composite PK consisting of the PK's of both sides. Load belongs in this intermediary table: it is an aspect of the employee/project combination.

    Since each project "belongs" to one company, you should include the company identifier in the projects table.

    Employee: (ENO, ENAME, POSITION)
    Load: (ENO, PNO, LOAD)
    Project: (PNO, PNAME, CNO, BUDGET)
    Company: (CNO, CNAME, PHONE, CITY)

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: database design (A)

    Seeing as sami1 said:
    >>An employee can work on multiple projects but 1 load for each project<<
    Wouldn't this mean that Load number should go with Project?

    So what you suggested:
    Employee: (ENO, ENAME, POSITION)
    Load: (ENO, PNO, LOAD)
    Project: (PNO, PNAME, CNO, BUDGET)
    Company: (CNO, CNAME, PHONE, CITY)

    should probably now read:
    Employee: (ENO, ENAME, POSITION)
    Load: (ENO, PNO)
    Project: (PNO, PNAME, CNO, BUDGET, LOAD)
    Company: (CNO, CNAME, PHONE, CITY)
    with the boldings as you had them for the PKs.

    Please correct me of I'm wrong.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (A)

    Hi Pat,

    The way I read it was that an employee has only 1 load for each project, i.e. there shouldn't be multiple loads for a particular project and a particular employee (this would necessitate another intermediary table). But sami1 will have to answer this.

  8. #8
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database design (A)

    Hi
    You're right hans, 1load for ecach project .
    I created, populated the database using Hans' suggested relational anotation & I'm running queries, so far so good. many thanks to you all
    ps: I think to learn data modelling is by practicing, I ve got 2 books they have plenty of case studies but the problem i get is I never know if I've got a correct answer because they don't have answers, does anyone know any website, book where I can get Exercises with solutions ??
    cheers

Posting Permissions

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