Business Intelligence Databases Stefano Grazioli Stefano Grazioli Ask

Business Intelligence Databases Stefano Grazioli © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

Critical thinking 2 § Still doing well § Easy meter

Why do we need Databases? § § What happens to your data when you quit your homework program? What if you want to share your data? Database 3

DBs vs. DBMSs § § A database (DB) is an organized collection of data A Data. Base Management System (DBMS) is a software that provides data-related functionality User location YOU 4 ‘Client’ program Data center DBMS Data. Base file(s) DBMS: Access, Oracle, IBM DB 2, SQL Server, My. SQL. . .

DBs and the Web User location Web server YOU User or program Browser program 5 Data center DBMS Data. Base file(s)

When is a DBMS “relational”? § § A DB is relational when it presents data as tables and the tables have defined relationships. Oracle, IBM, Microsoft, My. SQL/Oracle, SAP/Sybase, AWS Aurora… 6 https: //db-engines. com/en/ Gartner 2009, 2013 – Chart by IDC, Bernstein 2015, Blair 2019

BI and Analytics “BI refers to the general ability to organize, access and analyze information in order to learn and understand the business. ” (Gartner) 7 Analytics is “the extensive use of data, statistical and quantitative analysis, explanatory and predictive models, and fact-based management to drive decisions and actions” (Davenport and Harris – Competing on Analytics)

You do the talking 8 § Name, major § Learning objectives § Things you like about the class § Things that can be improved

Congratulations! You are hired at Small. Bank Homework 06 © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

You are a Business Intelligence analyst at Small. Bank Welcome! So glad that you are here and can help us with Business Intelligence! I have so many business questions for you… (1) I need a list of our Customer in Austin, TX: first names, last names and IDs (2) What are the rates that we offer on our small loans? Prepare a report with the loan IDs and rates for the loans under $250, 000 (3) …. Please access our MS SQL Server and create these reports for me… No problem, ? ? ? boss! 10

Accessing Business Data User location SQL Client Visual Studio Sql queries Data center RDBMS MS SQL Server F-sg 6 m-s 4 Small. Bank data: Loan, Loan Officer, Customer, Insurance Plan A file on the server 11

Example Table: Loan Officer ACTUAL TABLE DIAGRAM LO id f name l name phone 12 demo

Reading the Data Model Primary key: a unique identifier used to retrieve the record Relationship between two tables 13

Reading the Relationships 1 6 “A loan officer has many loans, a loan has one loan officer” One 5 2 3 14 Many 4

Small Bank DB: Data Dictionary § Loan l_id = the loan unique id principal, rate, date_due, § Loan officer lo_id = the loan officer’s unique id f_name, l_name, phone § Customer c_id = the customer’s unique id f_name, l_name, city, state § Insurance plan coverage = maximum amount covered premium = amount to pay every year 15

WINIT What Is New In Technology? © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

SQL Queries © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

SQL § Structured Query Language § The standard language for databases 70 s: § - Codd - SEQUEL – Structured English QUEry Language § 18 commercialized it in 1979 § 1986 ISO/ANSI § Sometimes sought as a required skill in interviews

SQL example: a query SELECT C_id, Last_Name, State FROM Customer WHERE State = ‘TX’ ORDER BY Last_Name; 19

4 basic SQL commands 20 § SELECT = ‘search’ § INSERT a new row § UPDATE an existing row § DELETE a row

What is the total lent to Peter Parker? LOAN 21 CUST IN LOAN CUSTOMER l id c id f name l name city state l id principal rate due 2 $1, 000 7% 2020 2 334 Peter Parker New York NY 3 $1, 000 10% 2022 3 410 335 Bruce Wayne New York NY 4 $3, 500, 000 5% 2023 4 334 347 Bruce Wayne TX 6 $1, 000 7% 2024 6 410 400 Barbara 8 $2, 000 5% 2024 8 334 410 Lois Lane Austin TX 9 $500, 000 4% 2020 9 335 411 Clark Kent Austin TX 10 $2, 000 5% 2024 6 411 10 347 Austin Gordon New York NY

Navigating the Relationships (‘join’) FK foreign key: a PK in another table, used to link two or more tables FK FK 22

Joins are commonsense If I give you c_id ‘ 4977’, can you give me the phone numbers of the loan officers for that client? 23

SQL Query Select loan_officer. phone If I give you c_id ‘ 4977’, can you give me the phone numbers of the loan officers for that client? from customer_in_loan, loan_officer where customer_in_loan. c_id = '4977' and customer_in_loan. l_id = loan. l_id and loan. lo_id = loan_officer. lo_id 24