Exam 2 Review Questions

What does *SDLC* mean and what does it portray

*Systems development life cycle* tracks the history of an application in an information system. It traces the history of an information system.

What is the difference between *partial completeness* and *total completeness*

- *Partial completeness:* not every supertype occurrence is a member of a subtype; there may be some supertype occurrences that are not members of any subtype. - *Total completeness:* every supertype occurrence must be a member of at least one subtype.

How do *systems analysis* and *systems development* relate to the discussion about information systems

- *Systems analysis* establishes a need for systems analysis - *Systems development* is process of creating an information system

What is the most common design trap, and how does it occur

- A design trap occurs when a relationship is improperly or incompletely identified and therefore, it is represented in a way that is not consistent with the real world. - The most common design trap is 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

*specialization hierarchy*

- A hierarchy that is based on the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. - Specialization is based on grouping unique characteristics and relationships of the subtypes.

Under what circumstances are *composite primary keys* appropriate

- 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.

No change over time

- 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. - 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?)

To remove a transitive dependency, the designer must perform the following *two* action

- Place the attributes that create the transitive dependency in a separate table. - Make sure that the new table's primary key attribute is the foreign key in the original table.

Suppose that someone tells you that an *attribute* that is part of a *composite primary key* is also a *candidate* key. How would you respond to that statement?

- This argument is incorrect if the composite PK contains *no redundant attributes*. - If the *composite primary key* is properly defined, all of the attributes that compose it are required to *identify the remaining attribute values*. - By definition, a *candidate key* is one that can be used to identify all of the remaining attributes, but it was *not chosen* to be a *PK* for some *reason*. - In other words, a candidate key can serve as a primary key, but it was not chosen for that task for one reason or another. Clearly, a part of a proper (―minimal) composite PK cannot be used as a PK by itself.

*entity cluster*

- a "virtual" entity type used to represent multiple entities and relationships in the ERD. - formed by combining multiple interrelated entities into a single abstractentity object.

*subtype discriminator*

- the attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related.

Discuss the distinction between *top-down* and *bottom-up* approaches to system design

- top-down design starts by identifying data sets and then defines the data lements for each of those sets. This is good for *large complex sets of objects and data* - For *few transactions, entities and relationships*, a bottom-up approach would be better. This involves identifying data elements and grouping them together.

with what normal form is it associated is partial dependency associated

1NF

Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF

A dependency based on only a part of a composite primary key is called a partial dependency; Therefore, if the PK is a *single attribute*, there can be *no partial dependencies*.

Preferably single-attribute

A primary key should have the minimum number of attributes possible. - Single-attribute primary keys simplify the implementation of foreign keys.

What is the minimal data rule and why is it important

All that is needed is there and all that is there is needed.

advantages of using an *entity cluster*

An entity cluster is considered "virtual" or "abstract" in the sense that it is not actually an entity in the final ERD, but rather a temporary entity used to represent multiple entities and relationships with the purpose of simplifying the ERD and thus enhancing its readability.

What is an information system and what is its purpose

An information system is composed of hardware, software, people, processes, databases and application programs to help transform data into information and provides for data collection storage and retrieval

1:1 ER Relationship Constraints: where should you place the foreign key - *Case |:* 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

1:1 ER Relationship Constraints: where should you place the foreign key - *Case 3:* Both sides are mandatory

See case || or consider revising your model to ensure that the two entities do not belong together in a single entity

1:1 ER Relationship Constraints: where should you place the foreign key - *Case 2:* 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

What is *DBLC* and what does it portray

The *database development life cycle* describes the history of the database within an information system.

Nonintelligent

The PK should not have embedded semantic meaning; An attribute with embedded semantic meaning is probably better used as a descriptive characteristic of the entity rather than as an identifier. *Example:* a student ID of "650973" would be preferred over "Smith, Martha L." as a primary key identifer.

What kinds of data would you store in an *entity subtype*

The entity subtype will store the data that is specific to the entity; that is, attributes that are unique the subtype

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.

How would you describe a condition in which one attribute is dependent on another attribute when neither attribute is part of the primary key?

This condition is known as a *transitive dependency*. - A transitive dependency is a dependency of one nonprime attribute on another nonprime attribute. (The problem with transitive dependencies is that they *still yield data anomalies.*)

desirable *primary key* characteristics

Unique values Non intelligent No change over time Preferably single-attribute Preferably numeric Security-compliant

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.

*entity supertype*

a generic entity type that is related to one or more entity subtypes, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype.

*surrogate key*

an artificial PK introduced by the designer with the purpose of simplifying the assignment of primary keys to tables. - usually *numeric*, they are often *automatically generated by the DBMS*, they are *free of semantic content* (they have no special meaning), and they are usually *hidden from the end users*

The *six* phases of *DBLC*

database initial study database design implementation and loading testing and evaluation operation maintenance and evolution

*partial dependency*

exists when an attribute is dependent on only a portion of the primary key

Why is an *entity supertype* used?

in order to minimize the number of nulls and to minimize the likelihood of redundant relationships

A table is in *3NF* when

it is in *2NF* and there are no *transitive dependencies*

The *five* phases of SDLC

planning analysis detailed system design implementation maintenance

Normalization does not eliminate data redundancy; instead, it

produces the carefully controlled redundancy that lets us properly link database tables.

*overlapping subtype*

subtypes that contain non-unique subsets of the supertype entity set; thatis, each entity instance of the supertype may appear in more than one subtype. *Example:* in a university environment, a person may be an employee or a student or both. In turn, an employee maybe a professor as well as an administrator. Because an employee also may be a student, STUDENT and EMPLOYEE are overlapping subtypes of the supertype PERSON, just as PROFESSOR and ADMINISTRATOR are overlapping subtypes of the supertype EMPLOYEE.

Unique values

the PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls

Subtypes can only exist within

the context of a supertype.

*normalization*

the process for assigning attributes to entities.

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

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.

Properly executed, the normalization process eliminates ______, thus . . .

uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies.

What three data anomalies are likely to be the result of data redundancy (How can such anomalies be eliminated)

update anomalies addition anomalies deletion anomalies - Can be eliminated through *data normalization*. - Data redundancy produces data integrity problems, caused by the fact that data entry failed to conform to the rule that all copies of redundant data must be identical.

When is a table in *1NF*

when all the *key attributes are defined* (no repeating groups in the table) and when all remaining attributes are *dependent on the primary key*. - However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and/or transitive dependencies that are based on a non-key attribute.

*transitive dependency*

when an attribute is dependent on another attribute that is not part of the primary key. - This kind of dependency usually requires the *decomposition* of the table *containing* the transitive dependency.

When is a table in *2NF*

when it is in 1NF and it includes *no partial dependencies*. - However, a table in 2NF may still have transitive dependencies

When is a table in *3NF*

when it is in 2NF and it contains *no transitive dependencies*.

For any given *supertype* occurrence, the value of the *subtype discriminator* will determine

which *subtype* the *supertype* occurrence is related to