LOGO For Immediate Assistance Call 855-211-9361

Open Enrollment

Fundamental of Relational Databases (800)

Price: $1,750.00

Call for availability 855-211-9361

This Capstone series will teach the fundamentals of database design, and provide an education in Structured Query Language (SQL), the standard language used to access database structure and data. Any modern Web application built today uses a back-end database, usually a SQL-based tool. Learn the fundamentals of SQL, along with an introduction to database design, which will allow you to work with any database when building your Web applications. We will be using MySQL as the basis for this class, a popular open-source product that supports ANSI SQL.

This Capstone series consists of these classes:

815 Introduction to SQL and Database Design
825 Advanced SQL and Database Programming


Students can choose to enroll in this 5-day course or choose to attend classes individually. By enrolling in the Capstone Series instead of the individual courses, students benefit from a $100 savings off the total tuition. Students who successfully complete the class exercises and/or Capstone project will receive the PB Tech Certification along with real-world sample code.

Course Overview

By the end of the class students will be able to:

  • Read and understand database model diagrams
  • Understand database vocabulary
  • Use Select, Insert, Update, and Delete to view and modify data
  • Query data simultaneously from multiple, related tables
  • Create, edit, and drop tables
  • Design efficient database structures
  • Ensure data integrity and quality
  • Enforce business logic and rules
  • Design sub-queries and correlated sub-queries
  • Use unions to combine queries into a single result set
  • Tune queries for performance
  • Apply views to shield users from underlying data complexity
  • Execute triggers and use stored procedures

Prerequisites

Students should have a strong knowledge of HTML, particularly HTML forms, and be comfortable with client-server applications such as the ones covered in PB Tech's Advanced HTML class. Experience using database-driven applications is helpful but not required.

Course Outline

  • Introduction: Databases and the Web

    • Overview
    • The File System
    • Databases and the ACID Test
  • An Overview of SQL

    • The Role of SQL
    • SQL History and Major DBMS vendors
  • Relational Databases

    • Queries and Recordsets
    • Exercise 1: Building a Simple SQL Query
  • More about the Parts of Relational Databases

    • Tables
    • Primary Keys, Foreign Keys, and Relationships
  • Retrieving Data with SELECT

    • Comparison Operators for the WHERE Clause
    • Apostrophes in Database Text
    • Calculated Columns: Doing Math in Select Statements
    • Select Distinct
    • Selecting All Columns
    • Exercise 2: Selecting Data from the Database
  • Data Manipulation: INSERT, UPDATE, DELETE

    • INSERT Statements
    • Exercise 3: Inserting Data into the Database
  • UPDATE Statements

    • Exercise 4: Updating Camp sales Data
  • DELETE Statements

    • Exercise 5: Deleting Camp sales Data
  • Data Retrieval: More About SELECT

    • ORDER BY
    • Exercise 6: Practicing with ORDER BY
    • Aggregate Functions
    • Exercise 7: Practicing with Aggregate Functions
    • Extended WHERE Conditions
    • Exercise 8: Practicing with Extended WHERE Clauses
  • Fine-Tuning Aggregate Functions

    • GROUP BY
    • The HAVING Clause
    • Exercise 9: Using GROUP BY and HAVING to Analyze Sales Data
  • Querying Data Across Tables

    • Basic Joins
    • Exercise 10: Selecting From Multiple Tables
  • Reusing Queries as Views

    • Exercise 11: Creating Views
  • Query Within A Query

    • Subqueries
    • Exercise 12: Subqueries vs. Joins
  • Database Definition SQL

    • CREATE TABLE
    • Exercise 13: Creating a Table for a Guest Book
  • Database Design

    • Data Modeling
    • Handling more than one phone number
    • Exercise 14: Adding Columns with ALTER TABLE
  • Establishing a Primary Key

    • Auto-numbered Columns
  • Relationships Between Tables

    • Normalization
    • Exercise 15: Normalizing the guestbook Table
    • Online Transaction Processing vs. Decision Support
  • Restructuring Tables

    • Renaming Tables or Columns
    • Adding or Deleting Columns
    • Exercise 16: Normalizing the Camp sales Database
  • Data Integrity: Controlling What Goes Into a Database

    • Default values
    • Exercise 17: Setting Default Values for our Guestbook
    • Check Constraints
    • Exercise 18: Checking Input
    • Creating Foreign Keys
    • Exercise 19: Defining Primary & Foreign Keys
  • Many-to-Many Relationships

    • Tying It All Together: Creating The Movies Database
  • Introduction to Transactions

  • Appendix A: The Camp sales Database

    • Diagram of the relationships among the tables
    • Table Data Types
    • Sample Data (Pre-Exercises)
  • Major SQL Statements

  • Advanced SQL and Database Design

  • Entity-Relationship Diagrams

  • Text Functions

    • Matching Patterns with LIKE
    • Text manipulation functions
    • Exercise 1: Text Manipulation Functions
    • Dates in SQL Server
    • Exercise 2: Date Handling
    • Conversion Functions
    • Using Union to Combine Queries
    • Exercise 3: Using Union
    • Transact-SQL's CASE statement
  • A Review of Aggregate Functions

    • GROUP BY
    • HAVING
    • WITH CUBE and WITH ROLLUP
    • COMPUTE BY
    • Exercise 4: Fancy Aggregate Functions
  • Querying Data Across Tables

    • Joins the ANSI-92 Way
    • Outer Joins
    • Exercise 5: Using an Outer Join to Show Sales Information
    • Self Joins
    • Exercise 6: Self-join and an organization chart
  • Subqueries

    • Exercise 7: Subqueries
    • Correlated Subqueries
    • Exercise 8: Correlated Subqueries
    • Performance Tuning
    • Connecting to Data in Other Databases
  • Beginning Transact-SQL Programming

    • Introduction to Stored Procedures
    • Exercise 9: A Simple Stored Procedure
    • Transactions in SQL Server
    • Exercise 10: Using a Stored Procedure to Calculate YTD Sales
    • Triggers
    • Exercise 11: Triggers
  • The Books Database

    • Entity-Relationship Diagrams
    • Create Table Statements