PHP and MySQL - Chapter 6 Review questions

Why is normalization important?

By using normalization, you carefully eliminate redundancies and other problems that would undermine the integrity of your database.

What are the two types of keys?

The two types of keys are primary and foreign keys.

What are the three types of table relationships?

The three types of relationships between tables are: one-to-one, one-to-many, and many-to-many.

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

Many-to-many relationships are not desirable because they lead to data redundancy and integrity problems. To eliminate them in a properly designed database, intermediary tables are used. These 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 four types of indexes are:

INDEX
UNIQUE
FULLTEXT
PRIMARY KEY

The types of columns that should generally be indexed are:

Columns that are frequently used in the WHERE part of a query
Columns that are frequently used in the ORDER BY part of a query
Columns that are frequently used as the focal point of a JOIN

The types of columns that should not generally be indexed are:

Columns that allow for NULL values
Have a very limited range 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?

The two most commonly used MySQL table types are MyISAM and InnoDB. My localhost MySQL installation uses the InnoDB table type as the default. My web host MySQL installation uses MyISAM as the default.

What is a 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?

A character set is an encoding of characters stored in the database. Examples are: ascii, utf8, latin1, etc. A collation refers to the rules used for comparing characters in a set, similar to alphabetization, but takes other characters into account as well. The character set impacts the database by dictating what characters, and therefore, languages, are supported. The collation affects sorting, case sensitivity, how are accented characters sorted, are spaces counted or ignored.

In general, I am using the utf8 character set, and the utf8_general_ci collation.

What is UTC? How do you find the UTC time in MySQL? How do you convert from UTC to another time zone’s time?

UTC stands for Coordinated Universal Time, which is a common reference point from which all times in the world can be expressed as UTC plus or minus some hours and minutes.

There are functions in MySQL that you can use to find the UTC time: UTC_DATE() returns the current UTC date; UTC_TIME() returns the current UTC time; and UTC_TIMESTAMP() returns the current date and time.

To convert from UTC time to another time zone, use the CONVERT_TZ(dt, from, to) function. An example SQL function invocation to convert from a UTC time to Eastern time:


SELECT CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', 'America/New_York')

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

Foreign key constraints, which are only supported by the InnoDB table type, are used to set rules as to what should happen when a relationship between tables would be broken by deleting a value in one table that is used in another. It also dictates what should happen when an attempt is made to use a value in one table that does not exist in the other table.