SQL and relational databases - introduction and basic concepts

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Requirements: Basic IT knowledge, spreadsheets Author: HTL Pinkafeld – IF/IT

1. What is a database?

1.1 Definition

A database is a structured, electronic collection of data that is stored permanently and can be used by one or more applications.

A database management system (DBMS) is the software that manages access to this data - i.e. enables storage, search, modification and deletion.

Anwendung 1 ─┐
Anwendung 2 ─┤→ DBMS → Datenbank (physische Speicherung)
Anwendung 3 ─┘

1.2 Why not just use files?

1.3 Database models at a glance

1960er: Hierarchisches Modell  (Baumstruktur, z.B. IBM IMS)
1970er: Netzwerkmodell         (Graph-Struktur, CODASYL)
1970s: Relational Model (Tables, Edgar F. Codd) -- Standard today
1990er: Objektorientiertes DB  (Objekte wie in OOP)
2000er: NoSQL                  (Document, Key-Value, Graph, Column)
2010er: NewSQL / Hybrid        (SQL + NoSQL-Skalierung)

Note: The relational model has been the standard for structured data for over 50 years. SQL is the language for this.

2. History of database systems

2.1 Milestones

2.2 Edgar F. Codd (1923–2003)

Edgar Frank Codd was a British-American computer scientist at IBM. His paper, published in 1970, founded the relational database model - one of the most important works in the history of computer science.

Codd’s core ideas:

• Data is stored in relations (tables)
• Relationships between data are established using keys
• A mathematically based language (relational algebra) describes all operations

3. The relational model – basic concepts

3.1 Relation (table)

A relation is a two-dimensional table with tuples (rows) and attributes (columns):

-- Relation: EMP
-- EMPNO  | ENAME  | JOB       | SAL
-- -------+--------+-----------+------
-- 7369   | SMITH  | CLERK     |  800
-- 7499   | ALLEN  | SALESMAN  | 1600
-- 7839   | KING   | PRESIDENT | 5000

3.2 Important terms

3.3 Key Types

Superkey: Any attribute set that uniquely identifies tuples
Candidate key: Minimum super key
Primärschlüssel:      Ausgewählter Kandidatenschlüssel (NOT NULL, eindeutig)
Foreign key: Attribute that refers to the PK of another relation

CREATE TABLE DEPT (
    DEPTNO  NUMBER(2)   PRIMARY KEY,
    DNAME   VARCHAR2(14),
    LOC     VARCHAR2(13)
);

CREATE TABLE EMP (
    EMPNO   NUMBER(4)   PRIMARY KEY,
    ENAME   VARCHAR2(10),
    DEPTNO  NUMBER(2)   REFERENCES DEPT(DEPTNO)  -- Foreign key
);

3.4 Referential integrity

The foreign key ensures that no orphaned references are created:

-- Error: Department 99 does not exist
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (9999, 'TEST', 99);
-- ORA-02291: integrity constraint violated - parent key not found

-- Error: There are still employees in department 10
DELETE FROM DEPT WHERE DEPTNO = 10;
-- ORA-02292: integrity constraint violated - child record found

4. The 12 rules according to Edgar F. Codd

In 1985, Codd formulated 13 rules (Rule 0-12) that a system must fulfill in order to be considered "relational":

Rule 0 – basic rule

A relational system must manage its data exclusively through its relational capabilities.

Rule 1 – Information Rule

All information is presented exclusively as values ​​in tables. No hidden pointers.

Rule 2 – Guaranteed Access

Each value can be accessed by: table name + primary key value + column name.

Rule 3 – Systematic ZERO treatment

NULL means "unknown" or "not applicable" - regardless of 0 or empty string.

-- Korrekt: IS NULL / IS NOT NULL
SELECT * FROM EMP WHERE COMM IS NULL;

-- Wrong: = NULL always results in no lines!
SELECT * FROM EMP WHERE COMM = NULL;

Rule 4 – Database description on a relational basis

The schema (data dictionary) itself is saved as a relation and can be queried using SQL.

-- Oracle Data Dictionary abfragen
SELECT TABLE_NAME FROM USER_TABLES;
SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';

Rule 5 – Comprehensive communication

There must be a complete language covering DDL, DML, transactions and rights. → SQL

Rule 6 – Refreshable Views

Views that are theoretically updateable must actually be able to be updated.

Rule 7 – Quantity Related Operations

INSERT, UPDATE and DELETE must be applicable to sets of tuples.

-- One statement affecting multiple lines
UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 20;

Rule 8 – Physical Data Independence

Changes to physical storage must not affect the logical schema.

Rule 9 – Logical Data Independence

Changes to the logical schema (e.g. new columns) must not break existing applications.

Rule 10 – Integrity Independence

Constraints (NOT NULL, UNIQUE, FK) are defined in the database itself, not in the application.

Rule 11 – Distributive Independence

The query language must work the same regardless of whether data resides on one or multiple systems.

Rule 12 – No evasion

There must be no way to circumvent constraints through low-level access.

In practice: No commercial DBMS fully complies with all 12 rules. Oracle does most of these very well.

5. RDBMS at a glance

5.1 Commercial Systems

5.2 Open source systems

5.3 SQL dialects in comparison

-- Aktuelle Zeit
SYSDATE                                   -- Oracle
GETDATE()                                 -- SQL Server
NOW()                                     -- MySQL / PostgreSQL

-- First 5 lines
SELECT * FROM EMP WHERE ROWNUM <= 5;      -- Oracle (klassisch)
SELECT * FROM EMP FETCH FIRST 5 ROWS ONLY; -- Oracle 12c+ / Standard
SELECT TOP 5 * FROM EMP;                  -- SQL Server
SELECT * FROM EMP LIMIT 5;               -- MySQL / PostgreSQL

6. Oracle Database – Overview

6.1 Version history

6.2 Schema and Users

In Oracle, a schema is tied to a user:

-- Als SYSDBA: neuen Benutzer anlegen
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;

-- As another user: Address table with schema prefix
SELECT * FROM scott.emp;

7. The SQL language and its subareas

7.1 Overview

SQL
├── DQL  -- Data Query Language
│         SELECT                        -- Daten abfragen
│
├── DML  -- Data Manipulation Language
│         INSERT, UPDATE, DELETE        -- Change data
│
├── DDL  -- Data Definition Language
│         CREATE, ALTER, DROP           -- Strukturen definieren
│
├── DCL  -- Data Control Language
│         GRANT, REVOKE                 -- Rechte vergeben
│
└── TCL  -- Transaction Control Language
          COMMIT, ROLLBACK, SAVEPOINT   -- Transaktionen steuern

7.2 ACID – Transaction Properties

-- Example of a transaction in PL/SQL
BEGIN
    UPDATE konten SET saldo = saldo - 500 WHERE knr = 1001;
    UPDATE konten SET saldo = saldo + 500 WHERE knr = 2002;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;

8. Database Design – Normal Forms

8.1 Why normalization?

Poor design leads to anomalies:

Unnormalized table:
-- BESTID | KUNDE | ADRESSE           | ARTIKEL  | PREIS
-- -------+-------+-------------------+----------+------
-- 1      | Maier | Wien, Hauptstr. 1 | Laptop   |  999
-- 1      | Maier | Wien, Hauptstr. 1 | Maus     |   25  -- Redundanz!
-- 2      | Maier | Wien, Hauptstr. 1 | Tastatur |   49  -- Redundanz!

-- Update anomaly: Change address → 3 lines need to be changed
-- Delete anomaly: Delete last order → customer data is lost
-- Insert anomaly: Entering a new customer without an order → not possible

8.2 First normal form (1NF)

Condition: All attribute values ​​are atomic (no repeating groups, no sets).

Verletzung:   TELEFON = '0676/12345, 01/98765'  -- multiple values!
1NF compliant: Separate TELEPHONE table with one number per line

8.3 Second Normal Form (2NF)

Condition: 1NF + each non-key attribute is fully functionally dependent on the entire PK.

PK: (BESTID, ARTIKELNR)
ARTICLE NAME depends only on ARTICLE NO. -- violates 2NF!
Loesung: ARTIKEL in eigene Tabelle auslagern.

8.4 Third Normal Form (3NF)

Condition: 2NF + no non-key attribute is transitively dependent on the PK.

EMP: EMPNO → DEPTNO → DNAME
DNAME haengt transitiv von EMPNO ab -- verletzt 3NF!
Solution: swap out DEPT table with DEPTNO, DNAME, LOC.

8.5 Normalization steps

Unnormalisiert
     │  atomare Werte, keine Wiederholungsgruppen
     ▼
1. Normalform (1NF)
     │  volle Abhaengigkeit vom gesamten PK
     ▼
2. Normalform (2NF)
     │  keine transitiven Abhaengigkeiten
     ▼
3. Normalform (3NF)   -- sufficient for most applications

9. Entity-Relationship Model

9.1 Basic elements

The ER model (Chen, 1976) is a graphical database modeling tool:

Entitaet:   Ein Objekt der realen Welt    -- Rechteck
Attribut:   Eigenschaft einer Entitaet    -- Ellipse / Spalte
Beziehung:  Verbindung zwischen Entitaeten -- Raute / Linie

EMP ──(N)── arbeitet_in ──(1)── DEPT

9.2 Cardinalities

M:N relationships are resolved using an intermediate table:

-- STUDENT --< EINSCHREIBUNG >-- KURS
CREATE TABLE EINSCHREIBUNG (
    STUDENT_ID  NUMBER REFERENCES STUDENT(ID),
    KURS_ID     NUMBER REFERENCES KURS(ID),
    PRIMARY KEY (STUDENT_ID, KURS_ID)
);

10. The SCOTT scheme

10.1 Overview

The SCOTT schema is the classic Oracle example schema - used for training since the 1980s:

SCOTT-Schema
├── EMP (14 employees)
├── DEPT     (4 Abteilungen)
├── SALGRADE (5 Gehaltsklassen)
└── BONUS (empty, for exercises)

10.2 Table EMP

10.3 Table DEPT

10.4 SALGRADE table

10.5 Create schema

-- Create and fill DEPT
CREATE TABLE DEPT (
    DEPTNO  NUMBER(2)     CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME   VARCHAR2(14),
    LOC     VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

-- EMP erstellen
CREATE TABLE EMP (
    EMPNO     NUMBER(4)    CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME     VARCHAR2(10),
    JOB       VARCHAR2(9),
    MGR       NUMBER(4),
    HIREDATE  DATE,
    SAL       NUMBER(7,2),
    COMM      NUMBER(7,2),
    DEPTNO    NUMBER(2)    CONSTRAINT FK_EMP_DEPT
                           REFERENCES DEPT(DEPTNO)
);

-- Fill EMP (selection)
INSERT INTO EMP VALUES (7839,'KING',  'PRESIDENT',NULL,TO_DATE('17.11.1981','DD.MM.YYYY'),5000,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES', 'MANAGER',  7839,TO_DATE('02.04.1981','DD.MM.YYYY'),2975,NULL,20);
INSERT INTO EMP VALUES (7698,'BLAKE', 'MANAGER',  7839,TO_DATE('01.05.1981','DD.MM.YYYY'),2850,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD',  'ANALYST',  7566,TO_DATE('03.12.1981','DD.MM.YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH', 'CLERK',    7902,TO_DATE('17.12.1980','DD.MM.YYYY'), 800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN', 'SALESMAN', 7698,TO_DATE('20.02.1981','DD.MM.YYYY'),1600, 300,30);

-- Create and fill SALGRADE
CREATE TABLE SALGRADE (
    GRADE  NUMBER,
    LOSAL  NUMBER,
    HISAL  NUMBER
);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

11. Oracle SQL Developer and SQL*Plus

11.1 SQL Developer

Oracle SQL Developer is the free graphical IDE for Oracle:

Verbindung anlegen:
  Connection Name:  HTL_SCOTT
  Username:         scott
  Password:         tiger
  Hostname:         localhost
  Port:             1521
  Service name:     XE

11.2 SQL*Plus – Important commands

-- Verbindung
sqlplus scott/tiger@localhost:1521/XE

-- Meta commands (no SQL, no semicolon necessary)
DESCRIBE EMP              -- Show table structure
SET PAGESIZE 50           -- lines per page
SET LINESIZE 150          -- characters per line
COLUMN ENAME FORMAT A15   -- column width
SPOOL ausgabe.txt         -- write output to file
SPOOL OFF                 -- stop writing output
@skript.sql               -- Run SQL script
EXIT                      -- close connection

11.3 Getting Started

-- Verbindung testen
SELECT SYSDATE FROM DUAL;

-- Show your own tables
SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME;

-- EMP-Struktur
DESCRIBE EMP;

-- All employees
SELECT * FROM EMP;

DUAL: A special Oracle table with one row and one column. Used when no real table context is needed: SELECT SYSDATE FROM DUAL, SELECT 2+3 FROM DUAL.

12. Summary and outlook

overview

Relationale Datenbanken
├── Modell (Codd, 1970)
│     ├── Relationen, Tupel, Attribute
│     ├── Primaer- und Fremdschluessel
│     └── 12 Regeln nach Codd
├── RDBMS
│     ├── Oracle, SQL Server, PostgreSQL, MySQL
│     └── SQL als standardisierte Abfragesprache
├── SQL-Teilbereiche
│     ├── DQL, DML, DDL, DCL, TCL
│     └── ACID-Transaktionen
├── Datenbankdesign
│     ├── ER-Modell
│     └── 1NF, 2NF, 3NF
└── Oracle-spezifisch
      ├── SCOTT-Schema (EMP, DEPT, SALGRADE)
      └── SQL Developer / SQL*Plus

Checklist

Unterschied Datenbank / DBMS / Schema erklaert koennen
Primaer- und Fremdschluessel erklaeren koennen
Referentielle Integritaet erklaeren koennen
Mindestens 5 Codd-Regeln nennen koennen
ACID-Eigenschaften kennen
SCOTT Schema: Know tables and relationships
SQL Developer verbinden und erste Abfragen ausfuehren
Know the DUAL table

Outlook: Next topics

• DQL – Data Query Language: SELECT, WHERE, ORDER BY, GROUP BY, aggregate functions
• DML: INSERT, UPDATE, DELETE
• Joins: INNER JOIN, OUTER JOIN, SELF JOIN
• Subqueries: Simple and correlated subqueries
• PL/SQL: Procedural extension of Oracle SQL

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade