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.

 

My top tips for new programmers

Over the years I have written quite a lot of code. These are the tips that I wish I had known when I had started out and would have saved me a lot of head scratching and restless nights:

  • Create a “to do” list. Always write down your tasks and keep it updated as you work through the problem. Your memory is limited and you can often forget something which may be critical later.
  • Always use variables and don’t hardcode. Generally this will make your life easier and it makes the application more flexible.
  • Code comments are your friend. Many times I returned to code I have written and never known what a function or piece of code is expected to do. If I had taken some time out to put some comments into the code it would have saved me hours troubleshooting issues.
  • Always break a big problem into a series of small problems. Sometimes a big problem can feel daunting and be more difficult than it seems. Break the problem down into smaller blocks and then solve each block one at a time. It’s okay to skip a block and return to a previous block as it may give you clues to fixing the skipped block.
  • Errors are not a bad thing they tell you where the problem is. This will help you to solve the error more easily and doesn’t mean you’re a bad programmer. Only be worried when something goes wrong and you don’t get an error message.
  • Never reinvent the wheel. It is more than likely someone has faced the same problem as you and it is okay to copy their solution.
  • A difficult problem doesn’t mean you’re a failure. There will be times when you face a problem which you find hard to grasp. It is perfectly acceptable to find things difficult.
  • Know who to contact in your ogranisation to ask questions. When you have very little information and need more details it is infinitely better when you have a list of go to people for all the applications you will be supporting.
  • 99% of your work will never be seen by anyone. Your solution may not necessarily be the most elegant one but try to keep it simple.
  • Prototypes do not have to always end up in production. Building things allows us to learn from coding. What you learnt is better than whether it ends up in production. Sometimes it is better to start again than fix problems with some poorly written code.
  • Read code of more experienced programmers. When you get passed some code don’t just deploy it. Read it and review it. You might learn something or you might pick up a problem which will save everyone time.
  • Don’t stop learning. Programming can change quite rapidly. If you don’t invest in learning new things you might end up encountering it for the first time when you are debugging something in production

Feel free to comment with any additional tips which you may have.

 

2017 Blogging Plan

This year I am going to get back to blogging regularly and this time I really mean it. I have neglected to blog regularly due to procrastination and not being happy with most of the blog posts I had written. I will try to blog at least every fortnight and will, subject to my work and family, try to blog more often. This schedule should be easy to maintain and not become a burden and lead to me going on another blogging sabbatical (hiding he he).

This year I plan on levelling up my PowerShell, C# skills and learning more about Cyber Security.

I have seen some of the cool stuff some people are doing with PowerShell and would love to automate everything in my life. This will free me up to do more Production DBA work and enable me to increase my project workload. I hope that I can try and produce some scripts which I can add to the DBATools.io project and give them back something for their awesome community work.

My C# journey is mainly so I can increase my programming expertise and I want to try and take part in some competitive programming and undertake some programming challenges. Currently my knowledge level is insufficient to do this but I want to learn more and hope that learning design patterns, algorithms and object orientated programming will let me fill in these gaps and allow me to have some programming fun.

Lately I have become interested in Cyber Security as I want to understand both the methodology used by hackers and the people preventing them. I find the mindset, creativity and folklore around all of this to be fascinating and hope that I can learn a few things along the way.

I hope that my audience will enjoy my 2017 journey and will help keep me accountable.

Thanks for your support.