Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu05:theorie:01 [2024/09/25 09:20] – vdemir
+++ modul: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 =====
 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 display all currently available instances.
   - How to connect to one specific db-instance.
   - how you can delete obsolete or no longer required databases.
-  - How to import pre-pared databases that are ready for immediate use.
+=====  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 =====
@@ Zeile 18: / Zeile 44: @@
   - Execute button that sends the SQL command to the database
-{{:modul:m290:learningunits:lu05:theorie:webstorm_db_window.png?600|Webstorm db window}}
+{{:modul:m290:learningunits:lu05:theorie:webstorm_db_window.png?700|Webstorm db window}}
 ===== 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.
-{{:modul:m290:learningunits:lu05:theorie:showdatabases.png?400|Resultset}}
+{{:modul:m290:learningunits:lu05:theorie:showdatabases.png?300|Resultset}}
 ==== 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.
@@ Zeile 39: / Zeile 65: @@
   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;
   SHOW DATABASES;
@@ Zeile 46: / Zeile 72: @@
 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 ====
@@ Zeile 62: / Zeile 88: @@
 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 ====
+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 ====