Data and Knowledge Engineering

Johann Mitloehner, 2020-23

Relational Database Systems
- Basic database concepts 15.03.
  - Video: Basic Concepts
  - Video: JupyterHub, Birding example
- Functional Dependencies and Keys 22.03.
  - Video: Functional dependencies and keys
  - Video: Exercise chess club
- SQL by examples: products, customers and orders 29.03.
  - Video: SQL by Examples: Join and Group by
  - Solutions to exercises
- Good table design: Normal Forms 12.04.
  - Video: Normal Forms 1,2,3
  - Video: BCNF and Algorithms
  - Video: 1NF movie example
  - Exercises for Normal Forms
  - Solutions for Exercises
- DB Applications 19.04.
  - Video: DB Apps Part 1: CSV Import
  - Video: DB Apps Part 2: CSV Export, Reports
  - Video: Another Import/Export Example: Auto MPG
Knowledge Graphs
- RDF and N3 10.05.
  - Video: Populating the Knowledge Graph
  - Video: Defining a simple Ontology
- Sparql 10.05.
  - Video: Sparql
- Reasoning 17.05.
  - Video: Reasoning
- Sparql Endpoints 17.05.
  - Video: Querying Endpoints
Applications
- Combining relational DB and RDF sources 24.05.
  - Video: Combine SQL and Sparql
  - Video: DBpedia
  - Video: Sun Spots and Pirates

Jupyter Notebooks

sind fuer Sie zugaenglich ueber die Learn-Seite zur LV (Lernaktivitaeten). Damit koennen Sie auf einem server unseres Instituts arbeiten, wo alle notwendigen packages schon installiert sind.

Mit ein wenig Installationsaufwand koennen Sie exakt die gleiche Umgebung auch auf Ihrem eigenen Computer haben; in der LV wird nur open source software verwendet: siehe jupyter.org und falls noetig fuer Python python.org oder auch miniconda

Misc

PostgresQL statt SQLite: verwenden Sie folgenden connector (statt sqlite:///.., und mit Ihrer Matrikelnummer):
%sql postgresql://h12345678:h12345678@postgresql.student-db.svc.cluster.local/h12345678
Ab nun gehen alle SQL statements zur Postgres DB. Den aktuellen connector sehen Sie in Jupyter notebook auch in der Rueckmeldung nach jedem statement. Sie haben in Postgres nur eine DB und koennen nicht weitere anlegen wie in SQLite. Die Daten sind am DB server gespeichert, nicht im directory Ihres notebooks.
Big source of CSV data files: R datasets
Comments: # for Python, -- for SQL
A list of countries is often needed for various examples; however, what constitutes a country is often a disputed issue, and DBPedia contains a large number of entries that are not countries in the usual sense of the word. Here is a query for the member states of the UN:
SELECT ?name WHERE {
?x a <http://dbpedia.org/class/yago/WikicatMemberStatesOfTheUnitedNations> .
?x rdfs:label ?name . FILTER (lang(?name) = 'en') . } ORDER BY ?name
Other RDF sources:
- WikiData query service
- OpenStreetMap Sophox service

References

C. J. Date, An Introduction to Database Systems, 8th edition, Pearson, 2003.
Other editions are fine as well, used copies are very cheap from online retailers, e.g. paperback 1999. This one stands out from the large crowd of DB books as it is not tied to a particular software product and approaches relational DB systems and SQL critically; Date covers relational theory in great detail and points out weaknesses and bad design decisions in existing DBMS and SQL, at least from his point of view. Lots of exercises, very interesting read. Highly recommended, but not at all needed for this lecture; for those who wish to further explore the relational approach.