Database normalization with example

http://www.phlonx.com/resources/nf3/

http://ektaraval.blogspot.com/2010/10/database-normalization-with-example.html

Full Text Search in postgres

http://www.espace.com.eg/blog/2009/03/15/postgresql-an-ultimate-strategy-for-full-text-search/

Division in database

http://setiawans.blogspot.com/2009/01/postgresql-division-trick.html

st_simplify

This function is used to simplify the geometory of polygon

st_simplify(the_geom,0.001)

PostgreSql Regular Expression

http://www.postgresql.org/docs/8.3/static/functions-matching.html

Unicode

MySQL 5.0 supports two character sets for storing Unicode data:

1.ucs2, the UCS-2 encoding of the Unicode character set using 16 bits per character.

        In UCS-2, every character is represented by a two-byte Unicode code with the most significant byte first. For example: LATIN CAPITAL LETTER A has the code 0x0041 and it is stored as a two-byte sequence: 0x00 0x41. CYRILLIC SMALL LETTER YERU (Unicode 0x044B) is stored as a two-byte sequence: 0x04 0x4B.

In MySQL, the ucs2 character set is a fixed-length 16-bit encoding for Unicode BMP characters.


2.utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character.
       UTF-8 (Unicode Transformation Format with 8-bit units) is an alternative way to store Unicode data. It is implemented according to RFC 3629, which describes encoding sequences that take from one to four bytes. Currently, MySQL support for UTF-8 does not include four-byte sequences. (An older standard for UTF-8 encoding, RFC 2279, describes UTF-8 sequences that take from one to six bytes. RFC 3629 renders RFC 2279 obsolete; for this reason, sequences with five and six bytes are no longer used.)
The idea of UTF-8 is that various Unicode characters are encoded using byte sequences of different lengths:

• Basic Latin letters, digits, and punctuation signs use one byte.
• Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others.
• Korean, Chinese, and Japanese ideographs use three-byte sequences.
  To save space with UTF-8, use VARCHAR instead of CHAR.

MySql Engines Comparison

	MyISAM	InnoDB	MEMORY	NDB
Multi-statement transactions, ROLLBACK	-	X	-	X
Foreign key constraints	-	X	-	-
Locking level	table	row	table	row
BTREE indexes	X	X	-	X
FULLTEXT indexes	X	-	-	-
HASH lookups	-	X	X	X
Other in-memory tree-based index	-	-	4.1.0	-
GIS, RTREE indexes	4.1.0	-	-	-
Unicode	4.1.0	4.1.2	-	-
Merge (union views)	X	-	-	-
Compress read-only storage	X	-	-	-
Relative disk use	low	high	-	low
Relative memory use	low	high	low	high

postgres vaccum command

1.VACUUM FULL table_name


2.vacuum analyse

POSTGRESQL vs MYSQL

	POSTGRESQL	MYSQL
ANSI SQL compliance	Closer to ANSI SQL standard	Follows some of the ANSI SQL standards
Performance	Slower	Faster
Sub-selects	Yes	No
Transactions	Yes	Yes, however InnoDB table type must be used
Database replication	Yes	Yes
Foreign key support	Yes	No
Views	Yes	No
Stored procedures	Yes	No
Triggers	Yes	No
Unions	Yes	No
Full joins	Yes	No
Constraints	Yes	No
Windows support	Yes	Yes
Vacuum (cleanup)	Yes	No
ODBC	Yes	Yes
JDBC	Yes	Yes
Different table types	No	Yes

SQL UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

syntax 1.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2


syntax 2.

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

1.The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

2.The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
 
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

3.The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

 SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

4.The FULL JOIN keyword return rows when there is a match in one of the tables.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name