Chapter 8: Object-Relational Modeling

True-False Questions

1.      All two-dimensional tables are relations.

Answer:    False                Page Reference:  219                         Difficulty:  Moderate

2.      Tables with multi valued attributes are in first normal form.

Answer:    False                Page Reference:  219                         Difficulty:  Moderate

3.      Tables representing two or more separate entities typically need to be normalized.

Answer:    True                 Page Reference:  220                         Difficulty:  Moderate

4.      The goal of normalization is to produce well-structured relations.

Answer:    True                 Page Reference:  220                         Difficulty:  Moderate

5.      Transitive dependencies may exist in relations that satisfy second normal form.

Answer:    True                 Page Reference:  220, 222                 Difficulty:  Moderate

6.      A relation in third normal form cannot simultaneously be in second normal form.

Answer:    False                Page Reference:  220                         Difficulty:  Moderate

7.      One goal of normalization is to increase the number of non-key functional dependencies in relations.

Answer:    False                Page Reference:  221                         Difficulty:  Moderate

8.      Every relation must have at least one determinant.

Answer:    True                 Page Reference:  222                         Difficulty:  Moderate

9.      In practice, most databases are normalized to fifth normal form.

Answer:    False                Page Reference:  223                         Difficulty:  Moderate

10.  Generalization is a key element of the relational data model.

Answer:    False                Page Reference:  224                         Difficulty:  Moderate

11.  In order to be considered well-designed, object relations must satisfy first normal form requirements.

Answer:    False                Page Reference:  225                         Difficulty:  Moderate

12.  Aggregation relationships are sometimes implemented using nested tables.

Answer:    True                 Page Reference:  225-226                  Difficulty:  Moderate

13.  The reference mode of establishing relationships between entities involves making use of foreign keys.

Answer:    False                Page Reference:  226                         Difficulty:  Moderate

14.  A foreign key appears in the relation on the many side of a one-to-many relationship.

Answer:    True                 Page Reference:  228                         Difficulty:  Moderate

15.  Many-to-many relationships in conceptual models usually translate to separate object relations in object-relational models.

Answer:    True                 Page Reference:  228-229                  Difficulty:  Moderate

16.  Recursive relationships occur between objects of the same class.

Answer:    True                 Page Reference:  229-230                  Difficulty:  Moderate

17.  All aggregation relationships are 1:m.

Answer:    False                Page Reference:  230                         Difficulty:  Moderate

18.  The number of relations in a finalized object-relational model equals the number of entity classes from the conceptual data model.

Answer:    False                Page Reference:  231                         Difficulty:  Moderate

19.  Synonyms are techniques for implementing aggregation.

Answer:    False                Page Reference:  231                         Difficulty:  Moderate

20.  The existence of many homonyms eases the integration of object relations.

Answer:    False                Page Reference:  232                         Difficulty:  Moderate

Multiple-Choice Questions

21.  A relation is a(n):

a.       association between objects.

b.      dependency between attributes.

c.       pointer from one row to another in a table.

d.      named table of data.

Answer: d                         Page Reference:  218-219                  Difficulty:  Moderate

22.  Which of the following is NOT a requirement for a table to be a relation?

a.       The sequence of rows and columns cannot be modified.

b.      The table must have a primary key.

c.       Attributes must contain atomic data.

d.      Entries in a column come from common data source.

Answer: a                          Page Reference:  219                         Difficulty:  Moderate

23.  A relation containing minimum data redundancy and allowing insertion, deletion, and modification of rows without introducing data inconsistencies is considered to be:

a.       generalized.

b.      well-structured.

c.       unified.

d.      referential.

Answer: b                         Page Reference:  219                         Difficulty:  Moderate

24.  Which of the following factors indicates that a table is not well-structured?

a.       It contains multiple foreign keys.

b.      The primary key consists of more than one attribute.

c.       The data in the table represents more than one entity.

d.      Referential integrity is violated when attempting to enter data.

Answer: c                          Page Reference:  220                         Difficulty:  Moderate

25.  The process of converting  complex object data structures into well-structured relations is called:

a.       object-relational modeling.

b.      normalization.

c.       referential integrity.

d.      determinant analysis.

Answer: b                         Page Reference:  220                         Difficulty:  Moderate

26.  A functional dependency is a relationship between two:

a.       attributes.

b.      operations.

c.       objects.

d.      tables.

Answer: a                          Page Reference:  221                         Difficulty:  Moderate

27.  A relation is considered to be in second normal form if it is in first normal form and it has no ____________ dependencies.

a.       referential

b.      functional

c.       partial key

d.      transitive

Answer: c                          Page Reference:  221                         Difficulty:  Moderate

28.  If one attribute is a determinant of a second, which in turn is a determinant of a third, then the relation cannot be:

a.       well-structured.

b.      in 1NF.

c.       in 2NF.

d.      in 3NF.

Answer: d                         Page Reference:  222                         Difficulty:  Moderate

29.  An attribute in one table that references a unique record in another table is called a:

a.       determinant.

b.      foreign key.

c.       referential attribute.

d.      functional dependency.

Answer: b                         Page Reference:  223                         Difficulty:  Moderate

30.  Referential integrity controls relationships between:

a.       attributes in a table.

b.      operations of an object.

c.       instances of a class.

d.      tables in a database.

Answer: d                         Page Reference:  223                         Difficulty:  Moderate

31.  Referential integrity dictates that:

a.       the value of a primary key must appear in a foreign key of the related table.

b.      the value of a foreign key must appear in a primary key of the related table.

c.       the value of a primary key cannot appear in a foreign key of the related table.

d.      the value of a foreign key cannot appear in a primary key of the related table.

Answer: b                         Page Reference:  223                         Difficulty:  Moderate

32.  The essential characteristic of____________ normal form is that every determinant in the table must be a candidate key.

a.       Boyce Codd

b.      Domain Key

c.       Fourth

d.      Fifth

Answer: a                          Page Reference:  224                         Difficulty:  Moderate

33.  Which of the following features is supported in the relational database model?

a.       Complex data types

b.      Multi-valued attributes

c.       Associations with multiplicities

d.      Generalization relationships

Answer: c                          Page Reference:  224-225                  Difficulty:  Moderate

34.  Which of the following is the most likely prediction of future database systems?

a.       Relational technology will be replaced by object-oriented technology.

b.      Relational technology will be extended to include object-oriented features.

c.       Relational technology will dominate without use of object-oriented features.

d.      Relational and object-oriented technologies will both be replaced with hierarchical data models.

Answer: b                         Page Reference:  225                         Difficulty:  Moderate

35.  Object-relational relations that include arrays violate the requirements for:

a.       relational integrity.

b.      first normal form.

c.       unique identification.

d.      minimum cardinality.

Answer: b                         Page Reference:  225                         Difficulty:  Moderate

36.  The links between rows of a master table and those of a nested table are maintained using:

a.       pointers.

b.      foreign keys.

c.       determinants.

d.      clusters.

Answer: a                          Page Reference:  226                         Difficulty:  Moderate

37.  The use of clusters is an object-relational approach for representing:

a.       generalization.

b.      behavior.

c.       cardinality.

d.      aggregation.

Answer: d                         Page Reference:  226                         Difficulty:  Moderate

38.  Which of the following associations is implemented as a separate object relation?

a.       Binary one-to-many

b.      Unary one-to-one

c.       Unary one-to-many

d.      Binary many-to-many

Answer: d                         Page Reference:  228-229                  Difficulty:  Moderate

39.  ____________ are always 1:n relationships.

a.       Recursions

b.      Aggregations

c.       Compositions

d.      Associations

Answer: c                          Page Reference:  230                         Difficulty:  Moderate

40.  Which object-oriented feature is NOT naturally represented in the object-relational data model?

a.       Generalizations

b.      Multi valued attributes

c.       Composition

d.      Relationship degrees

Answer: a                          Page Reference:  231                         Difficulty:  Moderate

Essay and Problem-Solving Questions

41.  What are the three main purposes of object-relational modeling? At which stage in the systems development process does this take place? What are the main steps involved?

Answer:

Object-relational data modeling serves three main purposes: to produce database structures tat are ready for physical design and implementation by a DBMS, to provide entity classes that can be used in sequence diagrams and other dynamic UML diagrams for describing object behaviors, and to enhance and finalize the attributes in the data model. This process usually takes place after conceptual data modeling, as part of the requirements structuring stage of systems development. The main steps involved are (1) review the conceptual data model, (2) examine object-oriented extensions standard relational technology in the selected DBMS, (3) translate the conceptual data model into object relations, integrate redundant object relations into merged object relations, and (4) draw analysis class diagrams. After this, the analysis class diagram will be assigned behavioral responsibilities.

                                          Page Reference:  218                         Difficulty:  Moderate

42.  What is a relation? List the five rules that a table must obey in order to be considered a relation. Finally, what does it mean for a relation to be well-structured, and what is the process used by database designers to achieve well-structured relations?

Answer:

A relation is a named, two-dimensional table of data in which the columns represent attributes and the rows represent records of data. In order to be a relation, a table must obey the following rules. First, each cell in the table must pertain to atomic data, indicating a single value. Second, all data values in a column must be of the same data type, originating from the same range of values. Third, each row of data must be unique; duplicate rows are not allowed. Fourth, the order of the columns in the table can be changed without affecting the meaning or use of the relation. Fifth, the rows of the column can be in any order without affecting the meaning or use of the relation. A well-structured relation is one in which data redundancy is kept to a minimum, and therefore insertions, deletions, and modifications can be made at will without causing inconsistencies or errors in the data. The process of converting tables of a database into well-structured relations is called normalization.

                                          Page Reference:  218-219                  Difficulty:  Moderate

43.  List and describe the three primary normal forms. Then discuss BCNF by indicating what the acronym BCNF stands for and describing the requirement for BCNF.

Answer:

The three primary normal forms are first normal form (1NF), second normal form (2NF) and third normal form (3NF). A table is in first normal form if it satisfies the rules for being a relation, and in particular the requirement that all attributes are atomic. This means there are no multivalued attributes allowed in 1NF. In order to be in 2NF, a table must be in 1NF. Additionally, any attribute that is not part of a primary key must be fully functionally dependent on the entire primary key; in other words there can be no partial functional dependency. In order to be in 3NF, the table must satisfy the requirements for 2NF, plus there can be no transitive dependencies. That means there can be no functional dependency between two or more nonprimary key attributes. BCNF stands for Boyce Codd Normal Form and specifies that every determinant in the table must be a candidate key.

                                          Page Reference:  220, 221-224                      Difficulty:  Moderate

44.  Discuss the concept of functional dependencies. Specifically, give a definition for the terms “functional dependency” and “determinant.” Show the common notation for symbolizing functional dependencies. In a well-structured relation, which attributes should be determinants, and which should not? How are functional dependencies identified in object relations? Can viewing sample data prove or disprove the existence of functional dependencies?

Answer:

A functional dependency is a relationship between attributes of a relation. Given two attributes A and B, attribute B is functionally dependent on attribute A if each value of A uniquely determines the value for B. In this case, A is the determinant of B. Therefore a determinant is the attribute (or set of attributes) that uniquely determine the values in other attributes. The common notation for such a functional dependency is A à  B, where the attribute(s) on the left side of the arrow are the determinants and the attribute(s) on the right side are functional dependent on the determinants. In a well-structured relation, the only determinant should be the primary key, or at least the candidate keys. No attribute that does not uniquely identify a row should be a determinant. Functional dependencies are identified in object relations through careful analysis and complete knowledge of the problem domain. Viewing sample data cannot prove the existence of a functional dependency, but it can possibly disprove the existence if it can be shown that the same value for one attribute coincides with different values for the second.

                                          Page Reference:  221-223                  Difficulty:  Hard

45.  Consider the following relation, called StudentData. Assume that the data shown is a reliable basis for identifying the functional dependencies. Use the notation from the textbook to define this relation. Then identify all functional dependencies, again using the proper notation. Indicate the normal form of this relation and explain why it is in the normal form indicated. If it is not already in 3NF, perform any necessary normalization to put the relation into third normal form.

StudentID	Name	PhoneNbr	Major	Advisor
1001	Alice Long	555-1212	CIS	Smith
1002	Mark Williams	555-2948	Accounting	Jones
1003	Sam Miller	555-2999	Marketing	Baker
1004	Julia Davis	555-4309	CIS	Smith
1005	Latisha Markus	555-1212	CIS	Smith
1006	Forrest Gump	555-9595	Marketing	Baker

Answer:

Relation definition:

StudentData(StudentID, Name, PhoneNbr, Major, Advisor)

Functional dependencies:

      StudentID à Name, PhoneNbr, Major, Advisor

      Major à Advisor

Relation is in second normal form. It satisfies 1NF because rows are unique and there is no multivalued attribute. It satisfies 2NF because there are no partial dependencies (because the primary key is a single attribute). It does not satisfy 3NF because it contains a transitive dependency: StudentID à Major à Advisor.

3NF relation definitions:

      StudentData(StudentID, Name, PhoneNbr, Major)

      MajorData(Major, Advisor)

                                          Page Reference:  221-223                  Difficulty:  Hard

46.  Describe the relational data model and compare it to the object-relational model. What are some shortcomings of relational databases, and how are these overcome in object-relational databases? Which normal form is not necessary to obey in object-relational databases, and why? Finally, how do these two models differ in terms of unique entity identification and relationships between entity types?

Answer:

The relational data model represents data in the form of related tables, called relations. This is the primary data model used in database management systems today. Relational databases suffer from other shortcomings. The relational model does not support generalization. Multivalued attributes are impossible in table-oriented databases, and complex data types are not supported. Also, there is no natural mechanism in relational databases for aggregation. In addition, the relational data model has no means of representing entity behaviors; it is solely a data model. The object-relational data model overcomes all of these shortcomings. Object-relational databases support generalization because they are object-oriented and therefore include inheritance mechanisms. Multivalued attributes are supported in object-relational databases, implemented as arrays. Because of the support for multivalued attributes, object-relational databases can violate the first normal form restriction against multivalued attributes and still be well-structured. The primary mechanisms to implement aggregation are nested tables, where one table is embedded inside another, and clusters, in which tables representing the aggregate and parts are stored physically together to improve retrieval time. Object identification in relational databases is accomplished via a primary key, which is one or more attributes in the table. By contrast, object-relational databases support the use of system-generated object identifiers. Whereas relationships in relational databases are implemented using foreign keys, object-relational databases provide the capability for implementing relationships via references (or pointers).

                                          Page Reference:  218, 224-226                      Difficulty:  Moderate

47.  Given a conceptual data model, what are the steps involved in converting it to a set of object relations? For each step, summarize the main activities that are involved.

Answer:

The first step in translating a conceptual data model into object-relational form is to translate each class into an object relation. The identifier becomes a primary key and all other attributes become non-key attributes in the relation. A second step is to translate relationships into object-relational form. There are two approaches for this. The most common approach is through the use of foreign keys. A less common approach is to use references. Next, the relations should be normalized to remove unnecessary data redundancies; this normalization should ensure that all relations are in at least third normal form. Finally, redundant object relations should be merged, and if necessary, renormalized.

                                          Page Reference:  227                         Difficulty:  Moderate

48.  Discuss how primary and foreign keys are used to implement relationships between tables in a relational or object-relational database. In this discussion, describe the purpose of a primary key and of a foreign key, and define the concept of referential integrity. Then describe the placement of foreign keys in unary and binary one-to-many relationships and in unary and binary many-to-many relationships.

Answer:

Primary and foreign keys are the way that relationships are implemented in relational databases. The purpose of a primary key is to uniquely identify a row in a relation. A foreign key is an attribute that appears as a nonprimary key attribute in one relation and a primary key attribute in the other. The foreign key in the dependent table will contain values that match primary key values in the dominant table. Foreign keys must satisfy referential integrity, which means that the value in a foreign key attribute in one relation must have a matching value in a primary key of the related table. In a unary one-to-many relationship the primary key and the foreign key are in the same relation; this is called a recursive foreign key. In a binary one-to-many relationship, the primary key value in the one side of the relationship has a matching foreign key value in the many side of the relationship. In the case of many-to-many relationships between two relations, a third relation is established whose primary key will be composed of two foreign keys, each relating to one of the two tables of the relationship.

                                          Page Reference:  223, 228-230                      Difficulty:  Moderate

49.  Given the following conceptual data model, convert it into a set of object relations with appropriate attributes, primary keys, and foreign keys. Use the notation from the textbook, and be sure to include the necessary stereotype labels.

Answer:

Person (IDNbr, FirstName, LastName)

FacultyMember<<Under Person>>(IDNbr, Rank, TenureStatus, DeptName)

Student<<Under Person>>(IDNbr, {Major}, GPA)

Course(CourseNbr, CourseName, Credits)

Prerequisite(CourseNbr, PrereqNbr)

Teaches(FacultyID, CourseNbr)

EnrolledIn(StudentID, CourseNbr)

Department(DeptName, RoomNbr, CollegeName)

College(CollegeName, BldgName)

                                          Page Reference:  227-231                  Difficulty:  Hard

50.  Discuss issues regarding the merging of object relations when translating conceptual data models. What circumstances may make it necessary to merge multiple object relations together? List and describe three problems that must be considered when merging object relations, and describe the typical solution to each problem.

Answer:

Translation of conceptual data models into object relations may result in redundant data. This is because the conceptual data models are often developed based on separate use cases, and sometimes multiple use cases require use of overlapping data elements. When redundancies occur, it is often advisable to merge the object relations containing duplicate data elements into a single object relation. When merging object relations, the designer must be careful to address three main issues in relation integration: synonyms, homonyms, and non-key attribute dependencies. Synonyms are attributes from different relations that have different names but refer to the same data; in this case the analyst should work with different user groups to come up with an agreed-upon standardized attribute name.  This usually involves condensing two separate synonyms into a single attribute in the merged relation. Homonyms are attributes of the same name in different relations that refer to different data elements. In this case, the designer should carefully study the same-named attributes to determine their real meanings and assign names indicating the specific meanings of the different data elements. This usually means that the merged relation will contain two attributes, one for each of the homonyms. If non-key dependencies are introduced when merging two relations, this introduces data redundancies, and the merged relation is not well-structured; specifically, it violates the requirements for 3NF. In this case, the relation should be normalized, which means splitting it into two relations to remove the transitive dependency.

                                          Page Reference:  231-232                  Difficulty:  Moderate