search

hayden-kim24 / Case_Database_IBJ

Case Database for International Bridges to Justice
0 stars 0 forks source link

Self-Training:SQL & Database Management #5

Open hayden-kim24 opened 2 weeks ago

hayden-kim24 commented 2 weeks ago

Overview

Database Tasks

  • Store Data

  • Form relationships among data

  • Filter Data

  • Search Data

  • Perform CRUD operations (Create, Read, Update, Delete)


A day in the Life of a Database Engineer


Database

Systematic Data

  • Identifiable features or attributes

  • Stored in entities

Types of Databases

How to Store Databases

  • Local Machines

  • Cloud Hosting

    • Easy to maintain

    • Low Cost


Evolution of Databases

Flat Files (1970 - 1990s)


Hierarchical Database Systems (1970-1990s)

  • One parent to many children (so basically a tree with root & leaves)


Network Databases

  • Multiple parents to multiple children


[Most Common] Relational Databases (1980s, E.F Codd: Hierarchical + Network)

What is a Relational Database?

  • Most common

  • SQL

  • Data stored in tables

  • Columns -> attributes of data

  • Unique ID & foreign key system

Object-oriented Databases (1990s)

  • Work with Object-oriented languages like Javascript, C++, etc

NoSQL Databases

  • Great with Unstructured Data

  • Types

    • Document

    • Key-value

    • Wide-column

    • graph





































Relational Databases

Primary Key

  • Unique Key for Identification

Foreign Key

  • Refers to primary key in another table to establish relationships

Characteristics

  • Data Consistency

  • Stored SQL access procedures

  • Handle Locking & Concurrency

Relational Database (static)

Object Oriented Databases | Graph Databases (Nodes & Edges)   |   Document Databases (JSON) | Classic Table Databases   |  



Overview Database Tasks Store Data Form relationships among data Filter Data Search Data Perform CRUD operations (Create, Read, Update, Delete)

A day in the Life of a Database Engineer

Database Systematic Data Identifiable features or attributes Stored in entities Types of Databases How to Store Databases Local Machines Cloud Hosting Easy to maintain Low Cost

Evolution of Databases Flat Files (1970 - 1990s)

Hierarchical Database Systems (1970-1990s) One parent to many children (so basically a tree with root & leaves)

Network Databases Multiple parents to multiple children

[Most Common] Relational Databases (1980s, E.F Codd: Hierarchical + Network) What is a Relational Database? Most common SQL Data stored in tables Columns -> attributes of data Unique ID & foreign key system Object-oriented Databases (1990s) Work with Object-oriented languages like Javascript, C++, etc NoSQL Databases Great with Unstructured Data Types Document Key-value Wide-column graph

Relational Databases Primary Key Unique Key for Identification Foreign Key Refers to primary key in another table to establish relationships Characteristics Data Consistency Stored SQL access procedures Handle Locking & Concurrency Relational Database (static) Object Oriented Databases Graph Databases (Nodes & Edges)

Document Databases (JSON) Classic Table Databases

NoSQL Databases Characteristics of NoSQL Databases Flexibility Easily Scalable Can store data in various formats Not only structured data Types of NoSQL Databases Document databases Key-value databases Graph databases Handles Big Data Characteristics Complex Grow exponentially Advantages Various types of data (structured, semi-structured, & unstructured data) More data power (more powerful problem solving problem) More data insights (provide more unique insights) Cloud Hosting Advantages: No Infrastructure, maintenance, & storage costs Many services available More affordable Business Intelligence (BI) Analyze Data + Extract Info to make decisions

Intro to SQL Functionalities: CRUD Create Read Update Delete

Types of SQL sublanguages & Commands Data Definition Language (DDL) Create: Define Data in your Database Create Storage Objects Alter: Modify the structure of a table U can add primary key! Separate command Drop: Remove a data object Truncate: Empty the table Comment “--”: To comment on the code Data Manipulation Language (DML) Insert: Add data to data object Delete: Delete data from data object Data Query Language (DQL) Select: Retrieve Data Data Control Language (DCL) Grant: Grant access to data Revoke: Remove access to data Transaction Control Language (TCL) Commit: Rollback: Advantages of SQL User-friendly (little coding skills to use) Standard Langauge for all available relational databases Portable Can be run on ANY hardware Handle both database creation and management Process large amounts of data quickly and efficiently DBMS (Database Management System) for SQL MySQL PostgreSQL Oracle Microsoft SQL Server

SQL Syntax NOT case sensitive, but convention is to use capital letters

Example CREATE DATABASE sample_database;

CREATE TABLE sample_category;

INSERT INTO sample_category (col_ID, first_name, last_name, …) VALUES (value1, value2, value3, …)

UPDATE sample_category SET date_of_birth = ‘2000-10’12’ WHERE ID = 02;

DELETE FROM sample_category WHERE ID

SELECT first_name, last_name FROM sample_category

Tables / Relationships / Entity / Objects Columns v. Record Each col -> data type (int, date, …) Record -> complete info about a unique id Data Types String Char v. Varchar Varchar -> more flexible Numeric Tinyint v Bigint Date & Time Binary CLOB (Character Large Object) BLOB (Binary Large Object) Domains Values that can be accepted Primary Key Unique key Constraints Key Constraints Domain Constraints Referential Integrity Constraints Logical Database Structure One-to-one relationships One-to-many relationships Many-to-many relationships No Multi-value Entries To avoid complciations when referring to foreign keys

Attributes Candidate key attributes A single attribute to form a unique value for each row Composite key attributes Two or more attributes to form a unique value Alternative Key Not selected as primary key

SQL Data Types SQL Cheatsheet Numeric Data Types

String Data Types Char Varchar Tinytext Text

Database Constraints NOT NULL & DEFAULT

CREATE TABLE Customer ( customer_id int NOT NULL, Customer_name varchar(255) DEFAULT “John Doe” );

Default value for city column “ Harrow” Make sure to create a database BEFORE creating a table

Update v. Alter Update: Change the data Alter: Change the data structure

DROP TABLE v. DELETE FROM v. TRUNCATE TABLE Drop: Delete the entire table Delete from: Delete data points but not the datastructure Truncate Table SQL Arithmetic Operators

SQL Comparison Operators (basically Boolean) NOT EQUAL (<> or !=) BETWEEN # AND # check if num is in the # and # IN (“a”, “b”) matches “a” or “b”

SQL Lab

CREATE DATABASE cm_devices;

CREATE TABLE devices (device_ID INT PRIMARY KEY, device_name VARCHAR(50), price DECIMAL);

DESCRIBE cm_devices;

SHOW columns FROM devices;

SHOW tables;

ALTER TABLE stock ADD PRIMARY KEY (device_ID);

INSERT INTO customer (username, full_name, email) VALUES("Custom001", "John Johnson", "J.johnson@email.net");

Show every data SELECT * FROM customer;

Show one column SELECT id FROM customer;

ALTER TABLE address ALTER COLUMN postcode SET DEFAULT “HA97DE”;

DROP TABLE address;

INSERT INTO customers VALUES (3, “John Doe”, “24 Carlson Road”);

INSERT INTO customers(id) VALUES (3);

UPDATE customers SET name = “John”, address = “E street” WHERE id = 1;

DELETE FROM customers WHERE last_name = “Millar”;

SELECT customer_salary, num_2 AS sum_col FROM numbers;