General

Quick introduction to SQL, databases and relational theory

Many people find it daunting when they think about Databases and SQL. I know I found it very scary in the beginning and my early experiences with Database developers left me feeling very small and I thought that maybe I was not good enough to succeed in this field.

However, I was luckily given a quick 30 minute overview from one of the senior developers and he gave me a whole load of terms to research and understand. This gave me the foundation of my Database career and I hope that I can provide the same information to whomever reads this post.

What is a database?

A database is an organised collection of data. Databases are used to store all sorts of information like academic records, medical history, sports statistics and many many more areas.

Why use databases?

The main reasons for using databases are
• Data consistency and ability to enforce data rules
• Ability to easily retrieve large amounts of data
• Ability to manage security
• Ability to open up and share the data to more users

In the past one of the main criticisms about databases has been that they were slow but improvement in both hardware and software have eliminated this issue.

What are tables, rows and columns?

A table is similar to a spreadsheet where the data is held in an organised and structured way which is comprised of columns and rows.

Each column contains a set of similar types of data such as post codes, dates of birth or colours

A row is simply data or information for the columns. For example, if the column is “Grades” each row may be comprised of “A-E”.

What is null?

A null is a missing or unknown value. It is not a zero, empty text string or one or more blank spaces.

Nulls occur when a value is not added to a column when the record is made.

What is normalisation?

Normalisation is the process of organising data in tables and columns to both reduce the amount of  data redundancy and to improve the data integrity in the database.

What is a primary key?

These are a unique attributes which identifies each row. These are generally a number increasing in a sequential manor but they can be comprised of other data. The primary key will identify that record throughout the database and helps maintain data integrity. An example of a primary key is a Customer ID.

What is a foreign key?

When you want two tables to have a relationship with one another normally this is done by replicating the primary key into another table which thus creates a parent-child table relationship. By enforcing the relationship the referential integrity is maintained.  A classic primary and foreign key example is Customer and Order details.

What are relationships?

These are how you connect information between tables and they compromise of three types

  • One to one
  • One to many
  • Many to Many

What is a view?

A view is a virtual table which is comprised of one or more base tables. It is not a physical table as it retrieves information from base tables.

What is an index?

An index is an object which helps speed up the retrieval of data. It is specified on one or more columns and there are quite a few different types of indexes. A simple way of thinking about an index is to think about the index or contents page of a book which helps direct you to the correct page without searching through each page individually.

 

Leave a Reply