Modern Database Management - Final Study
extra space and update time.
A disadvantage of partitioning is:
An attribute is also known as a ________. A) table B) relation C) row D) field
D
Answer: TRUE
Denormalization almost always leads to more storage space for raw data.
horizontal partitioning.
Distributing the rows of data into separate files is called:
A file organization is a named portion of primary memory
F
It is possible to have a relation that does not have a key.
False
Hashed
In which type of file is multiple key retrieval not possible?
Answer: TRUE
One decision in the physical design process is selecting structures.
Relations are classified into normal forms based on the types of modification anomalies that they are vulnerable to.
True
A strategy for breaking apart queries and running each part on separate processors is called
parallel query processing
Which type of file is easiest to update?
*A. Hashed* B. Sequential C. Clustered D. Indexed
Sensitivity testing involves:
*A. checking to see if missing data will greatly impact results.* B. checking to see if your teeth hurt when you brush. C. deleting the associated row. D. seeing how accurate data are.
A form of denormalization where the same data are purposely stored in multiple places in the database is called:
*A. data replication.* B. advanced placement. C. data duplication. D. horizontal partitioning.
In most cases, the goal of ________ dominates the design process.
*A. efficient data processing* B. quick pointer updates C. security D. shorter design times
A requirement to begin designing physical files and databases is:
*A. normalized relations.* B. physical tables created. C. implementation complete. D. all datatypes determined.
A relation is also known as a(n) ________. A) table B) tuple C) relationship D) attribute
A
A relation is in second normal form (2NF) if and only if it is in 1NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant
A
A table that meets the definition of a relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form
A
Given the functional dependency (A, B) → C, then ________. A) A and B together → C B) A → C C) B → A D) Both A and B are correct
A
Having to enter facts about two entities when we want to enter facts about only one is an example of ________. A) insertion anomaly B) update anomaly C) deletion anomaly D) normalization anomaly
A
In general, each relation should have ________. A) one and only one theme B) one or more themes C) exactly two themes D) one or two themes
A
The only reason(s) for having relations is to ________. A) store instances of functional dependencies B) store equation components C) store equation results D) Both B and C are correct
A
the key must indicate the row's position in the table
A candidate key must satisfy all of the following conditions EXCEPT: A) the key must uniquely identify the row. B) the key must indicate the row's position in the table. C) the key must be nonredundant. D) each nonkey attribute is functionally dependent upon it.
explain plan
A command used in Oracle to display how the query optimizer intends to access indexes, use parallel servers and join tables to prepare query results is the: A) explain plan. B) show optimization. C) explain query. D) analyze query
explain plan.
A command used in Oracle to display how the query optimizer intends to access indexes, use parallel servers and join tables to prepare query results is the:
functional dependency
A constraint between two attributes is called a(n): A) functional relation. B) attribute dependency. C) functional dependency. D) functional relation constraint
extent.
A contiguous section of disk storage space is called a(n):
extent.
A contiguous section of disk storage space is called a(n): A) track. B) sector. C) extent. D) tablespace.
Answer: FALSE
A default value is the value that a field will always assume, regardless of what the user enters for an instance of that field.
data type.
A detailed coding scheme recognized by system software for representing organizational data is called a(n): A) DBMS code. B) data type. C) SQL. D) DB layout.
data type.
A detailed coding scheme recognized by system software for representing organizational data is called a(n):
extra space and update time
A disadvantage of partitioning is: A) simplicity. B) remote optimization. C) extra space and update time. D) shorter technology spans.
integrity constraints.
A domain definition consists of all of the following components EXCEPT: A) domain name. B) data type. C) integrity constraints. D) size.
all of the above.
A factor to consider when choosing a file organization is:
efficient storage.
A factor to consider when choosing a file organization is: A) inefficient data retrieval. B) DDL. C) efficient storage. D) DML.
Answer: FALSE
A file organization is a named portion of primary memory.
hash index table.
A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a: A) hashed file organization. B) hash key. C) multi-indexed file organization. D) hash index table
hash index table.
A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a:
heap file organization.
A file organization where files are not stored in any particular order is considered a: A) hashed file organization. B) hash key. C) multi-indexed file organization. D) heap file organization
security specifications
A form of database specification which maps conceptual requirements is called: A) logical specifications. B) response specifications. C) security specifications. D) physical specifications
data replication.
A form of denormalization where the same data are purposely stored in multiple places in the database is called: A) data duplication. B) data replication. C) advanced placement. D) horizontal partitioning
transitive dependency.
A functional dependency between two or more nonkey attributes is called a: A) partial functional dependency. B) partial nonkey dependency. C) transitive dependency. D) partial transitive dependency.
partial functional
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. A) partial key-based B) partial functional C) cross key D) merged relation
Answer: TRUE
A hashing algorithm is a routine that converts a primary key value into a relative record number.
Answer: FALSE
A join index is a combination of two or more indexes.
selecting structures.
A key decision in the physical design process is:
selecting structures
A key decision in the physical design process is: A) ignoring the size of the database. B) selecting structures. C) deciding on the monitor. D) deciding the e-r diagrams
Answer: FALSE
A key is a data structure used to determine the location of rows in a file that satisfy some condition.
all of the above.
A method for handling missing data is to:
track missing data with special reports
A method for handling missing data is to: A) substitute with random numbers for the missing data. B) track missing data with special reports. C) perform insensitivity testing. D) delete the associated row
parallel query processing.
A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called: A) multiple partition queries. B) perpendicular query processing. C) parallel query processing. D) query optimization.
parallel query processing.
A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called:
clustering
A method to allow adjacent secondary memory space to contain rows from several tables is called: A) cluttering. B) clustering. C) concatenating. D) compiling.
clustering.
A method to allow adjacent secondary memory space to contain rows from several tables is called:
descriptor
A nonkey attribute is also called a(n): A) column. B) unimportant datum. C) descriptor. D) address.
Answer: TRUE
A pointer is a field of data that can be used to locate a related field or record of data.
Composite Key
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.
Enterprise Key
A primary key whose value is unique across all relations is called a(n): A) global primary key. B) inter-table primary key. C) enterprise key. D) foreign global key
Answer: TRUE
A range control limits the set of permissible values that a field may assume.
well-structured
A relation that contains minimal redundancy and allows easy use is considered to be: A) clean. B) simple. C) complex. D) well-structured
Second
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? A) First B) Second C) Third D) Fourth
all of the above.
A requirement to begin designing physical files and databases is:
normalized relations
A requirement to begin designing physical files and databases is: A) normalized relations. B) physical tables created. C) implementation complete. D) all datatypes determined.
all of the above.
A rule of thumb for choosing indexes is to:
referential integrity constraint.
A rule that states that each foreign key value must match a primary key value in the other relation is called the: A) referential integrity constraint. B) key match rule. C) entity key group rule. D) foreign/primary match rule.
Explain the concept of a surrogate key.
A surrogate key is an artificial key that is created to act as the primary key for a relation. The surrogate key is a unique, numeric value that is appended to the relation. Surrogate keys are used in situations when no suitable primary key exists within the user data, or when all available primary keys within the data are too cumbersome for an efficient design. Surrogate key values have no meaning to the users and are normally hidden on all forms, reports, and displays. Most DBMS products have the ability to automatically generate values for surrogate keys as needed.
Answer: TRUE
A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored.
Relation
A two-dimensional table of data sometimes is called a: A) group. B) set. C) declaration. D) relation.
pointer
A(n) ________ is a field of data used to locate a related field or record.
Pointer
A(n) ________ is a field of data used to locate a related field or record. A) key B) index C) lock D) pointer
hashing algorithm
A(n) ________ is a routine that converts a primary key value into a relative record number.
hashing algorithm
A(n) ________ is a routine that converts a primary key value into a relative record number. A) record index calculator B) index pointer program C) hashing algorithm D) pointing algorithm
file organization
A(n) ________ is a technique for physically arranging the records of a file on secondary storage devices. A) physical pointer B) retrieval program C) file organization D) update program
file organization
A(n) ________ is a technique for physically arranging the records of a file on secondary storage devices.
Which type of file is most efficient with storage space?
A. Clustered B. Indexed C. Hashed *D. Sequential*
In which data model would a code table appear?
A. Data layout B. Logical *C. Conceptual* D. Physical
The smallest unit of application data recognized by system software is a:
A. column. *B. field.* C. row. D. data type.
A method to allow adjacent secondary memory space to contain rows from several tables is called:
A. concatenating. *B. clustering.* C. cluttering. D. compiling.
A method for handling missing data is to:
A. delete the associated row. B. perform insensitivity testing. C. substitute with random numbers for the missing data. *D. track missing data with special reports.*
A key decision in the physical design process is:
A. ignoring the size of the database. B. deciding the e-r diagrams. C. deciding on the monitor. *D. selecting structures.*
A(n) ________ is a routine that converts a primary key value into a relative record number.
A. index pointer program *B. hashing algorithm* C. record index calculator D. pointing algorithm
Designing physical files requires ________ of where and when data are used in various ways.
A. keys B. hints *C. descriptions* D. maps
Distributing the rows of data into separate files is called:
A. normalization. B. file allocation. C. vertical partitioning. *D. horizontal partitioning.*
The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.
A. query design B. programmer productivity *C. data integrity* D. data integration
Database access frequencies are estimated from:
A. random number generation. B. security violations. C. user logins. *D. transaction volumes.*
The value a field will assume unless the user enters an explicit value for an instance of that field is called a:
A. range control. B. null value. *C. default value.* D. gurand.
All of the following are objectives when selecting a data type *EXCEPT*:
A. represent all possible values. B. support all data manipulations. C. improve data integrity. *D. use a lot of storage space.*
A contiguous section of disk storage space is called a(n):
A. sector. B. tablespace. *C. extent.* D. track.
Horizontal partitioning makes sense:
A. when only one category is allowed. B. when partitions must be organized the same. C. when less security is needed. *D. when different categories of a table's rows are processed separately.*
Answer: FALSE
Adding notations to the EER diagram regarding data volumes and usage is of no value to the physical design process.
a one-to-many relationship.
All of the following are common denormalization opportunities EXCEPT:
a one-to-many relationship
All of the following are common denormalization opportunities EXCEPT: A) two entities with a one-to-one relationship. B) a one-to-many relationship. C) a many-to-many relationship with nonkey attributes. D) reference data.
multivalued partitioning.
All of the following are horizontal partitioning methods in Oracle EXCEPT:
multivalued partitioning
All of the following are horizontal partitioning methods in Oracle EXCEPT: A) key range partitioning. B) hash partitioning. C) multivalued partitioning. D) composite partitioning
use a lot of storage space.
All of the following are objectives when selecting a data type EXCEPT:
use a lot of storage space
All of the following are objectives when selecting a data type EXCEPT: A) represent all possible values. B) improve data integrity. C) support all data manipulations. D) use a lot of storage space
Maximize Storage Pace
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
boolean.
All of the following are valid datatypes in Oracle 11g EXCEPT:
boolean.
All of the following are valid datatypes in Oracle 11g EXCEPT: A) varchar2. B) boolean. C) blob. D) number
Answer: TRUE
Along with table scans, other elements of a query can be processed in parallel.
efficiency.
An advantage of partitioning is:
efficiency.
An advantage of partitioning is: A) efficiency. B) remote optimization. C) extra space and update time. D) increase redundancy.
alias.
An alternative name for an attribute is called a(n): A) synonym. B) alias. C) alternate attribute. D) related characteristic
Blob
An appropriate datatype for adding a sound clip would be: A) VarChar. B) Char. C) Blob. D) Date.
Char
An appropriate datatype for one wanting a fixed-length type for last name would include: A) VarChar. B) Char. C) Blob. D) Date.
primary key
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.
Foreign 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: A) link attribute. B) link key. C) foreign key. D) foreign attribute.
homonym
An attribute that may have more than one meaning is called a(n): A) homonym. B) alias. C) double defined attribute. D) synonym.
Answer: FALSE
An extent is a named portion of secondary memory allocated for the purpose of storing physical records.
join index.
An index on columns from two or more tables that come from the same domain of values is called a: A) bitmap index. B) multivalued index. C) join index. D) transaction index
join index.
An index on columns from two or more tables that come from the same domain of values is called a:
range control.
An integrity control supported by a DBMS is:
range control
An integrity control supported by a DBMS is: A) substitute estimates. B) security. C) range control. D) GUI guards.
data replication.
Another form of denormalization where the same data are stored in multiple places in the database is called:
A combination of two or more columns used to identify particular rows in a relation is a ________. A) record B) composite key C) foreign key D) surrogate key
B
A relation is in fourth normal form if it is in BCNF and it has no ________. A) transitive dependencies B) multivalued dependencies C) partial dependencies D) deletion dependencies
B
A relation is in third normal form (3NF) if and only if it is in 2NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every candidate key is a determinant
B
Saying that two entities are functionally dependent means that ________. A) the entities are always connected by a mathematical equation B) for one of the entities, if we are given the value of that entity, we can determine the value of one other entity C) for both of the entities, if we are given the value of one entity, we can determine the value of the other entity D) the functional dependency will have to be removed through normalization.
B
When designing a database, one of the candidate keys in a relation is selected as the ________. A) composite key B) primary key C) foreign key D) surrogate key
B
Distinguish between the primary key and a candidate key.
Both the primary key and a candidate key can uniquely identify the rows in a table. The primary key is the candidate key that is chosen by the database designer, working with the users, to uniquely identify rows and to represent relationships. Although any candidate key could, by definition, be selected to act as the primary key, the choice of primary key is often based on design decisions such as the amount of foreign key data that would be generated.
A combination of one or more columns used to identify particular rows in a relation is a ________. A) record B) field C) key D) tuple
C
A key consisting of one or more columns that is a primary key in another relation is a ________. A) composite key B) candidate key C) foreign key D) surrogate key
C
A tuple is also known as a ________. A) table B) relation C) row D) field
C
Given the functional dependency A → (B, C), A is a(n) ________. A) independent variable B) dependent variable C) determinant D) composite determinant
C
If a relation is in BCNF, and each multivalued dependency has been moved to a relation of its own, then the first relation is in ________. A) Third Normal Form B) Boyce-Codd Normal Form C) Fourth Normal Form D) Domain/key normal form
C
Referential integrity constraints are used to limit the possible values of a ________. A) composite key B) candidate key C) foreign key D) surrogate key
C
Suppose that you need to update one value of the column SalesCost in a relation. The way the relation is constructed, this value actually needs to be changed in three different rows. However, you only change the value in two of the rows. You have just created a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly
C
Which of the following is NOT true about the functional dependency A → (X, Y)? A) X is functionally dependent on A. B) A determines Y. C) X and Y together determine A. D) X and Y are functionally dependent on A.
C
Which of the following is true about the functional dependency (A, B) → (C, D)? A) A is the determinant of C. B) A and B together are determined by C and D together. C) A and B together determine D. D) C and D together determine A.
C
Which of the following is true for a relation? A) Entities in a column vary as to kind. B) The order of the columns is important. C) The order of the rows is unimportant. D) More than one column can use the same name.
C
Sensitivity testing involves:
Checking to see if missing data will greatly impact results
Answer: TRUE
Clustering allows for adjacent secondary memory locations to contain rows from several tables.
A primary key that consists of more than one attribute is called a:
Composite key
In the SQL language, the __________ statement is used to make table definitions.
Create table
A ________ is used to limit the possible values of a foreign key. A) composite key B) surrogate key C) functional dependency D) referential integrity constraint
D
A determinant that determines all the other columns in a relation is a ________. A) record B) surrogate key C) foreign key D) candidate key
D
A relation is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and ________. A) all non-key attributes are determined by the entire primary key B) there are no non-key attributes determined by another non-key attribute C) every attribute is a candidate key D) every determinant is a candidate key
D
A relation is in domain/key normal form if ________. A) every key of the relation is a logical consequence of the definition of constraints and determinants B) every key of the relation is a logical consequence of the definition of constraints and domains C) every constraint on the relation is a logical consequence of the definition of keys and determinants D) every constraint on the relation is a logical consequence of the definition of keys and domains
D
An artificial column added to a relation to serve as the primary key is a ________. A) composite key B) candidate key C) foreign key D) surrogate key
D
Given the functional dependency (A, B) → C, (A, B) is a ________. A) dependent variable B) determinant C) composite determinant D) Both B and C are correct
D
If a table is designed so that every determinant is a candidate key, then that relation is in ________. A) First Normal Form B) Second Normal Form C) Third Normal Form D) Boyce-Codd Normal Form
D
If the removal of facts about one entity results in the unintentional loss of data about another entity, this is referred to as a(n) ________. A) normalization anomaly B) insertion anomaly C) update anomaly D) deletion anomaly
D
Which of the following is not true about a relation? A) Has rows containing data about an entity B) Has columns containing data about attributes of the entity C) Has cells that hold only a single value D) Can have two identical rows
D
The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space
Data Integrity
Physical database design decisions must be made carefully because of impacts on:
Data accessibility, response times, security
A component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated:
Data integrity
Tables
Data is represented in the form of: A) data trees. B) tables. C) data notes. D) chairs.
The relational data model consists of which components?
Data structure, Data manipulation, and Data integrity
True
Data structures include data organized in the form of tables with rows and columns. T / F
A detailed coding scheme recognized by system software for representing organizational data is called a:
Data type
transaction volumes.
Database access frequencies are estimated from:
transaction volumes
Database access frequencies are estimated from: A) transaction volumes. B) user logins. C) security violations. D) random number generation
The value a field will assume unless the user enters an explicit value for an instance of that field is called a:
Default value
Answer: FALSE
Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields.
Designing physical files requires _______ of where and when data are used in various ways.
Descriptions
A nonkey attribute is also called a(n):
Descriptor
descriptions
Designing physical files requires ________ of where and when data are used in various ways.
descriptions
Designing physical files requires ________ of where and when data are used in various ways. A) maps B) descriptions C) keys D) hints
The attribute on the left-hand side of the arow in a functional dependency is the:
Determinant
Coding techniques are used in cases when attributes have a sparse set of values or have large values which would negatively impact storage space. In this case, one can translate the values into codes. For example, one may have a list of colors for a car, which would have a limited number of values. In this case, one could create a lookup table for color and then replace the value for color with a code.
Discuss coding techniques and how they could be used.
In many database management systems, data integrity controls can be built into the field. The data type is one integrity control. Another integrity control is default values, where one specifies the value that the field will take if no data has been entered. Range control is another data integrity constraint which indicates the upper and lower bounds for the field. Null value control allows one to specify whether null values are allowed or not in the field. Finally, referential integrity ensures that any value entered into a foreign key must have a corresponding value in the primary key of the related table. This ensures that there are no misplaced values in the foreign key.
Discuss how data integrity can be controlled
Two relations are created. One represents the entity type in the relationship, and the other is an associative relation to represent the M:N relationship. The primary key of the associative relation consists of two attributes which take their value from the primary key of the other relation.
Discuss how to map a unary many-to-many relationship.
The entity type in the unary relationship is mapped to a relation in the same way as we did for a relation. We next add a foreign key attribute, which maps back to the primary key. This is called a recursive foreign key. So, if we had a relation such as a person supervises one or more persons, the person_id would be a primary key and then there would be a supervisor id which points back to the person_id
Discuss how to map a unary one-to-many relationship.
Each regular entity is transformed into a relation. There are a couple of things that need to be done with some special types of attributes. Composite attributes are broken down into their individual components. Multivalued attributes are broken down into separate relations. For example, if there was a multivalued skill attribute, this would become a skill relation. Also, we ignore derived attributes.
Discuss how you would map a regular entity to a relation.
Partitioning data has several advantages. First, queries are more efficient since data queried together are stored close to one another. Also, each partition of data can be stored in such a way as to optimize its own use. Partitioned data is more secure, since it is much easier to isolate data and make it available only to a group of users. Since smaller files take less time to backup and recover, it is easier to keep a partitioned database up and running. Also, the effects of damage are isolated, since other files (partitions) may not be affected by one damaged file. It is also easier to load balance, since the smaller files can be spread across multiple disks. Some disadvantages include inconsistent access speed. Different partitions may have different access times, depending upon physical storage of the file. Also, partitioning adds complexity for the programmer. Although to the user, partitioning appears transparent, this is often not the case for a programmer. Finally, data may be replicated across multiple partitions, which will require more storage space and possibly more time for updates
Discuss the advantages and disadvantages of partitioning
First, one must choose the storage format (or data types) of each attribute. These should be chosen to maximize data integrity and minimize storage space. Next, one must decide how to group the attributes from the logical model into tables. One must also decide upon the file organization to be used to store records, and also think about protecting data and how to recover if errors are found. Finally, one has to think about indexing and query optimization. What types of indexes will be required for the most efficient retrieval? How can queries be optimized?
Discuss the critical decisions that must be made during physical database design
Each relation (or table) has a unique name. An entry at the intersection of each row and column is atomic and each row is unique. Each attribute (or column) within a table has a unique name. The sequence of rows and columns is insignificant
Discuss the properties of relations
Data volume and frequency of use statistics are important inputs to the database design process. One has to maintain a good understanding of the size and usage patterns throughout the lifecycle of the database in order to plan the design.
Discuss the rationale behind data volume and usage analysis.
During logical database design, normalization is used as a check and balance to make sure that your E-R diagram is correct as well as your relational schema before doing physical design. Another occasion when you benefit is reverse-engineering an older system, since many of the tables and user views are redundant.
Discuss the two major occasions when you benefit from using normalization
There are two possible cases for associative entities. In one case, you can have an identifier assigned. You would then use this as the primary key. The other case is where the identifier is not assigned. In this case, you would use the primary keys from the other two (or more) relations that are related to the associative entity.
Discuss the two possible scenarios when mapping an associative entity
The three major types of integrity constraints are domain constraints, entity integrity and referential integrity. A domain is a set of attributes assigned to an attribute and consists of the domain name, data type, size and allowable values. The entity integrity rule is designed to ensure that every relation has a primary key and that the data values for the primary key are valid (in particular, not null). The referential integrity constraint is a rule that maintains consistency among the rows of two relations. If there is a foreign key in one relation, there must be a matching primary key in the other relation or the foreign key must be null
Discuss the types of integrity constraints.
A primary key uniquely identifies each row of a relation (or table). It can be either a single column or a composite of two or more columns, which is called a composite key. A foreign key allows us to represent the relationship between two tables. A foreign key in one table is generally a reference to a primary key in another table
Discuss the various relational keys.
There are several possible situations when denormalization makes sense. When we have two entities with a one-to-one relationship and high access frequency between them, then it makes sense to combine them into one entity. If one entity is optional, then there will be fields with null values in the combined entity. Another scenario is when you have an associative entity with nonkey attributes. If there is a high frequency of join operations between the entities, it might be more efficient to combine the associative entity with one of the other entities. The third case is reference data. If we have a 1:M relationship and the entity on the one side does not participate in any other relationships, then it might be wise to merge the entities
Discuss three possible opportunities for denormalization
A surrogate key should be created when any of the following conditions hold: there is a composite primary key, the natural primary key is inefficient (such as a long name) or the natural primary key is recycled. When you have an associative entity, it is often best to use a surrogate key when you have more than two relations that the associative entity is related to, since the primary key would be a composite of the primary keys of all entities related to the associative entity.
Discuss when it is best to create a surrogate key for an associative entity
One should always specify a primary key index for each table. Also, indexes are most useful on larger tables. Columns that frequently appear in WHERE clauses of SQL statements are good candidates for indexes, as well as those fields referenced by WHERE clauses and GROUP BY clauses. Any field where there is a wide variety of values is also a good candidate. One should use caution when creating indexes, however, since indexes do require overhead and must be updated for each insert, delete and update. This can cause significant overhead if there are a large number of transactions taking place against the database
Discuss when it is best to use indexes.
A multivalued attribute is one that can take on more than one value. According to the definition of a relation, there can be no multivalued attributes. The reason for this will be seen later when the schema is normalized. A multivalued attribute can be resolved by creating a separate instance for each value and repeating the data from the non-multivalued attributes
Discuss why it is important to remove multivalued attributes from a relation
Answer: Careful physical design enables an organization to demonstrate that data are accurate and well protected. Laws such as Sarbanes-Oxley and Basel II have been enacted to protect investors by requiring an internal control report within each financial report. Without a good physical design, an organization would be hard pressed to prove that its data are accurate
Discuss why physical database design is a foundation for compliance with regulations on financial reporting.
horizontal partitioning.
Distributing the rows of data into separate files is called: A) normalization. B) horizontal partitioning. C) vertical partitioning. D) file allocation.
In most cases the goal of _________ dominates the design process.
Efficient data processing
Answer: TRUE
Efficient database structures will be beneficial only if queries and the underlying database management system are tuned to properly use the structures.
States that no primary key attribute may be null
Entity integrity rule
Selecting a datatype involves four objectives that have different levels of importance for different applications. One must represent all possible values, improve data integrity, support all data manipulations and minimize storage space.
Explain how one goes about choosing data types.
When we have a supertype/subtype relationship, we need to assign one entity for the supertype and one for each subtype. The supertype has all attributes common to all subtypes as well as a primary key. Each subtype relation has the primary key of the supertype as well as any attributes that are specific to that subtype. Finally, we assign one or more attributes to the supertype to function as subtype discriminators
Explain how subtype/supertype relationships are converted to relations
In sequential file organization, the records are stored sequentially according to a primary key value. In index file organizations, the records are stored either sequentially or nonsequentially. An index is created that allows the application software to locate individual records. The index can be thought of as a card catalog.
Explain the differences between sequential and indexed file organizations.
A candidate key is an attribute or a combination of attributes that uniquely identifies a row in a relation. A candidate key must be nonredundant and must also uniquely identify each row. When we look at functional dependencies and candidate keys, we can always say that a determinant is always a candidate key.
Explain what a candidate key is and how it might be used
A tablespace is a named logical storage unit in which data from one or more tables, views, and indexes can be stored. A tablespace is mapped to one or more physical files in the OS and can be set to increase in size automatically. A tablespace can, in many ways, be thought of as its own file system available to the RDBMS.
Explain what a tablespace is and how it is used.
A primary key index is automatically generated when a table is created (provided that a primary key is specified). The primary key index identifies uniquely each row of the table. Thus, there is one entry in the primary key index for each row. A unique key index is similar, except that we have to define this with a separate create index command. A secondary key is nonunique. So, for example, we might create a secondary index on state. Since many records would have the same state, this is non-unique
Explain what primary, secondary and unique key indexes are.
A continguous section of disk storage space is called a(n):
Extent
A default value is the value that a field will always assume, regardless of what the user enters for an instance of that field.
F
A join index is a combination of two or more indexes
F
A key is a data structure used to determine the location of rows in a file that satisfy some condition
F
Adding notations to the EER diagram regarding data volumes and usage is of no value to the physical design process
F
Along with table scans, other elements of a query can be processed in parallel
F
An extent is a named portion of secondary memory allocated for the purpose of storing physical records
F
A characteristic of a relation is that the rows of a relation may hold identical values.
False
A combination key is a group of attributes that uniquely identifies a row.
False
A constraint that requires an instance of an entity to exist in one relation before it can be referenced in another relation is called an insertion anomaly.
False
A defining requirement for Boyce-Codd Normal Form (BCNF) is that every candidate key must be a determinant.
False
A relation can have only one candidate key.
False
A relation is a table that has special restrictions on it.
False
A tuple is a group of one or more columns that uniquely identifies a row.
False
Any table that meets the definition of a relation is in 2NF.
False
Candidates keys are called interlocking candidate keys when they share one or more attributes.
False
Given the functional dependency (A, B) → C, then it is true that A → C and B → C.
False
Given the functional dependency A → B, then it is necessarily true that B → A.
False
In the functional dependency shown as A → B, B is the determinant.
False
Surrogate keys usually slow performance.
False
The columns of a relation are sometimes called tuples.
False
The functional dependency noted as A → B means that the value of A can be determined from the value of B.
False
If a table meets the minimum definition of a relation, it has an effective or appropriate structure.
Fasle
Answer: TRUE
Fast data retrieval is one factor to consider when choosing a file organization for a particular database file.
The smallest unit of application data recognized by system software is a:
Field
A _________ is a technique for physically arranging the records of a file on secondary storage devices.
File organization
When all multivalued attributes have been removed from a relation, it is said to be in:
First normal form
An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:
Foreign key
Answer: FALSE
Free range partitioning is a type of horizontal partitioning in which each partition is defined by a range of values for one or more columns in the normalized table.
A constraint between two attributes is called a(n):
Functional dependency
Answer: TRUE
Hash partitioning spreads data evenly across partitions independent of any partition key value.
An attribute that may have more than one meaning is call a:
Homonym
Answer: FALSE
Horizontal partitioning is very different from creating a supertype/subtype relationship.
when different categories of a table's rows are processed separately.
Horizontal partitioning makes sense:
when different categories of a table's rows are processed separately.
Horizontal partitioning makes sense: A) when different categories of a table's rows are processed separately. B) when less security is needed. C) when partitions must be organized the same. D) when only one category is allowed
Answer: FALSE
Horizontal partitioning refers to the process of combining several smaller relations into a larger table.
When dealing with missing data, there are many options. Two integrity controls (default value and null control) will handle this problem. One also could substitute an estimate for the missing value which would then be stored in place of the missing value (and marked as an estimate). Another possible method is to use triggers to track missing values and create a log which can be viewed later. One other method is to perform sensitivity testing, which will tell us whether having missing data greatly impacts results. If results are not impacted, then the missing values are ignored
How are missing values generally handled in database management systems?
A weak entity becomes a relation. However, since a weak entity instance does not exist by itself, we must create a relation with the primary key from the strong relation as the primary key, as well as the identifying attribute.
How do you convert weak entities to relations?
Which of the following is an objective of selecting a data type?
Improve data integrity
Answer: TRUE
In a sequential file, the records are stored in sequence according to primary key.
efficient data processing
In most cases the goal of ________ dominates the design process.
efficient data processing
In most cases, the goal of ________ dominates the design process. A) efficient data processing B) security C) quick pointer updates D) shorter design times
Describe the requirements that a table must meet to qualify as a relation.
In order for a table to meet the requirements of a relation, it must satisfy certain conditions. First, the rows contain data about an entity or some portion of the entity. Second, the columns contain data that represent attributes of the entity. Third, each column must contain the same kind of data for all rows. Fifth, each column must have a unique name. Sixth, the cells must contain single values—no group or array values are allowed. Seventh, the order of the columns and the rows is insignificant. Finally, the rows must be unique.
create table
In the SQL language, the ________ statement is used to make table definitions. A) create session B) create table C) create index D) select
Candidate
In the figure below, Customer_ID in the CUSTOMER Table is which type of key? A) Composite B) Candidate C) Standard D) Grouped
TRUE.
In the figure below, each employee has exactly one manager. Answer : T / F
COMPOSITE
In the figure below, the primary key for "Order Line" is which type of key? A) Composite B) Foreign C) Standard D) Grouped
An associative entity
In the figure below, what is depicted? A) A one-to-one relationship B) A unary relationship C) A one-to-many relationship D) An associative entity
Recursive foreign
In the figure below, what type of key is depicted? A) Primary B) Recursive primary C) Composite D) Recursive foreign
Strong entity/weak entity
In the figure below, what type of relationship do the relations depict? A) Strong entity/weak entity B) Multivalued C) Composite foreign key D) One-to-many
one-to-Many
In the figure below, what type of relationship do the relations depict? A) Strong entity/weak entity B) One-to-many C) Ternary D) Many-to-many
Physical
In which data model would a code table appear?
Physical
In which data model would a code table appear? A) Conceptual B) Logical C) Physical D) Data layout
Hashed
In which type of file is multiple key retrieval not possible? A) Sequential B) Hashed C) Indexed D) Clustered
Answer: TRUE
Indexes are most useful for columns that frequently appear in WHERE clauses of SQL commands, either to qualify the rows to select or for linking.
Answer: FALSE
Indexes are most useful on small, clustered files.
A domain definition consists of the following components EXCEPT:
Integrity constraints
An index on columns from two or more tables that come from the same domain of values is called a:
Join index
Answer: TRUE
Keeping the zip code with the city and state in a table is a typical form of denormalization.
There are six different normal forms, all of which build upon each other. When a relation is in first normal form, all repeating groups have been removed. Second normal form means that any partial functional dependencies have been removed. A partial functional dependency occurs when a primary key is a composite key and a nonkey attribute is dependent upon part of the key. A relation is in third normal form when all transitive dependencies have been resolved. A transitive dependency occurs when one nonkey attribute is dependent upon another nonkey attribute. Boyce-Codd normal form removes any further dependencies that may result from anomalies. A relation is in fourth normal form when any dependencies from multivalued attributes have been removed. Fifth normal form removes any further anomalies. LO: 4.8: Use normalization to decompose a relation with anomalies into well-structured
List and explain 1st, 2nd, 3rd, BCNF, 4th, and 5th normal forms
The seven factors are: 1. Fast data retrieval 2. High throughput for processing data input and maintenance transactions 3. Efficient use of storage space 4. Protection from failures or data loss 5. Minimizing need for reorganization 6. Accommodating growth 7. Security from unauthorized use
List the seven factors to consider when choosing a file organization for a particular file in a database.
All of the following are that main goals of normalization EXCEPT:
Maximize storage space
The entity integrity rule states that:
No primary key attribute can be null
secondary key
One field or combination of fields for which more than one record may have the same combination of values is called a(n): A) secondary key. B) index. C) composite key. D) linked key
secondary key.
One field or combination of fields for which more than one record may have the same combination of values is called a(n):
Answer: FALSE
One method to handle missing values is to substitute an exact value.
Answer: TRUE
One objective of selecting a data type is to minimize storage space.
All of the following are common denormalization opportunities EXCEPT
One-to-many relationship
While Oracle has responsibility for managing data inside a tablespace, the tablespace as a whole is managed by the:
Operating system
Answer: FALSE
Parallel query processing speed is not significantly different from running queries in a non-parallel mode.
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 functional
A form of database specification that indicates all the parameters for data storage that are then input to database implementation is:
Physical
In which data model would a code table appear?
Physical
A ________ is a field of data used to locate a related field or record.
Pointer
An attribute (or attributes) that uniquely identifies each row in a relation is called a:
Primary key
________ partitioning is a method in which each partition is defined by a range of values for one or more columns of a normalized table
Range
A ________ limits the set of permissible values a field may assume.
Range control
An integrity control supported by a DBMS is:
Range control
Answer: FALSE
Reduced uptime is a disadvantage of partitioning.
Ensures that a value of a field exists in another database table
Referential integrity
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
A component is always used in only one item.
Referring to the figure below, which of the following is NOT true? A) A component is part of an item. B) A component is always used in only one item. C) A component can be part of an item. D) A component may be used in many items
A two-dimensional table of data is called a:
Relation
Explain the relationships among primary keys and functional dependencies.
Relations store instances of functional dependencies in their rows. All primary keys represent a functional dependency, since each primary key functionally determines the entire row. Since the primary key determines the entire row, if a primary key were duplicated in a relation, then the entire row would have to be duplicated. Since a relation can contain no duplicate rows, the primary key must be unique.
Answer: TRUE
Requirements for response time, data security, backup and recovery are all requirements for physical design.
Answer: FALSE
SOX stands for the Sorbet-Oxford Act.
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?
Second
One field or combination of fields for which more than one record may have the same combination of values is called a(n):
Secondary key
Answer: TRUE
Security is one advantage of partitioning.
A key decision in the physical design process is:
Selecting structures
________ is often used to decide whether or not missing data can be ignored
Sensitivity testing
Answer: TRUE
Sensitivity testing involves ignoring missing data unless knowing a value might significantly change results.
checking to see if missing data will greatly impact results.
Sensitivity testing involves:
checking to see if missing data will greatly impact results
Sensitivity testing involves: A) checking to see if your teeth hurt when you brush. B) seeing how accurate data are. C) checking to see if missing data will greatly impact results. D) deleting the associated row.
Two or more attributes having different names but the same meaning are called:
Synonyms
A hashing algorithm is a routine that converts a primary key value into a relative record number
T
A pointer is a field of data that can be used to locate a related field or record of data.
T
A range control limits the set of permissible values that a field may assume
T
A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored
T
Clustering allows for adjacent secondary memory locations to contain rows from several tables
T
Denormalization almost always leads to more storage space for raw data
T
Within Oracle, the named set of storage elements in which physical files for database tables may be stored is call a:
Tablespace
entity integrity rule
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
determinant.
The attribute on the left-hand side of the arrow in a functional dependency is the: A) candidate key. B) determinant. C) foreign key. D) primary key.
no primary key attribute can be null.
The entity integrity rule states that: A) no primary key attribute can be null. B) referential integrity must be maintained across all entities. C) each entity must have a primary key. D) a primary key must have only one attribute
Explain the essence of normalization that is implemented through the use of normal forms.
The essence of normalization is to create relations that contain data on a single theme, and for each row in a relation to contain all the data about that theme for a given entity. This essence is implemented in the normal forms by taking a relation that contains data on more than one theme and splitting it into multiple relations that each contains data on a single theme. The different normal forms protect against the occurrence of modification anomalies. In practice, making sure all relations are in Boyce-Codd Normal Form (BCNF), or in Fourth Normal Form (4NF) if multivalued dependencies are found, insures high reliability of the database.
Ternary
The figure below is an example of mapping which type of relationship? A) First B) Second C) Unary D) Ternary
Answer: FALSE
The logical database design always forms the best foundation for grouping attributes in the physical design.
merge
The need to ________ relations commonly occurs when different views need to be integrated. A) metadata B) system C) drop D) merge
fourth normal form.
The normal form which deals with multivalued dependencies is called: A) fifth normal form. B) fourth normal form. C) Boyce-Codd normal form. D) sixth normal form.
Boyce-Codd 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: A) fifth normal form. B) fourth normal form. C) Boyce-Codd normal form. D) sixth normal form.
Briefly describe the various tasks of the primary key.
The primary key is used for four primary tasks. First, it is used to uniquely identify the rows in a table. Second, it is used to represent rows in relationships. Third, most DBMS products use the values of the primary key to organize the storage of the relation. Finally, primary keys are used in indexes and other structures to improve performance for search operations.
Answer: FALSE
The query processor always knows the best way to process a query.
field.
The smallest unit of application data recognized by system software is a:
field.
The smallest unit of application data recognized by system software is a: A) field. B) row. C) data type. D) column.
Answer: FALSE
The smallest unit of named application data is a record.
Explain the steps in the process of putting a relation straight into Boyce-Codd Normal Form (BCNF).
The steps are: (1) Identify all functional dependencies in the relation. (2) Identify every candidate key in the relation. (3) Check to see if all determinants are candidate keys. a. If so, the relation is in BCNF-STOP. b. If not, then: i. Move the columns of the functional dependency of the determinant that is not a candidate key to a new relation. ii. Make the determinant of that functional dependency the primary key of the new relation. iii. Leave a copy of the determinant in the original relation as a foreign key. iv. Create a referential integrity constraint between the relations. NOTE: If there is more than one functional dependency that needs to be moved, move the functional dependency with the most columns first. (4) Repeat step (3) until you reach STOP.
data integrity
The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space. A) query design B) programmer productivity C) data integrity D) data integration
data integrity
The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.
Explain the terms relation, tuple, and attribute.
The terms relation, tuple, and attribute are used primarily by database theoreticians. These terms are synonymous with the terms table, row, and column, respectively, in regards to a relational database. They are also equivalent to the terms file, record, and field, which tend to be used by many traditional data processing professionals.
Default Value
The value a field will assume unless the user enters an explicit value for an instance of that field is called a: A) c. B) null value. C) range control. D) gurand.
default value.
The value a field will assume unless the user enters an explicit value for an instance of that field is called a(n):
Explain the concept of a foreign key
To implement a relationship within a relational database, the primary key of one relation is placed as an attribute in another relation. This attribute is called a foreign key in the second relation because it is the primary key of a relation that is foreign to the table in which the field resides.
A functional dependency between two or more nonkey attributes is called a:
Transitive dependency
A candidate key is one of a group of keys that may serve as the primary key in a relation.
True
A characteristic of a relation is that the cells of the relation hold a single value.
True
A deletion anomaly exists when deleting data about one entity results in the loss of data about another entity.
True
A determinant of a functional dependency may or may not be unique in a relation.
True
A foreign key is one or more columns in one relation that also is the primary key in another table.
True
A functional dependency is a relationship between attributes such that if we know the value of one attribute, we can determine the value of the other attribute.
True
A key can be composed of a group of attributes taken together.
True
A key is a combination of one or more columns that is used to identify particular rows in a relation.
True
A multivalued dependency exists when a determinant is matched to a set of values.
True
A primary key is a candidate key that has been selected to uniquely identify rows in a relation.
True
A referential integrity constraint is used to make sure the values of a foreign key match a valid value of a primary key.
True
A referential integrity constraint limits the values of a foreign key.
True
A relation is a table composed of columns and rows.
True
A relation is in 2NF if and only if it is in 1NF and all non-key attributes are determined by the entire primary key.
True
A relation is in 3NF if and only if is in 2NF and there are no non-key attributes determined by another non-key attribute.
True
A relation is in 4NF if it is in BCNF and it has no multivalued dependencies.
True
A relation is in 4NF when multivalued dependencies are isolated in their own relation.
True
A relation is in Boyce-Codd Normal Form (BCNF) if and only if it is in 3NF and every determinant is a candidate key.
True
A relation that is in domain/key normal form is assured to be free from all anomalies.
True
A row can be uniquely identified by a key.
True
A surrogate key is an artificial column that is added to a relation to be its primary key.
True
All relations are tables, but not all tables are relations.
True
An attribute is considered to be a non-key attribute when it is a non-prime attribute, which means that the attribute is not contained in any candidate key.
True
Any table that meets the definition of a relation is said to be in first normal form (1NF).
True
Attribute Y is functionally dependent on attribute X if the value of attribute X determines the value of Y.
True
Domain/key normal form requires that every constraint be a logical consequence of the definition of domains and keys.
True
Every time we break up a relation during the normalization process, we may have to create a referential integrity constraint.
True
Functional dependencies can involve groups of attributes.
True
Given the functional dependency (A, B) → C, the attributes (A, B) are referred to as a composite determinant.
True
Given the functional dependency A → (B, C), then it is true that A → B and A → C.
True
If by knowing the value of A we can find the value of B, then we would say that B is functionally dependent on A.
True
In functional dependencies, the attribute whose value is known or given is referred to as the determinant.
True
In relational terms as defined by E.F. Codd, a column is called an attribute.
True
In relational terms as defined by E.F. Codd, a row is called a tuple.
True
Surrogate keys are normally not shown on forms or reports.
True
The condition that a non-key attribute determines another non-key attribute is known as transitive dependency.
True
The essence of normalization is taking a relation that is not in BCNF and breaking it into multiple relations such that each one is in BCNF.
True
The multivalued dependency noted as A → → B, means that the value of A determines a set of values of B.
True
Undesirable consequences of changing the data in relation are called modification anomalies.
True
When designing or normalizing relations, each relation should have only one theme.
True
synonyms
Two or more attributes having different names but the same meaning are called: A) homonyms. B) aliases. C) synonyms. D) alternate attributes.
one must be able to check the output of a CASE tool.
Understanding the steps involved in transforming EER diagrams into relations is important because: A) one must be able to check the output of a CASE tool. B) there are rarely legitimate alternatives from which to choose. C) CASE tools can model any situation. D) CASE tools model hypothetical business problems
Answer: FALSE
Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance.
Answer: TRUE
Vertical partitioning means distributing the columns of a table into several separate physical records.
What is a functional dependency? Give an example not used in the text.
We say that a functional dependency exists when the value of one or more attributes determines the value of another attribute. For example, suppose we have a six-sided die numbered and colored as follows: Number Color 1 Red 2 Blue 3 White 4 Green 5 Green 6 Green In this case, Number functionally determines Color, written as Number → Color. Then, if we know the Number is 3, we know the Color is White. Note that the reverse is not true—Color does not determine Number since if we are given the Color Green the number could be 4, 5, or 6. The attribute on the left-hand side of the dependency is called the "determinant." Thus, Number is a determinant. Although equations can be expressed as functional dependencies [as when the equation TotalCost = NumberSold * ItemCost is written as the functional dependency (NumberSold, ItemCost) → TotalCost] functional dependencies are not equations. Also note that a determinant can have more than one attribute [as does (NumberSold, ItemCost) in the last example] and when it does it is called a composite determinant.
A relation that contains minimal redundancy and allows easy use is considered to be:
Well-structured
A hashing algorithm is a routine which converts a primary key value into a record address. Hashing algorithms are typically mathematical routines which perform computations such as dividing a primary key by a prime number and then using the remainder for a storage location. The hashing algorithm is used in a file organization to determine where to store the record. One limitation of this is that only one key value can be used for an index.
What is a hashing algorithm and how is it used in hashed file organizations?
An anomaly is an error or inconsistency that may result when a user attempts to update a table that contains redundant data. There are three types of anomalies. An insert anomaly occurs when a user attempts to enter new information for part of a table. For example, if we stored customer address with an order. A deletion anomaly occurs when the user wants to delete part of a record but also has to delete related data. A modification anomaly occurs when we have to change the same data in multiple places
What is an anomaly, and what are the three types of anomalies?
Clustering is a technique used in some database management systems (such as Oracle) to allow adjacent secondary memory space to contain rows from several tables. A cluster is defined by the tables used as well as the column or columns used for clustering. To create a cluster in Oracle, for example, we would create a cluster first with a create cluster command and then reference the cluster when we create each individual table.
What is clustering?
Since many of today's computers have either multiple processors or multi-core processors, many database management systems have introduced parallel query processing. With parallel query processing, a query can be broken apart and partitions can be run separately on each processor. The partitions have to be defined in advance by the database designer. The advantage to parallel query processing is speed, since the query can be done in much less time in parallel. The query has to be tuned to achieve the best degree of parallel
What is parallel query processing and how is it useful?
create two new relations, one containing the multivalued attribute.
When a regular entity type contains a multivalued attribute, one must: A) create a single relation with multiple lines for each instance of the multivalued attribute. B) create two new relations, one containing the multivalued attribute. C) create two new relations, both containing the multivalued attribute. D) delete the relation and start over.
First normal form
When all multivalued attributes have been removed from a relation, it is said to be in: A) first normal form. B) second normal form. C) Boyce-Codd normal form. D) third normal form
Horizontal partitioning is best used when different categories of rows are processed differently. For example, if you would like to have a report for each region, then it might make sense to partition the data by region. This type of partitioning will increase query performance. Horizontal partitioning is most often done by using a single column value or a date. Vertical partitioning splits up the columns of a table, repeating the primary key. This would best be used for only allowing access by a group of users to a portion of the data. Of course, in both cases, tables could be joined to retrieve all data.
When would you use horizontal partitioning, and when would you use vertical partitioning?
No two rows in a relation are identical.
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
Improve data integrity
Which of the following is an objective of selecting a data type?
Improve data integrity
Which of the following is an objective of selecting a data type? A) Represent a small number of possible values B) Maximize storage space C) Limit security D) Improve data integrity
Sam Hinz
Which of the following violates the atomic property of relations? A) Sam B) Hinz C) Sam Hinz D) Atomic
Hashed
Which type of file is easiest to update?
Hashed
Which type of file is easiest to update? A) Sequential B) Hashed C) Indexed D) Clustered
Sequential
Which type of file is most efficient with storage space?
Sequential
Which type of file is most efficient with storage space? A) Sequential B) Hashed C) Indexed D) Clustered
operating system
While Oracle has responsibility for managing data inside a tablespace, the tablespace as a whole is managed by the: A) user. B) CEO. C) XML. D) operating system.
operating system.
While Oracle has responsibility for managing data inside a tablespace, the tablespace as a whole is managed by the:
Sometimes, the query designer may know information that can be overlooked by the query optimizer. Often, in the course of testing queries, one may find that it is actually faster not to use a certain index or to use a different index. When this is the case, database management systems such as Oracle include a facility to override the query optimizer, called query hints.
Why would you want to override automatic query optimization?
tablespace.
Within Oracle, the named set of storage elements in which physical files for database tables may be stored is called a(n): A) extent. B) table. C) tablespace. D) partition
tablespace.
Within Oracle, the named set of storage elements in which physical files for database tables may be stored is called a(n):
Modification
________ are anomalies that can be caused by editing data in tables. A) Insertion B) Deletion C) Modification D) Creation
Physical
________ database specification indicates all the parameters for data storage that are then input to database implementation. A) Logical B) Physical C) Schematic D) Conceptual
Data integrity
________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated. A) Business rule constraint B) Data integrity C) Business integrity D) Data structure
Vertical
________ partitioning distributes the columns of a table into several separate physical records. A) Horizontal B) Crossways C) Vertical D) Final
Vertical
________ partitioning distributes the columns of a table into several separate physical records.
Deletion
________ problems are encountered when removing data with transitive dependencies. A) Insertion B) Modification C) Deletion D) Merging
A(n) ________ is defined by the tables and column of columns by which the tables are joined
cluster
________ can be built into the physical structure of fields for many database management systems
data integrity controls
A ________ is a detailed coding scheme recognized by system software, such as a DBMS, for representing organizational data
data type
Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields
f
Free range partitioning is a type of horizontal partitioning in which each partition is defined by a range of values for one or more columns in the normalized table
f
Horizontal partitioning is very different from creating a supertype/subtype relationship
f
Horizontal partitioning refers to the process of combining several smaller relations into a larger table
f
Indexes are most useful on small, clustered files
f
One method to handle missing values is to substitute an exact value
f
Parallel query processing speed is not significantly different from running queries in a non-parallel mode
f
Reduced uptime is a disadvantage of partitioning
f
The query processor always knows the best way to process a query
f
The smallest unit of named application data is a record
f
Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance.
f
Vertical partitioning means distributing the columns of a table into several separate physical records
f
A routine that converts a primary key value into a relative record number is called a(n)______
hashing algorithm
A(n) ________ overrides whatever query plan Oracle would naturally create for a query based upon the query optimizer.
hint
Distributing the rows of data into separate files is called ________
horizontal partitioning
________ distributes the columns of a table into several separate physical records
horizontal partitioning
A table or other data structure used to determine the location of rows in a file that satisfy some condition is called a(n)______
index
An index on columns from two or more tables that come from the same domain of values is called a(n) ________ index
join
Because data table row locations are dictated by the hashing algorithm, only one ________ can be used for hashing-based retrieval
key
A ________ limits the set of permissible values a field must assume
range control
A ________ key is one field or a combination of fields for which more than one record may have the same combination of values
secondary
Fast data retrieval is one factor to consider when choosing a file organization for a particular database file.
t
Hash partitioning spreads data evenly across partitions independent of any partition key value
t
In a sequential file, the records are stored in sequence according to primary key
t
Indexes are most useful for columns that frequently appear in WHERE clauses of SQL commands, either to qualify the rows to select or for linking
t
One decision in the physical design process is selecting structures
t
One objective of selecting a data type is to minimize storage space
t
Security is one advantage of partitioning
t
A ________ is a named set of disk storage elements in which physical files for database tables may be stored
tablespace
A candidate key must satisfy all of the following EXCEPT:
the key must indicate the row's position in the table
Denormalization is the process of transforming normalized relations into ________ physical record specifications
unnormalized
Indexes are most useful for columns that frequently appear in ________ clauses of SQL statements
where
