Chapter 1 Introduction Database System Concepts 6 th

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

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: 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 can be very large. n Databases touch all aspects of our lives Database System Concepts - 6 th Edition 1. 2 ©Silberschatz, Korth and Sudarshan

University Database Example n Application program examples l Add new students, instructors, and courses l Register students for courses, and generate class rosters l Assign grades to students, compute grade point averages (GPA) and generate transcripts n In the early days, database applications were built directly on top of file systems Database System Concepts - 6 th Edition 1. 3 ©Silberschatz, Korth and Sudarshan

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 Database System Concepts - 6 th Edition to add new constraints or change existing ones 1. 4 ©Silberschatz, Korth and Sudarshan

Drawbacks of using file systems to store data (Cont. ) l Atomicity of updates 4 4 l Failures may leave database in an inconsistent state with partial updates carried out Example: Transfer of funds from one account to another should either complete or not happen at all Concurrent access by multiple users 4 Concurrent access needed for performance 4 Uncontrolled concurrent accesses can lead to inconsistencies – Example: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time l Security problems 4 Hard to provide user access to some, but not all, data Database systems offer solutions to all the above problems Database System Concepts - 6 th Edition 1. 5 ©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 - 6 th Edition 1. 9 ©Silberschatz, Korth and Sudarshan

Relational Model n Relational model (Chapter 2) n Example of tabular data in the relational model Columns Rows Database System Concepts - 6 th Edition 1. 10 ©Silberschatz, Korth and Sudarshan

A Sample Relational Database System Concepts - 6 th Edition 1. 11 ©Silberschatz, Korth and Sudarshan

Data Definition Language (DDL) n Specification notation for defining the database schema Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8, 2)) n DDL compiler generates a set of table templates stored in a data dictionary n Data dictionary contains metadata (i. e. , data about data) l Database schema l Integrity constraints 4 Primary key (ID uniquely identifies instructors) 4 Referential integrity (references constraint in SQL) – e. g. dept_name value in any instructor tuple must appear in department relation l Authorization Database System Concepts - 6 th Edition 1. 13 ©Silberschatz, Korth and Sudarshan

SQL n SQL: widely used non-procedural language Example: Find the name of the instructor with ID 22222 select name from instructor where instructor. ID = ‘ 22222’ l Example: Find the ID and building of instructors in the Physics dept. select instructor. ID, department. building from instructor, department where instructor. dept_name = department. dept_name and department. dept_name = ‘Physics’ l n Application programs generally access databases through one of Language extensions to allow embedded SQL l Application program interface (e. g. , ODBC/JDBC) which allow SQL queries to be sent to a database n Chapters 3, 4 and 5 l Database System Concepts - 6 th Edition 1. 14 ©Silberschatz, Korth and Sudarshan

Database Design? n Is there any problem with this design? Database System Concepts - 6 th Edition 1. 16 ©Silberschatz, Korth and Sudarshan

Design Approaches n Normalization Theory (Chapter 8) l Formalize what designs are bad, and test for them n Entity Relationship Model (Chapter 7) l Models an enterprise as a collection of entities and relationships 4 Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects – Described by a set of attributes 4 Relationship: l an association among several entities Represented diagrammatically by an entity-relationship diagram: Database System Concepts - 6 th Edition 1. 17 ©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: What happened to dept_name of instructor and student? Database System Concepts - 6 th Edition 1. 18 ©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 - 6 th Edition 1. 21 ©Silberschatz, Korth and Sudarshan

Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation Database System Concepts - 6 th Edition 1. 22 ©Silberschatz, Korth and Sudarshan

Transaction Management n What if the system fails? n What if more than one user is concurrently updating the same data? 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 - 6 th Edition 1. 24 ©Silberschatz, Korth and Sudarshan

Database System Internals Database System Concepts - 6 th Edition 1. 26 ©Silberschatz, Korth

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 (multi-processor) n Distributed Database System Concepts - 6 th Edition 1. 27 ©Silberschatz, Korth and Sudarshan

History of Database Systems n 1950 s and early 1960 s: l Data processing using magnetic tapes for storage 4 Tapes l provided only sequential access Punched cards for input n Late 1960 s and 1970 s: l Hard disks allowed 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 - 6 th Edition 1. 28 ©Silberschatz, Korth and Sudarshan

History (cont. ) n 1980 s: Research relational prototypes evolve into commercial systems 4 SQL becomes industrial 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 Early 2000 s: XML and XQuery standards l Automated database administration n Later 2000 s: l Giant data storage systems 4 Google Big. Table, Yahoo PNuts, Amazon, . . l Database System Concepts - 6 th Edition 1. 29 ©Silberschatz, Korth and Sudarshan