Chapter 4 Continuation

• Please read chapter 4.
• You are responsible for the material.
• Some additional SQL commands that might be useful:
• Updatable Database Design
  • You might choose not to go to BCNF, but you should have a good reason to do so.
  • He lists zipcode as a problematic field.
• Running a script from mysql:
  • mysql -u username -p < scriptfile
  • mysql -u dbennett -p < RawEquipment.sql
  • These examples are in my DB_Equipment subdirectory on all the systems for your convenience.
• More on creating a table:
  • An AUTO_INCREMENT attribute can be connected to a field, (Integer or float)
  • This is probably for surrogate keys.
  • Engine=INNODB;
  • MYSQL supports multiple database engines.
  • INNODB enforces integrity constraints
  • But all such tables in the system must be so created, or altered.
  • show table status
  • show warnings
• Moving from one table to another:

  • INSERT INTO table name
        SELECT DISTINCT SrcField1, SrcField2, ... SrcFieldn 
        FROM table name;
    	

  • If these are not all the fields of a table:

  • INSERT INTO table name ( DestField1, DestField2, ... DestFieldn)
        SELECT DISTINCT SrcField1, SrcField2, ... SrcFieldn 
        FROM table name;
    	

• Detecting duplicate field entries

  • SELECT fieldname, count(*) as count
       from table
       group by fieldname 
           having cout > 1;
    	

• Create:
  • CONSTRAINT primary key (Field1, ...)
  • Constraint foreign key references table name(Field)