Why is normalization important?
Normalization is used to eliminate redundancies and other problems that would undermine the integrity of your database.
What are the two types of keys?
Primary key - a unique identifier, Must always have a value (cannot be NULL), has a value that never changes and has a unique value for each record in the table.
Foreign key - the representation in Table B of the primary key in Table A.
What are the three types of table relationships?
How do you fix the problem of a many-to-many relationship between two tables?
Use intermediary tables that break down one many-to-many relationship into two one-to-many relationships
What are the four types of indexes? What general types of columns should be indexed? What general types of columns should not be indexed?
The 4 types of indexes are INDEX (the standard), UNIQUE (each row has a unique value for that column), FULLTEXT (for performing FULLTEXT searches) and PRIMARY KEY
Indexes are best used on columns that are frequently used in the WHERE clause, that are frequently used in the ORDER BY clause, or that are,frequently used as the focal point of a JOIN
Do not index columns that allow for NULL values or have a limited number of values (such as Y/N or 1/0)
What are the two most common MySQL table types? What is the default table type for your MySQL installation?
MyISAM and InnoDB are the two most common MySQL table types. Table types can be mixed within the same MySQL database. CREATE TABLE ( ) ENGINE = type
The default table type for MySQL as of version 5.5.5 is InnoDB
What is character set? What is a collation? What impact does the character set have on the database? What impact does the collation have? What character set and collation are you using?
Character set is an encoding which is a series of numbers and letters that dictate which characters and languages are supported. Use SHOW CHARACTER SET to see which encodings your version of MySQL supports. Character set of database should match that of your PHP scripts.
Collation refers to the rules used for comparing characters in a set. Like alphabetization but takes into account numbers, spaces and other characters. Use SHOW COLLATION LIKE 'charset%'
The character set dictates which characters can be stored in the database
Collation is tied to the character set being used, reflecting both the kinds of characters present in that language and the cultural habits of people who generally use the language.
Server charset: UTF-8 Unicode (utf8) Collation: utf8_genreral_ci
What is UTC? How do you find the UTC time in MySQL? How do you convert from UTC to another zones time?
UTC is Coordinated Universal Time. Provides a common point of origin from which all times in the world can be expressed as UTC plus or minus some hours and minutes
UTC_TIME() returns UTC time, UTC_DATE() returns UTC date
CONVERT_TZ(dt, from, to) changes a date and time from one time zone to another
What are foreign key constraints? What table type supports foreign key constraints?
Foreign key constraints are where the foreign key in Table B relates to the primary key in Table A. Foreign key constraints set rules as to what should happen when a break would occur, including preventing that break