Question types

Start with

Question limit

of 68 available terms

Print test

68 Matching questions

  1. 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 ___
  2. Data can only be sorted in one way, it simply is not possible to have ___ clustered indexes on the same table.
  3. 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 ___ .
  4. Multiple values in the foreign key table can reference the ___
  5. The third normal form is violated when the ___
  6. 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.
  7. Candidate keys that are not selected as the primary key are then known as ___
  8. Candidate keys:
  9. 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.
  10. A primary key is a set of columns in a table that ___
  11. If the two attributes don't really uniquely identify the entity without the third attribute, then ___
  12. Central phrase to remember in describing the third normal form is that every attribute must ___
  13. A unique constraint acts similarly ___
  14. Three different types of constraints available within SQL Server ensure that you are able to maintain database integrity:
  15. System allows you to assign multiple products to multiple customers. This is a ___
  16. The second normal form (2NF) ensures that ___
  17. 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?
  18. Placing columns in the correct table makes it easier to ___
  19. An index is an on-disk (or stored) structure associated entirely with a table or a view that ___
  20. A table with a clustered index is considered a ___
  21. When a table has no clustered index, the data rows are then stored in an unordered structure called a ___
  22. By definition, a primary key must be ___
  23. A clustered index is a ___ construct, unlike most indexes, which are logical or software-based.
  24. A ___ is similar to a partial dependency in that both refer to attributes that are not fully dependent on a primary key.
  25. primary key:
  26. A unique constraint acts similarly to a primary key but with two important differences:
  27. 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 ___
  28. When two tables relate to each other, one of them will act as the primary table and the other will act as the ___
  29. 2NF is entirely based on dependency: specifically, the attributes of the entity in question, which is not part of a candidate key, must be ___
  30. There are two basic requirements for a database to be in third normal form:
  31. Every table must have a ___
  32. You cannot have two rows containing a ___
  33. A clustered index is a physical construct, unlike most indexes, which are ___
  34. Without locks, a change made by one transaction could be ___
  35. Normalization, by definition, is ___
  36. A foreign key then creates a relationship between two tables by linking the ___
  37. The third normal form (3NF) checks for ___
  38. Create a foreign key constraint using the SSMS interface:
  39. Create a unique constraint:
  40. A non-normalized database is often more complex and therefore more ___
  41. One important feature of SQL Server is its automatic creation of a clustered index when the ___
  42. Imagine that you take the tables in a database and categorize them based on two data types:
  43. Normalization reduces ___
  44. Fifth normalization form (5NF):
  45. Third normalization form (3NF):
  46. First normalization form (1NF):
  47. Each table can have only ___ clustered index that defines how SQL Server will sort the data stored inside the table.
  48. SQL Server will require all associated values with the constraint in the foreign key table to have ___
  49. If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___
  50. You can only have one clustered index per table. Why?
  51. A composite primary key occurs when ___
  52. 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 ___
  53. Normalization has these three advantages:
  54. Fourth normalization form (4NF):
  55. A unique key constraint will allow you to ___
  56. Create a non-clustered index using Sql Server Management Studio
  57. Second normalization form (2NF):
  58. Although it may take longer to design a normalized database, such databases are easier to work with and reduce ___
  59. The first normalized form (1NF) means the data is in an entity format, which basically means that the following three conditions must be met:
  60. The second normal form is violated when ___
  61. The third normal form is resolved by ___
  62. Once you have defined a primary key for the table, ___
  63. Locks are ___
  64. 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 ___
  65. It is in the tables that relate entities that ___
  66. A dependency is considered transient when ___
  67. Normalization, in a nutshell, is ___
  68. On a table ___ , you can add multiple table columns to the index key.
  1. a foreign key.
  2. b moving the nondependent attribute to a new entity.
  3. c not fully dependent on the primary key.
  4. d to a primary key (...)
  5. e mechanisms that are used to prevent simultaneous changes to the database
  6. f functionally dependent upon the entire primary key.
  7. g with a non-clustered index
  8. h the fourth normal form
  9. i attribute depends on the key but also on another nonkey attribute.
  10. j foreign key in one of the tables to the primary key of the referenced table.
  11. k one
  12. l second normal form.
  13. m physical
  14. n Eliminate repeating groups
  15. o transitive dependencies.
  16. p corresponding values in the primary key table.
  17. q primary key; without a primary key, it's not a valid table.
  18. r alternate keys.
  19. s the design violates the fourth normal form.
  20. t • Columns containing a unique key constraint may contain only one row with a NULL value.
    • A table may have multiple unique constraints.
  21. u the process of organizing data in order to reduce redundancy by dividing a database into two or more tables and then defining table relationships.
  22. v NULL value in the same option, as that would violate the unique constraint's duplicate value error.
  23. w B-tree
  24. x SQL Server Management Studio.
  25. y the composite primary key plays an important role.
  26. z 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.
  27. aa • Development costs
    • Usability
    • Extensibility
  28. ab uniquely identifies each row of data.
  29. ac clustered table
  30. ad 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
  31. ae an attribute depends on only part of the key.
  32. af 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
  33. ag Eliminate redundant data
  34. ah the elimination of redundant data to save space.
  35. ai The data in the rows are sorted in only one order.
  36. aj resolve the repeating group of tour attributes into a single unique attribute and then move any multiple values to a unique table.
  37. ak you define more than one column as your primary key.
  38. al "provide a fact about the key, the whole key, and nothing but the key."
  39. am Eliminate columns not dependent on key
  40. an heap.
  41. ao • 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.
  42. ap transitive dependency
  43. aq secondary table.
  44. ar development costs.
  45. as Isolate semantically related multiple relationships
  46. at understand a database and write correct queries.
  47. au unique and must have a value that is not null.
  48. av two
  49. aw you have met the first normalized form criterion.
  50. ax • Tables that define entities
    • Tables that relate entities
  51. ay attribute1 is dependent on attribute2, which is then dependent on the primary key.
  52. az logical or software-based.
  53. ba increases the speed of data retrieval.
  54. bb All the potential primary keys.
  55. bc Isolate independent multiple relationships
  56. bd overwritten by another transaction that executes at the same time.
  57. be • 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.
  58. bf enforce the uniqueness property of columns, in addition to a primary key within a table.
  59. bg primary key is defined for a table.
  60. bh 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
  61. bi Transact-SQL script statements
  62. bj difficult to modify.
  63. bk same record in the primary key table.
  64. bl "many-to-many" relationship between the Customer and Products tables.
  65. bm an attribute or set of attributes that can be used to uniquely identify each row.
  66. bn locking contention and improves multiple-user performance.
  67. bo each attribute does in fact describe the entity.
  68. bp Primary keys, foreign keys, and composite (unique) keys.