The Data Definition Language or DDL is used to create databases and their components. In this course, you will learn how to create databases, tables, views, and stored procedures using DDL. The learning path describes how databases can be normalized according to specific guidelines to increase storage capacity and scalability. You'll also learn how to use indexes to make your databases more responsive. Finally, you'll learn about the core security considerations for SQL Server databases, how to assign permissions to database objects, and how to back up and restore databases. This course can be used as part of the preparation for Microsoft's exam 98-364, Database Fundamentals, which leads to a Microsoft Technical Associate or MTA certification.
Learning Objectives
Creating Database Components
- start the course
- create a new SQL Server database with its specific syntax and parameters and review its schema by using SQL Server Management Studio or SSMS and Data Definition Language or DDL
- use, alter, and drop SQL Server databases by using SQL Server Management Studio or SSMS and Data Definition Language or DDL
- create SQL Server tables and define their properties by using SQL Server Management Studio or SSMS and Data Definition Language or DDL
- alter and drop SQL Server tables by using SQL Server Management Studio or SSMS and Data Definition Language or DDL
- compare TRUNCATE to DELETE and use the TRUNCATE command to remove data from a SQL Server table
- describe the purpose and performance impact of views and create a view in a SQL Server database by using SQL Server Management Studio or SSMS graphical designer or by using Transact-SQL or T-SQL
Stored Procedure and Functions
- create and use stored procedures in SQL Server databases
- compare the stored functions with stored procedures and create and use a stored function within a SQL Server database
Normalization
- describe the purpose of normalizing relational databases
- describe the normalization guidelines for relational databases and define the first normal form or 1NF
- describe the normalization guidelines for relational databases and define the second normal form or 2NF
- describe the normalization guidelines for relational databases and define the third normal form or 3NF
Working with Indexes
- describe clustered and nonclustered indexes in the context of their use in a SQL Server database
- create clustered and nonclustered indexes in SQL Server databases
Database Security
- describe the main considerations for relational database security
- identify and describe SQL Server fixed and user-defined server and database roles, the guest account, and how to revoke it by using SQL Server Management Studio or SSMS
- create, assign, and manage permissions to a SQL Server login
- list the options available in SQL Server for database backups and restores
- use SQL Server to back up databases
- use SQL Server to restore databases
Practice: Table Creation and Normalization
- use the appropriate commands to create and normalize tables in a SQL Server database