Chapter 6 - Review
Normalization is the process of simplifying the design of a database to eliminate data redundancy, maximize efficiency in data maintenance, control relationships between data and ensure data integrity and scalability over time.
PRIMARY KEY unique identifier for each row in a table.
FOREIGN KEY reference to the primary key in another table, used to define relationships between tables.
One-to-One
A record in one table is linked to one and only one record in another table.(a photo must have one filename)
One-to-Many
A record in one table may be linked to many records in another table.(a company may have many departments)
Many-to-Many
Many records from one table may be linked to many records in another table.(a project may have many contributors and each contributor can work on multiple projects)
- INDEX
- Provides general indexing of columns
- UNIQUE
- An index that cannot have repeating values. Allows for the value of NULL
- FULLTEXT
- An index that allows for faster searching of field values for specific matches. Only supported in MyISAM table types.
- PRIMARY
- This is considered a special case of a UNIQUE index that cannot be null. Used to uniquely identify each row in a table.
Generally columns used in WHERE, ORDER BY or GROUP By clauses should be indexed. Any foreign key column should also have its own index. FULLTEXT indexes should only be used in larger tables that have a wide array of data. Do Not generally assign indexes to columns that have a limited range of values or NULL values. Do not assign more than one index to a column.
The two most common table types in a MySQL database are MyISAM and InnoDB. MyISAM supports FULLTEXT indexes and table-level locking but does not support transactions. InnoDB allows for foreign key constraints and transactions as well as row level locking, but does not allow FULLTEXT indexes, takes up more disk space and is slightly slower than MyISAM tables.
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Selecting a character set specifies which characters are able to be stored in the table. The selected collation will determine how these characters are related to one another (ie.. case insensitive, accent recognition etc..). This particular database is using charset = UTF8, collation = utf8_general_ci
UTC stands for Coordinated Universal Time which represents a date/time value without regard to time zone. The functions UTC_DATE(), UTC_TIME() and UTC_TIMESTAMP() will return the current date/time values in UTC for storage in a database independent of the servers settings. To extract and display date/time values according to your location, use CONVERT_TZ(dt,from,to) where dt is the UTC date/time value, from = "UTC" and to would be the time zone you want to represent. example: CONVERT_TZ(UTC_TIMESTAMP,"UTC","America/New_York")
Foreign key constraints are supported in InnoDB tables and allow for maintaining referential integrity. If a row is altered in a parent table being referenced through a foreign key, that child table will be updated accordingly. This eliminates having orphaned data that no longer is useful to the application. If you are not using the InnoDB table type, data maintenance then falls on the programmer to use manual triggers to control data flow.
<?php $review = array( // 1=>array( // 'q'=>'', // 'a'=>'' // ), 1=>array( 'q'=>'Why is normalization important?', 'a'=>'<p>Normalization is the process of simplifying the design of a database to eliminate data redundancy, maximize efficiency in data maintenance, control relationships between data and ensure data integrity and scalability over time.</p>' ), 2=>array( 'q'=>'What are the two types of keys?', 'a'=>'<p><b>PRIMARY KEY</b> unique identifier for each row in a table.</p> <p><b>FOREIGN KEY</b> reference to the primary key in another table, used to define relationships between tables.</p>' ), 3=>array( 'q'=>'What are the three types of table relationships?', 'a'=>'<p><b>One-to-One</b><br/>A record in one table is linked to one and only one record in another table.(a photo must have one filename)</p> <p><b>One-to-Many</b><br/>A record in one table may be linked to many records in another table.(a company may have many departments)</p> <p><b>Many-to-Many</b><br/>Many records from one table may be linked to many records in another table.(a project may have many contributors and each contributor can work on multiple projects)</p>' ), 4=>array( 'q'=>'How do you fix the problem of a many-to-many relationship between two tables?', 'a'=>'A Many-to-Many relationship should be broken down with a third intermediary table to create two One to Many relationships' ), 5=>array( '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?', 'a'=>'<dl> <dt>INDEX</dt><dd>Provides general indexing of columns</dd> <dt>UNIQUE</dt><dd>An index that cannot have repeating values. Allows for the value of NULL</dd> <dt>FULLTEXT</dt><dd>An index that allows for faster searching of field values for specific matches. Only supported in MyISAM table types.</dd> <dt>PRIMARY</dt><dd>This is considered a special case of a UNIQUE index that cannot be null. Used to uniquely identify each row in a table.</dd> </dl><p>Generally columns used in WHERE, ORDER BY or GROUP By clauses should be indexed. Any foreign key column should also have its own index. FULLTEXT indexes should only be used in larger tables that have a wide array of data. Do Not generally assign indexes to columns that have a limited range of values or NULL values. Do not assign more than one index to a column.</p>' ), 6=>array( 'q'=>'What are the two most common MySQL table types? What is the default table type for your MySQL installation?', 'a'=>'<p>The two most common table types in a MySQL database are MyISAM and InnoDB. MyISAM supports FULLTEXT indexes and table-level locking but does not support transactions. InnoDB allows for foreign key constraints and transactions as well as row level locking, but does not allow FULLTEXT indexes, takes up more disk space and is slightly slower than MyISAM tables.</p>' ), 7=>array( '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'=>'<p>A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Selecting a character set specifies which characters are able to be stored in the table. The selected collation will determine how these characters are related to one another (ie.. case insensitive, accent recognition etc..). This particular database is using charset = UTF8, collation = utf8_general_ci</p>' ), 8=>array( '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?', 'a'=>'<p>UTC stands for <em>Coordinated Universal Time</em> which represents a date/time value without regard to time zone. The functions UTC_DATE(), UTC_TIME() and UTC_TIMESTAMP() will return the current date/time values in UTC for storage in a database independent of the servers settings. To extract and display date/time values according to your location, use CONVERT_TZ(dt,from,to) where dt is the UTC date/time value, from = "UTC" and to would be the time zone you want to represent. example: CONVERT_TZ(UTC_TIMESTAMP,"UTC","America/New_York")</p>' ), 9=>array( 'q'=>'What are foreign key constraints? What table type supports foreign key constraints?', 'a'=>'<p>Foreign key constraints are supported in InnoDB tables and allow for maintaining referential integrity. If a row is altered in a parent table being referenced through a foreign key, that child table will be updated accordingly. This eliminates having orphaned data that no longer is useful to the application. If you are not using the InnoDB table type, data maintenance then falls on the programmer to use manual triggers to control data flow.</p>' ) ); include('templates/review.php'); ?>