Transcript Overview of Database Systems

CSC 411/511:
DBMS Design
Overview of Database Systems
(Chapter 1)
Dr. Nan Wang
CSC411_L1_Overview
1
Contents
•
•
•
•
•
•
•
•
Data vs. Information
What is a DBMS
History of DBMS
File Systems vs. DBMS
Advantages of DBMS
Describing and Storing Data using DBMS
Transaction
Structure of DBMS
Dr. Nan Wang
CSC411_L1_Overview
2
Data
Dr. Nan Wang
CSC411_L1_Overview
3
Data vs. Information
• Data are raw facts
• Information is the result of processing raw data to
reveal meaning
• Information requires context to reveal meaning
• Raw data must be formatted for storage,
processing, and presentation
• Data are the foundation of information, which is
the bedrock of knowledge
Dr. Nan Wang
CSC411_L1_Overview
4
Data vs. Information (cont’d.)
•
•
•
•
Data: building blocks of information
Information produced by processing data
Information used to reveal meaning in data
Accurate, relevant, timely information is the key
to good decision making
• Good decision making is the key to
organizational survival
• Data management: focuses on proper generation,
storage, and retrieval of data
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
5
5
What Is a DBMS?
• Data
– Known facts that can be recorded and have an implicit meaning.
• Database
– A very large, integrated collection of data.
– Describe the activities of one or more related organizations
• Database models real-world enterprise.
–
–
Entities (e.g., students, professors, courses)
Relationships (e.g., Nicole is taking CSC411)
• A Database Management System (DBMS) is a software
package designed to store and manage databases.
– Design a database
– Use a database (store and retrival)
– Understand how a DBMS works
• DBMS Vendors:
• MySQL, IBM’s DB2, Oracle, Informix, Sybase
Dr. Nan Wang
CSC411_L1_Overview
6
What Is a Database?
Dr. Nan Wang
CSC411_L1_Overview
7
What will be covered in this book:
Dr. Nan Wang
CSC411_L1_Overview
8
History of Database
• Early database Technology
– Integrated data store
• The first general-purpose DBMS designed by Charles Bachman
at General Electric in early 1960s
• Bachman was the first recipient of ACM’s (Association for
Computing Machinery)Turing award
– Network data model
• Based on Integrated data model
• Strongly influenced database system through the 1960s
– Hierarchical data model
• Based on Information Management System (IMS, IBM)
• Relational database model
– By Edgar Codd at IBM’s San Jose Research Lab.
• Codd won 1981 Turing Award
– Dominant DBMS paradigm
– Standard SQL query language
Dr. Nan Wang
CSC411_L1_Overview
9
History of Database
• Object-oriented applications
– OODBMSs were introduced in late 1980’s and early 1990’s
to cater to the need of complex data processing in CAD and
other applications.
– Their use has not taken off much.
• Data on the Web and E-commerce Applications
– Web contains data in HTML (Hypertext markup language)
with links among pages.
– This has given rise to a new set of applications and Ecommerce is using new standards like XML (eXtended
Markup Language).
• James Gray won 1999 Turing award for his
contributions to database transaction management
Dr. Nan Wang
CSC411_L1_Overview
10
Extending Database Capabilities
• New functionality is being added to DBMSs
in the following areas:
– Scientific Applications
– Image Storage and Management
– Audio and Video data management
– Data Mining
– Spatial data management
– Time Series and Historical Data Management
The above gives rise to new research and development in
incorporating new data types, complex data structures,
new operations and storage and indexing schemes in
database systems.
Dr. Nan Wang
CSC411_L1_Overview
11
File Systems (vs. DBMS)
• Application must stage large datasets between
main memory and secondary storage
– Use a storage device if the main memory is not large enough
– Even if the memory is large enough, we cannot refer directly
to more than about 4GB of data on a system with 32-bit
addressing
• Need special code for different queries
– Complex for large datasets
• Must protect data from inconsistency due to
multiple concurrent users
• How to support crash recovery
• How to handle security and access control
Dr. Nan Wang
CSC411_L1_Overview
12
Role and Advantages of the DBMS
• DBMS is the intermediary between the user and
the database
– Database structure stored as file collection
– Can only access files through the DBMS
• DBMS enables data to be shared
• DBMS integrates many users’ views of the data
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
13
13
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
14
14
Role and Advantages of the DBMS
(cont’d.)
• Advantages of a DBMS:
–
–
–
–
–
–
–
Improved data sharing
Improved data security
Better data integration
Minimized data inconsistency
Improved data access
Improved decision making
Increased end-user productivity
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
15
15
Why Use a DBMS?
• Data independence
– DBMS provides an abstract view of data and hides details
• Efficient access
– Use sophisticated techniques, e.g., indexing and data structure
•
•
•
•
Data integrity and security
Uniform data administration (group of users, DBA etc.)
Concurrent access, recovery from crashes
Reduced application development time
Dr. Nan Wang
CSC411_L1_Overview
16
Types of Databases
• Databases can be classified according to:
– Number of users
– Database location(s)
– Expected type and extent of use
• Single-user database supports only one user at
a time
– Desktop database: single-user; runs on PC
• Multiuser database supports multiple users at
the same time
– Workgroup and enterprise databases
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
17
17
Types of Databases (cont’d.)
• Centralized database: data located at a single site
• Distributed database: data distributed across
several different sites
• Operational database: supports a company’s dayto-day operations
– Transactional or production database
• Data warehouse: stores data used for tactical or
strategic decisions
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
18
18
Types of Databases (cont'd.)
• Unstructured data exist in their original state
• Structured data result from formatting
– Structure applied based on type of processing to be performed
• Semistructured data have been processed to
some extent
• Extensible Markup Language (XML) represents
data elements in textual format
– XML database supports semistructured XML data
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
19
19
Why Database Design Is Important
• Database design focuses on design of database
structure used for end-user data
– Designer must identify database’s expected use
• Well-designed database:
– Facilitates data management
– Generates accurate and valuable information
• Poorly designed database:
– Causes difficult-to-trace errors
Database Systems, 10th Edition
Dr. Nan Wang
CSC411_L1_Overview
20
20
• How many records does the file contain? How many fields are
there per record?
•
• What problem would you encounter if you wanted to produce a
listing by city? How would you solve this problem by altering the
file structure?
•
• If you wanted to produce a listing of the file contents by last
name, area code, city, state, or zip code, how would you alter the
file structure?
•
• What data redundancies do you detect? How could those
redundancies lead to anomalies
Dr. Nan Wang
CSC411_L1_Overview
21
Solution
• The more you decompose the data into their
component parts, the greater the search flexibility.
Data that are decomposed into their most basic
components are said to be atomic.
Dr. Nan Wang
CSC411_L1_Overview
22
• Identify and discuss the serious data redundancy
problems exhibited by the file structure
• Looking at the EMP_NAME and EMP_PHONE contents
in Figure P1.5, what change(s) would you recommend?
• what new files should you create to help eliminate the
data redundancies found in the file
Dr. Nan Wang
CSC411_L1_Overview
23
Data Models
• A data model is a collection of concepts for describing
–
–
–
–
data
data relationships
data semantics
data constraints
A data model hides many low-level details
• A schema is a description of a particular collection of
data, using a given data model.
• The relational model of data is the most widely used
model today.
–
–
Main concept: relation, basically a table with rows and columns.
Every relation has a schema, which describes the columns, or fields.
Dr. Nan Wang
CSC411_L1_Overview
24
Relational Model
• The central data description construct in this
model is a relation, which can be thought of as a
set of records.
• schema for a relation specifies its name, the
name of each field (or attribute or column), and the
type of each field
Dr. Nan Wang
CSC411_L1_Overview
25
Student relations
Dr. Nan Wang
CSC411_L1_Overview
26
Levels of Abstraction
• Many views,
– Views describe how users see View 1 View 2 View 3
the data.
Conceptual Schema
• Single conceptual
(logical) schema and
physical schema.
–
–
Physical Schema
Conceptual schema defines
logical structure
Physical schema describes the
files and indexes used.
* Schemas are defined using DDL (data definition language);
* data is modified/queried using DML (data manipulation language).
Dr. Nan Wang
CSC411_L1_Overview
27
Example: University Database
• Conceptual schema:
–
–
–
Students(sid: string, name: string, login: string,
age: integer, gpa:real)
Courses(cid: string, cname:string, credits:integer)
Enrolled(sid:string, cid:string, grade:string)
• Physical schema:
–
–
Relations stored as unordered files.
Index on first column of Students.
• External Schema (View):
–
Course_info(cid:string, enrollment:integer)
Dr. Nan Wang
CSC411_L1_Overview
28
Conceptual schema
Dr. Nan Wang
CSC411_L1_Overview
29
Data Independence *
• Applications are insulated from how data is
structured and stored.
– Data independence is achieved through three levels of data
abstraction
– One of the most important benefits of using a DBMS!
• Physical data independence
– Protection from changes in physical structure of data.
• As long as the conceptual schema remains the same, we can
change the storage details without altering applications.
• Performance might be different but the results are the same
• Logical data independence
– Protection from changes in logical structure of data.
• If the underlying data is reorganized, the conceptual schema is
changed, and the definition of a view relation can be changed
so that the same relation is computed as before (the same
result).
Dr. Nan Wang
CSC411_L1_Overview
30
Database Applications
• Database Applications:
–
–
–
–
–
–
Banking: all transactions
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Manufacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax deductions
• Databases touch all aspects of our lives
Dr. Nan Wang
CSC411_L1_Overview
31
Logical three-tier client/server
architecture.
Dr. Nan Wang
CSC411_L1_Overview
32
CSC 411/511:
DBMS Design
Questions?
Dr. Nan Wang
CSC411_L1_Overview
33