Understanding Database table normalization

I am learning the fundamentals of how to "normalize" a database. Normalization means in the simplest sense - "remove data repeat, remove data constraints (or dependency) across tables when performing data operations (during create, update and delete operations on table).

The simplest form of Normalization is the First Normal Form (1NF)
The following rules must be followed to make a database table 1NF conformed.

1. Each column must have a unique name.
2. The ordering or rows and columns in the table is irrelevant.
3. Every column must have a single data type - that is if a column holds string values, then it cannot hold integer values.
4. Each row must hold unique values - no two rows can be the same.
5. Each column must contain a single value.
6. Columns cannot contain repeating groups. 

An example to illustrate point #6 would be an account holder who has multiple bank accounts

 CustID  Name         SSN-4       Bank Name1      Bank Name2       Bank Name3
  A123   Tom Brockaw  1234          BoFA          WellsFargo       Chase

Instead the bank data should be put in a seperate table 

Customer table
CustID  Name         SSN-4

A123   Tom Brockaw  1234

Bank table 

Cust ID Bank name
A123  BoFA
A123  WellsFargo
A123 Chase

Next step is to convert a database table to 2NF compliance. A table is considered 2NF when it satisfies

1. 1NF compliant
2. All of the non-key fields depend on all of the key elements.

To illustrate point #2,here is an example

Name	Languages	Current Home Location
Sean	C++	268 San Bruno
Sean	Java	268 San Bruno
Sean	Perl	268 San Bruno
Jim	C	114 Wall Street
Jim	Java	114 Wall Street
Jim	C#	114 Wall Street

Here the key is Name + Languages but the "Current Home Location" is based on part of the key - Name and not on "Languages". Therefore the non-key field ("Current Home Location") can be taken out of the table and placed in a separate table.
Now the 2 NF table is
Name Languages
Sean C++
Sean Java
Sean Perl
Jim C
Jim Java
Jim C#

Name Current Home Location
Sean  268 San Bruno
Jim 114 Wall Street.


A 3NF complaint table is

1. 2NF compliant
2. It contains no transitive dependencies - in this case a non key is dependent on another non key.

Here is an example of a Employees table which is not 3 NF compliant with Employee as primary key. 

Employee                        Project                     Department
Alison                             Java                          Computer Science
Jim                                  C#                            Computer Science
John                                SIP                           Electronics & Telecommunications
Tim                                 CTI                           Electronics & Telecommunications
Robert                            Long Jump                Athletics
Sean                               Sprint                         Athletics
Tara                                Java                           Computer Science

In this case Department is dependent on the Project an employee takes and both Project and Department are non-keys. In order to remove the transitive relationship, we take out the dependent field (Department) into a seperate table and link the dependent field with the other non-key (Project)

Employee (PK)               Project
Alison                             Java                         
Jim                                  C#                           
John                                SIP                        
Tim                                 CTI                        
Robert                            Long Jump              
Sean                               Sprint                       
Tara                                Java

Project (PK)                           Department
Java                          Computer Science
C#                            Computer Science
SIP                           Electronics & Telecommunications
CTI                           Electronics & Telecommunications
Long Jump                Athletics
Sprint                         Athletics

Boyce Codd Normal Form - 3 NF + Every determinant is a candidate key ( fields that may be dependent on part of the primary key)
 Note: - A candidate key is a "minimal" superkey.
4NF complaint table - which is BCNF compliant + It does not contain an unrelated multi-valued dependency

5NF - 4NF + does not contain any related multi-valued dependency

DKNF (Domain/Key Normal Form) - the table contains no constraints except domain and key constraints


5NF - 4 NF + does not contain any related multi-valued dependency

              

Java Tag Libraries - An Introduction.

Java Tag libraries allow developers seperate presentation layer from design/code layer in your Java Server Pages. You can have a professional designer design the look and feel or the layout of your web page and you can have an expert Java programmer create the necessary class libraries for the designer to call.

In order to create tag libaries, you will need to
- download and install servlet-2.3.jar or compatible version of servlet library within your lib folder of your Java installation.

The javax.servlet.jsp package ships with the install package of Tomcat.
You can download and install tomcat from
http://apache.cyberuse.com/tomcat/tomcat-7/v7.0.21/bin/apache-tomcat-7.0.21-windows-x86.zip

Note:- To learn a bit more about servlet package, refer to this documentation link
http://download.oracle.com/javaee/6/api/javax/servlet/package-summary.html

This links offers a history about servlets
http://weblogs.java.net/blog/driscoll/archive/2005/12/servlet_history_1.html

The logic of tag library works as follows
1. You have a JSP page that is requested by a client browser. Example :- The user enters in their browser http://server:8080/webapps/begjsp-ch02/currentTime.jsp.

2. The JSP page contains custom tags that basically executes the code associated with the custom tag in the background and send the output to JSP Runtime to send as output to the client requesting the currentTime.jsp page.

3. In order for the JSP page to execute code associated with the custom tag in the jsp page, JSP page should reference a TLD TLD (Tag Library Descriptor) file which maintains the mapping of custom tags used within the JSP to referenced tag-classes that have the java code.

Now lets get into the details....
The sample JSP file would appear as follows

<%@taglib prefix="example" uri="WEB-INF/tlds/exampleTags.tld" %>
<html>
<head> </head>
<body>
Welcome to my page. the current time is <example:time format="dd/MM/yy" evalPage="true"/>
<br>
<%
String [] strings = {"apples", "oranges", "bananas", "pineapples", "plums"};
pageContext.setAttribute("fruits", strings);
%>
Looping through fruit basket
<example:iterate>
The fruit is :
</example:iterate>

<example:foo>
<example:test/>
</example:foo>

<example:bodymod howMany="5"> Fruit name is:
</example:bodymod>

<p>


</body>

</html>

In the JSP example above we can see prefix="example" uri="WEB-INF/tlds/exampleTags.tld" in the first link of the sample above. In this example we will be referencing the tag

Date, GregorianCalendar, SimpleDateFormat Classes Explained

import java.util.*;
import java.text.SimpleDateFormat;
public class MyCalendar
{
private Date curr_date;
private GregorianCalendar greg_targ_date;
private String display_curr_date;
private SimpleDateFormat sf;
private String msg;

public MyCalendar()
{
curr_date = new Date();
//By default instantiation of this Date returns current Date. The date returned is in a long format.
//If you would like a simplified format here is how the Date object is converted into simple text format which is of type String.
sf = new SimpleDateFormat("K:mm a, z");
display_curr_date = sf.format(curr_date);

//You can use the gregorian format Object to set Year, month, day and time specifically.
greg_targ_date = new GregorianCalendar();
greg_targ_date.set(greg_targ_date.YEAR,3000);
greg_targ_date.set(greg_targ_date.MONTH,0);
greg_targ_date.set(greg_targ_date.DATE,1);
greg_targ_date.set(greg_targ_date.AM_PM,0);
greg_targ_date.set(greg_targ_date.HOUR,0);
greg_targ_date.set(greg_targ_date.MINUTE,0);
greg_targ_date.set(greg_targ_date.SECOND,0);
}
//This function returns the current date/time converted into simple date format object.
public String getCurrentDate()
{
return (display_curr_date);
}

//This function calculates the number of days remaining from today's date/time to Year 3000 stored in targ_date
public String getMessage()
{

Date targ_date= (Date)greg_targ_date.getTime();

// Gregorian calendar Date object is converted into Date object using getTime() method.
// When getTime() method is applied on a Date object, the Date/Time is returned in milliseconds.
// Therefore (greg_targ_date.getTime()).getTime() would yield the date/time in milliseconds.
switch(targ_date.compareTo(curr_date)) // Comparing the Date object using compareTo since Date object implements Comparable Interface.
{
case 1:
long milseconds = targ_date.getTime() - curr_date.getTime(); // Difference in milliseconds between target date and current date.
long msInDay = 1000 * 60 * 60 * 24;
long daysToGo = (long) (milseconds / msInDay);
return ("The number of days to go is " + daysToGo + " from target date");
case 0:
return ("Current date equal to target date");
case -1:
default:
return ("Expired Counter");
}
}


public static void main (String args[])
{
MyCalendar mc = new MyCalendar();
System.out.println("--------------------------------------------------------------------------------");
System.out.println("Date converted into SimpleFormat (displaying time and AM/PM)=" + mc.getCurrentDate());
System.out.println("Display current date/time using Date Object=" + mc.curr_date);
System.out.println("Display current date in milliseconds=" +mc.curr_date.getTime());
System.out.println("--------------------------------------------------------------------------------");
System.out.println("Example displaying GregorianCalendar object applied getTime() method multiple times");
System.out.println("--------------------------------------------------------------------------------");
GregorianCalendar temp = new GregorianCalendar();
System.out.println("Original GregorianCalendar Object(temp)=" + temp);
System.out.println("");
System.out.println("");
System.out.println("Applying getTime() on GregorianCalendar Object yields Date Object");
System.out.println("temp.getTime()=" + temp.getTime());
System.out.println("");
System.out.println("");
System.out.println("Applying getTime() on Date Object yields Date in seconds(datatype = long)");
System.out.println("temp.getTime()).getTime()=" + (temp.getTime()).getTime());
System.out.println("--------------------------------------------------------------------------------");
//Calling the method which displays number of days from today until Year 3000 (target date)
System.out.println(mc.getMessage());
}

}
The java output is displayed below

Write a simple Java Bean

1. Startup your apache tomcat from command line - Navigate to the bin folder and run the startup.bat. Make sure Apache tomcat webserver is running by bringing up the browser and running http://localhost:8080

Note:-The configured HTTP port can be found by viewing the server.xml file in the conf directory.

2.We first need to write a Java code and then compile the java code and place the generated .class file in the appropriate package directory.

For example: - In our test example here - MyCalendar.java is placed under webapps/begjsp-ch02 directory of the apache tomcat installation.

package dates;
import java.util.*;
import java.text.SimpleDateFormat;
public class MyCalendar
{
private Date curDate;
private SimpleDateFormat sf;
private GregorianCalendar gf;


public String getCurrentDate()
{
gf = new GregorianCalendar();
curDate=gf.getTime();
sf = new SimpleDateFormat("EEE, HH:MM:SS");
return ((String) sf.format(curDate));
}
/*
public static void main (String args[])
{
MyCalendar mc = new MyCalendar();
System.out.println("SimpleFormatDate" + mc.getCurrentDate());

}
*/
}

If you notice this java code belongs to "dates" package.

3.Therefore after you have compiled the .java file, place the generated .class file under
webapps/begjsp-ch02/WEB-INF/classes/dates/ folder path.

4.Place the calling jsp code under begjsp-ch02 folder


In order to call the function getCurrentDate in the java class, it is accessed through the java bean as currentDate. Notice we removed the "get" and converted the property into first letter in "CurrentDate" as lowercase to follow the bean naming standards.

Comparable interface example

When i was compiling my class, i was getting abstract method error on the MyShopping Cart class when i was specifically typecasting the list.
public class MyShoppingCart implements Comparable

The error thrown by the Java interpretor was the following error

C:\jakarta\apache-tomcat-6.0.24\webapps\begjsp-ch02\MyShoppingCart.java:4: MyShoppingCart is not abstract and does not override abstract method compareTo(MyShoppingCart) in java.lang.Comparable
public class MyShoppingCart implements Comparable

In order to the correct the error, I made the following changes to my compareTo method implemented for the Comparable interface.

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Collections;
public class MyShoppingCart implements Comparable
{
private String name;
private float cost;

public MyShoppingCart()
{
name="";
cost=0.0f;
}
public MyShoppingCart(String name, float cost)
{
this.name=name;
this.cost=cost;
}

public boolean equals (Object obj)
{
if (!(obj instanceof MyShoppingCart))
{
return false;
}
MyShoppingCart sc = (MyShoppingCart)obj;
return (this.name).equals(sc.name);
}

public int hashCode()
{
return name.hashCode();
}

//public int compareTo(Object element ) <-- Incorrect code
public int compareTo(MyShoppingCart element )
{

// MyShoppingCart sc = (MyShoppingCart) element; <-- Incorrect code
MyShoppingCart sc = element;
String uname = sc.name;
return (this.name.compareTo(uname));
}


public static void main (String args[])
{
ArrayList mylist = new ArrayList ();
System.out.println("The size of mylist is " +mylist.size());

MyShoppingCart a1 = new MyShoppingCart("Dell Laptop", 1500.99f);
MyShoppingCart a2 = new MyShoppingCart("Apple IPAD", 599.99f);
MyShoppingCart a3 = new MyShoppingCart("X-box 360", 699.99f);
MyShoppingCart a4 = new MyShoppingCart("Tennis racket", 49.5f);
System.out.println("Is the list empty? " + mylist.isEmpty());
mylist.add(a1);

System.out.println("Is the list empty? " + mylist.isEmpty());
mylist.add(a2);
mylist.add(a3);
mylist.add(a4);

System.out.println("The size of mylist is " +mylist.size());
System.out.println("The index within the list for matching record Dell Laptop, 1500.99f is:" + mylist.indexOf(a1));
Iterator iter = mylist.iterator();
while (iter.hasNext())
{
MyShoppingCart m1 = (MyShoppingCart)iter.next();
System.out.println(m1.name + " " + m1.cost);
}
iter = mylist.iterator();
System.out.println("After sorting....");
Collections.sort(mylist);
while (iter.hasNext())
{
MyShoppingCart m1 = (MyShoppingCart)iter.next();
System.out.println(m1.name + " " + m1.cost);
}

}
}

HashSets, TreeSets, Array Lists, Comparable and Comparators

import java.util.*;

//This class is used to sort a collection in descending order of age and name
//A comparator class is usually written when a programmer usually does not have access to
//source code and then he can create his own custom sort using custom Comparator class as shown in
//Person class below
class Person implements Comparator
{
String name;
int age;
public Person(String name,int age)
{
this.name=name;
this.age=age;
}
public Person()
{
name="";
age=0;
}
public int compare(Person p1,Person p2)
{
if (p2.age>p1.age) return (p2.age-p1.age);
else if (p2.age < p1.age) return (p2.age-p1.age);
else
{
return (p2.name.compareTo(p1.name));
}
}

//Implementing equals method is optional. If implemented to over-ride, make sure the result is consistent with the compare method
public boolean equals(Object o)
{
Person p2 = (Person) o;
if (p2.age>this.age) return (true);
else if (p2.age < this.age) return (false);
else
{
if (p2.name.compareTo(this.name)>= 0)
return true;
else
return false;
}
}


//Over-ride from the object class.
public String toString()
{
return ("Name=" + this.name + ", age=" + this.age);
}
}

//This class sorts a collection in ascending order of age and name and implements comparable.
class PersonComparable implements Comparable {
String name;
int age;
public PersonComparable(String name,int age)
{
this.name=name;
this.age=age;
}
public int compareTo(PersonComparable p2)
{

if (this.age>p2.age) return (this.age-p2.age);
else if (this.age < p2.age) return (this.age-p2.age);
else
{
return (this.name.compareTo(p2.name));
}
}
//Implementing equals method is optional. If implemented to over-ride, make sure the result is consistent with the compareTo method
public boolean equals(Object o)
{
PersonComparable p2 = (PersonComparable) o;
if (this.age>p2.age) return (true);
else if (this.age < p2.age) return (false);
else
{
if (this.name.compareTo(p2.name)>= 0)
return true;
else
return false;
}
}

public String toString()
{
return ("Name=" + this.name + ", age=" + this.age);
}
}

public class CollectionsTest01
{

public static void main(String args[])
{
//hashset allows duplicate and null values. This set is created with Primitive class- string
//hash sets are not ordered by default
Set hs1 = new HashSet();
System.out.println("The original size of the hashset is="+ hs1.size());
hs1.add("temp");
hs1.add(Integer.toString(1));
hs1.add(Boolean.toString(true));
hs1.add("Dushyant");
hs1.add("temp");
hs1.add(null);
System.out.println("The current size of the hashset is="+ hs1.size());
System.out.println("The String hash set is="+hs1);

//hashset allows duplicate and null values. This set is created with Custom class- Person
//hash sets are not ordered by default even if you implement the comparator or comparable interfaces.
HashSet ps1 = new HashSet ();
ps1.add(new Person("Donde Estas",35));
ps1.add(new Person("Ronnie Baron",35));
ps1.add(new Person("Google froogle",31));
ps1.add(new Person("Simple simon",24));
ps1.add(new Person("Greg Bruno",3));
ps1.add(new Person("Ronnie Baron",35));

System.out.println("Iterating through Person hash set");
Iterator it = ps1.iterator();
while(it.hasNext())
{
System.out.println(it.next());
}
//When sorting a tree set pass the comparator in the the tree set constructor.
//Tree sets are sorted order sets and apply the compare method
TreeSet personTreeSet1 = new TreeSet(new Person());
personTreeSet1.add(new Person("Donde Estas",35));
personTreeSet1.add(new Person("Ronnie Baron",35));
personTreeSet1.add(new Person("Google froogle",31));
personTreeSet1.add(new Person("Simple simon",24));
personTreeSet1.add(new Person("Greg Bruno",3));
personTreeSet1.add(new Person("Ronnie Baron",35));

System.out.println("Iterating through Person Tree set");
it = personTreeSet1.iterator();
while(it.hasNext())
{
System.out.println(it.next());
}

//Creating a tree set implementing Comparable.
//Tree sets are sorted order sets and apply the comparable interface. Use the compareTo method here to sort.
TreeSet ts1 = new TreeSet ();
ts1.add(new PersonComparable("Damini",35));
ts1.add(new PersonComparable("Gamini",35));
ts1.add(new PersonComparable("Kumari",31));
ts1.add(new PersonComparable("Tsunami",24));
ts1.add(new PersonComparable("Kumari",31));
Iterator it2 = ts1.iterator();
System.out.println("The size of the tree set is="+ts1.size());
System.out.println("Iterating through PersonComparable Tree set");
while(it2.hasNext())
{
System.out.println(it2.next());
}

//Example to apply the static collections method sort and reverseOrder. These static methods only apply on lists.
//The array list is created with Comparable constructor.
ArrayList lst1= new ArrayList();
lst1.add(new PersonComparable("Raj Kapoor",36));
lst1.add(new PersonComparable("Vinod Khanna",39));
lst1.add(new PersonComparable("Dilip Kumar",36));
lst1.add(new PersonComparable("Dilip Kumar",36));
System.out.println("Display the array list before sorting="+lst1);
Collections.sort(lst1);
System.out.println(lst1);

System.out.println("Display the array list after sorting="+lst1);
Collections.sort(lst1,Collections.reverseOrder());
System.out.println("Reverse order sorted list="+lst1);

//Example to apply the static collections method sort and reverseOrder. These static methods only apply on lists.
//The array list is created with Comparator constructor when applied later. In this example Person comparator is applied
ArrayList lstPerson = new ArrayList();
lstPerson.add(new Person("Robin Hood",36));
lstPerson.add(new Person("John Peter",39));
lstPerson.add(new Person("Dwight York",36));
lstPerson.add(new Person("Kevin Pieterson",36));
Collections.sort(lstPerson,new Person());
System.out.println("Applying comparator Person to sort the list="+lstPerson);

TreeSet lst2= new TreeSet();
lst2.add(new PersonComparable("Rajarshi",36));
lst2.add(new PersonComparable("JP",39));
lst2.add(new PersonComparable("Mangesh",36));
lst2.add(new PersonComparable("JP",39));
//Collections.sort(lst2); You cannot apply this Static method on a set. It only applies on a list.
System.out.println(lst2);

ArrayList c = new ArrayList();
c.add(1);c.add(2);
c.add(3);
ArrayList al = new ArrayList(c);
System.out.println("The current size of the collection is="+ al.size());

Set set = new HashSet();
set.add("zab");
set.add("one");
set.add("two");
set.add("three");
set.add("four");
set.add("one");
set.add("abc");
set.add(Integer.toString(3));
System.out.println(set);
Set sortedSet= new TreeSet(set);
System.out.println(sortedSet);
}
}

PipedInput Stream and PipedOutputStream

import java.io.*;
// reads numbers from input stream and compute running average
class WriteToFile extends Thread
{
private DataInputStream in;
String msg;
private FileOutputStream out;
File f1=new File("C:\\Program Files\\Java\\jdk1.5.0_14\\test\\AJP","output.txt");



public WriteToFile(InputStream i)
{
in=new DataInputStream(i);
try
{
out = new FileOutputStream(f1);
}catch(FileNotFoundException e) { e.printStackTrace();}

}
public void run()
{
String CRLF = "\n";
try {
int count;
while (true)
{

byte buffer[] = new byte[50];

count=in.read(buffer,0,50);
if (count==-1) break;
// System.out.println("Available bytes to read : " + count);
msg = new String(buffer,0,count);


if (count==-1 ||msg.equalsIgnoreCase("EOI") ) { in.close(); break;}
if (count>0 && msg.equalsIgnoreCase("EOI")==false)
{

out.write(buffer,0,count);
if (in.available()==0)
out.write(CRLF.getBytes());
}
}
out.flush();
} catch (Exception e) { e.printStackTrace();}

}

}
class ReadFromCommandLine extends Thread {
private DataOutputStream out;
private BufferedReader buf;
String msg;
public ReadFromCommandLine(OutputStream o) {
out = new DataOutputStream(o);
buf = new BufferedReader(new InputStreamReader(System.in));

}
public void run() {


try {
System.out.println("Please enter the text in multiple lines to write to a file:");
while (true)
{
msg = buf.readLine();

byte b[]=msg.getBytes();
out.write(b);
out.flush();
// System.out.println(msg);
if (msg.equalsIgnoreCase("EOI"))
{
out.close();
break;
}

}


} catch (Exception e) { e.printStackTrace();}

}

}

class MainPP
{
public static void main(String[] args)
{

try {
PipedOutputStream inputreader =new PipedOutputStream();
PipedInputStream filewriter =new PipedInputStream(inputreader);

ReadFromCommandLine readCommandLine = new ReadFromCommandLine(inputreader);
WriteToFile writeFile = new WriteToFile(filewriter);
readCommandLine.setPriority(1);
readCommandLine.start();
writeFile.start();
} catch (IOException e) {e.printStackTrace();}
}
}