Relational Database Design Training: Tools and Techniques

Relational Database Design Course Outline

• An Overview of DBMS Technology

  • How data is accessed, organised, and stored
  • Relational and NoSQL database comparisons
  • Roles involved in database design, development, and administration
  • The database development process

• How a Relational DBMS Works

  Relational technology fundamentals

  • Terminology and definitions
  • Tables, attributes and relationships
  • Primary and foreign keys
  • Manipulating data: selection, projection, join, union, intersection, difference

  Components of a relational DBMS

  • An integrated, active data dictionary
  • Databases, accounts, and schemas

• Designing Data Models

  A step-by-step approach and techniques

  • Extracting core business information from requirements
  • Generating conceptual data entities
  • Transforming a conceptual model into a logical one
  • Building a physical database from a logical model
  • Building database documentation

  Conceptual modelling

  • Capturing core entities
  • Identifying entity attributes
  • Creating unique identifiers
  • Graphically representing a conceptual model

  Logical modelling

  • Apply data types to entity attributes
  • Describing relationships: one-to-one, one-to-many, many-to-many
  • Building recursive relationships
  • Understanding different modelling notations

• Normalization Techniques

  • Avoiding update anomalies
  • Identifying functional dependencies
  • Applying rules for normalization
  • Normalising multi-valued attributes

• Building a Relational Database

  Physical database design

  • Implementing keys from unique identifiers
  • Building foreign keys from relationships
  • Enforcing business rules with check constraints

  Working with design software

  • Generating the DDL to build the database
  • Reverse engineering to capture the design of an existing database

  Applying best practises to database design

  • Natural versus surrogate keys
  • Exploring lookup table deployment options
  • Examine vertical and horizontal data partitioning strategies
  • Using record timestamps