Three value logic and the ACID test - concepts surrounding SQL

Preamble:
To understand this article you need to know some SQL basics. If you're familiar with SQL then you can skip the preamble section...

• You need to know firstly what tables, columns (fields) and rows (records) are in terms of databases – tables are spreadsheets of info within a database, columns define data of a particular type and rows are individual records.
• Secondly you need to know about Child-Parent object-orientated relationships – Parent can have many children but a child cannot have any parents, they themselves can become parents etc. etc.... And for that matter have a basic programming background (eg Javascript).
• Lastly you need to know what a primary key is and how to set up a table in a database management system (eg MySQL or ORACLE)

The ACID Test
A relational database can be defined as "A big spreadsheet that several people can update simultaneously". SQL is a language that queries databases for inserting, deleteting and updateing data. A database management system (or program) such as MySQL or ORACLE should live up to what is known as the ACID test:

Atomicity – When a transaction is carried out, either it all happens or none of it gets done. Its one or the other, there are no inbetweens.

Consistency – A database proceeds from one valid state to another valid state. For example, according to database design, orphans are invalid. What is an orphan? If you create a column in a table that references another table (becomes a child object of that parent table) you can not remove the parent until you have removed all the children. Valid state to valid state.

Isolation – transactions happen in isolation of each other.

Durability – If the system suddenly crashes, the committed transactions are still there and are recoverable.

3 value logic
Important in database management is the concept of 3 value logic. Boolean logic (true or false) is not used in database conditional tests. Istead 3 value logic is used (true, false or NULL). Therefore if you conduct a conditional test to see whether an old variable is different from a new variable – if a != b in most programming languages; in SQL you would say – if a!=b or b is NULL having already made sure that NULL values of a aren't stored in the database. Weird, to be sure but it has a purpose.

Conclusion:
Though SQL is an easy language to learn, there are more difficult concepts surrounding it that take time to grasp. For an example of a complex database driven site see Africapic.com .