For a course about chemical information we start at the lowest level - how computers deal with information internally. Some of you may already have some understanding of this, but there are some important topics we need to touch on to set things up for other topics.  As a start, imagine you have a Word document that contains the following:

904-620-1938

Humans of course can very easily identify that this is a telephone number, but to a computer it looks like the following in its basic format – binary (more information on this later…)

00111001, 00110000, 00110100, 00101101,
00110110, 00110010, 00110000, 00101101,
00110001, 00110001, 00110011, 00111000

Whether it be on a regular hard disk drive (magnetic coating on a disk) or a flash/SSD (memory chips) all data on a computer is stored as 1’s and O’s  (see http://www.pcmag.com/article2/0,2817,2404258,00.asp). As a result, all data on a computer must be represented in binary notation.  Each recorded 1 or 0 is a ‘bit’ and eight bits make a ‘byte’. A byte is the basis of presenting data because eight bits, being either 1 or 0, can together represent numbers up to 255.  This is because for each bit there are two possible permutations and eight bits gives 2⁸ combinations - 256 values, or 0 thru 255.

In the early days of computer systems, a single byte was used as the way to represent text characters, and was defined by the American Standard Code for Information Interchange (ASCII – see http://www.ascii-code.com/).  Initially, only the numbers 0-127 where used to represent letters, numbers, punctuation marks and symbols (32-127), and non-printed characters (0-31).  Subsequently, extended ASCII was introduced which added accented characters, other punctuation marks, and symbols (128-255).

Looking back on the example of the telephone number above, we can now translate the binary into the telephone number:

Although we still ‘use’ ASCII today, in reality we use something called UTF-8.  This is easier to say than how it is derived - Universal Coded Character Set + Transformation Format - 8-bit.  Unicode (see http://unicode.org) started in 1987 as an effort to create a universal character set that would encompass characters from all languages and defined 16-bits, two bytes -> 2¹⁶ -> 256 x 256 = 65536 possible characters – or code points. Today, the first 65536 characters are considered the “Basic Multilingual Plane”, and in addition there are sixteen other planes for representing characters giving a total of 1,114,112 code points.  Thankfully, we don’t need to worry because if something is UTF-8 encoded it is backward compatible with the first 128 ASCII characters.

It’s worth pointing out at this stage that the development of Unicode is a good thing for science.  We speak our own language and have special symbols that we use in many different situations (how about the equilibrium symbol?  ⇌ ) and so publishers in science and technology have developed fonts for reporting scientific research.  Check out and install STIX fonts (http://www.stixfonts.org) which would not be possible without Unicode.

Looking back at the telephone number in the previous section our identification of the characters is based off of two things – the fact that there are only digits and hyphens, and the pattern of digits and hyphens.  Again though the computer knows nothing of this, it just knows that there is a string of 12 characters. So, in the context of humans representing information in computers it is not just important for us to use characters, we also need to know generically what they represent.  This is critical to everything we do on a computer because we need to know how to process the characters.

In a broad sense characters represent either; text, numbers, or special formats.  In the context of discussing the common ‘data types’ we are going to reference those that are used in the relational database software ‘MySQL’.  More about what MySQL actually is later, for now we will look at the ways in which the program represents information that it stores (see http://dev.mysql.com/doc/refman/5.6/en/data-types.html for more details).

Numeric types

Representation of different numeric values is important because it is dependent on how they are used.  Choosing the right type is critical to making sure there are no inconsistencies, especially when making comparisons or rounding in calculations.

• Integers – You would think that there is only one type of integer (i.e. an exact number), but it turns out that we have to think about how much space we use up storing the number on a computer not just its type.  MySQL defines TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.  The definitions of these are based on how big the number is they can represent which in turn is based off how many bytes are used to store the number.  For each of these (like all numeric values) you also have to specify if the first bit of the leading byte indicates ± or not.
• Fixed Point Numbers – DECIMAL (or NUMERIC) in MySQL are used to store values with a defined precision (a set number of decimal places) and thus are non-integer exact numbers. Using the syntax DECIMAL(X,Y) you can define a decimal with X total digits (max 65) and Y decimal digits (max 30), so DECIMAL(4,2) allows you to store from -99.99 through +99.99.
• Floating Point Numbers – Very important in computers, and chemistry, floating point numbers FLOAT and DOUBLE are used to represent non-exact decimal numbers that range from very small to very large.  The difference between FLOAT and DOUBLE is FLOAT uses four bytes to store the number (accurate to seven decimal places) and DOUBLE uses eight bytes (accurate to 14 decimal places).  FLOAT is normally good enough for scientific calculations as it can store -3.402823466E+38 to -1.175494351E-38 and 1.175494351E-38 to 3.402823466E+38. These definitions work with the common IEEE standard of floating point arithmetic (see https://dx.doi.org/10.1109%2FIEEESTD.2008.4610935)

String Types

The differences between string types is primarily based on how much space they take up and how easy they are to search.  Thinking about the application of the string is important especially deciding if a string should be exclusive or inclusive.  Also, realize that there are situations where you might want to store a number as a string.

• Fixed Length Strings – CHAR allows you to define a string of characters (up to 30) of an exact length.  If the string length is lower than the number of characters it is padded with spaces after the text. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT store 255, 65,535, 16,777,215, and 4,294,967,296 characters respectively (no extra spaces).
• Variable Length Strings - VARCHAR allows you to define a string up to 65,535 characters in length – it therefore has a variable maximum length.  No spaces are added.
• Predefined String Types – There are times where you want to store strings but not just anything.  Take for example a field that stores the size of a shirt – small, medium, or large.  Using ENUM (short for enumerated) you define the list of possible strings that the field can take.  If you try and store any other value nothing gets stored.  You can also expand this idea to use more than one value from a defined list using SET.  You might recognize these string types as they are commonly used to populate dropdown menus and multiple select lists in web pages.

Other Types

There are many other types of information that are stored, some common ones are:

• Dates, Times and Timestamps – Common formats exist for representation of dates and exact points in time.  These of course are just special format text strings that are standardized so that computers can interpret them (you can do the same for telephone numbers, social security numbers, etc.).  DATE format is 'YYYY-MM-DD', TIME format is 'HH:MM:SS', and DATETIME is 'YYYY-MM-DD HH:MM:SS'.  TIMESTAMP looks the same as DATETIME except it stores the data internal factoring in the local time zone using the Coordinated Universal Time (UTC) format (see http://www.cl.cam.ac.uk/~mgk25/iso-time.html)
• Boolean – True or False is stored as a TINYINT(1) where 1 is true and 0 is false
• Binary – Everything so far has be stored in character strings but there are situations where you might want to store byte strings – or binary.  This is more technical than we need so we don’t have to worry about it.  It is fun to point out though that Binary Large Objects (BLOBs) can be used to store binary files like images, audio, etc.

The mention of binary files in the previous sections highlights that there are differences in how data is stored by computers.  Traditionally, programs or applications are stored in binary format because they contain code that is used to allow the program to run and thus should not be displayed as text (its not readable anyway).  In addition, there are many non-text based files, images, audio, video etc. that need to store their content in a format that is efficient – a place where UTF-8 (ASCII) is not needed. Historically though the most important reason for applications and files being stored as binary is that it gave the developer a way to protect their work and make money off of it.  If the application they have written stores the files in a proprietary format (one that only the application can read and write) then no one else can work with the file without licensing the software used to create it.

In the early years, without the presence of the Internet this model worked well.  But once we all became connected and more and more armchair software developers started developing free software, a significant push back started with this model.  People wanted to be able to share documents and not have to pay high licensing fees just to be able to read a letter they sent out to their relatives.  Although a lot of progress has been made in making file formats open and based on community standards, there are still a lot of issues will proprietary file formats, especially in the sciences.  If you think about it, any data you get off of an instrument is stored in a proprietary format – if you want to get the data into Microsoft Excel for instance you have to export it.  In a lot of cases you are not getting all the information that was gathered – but more about that later.

Microsoft is a good example of a company that used a proprietary binary file format for saving documents from all its applications, that has transitioned to a open standard text based format.  In 2000 Microsoft started producing versions of its files (starting with Excel) in what was to become the Open Office eXtensible Markup Language (OOXML), a standard released by ECMA in December 2006 (see http://www.ecma-international.org/publications/standards/Ecma-376.htm).   XML is a text based markup language published by the World Wide Web consortium (http://www.w3.org/XML/) that is used to annotatate text strings using tags.  Hypertext Markup Language (HTML) used in web browsers is a limited version of XML primarily used for presentation.

Comparing the binary Word document format (.doc file) with the new OOXML (.docx file) format and plain text (.txt file) we can see the differences (these files are attached and the bottom of the page).

The Word .doc File Format

The Word .docx File Format

We see that the .doc file is unintelligible in a text reader and has over 22K characters - large considering the file contains only the text ‘Chemical Informatics’.  The .docx file is also unreadable and slightly larger in size – but wasn’t this supposed to be in the OOXML text based format?  It actually is, however the .docx file is actually a folder of XML files that is ZIP compressed into a single file.  If you change the extension of a .docx file to .zip and un-compress it you see the folder of XML documents (that take up a lot of space – a problem with XML).

Finally, if you look inside the ‘document.xml’ file you find the text content.  The other files in the folder are used to store information about the font used, the Word settings, the styles in use and a n image of the file used by the operating system to display a thumbnail of the file content.  The ‘document.xml’ contains the text of the file and other ‘markup’ – the XML – that provides the structure of the file , the layout of the page, and other important information

Of course if you just need the text (i.e. no formatting etc.) then you could use the .txt format.  It's got the same size 21 bytes, as the length of the text (including the carriage return as the end of the sentence).  Sometimes less, is more…