Database Essentials

Course

Intro Video

Photo of Michael McClaren

Michael McClaren

Linux Training Architect I in Content

Length

06:43:09

Difficulty

Beginner

Videos

17

Hands-on Labs

5

Course Details

In this course, we will start with the basics of what a database is, then we will explore the different types of databases from flat file to relational. We will install some of the more popular database systems that are available on Linux and see how to work with data in those systems.

Interactive Diagram: https://interactive.linuxacademy.com/diagrams/databaseessentials.html

Syllabus

Introduction

Course Introduction

About the Training Architect

00:00:21

Lesson Description:

Hi, I am Mike and I am happy to bring you this course covering Database Essentials. This is a brief introduction to me and my background.

Getting to know Databases

The Foundation

What is a Database?

00:03:03

Lesson Description:

When we use the term "Database", what do we really mean? If we get a vision in our head of a complex data diagram that has tons of interconnected bits and pieces, we are overthinking what a database really is. A database is simply an organized collection of data. The way in which the data is organized is such that it can be accessed and processed to produce useful results, but it is just data. In this lesson, I will use a couple of examples of data, and we will take a quick peek at the reason that we need a database management system to make working with the data simpler.

Types of Databases

00:07:01

Lesson Description:

In this lesson, we will be looking at the types of databases that we will cover in this course. These are not all of the types of databases that there are, and I will be making the case that configurations and flat files are also forms of databases.

How Databases Are Used

00:04:02

Lesson Description:

In this lesson, we will do a quick overview of how databases are used in everyday dealings. From the backend of webstores for online shopping to how they are used for common GPS applications that you use to get from point A to point B. This is a really short talk on this and we are just going to mention some of these in some cases. If any of these pique your interest, I encourage you to do some searching on your own and learn the deeper application methods behind the many disciplines that make up data science.

Databases in Practice

Flat Files and Configurations

Flat Files You Know and Love

00:05:30

Lesson Description:

In this short lesson, we will take a look at some of the most common forms of flat files: YAML, JSON, and DB. I will quickly take a look at the best way to store data in a flat file and also discuss the limitation of flat files for complex data storage.

Flat Files Used for Configuration

00:03:38

Lesson Description:

In this short lesson, we will be looking at some common configuration files and data patterns. The ability to quickly see the format of data can help us visualize the data so that we can convert it from one format to another. This will be the final lesson in this section and from here, we will move into actual Database Management Systems.

MongoDB

MongoDB Installation

00:03:05

Lesson Description:

In this lesson, we will be installing MongoDB. The following are the commands used in the lesson: cat << EOF > /etc/yum.repos.d/mongodb-org-4.0.repo [mongodb-org-4.0] name=MongoDB Repository baseurl=https://repo.mongodb.org/yum/redhat/7/mongodb-org/4.0/x86_64/ gpgcheck=1 enabled=1 gpgkey=https://www.mongodb.org/static/pgp/server-4.0.asc EOF sudo yum install -y mongodb-org sudo service mongod start grep 27017 /var/log/mongodb/mongod.log

MongoDB: Working with Data

00:13:11

Lesson Description:

In this lesson, we will be working with data in MongoDB. We will cover the Create Read Update and Delete (CRUD) operations as well as doing a bulk insert. The following are the commands used in this lesson: show dbs db.createCollection("staff_info") db.staff_info.insert({name:"jenny",phone:"8675309",status:"jessies"}) db.staff_info.find() If you find that Git is not installed: sudo yum install -y git git clone https://github.com/linuxacademy/content-db-essentials.git cd content-db-essentials Bulk insert (as shown in the lesson): mongoimport -d cities -c cityinfo —type CSV —file ./cities.csv —headerline

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

01:00:00

Couchbase

Couchbase Installation

00:03:52

Lesson Description:

In this lesson, we will be installing Couchbase. Part of this lesson will involve using the Couchbase web interface to set up the administrator account. We will also be setting some limits in the /etc/security/limits.conf file so that Couchbase can operate on the server without issue. The following are the commands used in this lesson: wget https://packages.couchbase.com/releases/6.0.0/couchbase-server-community-6.0.0-centos7.x86_64.rpm sudo yum install -y couchbase-server-community-6.0.0-centos7.x86_64.rpm Edit the limits file: vim /etc/security/limits.conf Add lines: * soft nofile 75000 * hard nofile 75000 Start the server: sudo /opt/couchbase/bin/couchbase-server -- -noinput -detached Then access the server GUI via: http://serverip:8091 Access the Couchbase console interface: /opt/couchbase/bin/cbq -e http://localhost:8091 -u=Administrator

Couchbase: Working with Data

00:09:58

Lesson Description:

In this lesson, we will be performing the standard Create Read Update Delete (CRUD) operations in Couchbase server. The following are the commands used in this lesson: export PATH=$PATH:/opt/couchbase/bin couchbase-cli bucket-create -c 127.0.0.1:8091 --username Administrator --password Omgpassword! --bucket example-bucket --bucket-type couchbase --bucket-ramsize 512 Log in to CBQ: /opt/couchbase/bin/cbq -e http://127.0.0.1:8091 -u=Administrator Commands that are used in CBQ: INSERT INTO `example-bucket` ( KEY, VALUE ) Values ( "testdoc",{"name": "Jenny","type": "Jessies"} ) RETURNING META().id as docid, *; CREATE PRIMARY INDEX `example-bucket-index` ON `example-bucket`; SELECT * FROM `example-bucket` WHERE type= "Jessies"; UPDATE `example-bucket` set type = "mine" WHERE name= "Jenny"; DELETE from `example-bucket` WHERE name= "Jenny"; Delete the bucket: couchbase-cli bucket-delete -c 127.0.0.1:8091 --username Administrator --password Omgpassword! --bucket example-bucket

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

01:00:00

PostgreSQL

PostgreSQL Installation

00:04:21

Lesson Description:

In this lesson, we will be installing PostgreSQL. We will be installing version 11 and we will need to disable updates via the CentOS base repo since PostgreSQL is available via the main repo in CentOS 7. To disable the updates, it is necessary to modify the file /etc/yum.repos.d/CentOS-Base.repo and add an exclude to the [updates] section. exclude=postgresql* The following commands are used in this lesson: sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql11-server.x86_64 sudo /usr/pgsql-11/bin/postgresql-11-setup initdb sudo systemctl start postgresql-11 sudo systemctl enable postgresql-11

PostgreSQL: Working with Data

00:11:49

Lesson Description:

In this lesson, we will be working with the PostgreSQL installation that we have completed previously. We will need to sudo to root and then assume the postgres identity in order to access the psql command line. sudo su su -l postgres Once we have become the postgres user: sudo yum install -y git git clone https://github.com/linuxacademy/content-db-essentials.git The following are the commands used in this lesson: psql l create database music; c music CREATE TABLE friends( name varchar(80), phone varchar(80), status varchar(80) ); dt INSERT INTO friends VALUES ('jenny','8675309','jessies'); select * from friends; DELETE from friends WHERE name = ‘jenny’; DROP TABLE friends; c postgres DROP DATABASE music; l create database city_data; c city_data create table city_info( id serial NOT NULL, LatD numeric, LatM numeric, LatS numeric, NS text, LonD numeric, LonM numeric, LonS numeric, EW text, City text, State text ) ; COPY city_info(LatD,LatM,LatS,NS,LonD,LonM,LonS,EW,City,State) from '/var/lib/pgsql/content-db-essentials/cities.csv' DELIMITER ',' CSV HEADER;

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

01:00:00

MSSQL

MSSQL Installation

00:04:23

Lesson Description:

In this lesson, we will be installing MSSQL server on CentOS 7. We will be installing the free Express version via the command line configuration tool. In addition, we will be installing the necessary tooling to interact with the server via the command line. The following are the commands used in this lesson: sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo sudo yum install -y mssql-server sudo /opt/mssql/bin/mssql-conf setup sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo sudo yum install -y mssql-tools unixODBC-devel export PATH=$PATH:/opt/mssql-tools/bin

MSSQL: Working with Data

00:12:49

Lesson Description:

In this lesson, we will be exploring how to perform Create Read Update and Delete (CRUD) operations on an MSSQL server installation on CentOS 7. We will also look at how to restore a database from a backup (.bak) file. The following are the commands used in this lesson: select name from sys.databases select table_name from information_schema.tables create dabase users use users create table example ( name varchar(50), phone varchar(50), status varchar(50) ) insert into example values('jenny','8675309','jessies') update example set status='mine' where name='jenny' restore database AdventureworksDW2107 from disk = '/var/opt/mssql/AdventureworksDW2017.bak' with move 'AdventureworksDW2017' to '/var/opt/mssql/data/Adventureworksdw2017.mdf', move 'AdventureworksDW2017_log' to '/var/opt/mssql/data/AdventureworksDW2017.ldf'

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

01:00:00

MySQL/MariaDB

MySQL/MariaDB Installation

00:03:45

Lesson Description:

In this lesson, we will be installing MariaDB. This database server is functionally the equivalent of MySQL and is a drop-in replacement for MySQL. MariaDB is a community developed fork of MySQL released under the GNU GPL and its lead developer is the founder of the original MySQL.

MySQL/MariaDB: Working with Data

00:09:23

Lesson Description:

In this lesson, we will be working with data in the MySQL/MariaDB command line interface. We will be undertaking the standard Create Read Update and Delete (CRUD) commands as well as performing a bulk insert of CSV data. The following are the commands used in this lesson: create database music; use music create table users( name varchar(50), phone varchar(50), status varchar(50) ); insert into users values('jenny','8675309','jessies'); select * from users; update users set status='mine' where name='jenny'; select * from users delete from users where name='jenny'; drop table users; drop database music; For bulk insert: git clone https://github.com/linuxacademy/content-db-essentials.git sudo cp ./content-db-essentials/cities.csv /var/lib/mysql create table city_info( LatD int, LatM int, LatS int, NS char, LonD int, LonM int, LonS int, EW char, City varchar(50), State varchar(50) ) ; LOAD DATA INFILE '/var/lib/mysql/cities.csv' INTO table city_info Fields terminated by ',' ENclosed by '"' Lines terminated by 'n' Ignore 1 ROWS;

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

01:00:00

Where Do We Go from Here?

Recommendations for Next Steps

00:02:50

Lesson Description:

In this video, we will wrap up the course by looking at where to go next.