There are tools available to help us create and manage databases. Many of them hide the complexities of the underlying data structures. For instance, Ruby on Rails abstracts all database access and makes most direct access unnecessary, as do component technologies such as Enterprise JavaBeans and lots of object-oriented frameworks. For windows, Access does it. However, we still need to understand how relational databases work.

Relational databases store data in tables, which consist of rows and columns. Each table has at least one column, and each column has a type associated with it, which limits the type of data that can be stored in the column. Most tables have keys. One of the keys is designated to be the primary key. A table can be linked to another table using a foreign key.

• Structured Query Language (SQL)
  SQL provides mechanisms for most kinds of database manipulations. The SQL Server DataBase Management System (DBMS) that is bundled with Visual Basic takes the Relational DataBase Management Systems (RDBMS) like Oracle or IBM DB2.
• Data normalization
  Normalization rules insist that data is organize efficiently, and without duplication or redundancy, in order to reduce the potential for anomaly during data operations. They require each table to have a primary key column as a unique identifier and permissible data types must be defined for all other columns. This determines whether cells in the column may contain text or numbers, and whether cells may be left empty or not.
• Data (referential) integrity
  Another aspect of RDBMS, it requires that records referred in another table cannot be deleted unless the reference is the first deleted. In other words, we can't delete a row from a table if the foreign key of another table references the row.

A database management system (DBMS) is software provided by the database vendors. Micorsoft Access, Microsoft SQL Server, Oracle Database, Sybase, DB2, INGRES, MySQL, and PostgreSQL are all DMBSs or, more correctly, relational DBMSs (RDBMSs).

The DBMS provides all the basic services required to organize and maintain the database:

• Moving data to and from the physical data files as needed.
• Managing concurrent data access by multiple users, including provisions to prevent simultaneous updates from conflicting with one another.
• Managing transactions so that each transaction's database changes are an all-or-nothing unit of work. In other words, if the transaction succeeds, all database changes made by it are recorded in the database; if the transaction fails, none of the changes it made are recorded in the database.
• Support for query language, which is the system of commands that a database user employs to retrieve data from the database. SQL is the primary query language used with relational DBMSs.
• Provisions for backing up the database and recovering the database from.
• Security mechanisms to prevent unauthorized data access and modification.

The Structured Query Language (SQL) is a language designed specifically for communication with databases. SQL is the industry-wide standard language used by virtually all database systems.

Databases allow collections of data to be stored in an organized manner. Most modern Database Management Systems (DBMS) store data in related tables, so are called Relational DBMS (RDBMS). The data stored inside databases can be examined and manipulated by SQL commands.

SQL commands are known as queried and utilize special keywords that can be used both to add data to a database, or extract details of data contained within a database. There are not many keywords so SQL is simple to understand. However, despite its apparent simplicity, SQL is a powerful language.

There are number of ways that SQL queries may be sent to a database to deposit or extract data:

• Directly input through an integral SQL-client application that is part of the DBMS package - this is the most straight forward method.
• Input through a third-party SQL-client application - this method communicates with the database via an intermediate software driver. On Windows systems these are typically Open DataBase Connectivity (ODBC) data source drivers.
• From a script - often found on web servers to dynamically communicate with a database using scripting languages such as perl or php.
• From an Integrated Development Environment (IDE) - programmers using IDEs, such as Microsoft Visual Basic, can build programs that incorporate SQL queries to a database.

Popular software to execute SQL queries:

• Oracle
  The Oracle DBMS is popular and widely used in commerce. It has a management tool called Enterprise Manager that contains a feature named SQL Worksheet.
• IBM DB2
  The DB2 DBMS is a powerful multi-platform system that is supplied with a comprehensive suite of tools. This includes the Query Management Facility (QMF) that allows SQL queries to be entered for execution against a database.
• Microsoft SQL Server
  Microsoft SQL Server DBMS products are popular on computers running the Windows OS. They offer an impressive range of features - including a tool called SQL Query Analyzer that can be used to make SQL queries in a database.
• Microsoft Access
  Access is the popular database program supplied as part of the MS Office suite. It is popular with Office users on stand-alone PCs and small networks. It includes a Query Designer tool that allows SQL queried to be made against an open database.
• Microsoft Visual Basic
  Visual Basic also includes a Visual Data manager that can be used to enter SQL queries against a database via an ODBC Data Source.
• MySQL
  The world's most popular open-source database server is the freely available MySQL DBMS product that is supplied with an integral SQL-client.

Relational databases are the most commonly used type of database. Their theoretical basis is relational algebra. We do not need to know the relational theory but we need to understand some basic database concepts. For example, let's think about the database for users registered on a site.

• Tables
  Relational databases are made up of relations, which are tables. Table has a name, a number of columns, each corresponding to a different piece of data and rows that correspond to individual users.

• Columns
  Each column in the table has a unique name and contains different data. Additionally, each column has an associated data type.

• Rows
  Each row in the table represents a different user. Because of the tabular format, each row has the same attributes. Rows are also called records or tuples.

• Values
  Each row consists of a set of individual values that correspond to columns. Each value must have the data type specified by its column.

• Keys
  We need to have a way of identifying each user. Names usually aren't a good way of doing this because often we have common name. In general, using UserID is a good choice because an artificially assigned identification number can be guaranteed to be unique.

• Schemas
  The complete set of table designs for a database is called the database schema. It is similar to a blueprint for the database. A schema should show the tables along with their columns, and the primary key of each table and any foreign keys. A schema does not include any data, but we might want to show sample data with our schema to explain what it is for.

• Relationships
  Foreign keys represent a relationship between data in two tables. Three basic kinds of relationships exist in a relational database. They are classified according to the number of elements on each side of the relationship. Relationships can be either one-to-one, one-to-many, or many-to-many.

  • A one-to-one relationship means that one of each thing is used in the relationship.
  • In a one-to-many relationship, one row in one table is linked to many rows in another table.
  • In a many-to-many relationship. many rows in one table are linked to many rows in another table.

Database design can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views. Database design is the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the database management system (DBMS).

Here are some of the rules:

• Avoid storing redundant data
• Use atomic column values
• Choose sensible keys
• Think about what we want to ask the database
• Avoid designs with many empty attributes

Microsoft SQL Server 2008 is well integrated with Visual Basic 2010. So, we can easily create a new database from within the IDE.

1. Make a new Forms Application in Visual Basic 2010 with a name CarsApplication

2. In the Solution Explorer window, right-click on the project icon and then choose Add => New Item to launch Add New Item dialog.

3. Select the Service-based Database icon, type CarsDatabase.mdf in the name field. Then, click the Add button.

   
   
   

4. At this time, we just click Cancel.

   
   
   

1. Right-click the CarsDatabase.mdf icon that we've just added and then choose Open From the context menu to open the Database Explorer window.

   
   
   

2. Examine the CarsDatabase.mdf icon in Database Explorer and we should see it has below it to indicate we're connected to that database.

3. Right-click the CarsDatabase.mdf icon in Database Explorer then choose Close Connection from the context menu and we'll see the icon change to have a red X below it.

4. Right-click the CarsDatabase.mdf icon in Database Explorer then choose Refresh to reconnect to the database.

   
   
   

5. To test the connection, choose Modify Connection and then click its Test Connection button. Then, we'll see the Connection Succeeded confirmation dialog appear.