Databases II
Objectives
- Present and compare the characteristics of on-line transaction processing systems (OLTP) and online analytic processing (OLAP).
- Introduce the concepts of business intelligence and data warehousing.
- Provide students with methodologies adequate to the desigh and analysis of tabular and multidimensional models.
- Provide competence in multidimensional analysis and design assuring that students have the necessary skills to embrace the task of designing data warehouses and data marts that are efficcient and adapted to the business needs.
- Introduce ETL (Extract Transform and Load) tools to allow the data migration between OLTP and OLAP systems.
- Introduce tools to analyse, visualize and report existing data.
- Introduce the subjects of Information Security and Privacy in the context of databases.
General characterization
Code
100014
Credits
6.0
Responsible teacher
Henrique José de Jesus Carreiro
Hours
Weekly - Available soon
Total - Available soon
Teaching language
Portuguese. If there are Erasmus students, classes will be taught in English
Prerequisites
Knowledge acquired in the Databases I namely data modeling and SQL
Bibliography
Elmasri, R., & Navathe, S. B. (2017). Fundamentals of database systems. Hoboken, NJ: Pearson.
Petkovic, D. (2017). Microsoft SQL server 2016: a beginner's guide. New York: McGraw Hill Education.
Kimball, R., & Ross, M. (2013). The data warehouse toolkit: the definitive guide to dimensional modeling. Hoboken, NJ: Wiley.
Larson, B. (2017). Delivering business intelligence with Microsoft SQL server 2016. New York: McGraw-Hill Education.
Teaching method
Theoretical classes with the presentation of models from different functional contexts (e.g. Retail Sales, Procurement, Telecommunications, Education, Web Commerce) and practical classes with tutorials and exercises.
Evaluation method
Option 1
- 2 quizzes (10%+10%, minimum grade: 8).
- Group project with discussion and presentation (40%, minimum grade: 8).
- First call exam (40%, minimum grade: 8).
Option 2
- Group project with discussion and presentation (30%, minimum grade: 8).
- Second call exam (70%, minimum grade: 8).
Subject matter
- Differentiate between operational systems (OLTP) and decision support systems (OLAP).
- Conceptual, Logical and Physical Design for Data Marts and Data Warehouses.
- Data Warehouse architectures.
- The four step dimensional design process.
- Tabular and dimensional data modelling.with application in SQL Server Analysis Services (SSAS).
- Introduction to ETL with SQL Server Integration Services (SSIS).
- Designing and Implementing Control and Data Flow in SSIS.
- Introduction to Data Analysis, Visualization and Report using Power Pivot and Power BI.
- Data exploration with Power Pivot and Excel Power BI using Data Analysis Expressions (DAX).
- Introduce the themes of Information Security and Privacy in the context of databases.