Loading...Loading Impressive and Inspiring Learning Graph...

Database Concepts

If you want to get into databases, master SQL first!

There are two main types of databases, relational and non-relational. Relational databases almost all use SQL, and many non-relational database use a variant of SQL.

SQL stands for structured query language is used to access and manipulate data. The four most basic SQL statements are: SELECT, INSERT, UPDATE, DELETE.   After getting a grasp on basic SQL, go after the boxes in blue. You'll have more knowledge than an average developer, especially the ones that get fancy and try to over use advanced features without mastering the basics.

Relational databases are 'table' based and accessed with SQL. Each table is like a spreadsheet, with columns and rows. The columns are fixed per table. Each column has a data type (integer, string, date, etc).  Normalization is term for designing the tables so minimal data is duplicated (eg, each person in the system is a assigned an address, which allows multiple people to live at the same address).  JOINs are when two tables need to be linked together (eg, give me all people and their addresses in one go).  Indexes can be applied to columns that are commonly searched. They improve performance, sometimes dramatically.  Most non-relational databases rely on indexes for data retrieval.  A special type of index is a unique constraint, meaning for the entire table, that column must have unique values in every row.  A special type of unique index, called a primary key is used to uniquely identify that row throughout the database. Almost all tables should have a primary key, sometimes they are required. The primary key is typically named id with an auto incrementing integer for the data type.

SQL can also perform aggregate queries (SUM, Count, etc) and GROUP BY queries. Aggregate queries work across a range of rows (eg, give the total number of blue bikes). The GROUP BY clause is used to perform aggregate queries on sets of rows (eg, give the total count of each color bike).

DDL (data definition language) is similar to SQL except it is for creating and maintaining the structure of the database - tables, columns, indexes, views etc. All relational databases generally have the same set of features built into them, but the syntax is specific to the platform (MySQL, MSSQL, Postgres, etc).

Advanced features of RDBMSs include views, stored procedures, triggers, etc. These are in RED because they should be used only with a good understanding of how they work in practice vs in theory. It is very easy to needlessly complicate a system or hinder its performance with too many triggers and other DB specific advanced features. Most application frameworks won't cope very well with these fancy features either.

Non Relational databases come in many more flavors than older 'relational' databases. Non relational databases are built to handle a specific goal (high load, distributed data, flexible schema, etc). They may use a subset of SQL or their own unique (proprietary) command set.

In production (which means the live system, not the test system or local development system), additional topics need to be considered to make the system scalable (able to handle real user load) and robust from server failure.

There are also a few universal concepts common to all data storage. Every developer needs to understand what transactions are and why they are important. In fact, I'd say transactions are one of the single most important techniques in a quality software application. Data types, assigned to each column, are common to all systems (character data, integers, decimals, dates, etc) but may need conversion when porting platforms.

The CAP Theorem illustrates the tradeoff between making the data consistent to all users, available to all users, and fault tolerant.

ACID is a standard that applies to transactions (atomic, consistent, isolated, and durable).

Learning Graph by:
Views: 1052
Updated: Jan 3 2017 15:23 UTC

Introduction to SQL - Tutorial for beginners to databases. PART 1

Introduction to SQL - Tutorial for beginners to databases. PART 1 A fun, energetic and clear introduction to SQL and databases for just about anybody to understand! Finally understand SQL without being bored to tears or getting frustrated.

SQL for Beginners. Learn basics of SQL in 1 Hour

SQL for Beginners. Learn basics of SQL in 1 Hour SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations. Watch this Video to learn basics of SQL.

Intro to SQL: Querying and managing data

Intro to SQL: Querying and managing data Learn how to use SQL to store, query, and manipulate data. SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations.

No Learning Graphs have been added yet.