Duration: 3 days
Price: Contact us for pricing.
Description: 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.
Prerequisites: General computer knowledge. Familiarity with relational database concepts is helpful but not required.
Overview of Topics Covered:
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
|
