review of class 1 · class 2: database system concepts and architecture review of class 1 2...

22
8/26/2010 1 1 ITCS 3160 DATA BASE DESIGN AND IMPLEMENTATION JING YANG 2010 FALL Class 2: Database System Concepts and Architecture Review of Class 1 2 Database M Database management systems (DBMS) Concept: Meta-data Characteristics of database approach Concepts: Program-data and program-operation independence, data abstraction, view Fundamentals of Database Systems Elmasri and Navathe Actors on the scene Concepts: DBA, naïve or parametric user Workers behind the scene

Upload: others

Post on 21-Aug-2020

3 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

1

1

ITCS 3160DATA BASE DESIGN AND IMPLEMENTATIONJING YANG2010 FALL

Class 2: Database System Concepts and Architecture

Review of Class 12

DatabaseMDatabase management systems (DBMS)

Concept: Meta-data

Characteristics of database approach Concepts: Program-data and program-operation independence, data abstraction, view

Fundamentals of Database Systems Elmasri and Navathe

Actors on the sceneConcepts: DBA, naïve or parametric user

Workers behind the scene

Page 2: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

2

Review of Class 1 (cont'd.)3

Advantages of using DBMS approachC D li i d d li i Concept: Data normalization and denormalization, integrity constraints,

A brief history of database applicationsWhen not to use database

Fundamentals of Database Systems Elmasri and Navathe

4

1.9 - What is the difference between controlled and ncontrolled red ndanc ?and uncontrolled redundancy?

Fundamentals of Database Systems Elmasri and Navathe

Page 3: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

3

Outline

Data Models, Schemas, and InstancesA

5

Three-Schema Architecture and Data IndependenceDatabase Languages and InterfacesThe Database System EnvironmentCentralized and Client/Server Architecturesfor DBMSsClassification of Database Management Systems

Fundamentals of Database Systems Elmasri and Navathe

Data Abstraction

Data abstraction S i f d il f d i i d

6

Suppression of details of data organization and storageHighlighting of the essential features for an improved understanding of data

Fundamentals of Database Systems Elmasri and Navathe

Page 4: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

4

What is a Data Model

Data modelC ll i f h d ib h f

7

Collection of concepts that describe the structure of a databaseProvides means to achieve data abstractionBasic operations • Specify retrievals and updates on the database

Dynamic aspect or behavior of a database Dynamic aspect or behavior of a database application• Allows the database designer to specify a set of valid

operations allowed on database objects

Fundamentals of Database Systems Elmasri and Navathe

Categories of Data Models

High-level or conceptual data models Cl h i d

8

Close to the way many users perceive data

Low-level or physical data models Describe the details of how data is stored on computer storage media

Representational or implementation data modelsEasily understood by end users Also similar to how data organized in computer storage

Fundamentals of Database Systems Elmasri and Navathe

Page 5: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

5

Conceptual Data Models

Entity R l ld bj

9

Represents a real-world object or concept

AttributeRepresents some property of interest Further describes an entity

Relationship among two or more entities Represents an association among the entities

Entity-Relationship model

Fundamentals of Database Systems Elmasri and Navathe

Representational Data Models

Relational data modelU d f l i di i l i l DBMS

10

Used most frequently in traditional commercial DBMSs

Legacy network and hierarchical models

Record-based data models refer to relational, network, and hierarchical data modelsObject data model

New family of higher-level implementation data models Closer to conceptual data models

Fundamentals of Database Systems Elmasri and Navathe

Page 6: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

6

Physical Data Models

Describe how data is stored as files in the computerRepresent information s ch as record formats

11

Represent information such as record formats, record ordering, and access path Access path • Structure that makes the search for particular database

records efficientIndex Index • Example of an access path • Allows direct access to data using an index term or a

keyword

Fundamentals of Database Systems Elmasri and Navathe

Database Schema

Database schemaD i i f d b

12

Description of a database

Schema diagramDisplays selected aspects of schema

Schema constructEach object in the schema

Fundamentals of Database Systems Elmasri and Navathe

Page 7: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

7

13

Schema Diagram of the University Database

Fundamentals of Database Systems Elmasri and Navathe

Database State

Database state or snapshotData in database at a particular moment in time

14

Also called the current set of occurrences or instances in the databaseEach schema construct has its own current set of instances

Define a new databaseSpecify database schema to the DBMS onlyDatabase is in empty state with no data

Initial state Initial state Populated or loaded with the initial data

Valid stateSatisfies the structure and constraints specified in the schema

Fundamentals of Database Systems Elmasri and Navathe

Page 8: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

8

Schema evolution

Changes applied to schema as application req irements change

15

requirements changeMost modern DBMSs include operations for schema evolution

Fundamentals of Database Systems Elmasri and Navathe

Three-Schema Architecture

Internal level D ib h i l f h d b

16

Describes physical storage structure of the database

Conceptual levelDescribes structure of the whole database for a community of users

External or view level Describes part of the database that a particular user group is interested in

Fundamentals of Database Systems Elmasri and Navathe

Page 9: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

9

Three-Schema Architecture (cont'd.)17

Fundamentals of Database Systems Elmasri and Navathe

Data Independence

Capacity to change the schema at one level of a database system

18

yWithout having to change the schema at the next higher levelOnly the mapping between the two levels is changed

Types:Logical Data Independence

Change conceptual schema without changing external schemas or application programsapplication programs

Physical Data IndependenceChange internal schema without having to change conceptual schema

Fundamentals of Database Systems Elmasri and Navathe

Page 10: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

10

DBMS Languages

Data definition language (DDL)• Defines both conceptual and internal schemas or conceptual

19

p pschema only

Storage definition language (SDL)• Specifies the internal schemaView definition language (VDL)• Specifies user views/mappings to conceptual schemaData manipulation language (DML)p g g ( )• Allows retrieval, insertion, deletion, modification

• Comprehensive integrated language such as SQL• SQL is a combinations of DDL, VDL, and DML

Fundamentals of Database Systems Elmasri and Navathe

DBMS Languages (cont'd.)

Data manipulation language (DML)Hi h l l d l DML

20

High-level or nonprocedural DML • Can be used on its own to specify complex database

operations concisely• Set-at-a-time or set-oriented

Low-level or procedural DML • Must be embedded in a general-purpose programming g p p p g g

language• Record-at-a-time

Fundamentals of Database Systems Elmasri and Navathe

Page 11: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

11

DBMS Interfaces

Menu-based interfaces for Web clients or browsingf

21

Forms-based interfacesGraphical user interfacesNatural language interfacesSpeech input and outputInterfaces for parametric usersInterfaces for parametric usersInterfaces for the DBA

Fundamentals of Database Systems Elmasri and Navathe

The Database System Environment

DBMS component modulesBuffer management

22

gSchedule disk read/write

Stored data manager Control access to DBMS information stored on disk

DDL compiler Process schema definitions, stored schemas into catalog

Interactive query interface• Query compiler • Query compiler

• Parse and validate queries and compleile them into an internal form • Query optimizer

• Rearrange and reorder operations, eliminate redundancies, chose algorithms and index

Fundamentals of Database Systems Elmasri and Navathe

Page 12: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

12

The Database System Environment (cont'd.)

DBMS component modulesPrecompiler

23

PrecompilerExtract DML commands from an application program Send them to DML complier

Runtime database processorExecute privileged commends, the executable query plans, and canned transaction with runtime parametersManage buffersWork with System catalog and may update it with statistics

Concurrency control systemBackup and recovery system

Fundamentals of Database Systems Elmasri and Navathe

24Fundamentals of Database Systems Elmasri and Navathe

Page 13: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

13

Database System Utilities

LoadingL d i i d fil

25

Load existing data filesTransfer data from one DBMS to another

BackupCreates a backup copy of the databaseIncremental backups

Fundamentals of Database Systems Elmasri and Navathe

Database System Utilities (cont'd.)

Database storage reorganizationR i f d b fil i diff fil

26

Reorganize a set of database files into different file organizations

Performance monitoringMonitors database usage and provides statistics to the DBA

Fundamentals of Database Systems Elmasri and Navathe

Page 14: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

14

Tools, Application Environments, and Communications Facilities

CASE ToolsU d i h d i h

27

Used in the design phase

Data dictionary (data repository) systemStores design decisions, usage standards, application program descriptions, and user information

Application development environments

Communications software

Fundamentals of Database Systems Elmasri and Navathe

Centralized DBMS Architecture

All DBMS functionality, application program e ec tion and ser interface processing carried o t

28

execution, and user interface processing carried out on one machine

Fundamentals of Database Systems Elmasri and Navathe

Page 15: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

15

29

Fundamentals of Database Systems Elmasri and Navathe

Basic Client/Server Architectures

Servers with specific functionalitiesFile server

30

File server • Maintains the files of the client machines.Printer server • Connected to various printers; all print requests by the

clients are forwarded to this machineWeb servers

S d t b t t ( t ) h t i d Send out web content (pages, etc.) when a request is made by a web browser clientA website itself is hosted on the web server

e-mail servers

Fundamentals of Database Systems Elmasri and Navathe

Page 16: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

16

Basic Client/Server Architectures (cont'd.)

Client machines P id i h

31

Provide user with:• Appropriate interfaces to utilize these servers• Local processing power to run local applications

Fundamentals of Database Systems Elmasri and Navathe

32

Fundamentals of Database Systems Elmasri and Navathe

Page 17: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

17

Basic Client/Server Architectures (cont'd.)

Client U hi h id i f bili i

33

User machine that provides user interface capabilities and local processing

ServerSystem containing both hardware and software Provides services to the client machines• Such as file access, printing, archiving, or database access

Fundamentals of Database Systems Elmasri and Navathe

Two-Tier Client/Server Architectures for DBMSs

Server handlesQ d i f i li l d SQL

34

Query and transaction functionality related to SQL processing

Client handlesUser interface programs and application programs

Fundamentals of Database Systems Elmasri and Navathe

Page 18: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

18

Two-Tier Client/Server Architectures (cont'd.)

Open Database Connectivity (ODBC) P id li i i i f (API)

35

Provides application programming interface (API)Allows client-side programs to call the DBMS• Both client and server machines must have the necessary

software installed

JDBCAll J li t t Allows Java client programs to access one or more DBMSs through a standard interface

Fundamentals of Database Systems Elmasri and Navathe

Three-Tier and n-Tier Architectures for Web Applications

A li ti W b

36

Application server or Web serverAdds intermediate layer between client and the database serverRuns application programs and stores business rules

N-tierDivide the layers between the user and the stored data further into finer components

Fundamentals of Database Systems Elmasri and Navathe

Page 19: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

19

37

Fundamentals of Database Systems Elmasri and Navathe

Classification of DatabaseManagement Systems

Data modelR l i l

38

• Relational• Object• Hierarchical and network (legacy)• Native XML DBMS

Number of users• Single-user• Multiuser

Fundamentals of Database Systems Elmasri and Navathe

Page 20: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

20

Classification of DatabaseManagement Systems (cont'd.)

Number of sites• Centralized

39

• Centralized• Distributed

• Homogeneous• Use the same DBMS software at all the sites

• Heterogeneous• Use different DBMS software at each sites

Cost• Open source• Different types of licensing

Fundamentals of Database Systems Elmasri and Navathe

Classification of DatabaseManagement Systems (cont'd.)

Types of access path options

G l i l

40

General or special-purpose

Fundamentals of Database Systems Elmasri and Navathe

Page 21: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

21

Network Model41

Fundamentals of Database Systems Elmasri and Navathe

Summary

Concepts used in database systemsM f

42

Main categories of data modelsTypes of languages supported by DMBSsInterfaces provided by the DBMSDBMS classification criteria:

Data model, number of users, number of sties, access Data model, number of users, number of sties, access paths, cost

Fundamentals of Database Systems Elmasri and Navathe

Page 22: Review of Class 1 · Class 2: Database System Concepts and Architecture Review of Class 1 2 Database Database management systems (DB MS) Concept: Meta-data Characteristics of database

8/26/2010

22

After Class Exercise43

2.12 - Think of different users for the database of Figure 1 2 What type of applications would each user Figure 1.2. What type of applications would each user need? To which user category would each belong and what type of interface would they need?

2.14 - if you were designing a Web-based system to make airline reservations and to sell airline tickets

Fundamentals of Database Systems Elmasri and Navathe

make airline reservations and to sell airline tickets, which DBMS Architecture would you choose from Section 2.5? Why? Why would the other architectures not be a good choice?