Understanding Relationships in Entity Relationship Diagrams

School: Centennial College - Course: COMP MISC - Subject: Accounting

The necessary relationships are: COMPANY has OPENING (1:M) QUALIFICATION is earned by CANDIDATE (M:N) CANDIDATE has JOB_HISTORY (1:M) OPENING requires QUALIFICATION (1:M) OPENING is filled by CANDIDATE (M:N)

CANDIDATE has PLACEMENT (1:M) COURSE develops QUALIFICATION (1:M) SESSION offers COURSE (1:M) The connectivity for each relationship is: COMPANY has one or more OPENING (1:M) Each OPENING is associated with one COMPANY (M:1) Each QUALIFICATION is earned by one or more CANDIDATEs (M:N) Each CANDIDATE earns one or more QUALIFICATIONs (M:N) Each CANDIDATE has one or more JOB_HISTORY records (1:M) Each JOB_HISTORY record is associated with one CANDIDATE (M:1) Each OPENING requires one QUALIFICATION (M:1) Each QUALIFICATION is required by one or more OPENINGs (1:M) Each OPENING can be filled by one or more CANDIDATEs (M:N) Each CANDIDATE can fill one or more OPENINGs (M:N) Each CANDIDATE has one or more PLACEMENT records (1:M) Each PLACEMENT record is associated with one CANDIDATE (M:1) Each COURSE develops one QUALIFICATION (1:M) Each QUALIFICATION can be developed by one or more COURSEs (M:1) Each SESSION offers one COURSE (M:1) Each COURSE can be offered in one or more SESSIONs (1:M) The mandatory and optional dependencies for the relationships are: A COMPANY must have at least one OPENING (mandatory) An OPENING must be associated with one COMPANY (mandatory) A QUALIFICATION must be earned by at least one CANDIDATE (mandatory) A CANDIDATE must earn at least one QUALIFICATION (mandatory) A CANDIDATE must have at least one JOB_HISTORY record (mandatory) A JOB_HISTORY record must be associated with one CANDIDATE (mandatory) An OPENING must require one QUALIFICATION (mandatory) A QUALIFICATION must be required by at least one OPENING (mandatory) An OPENING can be filled by at least one CANDIDATE (optional) A CANDIDATE can fill at least one OPENING (optional) A CANDIDATE must have at least one PLACEMENT record (mandatory) A PLACEMENT record must be associated with one CANDIDATE (mandatory) A COURSE must develop at least one QUALIFICATION (mandatory) A QUALIFICATION must be developed by at least one COURSE (mandatory) A SESSION must offer one COURSE (mandatory) A COURSE can be offered in at least one SESSION (optional) 2

To resolve M:N relationships in the ERD, we need to introduce a new entity that represents the intersection between the two entities involved in the M:N relationship. For the given scenario, we have two M:N relationships: The M:N relationship between the COURSE and QUALIFICATION entities The M:N relationship between the CANDIDATE and QUALIFICATION entities To resolve these M:N relationships, we can introduce two new entities: A new entity called COURSE_QUALIFICATION, which represents the intersection between COURSE and QUALIFICATION. This entity will have a many-to-one relationship with COURSE and QUALIFICATION entities, and will have the attributes of course_id and qualification_id. A new entity called CANDIDATE_QUALIFICATION, which represents the intersection between CANDIDATE and QUALIFICATION. This entity will have a many-to-one relationship with CANDIDATE and QUALIFICATION entities, and will have the attributes of candidate_id and qualification_id.