+1 978.394.0597
Software Architects • Analysts • Educators
The Cathris Group > Home > Education > Business & Systems Analysis > BA501
Relational Database Design & SQL Programming
Databases are at the heart of most business applications, corporate websites, and e-commerce
platforms. In this course, participants will learn how to design relational databases using data
modeling and how to expand existing database by modifying schemas. Participants will learn how to
use SQL to access and update the data in the database. SQL programming concepts including
selection, projection, and grouping will be practices through numerous integrated and realistic
workshops. In addition, participants will learn how to leverage stored procedures, perform multi-table
access, and build transactions.
Phone: (978) 394-0597 | E-Mail: info@cathris.com
Site Map | Web Site Feedback | ©The Cathris Group, 2008. All Right Reserved.
Essential Concepts
  • Architecture of databases
  • Relational data model
  • Database clients and servers
  • Role of SQL
  • OLTP vs. OLAP database structures
  • Role of indexing

Relational Database Design
  • Concepts: Tables, Keys, Relations, Integrity
  • Identifying entities and attributes
  • Gathering requirements for databases
  • Building an Entity-Relationship Model
  • Defining tables from entities
  • Discovering attributes
  • Simple vs. composite vs. multi-valued attributes
  • Attributes domains
  • Empty values: NULL vs empty vs zero vs blank
  • Keys: primary, alternate, surrogate, foreign
  • Cardinalities and optionality
  • One-to-Many and Many-to-Many relationships
  • Creating association tables
  • Data normalization
  • Data modeling with tools (Visio, EA, etc.)

Data Access with SQL
  • The structure of SQL statements
  • SQL standards and variations
  • Creating tables with CREATE TABLE
  • Cascading deletes and integrity management
  • Queries with SELECT
  • Selection and column specification
  • Simple joins and multi-table access
  • Selection with WHERE clauses
  • Range tests
  • Set membership testing
  • NULL value testing
  • Compound conditions
  • Pattern matching
  • Aggregate functions: MIN, MAX, SUM, AVG
  • Ordering results using ORDER BY

Data Updates with SQL
  • Removing rows with DELETE
  • Adding rows with INSERT
  • Modifying rows with UPDATE
  • SQL standards and variations
 Advanced SQL Queries
  • Complex joins: inner vs outer vs natural joins
  • Grouping data with GROUP BY
  • Restricting groups with HAVING
  • Simple subqueries
  • Complex subqueries with GROUP BY,
    HAVING, and WHERE
  • Unions
  • Counting rows using COUNT
  • Managing unique rows with UNIQUE and
    DISTINCT
  • Existence testing with EXISTS
  • Quantified tests with ANY and ALL

Leveraging Views
  • The benefit of Views
  • Using views for security: authorization
  • Using views for query simplification
  • Decomposing subqueries with Views
  • View materialization concepts
  • Creating and dropping views

Stored Procedures and Triggers
  • Role of stored procedures and triggers
  • Creating stored procedures and triggers
  • SQL Server TSQL syntax
  • Variables
  • Common TSQL functions
  • Procedure parameters and arguments
  • Returning results
  • Transactions

Metadata and Mining
  • Creating indexes
  • Indexes and efficiency
  • Drawbacks and dangers of indexing
  • Clustering
  • Query optimization techniques
"Dr. Schedlbauer was the reason
I took this course -- his style &
past work experience were very
conducive to learning -- would
gladly take another course
taught by him."
Testimonials
Related Courses
Course Objectives
  • understand relational database design
  • design efficient and normalized relational databases using entity-relationship modeling
  • access data using SQL
  • practice WHERE, GROUP BY, HAVING clauses for SQL SELECT statements
  • update data through UPDATE, DELETE, and INSERT statements
  • learn how to create stored procedures in Microsoft SQL Server 2005 and 2008
  • calculate aggregrates using SUM, MAX, MIN, AVERAGE, among others
  • construct queries using sub-queries
Course Schedule
This course is only offered
for custom on-site delivery.

Please contact us to
schedule this course for
your team at a location of
your choice.
Course Contents
Course Details
Suggested Length
4 Days

Audience
Business Analysts, Project
Managers, Software
Developers

Cost
Please call for a quote.
"Dr. Schedlbauer is an excellent
instructor! His energy and
knowledge are fantastic. He was
able to explain practical uses
that you don't get from reading a
book. "
Benefits and Takeaways
  • design well-structured and efficient databases
  • know how to efficiently retrieve data for ad-hoc reporting
  • build complex queries using aggregation and sub-queries
  • define stored procedures to manage transaction and compound table updates