Process¶
Relational Databases¶
Relational Databases - Core Concepts¶
A relational database is a database system that stores information in tables (relations), where the data are organized as tuples (rows) and each column (attribute) has a defined data type.
Data are organized in table structures (schemas)
Relation is the term for a table
A relational database is a set of tables
| Column 1 | Column 2 | Column 3 |
|---|---|---|
| Row 1 | ... | ... |
| Row 2 | ... | ... |
Relational Databases - Basic Concepts¶
Jede Tabelle hat einen relation name (table name)
Relation denotes the set of all data tuples (all rows)
Jedes tuple an Daten besteht aus einer Abfolge von Attributwerten (Einzelne Zeile)
Reihenfolge festgelegt durch relation schema (Spaltennamen)
| Attribute 1 | Attribute 2 | Attribute 3 |
|---|---|---|
| Attribute value | ... | ... |
| Attribute value | ... | ... |
Example: OpenData Hanseatic City of Rostock¶
Information is usually spread across several tables and often needs to be merged and processed to derive actionable knowledge.
- Example: https://www.opendata-hro.de
- Land-use plans
- Municipalities
- Construction sites
- Standard land values
- Address list
Key Attributes¶
Certain attributes (columns) can be marked as keys
Keys are usually uniquely identifiable
They exist in the table only once
They can be used to identify the row (tuple)
Row numbers are not suitable (they can be deleted)
Key types:
A single attribute or a set of attributes
Unique ID (UID) - a single attribute
Globally Unique IDs (GUID) - globally unique
UUID (universally unique identifier) - ISO standard
Key Attributes - Example¶
Municipalities Table
| MunicipalityID | Name | Population |
|---|---|---|
| 1 | Dummerstorf | 7.329 |
| 2 | Graal-Müritz | 4.278 |
| 3 | Sanitz | 5.831 |
Key: MunicipalityID
Key References¶
Municipalities Table
| MunicipalityID | Name | Population |
|---|---|---|
| 1 | Dummerstorf | 7,329 |
| 2 | Graal-Müritz | 4,278 |
| 3 | Sanitz | 5,831 |
Primary Key: MunicipalityID
Buildings Table
| BuildingID | BuildingType | MunicipalityID |
|---|---|---|
| 5000 | Gas Station | 2 |
| 5001 | Hotel | 1 |
| 5002 | Church | 2 |
Foreign Key: MunicipalityID
Key Concepts¶
- The keys of other tables can be used to refer to rows in those tables
- A table's own keys are called Primary Key (Primary Key)
- The keys of other tables are called Foreign Key (Foreign Key)
- The tables are related through keys
The foreign key references the primary key in the other table
The Role of Keys¶
Keys define the so-called integrity constraints:
Primary keys
Represent the integrity of individual tables
Local integrity
Foreign keys
Represent the integrity of the entire database system
Global integrity
Lesson Learned¶
Lessons Learned - Prokrastination¶
- Some procrastination is normal and often part of working life (it follows from prioritization)
- Procrastination is very dangerous when we misestimate the effort and it turns out to be larger than the available time
Lessons Learned - Time Management¶
