What is a database?

A database is a place to store your application data. An example of a simple data storage system would be your windows folder explorer. You store files inside folders on a computer. It can be a simple text file, xml file, binary file, MS Access database, SQL Server database, Oracle database etc. In other words, your database need not be SQL Server or Oracle - it can be just plain text files.

However, there are several key differences between storing data in text files and database server based databases. Text file based database storage may be good enough for small applications which deal with very less amount of data. Most of the applications developers followed this approach before database systems like FoxPro, MS Access, SQL Server etc became popular.

Data storage and retrieval become very efficient if you store very large volume of data in text files.

The popular database systems in the market are SQL Server and Oracle along with few others. In the coming chapters, you will learn how the database systems evolved and how to use them.

Database, tables, records and fields

A database is a collection of all data required for an application.

What is a Table ?

- Tables are part of database.
- A database is composed of several tables.
- You need to create separate tables to store different type of data. For examples, if you have a School Management Software, you may need to create the following tables:

Students - to store list of all students
Teachers - to store list of all students
Attendance - to track the attendance of all students
MarkList - to store the mark list of all students

What is a Record?

A record represents one entry in a Table. A table can have any number of records.

If you have a "Students" table to store the student information, a record in the table represent a student. To add a student, you will add a record to the "Students" table. To delete a student from the software, you will delete a record from this table.

What is a Field ?

A field is a column in the table. A record is a collection of fields. All records in the same table will have the same set of fields.

If you have the "Students" table, you may have the following fields:

Name - to store the name of the student
Address - to store the address
DateofBirth - to store the date of birth of the student
RegistrationDate - to store the date on which the student registered.
etc.If you add a field to the Table, it is applicable to all records in the same table. In the above example, all records in the "Students" table will have the same 4 fields.

Summary

A database a collection of Tables
A Table is a collection of Records.
All records in the same table will have the same fields.
In most cases, an application will have one database which has several tables.

Different database systems

First generation database systems

These are file based systems. Even though they are structured databases, they are using files in the backend. Each table is stored as a separate file. There is no relation between files (tables).

DBase
Clipper
Foxbase
FoxPro

Among these, FoxPro was promoted by Microsoft and is still available in the market. The new form of FoxPro is called 'Visual FoxPro'.

The above systems allow data storage as well as programming against the data. For example, using FoxPro, you can save data as well as create fully functional FoxPro applications (This feature is not available in modern database systems like SQL Server).

Second generation database system

Microsoft introduced MS Access as a database system to compete with other industry leading database systems. MS Access is still file based, but all tables in a database is merged into one file. It is easy to move database into another place and all files are safe within the database.

MS Access allow you to store data as well as write fully functional applications. You can create rich applications with windows forms using MS Access.

Third generation database systems

SQL Server, Oracle, Sybase etc belong to this category. Even though data is still saved as file, you do not deal with them as files. You will always use an application called 'database server' to deal with the data. When you want to store or retrieve data, you will access the database server to do this job. You have to use a different application (like C#, VB.NET, C++) etc and communicate with the database server using some technology like ADO.NET.

In most cases, you will not even know where the database file is located. Instead, you will just need to know how to connect to the database server, user id, password etc. Once you are connected to the database server, you will send commands to the server to manipulate data.

Database servers understand a specific language called SQL.

What is SQL?

Modern database systems will not allow you to directly access the database files. Instead, you must access an application called 'database server' and send commands to the server. This server will understand the commands and perform appropriate action.

In order for the database server to understand the commands, you must use a specific language. This language is called 'Structured Query language' (SQL).

What is the need for a specific language for database access?

There are several database servers exist now (For example, SQL Server, Oracle, Sybase etc). If each of these systems have their own protocols and commands to communicate with them, it will be very hard for programmers to learn each one of them. This is the primary reason a common language was introduced. All database systems are required to understand this common language (SQL).

SQL is an ANSI (defined by American National Standards Institute) standard computer language for accessing and manipulating database systems. All ANSI compliant SQL systems should support the language rules defined by ANSI.

The bottom rule is, if you know SQL, you can work with any modern database systems.

SQL Extensions

All relational database systems are required to support the standard SQL. In addition to the standard SQL, each of the database systems support additional commands (extensions to SQL). If you are using any extensions provided by SQL Server, it may not work with Oracle and vice versa.

Definitions of SQL on the Web

You can find several definitions for the term 'SQL' in the web. Some of the popular definitions are shown below. You may search in Google for the term "define:SQL" to find more definitions.

Here is some of the definitions of SQL:

- Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard.

- SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a database.

- An acronym for Structured Query Language, this is a standard method of conveying information to and from a database

- The standardized query language for requesting information from a database.

- Stands for "Structured Query Language", and can be pronounced as either "sequel" or "SQL". It is a query language used for accessing and modifying information in a database. Some common SQL commands include "insert", "update", and "delete". The language was first created by IBM in 1975 and was called SEQUEL for "Structured English Query Language." Since then, it has undergone a number of changes, with the a lot of influence from Oracle Corporation.

CRUD Operation

All database operations can be broadly classified into 4 categories:

1. Insert data (Create)
2. Get existing data (Read)
3. Modify existing data (Update)
4. Delete data (Delete)

This set of tasks are called CRUD.

The term CRUD stands for Create, Read, Update, Delete which corresponds to the basic database operations mentioned above.

If you are working on a database application, you may be asked to implement the CRUD tasks for the commonly used tables (Entities). So be sure that you know what is meant by CRUD !

For example, if you are developing a student management software, you may have a table called 'Students'. Most probably, you will have to implement the following screens:

1. Screen to add a student
2. Screen which displays all the existing students
3. Screen to edit and modify existing student
4. Screen to select and delete a student.

Did you notice that the above operations match with the CRUD tasks?

In this case, the 'Student' represents an ENTITY. You will learn more about entities in upcoming chapters.

Examples of SQL Statements

As you already know by now, SQL is the language used to communicate with database systems. To execute commands on a database, you will compose SQL statements and send to a database or database server. The database system will parse the SQL statement and execute.

What is SQL Statement ?

An SQL statement represents a set of commands that can be executed by a database system. Here is a sample SQL statement:

select name from employee where age > 20

The above SQL statement will find the name of all employees from the EMPLOYEE table whoes Age is above 20. The words marked in blue color are keywords.

Let us break the statement into 3 sections:

select name - SELECT is a keyword and NAME represents the database field to select.

from employee - FROM is a keyword and EMPLOYEE represents the database table to select data from.

where age > 20 - WHERE is a keyword and AGE > 20 is the condition based on which the records are selected from the table.

Basically, the select statement has the following syntax:

SELECT [FIELD 1, FIELD 2, ...] FROM [TABLE NAME] WHERE [CONDITION]

The WHERE condition is optional. If there is no WHERE condition, then the sql statement will return all the records from the table.

In the next chapter, you will learn about the other types of SQL statements.

Sample SQL statements

Assume that you have a database table called 'EMPLOYEE' with the following fields:

1. Id
2. Name
3. Address
4. Salary

The below examples demonstrate the sql statements to perform the CRUD operations.

INSERT

INSERT INTO Employee (Id, Name, Address, Salary) VALUES (1, 'John', '8900 Research Park Dr', 1200)

The above statement will insert a new record into the employee table. The name of the employee used in the above example is 'John'.

UPDATE

UPDATE Employee SET Name = 'Mr. John', Address = '', Salary = 1400 WHERE id = 1

DELETE

DELETE FROM Employee WHERE id = 1

The above statement deletes the record of employee whoes Id is 1.

DELETE FROM Employee

There is no WHERE condition in the above statement and it will delete all records from employee table.

SELECT

SELECT Id, Name, Address, Salary FROM Employee where ID = 1

The above sql statement finds the record of the employee with the ID = 1.

SELECT Id, Name, Address, Salary FROM Employee

In the above statement, there is no WHERE condition. So, it will return all records from the employee table.