4 Circles Blog

Databases - Basics

2011-03-29T14:19:00.000-07:00

In the previous post - Databases Introduction we glimpsed at different types of databases and simple concepts of tables. In this section we will look at some basics of databases- tables, inner joins and outer joins. The syntax of SQL used here is for Oracle, however, it is very similar in all other databases like MySQL, MS SQLServer, Postgres, etc..

Tables

We will create two tables by using the SQL statements below:

create table customer (cust_id number(4,0) not null, customer_name varchar2(60) not null);
create table invoice (invoice_id number(4) not null, cust_id number(4) not null , invoice_total number(10,2) default 0.00, paid_ind char(1));

In the first SQL we create the table called Customer with the column names cust_id and customer_name. Observe that we do not want to store null values in each of the columns in the customer table. In the second SQL we create the table invoice, which has its columns and also the cust_id column from the customer table. This is how we delegate a specific invoice is associated to a specific customer.

Observe that one customer can have many invoices and hence we have a one-many relationship between customer and invoice tables. This implies that a customer may not have any invoices also i.e. a one-zero relationship beween the customer and invoice tables.

We can make the above create SQLs complex by specifying the tablespace name (we are using the default tablespace assigned to the user you are logging on) and adding foreign key constraints to the cust_id in the invoice table to the customer table which will eliminate the input of data into the invoice table for which a cust_id is not existent in the customer table.

A simple way to input data into the tables is to use the insert SQL statement which is given below:

insert into customer values (100, 'Paradigm Solutions');
insert into customer values (102, 'Siemens Pvt Ltd');
insert into customer values (104, 'British Telecom');
commit;

Now when we do a simple select from the customer table we get:

CUST_ID          CUSTOMER_NAME
---------------------- ------------------------------------------------------------

100                   Paradigm Solutions

102                   Siemens Pvt Ltd

104                  British Telecom

Now here are the SQL statements for inserting records into the invoice table.
insert into invoice values ( 8442, 102, 5430.00, 'Y');
insert into invoice values ( 8997, 102, 5430.00, 'Y');
insert into invoice values ( 8775, 102, 5430.00, 'N');
insert into invoice values (9876,100,2345.78,'N') ;
insert into invoice values (9886,100,345.00,'N');
commit;

As an exercise, we leave to the reader to retrieve the records inserted into the table invoice.

Inner and Outer Joins

To retrieve customers and invoices for which there are invoices then we do an inner join  where the sql is constructed by joining the column cust_id from the table customer and invoice:

select c.cust_id, customer_name, i.invoice_id, i.invoice_total, i.paid_ind
from customer c, invoice i where c.cust_id= i.cust_id;;

gives results as:

CUST_ID                CUSTOMER_NAME                       INVOICE_ID  INVOICE_TOTAL  PAID_IND
---------------- -------------------------------------------------- ---------------------- ----------------------    --------
100                        Paradigm Solutions                                 9876                     2345.78           N
100                       Paradigm Solutions                                  9886                    345                  N
102                      Siemens Pvt Ltd                                           8775                    5430                 N
102                     Siemens Pvt Ltd                                            8997                    5430                 Y
102                    Siemens Pvt Ltd                                             8442                    5430                 Y

We can make our SQL criteria (the sql after the where keyword) complex by requesting only those which have not been paid by modifying as below:

where c.cust_id= i.cust_id and i.paid_ind ='N'

which will return those invoices which have not paid for the customers who have invoices.

Observe that we have used aliases for table names customer, invoice as c and i respectively. This eliminates any ambigous column names like cust_id being specified in the SQL and also eliminates typing the complete table name when referring the columns in the criteria or in the select.

The above was an example of inner join where we get the records when the exist in both tables. However, if you remember that we do not get the third customer - British Telecom from the table customer as it has no invoices associated it. In order to obtain all the customer names and their invoices whether they exist or not we write an outer join as:

select c.cust_id, customer_name, i.invoice_id from customer c, invoice i where c.cust_id= i.cust_id (+);

returns the results as shown below:
CUST_ID                         CUSTOMER_NAME                     INVOICE_ID
---------------------- ---------------------------------------------------- ----------------------

100                                Paradigm Solutions                                         9876
100                                Paradigm Solutions                                         9886
102                               Siemens Pvt Ltd                                                 8775
102                              Siemens Pvt Ltd                                                  8997
102                              Siemens Pvt Ltd                                                  8442
104                              British Telecom

Observe that in the outer join SQL we specify in the join (+) which is equivalent to writing as:

select c.cust_id, customer_name, i.invoice_id from customer c left outer join invoice i on i.cust_id= c.cust_id ;

The latter method is more readable and specifies whether it is left or right outer join while the (+) indicates already how the tables are joined and the right , left outer is dependent on which side of the = sign the (+) is assingned.

It is nowadays in Object Oriented (OO) programming to understand both of these joins specifically when using an Object Relation Mapping (ORM) tools like Hibernate, topLink etc. Most of the time we are using an outerjoin to get the most relevant information and hence in Hibernate (we shall see in future articles) by default it uses outer join.

Databases - Introduction

2011-01-12T08:16:00.000-08:00

Database
In the information age (which has started in 1940 or so when US census used the first computer), a database is referred to a silo of information which has data stored in a particular way so that stored data is relevant to make business decisions by employees. Databases are a specific type of models. Models are any storage repository (server) which stores data whether it is 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 being used currently.

Non-Relational Databases
Before we move onto relational databases; non-relational databases which are flat files, spreadsheets etc were big in their time. These demanded a lot of sort, find code by programmers 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;
nextRecord
}
... 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 to retrieve the phone number the logic would be the same. As you can already see this is CPU (Central Processing Unit)intensive and there are better ways of storing data, and retrieve 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, Java, C++. An article [1] in reference section gives a short description of OO databases and their advantages and disadvantages. I urge the reader to skim the article, to get introduced to 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 say the last name, first name, address and telephone number of individuals is referred to as a table and each row in the spreadsheet is generally called 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 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 which 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) is 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 need 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 addresses email, business address, physical address, mail box address 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 say SSN, DL # or just a sequence. This is then stored in both tables and then we can 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

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

which will return all the records from both tables 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.

REFERENCES

[1]An Introduction to Object Oriented Databases article.

Hibernate-Secondary Table Annotation..

2010-12-08T10:07:00.000-08:00

When using Hibernate with annotation SecondaryTable one needs to be careful how it is used while using the Discriminator. We assume that there is a table called Inventory which is the parent table which has common elements Id, createDate, modifiedDate, SKU#, 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).

@Entity
@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:

@Entity
@DiscriminatorValue("TV")
@SecondaryTable(name = "TV_INVENTORY")

public class TVInventory extends Inventory {
              private Location loc;      //LOCATION Table entity
           // Other relevant columns, getters setters and methods.
}

In using the above definition and annotations then we get the following error (assuming we are using Oracle DB).

SELECT
<<<< columns from Entities >>>
from
TV_Inventory invento0_,
INVENTORY ainvento0_1_,
LOCATION inventoryl2_
where
invento0_.LOCATION_SEQ=inventoryl2_.LOCATION_SEQ(+)
and ainvento0_.INVENTORY_ID=ainvento0_1_.INVENTORY_ID
and ainvento0_.INVENTORY_ID=?

[10:55:46:218] org.hibernate.jdbc.AbstractBatcher DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[10:55:46:218] org.hibernate.util.JDBCExceptionReporter DEBUG could not load an entity: [com.hibernate.inv.model.aInventory#365746]
[The generated hibernate QUERY......]
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

This can be fixed by NOT using secondaryTable. So now the TVInventory class is re-defined as:

@Entity
@DiscriminatorValue("TV")
@Table(name="TV_INVENTORY")
public class TVInventory extends Inventory {

            private Location loc; //LOCATION Table entity

}

and it works fine without errors. Now assume that for each TV in our inventory we have features of TV stored in another table, say TV_FEATURES. Then we can use the SecondaryTable annotation as follows:

@Entity

@DiscriminatorValue("TV")
@Table(name="TV_INVENTORY")
@SecondaryTable(name="TV_FEATURES")
public class TVInventory extends Inventory {
                   @OneToMany(fetch = FetchType.EAGER)
                  @JoinColumn(name="INVENTORY_ID")
                   private List tvFeatures;
}

The TVFeature class is defined as below:

@Entity
@Table(name="TV_FEATURES")
public class TVFeature {
       ///getters and setters for columns
}

In the above scenarios we are not using any inheritance but joining the tables together on the inventory_id and defining the relationship between the TVInventory and TVFeature objects. So when using inheritance secondaryTable things can get a little sticky in hibernate.

Binary Digits.

2010-04-03T17:00:00.000-07:00

Consider the number 783 which can be obtained by:

7	x	10²	=700
8	x	10¹	= 80
3	x	10⁰	= 3
783₁₀	Total		= 783₁₀

So in decimal system we are counting how many 10s, 100s, 1000s, 10000s etc.. and then multiplying the the number depending on where it is positioned and adding them up.

If we were to do the same to the base 8; then the above table will look as follows:

1	x	8³	=512
4	x	8²	=216
6	x	8¹	=48
7	x	8⁰	=7
1467₈	Total		= 783₁₀

Now the number 783₁₀ ~= 1467₈. This means that any number (like 783) can be written to any number base even its own. So in computers we are interested in binary as the switches in circuits are either ON or OFF. With this in mind then the number above (783) would be written in binary as follows:

1	x	2⁹	=512
1	x	2⁸	=256
0	x	2⁷	=0
0	x	2⁶	=0
0	x	2⁵	=0
0	x	2⁴	=0
1	x	2³	=8
1	x	2²	=4
1	x	2¹	=2
1	x	2⁰	=1
1100001111₂	Total		= 783₁₀

This means that:
1100001111₂ ~=783₁₀

Here is a sequence for those who have not figured out it is powers of 2 i.e.

2⁰ = 1 = 1₂
2¹ = 2 = 10₂
2² = 4 = 100₂
2³ = 8 = 1000₂
2⁴ = 16 = 10000₂
2⁵ = 32 = 100000₂
2⁶ = 64 = 1000000₂
2⁴ = 128 = 10000000₂
and so on...

So now, gifting somebody $32.00 in decimal will be written in binary as $100,000₂ and giving $64.00 = $1,000,000₂ which is a LOT OF MONEY... :).

As the joke goes: There are 10 kinds of people, the kind that understand binary numbers and those who do not.

4 Circles releases Word Frequency..

2010-01-10T06:03:00.000-08:00

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

This application counts the number of words in your essay and tells you the frequency of each word in a table. This application as can be seen in the screen shot above 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 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 in the text box and click on the button to get the frequency in the grid. Please download the software here:
Word Frequency