RELATIONAL DATABASE DESIGN TRAINING TOOLS AND TECHNIQUES

Course Outline

• An Overview of DBMS Technology
• How data is accessed, organized, 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 modeling

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

Logical modeling

• Apply data types to entity attributes
• Describing relationships: one-to-one, one-to-many, many-to-many
• Building recursive relationships
• Understanding different modeling notations
• Normalization Techniques
• Avoiding update anomalies
• Identifying functional dependencies
• Applying rules for normalization
• Normalizing 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 practices to database design

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