modul:m290:learningunits:lu05:theorie:01

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu05:theorie:01 [2024/09/23 15:29] – vdemirmodul:m290:learningunits:lu05:theorie:01 [2024/10/24 12:50] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU06a - SQL-DDL: DB Management ======+====== LU07a - SQL-DDL: DB Management ======
  
 ===== Learning Objectives ===== ===== Learning Objectives =====
-In this session, we will learn some exciting details about how to properly manage our databases. To achieve this, we need to know more about ...+In this session, we will learn some thrilling details about how to properly manage our databases. To achieve this, we need to know more about: 
 +  - What a db schema / db instance is.
   - How to create database instances.   - How to create database instances.
-  - How to connect to the db-instance. 
   - How to display all currently available instances.   - How to display all currently available instances.
-  - How to delete obsolete databases. +  - How to connect to one specific db-instance. 
-  - How to import prepared databases that are ready for immediate use.+  - how you can delete obsolete or no longer required databases. 
 + 
 +=====  DB Schema vs. DB Instance ===== 
 +A database schema and a database instance are related concepts in the context of a database system, but they refer to different aspects: 
 + 
 +{{:modul:m290:learningunits:lu05:theorie:1545889164421.jpg?400|}} 
 + 
 +==== 1. Database Schema ==== 
 +The schema is the structure or blueprint that defines how data is organized within the database. It describes the database's logical design, including: 
 + 
 +  * **Tables**: The individual entities (e.g., Customers, Orders). 
 +  * **Columns**: The attributes of each entity (e.g., CustomerID, OrderDate). 
 +  * **Constraints**: Rules applied to the data (e.g., primary keys, foreign keys, unique constraints). 
 +  * **Relationships**: How tables relate to one another (e.g., one-to-many, many-to-many relationships). 
 +  * **Views, indexes, triggers, and stored procedures**: Other elements that define how the data is accessed or managed. 
 + 
 +Think of the schema as the blueprint or structure of the database that remains mostly static or changes rarely. 
 + 
 +==== 2. Database Instance: ==== 
 +The instance refers to the actual data stored in the database at a particular point in time. It is the current state of the database, including all of the records or entries inside the tables defined by the schema. 
 + 
 +  * It includes actual rows of data in the tables. 
 +  * The instance can change frequently as data is inserted, updated, or deleted. 
 +  * You can think of the instance as the current snapshot of the database contents, while the schema defines the permanent framework. 
 + 
 +==== Analogy ==== 
 +The schema is like the blueprint of a building (defining rooms, walls, and layout), while the instance is like the current state of the building (furniture, people inside, and their positions, which can change).
  
 ===== Access to the db with Webstorm code editor ===== ===== Access to the db with Webstorm code editor =====
-There are many ways how we can connect to our database installation. One way is from the terminal or commandline. Instead, during the module 290 we are going to use do the management with the code editor WEBSTORM, which is pictured below.+There are many ways how we can connect to our database instance. One way is from the terminal or commandline. Instead, during the module 290 we are going to use do the management with the code editor WEBSTORM, which is pictured below.
  
-  - New connection to our MySQL installation +  - New connection to our MySQL database system 
-  - The already existing connections to our database+  - The already existing connections to our database instance
   - New terminal window to the database to enter sql commands (leads to 4.)   - New terminal window to the database to enter sql commands (leads to 4.)
   - Terminal to the database to enter our SQL commands   - Terminal to the database to enter our SQL commands
   - Execute button that sends the SQL command to the database   - Execute button that sends the SQL command to the database
  
-{{:modul:m290:learningunits:lu05:theorie:webstorm_db_window.png?800|Webstorm db window}}+{{:modul:m290:learningunits:lu05:theorie:webstorm_db_window.png?700|Webstorm db window}}
  
 ===== Commands ===== ===== Commands =====
Zeile 30: Zeile 56:
 After executing this command, we will receive the list of currently installed database instances, as shown in the following picture. After executing this command, we will receive the list of currently installed database instances, as shown in the following picture.
  
-{{:modul:m290:learningunits:lu05:theorie:showdatabases.png?400|Resultset}}+{{:modul:m290:learningunits:lu05:theorie:showdatabases.png?300|Resultset}}
  
 ==== CREATE DATABASES ==== ==== CREATE DATABASES ====
-Source: [[https://www.w3schools.com/sql/sql_create_db.asp| CREATE DB]]+Source: [[https://www.w3schools.com/sql/sql_create_db.asp?200| CREATE DB]]
  
 A new application is basicly constructed within a new enviroment. Therefore we need to create a new database instance for us. To achive this, which is done by the following command syntax. A new application is basicly constructed within a new enviroment. Therefore we need to create a new database instance for us. To achive this, which is done by the following command syntax.
Zeile 39: Zeile 65:
   CREATE DATABASE myDatabase;   CREATE DATABASE myDatabase;
      
-So, if we want to create a new database for a webshop of the customer "Demir", we need to type in: +So, if we want to create a new database for a webshop for our customer "Demir", we need to type in: 
- +
   CREATE DATABASE DemirsDB;   CREATE DATABASE DemirsDB;
   SHOW DATABASES;   SHOW DATABASES;
Zeile 46: Zeile 72:
 After executing both commands, our result-set would look like: After executing both commands, our result-set would look like:
  
-{{:modul:m290:learningunits:lu05:theorie:createshowdatabases.png?900| Create database + update result-set}}+{{:modul:m290:learningunits:lu05:theorie:createshowdatabases.png?300| Create database + update result-set}}
  
 ==== DROP DATABASES ==== ==== DROP DATABASES ====
Zeile 62: Zeile 88:
 After, executing both commands, the result-set would look like: After, executing both commands, the result-set would look like:
  
-{{:modul:m290:learningunits:lu05:theorie:dropshowdatabases.png?400|Drop database + show }}+{{:modul:m290:learningunits:lu05:theorie:dropshowdatabases.png?300|Drop database + show }}
  
 ==== Using databases ==== ==== Using databases ====
 +Most database systems usually have more than one instance (project areas) running, e.g. DIGITECH.CH and GALAXUS.CH, these two database instances (storage areas for different purposes) are running on the same database systems, but organize their data separately. 
 +
 +As a database administrator, we cannot work on different instances at the same time, so we have to choose which instance we actually want to work on. The SQL statement in question is the USE command.
 +
 +As a database administrator, we cannot work on different instances at the same time, so we have to choose which instance we actually want to work on. The SQL statement in question is the USE command.
  
 +{{:modul:m290:learningunits:lu05:theorie:usedatabases.png?300|}}
  
 ==== Vocabulary ==== ==== Vocabulary ====
 ^English ^ Deutsch ^ ^English ^ Deutsch ^
 | Obsolete | veraltet | | Obsolete | veraltet |
 +| result-set | Ergebnismenge |
  
 ---- ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir
  
  • modul/m290/learningunits/lu05/theorie/01.1727098199.txt.gz
  • Zuletzt geändert: 2024/09/23 15:29
  • von vdemir