68 Multiple choice questions

1. • The database must already meet the requirements of both 1NF and 2NF.
   • The database must not contain any columns that aren't fully dependent upon the primary key.
   1. There are two basic requirements for a database to be in third normal form:
   2. The second normal form (2NF) ensures that ___
   3. The third normal form is violated when the ___
   4. Create a foreign key constraint using the SSMS interface:
2. an attribute or set of attributes that can be used to uniquely identify each row.
   1. Normalization reduces ___
   2. Locks are ___
   3. primary key:
   4. Candidate keys:
3. with a non-clustered index
   1. A primary key is a set of columns in a table that ___
   2. You cannot have two rows containing a ___
   3. On a table ___ , you can add multiple table columns to the index key.
   4. A table with a clustered index is considered a ___
4. Transact-SQL script statements
   1. You can only have one clustered index per table. Why?
   2. Create a non-clustered index using Sql Server Management Studio
   3. A table with a clustered index is considered a ___
   4. There are two ways to create a non-clustered index on a table. One uses ___ , and the other uses the visual interface of SQL Server Management Studio.
5. • Columns containing a unique key constraint may contain only one row with a NULL value.
   • A table may have multiple unique constraints.
   1. Once you have defined a primary key for the table, ___
   2. A unique constraint acts similarly to a primary key but with two important differences:
   3. A unique constraint acts similarly ___
   4. A unique key constraint will allow you to ___
6. "provide a fact about the key, the whole key, and nothing but the key."
   1. Central phrase to remember in describing the third normal form is that every attribute must ___
   2. Create a foreign key constraint using the SSMS interface:
   3. Create a non-clustered index using Sql Server Management Studio
   4. We must remove the TOTAL from the table to comply with the third normal form.
      We could replace our original attributes with the following attributes:
      • Order Number
      • Customer Number
      • Unit Price
      • Quantity
      
      Now our table is in 3NF. But, you might ask, what about the TOTAL?
7. same record in the primary key table.
   1. Multiple values in the foreign key table can reference the ___
   2. The third normal form is violated when the ___
   3. A table with a clustered index is considered a ___
   4. Placing columns in the correct table makes it easier to ___
8. secondary table.
   1. On a table ___ , you can add multiple table columns to the index key.
   2. Multiple values in the foreign key table can reference the ___
   3. When two tables relate to each other, one of them will act as the primary table and the other will act as the ___
   4. When a table has no clustered index, the data rows are then stored in an unordered structure called a ___
9. to a primary key (...)
   1. A unique key constraint will allow you to ___
   2. A unique constraint acts similarly ___
   3. Create a unique constraint:
   4. Normalization, in a nutshell, is ___
10. The data in the rows are sorted in only one order.
    1. A table with a clustered index is considered a ___
    2. You can only have one clustered index per table. Why?
    3. You cannot have two rows containing a ___
    4. Once you have defined a primary key for the table, ___
11. primary key is defined for a table.
    1. The third normal form is violated when the ___
    2. Data can only be sorted in one way, it simply is not possible to have ___ clustered indexes on the same table.
    3. A primary key is a set of columns in a table that ___
    4. One important feature of SQL Server is its automatic creation of a clustered index when the ___
12. SSMS »» . Right-click the table and select Design view »» Table Designer »» Select Relationships »» Select the table to which you wish to add the foreign key constraint »» Tables and Columns Specification »» Click the ellipsis (. . .) »» Select the table that your foreign key refers to in the primary key table »» OK »» Close »» Save All
    1. Create a foreign key constraint using the SSMS interface:
    2. A unique constraint acts similarly ___
    3. A unique key constraint will allow you to ___
    4. Create a unique constraint:
13. functionally dependent upon the entire primary key.
    1. It is in the tables that relate entities that ___
    2. 2NF is entirely based on dependency: specifically, the attributes of the entity in question, which is not part of a candidate key, must be ___
    3. A dependency is considered transient when ___
    4. A ___ is similar to a partial dependency in that both refer to attributes that are not fully dependent on a primary key.
14. locking contention and improves multiple-user performance.
    1. Normalization, by definition, is ___
    2. Locks are ___
    3. Normalization, in a nutshell, is ___
    4. Normalization reduces ___
15. Primary keys, foreign keys, and composite (unique) keys.
    1. Create a foreign key constraint using the SSMS interface:
    2. Three different types of constraints available within SQL Server ensure that you are able to maintain database integrity:
    3. Multiple values in the foreign key table can reference the ___
    4. Create a non-clustered index using Sql Server Management Studio
16. NULL value in the same option, as that would violate the unique constraint's duplicate value error.
    1. Normalization, in a nutshell, is ___
    2. Every table must have a ___
    3. It is in the tables that relate entities that ___
    4. You cannot have two rows containing a ___
17. enforce the uniqueness property of columns, in addition to a primary key within a table.
    1. Create a unique constraint:
    2. A unique key constraint will allow you to ___
    3. A unique constraint acts similarly ___
    4. Every table must have a ___
18. Isolate independent multiple relationships
    1. primary key:
    2. Fourth normalization form (4NF):
    3. Create a unique constraint:
    4. Third normalization form (3NF):
19. the composite primary key plays an important role.
    1. By definition, a primary key must be ___
    2. It is in the tables that relate entities that ___
    3. A primary key is a set of columns in a table that ___
    4. The second normal form (2NF) ensures that ___
20. alternate keys.
    1. Candidate keys that are not selected as the primary key are then known as ___
    2. A primary key is a set of columns in a table that ___
    3. A composite primary key occurs when ___
    4. Once you have defined a primary key for the table, ___
21. attribute depends on the key but also on another nonkey attribute.
    1. The third normal form is resolved by ___
    2. The third normal form is violated when the ___
    3. The second normal form (2NF) ensures that ___
    4. Third normalization form (3NF):
22. All the potential primary keys.
    1. Candidate keys:
    2. Normalization reduces ___
    3. Create a unique constraint:
    4. primary key:
23. B-tree
    1. These keys are then stored in a structure called a ___ that enables SQL Server to find the row(s) associated with those defined values much more quickly and efficiently.
    2. Once you have defined a primary key for the table, ___
    3. An index is an on-disk (or stored) structure associated entirely with a table or a view that ___
    4. There are two ways to create a non-clustered index on a table. One uses ___ , and the other uses the visual interface of SQL Server Management Studio.
24. uniquely identifies each row of data.
    1. A primary key is a set of columns in a table that ___
    2. A composite primary key occurs when ___
    3. It is in the tables that relate entities that ___
    4. A unique key constraint will allow you to ___
25. • The table must have no duplicate records.
    • The table also must not have multivalued attributes..
    • The entries in the column or attribute must be of the same data type.
    1. The third normal form is violated when the ___
    2. The third normal form (3NF) checks for ___
    3. The second normal form (2NF) ensures that ___
    4. The first normalized form (1NF) means the data is in an entity format, which basically means that the following three conditions must be met:
26. increases the speed of data retrieval.
    1. An index is an on-disk (or stored) structure associated entirely with a table or a view that ___
    2. It is in the tables that relate entities that ___
    3. You can only have one clustered index per table. Why?
    4. These keys are then stored in a structure called a ___ that enables SQL Server to find the row(s) associated with those defined values much more quickly and efficiently.
27. difficult to modify.
    1. Normalization, by definition, is ___
    2. Placing columns in the correct table makes it easier to ___
    3. A non-normalized database is often more complex and therefore more ___
    4. A table with a clustered index is considered a ___
28. Eliminate redundant data
    1. Fourth normalization form (4NF):
    2. Second normalization form (2NF):
    3. Third normalization form (3NF):
    4. First normalization form (1NF):
29. SSMS »» right-clicking the table and selecting Design »» Table Designer »» select Indexes/Keys »» Click on the Add button to create a new key »» the Type property in the right side of the property box and change it from the default of Index to Unique Key »» Columns property section, Click (. . .) »» Close »» Save All
    1. Create a unique constraint:
    2. Locks are ___
    3. Every table must have a ___
    4. Normalization reduces ___
30. clustered table
    1. The third normal form is resolved by ___
    2. A table with a clustered index is considered a ___
    3. You can only have one clustered index per table. Why?
    4. A unique constraint acts similarly ___
31. "many-to-many" relationship between the Customer and Products tables.
    1. On a table ___ , you can add multiple table columns to the index key.
    2. System allows you to assign multiple products to multiple customers. This is a ___
    3. A primary key is a set of columns in a table that ___
    4. It is in the tables that relate entities that ___
32. corresponding values in the primary key table.
    1. A table with a clustered index is considered a ___
    2. It is in the tables that relate entities that ___
    3. Create a unique constraint:
    4. SQL Server will require all associated values with the constraint in the foreign key table to have ___
33. you define more than one column as your primary key.
    1. A composite primary key occurs when ___
    2. Normalization reduces ___
    3. The second normal form is violated when ___
    4. primary key:
34. • Tables that define entities
    • Tables that relate entities
    1. Placing columns in the correct table makes it easier to ___
    2. Imagine that you take the tables in a database and categorize them based on two data types:
    3. Once you have defined a primary key for the table, ___
    4. Multiple values in the foreign key table can reference the ___
35. an attribute depends on only part of the key.
    1. The second normal form (2NF) ensures that ___
    2. The second normal form is violated when ___
    3. The third normal form is resolved by ___
    4. The third normal form is violated when the ___
36. foreign key.
    1. A composite primary key occurs when ___
    2. It is in the tables that relate entities that ___
    3. In order to connect two tables, the primary key is replicated from the primary to the secondary table, and all the key attributes duplicated from the primary table become known as the ___
    4. If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___
37. unique and must have a value that is not null.
    1. Normalization reduces ___
    2. Normalization, by definition, is ___
    3. Normalization, in a nutshell, is ___
    4. By definition, a primary key must be ___
38. the fourth normal form
    1. The second normal form (2NF) ensures that ___
    2. The third normal form is violated when the ___
    3. The first normalized form (1NF) means the data is in an entity format, which basically means that the following three conditions must be met:
    4. The fourth normal form (4NF) involves two independent attributes brought together to form a primary key along with a third attribute. But, if the two attributes don't really uniquely identify the entity without the third attribute, then the design violates ___ .
39. the design violates the fourth normal form.
    1. If the two attributes don't really uniquely identify the entity without the third attribute, then ___
    2. It is in the tables that relate entities that ___
    3. The third normal form is violated when the ___
    4. If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___
40. each attribute does in fact describe the entity.
    1. Second normalization form (2NF):
    2. It is in the tables that relate entities that ___
    3. The second normal form (2NF) ensures that ___
    4. The second normal form is violated when ___
41. transitive dependency
    1. A primary key is a set of columns in a table that ___
    2. A ___ is similar to a partial dependency in that both refer to attributes that are not fully dependent on a primary key.
    3. 2NF is entirely based on dependency: specifically, the attributes of the entity in question, which is not part of a candidate key, must be ___
    4. It is in the tables that relate entities that ___
42. logical or software-based.
    1. A primary key is a set of columns in a table that ___
    2. A clustered index is a physical construct, unlike most indexes, which are ___
    3. A dependency is considered transient when ___
    4. A clustered index is a ___ construct, unlike most indexes, which are logical or software-based.
43. foreign key in one of the tables to the primary key of the referenced table.
    1. A foreign key then creates a relationship between two tables by linking the ___
    2. A primary key is a set of columns in a table that ___
    3. It is in the tables that relate entities that ___
    4. A dependency is considered transient when ___
44. the elimination of redundant data to save space.
    1. Normalization, in a nutshell, is ___
    2. By definition, a primary key must be ___
    3. Normalization has these three advantages:
    4. Every table must have a ___
45. We can simply compute the TOTAL "on the fly" when performing database queries.
    
    We can now use the following query in order to achieve the same results, without violating normalization rules:
    
    SELECT OrderNumber, UnitPrice * Quantity
    AS Total
    FROM WidgetOrders
    1. On a table ___ , you can add multiple table columns to the index key.
    2. There are two basic requirements for a database to be in third normal form:
    3. We must remove the TOTAL from the table to comply with the third normal form.
       We could replace our original attributes with the following attributes:
       • Order Number
       • Customer Number
       • Unit Price
       • Quantity
       
       Now our table is in 3NF. But, you might ask, what about the TOTAL?
    4. Central phrase to remember in describing the third normal form is that every attribute must ___
46. SSMS »» Databases folder »» Tables folder »» Indexes subfolder »» New index »» New Index properties box »» This is where you would select whether the index you are creating is to be clustered or non-clustered.
    1. A table with a clustered index is considered a ___
    2. Create a unique constraint:
    3. Create a non-clustered index using Sql Server Management Studio
    4. Create a foreign key constraint using the SSMS interface:
47. Eliminate repeating groups
    1. First normalization form (1NF):
    2. Fifth normalization form (5NF):
    3. Fourth normalization form (4NF):
    4. Third normalization form (3NF):
48. primary key; without a primary key, it's not a valid table.
    1. Every table must have a ___
    2. First normalization form (1NF):
    3. Candidate keys:
    4. Third normalization form (3NF):
49. not fully dependent on the primary key.
    1. We have a table of widget orders that contains the following attributes:
       • Order Number (primary key)
       • Customer Number
       • Unit Price
       • Quantity
       • Total
       
       What about the total? The total can be derived by multiplying the unit price by the quantity, and therefore it's ___
    2. If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___
    3. You cannot have two rows containing a ___
    4. Create a foreign key constraint using the SSMS interface:
50. mechanisms that are used to prevent simultaneous changes to the database
    1. Candidate keys:
    2. Locks are ___
    3. Create a unique constraint:
    4. Normalization reduces ___
51. • Development costs
    • Usability
    • Extensibility
    1. Normalization, by definition, is ___
    2. Normalization reduces ___
    3. Normalization has these three advantages:
    4. Third normalization form (3NF):
52. attribute1 is dependent on attribute2, which is then dependent on the primary key.
    1. A unique constraint acts similarly ___
    2. The second normal form is violated when ___
    3. A composite primary key occurs when ___
    4. A dependency is considered transient when ___
53. the process of organizing data in order to reduce redundancy by dividing a database into two or more tables and then defining table relationships.
    1. Normalization, by definition, is ___
    2. Normalization, in a nutshell, is ___
    3. Third normalization form (3NF):
    4. Normalization reduces ___
54. transitive dependencies.
    1. The second normal form is violated when ___
    2. The third normal form is resolved by ___
    3. The second normal form (2NF) ensures that ___
    4. The third normal form (3NF) checks for ___
55. two
    1. A table with a clustered index is considered a ___
    2. A unique key constraint will allow you to ___
    3. Placing columns in the correct table makes it easier to ___
    4. Data can only be sorted in one way, it simply is not possible to have ___ clustered indexes on the same table.
56. you have met the first normalized form criterion.
    1. Once you have defined a primary key for the table, ___
    2. By definition, a primary key must be ___
    3. You cannot have two rows containing a ___
    4. A composite primary key occurs when ___
57. moving the nondependent attribute to a new entity.
    1. The third normal form (3NF) checks for ___
    2. The third normal form is resolved by ___
    3. The second normal form (2NF) ensures that ___
    4. The second normal form is violated when ___
58. Isolate semantically related multiple relationships
    1. Third normalization form (3NF):
    2. Fifth normalization form (5NF):
    3. First normalization form (1NF):
    4. Fourth normalization form (4NF):
59. Eliminate columns not dependent on key
    1. Normalization reduces ___
    2. Candidate keys:
    3. Third normalization form (3NF):
    4. First normalization form (1NF):
60. overwritten by another transaction that executes at the same time.
    1. Without locks, a change made by one transaction could be ___
    2. A table with a clustered index is considered a ___
    3. The third normal form is resolved by ___
    4. You cannot have two rows containing a ___
61. development costs.
    1. An index is an on-disk (or stored) structure associated entirely with a table or a view that ___
    2. You can only have one clustered index per table. Why?
    3. Although it may take longer to design a normalized database, such databases are easier to work with and reduce ___
    4. Imagine that you take the tables in a database and categorize them based on two data types:
62. heap.
    1. Each table can have only ___ clustered index that defines how SQL Server will sort the data stored inside the table.
    2. Create a non-clustered index using Sql Server Management Studio
    3. When a table has no clustered index, the data rows are then stored in an unordered structure called a ___
    4. When two tables relate to each other, one of them will act as the primary table and the other will act as the ___
63. one
    1. Each table can have only ___ clustered index that defines how SQL Server will sort the data stored inside the table.
    2. Create a foreign key constraint using the SSMS interface:
    3. A table with a clustered index is considered a ___
    4. Create a non-clustered index using Sql Server Management Studio
64. second normal form.
    1. If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___
    2. Once you have defined a primary key for the table, ___
    3. 2NF is entirely based on dependency: specifically, the attributes of the entity in question, which is not part of a candidate key, must be ___
    4. By definition, a primary key must be ___
65. understand a database and write correct queries.
    1. It is in the tables that relate entities that ___
    2. Placing columns in the correct table makes it easier to ___
    3. A composite primary key occurs when ___
    4. Every table must have a ___
66. physical
    1. A clustered index is a physical construct, unlike most indexes, which are ___
    2. Create a foreign key constraint using the SSMS interface:
    3. A primary key is a set of columns in a table that ___
    4. A clustered index is a ___ construct, unlike most indexes, which are logical or software-based.
67. SQL Server Management Studio.
    1. A table with a clustered index is considered a ___
    2. You can only have one clustered index per table. Why?
    3. There are two ways to create a non-clustered index on a table. One uses Transact-SQL script statements, and the other uses the visual interface of ___
    4. There are two ways to create a non-clustered index on a table. One uses ___ , and the other uses the visual interface of SQL Server Management Studio.
68. resolve the repeating group of tour attributes into a single unique attribute and then move any multiple values to a unique table.
    1. The base camp data (...) violates the first normal form: the repeating tour attribute (Tour 1, Tour 2, and Tour 3) is not unique ( there are three values assigned for Tours). To redesign the data model so that it complies with the first normal form, you must ___
    2. The third normal form is resolved by ___
    3. The second normal form is violated when ___
    4. The third normal form is violated when the ___