Data Design & mySQL Study Guide

This study guide is intended to help you prepare for the data design and mySQL exam questions. The following questions will not appear on the official exams, but have been designed to highlight key concepts that will covered and approximate the level of difficulty of the exam questions.

Please bear in mind that all Deep Dive Coding Assessment Exams are open book, open internet, open PHP parser, and open Google.

Data Design

  • Know where to put foreign keys for all the different relationships:
    • 1-to-1: the foreign key can go in either table.
    • 1-to-n: the foreign key goes in the n table.
    • n-to-m: both foreign keys go into a weak entity, known as an intersection table.
  • Know the similarities and differences between a primary key, a unique index, and an index.
  • Know when and when not to index a field:
    • Create indexes when directly searchable by end users.
    • Create indexes on fields involved in a join.
    • mySQL always requires foreign keys to be indexed.
    • Don't index fields not part of a foreign key or searched on.

mySQL

  • Know how to write an INSERT statement given a table's schema.
  • Know how to write a SELECT statement using simple string criteria.
  • Know whether it's a good idea or not to UPDATE a primary key.