Database by LinuxAcedemy

Database by LinuxAcedemy        1

Links        2

Database design architecting concepts        2

Database design depends on 3 categories        2

Database Concepts        2

Choosing the Database        3

When to use RDB        3

Provider of RDB which provides ACID guarantee        4

Edge case        4

Architecting Database        4

Must haves        4

RDS        5

When to use        5

OLTP        5

OLAP        6

Download and Installation        6

Database        6

Type of database        7

MongoDB        8

TODO        8

Create a database        8

Update database        8

Delete a record        8

Bulk import data        9

Couchbase        9

PostgreSQL        12

Create/Read database        12

Update Table        12

Delete table        12

Drop table        12

Postgres vs MySql        13

Links

https://lucid.app/lucidchart/00cb26cf-d76f-440c-ba1c-32b3b8a1665a/view?page=~ri_7aVGqpaI#

https://www.youtube.com/watch?v=cODCpXtPHbQ&ab_channel=codeKarle

Database design architecting concepts

Database design depends on 3 categories

1. How organized your data is (structured vs non-structured)
2. Query pattern you have
3. Amount of scale that you need to handle

Database Concepts

1. Caching - use Redis
2. Storage - Blob Type - Use S3 + CDN
3. Text searching capabilities - Text search engine - Elastic Search + Solar = Apache Lucene

1. Fuzzy search when someone types wrong spelling accidentally; database has to return the desired result
2. Elastic Search and Solar are Search Engines and not databases

1. Search Engines: potentially data could be lost. It provides availability and redundancy  
2. Database: it gives you a guarantee that data wouldn’t be lost
3. Takeaways:

1. Never keep search engines as your primary source of data
2. primary source data store should be somewhere else; you could load the data using Search Engines to provide searching capabilities

4. Store metric data/ Time series - Open TSDB

1. Application metrics tracking system: apps that are pushing metrics related to their throughput, CPU utilization, and latencies.

1. Now you want to build a system to support the above capabilities - use Time-series Database  

2. The time-series database is an extension of RDB with certain functionalities

1. Regular database: you can update a lot of records, giving you the ability to query random records
2. Metric monitoring system: you will never do random updates

1. You’ll do a sequential update in an append-only mode where first entry T1 and second entry T2 will be greater than T1
2. Read query that you do is bulk with the time range; ex: query for last hour, day, week data.  

5. Data warehouse for analytics - Use Hadoop

1. A large database in that you dump all the data; provide various querying capabilities on top of the data to serve a lot of reports.

1. Not suited for a transactional system; it’s used for offline reporting

2. Analytics for all the business transactions, total orders, geographic location

Choosing the Database

If you have structured data use RDB

• Where you can store data in terms of tables = rows + columns
• ex: web app where you store user ID, email, DOB, City, PhoneNo

When to use RDB

• When you need ACID = atomicity and transactional guarantee

• ex: building a payment system in bank where one person is debiting about into another person’s account
• Query has to work fully and about either has to be debited to not called transactional query

Provider of RDB which provides ACID guarantee

• My Sql
• Oracle
• SQL Server
• Postgres

Edge case

1. When you have relational data but need ACID guarantee // you can choose either relational or non-relational database

1. Because normally you could easily map a structured data into no-sql model

Architecting Database

Must haves

1. Multi Az for disaster capabilities

1. How it works?

1. Primary database in one AZ and Secondary database(exact copy of your primary database) in another AZ
2. If your primary DB shuts down, server then use standby database endpoint by filing over to standby database

1. It’s not used when your primary database is still running

2. How technically it works?

1. You get a endpoint of both of your database
2. EC2 server hooked up with your primary database
3. AWS automatically updated the DNS endpoint to point to another IP address of your standby DB when primary DB shuts down

3. Useful for  - Failover capabilities

1. This is just for disaster recovery and not for improving performance
2. Can’t connect to standby database when the primary database is active.
3. Can’t run your queries on your second database with multi AZ
4. Can’t send your web server traffic to secondary database
5. Your second database simply there for failure

4. Improve performance using read replicas

2. Automated backups
3. Improve RDS performance by read replicas

1. How it works?

1. You have exact replica of your primary database and you can use it for read querying
2. Can be in cross AZ or cross region

2. How tech works

1. With read replica you get your own unique DNS endpoint. So 1 for primary and 1 for RR
2. RR can promoted and make it independent database, and this will break the replication  
3. You do it when doing online processing  where you can queuing to this database without affecting your primary

3. Useful for

1. Scaling read performance
2. Requires automatic backups in order to deploy read replica
3. Multiple read replica supported (5 per each DB instance)

RDS

When to use

• Used for online transaction processing workload- OLTP

OLTP

• Process the data from Transactions in real time
• Ex: when you order something from amazon it has to store order details in table  

OLAP

• Process complex queries with large amount of data to analyze historic data
• ex: net profit for past 3 years

Download and Installation

Wget: download the software

Sudo yum installation: install the software  

Database

• It’s a collection of data
• It’s organized data, and data is organized in a way that we can go and retrieve it

• If the data isn’t organized and can’t retrieve it, we need a database management system that makes it easier to work with data

• MongoDB
• Couchbase
• PostgreSQL
• MSSQL
• MySql

Type of database

1. Flat files

1. Regular file, could have text, comma separator, XML, YMAL

2. Relational

1. It’s a schema-based database

1. Has rows and columns; spreadsheets
2. Always have column numbers and data type of the entires

3. Schemaless // NoSql

1. Key-value pair (json)

Databases

MongoDB

• For NoSql

Steps to installation:

1. Installation MongoDb server
2. Working in a server to create, read, update, and delete database

TODO

Create a database  

• Use employees   // will create employees database 

Create a collection(table)

• Is a logical grouping of document(json pages). It’s like a table  

• db.createCollection(“staff_info”) // create a collection/table 
• db.staff_info.insert({name:”John”,phone:”1234567890”,status:”okay” }) // insert value to the table

• Calling a method on collection
• Insert an array of values

• Show collections // to see list of collections
• db.staff_info.find() // see all the entries, select all

•  db.staff_info.find({name:”John”}) // gives entry named John

Update database

• Update a record in database

• db.staff_info.updateOne({name:”John”}, {$set:{status:”Not oKAY”}})

• Find the name John
• Update the status with that name from Okay to Not oKAY

Delete a record

• db.staff_info.remove({name: “John”}) // remove record named with John
• db.staff_info.drop() // drop the collection/table
• db.dropDatabase() // drop database

• Will delete data in the database

Bulk import data

• Create a database and import data from csv file

• mongoimport -d cityinfo -c cities --ype CSV --file ./cities.csv --headerline

Couchbase

• It’s N1QL(non-first normal form query language) // Nickel
• Stores document in JSON
• Looks like sql

Installation steps:

1. Install couchbase server
2. Start and setup(for login to the server) server
3. Connect to a server

TODO

1. create a bucket

1. It holds a document // S3 bucket
2. The document is object, descriptors for different items

2. Create a document

1. Query: document name/id & JSON Data

3. Index a bucket to query data (query like SQL) // to read bucket

1. To perform actions to look up things

4. Update the value in bucket
5. Clean up bucket

PostgreSQL

• RDBMS Database
• Is a Sql standard compliant and requires a schema

Installaton

• Install Postgre
• Intialize database server
• Enable and start the server/service
• Connect to PostgreSQL

TODO

• Create/Read database

• Create database music; // to create music database
• Create table friends() // to create table with schema
• Insert into friends VALUES (‘John’, ‘123456’, ‘jessy’’) // insert values in the table

• Update Table

• Update friend set status=‘mine’ where name=’John’;

• Delete table

• Delete from friends where name=’John’; // to delete data

• Drop table

• Drop table friends; // to delete table

TASK

• Copy csv data into the table

Postgres vs MySql

Postgres: is an object-relational database management system

• Includes features like table inheritance and function overloading // Key difference in comparison with MySql

MySql: