Relational Databases

Of courseee! đź’— Here are clear, structured notes based on the IBM article, highlighting the most important concepts and definitions. I've kept them suitable for studying and reviewing your database and data management topics.

Relational Databases – Study Notes

Source: IBM – What is a Relational Database?

Read the original article

1. What is a Relational Database?

  • A relational database is a type of database that organizes data into tables.

  • Tables consist of:

    • Rows: Individual records.

    • Columns: Attributes or fields describing the records.

  • Data is organized across multiple related tables.

  • Tables are connected through unique identifiers called primary keys and foreign keys.

  • SQL is used to retrieve, combine, and analyse information from these tables.

Example:

Both tables can be connected through Customer ID.

This allows companies to analyse transactions based on customer information without storing the same customer details repeatedly.

2. Primary Keys and Foreign Keys

Primary Key (PK)

  • A unique identifier for each record in a table.

  • Cannot contain duplicate values.

  • Ensures that each record can be identified.

Foreign Key (FK)

  • A field that references the primary key in another table.

  • Establishes relationships between tables.

Example:

  • Customer ID in the Customer Table = Primary Key.

  • Customer ID in the Transaction Table = Foreign Key.

3. ACID Properties

Relational databases are commonly used for transactions, where multiple operations must be completed reliably.

ACID describes four important properties of database transactions:


Example – Bank Transfer:

When transferring money between two accounts:

  1. Money is withdrawn from one account.

  2. Money is deposited into another account.

Both operations must happen successfully. Otherwise, the transaction should not be completed.

4. Relational Database Management System (RDBMS)

  • An RDBMS is software used to create, manage, and maintain relational databases.

  • It allows users to:

    • Create data.

    • Insert new records.

    • Update existing information.

    • Delete records.

    • Control user access.

Examples of RDBMS:

  • MySQL

  • PostgreSQL

  • IBM DB2

Difference between DBMS and RDBMS:

  • DBMS: General software for managing databases.

  • RDBMS: Specifically organizes information into related tables.

5. SQL (Structured Query Language)

  • SQL is the standard programming language used to communicate with relational databases.

  • It allows users to retrieve, insert, update, and delete data.

  • SQL can also combine information from multiple tables.

Important SQL operations:

|
SQL Command

|

Function

|
| --- | --- |
|

SELECT

|

Retrieves data

|
|

INSERT

|

Adds new data

|
|

UPDATE

|

Modifies existing data

|
|

DELETE

|

Removes data

|
|

JOIN

|

Combines information from multiple tables

|
|

WHERE

|

Filters records based on conditions

|
|

GROUP BY

|

Groups records with similar values

|
|

ORDER BY

|

Sorts results

|

Important: SQL makes it possible to analyse large datasets without manually searching through individual records.

6. History of Relational Databases

  • Before relational databases, companies mainly used hierarchical database systems.

  • These systems organized information in a tree-like structure.

  • They were often complex and limited in flexibility.

Important developments:

|
Year

|

Development

|
| --- | --- |
|

1970

|

IBM researcher Edgar F. Codd introduced the relational database model.

|
|

1973

|

IBM started System R to demonstrate the relational database concept.

|
|

1983

|

IBM introduced the DB2 family of relational databases.

|

The relational model made it easier to retrieve and combine information from different tables.

7. Relational vs. Non-Relational Databases

Relational databases (SQL):

  • Store data in structured tables.

  • Use predefined schemas.

  • Connect tables through relationships.

  • Suitable for structured information and complex transactions.

Non-relational databases (NoSQL):

  • Offer more flexible data structures.

  • Can handle semi-structured and unstructured data.

  • Often used for large-scale and flexible data storage.

Types of Non-Relational Databases

1. Key-Value Store

  • Stores information as key-value pairs.

  • Commonly used for caching and user sessions.

  • Examples: Redis and Memcached.

2. Document Store

  • Stores information as documents.

  • Common formats: JSON, XML and BSON.

  • Useful for user profiles and content management.

  • Example: MongoDB.

3. Wide-Column Store

  • Organizes information into column-based structures.

  • Useful for handling large amounts of distributed data.

  • Examples: Apache Cassandra and Apache HBase.

4. Graph Store

  • Organizes data as nodes, edges and properties.

  • Focuses on relationships between different entities.

  • Example: Neo4j.

8. Benefits of Relational Databases

1. Ease of Use

  • SQL makes retrieving and analysing information relatively simple.

  • Data can be filtered, grouped and combined.

  • Indexes help databases locate information faster.

2. Reduced Data Redundancy

  • Relational databases reduce unnecessary duplication of information.

  • This is achieved through a process called normalization.

  • Each piece of information can be stored in one place and referenced by other tables.

Example: Customer information is stored once, rather than repeated for every transaction.

3. Backup and Disaster Recovery

  • Relational databases maintain consistent data.

  • Many systems support easy data export and import.

  • Backups make it possible to restore information after system failures.

  • Cloud-based databases can use continuous data replication.

9. Key Concepts to Remember

|
Concept

|

Short Definition

|
| --- | --- |
|

Relational Database

|

Database that organizes information into related tables.

|
|

Table

|

Collection of related data organized into rows and columns.

|
|

Row

|

Individual record in a table.

|
|

Column

|

Attribute or characteristic of data.

|
|

Primary Key

|

Unique identifier for a record.

|
|

Foreign Key

|

Field connecting one table to another.

|
|

RDBMS

|

Software used to manage relational databases.

|
|

SQL

|

Language used to interact with relational databases.

|
|

ACID

|

Four properties ensuring reliable transactions.

|
|

Normalization

|

Process of organizing data to reduce redundancy.

|
|

JOIN

|

SQL operation combining data from multiple tables.

|
|

Index

|

Structure that helps retrieve data faster.

|

The most important takeaway

A relational database organizes information into connected tables, using primary and foreign keys to establish relationships. SQL allows users to work with this information, while ACID properties ensure reliable transactions.

The main advantages are structured data organization, reduced redundancy, easier querying, and reliable data management.

My recommendation for studying: Pay particular attention to the differences between PK and FK, DBMS and RDBMS, SQL and NoSQL, and the four ACID properties. These concepts form the foundation for understanding relational databases.

Dk

Selvfølgeligeee! 💗 Her er den danske oversættelse af noterne, med de vigtigste begreber og definitioner bevaret, så de er nemme at bruge til undervisning og eksamensforberedelse.

Relationelle databaser – Studiebemærkninger

Kilde: IBM – What is a Relational Database?

Læs den originale artikel

1. Hvad er en relationel database?

  • En relationel database er en type database, der organiserer data i tabeller.

  • Tabeller bestĂĄr af:

    • Rækker (Rows): Individuelle poster.

    • Kolonner (Columns): Attributter eller felter, der beskriver posterne.

  • Data organiseres pĂĄ tværs af flere relaterede tabeller.

  • Tabeller forbindes gennem unikke identifikatorer kaldet primærnøgler (Primary Keys) og fremmednøgler (Foreign Keys).

  • SQL bruges til at hente, kombinere og analysere information fra disse tabeller.

Eksempel:

En virksomhed kan have to tabeller:

|
Kundetabel

|

Transaktionstabel

|
| --- | --- |
|

Kunde-ID

|

Transaktionsdato

|
|

Virksomhedsnavn

|

Kunde-ID

|
|

Adresse

|

Transaktionsbeløb

|
|

Branche

|

Betalingsmetode

|

Begge tabeller kan forbindes gennem Kunde-ID.

Dette gør det muligt for virksomheder at analysere transaktioner baseret på kundeinformation uden at gemme de samme kundeoplysninger flere gange.

2. Primærnøgler og fremmednøgler

Primærnøgle (Primary Key / PK)

  • En unik identifikator for hver post i en tabel.

  • Kan ikke indeholde dublerede værdier.

  • Sikrer, at hver post kan identificeres individuelt.

Fremmednøgle (Foreign Key / FK)

  • Et felt, der henviser til primærnøglen i en anden tabel.

  • Opretter relationer mellem tabeller.

Eksempel:

  • Kunde-ID i Kundetabellen = Primærnøgle.

  • Kunde-ID i Transaktionstabellen = Fremmednøgle.

3. ACID-egenskaber

Relationelle databaser bruges ofte til transaktioner, hvor flere operationer skal gennemføres pålideligt.

ACID beskriver fire vigtige egenskaber ved databasetransaktioner:

|
Egenskab

|

Betydning

|
| --- | --- |
|

Atomicity (Atomicitet)

|

Alle operationer gennemføres succesfuldt, eller også gennemføres ingen af dem.

|
|

Consistency (Konsistens)

|

Data forbliver korrekte og følger de definerede regler.

|
|

Isolation (Isolation)

|

Samtidige transaktioner forstyrrer ikke hinanden.

|
|

Durability (Holdbarhed)

|

Gennemførte transaktioner forbliver gemt, selv hvis systemet går ned.

|

Eksempel – Bankoverførsel:

Når penge overføres mellem to bankkonti:

  1. Pengene trækkes fra den ene konto.

  2. Pengene indsættes på den anden konto.

Begge operationer skal gennemføres korrekt. Ellers bør transaktionen ikke fuldføres.

4. Relationelt Databasestyringssystem (RDBMS)

  • Et RDBMS (Relational Database Management System) er software, der bruges til at oprette, administrere og vedligeholde relationelle databaser.

  • Det giver brugerne mulighed for at:

    • Oprette data.

    • Indsætte nye poster.

    • Opdatere eksisterende information.

    • Slette poster.

    • Kontrollere brugeradgang.

Eksempler pĂĄ RDBMS:

  • MySQL

  • PostgreSQL

  • IBM DB2

Forskellen mellem DBMS og RDBMS:

  • DBMS: Generel software til administration af databaser.

  • RDBMS: Organiserer specifikt information i relaterede tabeller.

5. SQL (Structured Query Language)

  • SQL er et standardprogrammeringssprog, der bruges til at kommunikere med relationelle databaser.

  • Det gør det muligt at hente, indsætte, opdatere og slette data.

  • SQL kan ogsĂĄ kombinere information fra flere tabeller.

Vigtige SQL-kommandoer:

|
SQL-kommando

|

Funktion

|
| --- | --- |
|

SELECT

|

Henter data

|
|

INSERT

|

Tilføjer nye data

|
|

UPDATE

|

Ændrer eksisterende data

|
|

DELETE

|

Fjerner data

|
|

JOIN

|

Kombinerer information fra flere tabeller

|
|

WHERE

|

Filtrerer poster baseret pĂĄ bestemte betingelser

|
|

GROUP BY

|

Grupperer poster med lignende værdier

|
|

ORDER BY

|

Sorterer resultater

|

Vigtigt: SQL gør det muligt at analysere store datasæt uden manuelt at skulle søge gennem individuelle poster.

6. Historien bag relationelle databaser

  • Før relationelle databaser brugte virksomheder hovedsageligt hierarkiske databasesystemer.

  • Disse systemer organiserede information i en træstruktur.

  • De var ofte komplekse og havde begrænset fleksibilitet.

Vigtige udviklinger:

|
Ă…r

|

Udvikling

|
| --- | --- |
|

1970

|

IBM-forskeren Edgar F. Codd introducerede den relationelle databasemodel.

|
|

1973

|

IBM begyndte arbejdet med System R for at demonstrere konceptet bag relationelle databaser.

|
|

1983

|

IBM introducerede DB2-serien af relationelle databaser.

|

Den relationelle model gjorde det lettere at hente og kombinere information fra forskellige tabeller.

7. Relationelle vs. ikke-relationelle databaser

Relationelle databaser (SQL):

  • Gemmer data i strukturerede tabeller.

  • Bruger foruddefinerede skemaer.

  • Forbinder tabeller gennem relationer.

  • Velegnede til strukturerede data og komplekse transaktioner.

Ikke-relationelle databaser (NoSQL):

  • Tilbyder mere fleksible datastrukturer.

  • Kan hĂĄndtere semi-strukturerede og ustrukturerede data.

  • Bruges ofte til store datamængder og fleksibel datalagring.

Typer af ikke-relationelle databaser

1. Key-Value Store (Nøgle-værdi-database)

  • Gemmer information som nøgle-værdi-par.

  • Bruges ofte til caching og brugersessioner.

  • Eksempler: Redis og Memcached.

2. Document Store (Dokumentdatabase)

  • Gemmer information som dokumenter.

  • Almindelige formater: JSON, XML og BSON.

  • Velegnet til brugerprofiler og indholdsstyring.

  • Eksempel: MongoDB.

3. Wide-Column Store (Kolonneorienteret database)

  • Organiserer information i kolonnebaserede strukturer.

  • Velegnet til hĂĄndtering af store mængder distribuerede data.

  • Eksempler: Apache Cassandra og Apache HBase.

4. Graph Store (Grafdatabase)

  • Organiserer data som noder, kanter og egenskaber.

  • Fokuserer pĂĄ relationer mellem forskellige enheder.

  • Eksempel: Neo4j.

8. Fordele ved relationelle databaser

1. Brugervenlighed

  • SQL gør det relativt enkelt at hente og analysere information.

  • Data kan filtreres, grupperes og kombineres.

  • Indekser hjælper databaser med at finde information hurtigere.

2. Reduceret dataredundans

  • Relationelle databaser reducerer unødvendig gentagelse af information.

  • Dette opnĂĄs gennem en proces kaldet normalisering (Normalization).

  • Hver information kan gemmes Ă©t sted og henvises til fra andre tabeller.

Eksempel: Kundeinformation gemmes Ă©n gang i stedet for at blive gentaget for hver transaktion.

3. Backup og gendannelse efter systemfejl

  • Relationelle databaser hjælper med at opretholde konsistente data.

  • Mange systemer understøtter nem eksport og import af data.

  • Backups gør det muligt at gendanne information efter systemfejl.

  • Cloudbaserede databaser kan anvende løbende datareplikering.

9. Vigtige begreber at huske

|
Begreb

|

Kort definition

|
| --- | --- |
|

Relationel database

|

Database, der organiserer information i relaterede tabeller.

|
|

Tabel (Table)

|

Samling af relaterede data organiseret i rækker og kolonner.

|
|

Række (Row)

|

En individuel post i en tabel.

|
|

Kolonne (Column)

|

En attribut eller egenskab ved data.

|
|

Primærnøgle (Primary Key)

|

Unik identifikator for en post.

|
|

Fremmednøgle (Foreign Key)

|

Felt, der forbinder Ă©n tabel med en anden.

|
|

RDBMS

|

Software til administration af relationelle databaser.

|
|

SQL

|

Sprog, der bruges til at kommunikere med relationelle databaser.

|
|

ACID

|

Fire egenskaber, der sikrer pĂĄlidelige transaktioner.

|
|

Normalisering

|

Proces, der organiserer data for at reducere redundans.

|
|

JOIN

|

SQL-operation, der kombinerer data fra flere tabeller.

|
|

Indeks (Index)

|

Struktur, der hjælper med at hente data hurtigere.

|

Det vigtigste at huske

En relationel database organiserer information i forbundne tabeller ved hjælp af primær- og fremmednøgler til at etablere relationer. SQL gør det muligt at arbejde med denne information, mens ACID-egenskaberne sikrer pålidelige transaktioner.

De vigtigste fordele er struktureret dataorganisering, reduceret redundans, nemmere forespørgsler og pålidelig dataadministration.

Min anbefaling til din eksamensforberedelse: Læg især mærke til forskellene mellem PK og FK, DBMS og RDBMS, SQL og NoSQL samt de fire ACID-egenskaber. Disse begreber danner grundlaget for forståelsen af relationelle databaser. 💗