Introduction to Relational Databases (RDBMS)IBM
理由・背景
しっかり学ぼうと思います。
感触はそれなりにいいような気がしますね。
特記事項
第1週目 Relational Database Concepts
Fundamental Relational Database Concepts
⬛️シラバス・サマリー
Summary & Highlights
Congratulations! You have completed this lesson. At this point in the course, you know:
The relational model is the most used data model for databases because this model allows for logical data independence, physical data independence, and physical storage independence.
Entities are objects that exist independently of any other entities in the database, while attributes are the data elements that characterize the entity.
The building blocks of a relationship are entities, relationship sets, and crows foot notations.
Relationships can be one-to-one, one-to-many, or many-to-many.
When translating an Entity-Relationship Diagram to a relational database table, the entity becomes the table and the attributes become columns in the table.
Data types define the type of data that can be stored in a column and can include character strings, numeric values, dates/times, Boolean values and more.
The advantages of using the correct data type for a column are data integrity, data sorting, range selection, data calculations, and the of standard functions.
In a relational model, a relation is made up of two parts: A relation schema specifying the name of a relation and the attributes and a relation instance, which is a table made up of the attributes, or columns, and the tuples, or rows.
Degree refers to the number of attributes, or columns, in a relation.
Cardinality refers to the number of tuples, or rows in a relation.
⬛️ 用語
・トポロジー: ネットワークの形だよ · 接続形態を点と線でモデル化したもの。
・カーディナリティ【cardinality】:
カーディナリティとは、数学で基数あるいは濃度という意味の用語。ITの分野では、リレーショナルデータベースにおいてあるテーブルの同一の列（カラム）に含まれる異なる値の数（バリエーション）のことを指すことが多い。
・エンティティ（英：entity）:「実体」のこと。
・attribute 属性
・degree 程度
ちょっとズルい書き方をするとE-R図で出てくる箱のこと
です。
⬛️ Take Away
⬛️ Hands-on Lab : Relational Model Concepts
所感
Introducing Relational Database Products
⬛️シラバス・サマリー
Summary & Highlights
Congratulations! You have completed this lesson. At this point in the course, you know:
There are four types of database topology:
Single tier. The database is installed on a user’s local desktop.
2-tier. The database resides on a remote server and users access it from client systems.
3-tier. The database resides on a remote server and users access it through an application server or a middle tier.
Cloud deployments. The database resides in the cloud, and users access it through an application server layer or another interface that also resides in the cloud.
In shared disk distributed database architectures, multiple database servers process the workload in parallel, allowing the workload to be processed faster. There are three shared nothing distributed database architectures:
Replication. Changes taking place on a database server are replicated to one or more database replicas. In a single location, database replication provides high availability. When database replica is stored in a separate location, it provides a copy of the data for disaster recovery.
Partitioning. Very large tables are split across multiple logical partitions.
Sharding. Each partition has its own compute resources.
There are different classes of database users, who use databases in different ways:
Three main classes of users are Data Engineers, Data Scientists and Business Analysts, and Application Developers.
Database users can access databases through Graphical and Web interfaces, command line tools and scripts, and APIs and ORMs.
Major categories of database applications include Database Management tools, Data Science and BI tools, and purpose built or off the shelf business applications.
Relational databases are available with commercial licenses or open source.
MySQL is an object-relational database that supports many operating systems, a range of languages for client application development, relational and JSON data, multiple storage engines, and high availability and scalability options.
PostgreSQL is an open source, object-relational database that supports a range of languages for client application development, relational, structured, and non-structured data, and replication and partitioning for high availability and scalability
⬛️ 用語
tier: 層
high availability:高可用性のこと。HAと略記されることもある。
⬛️ Take Away
⬛️ Hands-on Lab : Relational Model Concepts
所感
第2週目 Using relational Databases
Designing Keys , Indexes, Constraints
⬛️ 用語
truncate：切り捨てること
⬛️ Take Away
⬛️ Hands-on Lab: Create Tables and Load Data in Db2
Fundamental Relational Database Concepts
⬛️シラバス・サマリー
Congratulations! You have completed this lesson. At this point in the course, you know:
PostgreSQL is an open-source object-relational database management system that you can download and install on your own systems or access on the Cloud.
You can either self-manage a Cloud instance of PostgreSQL or use a managed services provider, including IBM Cloud Databases for PostgreSQL, Amazon RDS, Google Cloud SQL for PostgreSQL, EnterpriseDB cloud, or Microsoft Azure for PostgreSQL.
PostgreSQL includes several options for creating databases and tables, loading and querying data, and importing and exporting data relational databases:
The psql command line interface. You use this CLI to run SQL statements.
pgAdmin. A graphical interface to the database server, which is available as a desktop application or as a web application that you can install on your web servers.
Navicat and Dbeaver. Commercial graphical interface options that you can use to access PostgresSQL, MySQL, and other types of databases.
Cloud vendor tools and APIs.
Using pgAdmin, you can:
Use pg_dump to back up databases and psql to restore them.
Use the Import/Export tool to load data into and export data from tables.
Using views:
You can use views to limit access to sensitive data and simplify data retrieval.
Views can be materialized, which means that the view store the result set for quicker subsequent access.
Materialized views enhance performance because the view is saved and often stored in memory. However, you cannot insert, update, or delete rows in a materialized view, and they must be refreshed before you can see updated data.
⬛️ 用語
Hands-on Lab: Create Tables and Load Data in Db2
⬛️ Take Away
⬛️Hands-on Lab: Normalization, Keys and Constraints in Relational Database
第3週目 MySQL and PostreSQL
MySQL
⬛️ 用語
Workbench:作業台
⬛️ Take Away
⬛️ Hands-on Lab: Create Tables and Load Data in MySQL using phpMyAdmin
Hands-on Lab: Keys and Constraints in MySQL
⬛️シラバス・サマリー
Summary & Highlights
Congratulations! You have completed this lesson. At this point in the course, you know:
MySQL is a free, open-source RDMS that you can download and install on your own systems or access on the Cloud. You can either self-manage a Cloud instance of MySQL or use a managed services provider, including IBM Cloud, Amazon RDS for MySQL, Azure Database for MySQL, or Google Cloud SQL for MySQL.
MySQL includes several options for creating databases and tables, loading and querying data, and importing and exporting data relational databases:
mysql and mysqladmin command line interfaces. You use these CLIs to run SQL statements.
MySQL Workbench. A desktop application for designing, developing, and administering MySQL databases.
phpMyAdmin. An easy to use, third-party web interface for working with MySQL databases.
API calls.
Using phpMyAdmin, you can:
Add and modify columns after you create a table.
Use backup and restore functionality to populate databases.
Use import and export functionality to populate tables and save their data to files.
Create primary keys by defining a primary index on one or more columns.
Use autoincrement to automatically generate sequential numeric data in a column.
When creating foreign keys, you can define ON DELETE and ON UPDATE actions.
MySQL columns are NOT NULL by default.
You can configure a column to only accept unique values.
PostreSQL
⬛️ 用語
⬛️ Take Away
⬛️シラバス・サマリー
Congratulations! You have completed this lesson. At this point in the course, you know:
PostgreSQL is an open-source object-relational database management system that you can download and install on your own systems or access on the Cloud.
You can either self-manage a Cloud instance of PostgreSQL or use a managed services provider, including IBM Cloud Databases for PostgreSQL, Amazon RDS, Google Cloud SQL for PostgreSQL, EnterpriseDB cloud, or Microsoft Azure for PostgreSQL.
PostgreSQL includes several options for creating databases and tables, loading and querying data, and importing and exporting data relational databases:
The psql command line interface. You use this CLI to run SQL statements.
pgAdmin. A graphical interface to the database server, which is available as a desktop application or as a web application that you can install on your web servers.
Navicat and Dbeaver. Commercial graphical interface options that you can use to access PostgresSQL, MySQL, and other types of databases.
Cloud vendor tools and APIs.
Using pgAdmin, you can:
Use pg_dump to back up databases and psql to restore them.
Use the Import/Export tool to load data into and export data from tables.
Using views:
You can use views to limit access to sensitive data and simplify data retrieval.
Views can be materialized, which means that the view store the result set for quicker subsequent access.
Materialized views enhance performance because the view is saved and often stored in memory. However, you cannot insert, update, or delete rows in a materialized view, and they must be refreshed before you can see updated data.
第4週目 MySQL and PostreSQL
⬛️Hands-on Lab: Database Design using ERDs