Assess Table Structure

• You have been handed a set of data.
• Which I hope you can insert into a table or two.
• Take a look at ~dbennett/313/ch4/newcollege2010.csv
  • This is college enrollment data for PA
  • It is from PDE.
  • It contains the following note: "Fall Enrollments, 2010 - Enrollment data by student level. Residence of First-Time Freshmen, Fall 2010 - First-time freshmen enrollments by state of residence. NOTE: Residence data on first-time freshmen is collected biennially in even numbered years"
  • Step 1, get it into a table, ~dbennett/313/ch4/college.sql
    • only one new piece, (IGNORE 1 LINES)
  • Oh, and don't forget
    • pager less
    • SELECT * from COLLEGE_RAW \G
    • nopager
• He goes over a few SQL commands that might help when exploring this data.
  • Count rows and examine the columns
  • Examine the data and talk to users to
    • Find multivalued dependencies
    • Functional Dependencies
    • Candidate Keys
    • Primary Keys
    • Foreign keys
  • Check to see if assumed referential integrity constraints are valid.
• So you might

  SELECT COUNT(*) FROM your_table;
  
  SELECT * FROM your_table LIMIT 5;
  
  SELECT field 
  	FROM table1
  	WHERE field NOT IN
  	(SELECT 	field
  		FROM 	table1, table2
  		WHERE	table1.field = table2.field)
  
  SELECT count(*) 
  	FROM table
  	WHERE filed IS NULL;
  
  SELECT field, count(*) 
  	FROM table
  	GROUP BY field
  

• Some questions
  • How many universities
  • How many Categories
  • What is the UnitID related to
  • Is Year useful?
  • Student Codes, Student Level - related
  • Counties?
• To Normalize or not to normalize
  • He lists reasons to and not to normalize
  • Advantages
    • Eliminate modification anomalies
    • Reduce duplicated data
      • Eliminate data integrity problems
      • Save file space
  • Disadvantages
    • More complicated Database
      • More tables to understand, but they are smaller
      • More complicated joins
      • Possibly slower applications due to extra work.
  • In the end he states "The problems of modification anomalies are so great that you should put your tables into BCNF"
• INSERT... SELECT
  • A reference

  • INSERT INTO table_name
       (col, col, col)
       SELECT (normal select statement)
    	   

  • So if we look InstitutionCategoryCode and InstitutionCategoryName
    • InstitutionCategoryCode → InstitutionCategoryName
    • COLLEGE_CATEGORY(Code,Name)
    • There appear to be a fairly low number, so make Code tinyint
    • SELECT MAX(CHAR_LENGTH(InstitutionCategoryName)) FROM COLLEGE_RAW;
    • Take a look at ~dbennett/313/ch4/category.sql
• Now we can test to see if all institutions are covered by an institution category.

• SELECT InstitutionName 
     FROM COLLEGE_RAW
     WHERE InstitutionCategoryCode NOT IN (
         SELECT InstitutionCategoryCode 
            FROM COLLEGE_CATEGORY,COLLEGE_RAW 
  	  WHERE COLLEGE_RAW.InstitutionCategoryCode = COLLEGE_CATEGORY.Code);
        

• Choosing NOT to Use BCNF
  • He discusses ZipCode → (City, State)
  • This data rarely changes, so modification anomalies would be decreased.
  • And normal usage would catch such errors.
  • And time to join two tables is much greater than the time savings.
• He does comment that Multivalued Dependencies should ALWAYS be eliminated.