csc 411/511: dbms design 1 1 dr. nan wangcsc411_l1_overview 1 overview of database systems (chapter...

CSC 411/511: DBMS Design

1

Dr. Nan Wang CSC411_L1_Overview1

Overview of Database Systems

(Chapter 1)

CSC411_L1_OverviewDr. Nan Wang22

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


Data

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

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

5

Database Systems, 10th Edition


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


What Is a Database?


What will be covered in this book:

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


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 E-commerce is using new standards like XML (eXtended Markup Language).

• James Gray won 1999 Turing award for his contributions to database transaction management


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.


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


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

13



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

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


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

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 day-

to-day operations– Transactional or production database

• Data warehouse: stores data used for tactical or strategic decisions

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

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

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


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.

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

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.


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

25


Student relations

26


Levels of Abstraction

• Many views, – Views describe how users see

the data. • Single conceptual

(logical) schema and 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).

Physical Schema

Conceptual Schema

View 1 View 2 View 3


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)


Conceptual schema

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).


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


Logical three-tier client/server architecture.

CSC 411/511: DBMS Design

33

Dr. Nan Wang CSC411_L1_Overview33

Questions?

csc 411/511: dbms design 1 1 dr. nan wangcsc411_l1_overview 1 overview of database systems (chapter...

Documents