Databases

  1. Objectives
  2. Materials/Resources
  3. Procedures
  4. Extensions
  5. Evaluation

Objectives

Materials/Resources

Procedures

History

Charles Bachman Fig 1-1

Fig 1-1: Charles Bachman

Databases originated in the 1960s, and took off largely in part because of Charles Bachman. Before Bachman came along, all computing was done through punch cards, and magnetic tape, which meant that serial processing was dominant. Bachman sought out a new way of computing that would be more effective and efficient. According to Wikipedia, "Two key data models arose at this time: CODASYL developed the network model based on Bachman's ideas, and (apparently independently) the hierarchical model was used in a system developed by North American Rockwell, later adopted by IBM as the cornerstone of their IMS product. While IMS along with the CODASYL IDMS were the big, high visibility databases developed in the 1960's, several others were also born in that decade, some of which have a significant installed base today. Two worthy of mention are the PICK and MUMPS databases, with the former developed originally as an operating system with an embedded database and the latter as a programming language and database for the development of data-based software."

Database Lingo

A Database is a collection of records (also called data). In a strictly computer-related context, a database is a structured collection of records or data that is stored in a computer, so that a program can consult it to answer queries. In most database models, records are organized in rows and columns, which are collected in tables. (columns are horizontal, while rows are vertical.) Thus, any data has an address of sorts, given by a location in columns and one in rows in the table. As there are many different ways of organizing data, there are also different database models. Some databases are built specifically on one model, while others are more open.

Since databases contain tables, you find fields in the table in the same way as you would for a spreadsheet program or something similar. For example, if my name is at the coordinates of (2,1), you would go to the second column and to the corresponding field in the first row (see Fig 1-2). (Note that most modern databases are not so simple, and rows are not assumed to be related to one another).

Figure 1-2

Fig 1-2: The above image shows a 3x3 (that is, 3 columns by 3 rows large) table. Cell (or field) with coordinates (2,1) is labeled in grey.

Databases also contain candidate keys. A candidate key is a combination of attributes that can be uniquely used to identify a database record. Each table may have one or more candidate keys.

SQL and Databases

Now you are going to learn how to create databases and tables in a common language for databases, SQL. Follow the tutorials on SQL here.

A Story of "Ted"

Figure 2-1

Fig 2-1: Edgar "Ted" Codd

E.F. Codd was a computer scientist who made significant contributions to the theory of relational databases. In fact, he thought up the Relational Database Theory, which is the model used by most databases today, in comparison to other models, such as the Hierarchical Model and network model.

According to Wikipedia, "Edgar Frank Codd was born in Portland, Dorset, in England. After attending Poole Grammar School, he studied mathematics and chemistry at Exeter College, Oxford, before serving as a pilot in the Royal Air Force during the Second World War. In 1948, he moved to New York to work for IBM as a mathematical programmer. In 1953, angered by Senator Joseph McCarthy, Codd moved to Ottawa, Canada. A decade later he returned to the USA and received his doctorate in computer science from the University of Michigan in Ann Arbor. Two years later he moved to San Jose, California to work at IBM's Almaden Research Center."

"In the 1960s and 1970s he worked out his theories of data arrangement, issuing his paper "A Relational Model of Data for Large Shared Data Banks" in 1970, after an internal IBM paper one year earlier. To his disappointment, IBM proved slow to exploit his suggestions until commercial rivals started implementing them."

Relational Database Theory

Codd's Relational Database Theory became popular in the '80s, although most implementations of the theory were what he considered not true to the principles he set out. Thus, he published twelve rules, which can be found online as 'Codd's Rules'. It includes rules on relationships, treatment of "null values" and other such items, and much more.

A relational database is collection of relations or tables. It can also be defined as a database that conforms to the relational model.  Today the most common database model that is closest to Codd's Database Theory is probably SQL, which was spun off from a project based on the Database Theory but which does not exactly follow the 12 Rules.

Normal Forms

Codd came up with the concept of giving databases normal forms. Normal forms (or NF, for short) provide extra security against anomalies in relational databases. The first three NF were defined by Codd himself. Today, NF may go as high as 6NF, although it is very uncommon.

Normal Forms provide the following benefits:


So how do you convert your relational database into a NF? It varies for each level of NF, and each NF builds on top of the previous. For example, a 4NF has all the requirements of a 3NF plus a few more additional requirements.

First Normal Form

In order to have first normal form (1NF), the table must have at least one candidate key (guaranteeing no duplicate records). In addition, there cannot be any repeating groups. In other words, each column must be single-valued in respect to the type of data.


The following is an example of a simple database, with no normality:

Model
Specs
PC-1000
1.0 Ghz, 512mb RAM, 40gb hd
PC-1250
1.25Ghz, 1gb RAM, 60gb hd
PC-1500
1.5Ghz, 1gb RAM, 120gb hd

Now, here is the same information from the table above, but applying the rules of 1NF:

Model
Spec. 1
Spec. 2
Spec. 3
PC-1000
1.0 Ghz
512mb RAM
40gb hd
PC-1250
1.25Ghz
1gb RAM
60gb hd
PC-1500
1.5Ghz
1gb RAM
120gb hd

Although the above example has no violations towards 1NF, it is badly designed. The ideal solution would be:

Model
CPU Speed
Memory
Hard Drive
PC-1000
1.0 Ghz
512mb RAM
40gb hd
PC-1250
1.25Ghz
1gb RAM
60gb hd
PC-1500
1.5Ghz
1gb RAM
120gb hd

Second Normal Form

Second normal form (2NF) is the second level of database normality. In order for a table to be considered as 2NF, it must first be in 1NF, and all non-key attributes must depend on the entire candidate key, rather than just part of it.

Take the following table as an example of a database in 1NF, but not in 2NF:

                                      Employee's Languages Spoken
Employee
Language Spoken
Location
Christopher Tew
English
Germany
Christopher Tew
Spanish
Germany
Christopher Tew
German
Germany
Robert Davis
English
USA
Nick Reaver
English
China
Nick Reaver
Chinese
China
Richard Moore
Finnish
Finland

The only candidate keys in the above table is {Employee, Language Spoken}. If the table was rewritten into 2NF, it should be split into two tables, an Employees table and a Languages Spoken table:

              Employees
Employee
Location
Christopher Tew
Germany
Robert Davis
USA
Nick Reaver
China
Richard Moore
Finland

                Languages Spoken
Employee
Language Spoken
Christopher Tew
English
Christopher Tew
Spanish
Christopher Tew
German
Robert Davis
English
Nick Reaver
English
Nick Reaver
Chinese
Richard Moore
Finnish

Third Normal Form

Third normal form (3NF) is the third level of database normality. It was the last normal form created by Cobb. In order for a database to be in 3NF, two criteria must be met:


Let's now take a look at a table in 2NF, keeping in mind that the only candidate key is {City, Year}:

                                         City Winners
City
Year
Winner Winner D.O.B.
Round Hill
2000
Chris Tew
24 March 1986
Herndon
2001
Robert Davis
11 July 1983
Purcellville
2001
Chris Tew
24 March 1986
Round Hill
2001
Richard Moore
16 September 1988

Can you see where the breach of 3NF is? Winner D.O.B. is a non-prime attribute, and it is transitively dependent on the candidate key {City, Year}. So why is 3NF important? In the future when the table is expanded, nothing is stopping the same person from having two different birth dates. In order to convert the above table into 3NF, it must be split into two separate tables, a "City Winners" table and a "Players D.O.B." table.

                                       City Winners
City
Year
Winner
Round Hill
2000
Chris Tew
Herndon
2001
Robert Davis
Purcellville
2001
Chris Tew
Round Hill
2001
Richard Moore

                        Players D.O.B.
Player
Date of Birth
Chris Tew
24 March 1986
Robert Davis
11 July 1983
Richard Moore
16 September 1988

Now that the table has been converted to 3NF, it is now future-proof against anomalies.

Database Models

There are two other types of databases that you should know about. The first is called a flat database. This database may not count as a data model because it consists of a single, two-dimensional array of data elements, where all members of a given column are supposed to be similar values, and all members of a row are supposed to be similar and related to one another as well. The second type of database that you should be aware of is known as a hierarchical model. In a hierarchical model, data is organized into a tree-like structure and it contains a sort field to keep the records in a particular order in each same-level list.

Extensions


Evaluation


Objectives
Score
Understood the history and origins of relational database theory.
     /3
Understood the creating of databases in OpenOffice and the usage of SQL.
     /3
Understood and is able to differentiate between 1NF, 2NF, and 3NF.
     /3
Completed an extension (if necessary)
     /1
Total
    /10

[top] | [lessons]