Lab 1 Solution

Problem 1,2,3

Figure P3.1 The ERD for Problems 1-3

1. Use the following business rules to write all appropriate connectivity in the E-R diagram:

a. A department employs many employees, but one department employs each employee.

The answers to question 1 (all parts) are included in the E-R diagram that accompanies Problem 3.

b. Some employees, known as "rovers," are not assigned to any department.

c. A division operates many departments, but one division operates each department

d. An employee may be assigned to many projects, and a project may have many employees assigned to it.

e. A project must have at least one employee assigned to it.

f. One of the employees manages each department, and only one employee manages each department.

g. One of the employees runs each division, and one employee runs each division.

2. Write all the cardinalities into the model.

The answer to question 2 is included in the E-R diagram that accompanies Problem 3.

3. Modify the E-R model by splitting the M: N relationship into two 1:M relationships that are connected through a composite entity. Then rewrite the connectivity and cardinalities to match the changes you have made.

The completed Chen ERD is shown in Figure P3.3. Note that there are two relationships between DEPARTMENT and EMPLOYEE.

Figure P3.3 The Completed Chen ERD for Problems 1-3

Discussion: Note that the ERD shown in Figure P3.3a – and in the Crow’s Foot ERD shown in Figure P3.3b -- reflects several useful features that become especially important when the design is implemented. For example:

• The ASSIGN entity is shown to be optional to the PROJECT. This decision makes sense from a practical perspective, because it lets you create a new project record without having to create a new assignment record. (If a new project is started, there will not yet be any assignments.)

• The relationship expressed by “DEPARTMENT employs EMPLOYEE” is shown as mandatory on the EMPLOYEE side. This means that a DEPARTMENT must have at least one EMPLOYEE in order to have departmental status. However, DEPARTMENT is optional to EMPLOYEE, so an employee can be entered without entering a departmental FK value. If the existence of nulls is not acceptable, you can create a “No assignment” record in the DEPARTMENT table, to be referenced in the EMPLOYEE table if an employee is not assigned to a department.

• Note also the implications of the 1:1 “EMPLOYEE manages DEPARTMENT” relationship. The flip side of this relationship is that “each DEPARTMENT is managed by one EMPLOYEE”. (This latter relationship is shown as mandatory in the ERD. That is, each department must be managed by an employee!) Therefore, one of the EMPLOYEE table’s PK values must appear as the FK value in the DEPARTMENT table. (Because this is a 1:1 relationship, the index property of the EMP_NUM FK in the DEPARTMENT table must be set to “unique.”)

Although you ought to approach a 1:1 relationship with caution – most 1:1 relationships are the result of a misidentification of attributes as entities – the 1:1 relationships reflected in the “EMPLOYEE manages DEPARTMENT” and “EMPLOYEE runs DISISION” are appropriate. These 1:1 relationships avoid the data redundancies you would encounter if you duplicated employee data – such a names, phones, and e-mail addresses – in the DIVISION and DEPARTMENT entities.

Problem 5

Problems 5a-e is answered in the E-R diagram. Shown in Figure P3.5.

Figure P3.5 The ERD for Problem 3.5

To help understand the E-R diagram's components better, the following discussion is likely to be useful:

¨ Each COMPANY may list one or more OPENINGs. Because we will maintain COMPANY data even if a company has not (yet!) hired any of TEC's candidates, OPENING is an optional entity in this relationship. OPENING is existence-dependent on COMPANY, because there cannot be an opening unless a company lists it. Because we have decided to use the COMPANY primary key as a component of the OPENING's primary key, we have satisfied the conditions that will allow us to classify OPENING as a weak entity.

¨ Each job CANDIDATE may have many job HISTORY entries. But keep in mind that a candidate may just have competed job training and, therefore, may not have had job experience (i.e., no job history) yet. In short, HISTORY is optional to CANDIDATE. On the other hand, a job candidate may have had many jobs (remember, TEC is a temp employer!) and, therefore, would have many entries in HISTORY. Finally, HISTORY is clearly existence-dependent on CANDIDATE; it is not possible to make an entry in HISTORY without having a CANDIDATE to generate that history. We will use the CANDIDATE primary key as one of the components of the HISTORY's primary key, thus allowing us to classify HISTORY as a weak entity.

¨ Each CANDIDATE may have earned one or more QUALIFICATIONs. Although a qualification may be listed by a company, there may not be a matching candidate because it is possible that none of the candidates have this qualification. For instance, it is possible that none of the available candidates is a Pascal programmer. Therefore, CANDIDATE is optional to QUALIFICATION. However, many candidates may have a given qualification. For example, many candidates may be C++ programmers. And each qualification may be matched to many job candidates, so the relationship between CANDIDATE and QUALIFICATION is M:N. This relationship must be decomposed into two 1:M relationships with the help of a composite entity we will name CAN_QUAL.

¨ Each job OPENING requires one QUALIFICATION, and any given qualification may fit many openings, thus producing a 1:M relationship between QUALIFICATION and OPENING. For example, a job opening for a C++ programmer requires an applicant to have the C++ programming qualification, but there may be many job openings for C++ programmers! However, a qualification does not require an opening. (After all, if there is no listing with a C++ requirement, a candidate who has the C++ qualification does not match the listing!) Therefore, OPENING is optional to QUALIFICATION. Because there cannot be a listed opening unless it also lists the required qualification for that opening, the OPENING is existence-dependent on QUALIFICATION. We will use the QUALIFICATION primary key as a component of the OPENING's (composite) primary key. Because OPENING is existence-dependent on QUALIFICATION and because it borrows the QUALIFICATION's primary key as component of its own primary key, OPENING is properly classified as a weak entity to QUALIFICATION.

¨ One or more candidates may fill a listed job opening. Also, keep in mind that, during some period of time, a candidate may fill many openings. (TEC supplies temporaries, remember?) Therefore, the relationship between OPENING and CANDIDATE is M:N. We will decompose this M:N relationship into two 1:M relationships, using the composite entity named PLACEMENT as the bridge between CANDIDATE and OPENING. Because there is not necessarily an opening for each candidate, OPENING is optional to CANDIDATE. Similarly, since an OPENING may be listed even when there is no available candidate, CANDIDATE is optional to OPENING. Note the migration of the optional symbols in the E-R diagram. (You may wonder why we show OPENING as a weak entity in the E-R diagram. Actually, OPENING was declared a weak entity in our earlier discussion concerning the OPENING's relationship to COMPANY!) Also, although an opening may not have an available candidate, and a candidate may not fit an opening, the actual placement of a candidate clearly demonstrates that there was a match between a candidate and a listed opening!

¨ There exists an entry in HISTORY for every PLACEMENT entry, but not all HISTORY entries will have a matching placement entry. In other words, all PLACEMENT entries will have a matching HISTORY entry, but PLACEMENT is optional to HISTORY because a position may have been filled by the candidate rather than through the TEC services!