soft2018spring

Databases for Developers

Learning objectives

Knowledge

The student must have knowledge of:

• Various database types and the underlying models
• A specific database system’s storage organisation and query execution
• A specific database system’s optimisation possibilities – including advantages and disadvantages
• Database-specific security problems and their solutions
• Concepts and issues when handling big data
• The particular issues raised by having many simultaneous transactions, including in connection with distributed databases
• Relational algebra (including its relationship to execution plans)

Skills

The student can:

• Transform logical data models into physical models in various database types
• Implement database optimisation
• Use parts of the administration tool to assist in the optimisation and tuning of existing databases, including the incorporation of a specific DBMS’ execution plans
• Use a specific database system’s tools for handling simultaneous transactions
• Use the programming and other facilities provided by a modern DBMS

Competencies

The student can:

• Analyse the application domain in order to select a database type
• Divide responsibility for tasks between the application and DBMS during system development, to ensure the best possible implementation.

NoSQL

Intro & Key-Value Stores (Helge)

Literature

• Martin Kleppmann “Designing Data-Intensive Applications” start of chapter 3
• https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/literature/session_1.zip

Exercises

See lecture material under: https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/lecture_notes/01-Intro_to_DB.ipynb

Study activity

• Read 2 hrs
• Exercises 6 hrs

Document-oriented DB 1 (Helge)

Literature

• K. Banker, P. Bakkum, S. Verch, D. Garrett “MongoDB in Action, Second Edition”
  • Chapter 1
  • Chapter 2
  • Chapter 3
• https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/literature/session_2.zip

Exercises

See lecture material under:

https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/lecture_notes/02-Intro_to_MongoDB.ipynb

Study activity

• Read 2 hrs
• Exercises 4 hrs

Document-oriented DB 2 (Helge)

Literature

• K. Banker, P. Bakkum, S. Verch, D. Garrett “MongoDB in Action, Second Edition”
  • Chapter 4
  • Chapter 5 (- Chapter 6)
• https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/literature/session_3.zip Focus on the first two chapters. The chapter on aggregation is included in case you will need it for your project and if you want to go more in depth.
• I. Robinson, J. Webber, E. Eifrem “Graph Databases”
  • Appendix A. NOSQL Overview
• The official manual
  • https://docs.mongodb.com/manual/data-modeling/ (including the contained chapters)
  • https://docs.mongodb.com/manual/tutorial/geospatial-tutorial/

Exercises

No study point exercises. See the in class exercises in the lecture notes https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/lecture_notes/03-MongoDB_Modelling.ipynb

Study activity

• Read 6 hrs
• Exercises 2 hrs

SQL

22.2. SQL and modeling foundations (Jens)

Literature

• Database models, dictionaries, schemas and ERDs
• Chen ERD notation
• Chapter 4: Building a logical data model
• Please bring a piece of paper and a pen!

Exercises

• Hand-in: ER diagram of IMDB data. Deadline 27 of February. See Peergrade.
• ERD and SQL foundations. See slides on SQL and modeling

Study activity

• Read 4 hrs
• Exercises 4 hrs

1.3 DBMS and normal forms (Jens)

Literature

• Basics of the Unix Philosophy (read until “Rule of Modularity”, but please at least make an effort to understand each bullet).
• Database normalization (read only “Objectives”)
• First normal form
• Normalization in DBMS
• Transitive dependency

Exercises

• Hand-in: Normalisation of Twitter data. Deadline: 6th or March.

Study activity

• Read 4 hrs
• Exercises 4 hrs

8.3 Algebraic relational modeling (Jens)

Literature

• Relational algebra: A brief introduction
• BCNF normal form (3.5NF)
• Fourth normal form
• Fifth normal form

Exercises

• Hand-in: Set operations in SQL. Deadline 13th of March 12:00.

Study activity

• Read 4 hrs
• Exercises 4 hrs

15.3. Indexing and runtimes (Jens)

Literature

PostgreSQL: Indexes under the hood

Exercises

Hand-in: Materialised views and join queries on peergrade. Deadline 20th of March 12:00.

Study activity

• Read 4 hrs
• Exercises 4 hrs

22.3. Scaling and concurrency (Jens)

Literature

• PostgreSQL planner and its usage of statistics
• Pgpool
• Serverless databases

Exercises

• Hand-in: Denormalisation and multi-joins. Deadline 3rd of April 12:00. Review deadline 4th of
• April 23:59. See peergrade.

Study activity

• Read 4 hrs
• Exercises 4 hrs

5.4. Storing spatial data (Jens)

Literature

Exercises

Study activity

• Read 4 hrs
• Exercises 4 hrs

12.4. Advanced SQL and security (Jens)

Literature

Exercises

Study activity

• Read 4 hrs
• Exercises 4 hrs

NoSQL

GraphDB 1 (Helge)

Literature

• Eric Lehman, F. Thomson Leighton, Albert R. Meyer “Mathematics for Computer Science” https://courses.csail.mit.edu/6.042/spring17/mcs.pdf
  • Intro to chapter 10, chapter 10.1, chapter 10.2
  • Intro to chapter 12, chapter 12.1
• Neo4j tutorials (those that you run and read from your Neo4j web-client)
  • :play concepts
  • :play movie graph
  • :play northwind graph

Exercises

Study activity

• Read 4 hrs
• Exercises 4 hrs

GraphDB 2 (Helge)

Literature

• A. Vukotic, N. Watt “Neo4J in Action”,
  • Chapter 1 (https://manning-content.s3.amazonaws.com/download/5/392c9aa-4c64-4c6d-a072-fcf6b73d4ef1/Neo4jinAction_CH01.pdf)
  • Chapter 2
  • Chapter 6
  • Chapter 9 (https://manning-content.s3.amazonaws.com/download/c/c481c47-9a0d-493d-b708-1877bb1d891f/Neo4jinAction_CH09.pdf)

Exercises

Study activity

• Read 4 hrs
• Exercises 4 hrs

GraphDB 3 (Helge)

Literature

• Neo4j tutorials (those that you run and read from your Neo4j web-client)
  • :play http://guides.neo4j.com/modeling_airports
  • :play http://guides.neo4j.com/modeling_airports/02_flight.html
  • :play http://guides.neo4j.com/modeling_airports/03_flight_booking.html
  • :play http://guides.neo4j.com/modeling_airports/04_specific_relationship_types.html
  • :play http://guides.neo4j.com/modeling_airports/05_refactoring_large_graphs.html
• https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/lecture_notes/Neo4j%20Performance.pdf
• https://github.com/datsoftlyngby/soft2018spring-databases-teaching-material/blob/master/lecture_notes/Modeling%20Guidelines.pdf
• The official documentation:
  • https://neo4j.com/docs/developer-manual/current/cypher/#execution-plans
  • http://neo4j.com/docs/stable/cypher-refcard/
  • https://neo4j.com/docs/developer-manual/current/cypher/functions/spatial/

Exercises

Study activity

• Read 4 hrs
• Exercises 4 hrs

Project

Project Workshop & Review (Helge)

Literature

All above

Exercises

Gutenberg Project

Study activity

• Prepare for review
• Work on project
• 12hrs

Project Workshop & Review 2 (Helge)

Literature

All above

Exercises

Gutenberg Project

Study activity

• Prepare for review
• Work on project
• 12hrs

Project Workshop & Review 3 (Helge)

Literature

All above

Exercises

Gutenberg Project

Study activity

• Prepare for review
• Work on project
• 12hrs

Assignments

You earn studypoints (SP) by solving the assignments from the list below. There are in total 11 assignments during the course

OBS: Hand-in of the below assignmentsis individually. That is, you are allowed to work in groups but you have to provide a personal solution. You cannot handin the same copy of the solution for all your group members.

Task	Hand-out	Hand-in	Studypoints
Key-Value Store Exercise	1. Feb. 2018	5. Feb. 23:55	10 mandatory
Review of the above assignment	6. Feb. 2018	7. Feb. 12:00 (noon)	10 mandatory
MongoDB Exercise + Review	8. Feb. 2018	15. Feb. 2:00 (noon)	20 mandatory
ER exercise + Review	22. Feb. 2018	28. Feb. 12:00 (noon)	10
Normal forms + Review	1. Mar. 2018	7. Mar. 12:00 (noon)	10
Algebraic modeling + Review	8. Mar. 2018	14. Mar. 12:00 (noon)	10
Indexing and runtimes + Review	15. Mar. 2018	21. Mar. 12:00 (noon)	10
Scaling PostgreSQL + Review	22. Mar. 2018	4. Apr 12:00 (noon)	10
Spatial searches + Review	5. Apr. 2018	11. Apr. 12:00 (noon)	10
Advanced SQL + Review	12. Apr. 2018	18. Aprt. 12:00 (noon)	10
Technical Comparison of an SQL and Graph DB + Review	26. Apr. 2018	2. May 12:00 (noon)	20 mandatory
Gutenberg Books Project	3. May 2018	28. May 12:00 (noon)	mandatory for exam

As you can see in the table above can you earn 130 SP for all the exercises. To get at least 80% of the SP, which is a requirement to be elegible for the exam, you can decide to not handin at maximum 2 assignments in the SQL assignment block.

Exam

Examination form:

• Oral group exams (max. 4 members) with individual grades
• The exam starts with a ten minutes group presentation about the Gutenberg Books Project. Subsequently, discussion with the examiners and the group about projects, technologies, design decisions, their impact, etc.
• The exam will last in total 50 minutes per group of four
  • In the unlikely case of smaller groups holds:
    • 3 members: 40 min
    • 1-2 members: 30 min

The Gutenberg Books Project is mandatory for the exam, where the project report is considered for grading (eksamensgrundlag).

Assessment

One single grade is given according to the 7-point grading scale

Admission criteria

The student must fulfil the mandatory learning activities