Designing a Relational Database Schema

Introduction

The Designing a Relational Database Schema module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with Table Designer
• Managing tables
• Working with schemas
• Managing schemas
• Working with normalization

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Five exam objectives are covered in this lab.

• Demonstrate how to create a table using the Table Designer. Rename the table. Add new columns to it with appropriate data types
• Demonstrate how to create a table using Transact-SQL statement
• Demonstrate how to create a schema using SQL Server Management Studio
• Demonstrate how to create a schema using Transact-SQL statement
• Review the table created in the first task to ensure it is normalized

Exercise 1 - Working with Table Designer

A Table Designer is a visual database tool that helps in designing and viewing the tables in the database. The Table Designer can be used to create, modify, or even delete tables or other database objects such as columns, relationships, indexes, keys and constraints. It allows you to define the primary constraints for unique columns in a table.

The primary key constraint in a table helps identify each record in the table uniquely. It allows only unique values and non-NULL values in the table columns.

In this exercise, you will work with Table Designer.

Exercise 2 - Managing Tables

You can define a primary key and foreign key constraints for fields to establish a link between two or more tables. The foreign key constraint does not allow invalid values to be entered in the field. In other words, the values that are entered in the foreign key field should be present in the primary key field of the source table.

In this exercise, you will learn to create a new table using simple Transact-SQL statement.

Exercise 3 - Working with Schemas

Schema refers to a collection of database objects such as tables, indexes, and so on that belong to a particular database user also known as the schema owner. The schema owner can create multiple database objects within the schema depending on the user privileges assigned. The schema owner can create, delete, or manipulate the schemas.

In this exercise, you will learn to create a schema using SQL Server Management Studio.

Exercise 4 - Managing Schemas

You can also create schemas using simple Transact-SQL statements.

In this exercise, you will learn to create schemas using Transact-SQL statements.

Exercise 5 - Working with Normalization

Normalization is the process to eliminate duplicate and redundant data in the database. It helps to logically reorganize and refine the data in the database.

The normalization process is divided into different forms. These are as follows:

• First Normal Form (FNF)
• Second Normal Form (SNF)
• Third Normal Form (TNF)
• Boyce and Codd Normal Form (BCNF)

In the First Normal Form (FNF), related data must be stored in individual tables. The related data must have a primary key. Repeating groups must be removed from tables. A single table must not have multiple fields to store similar data.

• In this exercise, you will learn to work with the FNF process of normalization.