Key Commands

Database Theory

“Keep what changes abd what stays the same separate”

Keep structure and date separate

SQL

Structures Query Language

interact with a relational database

Relational Database

Data is linked to other data through the use of, unique id and links/ references

Database Management

Databases need to be managed

Users, password access rights

Mostly beyond scope of this course

NoSQL

Store data in Key value Pairs

Similar to accusative arrays

Fast retrieval through hash functions Uses HAsh functions rather ehn

ACID Compliance

ensures transactions are robust

  • atomicity - each transaction is a single unit
  • consistency - only take the database from one valid state to another
  • isolation - Transactions occurring in parallel are result in the same result as transactions in series
  • durability - Once a transaction has occurs it will remain regardless of system failure

Database Table

Place to store data of the same type

Columns are decides on creation

Rows are data that is entered

Unlimited number of rows are available but fixed number of columns.

One to One

One record is associated with one and only one record.

  • one person has one passport

    One to Many

    One record points to many records.

  • A User has many posts
  • A post has one user

Many to Many

Many records are related to many records.

  • One student attends many courses
  • One course is attend by many Students

Identifiers

Each data row has an ID that can be used to reference the data.

Schema

A script used to create a database from scratch, used to define tables.

The script may also contain default/ demo values

Detailed Commands

Data Types

  • integer - whole Numbers
  • real - decimal numbers
  • text
  • varchar (length)- fixed length text
  • date
  • datetime - date and time
  • blob - binary data

Data Modifiers

Auto increment

id integer PRIMARY KEY when inserting values use NULL

Table Operations

Create Table

CREATE TABLE <table name>(
    <column1> <Datatype>,
    <column2> <Datatype>,
    <column3> <Datatype>,
    <column4> <Datatype>,
);

Delete Table

DROP TABLE table1;

Add Data

INSERT INTO table1 VALUES (value1,value2,value3,....);

Updating Records

UPDATE table1 SET column1 = value1 WHERE column2=value2;

Multiple records
UPDATE table1 SET column1 = value1,column2 = value2 WHERE column3=value3;

Deleting Records

DELETE FROM table1 WHERE <column1=value1;

Find Data

SELECT column1,column2,.. FROM table1 WHERE column1=value1;

* is a wildcard can be used in place of fields for all fields

can add order

ORDER BY column1,column2 ASC|DESC ;

Multiple Tables

Select * from table1,table2 where column1=value2 AND column1=table2.column2;