Data Management

2019/2020

Academic Year

ENG

Instruction in English

6

ECTS credits

Delivered at:

School of Business Informatics

Course type:

Compulsory course

When:

2 year, 3, 4 module

Course Syllabus

Abstract

As the amount of data being produced everyday increases, the skill of working with data sources (databases) becomes an essential one for anyone involved in IT or management. After completing the data management course students will be able to create a normalized relational database, connect to an existing data source, write complex queries to read and modify the data and prepare business intelligence reports. The course mostly focuses on relational data models, database design and data manipulations with use of programming language. Non-relational databases, physical storage organization and access management are additional topics of the course. The course introduces students to widely used server platforms like Microsoft SQL Server (main software), Oracle, Postgres and MySQL.

Learning Objectives

• Provide students with necessary knowledge and practical skills in business database design and maintenance
• Learn to use SQL programming language to extract and process the data stored in a database

Expected Learning Outcomes

• Use client software to manage and fill a relational database
• Find a plan for retrieving data from a relational DB in terms of relational operations
• Use query editor to write and execute queries
• Retrieve data from connected tables
• Develop complex queries with use of window functions
• Identify entities and relationships based on business rules analysis and create conceptual and logical models
• Prepare reports and dashboards to provide access for users to business-critical information
• Creates and manages databases on server platforms such as Oracle, Postgres, MySQL
• Use IDEF1X method to create a database
• Describe a relational database schema
• Explore dependencies of attributes and solve data redundancy problems
• Read and edit data in document-oriented and graph databases
• Use calculations in SQL queries
• Write analytical queries with aggregation
• Create database objects with SQL - procedures, functions and views
• Use spreadsheet tools to store and process data
• Build a logical data model on top of a conceptual model
• Track and process changes in table data with triggers
• Uses platform-specific features of query language

Course Contents

• Introduction to data management. Database systems
  Main data models. DBMS vs database. Features of DBMS. Benefits of RDBMS. Data structure. Key concepts of DBMS. Client tools for managing a database. Spreadsheet applications. Useful data formats.
• Relational data model and relational algebra
  Relations. Keys. Schema of relational database. Relational algebra operations. Main and additional operators. Normalization algorithm. Five normal forms. Functional dependencies. Multivalued dependencies.
• Data manipulations in structured query language. Create, read, update and delete operations.
  General information on writing queries. Structure of SELECT statement. Type-specific manipulations. Expressions. Connecting records of tables in SELECT. Modifying table data with SQL queries.
• Database design
  Database design techniques. Subject area analysis. Entityes and relationships. Conceptual models. Logical models. IDEF1X. Subtypes and supertypes. Using CASE tools to create a database from scratch.
• Advanced SQL. Analytical queries. Procedural SQL
  Aggregate functions and grouping. Writing analytical queries with expressions, derived tables and filters. Complex queries. Three types of window functions. Views, procedures, functions. Triggers. Passing parameters to procedures and functions.
• Reporting and data visualization
  Maintaining data views for users by means of reports. Dashboards and BI-systems. Setting navigation between different reports.
• Non-relational databases. NoSQL
  Different approach to data management - noSQL databases. Document store. Graph database. Key-value store. Creating databases in popular noSQL DBMS. Writing queries.
• Database implementation on modern server platforms
  Platforms, not covered in the main part of the course: Postgres DBMS, Oracle DBMS, MySQL DBMS. Client software setup. Structure and data manipuations.

Assessment Elements

• Test
• Homework
• In-class graded task
  There are two forms possible: 1) test with one question 2) practical task which requires a report preporation
• Exam test
  Examination format: The exam is taken written. The platform: The exam is taken on Canvas, Zoom platforms. Students are required to join a session 15 minutes before the beginning. The computers must meet the following technical requirements: https://docs.microsoft.com/ru-ru/microsoftteams/hardware-requirements-for-the-teams-app A student is supposed to follow the requirements below: Check your computer for compliance with technical requirements no later than 7 days before the exam; Sign in with your corporate account (@edu.hse.ru); Check your microphone, speakers or headphones, webcam, Internet connection (we recommend connecting your computer to the network with a cable, if possible); Prepare the necessary writing equipment, such as pens, pencils, pieces of paper, and others. Disable applications on the computer's task other than the Zoom, Canvas application or the browser that will be used to log in to the Zoom, Canvas If one of the necessary requirements for participation in the exam cannot be met, a student is obliged to inform a professor and a manager of a program 2 weeks before the exam date to decide on the student's participation in the exams. Students are not allowed to: Turn off the video camera; Use notes, textbooks, and other educational materials; Leave the place where the exam task is taken (go beyond the camera's viewing angle); Look away from your computer screen or desktop; Use smart gadgets (smartphone, tablet, etc.) Involve outsiders for help during the exam, talk to outsiders during the examination tasks; Read tasks out loud. Students are allowed to: Write on a piece of paper, use a pen for making notes and calculations; Use a calculator; Turn on the microphone to answer the teacher’s questions; Ask a teacher for additional information related to understanding the exam task; Interact with other students if allowed. Connection failures: A short-term communication failure during the exam is considered to be the loss of a student's network connection with the Zoom for no longer than 1 minute. A long-term communication failure during the exam is considered to be the loss of a student's network connection with the Zoom for longer than 1 minute. A student cannot continue to participate in the exam, if there is a long-term communication failure appeared. The retake procedure is similar to the exam procedure. In case of long-term communication failure in Zoom during the examination task, the student must notify the teacher, record the fact of loss of connection with the platform (screenshot, a response from the Internet provider). Then contact the manager of a program with an explanatory note about the incident to decide on retaking the exam.

Interim Assessment

• Interim assessment (4 module)
  0.3 * Exam test + 0.3 * Homework + 0.15 * In-class graded task + 0.25 * Test

Bibliography

Recommended Core Bibliography

• Garcia-Molina, H., Ullman, J. D., Dawson Books, & Widom, J. (2014). Database Systems: Pearson New International Edition : The Complete Book (Vol. Second edition). Harlow, Essex: Pearson. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1418178
• Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern Database Management, Global Edition (Vol. Global edition, Twelfth edition). Boston: Pearson. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1419666

Recommended Additional Bibliography

• Abramson, I. (2009). Oracle Database 11g : A Beginner’s Guide. New York: McGraw-Hill Professional. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=285015
• Clark, D. (2017). Beginning Power BI : A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop (Vol. Second edition). Camp Hill, Pennsylvania: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1478775
• DuBois, P. (2014). MySQL Cookbook : Solutions for Database Developers and Administrators (Vol. Third edition). Sebastopol, Calif: O’Reilly Media. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=818089
• Juba, S., & Volkov, A. (2019). Learning PostgreSQL 11 : A Beginner’s Guide to Building High-performance PostgreSQL Database Solutions, 3rd Edition (Vol. Third edition). Birmingham, UK: Packt Publishing. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=2023612