LIS2780 Exam

A surrogate key is

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

An entity cluster

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

An entity supertype is

- is a generic entity type that is related to one or more entity subtypes - contains the common characteristics

To remove a transitive dependency, the designer must

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

If a three-way relationship represented by a table that should actually be represented by two binary relationships, the table violates the following NF (choose the lowest one that applies):

4NF

A table that satisfies 2NF:

Always satisfies 1NF

A table that satisfies BCNF:

Always satisfies 3NF

A 1-M relationship is a connection between two tables in which rows of each table can be related to many rows of the other table.

False

A mandatory FK condition produces a strong (identifying) relationship.

False

Time-variant data is equivalent to multivalued attributes.

Solution: new entity, in a 1:M relationship. The historical table is the child table--containing the foreign key.

In a natural join operation, the join columns have the same unqualified name, which is the column name without the table name.

True

Partial completeness is symbolized by ____.

a circle over a single line

In a table in 1NF in which the only candidate key is a single attribute:

2NF may not be violated

A mandatory FK condition produces a strong (identifying) relationship.

The 1 side must be loaded first.

A relational algebra operator applied to table Employee and table Department produces the following result: Empno | Empname | Empdeptno | Deptname 555 | Kearny | 300 | Marketing 666 | Williams | 200 | Finance That operator is:

The natural join operator

A violation of BCNF is typical of the following condition(s) on a table:

The table has two candidate keys that share a common attribute

The divide operator is more difficult to use than a join because the matching requirement is more stringent.

True

An entity cluster is formed by combining multiple interrelated entities into ____.

a single abstract entity object

Two tables that are not union compatible can be made union compatible using the project operator.

True

A table is 2NF when

all the key attributes are defined (no repeating groups in the table) , all remaining attributes are dependent on the primary key, and it includes no partial dependencies.

A table is 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.

A table that satisfies 2NF

always satisfies 1nf

A table that becomes BCNF

always satisfies 3NF

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.

Denormalization will result in _____.

increased performance

A subtype discriminator

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

A table in 1NF in which the unique candidate key consists of two of its three attributes:

may violate 2NF

Partial completeness means

not every supertype occurrence is a member of a subtype

Within a specialization hierarchy, every subtype can have ____ supertype(s) to which it is directly related.

only one

The entity subtype will store the data that is

unique to the subtype

Overlapping subtypes

each entity instance of the supertype may appear in more than one subtype

A table is BCNF when

every determinant in the table is a candidate key.

One important inheritance characteristic is that all entity subtypes inherit their ____ key attribute from their supertype.

primary

A specialization hierarchy depicts the

arrangement of higher-level entity supertypes and lower-level entity subtypes

A table is 3NF when

when it includes no partial dependencies and it contains no transitive dependencies.

Normalization is

- the process for evaluating and correcting table structures to minimize data redundancies. - the process for evaluating and correcting table structures to reduce the likelihood of data anomalies. - the process for evaluating and correcting table structures to minimize and/or eliminate data integrity problems.

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?

-Place the PK of the entity on the mandatory side in the entity on the optional side as a FK - make the FK mandatory

Generally, it is best *not* to use composite primary keys. However, if they are used, they can be useful as identifiers of

-composite entities, where the primary key from each parent entity resolves a M:N relationship -weak entities, where the weak entity has a strong identifying relationship with the parent entity

When converting the ERD to a relational table, what would be the primary key of the Event table?

-event_id -the combination of date, time_started, room_num

When trying to determine a primary key for and entity, what characteristics of an attribute make an attribute a potential primary key?

-the attribute should be unique for all instances of an entity -the attribute should be stable (not an attribute that changes value over time) -the attribute should be single purpose (used for identification of an entity instance, not other purposes)

Primary key should have

-unique values -no embedded semantic meaning (nonintelligent) -no change in value over time -least number of attributes possible, preferably one -preferably numeric -no attributes that are security risk (social security number, etc)

A table MOVIES in a database consists of the attributes title (the primary key), year-produced, and actor-name; actor-name is a repeating group; different movies may have different numbers of actors. The table MOVIES violates the following normal form (choose the lowest one):

1NF

For most business transactional databases, we should normalize relations into ____.

3NF

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.

What is a partial dependency?

A partial dependency exists when a nonprime attribute is dependent on only a portion of the primary key.

A relational algebra operator applied to table Employee and table Department produces the following result: Empno | Empname | Deptno | Deptname | | 100 | sales 555 | Kearny | 300 | Marketing 666 | Williams | 200 | Finance That operator is:

An outer join operator

For the 1-1 relationship rule, which of the following statements is not correct?

Entities can be amalgamated when both sides are optional.

Union compatibility requires that each table must have the same number of columns, but the corresponding columns can have different data types because the DBMS will convert them during query execution.

False

For entity supertype and entity subtype, which of the following statement is not correct?

If there is only one entity subtype, both sides are mandatory in a one-to-one relationship

For the total, overlapping constraint, which of the following statements is not correct?

Implementation is based on the value of the single-attribute subtype discriminator.

A table in 1NF in which the unique candidate key consists of two of its three attributes:

May violate 2NF

The most likely data type for a surrogate key is ____.

Numeric

For a violation of 2NF to occur in a table in 1NF, the following condition(s) must exist:

Part of a key determines a nonkey attribute

A one-sided outer join can be useful in preserving rows in the result table in a situation where a table has null values in a foreign key.

True