MTA 98-364 Test 4

a) normalization Section Reference: Normalizing a Database Explanation: Using normalization techniques, with respect to data storage, came more into play back in the days when data storage cost a great deal more than it does today. Normalization, in a nutshell, is the elimination of redundant data to save space.

a) 1NF Section Reference: What is Normalization? Explanation: Of the five normalization forms (NFs), the first one, 1NF, is Eliminate Repeating Groups.

b) 2NF Section Reference: What is Normalization? Explanation: The second normalization form, 2NF, is Eliminate Redundant Data,

c) There are no primary keys. Section Reference: First Normal Form (1NF) - Eliminate Repeating Groups Explanation: The first normalized form (1NF) means the data is in an entity format, which means that the following three conditions must be met: The table must have no duplicate records. After you define a primary key for the table, you have met the first normalized form criteria. The table must not have multi-valued attributes—meaning, you can't combine multiple values that are considered valid for a column in a single column. The entries in the column or attribute are of the same data type.

d) unique constraint key Section Reference: Understanding Primary, Foreign and Composite Keys Explanation: Unique constraint keys enable you to enforce the uniqueness property of columns, other than a primary key within a table. A unique constraint key acts similar to a primary key, but with two important differences: Columns containing a unique key constraint may contain only one row with a NULL value. You cannot have two rows containing a NULL value in the same option because that would violate the unique constraint's duplicate value error. A table can have multiple unique constraints.

a) primary key Explanation: Perhaps the most important concept of designing any database table is that it has a primary key—an attribute or set of attributes that can be used to uniquely identify the row. Every table must have a primary key; without a primary key, it's not a valid table. By definition, a primary key must be unique and must have a value (which is not null).

d) foreign key Section Reference: Foreign Keys Explanation: When you have two tables relating to one another, one of those tables acts as the primary table and the other acts as the secondary table. To connect the two tables, the primary key is replicated from the primary to secondary table, and then all the key attributes duplicated from the primary table are known as the foreign key. Although this may be referred to, at times, as a parent-child relationship, enforcing the foreign key attribute is actually referred to as referential integrity.

a) clustered index Section Reference: Clustered Indexes Explanation: A clustered index defines how SQL Server will sort the data stored inside the table. Because that data can be sorted only in one way, it simply is not possible to have two clustered indexes on the same table. Also, a clustered index is a physical construct as opposed to most indexes, which are logical or software-based.

b) non-clustered index Section Reference: Non-Clustered Indexes Explanation: A nonclustered index contains the nonclustered index key values, and each of those keys has a pointer to a data row that contains the key value. This pointer is referred to as a row locator, whose structure depends on whether the data pages are stored in a heap or as a clustered table. This is an important part of a nonclustered index's function because if it points to a heap, the row locator is a pointer to the row but, in a clustered table, the row locator is then the clustered index key.

c) unique constraint key Section Reference: Understanding Primary, Foreign and Composite Keys Explanation: Because an employee ID should be unique within a company, it would make sense that it would be a unique constraint key.

a) 1NF Section Reference: First Normal Form (1NF) - Eliminate Repeating Groups Explanation: Of the three conditions required to meet 1NF, as soon as you have defined a primary key for the table, you have met the first normalized form criteria.

c) 3NF Section Reference: Third Normal Form (3NF) - Eliminate Columns Not Dependent on Keys Explanation: The third normal form (3NF) checks for transitive dependencies. A transitive dependency is similar to a partial dependency in that they both refer to attributes that don't fully depend on a primary key. A dependency is considered transient when attribute1 is dependent on attribute2, which is then dependent on the primary key.

a) 1NF Section Reference: First Normal Form (1NF) - Eliminate Repeating Groups Explanation: After you define a primary key for the table, you have met the first normalized form criteria.

c) B-tree Section Reference: Clustered Index Explanation: An index is an on-disk (or stored) structure associated entirely with a table or a view that aids in the speed of data retrieval. To build this index, a series of keys are built from one or more columns in each row within the table or a view. These keys are then stored in a structure called a B-tree, which enables SQL Server to then find the row(s) associated with those defined values much more quickly and efficiently.

b) non-clustered index Section Reference: Non-Clustered Indexes Explanation: A non-clustered index contains the non-clustered index key values, and each of those keys has a pointer to a data row that contains the key value. This pointer is referred to as a row locator, whose structure depends on whether the data pages are stored in a heap or as a clustered table.

Answer: Unlimited Section Reference: Foreign Keys Explanation: Because the non-clustered indexes uses points to sort the data without actually storing the stored table, you can have as many non-clustered indexes as you want.

Answer: primary key Section Reference: Foreign Keys Explanation: Every table must have a primary key; without a primary key, it's not a valid table. By definition, a primary key must be unique and must have a value (which is not null).

Answer: composite primary key Section Reference: Composite Primary Keys Explanation: A composite primary key occurs when you define more than one column as your primary key and although many database administrators do not use them or are not aware of them, they play an integral part in the designing of a good, solid data model.

Answer: one Section Reference: Clustered Indexes Explanation: When you begin looking at implementing indexes, remember that each table can have only one clustered index that defines how SQL Server will sort the data stored inside the table. Because that data can only be sorted in one way, having two clustered indexes on the same table is simply not possible. Also note that the clustered index is a physical construct as opposed to most indexes, which are logical, or software-based.

Answer: clustered and non-clustered indexes Section Reference: Understanding Clustered and Non-Clustered Indexes Explanation: As a database administrator, you need to understand what the two types of indexes (clustered and nonclustered) do, and what the role of these indexes are within a database environment.