• A
  • A
  • A
  • ABC
  • ABC
  • ABC
  • А
  • А
  • А
  • А
  • А
Regular version of the site

Databases

2019/2020
Academic Year
ENG
Instruction in English
5
ECTS credits
Course type:
Elective course
When:
2 year, 1, 2 module

Instructors


Kuznetsova, Anastasiya


Тормашева Софья Дмитриевна

Course Syllabus

Abstract

We will cover database theory, characteristics of contemporary DBMS landscape, Relational Algebra, Entity Relationship modeling of different domains, SQL and its dialects, different approaches to data modelling. Course DB project will include group modelling of a complex domain area, design, and implementation of DB for this area, including relationships, triggers, stored procedures, complex queries. Applications of databases to Business Intelligence are also discussed through the course, including the integration of predictive analytics with DBMS and user BI systems. Course BI project will include defining BI goals, setting KPI and designing dashboards for a particular case from different domain areas. Visualization of business metrics and dashboards design will become a special topic of the course, which will allow students to get familiar with the process of data communication. Data-driven dashboards, being one of the most essential parts of Business Intelligence, will be discussed a lot.
Learning Objectives

Learning Objectives

  • introduces database design and querying in different Database Management Systems. Emphasis is both on database design and on applications of databases to analytical tasks.
Expected Learning Outcomes

Expected Learning Outcomes

  • model different domain areas using ER approach
  • design and implement normalized database structures by creating database tables, queries, triggers, stored procedures, reports, and forms
  • perform typical BI reporting queries using SQL and analytical tools
  • understand applications of BI to decision support in modern companies
  • produce custom reports and dashboards based on DB data in Tableau and/or Power BI
Course Contents

Course Contents

  • Fundamentals of Databases: Relational Algebra, ER modelling
    1.1 Theoretical introduction to databases Relational and non-relational databases. SQL databases. Introduction to set theory and relational algebra. 1.2 Entity-relationship diagrams and Relational schemas Usage of ER diagrams. ER diagram symbols. Relational schemas design. Keys in relational schemas and main terminology.
  • Database Design
    2.1 Databases design Designing databases in Lucidcharts, MySQL and draw.io. 2.2 Database creation Connection to databases. Creation of databases with diagrams and code.
  • SQL
    3.1 Introduction to SQL queries Practice with functions SELECT (*), WHERE, AND/OR. 3.2 Queries with constraints Practice with functions: SELECT, ORDER BY, LIKE, IN, LIMIT. 3.3 Queries with several conditions. Filtering and sorting Practice with functions: WHERE, ORDER BY, NULL. 3.4 Introduction to dataset joins Types of joins and their usage. Practice with JOIN function. 3.5 Data aliases Practice with joins and AS function. 3.6 Aggregate functions in SQL Practice with functions: MIN, MAX, AVG, COUNT, GROUP BY, HAVING. 3.7 Complex SQL queries SQL subqueries for data preprocessing. 3.8 Data modification Practice with functions: INSERT, UPDATE, DELETE, CREATE / TEST.
  • Dashboards design and basics of BI
    4.1 Dashboard design The history of the dashboards, getting familiar with what KPI metrics are. Types of information dashboards. Main rules for making dashboards and common mistakes. 4.2 Introduction to Tableau Types of data connections in Tableau. How to connect SQL database to Tableau. Basic diagrams in Tableau (bar charts, scatterplots, line graphs, pie charts, tables). 4.3 SQL and Tableau SQL queries in Tableau for getting better data for your dashboards. Data joins and filtering with queries.
Assessment Elements

Assessment Elements

  • non-blocking Seminar participation
    Seminar participation can be replaced with extra homework.
  • non-blocking In-class test on ER diagrams and Relational Schemas
    If the student were not able to attend the in-class test without a valid excuse, the in-class test can be retaken in the next two days with its score reduced by 1 point. If the student were not able to attend the in-class test with a valid excuse, the in-class test can be retaken without any penalties. Confirmation of the valid excuse is required. Retake time is negotiated individually.
  • non-blocking In-class test on SQL queries
    If the student were not able to attend the in-class test without a valid excuse, the in-class test can be retaken in the next two days with its score reduced by 1 point. If the student were not able to attend the in-class test with a valid excuse, the in-class test can be retaken without any penalties. Confirmation of the valid excuse is required. Retake time is negotiated individually.
  • non-blocking DB Project
    If the project was submitted an hour after the deadline, the score for it is reduced by 1 point, 6 hours - by 5 points, after 7 hours after the deadline projects are not accepted.
  • non-blocking BI Project
    If the project was submitted an hour after the deadline, the score for it is reduced by 1 point, 6 hours - by 5 points, after 7 hours after the deadline projects are not accepted.
  • non-blocking 80-minute final test
Interim Assessment

Interim Assessment

  • Interim assessment (2 module)
    0.2 * 80-minute final test + 0.25 * BI Project + 0.25 * DB Project + 0.1 * In-class test on ER diagrams and Relational Schemas + 0.1 * In-class test on SQL queries + 0.1 * Seminar participation
Bibliography

Bibliography

Recommended Core Bibliography

  • Churcher, C. (2012). Beginning Database Design : From Novice to Professional (Vol. 2nd ed). New York: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1173722
  • Foster, E. C., & Godbole, S. (2014). Database Systems : A Pragmatic Approach. [Berkeley, CA]: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=933677

Recommended Additional Bibliography

  • Khan, A. (2016). Jumpstart Tableau : A Step-By-Step Guide to Better Data Visualization. [United States]: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1250591
  • Laursen, G. H. N., & Thorlund, J. (2010). Business Analytics for Managers : Taking Business Intelligence Beyond Reporting. Hoboken, N.J.: Wiley. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=330345
  • Monsey, M., & Sochan, P. (2016). Tableau For Dummies. Hoboken, NJ: For Dummies. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1082334
  • Rockoff, L. (2017). The Language of SQL (Vol. Second edition). Hoboken, NJ: Addison-Wesley Professional. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1601663