IT 101: Databases

This series of articles is a compilation of the notes I gathered during my programming bootcamp at Green Fox Academy, last year.

You can read the other articles here:

  1. Endpoints
  2. Authentication
  3. Data Flow
  4. Testing

Tables and Fields

  • A database table is composed of records and fields that hold data. Each table in a database holds data about a different, but related, subject.
  • Data is stored in records. A record is composed of fields and contains all the data about one particular item (person, company etc.) in a database. Records appear as rows in the database table.
  • A field is part of a record and contains a single piece of data for the subject of the record. Fields appear as columns in a database table.

CRUD — Create, Read, Update, Delete

  • When we are building APIs, we want our models to provide four basic types of functionality. The model must be able to Create, Read, Update, and Delete resources. Computer scientists often refer to these functions by the acronym CRUD.
  • In a REST environment, CRUD often corresponds to the HTTP methods POST, GET, PUT, and DELETE, respectively. These are the fundamental elements of a persistent storage system.

ORM

Object-relational mapping (ORM) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to construct their own ORM tools.


SQL syntax

Most of the actions you need to perform on a database are done with SQL statements. Statements consist of keywords and references to tables (and possibly fields within) in the database, and all statements start with a keyword. SQL keywords are NOT case sensitive, but it is general practice to write them in ALLCAPS (whereas in MySQL, table names ARE case sensitive). There are slight differences in the exact syntax between the various implementations (e.g. MySQL, SQLite…).

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

There are multiple types of JOIN, not all of which are implemented in all SQL variants.

  • (INNER) JOIN (usually the default type) — returns records that have matching values in both tables
  • LEFT (OUTER) JOIN — Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN — Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN — Return all records when there is a match in either left or right table

Foreign Key

· A FOREIGN KEY is a key used to link two tables together. It is a field (or collection of fields) in one table that refers to the Primary Key in another table.


Relationships

One-To-One

This type of relationship happens when an entity instance exists in a table and it is connected to another entity instance, but only to one. For example, in a school, a student has a student ID and this this relationship is unique: the student can only have one student ID and that student ID can only belong to one student.

One-To-Many

This kind of relationship exists when an entity instance in one of the tables can be associated with multiple records in the other table.

Many-To-Many

In a Many-To-Many relationship, both sides can have multiple instances of the other.

You can read further information about database design on my article here.

Categories Articles

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close