New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • Normalization, in a nutshell, is ___

    the elimination of redundant data to save space.

    Normalization, by definition, is ___

    the process of organizing data in order to reduce redundancy by dividing a database into two or more tables and then defining table relationships.

    First normalization form (1NF):

    Eliminate repeating groups

    Second normalization form (2NF):

    Eliminate redundant data

    Third normalization form (3NF):

    Eliminate columns not dependent on key

    Fourth normalization form (4NF):

    Isolate independent multiple relationships

    Fifth normalization form (5NF):

    Isolate semantically related multiple relationships

    The first normalized form (1NF) means the data is in an entity format, which basically means that the following three conditions must be met:

    • 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.

    Once you have defined a primary key for the table, ___

    you have met the first normalized form criterion.

    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 ___

    resolve the repeating group of tour attributes into a single unique attribute and then move any multiple values to a unique table.

    The second normal form (2NF) ensures that ___

    each attribute does in fact describe the entity.

    2NF is entirely based on dependency: specifically, the attributes of the entity in question, which is not part of a candidate key, must be ___

    functionally dependent upon the entire primary key.

    If an attribute depends on one of the primary key attributes but not the others, then it becomes a partial dependency, which violates the ___

    second normal form.

    The third normal form (3NF) checks for ___

    transitive dependencies.

    A ___ is similar to a partial dependency in that both refer to attributes that are not fully dependent on a primary key.

    transitive dependency

    A dependency is considered transient when ___

    attribute1 is dependent on attribute2, which is then dependent on the primary key.

    The second normal form is violated when ___

    an attribute depends on only part of the key.

    The third normal form is violated when the ___

    attribute depends on the key but also on another nonkey attribute.

    Central phrase to remember in describing the third normal form is that every attribute must ___

    "provide a fact about the key, the whole key, and nothing but the key."

    The third normal form is resolved by ___

    moving the nondependent attribute to a new entity.

    There are two basic requirements for a database to be in third normal form:

    • 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.

    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 ___

    not fully dependent on the primary key.

    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?

    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

    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 ___ .

    the fourth normal form

    If the two attributes don't really uniquely identify the entity without the third attribute, then ___

    the design violates the fourth normal form.

    Normalization reduces ___

    locking contention and improves multiple-user performance.

    Locks are ___

    mechanisms that are used to prevent simultaneous changes to the database

    Without locks, a change made by one transaction could be ___

    overwritten by another transaction that executes at the same time.

    Normalization has these three advantages:

    • Development costs
    • Usability
    • Extensibility

    Although it may take longer to design a normalized database, such databases are easier to work with and reduce ___

    development costs.

    Placing columns in the correct table makes it easier to ___

    understand a database and write correct queries.

    A non-normalized database is often more complex and therefore more ___

    difficult to modify.

    Three different types of constraints available within SQL Server ensure that you are able to maintain database integrity:

    Primary keys, foreign keys, and composite (unique) keys.

    A unique key constraint will allow you to ___

    enforce the uniqueness property of columns, in addition to a primary key within a table.

    A unique constraint acts similarly ___

    to a primary key (...)

    A unique constraint acts similarly to a primary key but with two important differences:

    • Columns containing a unique key constraint may contain only one row with a NULL value.
    • A table may have multiple unique constraints.

    You cannot have two rows containing a ___

    NULL value in the same option, as that would violate the unique constraint's duplicate value error.

    Create a unique constraint:

    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

    primary key:

    an attribute or set of attributes that can be used to uniquely identify each 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 that is not null.

    Candidate keys:

    All the potential primary keys.

    Candidate keys that are not selected as the primary key are then known as ___

    alternate keys.

    When two tables relate to each other, one of them will act as the primary table and the other will act as the ___

    secondary table.

    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 ___

    foreign key.

    A foreign key then creates a relationship between two tables by linking the ___

    foreign key in one of the tables to the primary key of the referenced table.

    Create a foreign key constraint using the SSMS interface:

    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

    SQL Server will require all associated values with the constraint in the foreign key table to have ___

    corresponding values in the primary key table.

    Multiple values in the foreign key table can reference the ___

    same record in the primary key table.

    A composite primary key occurs when ___

    you define more than one column as your primary key.

    Imagine that you take the tables in a database and categorize them based on two data types:

    • Tables that define entities
    • Tables that relate entities

    It is in the tables that relate entities that ___

    the composite primary key plays an important role.

    System allows you to assign multiple products to multiple customers. This is a ___

    "many-to-many" relationship between the Customer and Products tables.

    A primary key is a set of columns in a table that ___

    uniquely identifies each row of data.

    Each table can have only ___ clustered index that defines how SQL Server will sort the data stored inside the table.

    one

    Data can only be sorted in one way, it simply is not possible to have ___ clustered indexes on the same table.

    two

    A clustered index is a ___ construct, unlike most indexes, which are logical or software-based.

    physical

    A clustered index is a physical construct, unlike most indexes, which are ___

    logical or software-based.

    One important feature of SQL Server is its automatic creation of a clustered index when the ___

    primary key is defined for a table.

    An index is an on-disk (or stored) structure associated entirely with a table or a view that ___

    increases the speed of data retrieval.

    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.

    B-tree

    You can only have one clustered index per table. Why?

    The data in the rows are sorted in only one order.

    A table with a clustered index is considered a ___

    clustered table

    When a table has no clustered index, the data rows are then stored in an unordered structure called a ___

    heap.

    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.

    Transact-SQL script statements

    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 ___

    SQL Server Management Studio.

    Create a non-clustered index using Sql Server Management Studio

    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.

    On a table ___ , you can add multiple table columns to the index key.

    with a non-clustered index

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature