Back to Search Results

Training Course Summary:

This relational database design class develops relational database design skills and techniques. Practical methodologies such as E-R diagrams and normalization forms are emphasized. Attention is placed on designing for data integrity and efficiency at the same time. During hands on lab sessions students are required to design multiple database models from business requirements and specifications. Through hands on experience using SQL programming code to create, populate and manage databases, students will experience the necessity of proper design methods and gain an in depth understanding of the link between design, creation, and utilization. The impact of alternative designs on maintainability and database performance is emphasized.

The information in this course is also contained in the SQL Server Administration and SQL Server Developer courses. Students requiring those additional skills should register for those courses instead.

Pre-Requisites:

General computer knowledge. Familiarity with relational database concepts is helpful but not required.

Training Course Overview/Content:

Developing Relational Databases
Relational Database Fundamentals
Database Development Methodology Overview
Transforming to Physical Design

Migrating Entities to Tables
Selecting Primary Keys
Defining Columns
Enforcing Relationships with Foreign Keys
Implementing Exclusive and Recursive Relationships
Implementing Supertypes / Subtypes
Enforcing Business Rules
NOT NULL, UNIQUE and CHECK Constraints
Assigning DEFAULT Values
DELETE and UPDATE Rules
Historical and Control Tables
Database VIEWs
Estimating Table Sizes
Designing for Security: Users and Roles
Building a Logical Data Model
Understanding Data Analysis and Modeling
Identifying Entities and Attributes
Isolating Keys
Relationships between Entities (One-to-One, One-to-Many, Many-to-Many)
Creating Entity-Relationship Diagrams
Complex Modeling Issues
Exclusive and Recursive Relationships
Supertype / Subtype Entities
Normalization as a Design Technique
Progressive Steps to Normalization (First, Second, and Third Normal Form)
Higher Levels of Normalization
Using Normalization to Build an E-R Diagram

Designing for Performance
Transactional vs. Query Tables
Indexing
Denormalization
Row Column Layout
Table Partitioning
Addressing Data Contention
Storage Considerations
Other Design Issues
Selecting Database or Database Management System Software
Operating Environment Considerations
Referential Integrity Enforcement and Implications

Constructing the Database
Creating the Database Objects
The Schema
Creating Tables, Indexes, Constraints and Views
Using SYNONYMs
Using Table and Column COMMENTs
Creating ROLEs and GRANTing Privileges
Managing Database Objects
Dropping Tables, Indexes and Views
Revoking Privileges
ALTERing Tables, Indexes and Views
Enabling and Disabling Constraints
Backup and Recovery Planning
Populating the Database
Creating and Manipulating Data
INSERT
UPDATE
DELETE
TRUNCATE
Controlling transactions
COMMIT
ROLLBACK
SAVEPOINT
Data Loading Utilities

Basic SQL Queries
Displaying Table Structures
SELECTing Column Data FROM a Table
SELECT DISTINCT values
COUNTing rows
Restricting Rows Using the WHERE Clause
Using Operators
Logical
Mathematical
Comparison
Concatenation
Sorting Results Using ORDER BY
Joining Multiple tables
Manipulating and Summarizing Results
Using Row Functions
Using Column Functions
Handling NULLs
Aggregating Results Using GROUP BY
Restricting Groups with the HAVING Clause
Advanced Query Techniques
Outer Joins
Set processing: UNION, MINUS and INTERSECT
Subqueries
Tips for Developing Complex SQL Queries