Introduction to OLTP and OLAP Databases

1. IS6146 Databases for Management Information SystemsLecture 1: Introduction to IS6146 Rob Gleasure R.Gleasure@ucc.ie robgleasure.com

2. IS6146 • Contact me at • Ext 2503 • Room 2.112 • R.Gleasure@ucc.ie • Website for this course • robgleasure.com

3. IS6146 • Module content and learning outcomes • Using relational databases (i.e. SQL) • NoSQL and distributed data stores • Analysing unstructured/semi-structured data

4. IS6146 • Course Assessment • Continuous assessment: 50 marks • In-class exam – 20 marks • Group report – 30 marks • Exam: 50 marks • Staff Student Support Committee meeting 31st January, 15.00-16.00, Room 2.120.

5. Continuous Assessment • In-class SQL exam (20 marks) • 21st February* • Reports due (20 marks) • 11th April* *Provisional dates

6. OLTP Databases • Online transaction processing (OLTP) databases/data stores support ongoing activities in an organisation • Hence, they need to • Manage accurate real-time transactions • Handle reads, writes, and updates by large numbers of concurrent users • Decompose data into joinable, efficient rows (e.g. normalised to 3rd form) • These issues are often labelled ACID database transactions • Atomic: Every part of a transaction works or it’s all rolled back. • Consistent: The database in never left in inconsistent states • Isolated: Transactions do not interfere with one other • Durable: Completed transactions are not lost if system crashes

7. OLAP Data stores • Online analytical processing (OLAP) databases/data stores are used to support descriptive/diagnostic/predictive/prescriptive analytics • Hence, they need to • Allow vast quantities of historical data to be accessed quickly • Be updatable in batches (often daily) • Aggregate diverse structures with summary data • These issues are often labelled BASE database transactions • Basic Availability • Soft-state • Eventual consistency

8. Data warehousing OLTP OLAP We’ll come back to this stuff later in the course Business intelligence database Operational databases HR and payroll Extract Transform Load Data warehouse Data mining Sales and customers Visual-isation Orders Reporting Technical support Purchased data

9. Database Management Systems • A Database Management System (DBMS) is an OLTP software package designed to store and manage databases. • This is different from a database file for several reasons • DBMS makes tables visible and generates reports • DBMS helps less technical users to use data efficiently • DBMS protects data from inconsistency and duplication due to multiple concurrent users • DBMS handles crash recovery • DBMS handles security and access control

10. Database Management Systems • DBMS operates at the physical level • This is where data handling becomes increasingly technical Management and business users Conceptual model System analysts and designers Logical model Physical model Database administrators

11. Database Management Systems • DBMS are typically managed by a set Database Administrator, whose responsibilities include • Defining or refining logical schema in response to requirements • Granting appropriate access to users • Monitoring DBMS performance • Updating/changing DBMS software

12. Database Management Systems • Other users will include • Naïve users (use applications that access/modify data) • Sophisticated/specialised users (use DBMS directly to generate reports and assist business analysis) • Application developers (write programs that need to access/modify data)

13. Examples of DBMS Oracle • Large multi-user DBMS that excels at handling many clients requesting, accessing, and modifying data • Popular in large organisations and cloud services due to scalability • Very effective handling of concurrency, read consistency, parallel execution, and locking mechanisms MS Access • Popular DBMS for small scale projects due to its relatively low cost and usability • Imports and exports data to many formats commonly used in organisations, e.g. Excel, Outlook, SQL Server, Oracle • Integrates easily with MS tools like VB, C# and .NET

14. Examples of DBMS MySQL • Open source SQL database with free and paid versions • Flexible and scalable • Open source means lots of support and the potential for adaptability MS SQL Server • Good performance • XML integration • Inclusion of try/catch queries

15. Accessing a DBMS with SQL • So once we have our data models in place and our DBMS set up, how do we get started using our data?  The SQL (pronounced like sequel) query language • SQL (Structured Query Language) was introduced in the 70’s and has evolved since to add features such as • Compatibility with object-oriented programming • Compatibility with XML • Compatibility with XQuery

16. Accessing a DBMS with SQL

17. The Structure of SQL • SQL can be divided into two parts: • The Data Manipulation Language (DML) • Used to create, read, update and delete tuples (CRUD operations) • Mostly used by application programmers and sophisticated/specialised users • The Data Definition Language (DDL) • Used to define database structure (relation schemas) and data access control, as well as integrity constraints and views • Mostly used by database administrator

18. Executing SQL in a DBMS

19. Accessing DBMS with SQL • The basic syntax of SQL queries are as follows: COMMAND column_name1, column_name2, … FROM/SET/VALUES table_name1, table_name2, … WHERE column_name comparison_operator value; • All statements in a semi-colon

20. The SQL Select statement • The SELECT statement is used to retrieve data from a database into a result table, or result-set • You can use an asterisk (*) instead of a column name if you wish to select all columns satisfying the criteria e.g. SELECT * FROM my_table;

21. The SQL Select statement • Let’s open up an online example from W3Schools • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

22. The SQL Select statement • Sometimes non-primary key columns may contain duplicate values - you can also use SELECT DISTINCT when you want to avoid duplicates in your result-set e.g. SELECT DISTINCT * FROM my_table;

23. The SQL Where clause • A number of comparison operators are possible with the WHERE clause • Examples • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_between_in • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_in

24. The ‘LIKE’ condition and Wildcards • Sometimes we want to identify records based on slices of the data within a cell - we do this using wildcards and the LIKE condition • Examples • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_percent • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_underscore • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_charlist&ss=-1

25. The SQL Where clause (continued) • Numerous clauses can be combined with the keywords AND & OR e.g. SELECT * FROM my_table WHERE val1=‘true’ AND val2=‘false’; • Complex clause can be creates by nesting clauses in parentheses e.g. SELECT * FROM my_table WHERE val1=‘true’ AND (val2=‘false’ OR val2=‘null’); • Example • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_where_and_or

26. The SQL Order By keyword • The ORDER BY keyword is used to sort the result-set e.g. SELECT * FROM my_table ORDER BY NAME; • You may also specify whether you want the returned result-set to be sorted in ascending or descending order by using the keywords ASC or DESC e.g. SELECT * FROM my_table ORDER BY NAME DESC; • Example • http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby_desc

27. Report • Identify a real workplace (this should be referred to as a pseudonym in all written documentation to preserve anonymity) • For this workplace, your core requirements are as follows • Describe the workplace • What kind of business it is and why it’s interesting from a data perspective • Describe your interactions with them • How many people did you speak with, how often did you speak with them, and what was the value of those interactions • Profile the data-related technologies currently in use • What types of system do they currently use and how do they feel about them

28. Report (continued) • Build a conceptual model (ERD) of the data involved, including any improvements you feel are possible • How could existing data be better organised? What new data sources are possible? How do new data link to existing structures? • Design and develop a database (e.g. MS Access) to implement this improved data model • How did you implement this – were you able to show it to them and get feedback? • Design and develop OLAP functions that create additional business intelligence • Views and reports for management • Social media analysis for market research • Static analyses of stored data

29. Report • Groups should self-select, 3 people is ideal, 4 is ok • Your submission will include • A USB or CD storing all of the files and code necessary to run your system • An individual email directly to me stating the proportion of contribution made by each of your group mates