Relational Database Design Concepts
Duration: 3 Days (Face-to-Face or Remote-Live), or 21 hours of coursework with personal facilitation over a four week span (On-Demand)
US Price: $1695 (Face-to-Face or Remote-Live), or $1495 (On-Demand)
Delivery Options: Attend face-to-face in the classroom, remote-live or via on-demand training
Registration: Click here to view upcoming schedules and register for face-to-face sessions, click here to register for live remote attendance sessions, or click here to register for on-demand training with a start date that is convenient for you.
View the course schedule
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 relational tables, 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.
General computer knowledge. Familiarity with relational database concepts is helpful but not required.
Developing Relational Databases
- Overview of Relational Database Concepts
- Relational Databases and Relational Database Management Systems
- Database Development Methodology Overview
- Conceptual, Logical and Physical Data Models
|
Building a Logical Data
Model
- Identifying Entities and Attributes
- Relationships Between Entities (One-to-One, One-to-Many, Many-to-Many)
- Creating Entity-Relationship Diagrams
- Normalization as a Design Technique
- Progressive Steps to Normalization (First, Second, and Third Normal
Form)
|
Transforming to Physical Design
- Migrating Entities to Tables
- Selecting Primary Keys
- Defining Columns
- Enforcing Relationships with Foreign Keys
- Enforcing Business Rules with Constraints
|
Constructing the Database Using DDL
- CREATE DATABASE
- Creating Objects with CREATE
- Creating Tables, Indexes, Stored Procedures, Triggers and Views
- Modifying Objects with ALTER
- Adding Columns to a Table
- Modifying Parameters to a Stored Procedure
- Adding Constraints to a Column
- Modifying the Timing of a Trigger
- Changing the Body of Stored Procedures and Views
- Removing Objects with DROP
- Dropping Tables, Indexes, Constraints and Views
|
Manipulating Table Data Using SQL's Data Manipulation Language (DML)
- Inserting Data into Tables
- Updating Existing Data
- Deleting Records
- Truncating Tables
- Implementing Data Integrity with Transactions
- Beginning Explicit Transactions
- Committing Transactions
- Rolling Back Transactions
- Defining Save Points
|
Writing Basic SQL Queries
- Displaying Table Structures
- Retrieving Column Data From a Table or View
- Selecting Unique Values
- Obtaining a Row Count
- Filtering Rows Using the WHERE Clause
- Sorting Results Using ORDER BY
- Joining Multiple Tables
- Using Column and Table Aliases
|
Manipulating Query Results
- Using Row Functions
- Character
- Numeric
- Date and Time
- Data Conversion (CAST and CONVERT)
- Using the CASE Function
- Handling Null Values
Working with Table Expressions
- Overview of Table Expressions
- Working with Views
- Using Derived Tables
- Common Table Expressions
- Table-Valued Functions
|
Advanced Query Techniques
- Inner Joins
- Outer Joins (Left, Right, Full)
- Performing Self-Joins
- Subqueries
- Using the EXISTS Operator
- Tips for Developing Complex SQL Queries
- Using Aggregate Functions
- Performing Set Operations
- UNION
- INTERSECT
- EXCEPT/MINUS
- Aggregating Results Using GROUP BY
- Restricting Groups with the HAVING Clause
- Creating Temporary Tables
|
Related Training
