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:
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.
Diana Bernardo 