Database

Database is collection of data which is related by some aspect. Data is collection of facts and figures which can be processed to produce information. Name of a student, age, class and her subjects can be counted as data for recording purposes.

Database Management System

Database Management System or DBMS in short, refers to the technology of storing and retriving users data with utmost efficiency along with safety and security features. DBMS allows its users to create their own databases which are relevant with the nature of work they want. These databases are highly configurable and offers bunch of options.

A database management system stores data, in such a way which is easier to retrieve, manipulate and helps to produce information.

Some of the more popular relational database management systems include:

1. Microsoft Access
2. Filemaker
3. Microsoft SQL Server
4. MySQL
5. Oracle

Microsoft Access

This is the main screen you'll see when opening up Access to view an existing database. The outer part is the database management system and it's menu, the middle part is the actual database. In this example, the database is called "dateSite" and has 20 tables. If you were to open a different database, the name of the database would be different and you would see different tables, but the available options would be the same (i.e. Tables, Queries, Forms, Reports, Macros, Modules, Open, Design, New).

Microsoft SQL Server

Microsoft SQL Server is a more robust database management system than Access. While Access is better suited to home and small office use, SQL Server is more suited to enterprise applications such as corporate CRMs and websites etc. The above screen is what you see when you open SQL Server through Enterprise Manager. Enterprise Manager is a built-in tool for managing SQL Server and its databases. In this example, there are 6 databases. Each database is represented down the left pane, and also in the main pane (with a "database" icon).

About Database Tables

Database tables will most likely be the area you'll become most familiar with after working with databases for a while. Now, before we go ahead and start adding tables to our new database, let's have a look at what a database table actually is

What is a Table?

In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns. A row contains each record in the table, and the column is responsible for defining the type of data that goes into each cell. Therefore, if we need to add a new person to our table, we would create a new row with the person's details.

Programatically

The following is an example of creating a new table. Note that we are specifying the name of the table, the name of each column, and the data type of each column. More parameters can be added to this example if your requirements are more specific.

CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
DateCreated dateTime
)

User Interface

Database management systems usually have a "Design View" for creating tables. Design view enables you to create the names of each column, specify the type of data that can go into each column, as well as specifying any other restrictions you'd like to enforce. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date.

More parameters can be added against each column if you require them. For example, you could specify a default value to be used (in case the field has been left blank by the user).

Structured Query Language (SQL)

You can use a programming language called SQL to insert the data (we could also have used SQL to create the database and tables if we'd wanted to). One advantage of this is that you can save your SQL script for re-use. This could be handy if you need to insert the data into multiple databases. It's also useful to create scripts that insert "lookup" data - this is generally a base set of data that never changes (such as Countries, Cities, etc). If you ever need to rebuild your database, you can simply run your ready made script against it (which saves you from manually re-entering the data).

Standards for SQL exist. However, the SQL that can be used on each one of the major RDBMS today is in different flavors. This is due to two reasons: 1) the SQL command standard is fairly complex, and it is not practical to implement the entire standard, and 2) each database vendor needs a way to differentiate its product from others. In this tutorial, such differences are noted where appropriate.

This SQL programming help site lists commonly-used SQL statements, and is divided into the following sections:

SQL CommandsBasic SQL statements for storing, retrieving, and manipulating data in a relational database.

SQL FunctionsCommonly-used math functions in SQL.

SQL JOINDifferent ways of retrieving data from more than one table.

SQL String FunctionsCommon string functions used in SQL.

SQL Date FunctionsCommon date functions used in SQL.

Data Definition Language (DDL)Commdns used to create, modify, and delete database objects.

SQL Constraint:Commands that limit the type of data that can be inserted into a column or a table.

For each command, the SQL syntax will first be presented and explained, followed by an example. By the end of this tutorial, you should have a good general understanding of the SQL syntax, and be able to write SQL queries using the correct syntax. My experience is that understanding the basics of SQL is much easier than mastering all the intricacies of this database language, and I hope you will reach the same conclusion as well.