Worksheetschap4
Total questions: 101
Worksheet time: 51mins
A form of database design which maps conceptual requirements is called:
logical designs
response designs
security design
physical design
Data is represented in the form of:
data trees
tables
data notes
chairs
A two-dimensional table of data sometimes is called a:
group
set
declaration
relation
__________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.
Business rule constraint
Data integrity
Business integrity
Data structure
An attribute (or attributes) that uniquely identifies each row in a relation is called a:
column
foreign field
primary key
duplicate key
An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:
link attribute
link key
foreign key
foreign attribute
A primary key whose value is unique across all relations is called a(n):
global primary key
inter-table primary key
enterprise key
foreign global key
A primary key that consists of more than one attribute is called a:
foreign key
composite key
multivalued key
cardinal key
In the SQL language, the ________ statement is used to make table definitions.
create session
create table
create index
select
In the figure below, the primary key for "Order Line" is which type of key?
Composite
Foreign
Standard
Grouped
Which of the following is NOT a reason to create an instance of a relational schema with sample data?
Sample data can be used to improve user communications.
Sample data can be used for prototype generation.
Sample data can reverse database implementation errors.
Sample data provide a convenient way to check the accuracy of your design.
A domain definition consists of all of the following components EXCEPT:
domain name
data type
integrity constraints
size
Which of the following are properties of relations?
Each attribute has the same name.
No two rows in a relation are identical.
There are multivalued attributes in a relation.
All columns are numeric.
Which of the following violates the atomic property of relations?
Sam
Hinz
Sam Hinz
Atomic
The entity integrity rule states that:
no primary key attribute can be null.
referential integrity must be maintained across all entities.
each entity must have a primary key.
a primary key must have only one attribute.
The ______ states that no primary key attribute may be null.
referential integrity constraint
entity integrity rule
partial specialization rule
range domain rule
In the figure below, Customer_ID in the CUSTOMER Table is which type of key?
Composite
Candidate
Standard
Grouped
All of the following are the main goals of normalization EXCEPT:
minimize data redundancy.
simplify the enforcement of referential integrity.
maximize storage space.
make it easier to maintain data.
When all multivalued attributes have been removed from a relation, it is said to be in:
first normal form.
second normal form.
Boyce-Codd normal form.
third normal form.
The normal form which removes any remaining functional dependencies because there was more than one primary key for the same nonkeys is called:
fifth normal form.
fourth normal form.
Boyce-Codd normal form.
sixth normal form.
The normal form which deals with multivalued dependencies is called:
fifth normal form.
fourth normal form.
Boyce-Codd normal form.
sixth normal form.
A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form?
First
Second
Third
Fourth
A constraint between two attributes is called a(n):
functional relation.
attribute dependency.
functional dependency.
functional relation constraint.
A candidate key must satisfy all of the following conditions EXCEPT:
the key must uniquely identify the row.
the key must indicate the row's position in the table.
the key must be nonredundant.
each nonkey attribute is functionally dependent upon it.
The attribute on the left-hand side of the arrow in a functional dependency is the:
candidate key.
determinant.
foreign key.
primary key.
A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key is called a ________ dependency.
partial key-based
partial functional
cross key
merged relation
A functional dependency between two or more nonkey attributes is called a:
partial functional dependency.
partial nonkey dependency.
transitive dependency.
partial transitive dependency.
________ problems are encountered when removing data with transitive dependencies.
Insertion
Modification
Deletion
Merging
The need to ________ relations commonly occurs when different views need to be integrated.
metadata
system
drop
merge
Two or more attributes having different names but the same meaning are called:
homonyms.
aliases.
synonyms.
alternate attributes.
An attribute that may have more than one meaning is called a(n):
homonym.
alias.
double defined attribute.
synonym.
An alternative name for an attribute is called a(n):
synonym.
alias.
alternate attribute.
related characteristic.
Understanding the steps involved in transforming EER diagrams into relations is important because:
one must be able to check the output of a CASE tool.
there are rarely legitimate alternatives from which to choose.
CASE tools can model any situation.
CASE tools model hypothetical business problems.
A nonkey attribute is also called a(n):
column.
unimportant datum.
descriptor.
address.
When a regular entity type contains a multivalued attribute, one must:
create a single relation with multiple lines for each instance of the multivalued attribute.
create two new relations, one containing the multivalued attribute.
create two new relations, both containing the multivalued attribute.
delete the relation and start over.
In the figure below, what type of relationship do the relations depict? The figure shows an Employee relation with columns Employee_ID and Employee_Name, and a Child relation with columns Name, Employee_ID, Date of Birth, and Gender; an arrow indicates the identifying relationship between Child and Employee via Employee_ID.
Identifying entity/weak entity
Multivalued
Composite foreign key
One-to-many
In the figure below, what type of relationship do the relations depict?
Identifying entity/weak entity
One-to-many
Ternary
Many-to-many
In the figure below, what is depicted?
A one-to-one relationship
A unary relationship
A one-to-many relationship
An associative entity
In the figure below, what type of key is depicted?
Primary
Recursive primary
Composite
Recursive foreign
Referring to the figure below, which of the following is NOT true?
A component is part of an item.
A component is always used in only one item.
A component can be part of an item.
A component may be used in many items.
The figure below is an example of mapping which type of relationship?
First
Second
Unary
Ternary
A rule that states that each foreign key value must match a primary key value in the other relation is called the:
referential integrity constraint
key match rule
entity key group rule
foreign/primary match rule
______ anomalies can be caused by editing data in tables.
Insertion
Deletion
Modification
Creation
A relation that contains minimal redundancy and allows easy use is considered to be:
clean
simple
complex
well-structured
Data structures include data organized in the form of tables with rows and columns.
True
False
A composite key consists of only one attribute.
True
False
A primary key is an attribute that uniquely identifies each row in a relation.
True
False
A foreign key is a primary key of a relation that also is a primary key in another relation.
True
False
Sample data are useful for developing prototype applications and for testing queries.
True
False
An enterprise key is a foreign key whose value is unique across all relations.
True
False
One property of a relation is that each attribute within a relation has a unique name.
True
False
There can be multivalued attributes in a relation.
True
False
The columns of a relation can be interchanged without changing the meaning or use of the relation.
True
False
Unlike columns, the rows of a relation may not be interchanged and must be stored in one sequence.
True
False
All values that appear in a column of a relation must be taken from the same domain.
True
False
View integration is the process of merging relations together.
True
False
A synonym is an attribute that may have more than one meaning.
True
False
When two or more attributes describe the same characteristic of an entity, they are synonyms.
True
False
CASE tools can model more complex data relationships, such as ternary relationships.
True
False
When a regular entity type contains a multivalued attribute, two new relations are created.
True
False
When transforming a weak entity, one should create one relation with both the attributes of the strong entity and the attributes of the weak entity.
True
False
The primary key of the many side migrates to the one side when transforming a one-to-many relationship.
True
False
When transforming a one-to-one relationship, a new relation is always created.
True
False
If an identifier is not assigned, the default primary key for an associative relation consists of the two primary key attributes from the other two relations.
True
False
An identifier assigned to an associative entity is also called a cross-relation key.
True
False
In the figure below, each employee has exactly one manager.
True
False
When transforming a unary many-to-many relationship to relations, a recursive foreign key is used.
True
False
The relational data model does, at this time, directly support subtype/supertype relationships.
True
False
Data integrity consists of powerful operations to manipulate data stored in relations.
True
False
The allowable range of values for a given attribute is part of the domain constraint.
True
False
The entity integrity rule states that a primary key attribute can be null.
True
False
In the relational data model, associations between tables are defined through the use of primary keys.
True
False
A referential integrity constraint is a rule that maintains consistency among the rows of two relations.
True
False
A cascading delete removes all records in other tables associated with the record to be deleted.
True
False
The truncate table statement in SQL creates a new table.
True
False
A well-structured relation contains minimal redundancy and allows users to manipulate the relation without errors or inconsistencies.
True
False
An anomaly is a type of flaw in the database server.
True
False
When normalizing, the goal is to decompose relations with anomalies to produce smaller, well-structured relations.
True
False
A relation in fifth normal form may not contain any anomalies.
True
False
A co-dependency is a constraint between two attributes or two sets of attributes.
True
False
A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation.
True
False
A relation is in first normal form if it has no more than one multivalued attribute.
True
False
A partial functional dependency is a functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key.
True
False
A transversal dependency is a functional dependency between two or more nonkey attributes.
True
False
Anomalies do not generally arise out of transitive dependencies.
True
False
Which description accurately summarizes various relational keys?
A primary key uniquely identifies each row; it can be a single column or a composite of columns, and a foreign key in one table references a primary key in another table to represent relationships.
A primary key labels a table; composite keys are prohibited, and foreign keys reference nonkey attributes in the same table.
Primary keys may be null and need not be unique; composite keys are only allowed with text columns, and foreign keys create new tables when referenced.
A foreign key uniquely identifies each row; primary keys reference foreign keys in another table, and composite keys are only for temporary tables.
Which statement best captures the properties of relations in a relational database?
Each table (relation) has a unique name; each row is unique; each attribute within a table has a unique name; each cell contains an atomic value; the order of rows and columns is insignificant.
Tables may share names; rows can repeat; attributes need not be uniquely named; cells may store multiple values; the order of rows and columns determines meaning.
Each table must be ordered by primary key; rows may duplicate; attributes can be duplicated across a table; cells must store lists; column order conveys constraints.
Tables require unique column order; rows are unique only if indexed; attributes may share names; cells are unrestricted and can store sets.
Why is it important to remove multivalued attributes from a relation, and how is this typically resolved?
Because relations require atomic values, multivalued attributes violate the definition; they are resolved by creating a separate instance for each value and repeating the non-multivalued attributes.
Because multivalued attributes improve performance, they must be removed and replaced with a single summary value stored in the original table.
Because normalization requires all attributes to be numeric, multivalued text attributes are removed by converting them into codes within the same row.
Because foreign keys cannot reference multivalued attributes, they are removed by deleting the entire row and inserting a single value per attribute.
Which benefits result from creating an instance of your relational schema with sample data? Select all that apply.
Testing assumptions about the design
Conveniently checking the accuracy of the schema
Improving communication with users
Developing prototype applications and sample queries
Explain what a candidate key is and how it might be used.
An attribute or combination of attributes that uniquely identifies each row, is nonredundant, and can be used as a primary key or determinant
Any attribute that appears in more than one table and is used solely for joins
A numeric identifier assigned by the DBMS to speed up indexing but not to identify rows
A set of attributes used for sorting data but not for enforcing uniqueness
Discuss how you would map a regular entity to a relation.
Create one relation for the entity; choose a primary key; decompose composite attributes into their components; convert multivalued attributes into separate relations; ignore derived attributes
Create multiple relations for the same entity, one per simple attribute, and make all derived attributes the primary keys
Create one relation and store composite and multivalued attributes in repeating groups inside the same table
Create a relation with no primary key, keeping all attributes as they appear in the E-R diagram
How do you convert weak entities to relations?
Create a relation whose primary key includes the strong entity’s primary key together with the weak entity’s identifying attribute
Create a relation with its own independent surrogate primary key and no reference to the strong entity
Create a relation that uses only the weak entity’s partial key as the primary key
Do not create a separate relation; instead merge weak entities into the strong entity’s relation as optional columns
Discuss when it is best to create a surrogate key for an associative entity.
When a composite primary key would be large or inefficient, when a natural key is long or recycled, or when the associative entity relates to more than two relations
Only when the associative entity has no attributes of its own
Whenever there is any foreign key in the relation, regardless of size or reuse
When you want to eliminate all joins between related tables
Discuss the two possible scenarios when mapping an associative entity.
If the associative entity has its own identifier, use it as the primary key; otherwise, use a composite primary key formed from the primary keys of the related relations
Always assign a new surrogate key and ignore keys from related relations
Use the foreign key from just one related relation as the primary key in the associative relation
Do not create a relation; embed associative attributes into one of the participating entity relations
Discuss how to map a unary one-to-many relationship.
Map the entity type to a relation and add a recursive foreign key that references the relation’s own primary key
Create two separate relations, one for the entity and one for the relationship, both with independent primary keys
Use a multivalued attribute to represent the relationship within the same relation
Merge the relationship into a different entity’s relation using an external foreign key
Discuss how to map a unary many-to-many relationship.
Create one relation for the entity type and a separate associative relation with two attributes that both reference the primary key of the entity relation
Use a single relation with a repeating group to store multiple references to the same entity
Add a single foreign key column to the entity relation referencing itself
Create a surrogate key and eliminate the need for an associative relation
Explain how subtype/supertype relationships are converted to relations.
Create one relation for the supertype containing common attributes and a primary key; create a relation for each subtype that uses the supertype’s primary key and adds subtype-specific attributes; add discriminator attribute(s) to the supertype
Merge all subtypes into the supertype relation and remove subtype-specific attributes
Create separate relations for subtypes only, with no supertype relation or discriminator
Assign a surrogate key to each subtype and avoid using the supertype’s primary key
Discuss the types of integrity constraints.
Domain constraints, entity integrity, and referential integrity
Key constraints, trigger integrity, and transaction integrity
Domain constraints, tuple ordering, and view integrity
Referential integrity only
What is an anomaly, and what are the three types of anomalies?
An error or inconsistency during updates; insert anomaly
An error or inconsistency during updates; deletion anomaly
An error or inconsistency during updates; modification (update) anomaly
A performance slowdown during queries; indexing anomaly
Discuss the two major occasions when you benefit from using normalization.
During logical database design as a check and balance before physical design
When reverse-engineering older systems to reduce redundant tables and user views
When designing network topology to improve bandwidth
When creating presentation-layer views for reporting only
List and explain 1st, 2nd, 3rd, BCNF, 4th, and 5th normal forms.
1NF removes repeating groups; 2NF removes partial functional dependencies; 3NF removes transitive dependencies; BCNF removes remaining dependencies causing anomalies; 4NF removes multivalued dependencies; 5NF removes any further anomalies
1NF enforces referential integrity; 2NF defines surrogate keys; 3NF removes domain constraints; BCNF introduces repeating groups; 4NF adds multivalued attributes; 5NF eliminates composite keys
1NF creates composite keys; 2NF removes all foreign keys; 3NF partitions tables by subject; BCNF bans nulls; 4NF requires unique indexes; 5NF defines triggers
1NF removes transitive dependencies; 2NF removes multivalued dependencies; 3NF removes partial dependencies; BCNF removes domain constraints; 4NF removes composite keys; 5NF adds repeating groups
