Chapter 3 Database Design

disjointedness constraint

A ________ addresses whether an instance of a supertype may simultaneously be a member of two or more subtypes. A) disjointedness constraint B) disjoint rule C) partial specialization D) total specialization

universal data model

A generic or template data model that can be reused as a starting point for a data modeling project is called a(n): A) packaged data model. B) universal data model. C) enterprise data model. D) collection data model

data profiling

A good method for identifying inconsistencies and finding hidden meaning in the customized purchased data model is: A) data analysis. B) data volume usage analysis. C) user interviews. D) data profiling

subtype discriminator

An attribute of the supertype that determines the target subtype(s) is called the: A) determinant. B) subtype decision. C) disjoint indicator. D) subtype discriminator.

Generalization is the process of defining a more general entity type from a set of more specialized entity types. For example, if we had undergraduate and graduate students with similar attributes, then we might create a student supertype. The student supertype would then contain the attributes that all subtypes have in common. Specialization, on the other hand, would occur when we discover that we have a student type but there are different attributes for different types of students. In this case, we would create subtypes which would contain unique attributes for that subtype.

Compare and contrast generalization and specialization.

The disjoint rule is used when you wish to specify that an entity instance can only be one type of subtype. For example, if a student could only be a graduate or undergraduate student, but not both. The overlap rule specifies that an entity instance could be one or more subtypes. For example, if an instance of a person supertype could be a faculty member as well as a student subtype, then we would use the overlap rule.

Contrast the overlap rule to the disjoint rule

Subtypes that are lower in the hierarchy inherit attributes not only from their immediate supertype but also from all supertypes higher in the hierarchy all the way to the root

Discuss how attribute inheritance works in a supertype/subtype hierarchy

Define one vehicle entity type to hold all entities.

Given the following entities, which of the choices below would be the most complicated? Automobile: VIN, EngineSize, NumberOfDoors, NumberOfPassengers, FuelType, Transmission SUV: VIN, EngineSize, NumberOfPassengers, NoWheelDrive, FuelType, Transmission Truck: VIN, EngineSize, NoWheelDrive, FuelType, Transmission, Payload A) Define one vehicle entity type to hold all entities. B) Define a separate entity type for each entity. C) Define a supertype called vehicle and make each of the entities subtypes. D) Keep only the Truck entity type.

A packaged data model is quite extensive, so you would begin with the part of the data model that you will use for your situation. You would start first with entities, then attributes and then relationships. The next step is to rename the data elements to terms that are meaningful to your organization. Since the packaged data model may be used to replace an existing system, the next step is to map the packaged model to the current database. This mapping will be used later for data migration. Perhaps the most challenging step is to determine the business rules for the new system.

How is the data modeling process different when starting with a purchased solution? Answer:

disjoint

In the figure below, the patient must be either an outpatient or a resident patient. This is an example of the ________ rule. A) disjoint B) specialization C) generalization D) overlap

Vehicle

In the figure below, to which of the following entities are the entities "CAR" and "TRUCK" generalized? A) Make B) Vehicle C) Model D) Price

Patient_Name

In the figure below, which of the following apply to both OUTPATIENTs and RESIDENT_PATIENTs? A) Checkback_Date B) Date_Discharged C) Patient_Name D) XML

Outpatient

In the figure below, which of the following is a subtype of patient? A)Outpatient B) Physician C) Bed D) Date_Hired

partial specialization

The ________ rule specifies that an entity instance of a supertype is allowed not to belong to any subtype. A) semi-specialization B) total specialization C) partial specialization D) disjointedness

total specialization

The ________ rule specifies that each entity instance of the supertype must be a member of some subtype in the relationship. A) semi-specialization B) total specialization C) partial specialization D) total convergence

overlap

The ________ rule states that an entity instance can simultaneously be a member of two (or more) subtypes. A) disjoint B) overlap C) partial specialization D) total specialization

entity cluster. An entity cluster is a set of one or more entity types and associated relationships

The figure below is an example of a(n): A) supertype/subtype hierarchy. B) hierarchical data model. C) entity cluster. D) column cluster.

subtype discriminator

The following diagram shows: A) total specialization. B) partial specialization. C) the overlap rule. D) subtype discriminator

FALSE

The following figure is an example of the overlap rule

overlap rule

The subtype discriminator is a composite attribute when there is a(n): A) overlap rule. B) disjoint rule. C) partial specialization. D) full specialization.

map data to be used from package to data in current databases

The third step in the data modeling process with a packaged data model is: A) rename identified data elements. B) rename relationships. C) map data to be used from package to data in current databases. D) interview users

TRUE

There are three separate discriminators in the following diagram because of the overlap rule.

A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time.

Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time

A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time.

Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time

require customization

Packaged data models: A) are ready to use right out of the box. B) require customization. C) allow partial specialization. D) cannot be used for most applications.

Completeness

A(n) ________ constraint is a type of constraint that addresses whether an instance of a supertype must also be an instance of at least one subtype. A) disjoint B) overlap C) completeness D) weak

more one-to-one relationships give the data model more flexibility.

All of the following are advantages of packaged data models EXCEPT: A) packaged data models can be built using proven components evolved from cumulative experiences. B) projects take less time and cost less. C) the data model is easier to evolve. D) more one-to-one relationships give the data model more flexibility.

Attributes are assigned at the highest logical level that is possible in the hierarchy. For example, in the following diagram, one would assign the name to the person supertype so that it could be shared by as many subtypes as possible

Discuss how attributes are assigned in a supertype/subtype hierarchy.

Total specialization would be used when you know that there are no other subtypes of a supertype other than those defined. For example, if we only had undergraduate and graduate students but no other types of students, then we would use total specialization. However, if there were students other than graduate and undergraduate, such as just a general student, then we would want to use partial specialization.

Discuss when one would use total specialization and when one would use partial specialization

only one supertype.

In a supertype/subtype hierarchy, each subtype has: A) only one supertype. B) many supertypes. C) at most two supertypes. D) at least one subtype.

supertypes

In a supertype/subtype hierarchy, subtypes that are lower in the hierarchy inherit attributes from not only their immediate supertype but from all ________ in the hierarchy. A) subtypes B) supertypes C) constraints D) dimensions

total specialization; overlap

In packaged data models, all subtype/supertype relationships follow the ________ and ________ rules. A) partial specialization; disjoint B) total specialization; disjoint C) total specialization; overlap D) partial specialization; overlap

there are attributes that apply to some but not all instances of an entity type

Subtypes should be used when: A) there are attributes that apply to some but not all instances of an entity type. B) supertypes relate to objects outside the business. C) the instances of a subtype do not participate in a relationship that is unique to that subtype. D) a recursive relationship is needed.

FALSE

The following figure is an example of total specialization.

total specialization.

The following figure is an example of: A) partial specialization. B) completeness. C) total specialization. D) disjointness

specialization

The process of defining one or more subtypes of a supertype and forming relationships is called: A) specialization. B) generalization. C) creating discord. D) selecting classes

attribute inheritance.

The property by which subtype entities possess the values of all attributes of a supertype is called: A) hierarchy reception. B) class management. C) attribute inheritance. D) generalization.

Part type - A Subtype Discriminator is an attribute of the Supertype that is used to "code" or denote which Subtype an entity is

The subtype discriminator in the figure below is: A) Part_Type. B) Part_No. C) Manufactured Part. D) Location.

essential components and structures are already defined.

Using a packaged data model, projects take less time and cost because: A) less personnel are required. B) essential components and structures are already defined. C) there is more time taken to model the enterprise. D) packaged data models only run in Oracle.

entities

When identifying the parts of the packaged data model that apply to your organization, one should first start with: A) entities. B) attributes. C) primary keys. D) relationships

Total specialization

Which of the following is a completeness constraint? A) Total specialization B) Partial generalization C) Total recall D) Partial hybridization

utilize all business rules that come with the packaged data model.

All of the following are steps to using a packaged data model EXCEPT: A) identify the parts of the data model that apply to your data modeling situation. B) utilize all business rules that come with the packaged data model. C) rename the identified data elements. D) map data to be used in packages with existing data in the current databases

combining a strong entity and its weak entities

An entity cluster can be formed by: A) deleting a supertype and its subtype. B) combining metadata. C) combining a strong entity and its weak entities. D) deleting metadata.

a set of one or more entity types and associated relationships grouped into a single abstract entity type.

An entity cluster is: A) a formal method for specifying attributes of related entities. B) a set of one or more entity types and associated relationships grouped into a single abstract entity type. C) a useful way to present data for a small and fairly simple organization. D) a way of developing more granular views of the data model.

It is best to use a supertype/subtype hierarchy when there are attributes that apply to some but not all instances of an entity type. For example, if we have an employee entity type and there is an hourly wage attribute that only applies to hourly employees, it may be best to use a supertype/subtype relationship. Another reason for using supertype/subtype relationships is when you have instances of a subtype that participate in a relationship unique to that subtype. For example, if we have a contractor subtype of employee that has a relationship with staffing agency, then it would be best to use supertype/subtype relationship

Discuss the reasoning behind using supertype/subtype relationships

A subtype is an entity which represents data meaningful to the organization. For example, an undergraduate student and a graduate student might be a subtype. A supertype is a generalization of various subtypes and contains attributes which are common to both. Supertypes and subtypes may have relationships with other entities. Also, a subtype inherits the attributes of its supertype.

Explain the terms subtype and supertype. Discuss the differences between them

An entity cluster is a group of entities and relationships which act as one entity. Entity clustering can be thought of as a way to decompose a data model hierarchically, gaining finer and finer views. Entity clusters can interact with other entity clusters just like entities can interact with each other.

Explain what entity clustering is.

The business environment has drastically changed since the relational model was first introduced in the 1970s. Business relationships are more complex, and organizations must have ways to represent data to represent the complexity. Organizations must be prepared to segment their markets as well as customize their products. The enhanced E-R model has evolved to represent these changes and also is similar to the object-oriented data model.

Explain why the E-R model needed to be expanded into the enhanced E-R model.

M:N relationships - many-to-many

In packaged data models, strong entities always have ________ between them. A) weak entities B) 1:1 relationships C) 1:M relationships D) M:N relationships

must be a graduate student, an undergraduate, a special student or some other type of student

In the figure below, a student: A) must be a graduate student, an undergraduate, a special student or some other type of student. B) must be a graduate student or an undergraduate student. C) must be at least a special student. D) must be a doctoral student.

partial specialization.

The following figure is an example of: A) partial specialization. B) disjoint completeness. C) total specialization. D) transunion constraint.

supertype/subtype hierarchy.

The following figure shows a: A) disjoint constraint. B) completeness constraint. C) supertype/subtype hierarchy. D) spindle constraint

the overlap rule.

The following figure shows an example of: A) the disjoint rule. B) the completeness rule. C) the underdog rule. D) the overlap rule

determining the business rules that will be established through the data model.

The most important challenge of customizing a purchased data model is: A) getting user buy-in. B) determining the business rules that will be established through the data model. C) implementation. D) user training.

generalization

The process of defining a more general entity type from a set of more specialized entity types is called: A) generalization B) specialization. C) normalization. D) extrapulation.

Supertype

Which of the following is a generic entity type that has a relationship with one or more subtypes? A) Megatype B) Supertype C) Subgroup D) Class

Relationships are connected to the highest-level entity type in an order that makes sense.

Which of the following is true of packaged data models? A) Relationships are connected to the highest-level entity type in an order that makes sense. B) All subtype/supertype relationships follow the total specialization and disjoint rules. C) No entities on the many sides of a relationship can be weak. D) All weak entities are considered strong.

A rental unit must be either an apartment or a house, and could be both.

Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time.

A person can only be a faculty, student or staff

Which statement is true about the following diagram? A) A person can only be a faculty, student or staff. B) A student can be both an undergraduate and a graduate student at the same time. C) All attributes of person and student are inherited by undergraduate. D) All attributes of graduate are inherited by person.

Most organizations can no longer afford to have data models developed in house, both in terms of labor costs as well as time. As such, the role of data modeler is evolving from artisan to that of engineer. A packaged data model allows one to customize an organization's database needs using a superset of the model needed by the organization

Why are packaged data models gaining popularity?