Review

Q. Why is normalization important?

Normalization reduces redundancy and increases efficiency, integrity and organization in databases.

Q. What are the two types of keys?

Primary keys: a unique identifier within a table in a database.

Foreign keys: an identifier used in a table that is a primary key from another table within the database.

Q. What are the three types of table relationships?

One-to-One, One-to-Many, and Many-to-Many. The third type of data relationship is common in the real world but they are to be avoided if possible because they decrease the integrity of the database and cause data redundancy problems.

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

Instead of having Many-to-Many relationships, properly designed databases use intermediary tables that break down one Many-to-Many relationship into two One-to-Many relationships.

Q. 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: (1) PRIMARY KEY index, (2) UNIQUE index, (3) FULLTEXT index, and (4) Standard index.

Indexes are best used on columns: (a) that are frequently used in the WHERE part of the query, (b) that are frequently used in an ORDER BY part of a query, and (c) that are frequently used as the focal point of a JOIN.

You should not index columns that : (a) Allow for NULL values, and (b) have a very limited range of values.

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

The most common MySQL table types (storage engines) are: (1) MyISAM and (2) InnoDB. The default table type for my MySQL installation (v5.5.32) is MyISAM.

Q. 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 the list of encodings for each character/letter in the alphabet of each language supported by the database. The character set determines what characters, and therefore which languages can be displayed. The collation affects the way that characters wthin the set are compared to each other, with consideration to different versions of letters (upper- or lowercase, or accented), numbers, spaces and other characters.

The encodings supported by this installation of MySQL are:

SQL result
Host: localhost
Database: studiojc_percolate
Generation Time: Oct 26, 2013 at 09:34 PM
Generated by: phpMyAdmin 4.0.5 / MySQL 5.5.32-cll-lve
SQL query: show character set;
Rows: 39
Charset	Description	Default collation	Maxlen
big5	Big5 Traditional Chinese	big5_chinese_ci	2
dec8	DEC West European	dec8_swedish_ci	1
cp850	DOS West European	cp850_general_ci	1
hp8	HP West European	hp8_english_ci	1
koi8r	KOI8-R Relcom Russian	koi8r_general_ci	1
latin1	cp1252 West European	latin1_swedish_ci	1
latin2	ISO 8859-2 Central European	latin2_general_ci	1
swe7	7bit Swedish	swe7_swedish_ci	1
ascii	US ASCII	ascii_general_ci	1
ujis	EUC-JP Japanese	ujis_japanese_ci	3
sjis	Shift-JIS Japanese	sjis_japanese_ci	2
hebrew	ISO 8859-8 Hebrew	hebrew_general_ci	1
tis620	TIS620 Thai	tis620_thai_ci	1
euckr	EUC-KR Korean	euckr_korean_ci	2
koi8u	KOI8-U Ukrainian	koi8u_general_ci	1
gb2312	GB2312 Simplified Chinese	gb2312_chinese_ci	2
greek	ISO 8859-7 Greek	greek_general_ci	1
cp1250	Windows Central European	cp1250_general_ci	1
gbk	GBK Simplified Chinese	gbk_chinese_ci	2
latin5	ISO 8859-9 Turkish	latin5_turkish_ci	1
armscii8	ARMSCII-8 Armenian	armscii8_general_ci	1
utf8	UTF-8 Unicode	utf8_general_ci	3
ucs2	UCS-2 Unicode	ucs2_general_ci	2
cp866	DOS Russian	cp866_general_ci	1
keybcs2	DOS Kamenicky Czech-Slovak	keybcs2_general_ci	1
macce	Mac Central European	macce_general_ci	1
macroman	Mac West European	macroman_general_ci	1
cp852	DOS Central European	cp852_general_ci	1
latin7	ISO 8859-13 Baltic	latin7_general_ci	1
utf8mb4	UTF-8 Unicode	utf8mb4_general_ci	4
cp1251	Windows Cyrillic	cp1251_general_ci	1
utf16	UTF-16 Unicode	utf16_general_ci	4
cp1256	Windows Arabic	cp1256_general_ci	1
cp1257	Windows Baltic	cp1257_general_ci	1
utf32	UTF-32 Unicode	utf32_general_ci	4
binary	Binary pseudo charset	binary	1
geostd8	GEOSTD8 Georgian	geostd8_general_ci	1
cp932	SJIS for Windows Japanese	cp932_japanese_ci	2
eucjpms	UJIS for Windows Japanese	eucjpms_japanese_ci	3

I am using UTF-8, with the collations available below (default in bold):

Collation	Charset	Id	Default	Compiled	Sortlen
utf8_general_ci	utf8	33	Yes	Yes	1
utf8_bin	utf8	83		Yes	1
utf8_unicode_ci	utf8	192		Yes	8
utf8_icelandic_ci	utf8	193		Yes	8
utf8_latvian_ci	utf8	194		Yes	8
utf8_romanian_ci	utf8	195		Yes	8
utf8_slovenian_ci	utf8	196		Yes	8
utf8_polish_ci	utf8	197		Yes	8
utf8_estonian_ci	utf8	198		Yes	8
utf8_spanish_ci	utf8	199		Yes	8
utf8_swedish_ci	utf8	200		Yes	8
utf8_turkish_ci	utf8	201		Yes	8
utf8_czech_ci	utf8	202		Yes	8
utf8_danish_ci	utf8	203		Yes	8
utf8_lithuanian_ci	utf8	204		Yes	8
utf8_slovak_ci	utf8	205		Yes	8
utf8_spanish2_ci	utf8	206		Yes	8
utf8_roman_ci	utf8	207		Yes	8
utf8_persian_ci	utf8	208		Yes	8
utf8_esperanto_ci	utf8	209		Yes	8
utf8_hungarian_ci	utf8	210		Yes	8
utf8_sinhala_ci	utf8	211		Yes	8
utf8_general_mysql500_ci	utf8	223		Yes	1
utf8mb4_general_ci	utf8mb4	45	Yes	Yes	1
utf8mb4_bin	utf8mb4	46		Yes	1
utf8mb4_unicode_ci	utf8mb4	224		Yes	8
utf8mb4_icelandic_ci	utf8mb4	225		Yes	8
utf8mb4_latvian_ci	utf8mb4	226		Yes	8
utf8mb4_romanian_ci	utf8mb4	227		Yes	8
utf8mb4_slovenian_ci	utf8mb4	228		Yes	8
utf8mb4_polish_ci	utf8mb4	229		Yes	8
utf8mb4_estonian_ci	utf8mb4	230		Yes	8
utf8mb4_spanish_ci	utf8mb4	231		Yes	8
utf8mb4_swedish_ci	utf8mb4	232		Yes	8
utf8mb4_turkish_ci	utf8mb4	233		Yes	8
utf8mb4_czech_ci	utf8mb4	234		Yes	8
utf8mb4_danish_ci	utf8mb4	235		Yes	8
utf8mb4_lithuanian_ci	utf8mb4	236		Yes	8
utf8mb4_slovak_ci	utf8mb4	237		Yes	8
utf8mb4_spanish2_ci	utf8mb4	238		Yes	8
utf8mb4_roman_ci	utf8mb4	239		Yes	8
utf8mb4_persian_ci	utf8mb4	240		Yes	8
utf8mb4_esperanto_ci	utf8mb4	241		Yes	8
utf8mb4_hungarian_ci	utf8mb4	242		Yes	8
utf8mb4_sinhala_ci	utf8mb4	243		Yes	8

Q. 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 is Coordinated Universal Time, a date/time format that is time zone-neutral. The UTC_TIME() function returns the UTC time in MySQL. To convert from UTC to another time zone's time, use the function CONVERT_TZ(datetimevalue, fromtimezone, totimezone);

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

Foreign key constraints is a feature of teh InnoDB table type only. They set rules as to what should happen when a break in the relationship between tables would be broken, e.g. when a (child) table contains a foreign key that does not exist in a related (parent) table. It prevents errors such as might be caused by: (a) deleting a record from a table that contains a primary key value that functions as a foreign key value in another table or (b) adding a record with a nonexistent foreign key value.

Pursue

Design your own database:

Work from StudioJCD
Work Category		Work Subcategory		Pieces
Illustration		Kidlit		Piece ID
Design		Editorial		Title
Fine Art		Logo		Caption
Crafts		Interactive		Work Category
		Print		Work Subcategory
		Logo/Branding
		Landscape
		Still Life
		Portrait
		Fabric
		Handpainted