Financial Information Management Business Intelligence Databases Stefano Grazioli

Critical thinking § § § Roll call: last name, first name in the chat Going well S: folder ‘Grazioli’ Bug bounty program Easy. Meter Part II: Business Intelligence

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 Data. Base (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: Oracle, IBM DB 2, SQL Server, My. SQL. . .

DBs and the Web User location Data center Web server YOU Browser program 5 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

Financial Information Management Congratulations! You are hired at Small. Bank Homework 06

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 customers 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

The Data Model Primary key: a unique identifier used to retrieve the record 13

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 14

Financial Information Management WINIT What Is New In Technology?

Financial Information Management SQL Queries

SQL § § § Structured Query Language The standard language for databases 70 s: - Codd - SEQUEL – Structured English QUEry Language § § § 17 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; 18

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

What is the total lent to Peter Parker? LOAN 20 CUST IN LOAN l id principal rate due 2 $1, 000 7% 3 $1, 000 4 CUSTOMER l id c id f name l name city state 2020 2 334 Peter Parker New York NY 10% 2022 3 410 335 Bruce Wayne New York NY $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

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

Joining tables lets you cross-reference info in different tables FK foreign key: a PK in another table, used to link two or more tables FK FK 22

SQL Query If I give you c_id ‘ 4977’, can you give me the phone numbers of the loan officers for that client? Select loan_officer. phone 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 23