Data [Elmasri and Navathe. Fundamentals ... Database management system (
DBMS). ○ Database ... Database systems offer solutions to all the above
problems ...
Chapter 1: Introduction
Database System Concepts, 6th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use
Data, Database, and DBMS n Data l
[Elmasri and Navathe. Fundamentals of Database Systems]
Known facts that can be recorded and that have implicit meaning
e.g.) names, phone numbers, addresses, … n Database l
Collection of interrelated data
n Database management system (DBMS) l
Database + set of programs to access those data
l
An environment that is both convenient and efficient to use
Database System Concepts - 6th Edition
1.2
©Silberschatz, Korth and Sudarshan
Database System Applications n Databases touch all aspects of our lives
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
Database System Concepts - 6th Edition
1.3
©Silberschatz, Korth and Sudarshan
Drawbacks of Using File Systems to Store Data n Data redundancy and inconsistency l
Multiple file formats, duplication of information in different files
n Difficulty in accessing data l
Need to write a new program to carry out each new task
n Data isolation l
Data are scattered in multiple files and formats
n Integrity problems l
Integrity constraints (e.g., account balance > 0) become “buried” in program code rather than being stated explicitly
l
Hard to add new constraints or change existing ones
Database System Concepts - 6th Edition
1.4
©Silberschatz, Korth and Sudarshan
Drawbacks of Using File Systems to Store Data (Cont.) n Atomicity of updates l
Failures may leave database in an inconsistent state with partial updates carried out
l
Example: Transfer of funds from one account to another should either complete or not happen at all
n Concurrent access by multiple users l
Concurrent access needed for performance
l
Uncontrolled concurrent accesses can lead to inconsistencies 4
Example: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time
n Security problems l
Hard to provide user access to some, but not all, data
Database systems offer solutions to all the above problems
Database System Concepts - 6th Edition
1.5
©Silberschatz, Korth and Sudarshan
Levels of Abstraction n View level: application programs hide details of data types. Views can also
hide information (such as an employee’s salary) for security purposes. n Logical level: describes data stored in database, and the relationships among
the data type instructor = record ID : string; name : string; dept_name : string; salary : integer; end; n Physical level:
describes how a record is stored
Database System Concepts - 6th Edition
1.6
©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
Example: The database consists of information about a set of customers and accounts and the relationship between them
n Instance – the actual content of the database at a particular point in time l
Analogous to the value of a variable
Database System Concepts - 6th Edition
1.7
©Silberschatz, Korth and Sudarshan
Physical Data Independence n Schemas according to the level of abstraction l
Logical schema: database design at the logical level
l
Physical schema: database design at the physical level
n 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 - 6th Edition
1.8
©Silberschatz, Korth and Sudarshan
Data Models n A collection of tools for describing l l l l
Data Data relationships Data semantics Data constraints
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 - 6th 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 - 6th Edition
1.10
©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
l
Described by a set of attributes
Relationship: an association among several entities
n Represented diagrammatically by an entity-relationship diagram:
Database System Concepts - 6th Edition
1.11
©Silberschatz, Korth and Sudarshan
Object-Based Data Model n Supports complex types and inheritance
Database System Concepts - 6th Edition
1.12
©Silberschatz, Korth and Sudarshan
XML Data Model n Supports extensible tags and nested structures
Comp. Sci. Comp. Sci. Taylor Taylor 100000 100000 CS-101 CS-101 Intro. to Computer Science Intro. to Computer Science Comp. Sci Comp. Sci 4 4
Database System Concepts - 6th Edition
1.13
©Silberschatz, Korth and Sudarshan
Data Definition Language (DDL) n Language for defining the database schema
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 - 6th Edition
1.14
©Silberschatz, Korth and Sudarshan
Data Manipulation Language (DML) n Language for accessing and manipulating the data organized by the appropriate
data model l
Retrieval, insertion, deletion, modification
n Query language: part of DML that requests data retrieval l
Commonly used as a synonym for DML
n Two classes of languages l
Procedural – user specifies what data is required and how to get those data
l
Declarative (nonprocedural) – user specifies only what data is required, without specifying how to get those data
n SQL is the most widely used query language
Database System Concepts - 6th Edition
1.15
©Silberschatz, Korth and Sudarshan
SQL n SQL: widely used non-procedural language l
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 = “physics”
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
n Chapters 3, 4 and 5
Database System Concepts - 6th Edition
1.16
©Silberschatz, Korth and Sudarshan
Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation
Database System Concepts - 6th Edition
1.17
©Silberschatz, Korth and Sudarshan
Storage Management n DBMS must effectively and efficiently manage storage (disk) space
n Storage manager l
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
l
Efficient storing, retrieving and updating of data
n Issues: l
Storage access
l
File organization
l
Indexing and hashing
Database System Concepts - 6th Edition
1.18
©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 Transaction – 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 - 6th Edition
1.19
©Silberschatz, Korth and Sudarshan
Database Users 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 - 6th Edition
1.20
©Silberschatz, Korth and Sudarshan
Database Administrator n Coordinates all the activities of the database system; the database
administrator has a good understanding of the enterprise’s information resources and needs. n Database administrator's duties include: l
Schema definition
l
Storage structure and access method definition
l
Schema and physical organization modification
l
Granting user authority to access the database
l
Specifying integrity constraints
l
Acting as liaison with users
l
Monitoring performance and responding to changes in requirements
Database System Concepts - 6th Edition
1.21
©Silberschatz, Korth and Sudarshan
Overall Database System Structure
Database System Concepts - 6th Edition
1.22
©Silberschatz, Korth and Sudarshan
Application Architecture
n Two-tier architecture: application programs communicate with DBMS using
API standards (like ODBC/JDBC) n Three-tier architecture: “middleware” (like WAS – Web application server) is
used for accessing data Database System Concepts - 6th Edition
1.23
©Silberschatz, Korth and Sudarshan
History of Database Systems n 1950s and early 1960s: l
Data processing using magnetic tapes for storage 4
l
Tapes provided only sequential access
Punched cards for input
n Late 1960s and 1970s: l
Hard disks allowed direct access to data
l
Network and hierarchical data models in widespread use
l
E. F. “Ted” Codd defines the relational data model
l
4
Would win the ACM Turing Award for this work
4
IBM Research begins System R prototype
4
UC Berkeley begins Ingres prototype
High-performance (for the era) transaction processing
Database System Concepts - 6th Edition
1.24
©Silberschatz, Korth and Sudarshan
History of Database Systems (Cont.) n 1980s: l
l
Research relational prototypes evolve into commercial systems 4 SQL becomes industrial standard Parallel and distributed database systems
Object-oriented database systems n 1990s: l
l l
Large decision support and data-mining applications Large multi-terabyte data warehouses
Emergence of Web commerce n Early 2000s: l
l l
XML and XQuery standards Automated database administration
n Later 2000s: l
Giant data storage systems 4
Google BigTable, Yahoo PNuts, Amazon, ..
Database System Concepts - 6th Edition
1.25
©Silberschatz, Korth and Sudarshan
End of Chapter 1
Database System Concepts - 6th Edition
1.26
©Silberschatz, Korth and Sudarshan