Relational Database Concepts and Design

Course Outline

• An Overview of Hierarchical, Network, Relational and Object Databases
• Differences between Data Definition Languages (DDL) and Data Manipulation Languages (DML)

• E/R Terms and Definitions
• Problems Solving using E/R

• ANSI-3 Schema Architecture
  • Physical, Logical/Conceptual and View Levels
• Key Definition
  • Superkey, Candidate key and Primary key
• Schemes and Instances
• Attributes and Domains
• Relational Algebra
  • The Fundamental Operators - Select, Project, Join, Cartesian Product and Set Difference
  • Additional Useful Operators - Set Intersection, Theta-Join, Natural Join, Outer & Inner Joins Division, Rename
  • Problem Solving using Relational Algebra
• Views and Conceptual Tables
  • The View Update Problem
  • Advantages and Disadvantages of Views

• SELECT .. FROM .. WHERE
• Showing the resolution of SQL Queries from Relational Algebra
• SQL Expressions - like, exists, in, all, Max, min, avg.
• Groupby, Orderby and Having clauses
• SubQueries
  • Self-Join, Correlated SubQueries, and the CASE Statement
• The SQL-92 Standard
• Problem Solving in SQL - Basic and Advanced

• Authorization and Authentication
• Ownership and Privileges
• Authorization Grant Graph and GRANT Clauses
• Encryption
• Establishment of a Security Policy

• Domain and Referential Integrity
• Expression of Business Rules
• Functional Dependencies and Database Design Theory

• Normalization Anomalies
• First, Second, Third,
• Boyce-Codd Normal Form and Dependency Preservation
• Multivalued dependencies and Fourth Normal Form
• Project-Join or Fifth Normal Form
• Ron Fagin's Domain/Key Normal Form
• Problem Solving in Normalization theory

• File Organization
• Buffer Management
• Indexing using B-Trees
• Indexing using Hash Structures

• Database Statistics
  • # of rows, Size of Rows and Distinction #s
• Heuristic and Cost-Based Optimization
• Join Resolution
• Query Plan Evaluation
• Problem Solving for query Optimization

• Recovery from software failure
  • Log with Deferred Update
  • Log with Immediate Update
  • Shadow Paging
• Recovery from media failures
  • RAID
• Checkpointing- Commit-Consistent, Cache-Consistent and Fuzzy

• Serializability theory
• Definition of a Transaction
• ACID Properties of a Transaction
• Transaction Categorization - Flat, Nested and those with Savepoiints
• 2-phase locking
• Levels of Isolation
• Lock Modes and Lock Compatibility Matrices
  • R, W, X and Intention Locks
• Rosencrantz's Wound-Wait & Wait-Die Algorithms
• Multiversion Concurrency Control
• Optimistic Concurrency Control
• Problem Solving in Transaction Management

• Database Planning & Information Engineering
• Database Analysis
• Conceptual Design
• Logical Design
• Physical Design

The role of the Database Administrator

• Logical Design
• Physical design
• Performance Evaluation

Back to Course Description  Onsite Training  Class Schedule