Skip to main content

Databases - Introduction

In the information age (which started in 1940 or so when the US census used the first computer), a database is referred to as a silo of information which has data stored in a particular way. This makes stored data relevant to employees in order to make business decisions. Databases are a specific type of model. Models are any storage repository (server) which stores data whether it is a relational database, object-oriented database, flat or just files.

In this article, we confine ourselves to relational databases like MySQL, MS SQLServer20xx, Oracle, Sybase, MS Access and HSQLDB. These are the most common relational databases currently in use.

Non-Relational Databases
Before we move onto relational databases we will first discuss non-relational ones. These demanded a lot of sort to find code by programmers in order to retrieve the data to the users. Consider the US phone book and the number 307-456-7892. In order to search the phone book to retrieve who is associated to this phone number, the code (pseudo-code) would look something like:

.... set up code
do while (!eof(PhoneBook)) {
if (recordPhoneNumber EQUALS '3074567892') then
get out of the DO WHILE;
... grab the record's last name and first name and send it back.

This means that for every phone number, we will go through reading the file. Of course, if we had the file sorted by phone numbers a priori then it will be very easy to retrieve the name. Now, if we were given the last name of the individual, then retrieving the phone number would hold the same logic. As you can already see this is CPU (Central Processing Unit) intensive and there are better ways of storing data and retrieving them in less time.

Object Oriented Databases
Object Oriented(OO) Databases store objects. Objects have attributes and methods. These can be retrieved with OO languages like SmallTalk and Java, C++. An article [1] in the reference section gives a short description of OO databases including their advantages and disadvantages. I urge the reader to skim through said article to get familiar with OO databases.

Relational Databases
A relational database is two-dimensional and stores data in rows with the columns being attributes. It is akin to a spreadsheet. A spreadsheet (or a file) which stores particular data by the last name, first name, address and the telephone number of individuals is referred to as a table and each row in the spreadsheet is generally called a record.

Consider our example in the flat file section where we need to retrieve the person associated with a telephone number. Now the pseudo code is reduced to a query in Structured Query Language (SQL) as:

SELECT * FROM person WHERE telephoneNumber = 3074567892;

The database engine then searches the table to find the person for the record with the telephone number 3074567892 and returns the record. Now if there is no record matching the number then it will return zero records.

In a database, a table can have index files which are files that sort the table on a specified column or concatenation of columns. When the query is sent to the table then the index file (in our example, the telephoneNumber Index file) will be used if it exists. If there is no index file then we are back to a full scan. Now, if we were given the last name and were asked to retrieve the phone number, then the query is basically the same except for the where clause i.e.

SELECT * FROM person WHERE lastName ='COOK';

and the database engine will utilize the lastName index file for the above query.

Primary Id
Each table stores information regarding a primary unit in a structured way. For instance, a person can have many types of email addresses, business addresses, physical addresses, mail box addresses and sometimes a permanent address. This automatically lets a database designer think in terms of having a table which stores addresses and a table called "say person" which stores the last name, first name etc of the individual.

To join the person table and the address table we define a unique identification for each person such as SSN, DL # or just a sequence. This is then stored in both tables and we are then able to join the two tables to retrieve information of the person and their addresses. In other words, we relate the person table to the address table on the key sequenceNumber. Now the query will look like this:

SELECT * FROM person, address WHERE person.sequenceNo = address.sequenceNo AND person.telephoneNumber = 3074567892

This code will return all the records from both tables; that is if they match in both tables for the specific telephone number.

Index files are created on primary keys generally to speed the process of querying (searching).

Now that the storage of data (models) has been introduced, we move onto other topics in programming and analysis. We now move into some SQL for creating, inserting data and also joins of tables in Databases Basics.


[1]An Introduction to the Object Oriented Databases article.


fair said…
Well presented and very informative. From whitepaper to book?

Popular posts from this blog

Hibernate-Secondary Table Annotation..

When using Hibernate with annotation, SecondaryTable needs to be used carefully while using the Discriminator. We assume that there is a table called Inventory which is the parent table that has common elements including Id, createDate, modifiedDate, SKU#, and productType.
The other tables, which are children of the Inventory table, are TV_Inventory, DVD_inventory etc.. In this example, we consider TV_Inventory which inherits from Inventory (in terms of objects).

@Table(name = "INVENTORY")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "PRODUCT_TYPE", discriminatorType = DiscriminatorType.STRING, length = 20)
@SequenceGenerator(name = "InventorySequence", sequenceName = "INV_SEQ", allocationSize = 1)

public abstract class Inventory {
 // getters and setters with other annotations for the columns and relevant methods.

Consider the child object associated with the table TV_INVENTORY. The outline is shown below:


4 Circles releases Word Frequency..

4 Circles released its application 'Word Frequency v1.1'.

This application counts the number of words in your essay and tells you the frequency of each word. This application is simple to use and helpful for bloggers, writers, journalists, students and speech writers. The application has 1 box for the text to be copied and pasted (left hand side) while clicking the button will count the total number of words in the essay.

This application is written in JDK 1.6, and runs on JRE 1.6.x or above. The user needs to copy the text and place it in the text box and click on the button to get the frequency in the grid. Please download the software here:
Word Frequency

Iphone & Ipad Tips

Tips for using iPhone & iPad - 1
1.One of the most useful tool of an Iphone or an Ipad is the ability to take a screenshot of your screen. One example of its usefulness is taking a screenshot of an image or article online so it can be view later without needing internet access. To take a ScreenShot of whatever is currently on your screen hold down the home button and the lock button at the same time for about 2 seconds. You should see a flash and a screenshot of your screen will be in your camera roll.

2. Double-clicking the home button brings up the Multitasking bar. From here you can switch between running apps or completely exit out of an app.

3. Creating folders on the home screen can help save some space. It also allows you to group similar apps together, like games. To create folders on the home screen press down on an app until the apps start shaking, then decide what two apps you what in the same folder and drag one of the apps onto the other one. The two apps will merg…