Databases and Database Management System

1. Databases and Database Management System

2. Goals • comprehensive introduction to • the design of databases • database transaction processing • the use of database management systems for applications

3. Topics • the entity-relationship (E/R) and relational data models • approaches to database design • abstract query languages (relational algebra) • SQL • issues in database design and use (views, integrity constraints, triggers, transactions, and security) • physical data organization • query processing and optimization • transaction processing • advanced topics (data warehouses, data mining, temporal databases, and XML) [time permit]

4. Book • Textbook: Databases and Transaction Processing – An Application-Oriented Approach by Lewis, Bernstein, and Kifer (Addison Wesley) • Additional reading: • A first course in Database Systems by Ullman & Widom • Database systems the complete book by Garcia-Molina, Ullman, & Widom

5. Organizational issues • Class web site: http://www.cs.nmsu.edu/~tson/classes/fall03-482 • 5 weeks: First mid-term (September 18) • 11 weeks: Second mid-term (October 28) • December 8: Final • Office hours: 4-5 pm Tues & Thus. I will need to go home right after the class on Tues, please do not try to ask questions after Tue’s class • Use of emails: questions, notifications • Homework submission – web based

6. What is a Database? • American Heritage Dictionary: a collection of data organized for easy and speed of search and retrieval

7. What is a Database? • Related to data, perhaps lot of them • same characteristics • over long period of time (months, years, …) • Pertinent to people who use the data • Might or might not be of interest to other

8. Definition and Properties • Definition: A database is a collection of data central to some enterprise that is managed by a Database Management System (DBMS) • Properties: • Essential to operation of enterprise (contains the only record of enterprise activity) • Valuable to the enterprise (Historical data can guide enterprise strategy, might be of interest to other enterprises) • Reflection of the state of the enterprise (database is persistent)

9. DBMS • Specialized software manages databases • create new databases • modify existing databases (update data, create reports for different purposes) • Supports • high-level access language (e.g. SQL) • application describes database accesses using that language.

10. DBMS • Provides users with • Persistent storage: like file system but much more flexible • Programming interface: accessing and modifying data through a query language • Transaction management: concurrent access to data

11. Overview of a DBMS Admin DBMS (Oracle, DB2, MySQL, …) Data-bases Results Modify/ Retrieval (Command) Users

12. Overview of a DBMS (Cont.) Transaction Manager Results Storage Manager commands M Query Processor E Users • Storage manager: uses OS techniques in memory management • (buffer, page, read/write pages) • Query processor: receive queries, create query plans, send to execution • engine primitive commands (index, file, record requests), • which will be satisfied by the corresponding manager • Transaction manager: maintains the consistency of the database • (control read from/write to a database, concurrency execution, • recovery) Data-bases E M

13. Overview of a DBMS (Cont.) • Database administrator: • set-up databases • creates new schema • modifies existing schema • manages users (authorization, permission, etc.) • uses Data Definition Language – a specialized language for creating and defining database schema

14. query plan Execution Engine index,file, record requests Index/File Record Manager Data-bases page commands Buffer Manager Storage Manager Buffer read/write pages flow & data data Query Processing User/application queries/updates Query Compiler

15. Transaction Manager • Responsible for the consistency of database • changes in the real-world are reflected correctly in the database • every time a real-world event happens, a transaction occurs to cause the corresponding changes in the database • Definition: A transaction is an application program with special properties – see next slides – to guarantee it maintains database correctness

16. Properties of Transactions (ACID) • Atomicity: ALL-or-NOTHING execution (a sequence of primitive commands that needs to be executed ALL or NONE). • Isolation: No two transactions should be executed at the same time. • Durability: Effects of a transaction can never be lost • Consistency: Constraints are satisfied all the time

17. Transaction Manager • Log manager: every change in the database is logged separately on the disk (for recovery or durability) • Concurrency-control manager: for isolation (uses lock, similar to lock in OS) • Deadlock resolution: resources control

18. Today • Examples of database systems (databases) • Characteristics of current db vs. old one • Requirements on db systems • People involving in designing, implementing, and maintaining of db • Study directions in db systems • Two common applications of db

19. Examples of databases • Airline reservation system • Banking system • Student registration system • Supermarket • Corporate record • ….

20. Airline reservation system • Data: Information about flights • Flight number, type of aircraft • Date, time, departure airport, arrival airport • Number of seats (1st, 2nd class if applicable) • Lists of travelers, their reservation • Ticket prices, number of available seats • Operations (Queries/Transactions): • Customer inquires about the availability of a flight, ticket for a flight • Customer makes a reservation • Customer cancels a reservation • Properties: • Large number of transactions (very frequently) • Cannot be processed in batch mode (on-line transaction processing) • Concurrency required

21. Banking system • Data: Account information • Customer information (name, address, accounts, balances) • Relationship between customers and accounts • Operations (Queries/Transactions): • Customer inquires about the balance of one of its accounts • Customer makes a deposit • Customer withdraws • Properties: • Large number of transactions (very frequently) • Cannot be processed in batch mode (on-line transaction processing) • Concurrency required • Recovering from failures

22. Student Registration System • Data: Information about students and courses • Student information (name, address, SSN, status, major, minor, courses taken and grade, courses enrolled, balance, picture) • Course information (name, call number, number, credit hours, department, instructor, date and time, location, number of students) • Operations (Queries/Transactions): • Students ask for a transcript, list of enrolled classes • Adding/Dropping classes • Prerequisites enforcement • Properties: • Large number of transactions at the beginning and end of semester • Batch mode processing possible (better with on-line transaction processing) • Concurrency required

23. Databases (Now vs. Then) • Relational model using SQL - high-level view of data • Older systems presented low-level view • Might contain multimedia data • Older systems restricted to alphanumeric data • On-line: database accessed at time of event • Older systems were off-line, batch

24. Databases (Now vs. Then) • Concurrent - multiple transactions execute simultaneously • Older systems processed transactions sequentially • Distributed computation - different parts of the application execute on different computers • Older systems were centralized

25. Databases (Now vs. Then) • Distributed data - different parts of the data are stored in different databases on different computers • Older systems were centralized • Heterogeneous - involves HW and SW modules from different manufacturers • Older systems were homogeneous • Accessed by everyone (e.g., e-commerce) • Older systems restricted to trained personnel

26. Database (System) Requirements

27. Database (System) Requirements • High Availability: on-line => must be operational while enterprise is functioning • High Reliability: correctly tracks state, does not lose data, controlled concurrency • High Throughput: many users => many transactions/sec • Low Response Time: on-line => users are waiting

28. Requirements (cont.) • Long Lifetime: complex systems are not easily replaced • Must be designed so they can be easily extended as the needs of the enterprise change • Security: sensitive information must be carefully protected since system is accessible to many users • Authentication, authorization, encryption

29. People in Design, Implementation, and Maintenance of a Database • System Analyst - specifies system using input from customer; provides complete description of functionality from customer’s and user’s point of view • Database Designer - specifies structure of data that will be stored in database • Application Programmer - implements application programs (transactions) that access data and support enterprise rules

30. People (cont.) • Database Administrator - maintains database once system is operational: space allocation, performance optimization, database security • System Administrator - maintains transaction processing system: monitors interconnection of HW and SW modules, deals with failures and congestion

31. Database System Studies

32. Design of databases • how to design a database • what should be stored • which structure for the data • what assumptions should be made • how is the connection between data

33. Database programming • how to write queries on the database • how to use other capabilities of a DBMS in an application • how is database programming combined with conventional programming

34. Database System Implementation • how to build a DBMS (query processing, transaction processing, storage manager etc.) This will not be discussed in this course.

35. Application of Database

36. Decision Support System(OLTP vs. OLAP) • On-line Transaction Processing (OLTP) • Day-to-day handling of transactions that result from enterprise operation • Maintains correspondence between database state and enterprise state • On-line Analytic Processing (OLAP) • Analysis of information in a database for the purpose of making management decisions

37. On-Line Analytical Processing • Analyzes historical data (terabytes) using complex queries • Due to volume of data and complexity of queries, OLAP often uses a data warehouse • Data Warehouse - (offline) repository of historical data generated from OLTP or other sources • Data Mining - use of warehouse data to discover relationships that might influence enterprise strategy

38. Exp – Airline reservation system • OLTP • Event: customer A books tickets from ELP to NY; update database to reflect that event • OLAP • During the last holiday season, how many customers fly from ELP to Dallas and NY? • Data Mining • Are there any airports in which more than 50% of travelers from ELP need to change their flight?