Objectives
- Learn what a database is and what the parts of a database are.
- Learn who Edgar F. Codd ("Ted") was, and what the Relational Database Theory he created is.
- Learn about database normality.
Materials/Resources
- The Internet (for researching)
- Open Office (for experimenting)
Procedures
History

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).

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"

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:
- Reduces data redundancy
- Increases table integrity
- Simplifies maintenance and expandability
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 |
|
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:- It must be in second normal form (2NF)
- All attributes that aren't candidate keys must be transitively dependent on a candidate key
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
- Make a database using a spreadsheet application of five of your friends. Include their address, e-mail, phone number, and first and last name. Experiment to find the most organized and efficient way to display the data.
- Investigate the different types of databases online or in a book, and be able to explain the differences in each.
- Research SQL and be able to give a brief but detailed history about it.
- Create a database with at least 4 columns and 4 rows, in 3NF.
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 |