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

Programming and Databases

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

Course Syllabus

Abstract

The course consists of two parts. The first part is teaching at advanced level the specific features of spreadsheet software MS Excel for economic and financial tasks as well as with data and output analysis. This part also considers the Visual Basic for Applications (VBA) programming, which facilitates processing spreadsheet data and increases the productivity in MS Excel. The second part is teaching databases. It deals with Microsoft Access, which is used to create and manage small and midsize computer-based databases on desktop computers and/or on network connected computers for the Microsoft Windows family of operating systems. This part also covered SQL to communicate through the server.
Learning Objectives

Learning Objectives

  • To give students a holistic view of modern information technologies and their role in professional activities.
  • To develop programming skills in using the object-oriented language aiming on expanding the functionality of MS Excel and MS Access
  • To show how integrate MS Access with SQL Server and other Business Intelligence (BI) applications.
  • To teach how to build business applications that integrate local data, web-generated data, and data available from SQL Server and other sources
Expected Learning Outcomes

Expected Learning Outcomes

  • use built-in functions for data analysis
  • solve system of equations by graphing
  • work with large series of data
  • use MS Excel Add-ins (Analysis ToolPak, Solver) for solving economic tasks
  • expand the functionality of MS Excel by VBA
  • create a Database and Using the Tools
  • manipulate data using SQL, queries, and recordsets with Data Access Objects (DAO)
  • create and use Forms and Reports
  • use Other Applications with MS Access
  • confidently use Microsoft SQL Server
  • use Microsoft BI: PowerPivot, PowerView, PowerMap
Course Contents

Course Contents

  • Computer Architecture
    Introduction. Computer science and information. The digital information units. Computer hardware. Characteristics of the main computer devices, their impact on efficient work. Optimization of Hardware Performance 1.3. System and application software. The operating system: Windows, MacOS, Unix (Linux). Types of application software Setting up the working environment. Standard programs of the Windows system. The file system and working with it. Search for files. The Explorer program. Service programs: archivers, antiviruses.
  • Networks and Internet. (
    Network types. Local and global networks, Intranet, Extranet, Internet,. 4 Internet. Principles of functioning. Names and addresses of computers, a uniform resource locator (URL).Internet applications types: online media, online information search, online communications, online communities, online entertainment, e-business, online finance and other applications. E-mail. The structure of the mailbox. Elements of the letter. Effective organization of business correspondence: reasonable subject, filtering of letters, automatic signature, notification of receipt of a letter, etc. Teamwork tools on examples of Google, Microsoft (Office 365, including Teams, One Drive). Internet services.Search engines and portals. The search query optimization .
  • Formatting text via MS Word
    Basic editing. Character formatting. Paragraph formatting. Creating tables. Working with themes, style sets, backgrounds, quick parts, and text boxes. Using illustrations and graphics
  • Creating presentations via MS PowerPoint.
    Create and edit slides. Import data from other sources. Create speaker notes. Add charts and SmartArt. Use PowerPoint drawing tools. Set up and use a slide library. Managing the presentation display. Save in different formats.
  • Using built-in functions for data analysis
    Computational and financial Excel functions. Conditional formatting for results highlighting.
  • Graphical Data Analysis in MS Excel
    Charts, graphs, and their properties. Customizing different charts. Smoothing. Managing graphic objects in MS Office. Graphical data analysis. Sparklines for visual representation of data. Solving system of equations by graphing. Microsoft Equation Editor
  • Working with large series of data.
    Excel database. Creating a database from an Excel spreadsheet. Sorting, searching and editing. Filtering, AutoFilter. Creating custom filters using Excel Advanced Filter. Create two or more sets of Conditions. The use of computed criteria. Database functions. Vertical and horizontal lookup functions. Subtotalling the data. Merge spreadsheets using Data Consolidation. Pivot Tables and Charts. Creating and manipulating pivot tables. Sorting and filtering subtotals. Calculations in pivot tables: additional calculations, calculated fields and objects. Pivot charts. Printing pivot table reports. Printing multi-page tables.
  • MS Excel Add-ins for solving economic tasks.
    Microsoft Excel add-ins for statistical tasks (Analysis ToolPak) and optimization (Solver). Analysis ToolPak for Microsoft Excel: finance, statistics and engineering functions. Solver Add-In. What-If analysis. Using Solver for solving systems of linear and non linear equations. Goal Seek. Solving system of equations.
  • Expanding the functionality of MS Excel by VBA.
    Type declaration of variables. Built-in data types. Scope and lifetime of variables. Variable initialization. Arrays. Custom data types. Dialog boxes for data input/output. Data manipulation. Loop and Conditional Statements used in VBA Excel programming. Object variables. Object properties and methods. Object model. Object collections. Referencing objects Manipulating forms: constructor mode, run mode. Event handlers, global variables. Forms initialization. Text controls. Choosing controls. Manipulating form controls. Creating and manipulating lists.
  • Database concepts and terminology
    Types of Database Management Systems: Network, Hierarchical, Relational. Object-oriented Database Management Systems.
  • Relational database management systems
    Relational operations. Relational algebra. Relational calculus. Normalization. Structured Query Language. Designing a Database
  • MS Access Basics. Creating a Database and Using the Tools
    Starting Access. Creating a new file. Trusting a file. The Quick Access toolbar. Backstage view. Exploring ribbons. Using the Navigation pane. Getting help. Planning and designing your database. Creating tables using Application Parts. Creating tables in Layout view with Quick Start. Creating and editing tables in Design view. Setting a primary key. Creating a lookup field. Creating multi-value fields. Using calculated fields. Setting field properties. Setting input masks. Setting validation rules. Creating relationships and enforcing referential integrity. Viewing subdatasheets. Entering data into your tables. Formatting tables. Finding, sorting, and filtering records
  • Queries
    Using the Query Wizard. Creating a query in Design view with criteria. Creating wildcard queries, reusable parameter queries, yes/no queries, "and" and "or" queries, building calculation queries, statistical queries, crosstab queries. Using update and delete queries. Manipulate data using SQL, queries, and recordsets with Data Access Objects (DAO)
  • Forms and Reports
    Creating data-entry forms. Using the Form Wizard. Modifying a form in Layout view. Using Design view. Setting tab stops. Adding buttons to a form. Navigation forms. Introduction to reports. Using the Report Wizard. Formatting reports in Layout view. Identifying report structure in Design view. Adding group and sort capabilities to a report. Adding existing fields from other tables. Adding totals and subtotals to a report. Adding conditional formatting and data bars to a report. Creating multi-table reports. Creating mailing labels. Printing reports
  • Using Other Applications with MS Access
    Importing Excel and text data. Exporting data into Excel. Advanced Tools in the Database. Customization. Compacting and repairing a database. Using data analysis tools. Encrypting a database and setting a password. Splitting a database. Customizing the ribbons. Setting Access options.
  • Microsoft SQL Server
    Using SQL Server. SQL Server Management Studio. SQL Server Security. Authentication, Server and Database Roles, Ownership and User-Schema Separation, Permissions in SQL Server. Upsizing Access to SQL Server.
Assessment Elements

Assessment Elements

  • non-blocking Home assignments and class work
  • non-blocking Mid-term exam
  • non-blocking End-of-term exam
Interim Assessment

Interim Assessment

  • Interim assessment (1 module)
    0.3 * Home assignments and class work + 0.7 * Mid-term exam
  • Interim assessment (2 module)
    0.45 * End-of-term exam + 0.15 * Home assignments and class work + 0.4 * Mid-term exam
Bibliography

Bibliography

Recommended Core Bibliography

  • Goldmeier, J. (2014). Advanced Excel Essentials. [United States]: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=898783

Recommended Additional Bibliography

  • - Работа в Microsoft Access XP - Национальный Открытый Университет "ИНТУИТ" - 2016 - 127с. - ISBN: - Текст электронный // ЭБС ЛАНЬ - URL: https://e.lanbook.com/book/100430
  • Биллиг В.А. - Основы офисного программирования и язык VBA - Национальный Открытый Университет "ИНТУИТ" - 2016 - 708с. - ISBN: - Текст электронный // ЭБС ЛАНЬ - URL: https://e.lanbook.com/book/100309