Databases for Beginners

A Gentle Introduction to Databases, SQL, and Microsoft Acces

• 
  

On the surface, a database might seem much like a spreadsheet: it has data arranged in columns and rows. But that is where the similarity ends because a database is far more powerful.

What Can a Database Do?

A database has broad searching functionality. For example, a sales department could quickly search for and find all sales personnel who had achieved a certain amount in sales over a particular time period.

A database can update records in bulk — even millions or more records. This would be useful, for example, if you wanted to add new columns or apply a data patch of some sort.

If the database is relational, which most databases are, it can cross-reference records in different tables. This means that you can create relationships between tables. For instance, if you linked a Customers table with an Orders table, you could find all purchase orders from the Orders table that a single customer from the Customers table ever processed, or further refine it to return only those orders processed in a particular time period — or almost any type of combination you could imagine.

A database can perform complex aggregate calculations across multiple tables. For example, you could list expenses across multiple retail outlets, include all possible sub-totals, and then a final total.

A database can enforce consistency and data integrity, which means that it can avoid duplication and ensure data accuracy through its design and a series of constraints.

What is the Structure of a Database?

At its simplest, a database is made up of tables that contain columns and rows. Data is separated by categories into tables in order to avoid duplication. For example, a business might have a table for Employees, one for Customers and another for Products. 

Each row in a table is called a record, and each cell is a field.

Each field (or column) can be designed to hold a specific type of data, such as a number, text or a date. This is enforced by a series of rules to ensure that your data is accurate and dependable.

The tables in a relational database are linked through a key. This is an ID in each table that uniquely identifies a row. Each table has a primary key column, and any table that needs to link to that table will have a foreign key column whose value will match the first table's primary key.

A database will include forms so that users can input or edit data. In addition, it will have the facility to generate reports from the data. A report is simply the answer to a question, called a query in database-speak. For instance, you might query the database to find out a company's gross income over a particular time period. The database will return to you the report with your requested information.

Common Database Products

Microsoft Access is one of the most popular database platforms on the market today. It ships with Microsoft Office and is compatible with all Office products. It features wizards and an easy-to-use interface that guides you through the development of your database. Other desktop databases are also available, including FileMaker Pro, LibreOffice Base (which is free) and Brilliant Database.

If you are considering a database for a medium to large business, you may want to consider a server database based on Structured Query Language (SQL). SQL is the most common database language and is used by most databases today. 

Server databases like MySQL, Microsoft SQL Server, and Oracle are enormously powerful—but also expensive and can come with a steep learning curve.