Database Design using Normalization

• We will assume that we are working from an existing set of data.
• This data is not normalized.
• This chapter provides guidelines to turning the raw data into a usable database.
• But it feels like just some loose ends.

Creating and loading a table

• This is just a quick introduction to allow you to do the things described in the chapters.
• We will examine all of these commands in more detail later.
• Help:
  • mysql help topic;
  • Just a tad bit overwhelming for some topics, but it is there.
• Table Creation
  • You can create simple tables with the create table command
  • I just want to create a table to store some data.
  • SELLER(SellerName, Department)
  • CREATE TABLE TABLENAME ( field desc, ...)

  • CREATE TABLE SELLER (
          Seller       char(30),
          Department   char(50)
    );
    	  

  • See table 7-4 page 251 for datatypes
  • Or better yet check here
  • MANY MANY MANY more options here.
• Table Deletion
  • DROP TABLE TABLENAME;
• Loading a table from a file:
  • LOAD DATA INFILE "full path to file" INTO TABLE table name
  • LOAD DATA LOCAL INFILE "local file name" INTO TABLE table name
  • The data must be one entity per line
  • "You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table."
  • The fields must be delimited by a \t

  • LOAD DATA LOCAL INFILE "Sellers" 
    INTO TABLE SELLERS 
    FIELDS TERMINATED BY ',';
    

  • Many more options, but these will do some of these later.
• Inserting data into a table
  • INSERT INTO table name (field name,...) VALUES (field value);

  • INSERT INTO SELLERS (SellerName, Department) 
    VALUES ("Meriwether Lewis", "Camping");
    	 

• Delete an entity
  • DELETE FROM table name;
  • DELETE FROM table name where condition;

  • DELETE FROM SKU_DATA 
    WHERE       SKU=500002;
    	

• Update a data field
  • UPDATE table name SET column=value, column=value, ... WHERE condition

  • UPDATE SKU_DATA 
    SET    SKU_Description="Crossbow, Hand Crank" 
    WHERE  SKU=500002;
    	

Assess Table Structure

• Examine the data for
  • Functional Dependencies
  • Multivalued Dependencies
• Look for candidate keys
• Determine the primary key.
• WRITE DOWN ALL ASSUMPTIONS.
• Discuss these assumptions and findings with the user.
• It is possible to load a file into a table
• Some Useful queries:
• Count the rows:

  SELECT count(*)
  FROM   tablename;
       

• Investigate the data
  • Other sql dialects support the TOP keyword in select.

  • SELECT TOP 10 *
    FROM tablename;
    	 

  • mysql supports a limit field to selects

  • SELECT * 
    FROM tablename
    LIMIT count
         

  • Also LIMIT offset,rowcount
  • or LIMIT rowcount OFFSET offset
  • SKU_DATA as before
  • BUYER(Buyer, Department)
  • We want to see if there are any buyers in SKU_DATA that are not in the buyer table

  • Select Buyer 
    From   BUYER
    Where  Buyer not in (
           Select SKU_DATA.Buyer 
           From   SKU_DATA, BUYER
           Where  SKU_DATA.Buyer=BUYER.Buyer);
    	 

• Kroenke gives the following table:
  • Count rows and examine columns
  • Examine data values and interview users to determine:
    • Multivalued dependencies
    • Functional Dependencies
    • Candidate Keys
    • Primary Keys
    • Foreign Keys
  • Assess the validity of assumed referential integrity constraints.