Overview

Introduction

The Normalization, Primary, Foreign and Composite Keys module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

Learning Outcomes

In this module, you will complete the following exercises:

  • Exercise 1 - Normalizing Data
  • Exercise 2 - Creating Constraints
  • Exercise 3 - Creating Indexes

After completing this lab, you will be able to:

  • Normalize a database
  • Create constraints for Primary, Foreign and Composite Primary Keys
  • Create clustered and non-clustered indexes.

Exam Objectives

The following exam objective is covered in this lab:

  • 3.1 Understand normalization
  • 3.2 Understand primary, foreign and composite keys
  • 3.2 Understand indexes

Lab Duration

It will take approximately 20 minutes to complete this lab.

Exercise 1 - Normalizing Data

Normalization is the process of organizing the database design to minimize data duplication. A table with more columns is a feature of a non-normalized database. For example, consider a table named Faculty with the following fields

  • FacultyID
  • FacultyName
  • HireDate
  • CourseName
  • Gender
  • Salary

In this case, a faculty may take more than one course and hence the details of an individual faculty get repeated for the number of courses he /she takes. This duplication can be avoided by separating the details of the courses to a separate table say Courses and relating it to the table Faculty through a common field say FacultyID. This way, you can normalize or standardize the data and manage the data efficiently. As you see, the normalization process involves dividing the data into two or more tables and creating a relationship between the tables. The primary advantages of normalizing a database design involve:

  • Eliminating the redundancy of data
  • Ensuring the efficient and reliable management of data

Learning Outcomes

After completing this lab, you will be able to:

  • Normalize a database

Exercise 2 - Creating Constraints

Primary Key constraint is specified on a single column or multiple columns to enforce entity or table integrity. An entity integrity implies that all rows in a table have a distinctive identifier. Primary Key constraint can be defined when creating a new table or to an existing table. When a Primary Key constraint is applied to a column, the column can have no null values and no duplicate values. When specifying Primary Key Constraint, remember that one table can have only one primary key.

Foreign Key constraint can be defined when a new table is created or to an existing table. The column designated as foreign key in a table should reference a column that has a primary key constraint or unique constraint in another table or the same table. With this reference, the database engine ensures that the Foreign Key column contains only those values that exist in the referenced column. A table can have multiple Foreign Key constraints. When specifying a Foreign Key constraint, remember that the size of the column designated as foreign key cannot be altered after the constraint is applied.

Learning Outcomes

After completing this lab, you will be able to:

  • Create constraints for Primary, Foreign and Composite Primary Keys

Exercise 3 - Creating Indexes

Creating indexes on tables allow you quickly to gain access to data. An index holds the keys constructed from one or more columns in the table or view. SQL Server stores these keys in a structure and accesses the row or rows linked with the key values faster. The two types of indexes in SQL Server include clustered Index and nonclustered index.

Clustered Index: A clustered index sort the data in a table or a view based on their key values. You can create only one clustered index per table. When you create a primary key constraint on a table, SQL Server automatically creates a unique clustered index to enforce PRIMARY KEY constraint if a clustered index does not already exist.

Nonclustered Index: A nonclustered index stores its key values in a separate structure and each key value entry holds a pointer to the data row linked to the key value. When you create a UNIQUE constraint on a table, the SQL Server automatically creates a unique nonclustered index to enforce the UNIQUE CONSTRIANT unless you explicitly define a unique clustered index.

A good way to create indexes on tables is to create a clustered index first and then build any nonclustered indexes.

Learning Outcomes

After completing this lab, you will be able to:

  • Create clustered and non-clustered indexes.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.