Introduction to Database Design, BSc (Autumn 2018)

Abstract

The course provides a practical and theoretical introduction to relational databases as well as an introduction to "data analytics". Central to the course is modeling and execution of queries in relational databases. The course contains topics such as programming in SQL and how SQL is used in e.g. java applications but also deals with database design, normalization of relationships, OLAP, triggers and views.

Description

Databases are a key component of most IT systems. They are particularly important as part of modern services based on an increasing amount of stored data.

The course provides an introduction to relational database models, SQL database systems and an introduction to data analysis, both from a practical and theoretical point of view.

The main part of the course deals with relational databases, including theory and practice for modeling and querying a database. Towards the end of the course the focus will be on techniques for OLAP. 

For relational databases the following topics will be covered: 

• Programming in SQL, including all basic operations as well as some more advanced constructions (e.g. subqueries) 
  
• Database design using E-R modelling 
  
• Defining a database design using the relational model and SQL schemas 
  
• Normalization of relations 
  
• Use of different indexes, including hash indexes, B-tree indexes, non-clustered and clustered indexes 
  
• Transaction handling 
  
• Using SQL in applications, e.g. Java-applications 
  
• Other basic concepts related to relational databases and SQL, such as triggers, views etc. 
  
  

For data analytics the following topics will be covered: 

• OLAP and data warehousing
  

Formal prerequisites

This course assumes basic ability to use a computer. We also assume that a student is taking an introductory programming course in parallel (otherwise basic programming skills in Java are expected) and is following a discrete math course such as SGDM (or has knowledge of basic discrete math: logic, set theory and proofs).

Intended learning outcomes

After the course, the student should be able to:

• Suggest a database design according to the relational model, and present it as an SQL schema, using the concepts key, type, and constraint
• Transform a database design in the E-R model to a corresponding relational data model
• Analyze and improve database designs using normal form theory
• Write SQL queries, involving multiple relations, compound conditions, grouping, aggregation, and subqueries
• Find functional dependencies in a relation and perform decomposition to eliminate unwanted dependencies
• Decide if a given index is likely to improve performance for a given query
• Identify possible problems in transaction handling, related to consistency, atomicity, and isolation
• Analyze/predict/improve efficiency of the designed database using indices
• Use SQL from other programming languages
• Suggest a conceptual and physical design of an OLAP system

Ordinary exam

Exam type:
A: Written exam, external (7-trinsskala)
Exam description:
There will be four homework assignments. Each will be graded. There will be a final written exam. The final grade is computed 10% for each homework + 60% final exam. The duration of the written examination on premises is 4 hours with the following restrictions: 1. Physical copies of the book and other printed materials are permitted. 2. Ebooks on laptops, ipads, and other e book readers are permitted. 3. Use of a local DBMS on your laptop is permitted. 4. Cell phones are *not* permitted. 5. It is *not* permitted that you access any other other information from the internet, including newsgroups, social media, email, facebook twitter, etc. or elsewhere that is not in book form. Students should bring a computer with wifi and with the MySQL database system installed. The reexam form will depend of the number registered for re-exam