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 Airlines: reservations, schedules l Universities: registration, grades l Sales: customers, products, purchases, order tracking l Employee records, salaries, l …. . 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 l Clicker data: record attendance and quiz answers Quiz Q 1: Click 1 now! 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

Purpose of Database Systems n Drawbacks of using file systems to store data: l Data redundancy and inconsistency 4 Multiple file formats, duplication of information in different files l 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. “dept_name of student must be a valid department name”) 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

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 (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 n Database systems offer solutions to all the above problems l Database System Concepts - 6 th Edition 1. 5 ©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 Analogous to type information of a variable in a program l Physical schema: database design at the physical level 4 E. g. l data storage structures, indices for fast access Logical schema: database design at the logical level n Instance – the actual content of the database at a particular point in time l Analogous to the value of a variable n Physical Data Independence – the ability to modify the physical schema without changing the logical schema l Applications depend on the logical schema Database System Concepts - 6 th Edition 1. 6 ©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. 7 ©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. 8 ©Silberschatz, Korth and Sudarshan

A Sample Relational Database instructor department Database System Concepts - 6 th Edition 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 - 6 th Edition 1. 10 ©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 tables n Data dictionary contains metadata (i. e. , data about data) l Database schema 4 Which tables are present, what are their attributes, … l Integrity constraints 4 Which attributes are primary keys, foreign keys, … Database System Concepts - 6 th Edition 1. 11 ©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 - 6 th Edition 1. 12 ©Silberschatz, Korth and Sudarshan

Database Design? n Is there any problem with this design? Quiz Q 2: The problem is: (1) missing information (2) repeated information (3) there is no problem (4) these instructor salaries are too low Database System Concepts - 6 th Edition 1. 13 ©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. 14 ©Silberschatz, Korth and Sudarshan

Database System Internals Query Processor Storage Manager Storage Database System Concepts - 6 th Edition 1. 15 ©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 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 - 6 th Edition 1. 16 ©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 parallel data warehouses l Emergence of Web commerce l n Early 2000 s: XML and XQuery standards l Automated database administration n Later 2000 s: Big Data l massively parallel storage systems 4 Google Big. Table, Yahoo PNuts, Amazon, . . l Parallel data analysis, using Map. Reduce l Database System Concepts - 6 th Edition 1. 17 ©Silberschatz, Korth and Sudarshan

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

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