Relational Database Design, Tools and Techniques

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

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
  

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
  

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

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