Advanced Data Modeling Exam Study Guide: Chapter 5, Exams of Information Technology

Download Advanced Data Modeling Exam Study Guide: Chapter 5 and more Exams Information Technology in PDF only on Docsity!

Chapter 5: Advanced Data

Modeling

Exam Study Guide Latest

Updated 2024/

Certified Exam Study Guide

Updated 2024/

The Best Study Notes

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

Discussion Focus

Your discussion can be divided into three parts to reflect the chapter coverage:

 The first part of the discussion covers the Extended Entity Relationship Model. a. Start by exploring the use of entity supertypes and subtypes. b. Use the specialization hierarchy example in Figure 5.2 to illustrate the main constructs. c. Illustrate the benefits of attribute inheritance and relationship inheritance. d. Remember that an entity supertype and an entity subtype are related in a 1:1 relationship. e. Emphasize the use of the subtype discriminator and then explain the concept of overlapping and disjoint constraints in relation to entity subtypes. f. The completeness constraint indicates whether all entity supertypes must have at least one subtype. g. Explore the specialization and generalization hierarchies. h. Finally, explain the use of entity clusters as an alternative method to simplify crowded data models.  The second part of the discussion covers the importance of proper primary key selection. a. Start by clearly stating the function of a PK -- identification -- and how that function differs from the descriptive nature of the other attributes in an entity. Explain the use of PKs to uniquely identify each entity instance. b. Discuss natural keys, primary keys, and surrogate keys. c. Examine the primary key guidelines that specify the PK characteristics. PKs must be unique, non-intelligent, they do not change over time, they are ideally composed of a single attribute, they are numeric, and they are security compliant. d. Finally, contrast the use of surrogate and composite primary keys. Remind students that composite primary keys are useful in composite entities where each primary key combination is allowed only once in the M:N relationship.  The third part of the discussion covers four special design cases: a. Implementing 1:1 relationships. b. Maintaining the history of time-variant data. c. Fan traps. d. Redundant relationships.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

(Text) FIGURE 5.2 A Specialization Hierarchy

The specialization hierarchy shown in Figure 5.2 reflects the 1:1 relationship between EMPLOYEE and its subtypes. For example, a PILOT subtype occurrence is related to one instance of the EMPLOYEE supertype and a MECHANIC subtype occurrence is related to one instance of the EMPLOYEE supertype.

4. What is a subtype discriminator? Given an example of its use.

A subtype discriminator is the attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of the subtype discriminator will determine which subtype the supertype occurrence is related to. For example, an EMPLOYEE supertype may include the EMP_TYPE value “P” to indicate the PROFESSOR subtype.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

5. What is an overlapping subtype? Give an example.

Overlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set; that is, each entity instance of the supertype may appear in more than one subtype. For example, in a university environment, a person may be an employee or a student or both. In turn, an employee may be a professor as well as an administrator. Because an employee also may be a student, STUDENT

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

For questions 7 – 9, refer to Figure Q5.

FIGURE Q5.7 The PRODUCT data model

7. List all of the attributes of a movie.

Recall that the subtype inherits all of the attributes and relationships of the supertype. Therefore, all of the attributes of a subtype include the common attributes from the supertype plus the unique (unique to that subtype) attributes from the subtype. All of the attributes of a movie would be:  Prod_Num  Prod_Title  Prod_ReleaseDate  Prod_Price  Prod_Type  Movie_Rating  Movie_Director

8. According to the data model, is it required that every entity instance in the PRODUCT table be associated with an entity instance in the CD table? Why or why not?

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

No. The completeness constraint for the data model shows a total completeness constraint from PRODUCT to the subtypes. However, the total completeness constraint indicates that every instance in the supertype (PRODUCT) must be associated with one row in some subtype, not all subtypes. Since the subtypes are designated as disjoint, or exclusive, then every row in the supertype is

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

No change over time If an attribute has semantic meaning, it may be subject to updates. This is why names do not make good primary keys. If you have “Vickie Smith” as the primary key, what happens when she gets married? If a primary key is subject to change, the foreign key values must be updated, thus adding to the database work load. Furthermore, changing a primary key value means that you are basically changing the identity of an entity. Preferably single-attribute A primary key should have the minimum number of attributes possible. Single-attribute primary keys are desirable but not required. Single-attribute primary keys simplify the implementation of foreign keys. Having multiple-attribute primary keys can cause primary keys of related entities to grow through the possible

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

addition of many attributes, thus adding to the database work load and making (application) coding more cumbersome. Preferably numeric Unique values can be better managed when they are numeric because the database can use internal routines to implement a “counter-style” attribute that automatically increments values with the addition of each new row. In fact, most database systems include the ability to use special constructs, such as Autonumber in MS Access, to support self-incrementing primary key attributes. Security complaint The selected primary key must not be composed of any attribute(s) that might be considered a security risk or violation. For example, using a Social Security number as a PK in an EMPLOYEE table is not a good idea.

TABLE 5.3 Desirable Primary Key Characteristics

12. Under what circumstances are composite primary keys appropriate?

Composite primary keys are particularly useful in two cases:  As identifiers of composite entities, where each primary key combination is allowed only once in the M:N relationship.  As identifiers of weak entities, where the weak entity has a strong identifying relationship with the parent entity.

To illustrate the first case, assume that you have a STUDENT entity set and a CLASS entity set. In addition, assume that those two sets are related in a M:N relationship via an ENROLL entity set in which each student/class combination may appear only once in the composite entity. The text’s Figure 5.6 (reproduced here for your convenience) shows the ERD to represent such a relationship.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

world as an independent object, but rather as part of an INVOICE.

In both cases, having a strong identifying relationship ensures that the dependent entity can exist only when it is related to the parent entity. In summary, the selection of a composite primary key for composite and weak entity types provides benefits that enhance the integrity and consistency of the model.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

13. What is a surrogate primary key, and when would you use one?

A surrogate primary key is an “artificial” PK that is used to uniquely identify each entity occurrence when there is no good natural key available or when the “natural” PK would include multiple attributes. A surrogate PK is also used if the natural PK would be a long text variable. The reason for using a surrogate PK is to ensure entity integrity, to simplify application development – by making queries simpler – to ensure query efficiency – for example, a query based on a simple numeric attribute is much faster than one based on a 200-bit character string -- and to ensure that relationships between entities can be created more easily than would be the case with a composite PK that may have to be used as a FK in a related entity.

14. When implementing a 1:1 relationship, where should you place the foreign key if one side is mandatory and one side is optional? Should the foreign key be mandatory or optional?

Section 5.4.1 provides a detailed discussion. The text’s Table 5.5, reproduced here for your convenience, shows the rationale for selecting the foreign key in a 1:1 relationship based on the relationship properties in the ERD.

Case ER Relationship Constraints Action I One side is mandatory and the other side is optional.

Place the PK of the entity on the mandatory side in the entity on the optional side as a FK and make the FK mandatory. II Both sides are optional. Select the FK that causes the fewest number of nulls or place the FK in the entity in which the (relationship) role is played. III Both sides are mandatory. See Case II or consider revising your model to ensure that the two entities do not belong together in a single entity.

TABLE 5.5 Selection of Foreign Key in a 1:1 Relationship

15. What are time-variant data, and how would you deal with such data from a database design point of view?

As the label implies, time variant data are time-sensitive. For example, if a university wants to keep track of the history of all administrative appointments by date of appointment and date of termination, you see time-variant data at work.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

known as a fan trap. A fan trap occurs when you have one entity in two 1:M relationships to other entities, thus producing an association among the other entities that is not expressed in the model.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

Problem Solutions

1. Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.

Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of hire, and title. If an employee is an inspector, then the date of certification and the renewal date for that certification should also be recorded in the system. For all employees, the Social Security number and dependent names should be kept. All dependents must be associated with one and only one employee. Some employees will not have dependents, while others will have many dependents.

The data model for this solution is shown in FigP5.1 below.

FIGURE P5.1 Two-Bit Drilling Company ERD

In this scenario, a specialization hierarchy is appropriate because there is an identifiable type or kind of employee (Inspectors), and additional attributes are recorded that are specific to just that kind or type. It is worth noting that if there is only a single subtype, the disjoint/overlapping designation may be omitted – if there is only one subtype then there is no other subtype to overlap or be disjoint

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

2. Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.

Tiny Hospital keeps information on patients and hospital rooms. The system assigns each patient a patient ID number. In addition, the patient’s name and date of birth are recorded. Some patients are resident patients (they spend at least one night in the hospital) and others are outpatients (they are treated and released). Resident patients are assigned to a room. Each room is identified by a room number. The system also stores the room type (private or semiprivate), and room fee. Over time, each room will have many patients that stay in it. Each resident patient will stay in only one room. Every room must have had a patient, and every resident patient must have a room.

The data model for this scenario is given in Figure P5.2 below.

FIGURE P5.2 Tiny Hospital ERD

Note that in this scenario, a specialization hierarchy is not appropriate. While resident patients are an identifiable kind or type of patient instance, there are not additional attributes that are unique to only that kind or type of patient. Participation in a relationship that is unique to a particular kind or type of instance is not sufficient justification for a specialization hierarchy. Indicating that only some instances will participate in a relationship is addressed by the optional participation designation. In this scenario, all resident patients must have a room; however, not all patients are resident patients so ROOM is optional to patient. If students ask about the need for an attribute to distinguish between outpatients and resident patients, remind them that in this limited scenario the only distinction between outpatients and resident patients is whether or not they are associated with a room. Therefore, they can consider the Room_Num foreign key in the PATIENT table can serve in that capacity.

Exam Study Guide Latest Updated

Certified Exam Study Guide Updated

3. Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.

Granite Sales Company keeps information on employees and the departments that they work in. For each department, the department name, internal mail box number, and office phone extension are kept. A department can have many assigned employees, and each employee is