Problem
Draw an entity-relationship diagram for a database with companies, people, and professionals (people who work for companies).
Solution
- Entities:
- Company: companyID, companyName, companyLocation, ect
- People: peopleID, name, gender, age, etc
- Professional: professionalID , FK to People, degree, work experience
- Relations:
- People ISA Professionl: 1:1
- Professional WORKS Company: N:1
People who work for companies are Professionals. So there is an ISA (is a) relationship between People and Professionals (or we could say that a Professional is derived from People). So, Professional is a person, but not all people are professional.
Each Professional has additional information such as degree, work experiences, etc, in addition to the properties derived from People.
A Professional works for one company at a time, but Companies can hire many Professionals, so there is a Many to One relationship between Professionals and Companies. This “Works For” relationship can store attributes such as date of joining the company, salary, etc. These attributes are only defined when we relate a Professional with a Company.
A Person can have multiple phone numbers, which is why Phone is a multi-valued attribute.
References
http://tianrunhe.wordpress.com/2012/04/18/er-diagram-for-a-database-of-company/