Table P6.4 Sample STUDENT Records

a.     Write the relational schema, draw its dependency diagram, and identify all dependencies, including all transitive dependencies.

 The dependency diagram for problem 4a is shown in Figure P6.4a.

Figure P6.4a The Dependency Diagram for Problem 4a

Note 1: The ADV_LASTNAME is not a determinant of ADV_OFFICE or ADV_PHONE, because there are (potentially) many advisors who have the same last name.

Note 2: If a department has only one phone, DEPT_CODE is a determinant of DEPT_PHONE. If a department has several phones, the DEPT_CODE is no longer a determinant of DEPT_PHONE. In any case, if you know the DEPT_PHONE value, you know the DEPT_CODE value. Therefore, DEPT_PHONE is a determinant of DEPT_CODE. This latter dependency, indicated in orange, sets the stage for a BCNF violation when the initial structure is normalized.

Note 3: ADV_OFFICE is a determinant of ADV_BUILDING if the ADV_OFFICE is , in effect, a code. For example, if offices such as HE-201 and HE-324 use the prefix HE to indicate their location in the Heinz building, the office locators determine the building.

As you discuss Figure 6.4a, note that the single attribute PK (STU_NUM) automatically places this table in 2NF, because it is not possible to have partial dependencies when the PK consists of a single attribute.

The relational schema for the dependency diagram shown in Figure P6.4a is written as:

STUDENT(STU_NUM, STU_LNAME, STU_MAJOR, DEPT_CODE, DEPT_NAME, DEPT_PHONE, ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE, STU_GPA, STU_HOURS, STU_CLASS)

b.     Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

NOTE

Although the completed student hours (STU_HOURS) do determine the student classification (STU_CLASS), this dependency is not as obvious as you might initially assume it to be. For example, a student is considered a junior if that student has completed between

61 and 90 credit hours. Therefore, a student who is classified as a junior may have completed 66, 72, or 87 hours or any other number of hours within the specified range of 61– 90 hours. In short, any hour value within a specified range will define the classification.

The normalized structure is shown in Figure P6.4b. The relational schemas are written as:

STUDENT(STU_NUM, STU_LNAME, STU_MAJOR, DEPT_CODE, ADVISOR_NUM, STU_GPA, STU_HOURS, STU_CLASS)

(Note that we have added the ADVISOR_NUM to serve as a FK to the advisor attributes.) 

MAJOR(MAJOR_CODE, DEPT_CODE, MAJOR_DESCRIPTION)

BUILDING(BLDG_CODE, BLDG_NAME, BLDG_MANAGER)

DEPARTMENT(DEPT_CODE, DEPT_NAME, DEPT_PHONE, COLLEGE_CODE)

COLLEGE(COLL_CODE, COLL_NAME)

(After studying Chapter 4, ―Entity Relationship Modeling,‖ your students should know enough about database design to suggest many improvements in the design before it can be implemented.)

Figure P6.4b The Normalized Dependency Diagrams for Problem 4b

As you discuss Figure P6.4b, explain that, in this case, the STUDENT table structure indicates a 2NF condition because two transitive dependencies exist. If there is an information requirement to track the components of each major, we can break out a major code, store it in student, create a new entity named MAJOR, and relate it to its department in a 1:M relationship. (Each department offers many majors, but only one department offers each major.) Creating a new entity to eliminate the student classification-induced transitive dependency increases implementation complexity needlessly; student hours are updated each semester by application software and other application software can then use a look-up table to update the classification when necessary. Structure simplicity is a virtue. In any case, the normalization diagram may be modified as shown next. (We have added a few attributes, such as BLDG_MANAGER, to improve the database's ability to provide information.) Note that the assumptions inherent in the business rules also make an impact on normalization practices!

If the room is numbered to reflect the building it is in – for example, HE105 indicates room 105 in the Heinz building – one might argue that the ADV_OFFICE value is the determinant of the ADV_BUILDING. (You will learn in Chapter 6 that you can create a query to find a building by looking at room prefixes.) However, if you define dependencies in strictly relational algebra terms, you  might  argue  that  partitioning  the  attribute  value  to  ―create‖  a  dependency  indicates  that  the partitioned attribute is not (in that strict sense) a determinant. Although we have indicated a transitive dependency from ADV_OFFICE to ADV_BUILDING, we have used a dotted line to indicate that there is room for argument in this set of transitive dependencies. In any case, the (arguable) dependency ADV_OFFICE ® ADV_BUILDING does not create any problems in a practical sense, so it is acceptable to ignore this (arguable) transitive dependency.

Keep in mind that the decomposition shown in Figure P6.8 is subject to many modifications, depending on information requirements and business rules. For example, both the department and the college may be tied to the building in which they are located. Additional modifications are discussed in the answer to Problem 9.

b.      Draw the Crow’s Foot ERD.

NOTE

This ERD constitutes a small segment of a university’s full-blown design. For example, this segment might be combined with the Tiny College presentation in Chapter 4.

The Crow’s Foot ERD is shown in Figure P6.4c.

Figure P6.4c The College ERD

As you examine the ER diagrams in Figure P6.4c, note that we have made several assumptions that cannot be inferred directly from the dependency diagram in problem 4b. For example:

• Apparently, some buildings do not house Some buildings may be used for storage, others for classrooms, and so on.
• When a student is assigned to a department, that department must assign an advisor to that That is, a student must have an advisor. Therefore, ADVISOR is mandatory to STUDENT.
• Evidently, some advisors do not (yet?) have students assigned to them. From an operational point of view, this optionality is desirable, because it enables us to create a new advisor without having to assign a student advisee to that new advisor. (The new advisor may have to receive some training before having students assigned to him or her.)
• Some departments do not offer majors. For example, a department may offer service courses only.
• Some colleges do not have This condition is subject to a business rule that is not specified, nor can it be inferred from the dependency diagram. However, this characteristic is not unusual in a college environment. For example, some professional curricula are certified by special boards. Such boards may make certification conditional on the professional curriculum’s independence. (We have created the optionality for discussion purposes. This discussion should stress the importance of the business rules. You generate the business rules by asking detailed questions!)
• All departments must be affiliated with a college.
• STUDENT is optional to MAJOR. This optionality, too, is desirable from an operational point of view. For example, new majors may not (yet) have attracted students.

  Business rules may change the nature of the structures shown here. For example, an advisor is likely to be a professor ... who is an employee of the university. Therefore, you might introduce a superset/subset relationship between EMPLOYEE and PROFESSOR, while the need to distinguish between professors and advisors disappears. Similarly, EMPLOYEE may be the source of information concerning the BUILDING manager, thus creating a relationship between BUILDING and EMPLOYEE.

   

  Note also that the nature of the relationships (1:1, 1:M, M:N) is not revealed by the dependency diagrams. For example, the 1:M relationship between MAJOR and DEPARTMENT (a department can offer many majors, but each major is offered by only one department) cannot be inferred from the dependency diagram. Normalization and ER modeling are part of the same design process!

   

  Finally, note that we have also included several new entities, MAJOR and BUILDING, to reflect the preceding discussion.

  NOTE

  Remind your students that the order of the attribute listing in each entity is immaterial. Although it is customary to list the PK attribute first, there is no requirement to do so. Similarly, whether the STU_LNAME is listed before or after the STU_GPA has no effect on the STUDENT entity’s functionality.