Chapter 6-Database Design: Review and Pursue

• Why is normalization important?

Normalization reduces redundancies and other problems that would undermine the integrity of your database.

• What are the two types of keys?

The primary key which is a unique identifier which must always have a value which never changes and has a unique value for each record in a table. The other is the foreign key which is a primary key that is linked to another table.

• What are the three types of table relationships?

One to one: One item in column A can relate to only one item in column B One to many: One item in column A can relate to many items in column B Many to many: Multiple items in column A can relate to multiple items in column B

• How do you fix the problem of a many-to-many relationship between two tables?

By normalizing your database.

• What are the four types of indexes? What general types of columns should be indexed? What general types of columns should not be indexed?

INDEX UNIQUE FULLTEXT PRIMARY KEY The type to columns that should be indexed are columns that are frequently used in the WHERE part of the query. Columns that are frequently used in a ORDER BY part of query and columns that are frequently used as the focal point of a JOIN query.

• What are the two most common MySQL table types? What is the default table type for your MySQL installation?

The two most common table types are MylSAM (which is for most application handling SELECT and INSERT very quickly but are vulnerable to corruption and data loss should a crash occur) and InnoDB (which per updates nicely, but is generally slower and requires more disk space). You default table type really depends on the version of MySQL you are using. Prior to version 5.5.5 MylSAM was the default table type but as of version 5.5.5 InnoDB as become the default type.

• 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 a collation are you using?

A character set is another term for encoding. Encoding is a series of numbers and letters which dictates what characters and languages are supported.

Collations are rules for comparing characters in a set and ties to the character set being used. Collation takes into consideration the language that is being used reflecting the type of characters being used as well as the cultural habits of those people. I’m using Charset utf8 and collation utf8_general_ci.

• What is UTC? How do you find the UTC time in MySQL? How do you convert from UTC to another zones time?

UTC stands for Coordinated Universal Time which is considered time zone neutral.

To convert from UTC to another time zone use CONVERT_TZ() using the arguments dt, from, . The first argument is the date and time, and send and third arguments are named time zones. In order to use this function, the list of time zones must already be stored in MySQL.

• What are foreign key constraints? What table type supports foreign key constraints?

Foreign key constraints are a way of creating a relationship between two tables which in case of break between the tables occurring, the foreign key restraint will set rules as to what would happen if this break occurs or to prevent this break from happening all together.

InnoDB is the table type that supports foreign keys.