Data and Knowledge Engineering

Johann Mitloehner, 2020-23

Relational Database Systems
- Basic database concepts 11.10.
  - Video: Basic Concepts
  - Video: JupyterHub, Birding example
- Functional Dependencies and Keys 18.10.
  - Video: Functional dependencies and keys
  - Video: Exercise chess club
- SQL by examples: products, customers and orders 25.10.
  - Video: SQL by Examples: Join and Group by
  - Solutions to exercises
- Good table design: Normal Forms 08.11.
  - Video: Normal Forms 1,2,3
  - Video: BCNF and Algorithms
  - Video: 1NF movie example
  - Exercises for Normal Forms
  - Solutions for Exercises
- DB Applications 15.11.
  - 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 22.11.
  - Video: Populating the Knowledge Graph
  - Video: Defining a simple Ontology
- Sparql 22.11.
  - Video: Sparql
- Reasoning 06.12.
  - Video: Reasoning
- Sparql Endpoints 06.12.
  - Video: Querying Endpoints
Applications
- Combining relational DB and RDF sources 13.12.
  - 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

Misc & FAQ

Comments: # for Python, -- for SQL
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

RDF List of countries:

SELECT ?name  WHERE {
 ?x a <http://dbpedia.org/class/yago/WikicatMemberStatesOfTheUnitedNations> .
 ?x rdfs:label ?name . FILTER (lang(?name) = 'en') . } ORDER BY ?name

RDF sources for more challenging projects:
- 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.