svn commit - mysqldoc@docsrva: r5523 - trunk/userguide

[plavin]

Author: plavin
Date: 2007-03-23 14:57:46 +0100 (Fri, 23 Mar 2007)
New Revision: 5523

Log:
Add another section

Modified:
   trunk/userguide/Makefile
   trunk/userguide/excel-mysql.xml


Modified: trunk/userguide/Makefile
===================================================================
--- trunk/userguide/Makefile	2007-03-23 11:51:25 UTC (rev 5522)
+++ trunk/userguide/Makefile	2007-03-23 13:57:46 UTC (rev 5523)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 1403 bytes

@@ -39,7 +39,7 @@
 
 USERGUIDE_SRCS_EXTRA = ../common/fixedchars.ent
 
-USERGUIDE_SRCS = $(USERGUIDE_SRCS_EXTRA) userguide.xml configuring.xml copyright.xml
creating.xml disaster.xml dotnet.xml glossary.xml
images/indexing-displaying-administrator.png
images/logfiles-slow-query-log-administrator-settings.png
images/logfiles-slow-query-log-administrator-view.png indexing.xml installing.xml
introduction.xml java.xml legalnotice.en.xml logfiles.xml modifying.xml php-mysqli.xml
populating.xml querying.xml security.xml storage-engines.xml stored-procedures.xml
tools-tricks.xml views.xml 
+USERGUIDE_SRCS = $(USERGUIDE_SRCS_EXTRA) userguide.xml configuring.xml copyright.xml
creating.xml disaster.xml dotnet.xml excel-mysql.xml glossary.xml
images/indexing-displaying-administrator.png
images/logfiles-slow-query-log-administrator-settings.png
images/logfiles-slow-query-log-administrator-view.png indexing.xml installing.xml
introduction.xml java.xml legalnotice.en.xml logfiles.xml modifying.xml php-mysqli.xml
populating.xml querying.xml security.xml storage-engines.xml stored-procedures.xml
tools-tricks.xml views.xml 
 
 userguide-prepped.xml: $(USERGUIDE_SRCS) $(IDMAP_OBJS)
 


Modified: trunk/userguide/excel-mysql.xml
===================================================================
--- trunk/userguide/excel-mysql.xml	2007-03-23 11:51:25 UTC (rev 5522)
+++ trunk/userguide/excel-mysql.xml	2007-03-23 13:57:46 UTC (rev 5523)
Changed blocks: 12, Lines Added: 113, Lines Deleted: 39; 10291 bytes

@@ -141,7 +141,7 @@
       OS you are using. If you are working under Windows with an Excel
       spreadsheet, open the spreadsheet in Excel and choose the the
       <guimenu>Save As</guimenu> menu option under the
-      <guimenu>File</guimenu>menu. From the <guilabel>Save as
+      <guimenu>File</guimenu> menu. From the <guilabel>Save as
       type</guilabel> list box choose the <option>Text(Tab
       delimited)</option> option.
     </para>

@@ -331,10 +331,11 @@
     </para>
 
     <para>
-      When you are finished use the <guibutton>Apply Changes</guibutton>
-      button. This opens a dialog box showing the SQL code that will
-      execute. Before executing this code, copy it and paste it into the
-      script window. The code should look something like this:
+      When you are finished, use the <guibutton>Apply
+      Changes</guibutton> button. This button opens a dialog box showing
+      the SQL code that will execute. Before executing this code, copy
+      it and paste it into the script window. The code should look
+      something like this:
 
 <programlisting>      
 CREATE TABLE `alldata` (

@@ -351,10 +352,10 @@
 </programlisting>
 
       Right click the <literal>spreadsheet</literal> database in the
-      <guilabel>Schemata</guilabel> pane and chooseRefresh Schemata the
-      <guimenu>Refresh Schemata</guimenu> option. The newly created
-      table should appear beneath the <literal>spreadsheet</literal>
-      database, ready for imported data.
+      <guilabel>Schemata</guilabel> pane and choose the
<guimenu>Refresh
+      Schemata</guimenu> option. The newly created table should appear
+      beneath the <literal>spreadsheet</literal> database, ready for
+      imported data.
     </para>
 
   </section>

@@ -513,7 +514,7 @@
 
     <para>
       Among other things, this query shows actual minimum and maximum
-      values for data in the various fields. It any of the maximum field
+      values for data in the various fields. If any of the maximum field
       length values equal the field length, then you have probably
       truncated data. Empty or <literal>NULL</literal> values in some
       fields may also indicate problems.

@@ -624,7 +625,7 @@
       <literal>id</literal> field generates a unique auto increment
       value for each record. Look at the records in the
       <literal>tempmembers</literal> table to confirm that unique id
-      number s have been generated.
+      numbers have been generated.
     </para>
 
     <para>

@@ -662,9 +663,10 @@
 `certificationnumber` VARCHAR(10) NOT NULL
 </programlisting>
 
-      So far we've treated the <literal>certificationdate</literal> field as
text. While
-      we're creating a member accreditations table we can convert this
-      field to the DATE data type. The new definition for this field is:
+      So far we've treated the <literal>certificationdate</literal>
+      field as text. While we're creating a member accreditations table
+      we can convert this field to the DATE data type. The new
+      definition for this field is:
 
 <programlisting>
 `certificationdate` DATE DEFAULT NULL  

@@ -672,9 +674,8 @@
 
       Again we want to concatenate three columns to create a unique
       value and also add an integer column for the member id &mdash; so
-      we can relate the member certifications to their matching records in the
-      members table. The two
-      additional columns are as follows:
+      we can relate the member certifications to their matching records
+      in the members table. The two additional columns are as follows:
 
 <programlisting>
 `unique_value` VARCHAR(150) DEFAULT NULL

@@ -689,7 +690,7 @@
       You've already added <literal>VARCHAR</literal> and
       <literal>INTEGER</literal> fields so adding a
       <literal>DATE</literal> type field should present no problems.
-      Create a table named <literal>tempmeberaccreditations</literal>
+      Create a table named <literal>tempmemberaccreditations</literal>
       and apply your changes.
     </para>
 

@@ -717,15 +718,14 @@
 
     <para>
       Again we need to populate this table from the
-      <literal>alldata</literal> table. We are going to select all
-      the records from the <literal>alldata</literal> table but only
-      selected fields. Click on the
-      <guilabel>Resultset1</guilabel> tab and enter the following query
-      into the query text box:
+      <literal>alldata</literal> table. We are going to select all the
+      records from the <literal>alldata</literal> table but only
+      selected fields. Click on the <guilabel>Resultset1</guilabel> tab
+      and enter the following query into the query text box:
 
 <programlisting>
 INSERT INTO tempmemberaccreditations 
-  SELECT CONCAT(firstname,lastname, city) AS unique_value, 
+  SELECT CONCAT(firstname,lastname,city) AS unique_value, 
   certification, 0 AS memberid, 
   certnumber, 
   STR_TO_DATE(certificationdate, "%d-%b-%y") 

@@ -742,27 +742,27 @@
       '12-Dec-07'. The format specifier tells MySQL exactly how to
       interpret the string representation of the date. In this case the
       specifier, "%d-%b-%y", means the day of the month comes first and
-      is expressed as two digits &mdash; it will have a
-      leading zero even if the value is less than 10, the month is
-      expressed as an abbreviated name, and the year 
-      numerically with two digits. All values are separated by a
-      <quote>-</quote>. <emphasis>Note</emphasis>: The complete
list of
-      specifiers is given in the manual immediately following discussion
-      of the <literal>DATE_FORMAT</literal> function.
+      is expressed as two digits &mdash; it will have a leading zero
+      even if the value is less than 10, the month is expressed as an
+      abbreviated name, and the year numerically with two digits. All
+      values are separated by a <quote>-</quote>.
+      <emphasis>Note</emphasis>: The complete list of specifiers is
+      given in the manual immediately following discussion of the
+      <literal>DATE_FORMAT</literal> function.
     </para>
 
     <para>
       Review the data after executing the <literal>INSERT</literal>
       statement. You'll see that dates are now expressed in the default
-      MySQL format, the year as four digits followed by a two digit
+      MySQL format, the year has four digits followed by a two digit
       month, and a two digit day.
     </para>
 
     <para>
-      At this point it's a good idea to again review the data to ensure
-      consistency. Any stray dates that were improperly formatted in the
-      original spreadsheet will not convert to the
-      <literal>DATE</literal> data type.
+      At this point, reviewing the data to ensure consistency is a good
+      idea. Any dates that were improperly formatted in the original
+      spreadsheet will not convert to the <literal>DATE</literal> data
+      type.
     </para>
 
     <para>

@@ -782,8 +782,10 @@
     </para>
 
     <para>
-      That's the last time we'll need the <literal>unique_value</literal>
field for either
-      of our transitional tables.
+      That's the last time we'll need the
+      <literal>unique_value</literal> field for either of our
+      transitional tables. We can now relate these two tables on the
+      numeric id field.
     </para>
 
   </section>

@@ -800,8 +802,80 @@
 
     <title>Confirming Data Integrity</title>
 
-    <para></para>
+    <para>
+      It's always wise to check the state of your transformed data. There's no
+      substitute for visual inspection but there are a variety of ways
+      to check your data using SQL.
+    </para>
 
+    <para>
+      For example, there should be no orphaned member parecords. Since we've
+      migrated from a flat-table database that contained all the
+      original data, finding an id in the members table with no
+      corresponding record in the member accreditations table would indicate
+      that something was wrong.
+      The following SQL statement will return all records in the members
+      table that don't have matching records in the member accreditations table:
+    </para>
+
+<programlisting>
+SELECT `t`.`id` ,`t`.`firstname`,`t`.`lastname`  
+  FROM `tblmembers` `t` 
+  LEFT JOIN `tblmemberaccreditations` `tma` 
+  ON `t`.`id` = `tma`.`memberid`  
+  WHERE ISNULL(`tma`.`memberid`);      
+    </programlisting>
+
+    <para>
+      If the above <literal>SELECT</literal> statement returns an empty
+      set, there are no orphaned member records.
+    </para>
+
+    <para>
+      An easy way to reuse this SQL statement is to save it as a view.
+      To do this using Query Browser, make sure the
+      <literal>spreadsheet</literal> database is active, then right click
+      on any one of the tables in the <literal>Schemata</literal> window
+      and choose the <guimenu>Create View</guimenu> option. Clicking
+      <guibutton>Okay</guibutton> after entering a viepaw name opens a new
+      tab displaying the basic syntax for creating a view. Paste the
+      preceding SQL statement into the <literal>AS</literal> clause and
+      execute the query. After refreshing the schemata the new view
+      should show up.
+    </para>
+
+    <para>
+      To check that there are no orphaned records in the member
+      accreditations table execute the following query:
+    </para>
+
+<programlisting>
+SELECT `tma`.`accreditationacronym`,
+  `tma`.`memberid`,`tma`.`certificationnumber`,
+  `tma`.`expirydate` 
+  FROM (`tblmemberaccreditations` `tma` 
+  LEFT JOIN `tblmembers` `t` on((`tma`.`memberid` = `t`.`id`))) 
+  WHERE ISNULL(`t`.`id`);     
+    </programlisting>
+
+    <para>
+      Again, convert this to a view using the procedure described above.
+    </para>
+
+    <para>
+      check for duplicate certification numbers 
+      
+      SELECT
+      count(t.`certificationnumber`), t.`certificationnumber` FROM
+      tblmemberaccreditations t Group by (t.`certificationnumber`)
+      Having count(t.`certificationnumber`) > 1; You can also make
+      changes to the data from within Query Browser.
+    </para>
+    
+    <para>
+      edit records from within Query Browser
+    </para>
+
   </section>
 
   <section id="production-database">