Database System Concepts and Architecture s

1. Database System Concepts and Architectures Indra Budi indra@cs.ui.ac.id

2. Which of the following is a problem of lists that is solved by using a database? • data inconsistencies • problems adding data • problems deleting data • missing data • All of the above

3. In database processing systems, the data is directly accessed only by the…. • In database application processing systems, the user interacts directly with the…

4. Evolution of computing • Database has evolved since nearly every stage of computing, from: • mainframes, which are monolithic • Client-server • Web-based

5. Data Models • A collection of concepts used to describe the structure of a database • Data types • Relationships • Constraints • Etc

6. Categories of Data Models • Conceptual data models: high level • Physical data models: how data is actually stored in a disk • Representational data models: somewhere between those two… let’s see…

7. Representational data model • Relational data model (our main discussion in this class) • Network data model (uses directed graphs) • Hierarchical data model (history…) • Object data model (this is new!)

8. Database schemas • Schemas: description of the database, but not the database itself! • Example of a schema diagram:

9. Not included in the schema diagram above: • Data types? • Relationships? • Constraints, such as “students in CS major must take CS1310…”

10. Database states • Also called ‘snapshot’ • After we define, generally a database is at the ‘empty state’ • We get the ‘initial state’ after we first load the database • Valid state is a state which satisfies the structure and constraints in the schema

11. Three Schema Model • ANSI/SPARC introduced the three schema model in 1975 • It provides a framework describing the role and purpose of data modeling

13. Three Schema Model (cont.) • External schema or user view • Representation of how users view the database • Conceptual schema • A logical view of the database containing a description of all the data and relationships • Independent of any particular means of storing the data • One conceptual schema usually contains many different external schemas • Internal schema • A representation of a conceptual schema as physically stored on a particular product • A conceptual schema can be represented by many different internal schemas

14. Data Independence • Application insulated from how data is structured or stored • Logical data independence: we can change the conceptual schema without changing the application program (internal schema) • Physical data independence: we can change the internal schema without changing the conceptual schema. It might be because file database must be reorganized for fine tuning.

15. Database Languages • Data definition language • View definition language • Data manipulation language • High level: SQL, set-at-a-time processing • Low level: VB, COBOL, record-at-a-time processing

16. DBMS Interfaces • Menu based interface • Forms based interface • Graphical user interface  utilize menu & forms • Natural language interface, you can ask, “Show me all the students with GPA > 3” • Parametric interface, such as application for Bank Teller • Interface to DBA: create account, granting account, changing schema, delete db, etc.

17. DBMS utilities • Concurrency Control • Loading utility, or importer • Backup utility, usually to tape • File optimizer or reorganizer • Performance monitoring: fragmentation, load balancing • Commit & Rollback • etc

18. Transaction: An Execution of a DB Program • Key concept is transaction , which is an atomic (all-or-nothing property), sequence of database actions (reads/writes). • Each transaction, executed completely, mustleave the DB in a consistent state, if DB isconsistent when the transaction begins. • Users can specify some simple integrity constraints on the data, and the DBMS will enforce these constraints. • Beyond this, the DBMS does not really understand thesemantics of the data. (e.g., it does not understandhow the interest on a bank account is computed). • Thus, ensuring that a transaction (run alone) preservesconsistency is ultimately the user’s responsibility!

19. Classification of DBMS • Data model: relational, network, hierarchical, etc. • Number of users: Single user vs multi user • Centralized vs distributed • Price…! • OLTP support? • OLAP support (read page 842 of Elmasri)

20. Which of the following plays an important roles representing information about a real world in the database ? • The data definition language • The data manipulation language • The buffer manager • The data model

21. What is the differences ? • Database Schema Vs Database State ?

22. Group Assignment Comparative Study of Popular DBMS

23. Group 1 (Mahesa, Evan Bambang, Panca) MySQL MS-SQL Server FoxPro Group 2 (Lamo, Siti, Arief) Informix MS-Access IBM DB2 Group 3 (Tyas, Dika, Ririn) Oracle database Postgres Borland Interbase Which ones?

24. What to look for ? • Use theories in Elmasri Ch 1 & 2, and Kroenke Ch 1! • Examples: function, concurrency control, type, price, primary use ?, performance, capacity, etc.

25. Deliverables • Paper, submitted to me, you can give me directly or email me at indra@cs.ui.ac.id, due date on Tuesday, September 14th 2004 • Presentation (of course)  Wednesday, September 15th 2004