LearnKey Training

Database Design Fundamentals Course (1D0-541)

Database Design Fundamentals Course (1D0-541)

Back to Product Page


Database Design Fundamentals Course (1D0-541)

4 Sessions -
9 Hours of Interactive Training

The Database Design Fundamentals course teaches students how to plan and design relational databases. You will learn about the theory behind relational databases, relational database nomenclature, and relational algebra. The course includes sections on Structured Query Language (SQL) and optimizing databases through normalization. This course covers all of the objectives necessary to pass the CIW Database Design Methodology exam.

Benefits
  • Identify relational data modeling schemas, characteristics, and manipulation
  • Apply normalization techniques and processes
  • Identify SQL commands and syntax
  • Define and describe the use of relational algebra in order to create new relationships from existing database relations
  • Identify elements of database security

About The Author
Wayne Snyder is recognized worldwide as a SQL Server expert and Microsoft Most Valued Professional (MVP), with over 25 years of experience in project management, database administration, software design, performance measurement and capacity planning. He is a sought out consultant, trainer, writer and speaker, and produces a series of web-based seminars on SQL Server 2005. Wayne has edited many SQL Server books, contributes monthly to SQL Server Magazine and is a Managing Consultant for Mariner, a Business Intelligence Company.

Session 1

Section A: Introduction to Databases

  • Databases
  • Flat File Databases
  • Relational Databases
  • RDBMS Benefits
  • Relational Database Management
  • RDBMS Disadvantages

Section B: Relational Database Fundamentals

  • History
  • Application Components
  • Two-Tier Client/Server
  • Browser Market Share
  • Three-Tier Client/Server
  • Relational/SQL Model Terminology
  • Representative Tables
  • Entity Relationships
  • Primary Key
  • Data Models
  • Entity-Relationship Model
  • Design Language
  • Strong/Weak Entities

Section C: Relationships

  • Relationship Degrees
  • Binary Relationships
  • Relational Integrity
  • Referential Integrity Completeness
  • Many-Many Relationship
  • Foreign Key Constraint Actions
  • Data Integrity

Section D: Database Languages and Dictionaries

  • Database Languages
  • Structured Query Language
  • Data Definition Language
  • Data Manipulation Language
  • Data Control Language
  • Data Dictionaries

Section E: Database Planning

  • Planning a Database
  • Database Project Life Cycle
  • Database Design/Requirements Document
  • Choosing a DBMS
  • Selecting an Application Interface
  • Application Interface

Session 2

Section A: Conceptual Design Phase

  • Database Design
  • Identify Entities
  • Attributes/Domains
  • Sample Data Types
  • Identify Relationships
  • Candidate/Primary Keys
  • Entity-Relationship Diagrams
  • IE Method
  • Chen Data Model
  • Problems/Anomalies
  • Row Insert Anomalies
  • Delete Anomaly
  • Update Anomaly

Section B: Normalization Techniques Part I

  • Database Normalization
  • Normal Forms
  • First Normal Form Rules
  • First Normal Form Example
  • First Normal Form Anomalies
  • Second Normal Form
  • Second Normal Form Example
  • Second Normal Form Rules
  • Second Normal Form Anomalies

Section C: Normalization Techniques Part II

  • Third Normal Form Rules
  • Boyce-Codd Normal Form
  • BCNF Primary Key
  • Prime Attributes
  • Third Normal Form Revisited
  • BCNF Example
  • BCNF Determinates
  • Data Normalization

Section D: Logical Database Design

  • Logical Data Model
  • Resolve M:N Relationships
  • Complex Relationships
  • Recursive Relationships
  • Attributes in a Relationship
  • Remove Redundant Relationships
  • Creating a Logical Model
  • Example of DDL
  • Validating the Logical Model
  • Creating an Enterprise Data Model

Section E: Physical Database Design

  • DBMS
  • DDL Example 1
  • DDL Example 2
  • Enterprise Constraints
  • Check Constraints
  • Column Level Constraints
  • Foreign Key Constraints
  • Using Secondary Indexes

Session 3

Section A: Physical Database Design Considerations

  • Denormalization
  • Denormalize Foreign Keys
  • Denormalize for Aggregation
  • Creating User Views
  • Database Access Rules
  • Grant/Revoke Statement

Section B: Introduction to SQL

  • Structured Query Language
  • Accuracy Scale
  • Data Types
  • Data Definition Language
  • Create Schema
  • Alter Table Command
  • Alter/Drop Command
  • Alter/Drop Domain

Section C: Insert, Delete, and Update Data

  • Insert
  • Inserting Data
  • Delete
  • Referential Integrity
  • Update
  • Updating Data
  • Multiple Tables

Section D: Working with Select Statements

  • Select
  • Using Select
  • Column Aliases
  • Distinct

Section E: Filtering with Comparison Operators

  • WHERE
  • Comparison Operators
  • Multiple Boolean Operators
  • WHERE Clause Values
  • IN Operator

Session 4

Section A: Filtering with LIKE

  • LIKE
  • LIKE Clause in a Sequence
  • Underscore Wildcard
  • Square Bracket Wildcard
  • Null Handling

Section B: Sorting Result Sets

  • ORDER BY
  • Multiple ORDER BY
  • Retrieving Data by Relations
  • Relating Tables

Section C: Data Control Language

  • DCL
  • Grant
  • Revoke

Section D: Relational Algebra

  • RA Defined
  • Selection
  • Projection
  • Cartesian Product
  • Unions
  • Union with Projection
  • Difference
  • Intersection
  • Creating Unions/Projections

Section E: Composite Joins

  • Joins/Theta-Join
  • Equi-Join
  • Natural Join
  • Outer Join/Semi Join
  • Inner Join
  • Right and Left Outer Join

Section F: Transactions

  • Transaction Control
  • ACID Properties
  • Lost Updates
  • Dirty Reads
  • Non-Repeatable Reads
  • Phantoms
  • Serializability
  • Concurrency Control Methods
  • Deadlock Error
  • Timestamps
  • Transaction Examples
  • Concurrency Control Review
  • Optimistic Concurrency Control

Section G: Database Security

  • Security
  • SQL Injection
  • Security Techniques