Chapter 1 Introduction Database System Concepts 5 th

Chapter 1: Introduction Database System Concepts, 5 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Chapter 1: Introduction n Purpose of Database Systems n Database Languages n Relational Databases n Database Design n Data Models n Database Internals n Database Users and Administrators n Overall Structure n History of Database Systems Database System Concepts - 5 th Edition, May 23, 2005 1. 2 ©Silberschatz, Korth and Sudarshan

Database Management System (DBMS) n DBMS contains information about a particular enterprise l Collection of interrelated data l Set of programs to access the data l An environment that is both convenient and efficient to use n Database Applications: l Banking: all transactions l Airlines: reservations, schedules l Universities: registration, grades l Sales: customers, products, purchases l Online retailers: order tracking, customized recommendations l Manufacturing: production, inventory, orders, supply chain l Human resources: employee records, salaries, tax deductions n Databases touch all aspects of our lives Database System Concepts - 5 th Edition, May 23, 2005 1. 3 ©Silberschatz, Korth and Sudarshan

Purpose of Database Systems n In the early days, database applications were built directly on top of file systems n Drawbacks of using file systems to store data: l Data redundancy and inconsistency 4 Multiple l file formats, duplication of information in different files Difficulty in accessing data 4 Need to write a new program to carry out each new task l Data isolation — multiple files and formats l Integrity problems 4 Integrity constraints (e. g. account balance > 0) become “buried” in program code rather than being stated explicitly 4 Hard to add new constraints or change existing ones Database System Concepts - 5 th Edition, May 23, 2005 1. 4 ©Silberschatz, Korth and Sudarshan

Purpose of Database Systems (Cont. ) n Drawbacks of using file systems (cont. ) Atomicity of updates 4 Failures may leave database in an inconsistent state with partial updates carried out 4 Example: Transfer of funds from one account to another should either complete or not happen at all l Concurrent access by multiple users 4 Concurrent accessed needed for performance 4 Uncontrolled concurrent accesses can lead to inconsistencies – Example: Two people reading a balance and updating it at the same time l Security problems 4 Hard to provide user access to some, but not all, data n Database systems offer solutions to all the above problems l Database System Concepts - 5 th Edition, May 23, 2005 1. 5 ©Silberschatz, Korth and Sudarshan

Levels of Abstraction n Physical level: describes how a record (e. g. , customer) is stored. n Logical level: describes data stored in database, and the relationships among the data. type customer = record customer_id : string; customer_name : string; customer_street : string; customer_city : string; end; n View level: application programs hide details of data types. Views can also hide information (such as an employee’s salary) for security purposes. Database System Concepts - 5 th Edition, May 23, 2005 1. 6 ©Silberschatz, Korth and Sudarshan

View of Data An architecture for a database system Database System Concepts - 5 th Edition, May 23, 2005 1. 7 ©Silberschatz, Korth and Sudarshan

Instances and Schemas n Similar to types and variables in programming languages n Schema – the logical structure of the database l l Analogous to type information of a variable in a program l Physical schema: database design at the physical level l n Example: The database consists of information about a set of customers and accounts and the relationship between them) Logical schema: database design at the logical level Instance – the actual content of the database at a particular point in time l n Analogous to the value of a variable Physical Data Independence – the ability to modify the physical schema without changing the logical schema l Applications depend on the logical schema l In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others. Database System Concepts - 5 th Edition, May 23, 2005 1. 8 ©Silberschatz, Korth and Sudarshan

Data Models n A collection of tools for describing Data l Data relationships l Data semantics l Data constraints l n Relational model n Entity-Relationship data model (mainly for database design) n Object-based data models (Object-oriented and Object-relational) n Semistructured data model (XML) n Other older models: l l Network model Hierarchical model Database System Concepts - 5 th Edition, May 23, 2005 1. 9 ©Silberschatz, Korth and Sudarshan

Data Manipulation Language (DML) n Language for accessing and manipulating the data organized by the appropriate data model l DML also known as query language n Two classes of languages l Procedural – user specifies what data is required and how to get those data l Declarative (nonprocedural) – user specifies what data is required without specifying how to get those data n SQL is the most widely used query language Database System Concepts - 5 th Edition, May 23, 2005 1. 10 ©Silberschatz, Korth and Sudarshan

Data Definition Language (DDL) n Specification notation for defining the database schema Example: create table account ( account_number char(10), branch_name char(10), balance integer) n DDL compiler generates a set of tables stored in a data dictionary n Data dictionary contains metadata (i. e. , data about data) l Database schema l Data storage and definition language 4 Specifies the storage structure and access methods used Integrity constraints 4 Domain constraints 4 Referential integrity (e. g. branch_name must correspond to a valid branch in the branch table) l Authorization l Database System Concepts - 5 th Edition, May 23, 2005 1. 11 ©Silberschatz, Korth and Sudarshan

Relational Model Attributes n Example of tabular data in the relational model Database System Concepts - 5 th Edition, May 23, 2005 1. 12 ©Silberschatz, Korth and Sudarshan

A Sample Relational Database System Concepts - 5 th Edition, May 23, 2005 1. 13 ©Silberschatz, Korth and Sudarshan

SQL n SQL: widely used non-procedural language l Example: Find the name of the customer with customer-id 192 -83 -7465 select customer_name from customer where customer_id = ‘ 192 -83 -7465’ l Example: Find the balances of all accounts held by the customer with customer-id 192 -83 -7465 select account. balance from depositor, account where depositor. customer_id = ‘ 192 -83 -7465’ and depositor. account_number = account_number n Application programs generally access databases through one of l Language extensions to allow embedded SQL l Application program interface (e. g. , ODBC/JDBC) which allow SQL queries to be sent to a database Database System Concepts - 5 th Edition, May 23, 2005 1. 14 ©Silberschatz, Korth and Sudarshan

Database Design The process of designing the general structure of the database: n Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. l Business decision – What attributes should we record in the database? l Computer Science decision – What relation schemas should we have and how should the attributes be distributed among the various relation schemas? n Physical Design – Deciding on the physical layout of the database Database System Concepts - 5 th Edition, May 23, 2005 1. 15 ©Silberschatz, Korth and Sudarshan

The Entity-Relationship Model n Models an enterprise as a collection of entities and relationships l Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects 4 Described l by a set of attributes Relationship: an association among several entities n Represented diagrammatically by an entity-relationship diagram: Database System Concepts - 5 th Edition, May 23, 2005 1. 16 ©Silberschatz, Korth and Sudarshan

Other Data Models n Object-oriented data model n Object-relational data model Database System Concepts - 5 th Edition, May 23, 2005 1. 17 ©Silberschatz, Korth and Sudarshan

Database Application Architectures (web browser) Modern Old Database System Concepts - 5 th Edition, May 23, 2005 1. 18 ©Silberschatz, Korth and Sudarshan

Database Management System Internals n Storage management n Query processing n Transaction processing Database System Concepts - 5 th Edition, May 23, 2005 1. 19 ©Silberschatz, Korth and Sudarshan

Storage Management n Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. n The storage manager is responsible to the following tasks: l Interaction with the file manager l Efficient storing, retrieving and updating of data n Issues: l Storage access l File organization l Indexing and hashing Database System Concepts - 5 th Edition, May 23, 2005 1. 20 ©Silberschatz, Korth and Sudarshan

Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation Database System Concepts - 5 th Edition, May 23, 2005 1. 21 ©Silberschatz, Korth and Sudarshan

Query Processing (Cont. ) n Alternative ways of evaluating a given query l Equivalent expressions l Different algorithms for each operation n Cost difference between a good and a bad way of evaluating a query can be enormous n Need to estimate the cost of operations l Depends critically on statistical information about relations which the database must maintain l Need to estimate statistics for intermediate results to compute cost of complex expressions Database System Concepts - 5 th Edition, May 23, 2005 1. 22 ©Silberschatz, Korth and Sudarshan

Transaction Management n A transaction is a collection of operations that performs a single logical function in a database application n Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e. g. , power failures and operating system crashes) and transaction failures. n Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. Database System Concepts - 5 th Edition, May 23, 2005 1. 23 ©Silberschatz, Korth and Sudarshan

Overall System Structure Database System Concepts - 5 th Edition, May 23, 2005 1.

History of Database Systems n 1950 s and early 1960 s: l Data processing using magnetic tapes for storage 4 Tapes l provide only sequential access Punched cards for input n Late 1960 s and 1970 s: l Hard disks allow direct access to data l Network and hierarchical data models in widespread use l Ted Codd defines the relational data model 4 Would 4 IBM 4 UC l win the ACM Turing Award for this work Research begins System R prototype Berkeley begins Ingres prototype High-performance (for the era) transaction processing Database System Concepts - 5 th Edition, May 23, 2005 1. 25 ©Silberschatz, Korth and Sudarshan

History (cont. ) n 1980 s: Research relational prototypes evolve into commercial systems 4 SQL becomes industry standard l Parallel and distributed database systems l Object-oriented database systems n 1990 s: l Large decision support and data-mining applications l Large multi-terabyte data warehouses l Emergence of Web commerce l n 2000 s: XML and XQuery standards l Automated database administration l Increasing use of highly parallel database systems l Web-scale distributed data storage systems l Database System Concepts - 5 th Edition, May 23, 2005 1. 26 ©Silberschatz, Korth and Sudarshan

End of Chapter 1 Database System Concepts, 5 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Database Users are differentiated by the way they expect to interact with the system n Application programmers – interact with system through DML calls n Sophisticated users – form requests in a database query language n Specialized users – write specialized database applications that do not fit into the traditional data processing framework n Naïve users – invoke one of the permanent application programs that have been written previously l Examples, people accessing database over the web, bank tellers, clerical staff Database System Concepts - 5 th Edition, May 23, 2005 1. 28 ©Silberschatz, Korth and Sudarshan

Database Administrator n Coordinates all the activities of the database system l has a good understanding of the enterprise’s information resources and needs. n Database administrator's duties include: l Storage structure and access method definition l Schema and physical organization modification l Granting users authority to access the database l Backing up data l Monitoring performance and responding to changes 4 Database tuning Database System Concepts - 5 th Edition, May 23, 2005 1. 29 ©Silberschatz, Korth and Sudarshan

Database Architecture The architecture of a database systems is greatly influenced by the underlying computer system on which the database is running: n Centralized n Client-server n Parallel (multiple processors and disks) n Distributed Database System Concepts - 5 th Edition, May 23, 2005 1. 30 ©Silberschatz, Korth and Sudarshan

Object-Relational Data Models n Extend the relational data model by including object orientation and constructs to deal with added data types. n Allow attributes of tuples to have complex types, including non-atomic values such as nested relations. n Preserve relational foundations, in particular the declarative access to data, while extending modeling power. n Provide upward compatibility with existing relational languages. Database System Concepts - 5 th Edition, May 23, 2005 1. 31 ©Silberschatz, Korth and Sudarshan

XML: Extensible Markup Language n Defined by the WWW Consortium (W 3 C) n Originally intended as a document markup language not a database language n The ability to specify new tags, and to create nested tag structures made XML a great way to exchange data, not just documents n XML has become the basis for all new generation data interchange formats. n A wide variety of tools is available for parsing, browsing and querying XML documents/data Database System Concepts - 5 th Edition, May 23, 2005 1. 32 ©Silberschatz, Korth and Sudarshan