LU CSIS 325 - CH. 1-3 & 5-7 (Midterm)

With respect to transaction processing, any DBMS should be capable of

all of the above

Narrative analysis is one method of developing the data model. Which of the following are good techniques to help create the narrative analysis?

all of the above A) conducting interviews B) reviewing documents and system documentation C) examining existing data

Which of the following should be included in design documentation?

all of the above A) decisions that might be unclear to others even if there are no feasible alternatives B) documentation of inconsistency in a specification C) documentation of incompleteness in a specification

2NF

keywords are ALL CANDIDATE KEYS Every nonkey column depends on all candidate keys, not a subset of any candidate key

BCNF

keywords are NON-DEPENDENCY CANDIDATE KEYS BCNF does not allow dependencies between attributes that belong to candidate keys Violations: Nonkey -> Part of key Primary key: (OfferNo, StdNo) violations: StdNo -> StdCity, StdClass OfferNo -> OffTerm, OffYear, CourseNo CourseNo -> CrsDesc

3NF

keywords are ONLY CANDIDATE KEYS Every nonkey column depends only on candidate keys, not on non key columns Violations: Nonkey -> Nonkey No transitive FDs A -> B, B -> C then A -> C OfferNo -> CourseNo, CourseNo -> CrsDesc then OfferNo -> CrsDesc

4NF

keywords are SUPERKEY MULTIVALUED DEPENDENCIES (MVD) NOTE, FDs have no weight on whether 4NF is met, exclude this terminology at this point when evaluating Violations: Superkey -> Subset of key A associates with MULTIPLE attributes or a collection of B and C A ->--> B | C (multi-determines) OfferNo ->--> StdNo | TextNo OfferNo StdNo TextNo 01 S1 T1 01 S2 T2 01 S1 T2 01 S2 T1 If MVD is true, the bottom two rows exist: 01 S1 T2 01 S2 T1 *this is dynamic as tables change as a result.

A goal of normalization is to:

minimize data redundancy

A database description

schema

When converting the ERD to a relational table, how many attributes with the RoomAssign table have?

three

Relationship cardinalities:

(All of the above) A) Constrain the number of objects that participate in a relationship B) Are shown for both directions of a relationship C) Indicate whether a relationship is mandatory or optional

The primary key of an entity type can be:

(All of the above) A) Directly derived B) Borrowed C) Inherited

When applying the optional 1-M relationship rule for converting an ERD into a relational table design, which of the following statements is true:

(All of the above) A) Each 1-M relationship with 0 for the minimum cardinality on the parent side becomes a new table. B) The primary key of the new table is the primary key of the entity type on the child (many) side of the relationship. C) The new table contains foreign keys for the primary keys of both entity types participating in the relationship. Both foreign keys in the new table do not permit null values.

Nonprocedural access to a database:

(All of the above) 1. Can provide a dramatic improvement in software productivity. 2. Allows a user to submit queries to a database without having to know how the data will be retrieved. 3.Is supported by more than one tool in most DBMSs.

The SQL database language includes statements for:

(All of the above) Database definition, database manipulation, database control

For the generalization hierarchy rule, which of the following is true?

(All of the above.) A) Each entity type of a generalization hierarchy becomes a table. B) The columns of a table are the attributes of the corresponding entity type plus the primary key of the parent entity type. C) For each table representing a subtype, define a foreign key constraint that references the table corresponding to the parent entity type.

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

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

Key column

- A candidate key by itself - Part of a combined Candidate Key

Nonkey column

- A column that is not a key column

Superkey

- Column or combination of columns containing unique values for each row -The combination of every column in a table is always a superkey because rows in a table - must be unique

Candidate key

- Unique - Minimal: no extraneous columns without losing uniqueness property - Can have multiple candidate keys per table

The Hollywood studio BestFlick wants to have a database designed to have access to the following information about all the movies it ever produced: title, yearreleased, director, actors, stuntmen, "Oscars" awarded. A movie has only one director. In this description, the gender is irrelevant. "Actor", "director", "stuntman" can be male or female. "Oscar for best acting" refers to an Oscar for best actor or best actress and also includes best supporting actor or actress. You would show director OF movie by a:

1-M relationship

A table MOVIES in an object-oriented 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

1NF

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

2NF may not be violated

If a three-way relationship represented by a table that should actually be represented by two binary relationships, the table violates the following NF

4NF

If a three-way relationship represented by a table that should actually be represented by three binary relationships, the table violates the following NF

5NF

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

A and B A. The 1-1 relationship is converted into two foreign keys. B. If the relationship is optional with respect to one of the entity types, the corresponding foreign key may be dropped to eliminate null values.

When applying the M-M relationship rule for converting an ERD into a relational table design, which of the following statements is true:

A and C A. Each M-N relationship becomes a separate table. C. The primary key of the table is a combined key consisting of the primary keys of the entity types participating in the M-N relationship.

When an equi-join is performed on a table of N rows and a table of M rows, the resulting table has the following number of rows:

A number in the range 0 to M*N

The Union of a table of N rows and of a table of M rows (M<=N) has the following number of rows:

A number in the range N to M+N

In the relational database terminology, a table is synonymous with:

A relation

Oscar for best acting would be an attribute of:

A relationship "Starring-in" between Movie and Actor

When the referential integrity rule is enforced, which one is usually not a valid action in response to the deletion of a row that contains a primary key value referenced elsewhere?

Accept the deletion without any other action

With respect to transaction processing, and DBMS should be capable of

All of the above

In a systems development methodology, the following graphical model should be produced:

All of the above (Data model, process model, environment interaction model)

Which of the following is not one of the four phases of database development?

All of the above are phases of database development

A support position that specializes in managing individuals databases and DBMS

Database administrator

The following pair of tables is union compatible:

Dept1, Dept4

In an ERD, generalization hierarchies always contain:

Disjointness and completeness constraints

A graphical representation that depicts things of interest (entities) and relationships among entities

Entity Relationship Model

Which of the following is a level of database description in the Three Schema Architecture

External

Because the user specifies what parts of the database to retrieve, the SELECT statement of SQL is a procedural way to access a database.

False

IBM's DB2 is one example of an embedded DBMS.

False

In a client-server environment, DBMS software typically executes only on the server.

False

In most organizations, the data administrator has narrower responsibilities than the database administrator and usually reports directly to them.

False

The Three Schema Architecture is one of several types of client-server architecture.

False

Three characteristics of all databases are that the data is persistent, the database is distributed, and the data is interrelated.

False ( Persistent, inter-related, shared)

Converting from an ERD to a table design

Forward Engineering

Which of the following is not true of a DBMS?

It has evolved over the years into a fairly simple set of tools that are relatively easy to master.

Which of the following statements is not true of information resource management?

It is very different and much more challenging than managing the other physical resources of an organization.

Which of the following is an advantage of view design and integration over unique conceptual design?

It works better for large projects

Which statement is not true of the Three Schema Architecture?

Its details have been widely adopted in third- and fourth-generation DBMSs.

Following a power failure, the database is restored with a wrong price for a data item. This break in data quality was caused by:

Lack of reliability

You would show actor starring IN movie as a:

M-N relationship

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

May Violate 2NF

A table in 1NF in which the unique candidate key consists of all the attributes of the table

May violate 4NF

A language, such as SQL that allows you to specify the parts of a database to access rather than to code a complex procedure.

Non-Procedural database language

In comparing ERD's to relational database diagrams, which of the following statements is true:

None of the above

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 scaled down version of a system, usually implemented quickly and used to clarify requirements

Prototype

Which of the following statements does not apply to relational databases?

Relational databases have a strong procedural orientation

An industry standard database language that includes statements for database definition, database manipulation, and database control

SQL

Given the evolution of DBMSs, business data is now:

Stored only if it is relevant to business decisions.

In the traditional Systems Development Life Cycle, databases are actually produced during the following phase:

Systems Implementation

Logical database design takes place during:

Systems design

You would show stuntman doing stunts for actor in movie as a:

Ternary relationship (Chen's notation) or associative entity type (Crow's Foot notation)

A null value indicates:

The absence of a value

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

The combination of Faculty_ID, Room_Number, Building_Number, and DateAssigned

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

The combination of Room_Number and Building_Number

A relational algebra operator applied to tables Ex and Dx produces the following result: That operator is:

The extended cross product operator (Empdeptno, Deptno)

A relational algebra operator applied to tables Dx and Ex produces the following result: That operator is:

The natural join operator (Empdepton 300,200)

Which one of the following indicates poor data quality?

The same supplier is shown with two different addresses in two parts of the database

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

In the evolution of database technology, second-generation products are considered to be the first true DBMSs because:

They could manage multiple entities and relationships.

Reliable and efficient processing of large volumes of repetitive work. DBMSs ensure that simultaneous users do not interfere with each other and that failures do not cause lost work.

Transaction processing

In many organizations, power users are not responsible for collecting database requirements and designing applications.

True

Most commercial DBMS systems support database definition, manipulation and tuning, as well as providing graphical application development tools and a procedural language interface.

True

Persistence of data in a database is dependent on the relevance of the business's intended usage.

True

With the growing importance of network computing and the internet, distributed processing has become a critical function of DBMSs.

True

Six common characteristics of data quality

COMPLETENESS. UNIQUENESS. TIMELINESS. VALIDITY. ACCURACY. CONSISTENCY.

An arrangement of components (clients and servers) and data among computers connected by a network. It supports efficient processing of messages between clients and servers.

Client-server architecture

Entity Relationship Diagrams are output from the following phase of Database Development:

Conceptual Data Modeling

Activity that produces a table design from an ERD:

Conversion

Which of the following is not a phase of database development?

DBMS Selection

Which of the following statements is not true of a desktop DBMS?

Although useful for processing ad hoc queries, they cannot perform transaction processing.

A table that satisfies 2NF

Always satisfies 1NF

A table that satisfies BCNF

Always satisfies 3NF

Oscar for best director should be:

An attribute of movie

Which of the following decisions would be part of Physical Database Design?

An index file will be created on the last name of students in the Student table

A relational algebra operator applied to tables Dx and Ex produces the following result:

An outer join operator (Deptno 100,200,300)

Regarding connection rules for ERD's, which statement is not true:

B and C are not true B) Relationships can be connected to other relationships C) All entity types participate in at least one relationship

In the evolution of database technology, third-generation products supplanted second-generation systems because:

Because non-procedural access provided such an improvement over navigational access, third-generation systems supplanted the second generation.