Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
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.
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.
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.
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 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."
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.
Without locks, a change made by one transaction could be ___
overwritten by another transaction that executes at the same time.
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.
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 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
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.
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.
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.
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.