CIS 350 MIDTERM REVIEW Part 4: Logical Database Design and the Relational Model​

What are the three anomalies should be avoided?

1. Insertion Anomaly 2. Deletion Anomaly 3. Modification Anomaly

There are six requirements for a table to qualify as a relation. Name three.

1. It must have a unique name.​ 2. Every attribute value must be atomic (not multivalued, not composite).​ 3. Every row must be unique (can't have two rows with exactly the same values for all their fields).​ 4. Attributes (columns) in tables must have unique names.​ 5. The order of the columns must be irrelevant.​ 6. The order of the rows must be irrelevant.​

What are four issues to watch out for when merging entities from different E-R models?

1. Synonyms - two or more attributes with different names but same meaning​ 2. Homonyms - attributes with same name but different meanings​ 3. Transitive dependencies - even if relations are in 3N F prior to merging, they may not be after merging​ 4. Supertype/subtype relationships - may be hidden prior to merging​

A transitive dependency is which of the following? a. A functional dependency between two or more key attributes. b. A functional dependency between two or more non-key attributes. c. A relation that is in first normal form. d. A relation that is in second normal form.

A functional dependency between two or more non-key attributes.

Data is represented in the form of: A) data trees. B) tables. C) data notes. D) chairs.

A) data trees.

Which of the following are properties of relations? A) Each attribute has the same name. B) No two rows in a relation are identical. C) There are multivalued attributes in a relation. D) All columns are numeric.

B) No two rows in a relation are identical.

A primary key that consists of more than one attribute is called a: A) foreign key. B) composite key. C) multivalued key. D) cardinal key.

B) composite key.

The ________ states that no primary key attribute may be null. A) referential integrity constraint B) entity integrity rule C) partial specialization rule D) range domain rule

B) entity integrity rule

Which of the following violates the atomic property of relations? A) Sam B) Hinz C) Sam Hinz D) Atomic

C) Sam Hinz

Which of the following is NOT a reason to create an instance of a relational schema with sample data? A) Sample data can be used to improve user communications. B) Sample data can be used for prototype generation. C) Sample data can reverse database implementation errors. D) Sample data provide a convenient way to check the accuracy of your design.

C) Sample data can reverse database implementation errors.

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: A) link attribute. B) link key. C) foreign key. D) foreign attribute.

C) foreign key.

All of the following are the main goals of normalization EXCEPT: A) minimize data redundancy. B) simplify the enforcement of referential integrity. C) maximize storage space. D) make it easier to maintain data.

C) maximize storage space.

An attribute (or attributes) that uniquely identifies each row in a relation is called a: A) column. B) foreign field. C) primary key. D) duplicate key.

C) primary key.

A two-dimensional table of data sometimes is called a: A) group. B) set. C) declaration. D) relation.

D) relation.

True or False: Rows of a relation must not be interchanged and must be stored in a certain sequence.

False

What is second normal form?

First normal form and every non-key attribute is fully functionally dependent on the ENTIRE primary key​

What is first normal form?

No multivalued attributes​ Every attribute value is atomic​

What is a primary key composed of?

Partial identifier of weak entity​ Primary key of identifying relation (strong entity)​

What is third normal form?

Second normal form PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)​

True or False: All relations are in first normal form

True

True or False: For a table to qualify as a relation: Every row must be unique, i.e. two rows cannot have the same values in all their fields.

True

True or False: Functional Dependency is when the value of one attribute (the determinant) determines the value of another attribute.​

True

A non-prime key is dependent on part of the prime key. This is called: a) partial functional dependency b) transitive dependency c) a repeating group d) full functional dependency e) composite key

a) partial functional dependency

Choose three options. The objective of Normalization is to a) simplify the enforcement of referential integrity constraints b) simplify printing reports c) simplify queries and display data d) ensure that data is easily maintained e) derive relations that are free of anomalies f) ensure data storage efficiency on magnetic media

a) simplify the enforcement of referential integrity constraints d) ensure that data is easily maintained e) derive relations that are free of anomalies

If no multivalued attributes exist in a relation, then the relation is in what normal form? a. First normal form b. Second normal form c. Third normal form d. Fourth normal form

a. First normal form

When mapping a supertype/subtype relationship which of the following is true? a. The supertype primary key is assigned to each subtype. b. The subtype primary key is assigned to each supertype. c. There is no link between the supertype/subtype entities. d. There is no primary key/foreign key relationship between a supertype/subtype.

a. The supertype primary key is assigned to each subtype.

A primary key a) can be NULL b) cannot be NULL c) can be NULL if the database is in First Normal Form d) can be NULL if the database is in Second Normal Form e) can be NULL if the database is in Third Normal Form

b) cannot be NULL

If no multivalued attributes exist and no partial dependencies exist in a relation, then the relation is in what normal form? a. First normal form b. Second normal form c. Third normal form d. Fourth normal form

b. Second normal form

When mapping a many-to-many unary relationship into a relation which of the following is true? a. One relation is created. b. Two relations are created. c. Three relations are created. d. Four relations are created.

b. Two relations are created.

When mapping a multivalued attribute into a relation which of the following is true? a. One relation is created. b. Two relations are created. c. Three relations are created. d. Four relations are created.

b. Two relations are created.

When mapping a regular entity into a relation which of the following is true? a. One relation is created. b. Two relations are created. c. Three relations are created. d. Four relations are created.

b. Two relations are created.

The process of decomposing relations, that have anomalies, to smaller, well-structured relations is called a) Drawing logical ER diagrams b) Denormalization c) Normalization d) Mapping entities to relations

c) Normalization

Identify the rule that maintains consistency among the rows of two relations a) Integrity Rule b) Entity Integrity Rule c) Referential Integrity Rule d) Domain Integrity Rule

c) Referential Integrity Rule

A composite key is a) an attribute that stores more than one data item b) an attribute that has different names but the same meaning c) a primary key that consists of more than one attribute d) an alternative name used for an attribute

c) a primary key that consists of more than one attribute

Identify the term used for a key that is unique in an organization's database, and its value is unique across all relations: a) primary key b) composite key c) enterprise key d) surrogate key e) foreign key

c) enterprise key

Choose two options. A database is in Second Normal Form if a) it has no transitive dependencies b) repeating groups have been resolved c) every non-key attribute is fully functionally dependent on the primary key d) primary key has been defined e) every non-key attribute is fully functionally dependent on other non-key attributes f) it is in first normal form

c) every non-key attribute is fully functionally dependent on the primary key f) it is in first normal form

Choose two options. A database is in First Normal Form if a) every non-key attribute is fully functionally dependent on other non-key attributes b) primary key has been defined c) repeating groups have been resolved d) every non-key attribute is fully functionally dependent on the primary key e) a table has no transitive dependencies f) every non-key attribute is fully functionally dependent on the primary key

c) repeating groups have been resolved d) every non-key attribute is fully functionally dependent on the primary key

A non-key is dependent on another non-key. This is called a) partial functional dependency b) full functional dependency c) transitive dependency

c) transitive dependency

When mapping subtype and supertype relationships, how many relations are there for each entity subtype and supertype? a. 2 and 2 b. 1 and 2 c. 1 and 1 d. 2 and 1

c. 1 and 1

When mapping a binary many-to-many relationship into a relation which of the following is true? a. One relation is created. b. Two relations are created. c. Three relations are created. d. Four relations are created.

c. Three relations are created.

Rows in a table a) Must be inserted in alphabetical order b) Must be inserted in reverse alphabetical c) Must be inserted in a way to ensure efficient retrieval d) Can be inserted in any order

d) Can be inserted in any order

Choose three options. The entity integrity rule states that: a) primary key can be null b) a primary key must have only one attribute c) a primary key must have more than one attribute d) each entity must have a primary key e) each primary key must be unique f) a primary key is not NULL

d) each entity must have a primary key e) each primary key must be unique f) a primary key is not NULL

Order of columns in a table is a) important, the prime key must be displayed and inserted first b) important, the prime key must be displayed and inserted last c) important, prime key must be displayed first, all other columns must be in alphabetical order d) not relevant, columns can be in any order

d) not relevant, columns can be in any order

When mapping ternary relationship, how many relations are there for each entity and how many are there for the associative entity? a. 3 and 3 b. 1 and 3 c. 3 and 1 d. 1 and 1

d. 1 and 1

When mapping a ternary relationship with an associative entity into a relation which of the following is true? a. One relation is created. b. Two relations are created. c. Three relations are created. d. Four relations are created.

d. Four relations are created.

Choose two options. A database is in Third Normal Form if a) every non-key attribute is fully functionally dependent on the primary key b) repeating groups have been resolved c) primary key has been defined d) every non-key attribute is fully functionally dependent on other non-key attributes e) it has no transitive dependencies f) it is in second normal form

e) it has no transitive dependencies f) it is in second normal form