-2

Problem:

Design an ER diagram where:

  • An item has the attribute: description.
  • An item can be sold by a company or a person.
  • A person has the attributes: name, phone and email.
  • A company has the attributes: company name, address and a contact person who is one from the person entity set.
  • A contact person cannot sell the same item with the company he works for.

This is my design:

enter image description here

I'm learning. Please correct and point out what is wrong in my design.

  • I'm not sure that I should remove the primary key SellerID in the Seller entity and add companyID to the Company entity and personID to the entity.
  • Is the Contact Person entity connected to the Person entity correctly?
  • How can I demonstrate this constraint: A contact person cannot sell the items (distinguished by item ID) his company is selling.
philipxy
  • 14,867
  • 6
  • 39
  • 83
Louis Tran
  • 1,154
  • 1
  • 26
  • 46
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Feb 11 '20 at 04:22
  • 1
    Please ask exactly 1 specific non-duplicate question re the 1st place you are stuck. Show relevant steps of your work following your reference/textbook, with justification. If you "are not sure" then you need to find & give/reference what steps you are following & then ask re how you are stuck following them. Otherwise you are asking us to guess which of many methods you are following & guess how you applied it including what choices you made & guess where you got stuck. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Feb 11 '20 at 04:30
  • Covered many times already. Better asked on the sister site [*DBA.StackExchange.com*](https://dba.stackexchange.com/) such as [here](https://dba.stackexchange.com/q/243825/19079), [here](https://dba.stackexchange.com/q/6108/19079), [here](https://dba.stackexchange.com/q/6069/19079), and more. – Basil Bourque Feb 11 '20 at 08:25

1 Answers1

0

Based on the use-case you have outlined here is my suggestion.

  1. Seller should be the anchor entity to Company and Person sub-entities. Basically Seller table should have an unique identifier column (Seller ID) along with the seller type (Company/Person)

  2. Seller ID should be brought as the foreign key to the Company / Person table. Also, these tables should have their respective primary keys as company ID and Person ID

  3. The association table Contact Person should have the company ID, Person ID. This table should primarily answer 3 questions i) Given a company name, it should list out all the possible contact persons and vice-versa ii) If the company has multiple contact persons, who is the primary/default (Though your use-case has asked for it, in terms of scalability, this should be considered) iii) If the company has multiple contact persons, what is the relation of that person with the company

Meena
  • 1
  • 1