MIS 372 Final Exam

A relational table must not contain a(n) ______ a. entity b. attribute c. relationship d. repeating group

d. repeating group

______ is used to reveal the meaning of data.

information

Entity Instance

person, place, object, event, concept (often corresponds to a row in a table)

Internal Schema

specification of database objects and structures to support conceptual schema.

Relationship degrees

specifiy number of entity types involved in a relationship.

What is a subtype?

subgroups of the supertype entity and have unique attributes, but they will be different from each subtype

The _____ serve(s) as the intermediary between the user and the database.

(DBMS) Database Management System

Reasons for studying file systems?

- Complexity of database design is easier to understand - Understanding file system problems helps to avoid problems with DBMS systems - Knowledge of file system is useful for converting file system to database system

Surrogate Key

A column created by data modeler/designer for the sole purpose of serving as a primary key.

What is a supertype?

A generic entity type that has a relationship (parent to child relationship) with one or more subtypes

____ keys work with primary keys to properly implement relationships in the relational model. a. Foreign b. Composite c. Natural d. Surrogate

A. Foreign

One important inheritance characteristic is that all entity subtypes inherit their ____ key attribute from their supertype. a. primary b. natural c. foreign d. surrogate

A. primary

Foreign Key

An attribute or attributes in one relation that serves as a primary key in another relation

Foreign Key

An attribute or attributes in one relation that serves as a primary key in another relation.

Composite Attribute

An attribute that can be further subdivided to yield additional attributes.

Composite attribute

An attribute that can be further subdivided to yield additional attributes.

Simple attribute

An attribute that consists of a single atomic value EX: salary, age, etc

Single-Valued attribute

An attribute, such as social security number, that can have only one value.

What is a composite entity? When is it useful? Give an example

An entity that serves as a bridge between two entities that have a many-to-many relationship. For example Order_Item is the composite entity that resolves the M:M relationship between Order and Item.

Disjoint Rule

An instance of the supertype can be only ONE of the subtypes

Candidate Key

Any attribute (or collection of attributes) whose value will be different for every valid tuple (row) within a relation (table).

Candidate Key

Any identifier that uniquely distinguishes all instances of an entity type.

In what two cases are composite primary keys particularly useful?

As identifiers of composite entities, in which each primary key combination is allowed only once in the M:N relationship. As identifiers of weak entities, in which the weak entity has a strong identifying relationship with the parent entity

At the implementation level, the supertype and its subtype(s) depicted in the specialization hierarchy maintain a(n) ____ relationship. a. self-referencing c. 1:M b. 1:1 d. M:N

B. 1:1

___ is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes. a. Specialization b. Generalization c. Partial completeness d. Total completeness

B. Generalization

The property of ____ enables an entity subtype to inherit the attributes and relationships of the supertype. a. subtype discriminator b. inheritance c. specialization hierarchy d. entity supertype

B. inheritance

A ____ key is a real-world, generally accepted identifier used to uniquely identify real-world objects. a. primary b. natural c. foreign d. surrogate

B. natural

Surrogate primary keys are especially helpful when there is no ____ key. a. primary b. natural c. foreign d. composite

B. natural

A table that is in 2NF and contains no transitive dependencies is said to be in ____. a. 1NF b. 2NF c. 3NF d. 4NF

C. 3nf

Which of the following is false about a relational DBMS? a. It does not rely on user application programs to enforce consistency among the data elements. b. It helps reduce data redundancy. c. It employs memory locations to reference contents of a relation (table). d. It is self-describing

C. It employs memory locations to reference contents of a relation (table).

The preferred placement for a foreign key when working with a 1:1 relationship is to ____. a. use the same primary key for both entities b. create a bridge entity c. place a foreign key in one of the entities d. place a foreign key in both entities

C. place a foreign key in one of the entities

________ __________ are used to resolve M:N relationships in relational databases.

Composite Entities

What does an Entity Subtype contain?

Contains unique characteristics of each entity subtype

______ is a generic entity type that is related to one or more entity subtypes. a. A subtype discriminator b. Inheritance c. A specialization hierarchy d. An entity supertype

D. An entity supertype

A(n) _______ is a collection of programs that manages the database structure and controls access to the data stored in the database.

DBMS

__________ _________ exists when it is possible to make changes in the data storage characteristic's without affecting the application programs ability to access the data.

Data Independence

_____ exists when different versions of the same data appear in different places.

Data inconsistency

________ ________ exists when different and conflicting versions of the same data appear in different places

Data inconsistency

________ exists when the same data are stored unnecessarily at different places.

Data redundancy

The set of possible values for an attribute is referred to as its ________.

Domain

(True or False) A manager and a programmer usually have the same view of the same data.

False

(True or False) All SQL commands must be issued on a single line.

False

Data and information are essentially the same thing

False

Dependencies that are based on only a part of a composite primary key are called transitive dependencies. (T/F)

False

Entity subtypes do not inherit the relationships in which the supertype entity participates. (T/F)

False

Generalization is based on grouping unique characteristics and relationships of the subtypes. (T/F)

False

Ideally, a primary key is composed of several attributes

False

Implementing non-overlapping subtypes requires the use of one discriminator attribute for each subtype. (T/F)

False

One advantage of a database system over previous data management approaches is that the database system is considerably less complex. (True or False)

False

Structural dependence exists when it is possible to make changes in the file structure without affecting the application program's ability to access the data. (True or False)

False

In the relational model, the term "relation" is synonymous with a ______________ in the data processing world

File

In logical DB design, an important association between attributes in an entity is called a ____________ dependency.

Functional

The process of identifying a more generic entity supertype from a lower-level entity subtypes is called what?

Generalization

Composite identifiers/keys

Identifiers that consist of two or more attributes.

External Schema

Individual user view of the data (base). Multiple

________ is the result of processing raw data to reveal its meaning.

Information

In a(n) _________ join, rows from table1 and table2 are included in the result only where the join column values match in both tables.

Inner

______ provide a description of the data characteristics and the set of relationships that link the data found within the database.

Metadata

________ is data about data through which the end-user data are integrated and managed.

Metadata

If X functionally determines Y, then for each value of X, how many values of Y are permitted?

One

Primary Key

One of the candidate keys of an entity, designated by database designer

When implemented as tables, supertype and subtype entities maintain a ______-to-______ relationship.

One-to-One

Conceptual Schema

Org. view of all data. Integration of all external views.

The LEFT JOIN can also be specified as a LEFT _______ JOIN.

Outer

______ relates to the activities that make the database perform more efficiently in terms of storage and access speed.

Performance tuning

The __________________ Operator extracts tuples from a relation that satisfy some criterion.

RESTRICT

Describe Specialization and Generalization

Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Specialization is based on grouping the unique characteristics and relationships of the subtypes. In the aviation example, you used specialization to identify multiple entity subtypes from the original employee supertype. Generalization is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes. Generalization is based on grouping the common characteristics and relationships of the subtypes. For example, you might identify multiple types of musical instruments: piano, violin, and guitar. Using the generalization approach, you could identify a "string instrument" entity supertype to hold the common characteristics of the multiple subtypes

Relationship Cardinalities

Specify how many of each entity type is allowed in a relationship.

Domain

The range of acceptable values for an attribute

A relation in _____________ normal form includes "and transitive dependencies removed" as part of its definition.

Third

(True or False) SQL allows the use of logical restrictions in its WHERE clause such as OR, AND, and NOT.

True

(True or False) The terms data model and database model are often used interchangeably.

True

(True or False) When referential integrity is enforced, a foreign key attribute may have a value of NULL.

True

All relational tables satisfy the 1NF requirements (T/F)

True

An entity supertype can have disjoint or overlapping entity subtypes. (T/F)

True

An identifying relationship between two entities exists when the primary key of one entity appears as part of another's entity's primary key. (T/F)

True

At the implementation level, the supertype and its subtype(s) depicted in the specialization hierarchy maintain a 1:1 relationship. (T/F)

True

Data constitute the building blocks of information. (True or False)

True

Normalization works through a series of stages called normal forms. (T/F)

True

One disadvantage of a database system over previous data management approaches is increased costs. (True or False)

True

One important inheritance characteristic is that all entity subtypes inherit their primary key attribute from their supertype. (T/F)

True

Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. (T/F)

True

The entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype. (T/F)

True

The order of rows and columns in a relation is not important in a relational database

True

The relationships depicted within the specialization hierarchy are sometimes described in terms of "is-a"relationships. (T/F)

True

The ___________ query combines the results of two queries without eliminating duplicate rows.

UNION ALL

A two dimensional table that has repeating groups and no primary key identified is said to be in __________ form.

Un-normalized

Transitive Dependency

When there are functional dependencies such that Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key. If x = 0, y= 0

_________ exists when different versions of the same data appear in different places a. Data redundancy b. Data locking c. Data inconsistency d. Data dependence

a. Data redundancy

_____ keys work with primary keys to properly implement relationships in the relational model. a. Foreign b. Composite c. Natural d. Surrogate

a. foreign

Identifiers

attributes that name, or identify, entity instances.

Which of the following is not true about business rules? a. They allow the designer understand the nature, role, and scope of data b. *They allow the designer to set company policies with regard to data c. They are a communications tool between users and designers d. They allow the designer to understand business processes. e. They allow the designer to develop appropriate relationship participation rules and constraints

b. They allow the designer to set company policies with regard to data

A table that displays data redundancies yields _____. a. consistencies b. anomalies c. fewer attributes d. more entity

b. anomalies

What definition best describes the external schema of the ANSI/SPARC framework? a. Specification of database objects and structures to support conceptual schema. b. Specification of storage structures and access methods. c. Individual user view(s) of the data. d. Global view of the data, as viewed by the entire organization

c. Individual user view(s) of the data.

A verb associating two nouns in a business rule translates to a(n) ________ in the data model a. entity b. attribute c. relationship d. constraint

c. relationship

Entity Type

collection of entities (often corresponds to a table)

_____ is a generic entity type that is related to one or more entity subtypes. a. A subtype discriminator b. Inheritance c. A specialization hierarchy d. An entity supertype

d. An entity supertype