CMPE 226 Database Systems January 31 Class Meeting

1. CMPE 226Database SystemsJanuary 31 Class Meeting Department of Computer EngineeringSan Jose State UniversitySpring 2017Instructor: Ron Mak www.cs.sjsu.edu/~mak

2. Basic Info • Office hours • Th 2:30 – 4:30 PM • ENG 250 • Website • Faculty webpage: http://www.cs.sjsu.edu/~mak/ • Class webpage: http://www.cs.sjsu.edu/~mak/CMPE226/ • Green sheet • Assignments • Lecture notes

3. Course Objectives • The primary goal of this class is to learn the fundamentals of databases and data management tools and procedures in order to develop a significant data-driven enterprise application by the end of the semester . • You will gain important data management and project development skillsthat are valued by employers.

4. Course Objectives, cont’d • You will understand practical aspects of data modeling, relational databases, SQL, and object-relational mapping. • You will learn how to develop a web-based front-end for a back-end database. • You will understand how to design and deploy operational databases and analytical databases.

5. Course Objectives, cont’d • You will also learn about data warehousing, OLAP (online analytical processing), data virtualization, unstructured data, XML,and NoSQL databases. • With cooperation from local companies, you will practice using their commercial data management tools for your assignments and projects.

6. Course Objectives, cont’d • Not course objectives: • Deep expertise in any one topic • Database theory • Database management system (DBMS) implementation • Database administration (DBA)

7. Permission Codes? • If you need a permission code to enroll in this class, see the department’s instructions at https://cmpe.sjsu.edu/content/Undergraduate-Permission-Number-Requests • Complete the form at https://goo.gl/forms/Ayl0jablW5Ythquf1

8. Prerequisites • CMPE 272: Enterprise Software Platforms • Grade C- or better • Or instructor consent • To get instructor consent, you must show: • Experience working with enterprise systems. • Experience working with (but not necessarily designing) databases

9. Required Text • Database Systems: Introduction to Databases and Data Warehouses • Authors: NenadJukic, Susan Vrbsky, and SvetlozarNestorov • Publisher: Prospect Press, 2017 • Paperback ISBN: 978-1-943153-19-0 (available from Redshelf.com) • eTextbook ISBN: 978-1-943153-18-3 (available from Redshelf.com and VitalSource.com) These are much less expensive versions of the textbook originally published in 2014 by Pearson. Pearson ISBN: 978-0-13-257567-6

10. Software to Install • Download and install XAMPP • https://www.apachefriends.org/index.html • Available for Mac, Windows, Linux • Contents: • Apache Web Server • PHP • MariaDB database server (compatible with MySQL)

11. Project Teams • Assignments will be done by small project teams. • Form your own teams of 4 members each. • Choose your team members wisely! • Be sure you’ll be able to meet and communicate with each other and work together well. • No moving from team to team.

12. Project Teams, cont’d • Each team member will receive the same score on each team assignment and team project. • Each team email to ron.mak@sjsu.eduby Monday, February 6: • Your team name • A list of team members and email addresses • Subject:CMPE 226 TeamTeam Name • Example: CMPE 226 Team Super Coders

13. Individual Responsibilities • You are personally responsible for participating and contributing to your team’s work, and for understanding each part of the work for every assignment whether or not you worked on that part.

14. Postmortem Assessment Report • At the end of the semester, each student will individuallyturn in a short (one page) report: • A brief description of what you learned in the course. • An assessment of your personal accomplishments for your project team. • An assessment of each of your project team members. • This report will be seen only by the instructor.

15. Final Individual Class Grade Your final class grade will be adjustedup or down depending on your level and quality of participation, as determined by the project tracking tools and your teammates’ postmortem reports. • 30% assignments • 35% project • 15% midterm • 20% final • During the semester, keep track of your progress in Canvas. • At the end of the semester, students with the median score will get the B+ grade. • Higher and lower grades will then be assigned based onhow the scores cluster above and below the median. • Therefore, your final class grade will be based primarily on your performance relative to the other students in the class.

16. Participation is Important • Can move your final grade up or down, especially in borderline cases. • Participation in class. • Participation in your team. • As reported by the postmortem assessment reports.

17. Install XAMPP • Download and install XAMPP • Installs and configures Apache (with PHP) and MariaDB in one package. • Both Windows and Mac. • See: https://www.apachefriends.org/index.html

18. XAMPP Control Panel • Use the XAMPPcontrol panel to start or stop: • Apache Web Server • MariaDB Database Server • FTP server

19. “localhost” Home Page First, you may have to visit http://localhost/xampp/lang.php?en which automatically initializes some pages.

20. XAMPP Directory Structure Folder htdocs is the root of all the web pages on your web server.

21. Take Roll!

22. Key Database Concepts • Why have databases? • Why not simply store all our data in plain files? • What advantages do databases provide? • Sophisticated modern database techniques have been developed by computer scientists starting in the 1970s.

23. Major Issues in Transaction Processing • Efficiency • Algorithms permit thousands of customers to simultaneously conduct transactions. • No conflicts or inconsistencies. • Reliability • Algorithms allow data to survive intact despite storage and network failures. • Canonical example: Online banking

24. Fundamental Ideas • Write-ahead logging • To-do list • Two-phase commit • Prepare then commit • Relational databases • Virtual tables

25. Structured vs. Unstructured Data • Example of unstructured data: • Example of structured data: Rosa is 22 and friends with Mike, who is 23. Jill is 25 and Steve is 24. There are friendships among the four of them.

26. Data Consistency • What is wrong with this table? • Rosa is a friend of Jill, but Jill is not a friend of Rosa. • This type of data inconsistency is easy to avoid when new data is added to the database. • Check for the rule “If a R b then b R a”where R is some relation such as “friends of”.

27. Data Consistency, cont’d • But other types of data inconsistency are harder to detect and require more advanced solutions.

28. Data Consistency, cont’d • Computers can crash. • After a crash, data that wasn’t saved to external storage may not be recoverable. • Storage devices such as disk drives can only write small amounts of data at a time. • Typically, one sector at a time, often 512 bytes. • Therefore, a disk file is changed only a few hundred characters at a time.

29. Data Consistency, cont’d • Suppose we can update only one row of a database table at a time on disk. • Table to update: • Update the first row:

30. Data Consistency, cont’d • Now the computer must update the second row to indicate that Jill’s friends include Rosa. • If the computer crashes before that happens, the table is left in an inconsistent state. • This is easy to detect and fix with a program that periodically scans the database.

31. Data Consistency, cont’d • A banking example: • Sally requests a transfer of $200 from her checking account to her savings account. • Update the first row by reducing the checking balance by $200.

32. Data Consistency, cont’d • Then the computer crashes. • Sally has lost $200. • There is no apparent data inconsistency! • Initially, Sally had $1100 in both accounts. • After the crash, she has only $900. • Sally did not withdraw any money. • How to prevent this type of inconsistency?

33. The Transaction Concept • A transaction is a set of changes that must all occur in order for the database to remain consistent. • If only some of the changes in a transaction are performed, the database might become inconsistent. • A database program issues a begin transaction command before issuing the set of changes, and finishes with an end transaction command. • The database computer must guarantee that all the changes will occur.

34. The Transaction Concept, cont’d • But what if the database computer crashes in the middle of a transaction? • After the computer restarts, the application program is told that the transaction failed. • The program “rolls back” the transaction to return the database to its consistent state before the transaction started. • The program resubmits the transaction. • If it’s successful, the database is still consistent.

35. Write-Ahead Logging • How to implement transactions? • Use a write-ahead log (a “to-do list”). • In some permanent storage, the database maintains a log of actions it’s planning to do, such as for a transaction. • Even if there’s a crash and then a restart, the list of actions has survived and can be reused.

36. Write-Ahead Logging, cont’d • Write-ahead log • Begin transfer transaction. • Update Sally’s checking balance from $800 to $600. • Update Sally’s savings balance from $300 to $500. • End transfer transaction.

37. Write-Ahead Logging, cont’d • What’s wrong with this log? • Begin transfer transaction. • Subtract $200 fromSally’s checking balance. • Add $200 toSally’s savings balance. • End transfer transaction.

38. Write-Ahead Logging, cont’d • The log must be idempotent. • Begin transfer transaction. • Update Sally’s checking balance to $600. • Update Sally’s savings balance to $500. • End transfer transaction. • Each action is undoable. • Each action can be performed multiple times.

39. Atomicity • Every transaction is atomic. • A transaction cannot be divided into smaller actions. • Either the entire transaction completes successfully, or the database is returned to its state before the transaction started.

40. Atomicity, cont’d • Therefore: • Is consistency sufficient for efficiency and reliability? • Transactions prevent data corruption,but what about data loss? write-ahead logging transactions consistency

41. Break

42. Transactions and Database Locking • In a busy database, many transactions can be occurring at the same time. • Sometimes, it is important for the database to lock certain records during a transaction.

43. Transactions and Database Locking, cont’d • For example, during the transaction to transfer $200 from Sally’s checking account to her savings account, those two rows of the database table must be locked. • You don’t want another transaction to modify those same rows while the first transaction is occurring.

44. Transactions and Database Locking, cont’d • A transaction can fail if a deadlock occurs. • Transaction A locks Sally’s checking rowand Transaction Blocks the savings row. • Now Transaction Awants to lock thesavings row, andTransaction B wantsto lock the checking row. • Each transaction is locked out by the other. A A A B B B X X

45. Transactions and Database Locking, cont’d • If the database detects that a deadlock has occurred between two transactions, it must abort and roll back one of the transactions. X X A A B B

46. Replicated Databases • Transactions allow a database to recover from certain types of crashes. • You can roll back and restart a transaction. • Assumption: The data that existed before the transaction is still there. • What if the database’s hard drives crashed with permanent data loss? • Multiple copies of a replicated database are stored in different locations.

47. Replicated vs. Backup • A backup of a database is a snapshot of the contents of a database at a particular time. • Backups can be made automatically, such as nightly. • A backup is not necessarily up-to-date, since changes to the database since the last update are not captured until the next update. • A replicated database keeps all copies of the database in sync at all times. • Each change to the database is instantly made to all the replicas.

48. Two-Phase Commit Protocol • What happens if one of the replicas encounters a problem during a transaction?

49. Analogy of a Two-Phase Commit • You are on a three-student project team. • You need to schedule a meeting. • You propose 7:30 to Teammate A. • Teammate A agrees. • You tell her to pencil in that time and wait for confirmation. • You propose 7:30 to Teammate B. • Teammate B agrees. • You confirm with Teammate B. • You confirm with Teammate A. Phase 1:Prepare Phase 2:Commit

50. Analogy of a Two-Phase Commit, cont’d • But suppose Teammate B can’t make it at 7:30. • Tell Teammate B to forget 7:30. • Tell Teammate A to forget 7:30. • Choose a different time and repeat. Phase 2:Abort