lecture01_257.ppt

8/14/2019 Lecture01_257.ppt

1/51

2004.01.20 - SLIDE 1

IS 257Spring 2004

Database Management:

IntroductionRay R. Larson

University of California, BerkeleySchool of Information Management

and Systems

SIMS 257: Database Management

8/14/2019 Lecture01_257.ppt

2/51

2004.01.20 - SLIDE 2

IS 257Spring 2004

Overview

Announcements TAMayjane Co

Course Description

Database Concepts and Terminology Database Models

8/14/2019 Lecture01_257.ppt

3/51

2004.01.20 - SLIDE 3

IS 257Spring 2004

Course Overview

Description of the Course Assignments

Readings

Grading

Schedule

Web site:

http://sims.berkeley.edu/courses/is257/s04

8/14/2019 Lecture01_257.ppt

4/51

2004.01.20 - SLIDE 4

IS 257Spring 2004

Course Description

This course is concerned with the designof the database itself -- not with the designof database system software.

We will discuss DBMS internals only as they

relate to the database and its design andstructure

We will spend a fair amount time ondatabase application design, especially onoptions for Web application databasesupport -- but this will not be primaryfocus.

8/14/2019 Lecture01_257.ppt

5/51

2004.01.20 - SLIDE 5

IS 257Spring 2004

Assignments

Two kinds of assignments Using a pre-built database for search and

retrieval and database modification queries

Designing, populating, and running queries

against your own personal database Types of database project

Individual

Work related

Course only

Projects from around campus that need doing

Group

Course related

SIMS Final project

8/14/2019 Lecture01_257.ppt

6/51

8/14/2019 Lecture01_257.ppt

7/51

2004.01.20 - SLIDE 7

IS 257Spring 2004

Grading

Grades will be based on:Assignments (30%)

Personal/Group Database project (60%)

Class participation (10%) (No midterm or final)

8/14/2019 Lecture01_257.ppt

8/51

2004.01.20 - SLIDE 8

IS 257Spring 2004

Schedule

on website: http://sims.berkeley.edu/courses/is257/s04

/Schedule.html

8/14/2019 Lecture01_257.ppt

9/51

2004.01.20 - SLIDE 9

IS 257Spring 2004

What is a Database?

8/14/2019 Lecture01_257.ppt

10/51

2004.01.20 - SLIDE 10

IS 257Spring 2004

Files and Databases

File: A collection of records or documents

dealing with one organization, person, area or

subject. (Rowley)

Manual (paper) files Computer files

Database: A collection of similar records with

relationships between the records. (Rowley)

bibliographic, statistical, business data, images,etc.

8/14/2019 Lecture01_257.ppt

11/51

2004.01.20 - SLIDE 11

IS 257Spring 2004

Database

A Database is a collection of storedoperational data used by the applicationsystems of some particular enterprise. (C.J.

Date) Paper Databases

Still contain a large portion of the worlds knowledge

File-Based Data Processing Systems

Early batch processing of (primarily) business data

Database Management Systems (DBMS)

8/14/2019 Lecture01_257.ppt

12/51

2004.01.20 - SLIDE 12

IS 257Spring 2004

Terms and Concepts

Database Management System -- DBMS Software system used to define, create,

maintain and provide controlled access to the

database and repository

8/14/2019 Lecture01_257.ppt

13/51

2004.01.20 - SLIDE 13

IS 257Spring 2004

Terms and Concepts

RepositoryAKA Data Dictionary

The place where all metadata for a particular

database is stored

may also include information on relationships

between files or tables in a particular

database

8/14/2019 Lecture01_257.ppt

14/51

2004.01.20 - SLIDE 14

IS 257Spring 2004

Terms and Concepts

Metadata Data about data

In DBMS means all of the characteristics

describing the attributes of an entity, E.G.:

name of attribute

data type of attribute

size of the attribute

format or special characteristics

Characteristics of files or relations name, content, notes, etc.

8/14/2019 Lecture01_257.ppt

15/51

2004.01.20 - SLIDE 15

IS 257Spring 2004

Why DBMS?

History 50s and 60s all applications were custombuilt for particular needs

File based

Many similar/duplicative applications dealingwith collections of business data

Early DBMS were extensions of programminglanguages

1970 - E.F. Codd and the Relational Model

1979 - Ashton-Tate & first MicrocomputerDBMS

8/14/2019 Lecture01_257.ppt

16/51

2004.01.20 - SLIDE 16

IS 257Spring 2004

File Based Systems

Naughty

NiceJust whatasked for

Coal

Estimation

Delivery

List

Application File

ToysAddresses

Toys

8/14/2019 Lecture01_257.ppt

17/51

2004.01.20 - SLIDE 17

IS 257Spring 2004

From File Systems to DBMS

Problems with File Processingsystems

Inconsistent Data

Inflexibility

Limited Data Sharing

Poor enforcement of standards

Excessive program maintenance

8/14/2019 Lecture01_257.ppt

18/51

2004.01.20 - SLIDE 18

IS 257Spring 2004

DBMS Benefits

Minimal Data Redundancy Consistency of Data Integration of Data Sharing of Data

Ease of Application Development Uniform Security, Privacy, and IntegrityControls

Data Accessibility and Responsiveness

Data Independence Reduced Program Maintenance

8/14/2019 Lecture01_257.ppt

19/51

8/14/2019 Lecture01_257.ppt

20/51

2004.01.20 - SLIDE 20

IS 257Spring 2004

Database Environment

CASE

Tools

DBMS

User

Interface

Application

Programs

Repository Database

8/14/2019 Lecture01_257.ppt

21/51

2004.01.20 - SLIDE 21

IS 257Spring 2004

Database Components

DBMS===============

Design toolsTable Creation

Form Creation

Query CreationReport Creation

Procedural

language

compiler (4GL)

=============

Run time

Form processorQuery processor

Report Writer

Language Run time

User

Interface

Applications

Application

ProgramsDatabase

Database contains:Users Data

Metadata

Indexes

Application Metadata

8/14/2019 Lecture01_257.ppt

22/51

2004.01.20 - SLIDE 22

IS 257Spring 2004

Types of Database Systems

PC Databases Centralized Database

Client/Server Databases

Distributed Databases Database Models

8/14/2019 Lecture01_257.ppt

23/51

2004.01.20 - SLIDE 23

IS 257Spring 2004

PC Databases

E.G.

Access

FoxPro

DbaseEtc.

8/14/2019 Lecture01_257.ppt

24/51

2004.01.20 - SLIDE 24

IS 257Spring 2004

Centralized Databases

Cental

Computer

8/14/2019 Lecture01_257.ppt

25/51

8/14/2019 Lecture01_257.ppt

26/51

2004.01.20 - SLIDE 26

IS 257Spring 2004

Distributed Databases

computercomputer

computer

Location A

Location CLocation B

HomogeneousDatabases

8/14/2019 Lecture01_257.ppt

27/51

2004.01.20 - SLIDE 27

IS 257Spring 2004

Distributed Databases

Local Network

DatabaseServer

Client

Client

CommServer

Remote

Comp.

Remote

Comp.

Heterogeneous

Or Federated

Databases

8/14/2019 Lecture01_257.ppt

28/51

2004.01.20 - SLIDE 28

IS 257Spring 2004

Terms and Concepts

Database App l icat ionAn application program (or set of related

programs) that is used to perform a series of

database activities:

Create

Read

Update

Delete On behalf of database users

8/14/2019 Lecture01_257.ppt

29/51

2004.01.20 - SLIDE 29

IS 257Spring 2004

Range of Database Applications

PC databases Usually for individual

WorkGroup databases

Small group use where everyone has access

to the database over a LAN

Departmental databases

Larger than a workgroupbut similar

Enterprises databases For the entire organization over an intranet (or

sometimes the internet)

8/14/2019 Lecture01_257.ppt

30/51

2004.01.20 - SLIDE 30

IS 257Spring 2004

Terms and Concepts

Database activities: Create

Add new data to the database

Read

Read current data from the database

Update

Update or modify current database data

Delete Remove current data from the database

8/14/2019 Lecture01_257.ppt

31/51

2004.01.20 - SLIDE 31

IS 257Spring 2004

Terms and Concepts

Enterpr ise Organization

Enti ty

Person, Place, Thing, Event, Concept...

Att r ibutes

Data elements (facts) about some entity

Also sometimes called fields or items or domains

Data values instances of a particular attribute for a particular entity

8/14/2019 Lecture01_257.ppt

32/51

2004.01.20 - SLIDE 32

IS 257Spring 2004

Terms and Concepts

Records The set of values for all attributes of a

particular entity

AKA tuples or rows in relational DBMS

File

Collection of records

AKA Relation or Table in relational DBMS

8/14/2019 Lecture01_257.ppt

33/51

2004.01.20 - SLIDE 33

IS 257Spring 2004

Terms and Concepts

Key an attribute or set of attributes used to identify

or locate records in a file

Primary Key

an attribute or set of attributes that uniquely

identifies each record in a file

8/14/2019 Lecture01_257.ppt

34/51

2004.01.20 - SLIDE 34

IS 257Spring 2004

Terms and Concepts

DA Data adminstrator - person responsible for the

Data Administration function in an

organization

Sometimes may be the CIO -- Chief

Information Officer

DBA

Database Administrator - person responsiblefor the Database Administration Function

8/14/2019 Lecture01_257.ppt

35/51

T d C

8/14/2019 Lecture01_257.ppt

36/51

2004.01.20 - SLIDE 36

IS 257Spring 2004

Terms and Concepts

Models (1) Levels or views of the Database

Conceptual, logical, physical

(2) DBMS types

Relational, Hierarchic, Network, Object-Oriented,

Object-Relational

M d l (1)

8/14/2019 Lecture01_257.ppt

37/51

2004.01.20 - SLIDE 37

IS 257Spring 2004

Models (1)

Conceptual

Model

Logical

Model

External

Model

Conceptual

requirements

Conceptual

requirements

Conceptual

requirements

Conceptual

requirements

Application 1

Application 1

Application 2 Application 3 Application 4

Application 2

Application 3

Application 4

External

Model

External

Model

External

Model

Internal

Model

8/14/2019 Lecture01_257.ppt

38/51

D t M d l (2) Hi t

8/14/2019 Lecture01_257.ppt

39/51

2004.01.20 - SLIDE 39

IS 257Spring 2004

Data Models(2): History

Network Model (1970s) Provides for single entries of data and

navigational links through chains of data.

Subjects Books

Authors

Publishers

D t M d l (2) Hi t

8/14/2019 Lecture01_257.ppt

40/51

2004.01.20 - SLIDE 40

IS 257Spring 2004


Relational Model (1980s) Provides a conceptually simple model for data

as relations (typically considered tables) with

all data visible.

Book ID Tit le pubid Author id

1 Introductio 2 1

2 The histor 4 23 New stuff 3 3

4 Another titl 2 4

5 And yet m 1 5

pubid pubname

1 Harper

2 Addison

3 Oxford

4 Que

Authorid Author na

1 Smith

2 Wynar

3 Jones

4 Duncan

5 Applegate

Subid Subject

1 cataloging

2 history

3 stuff

Book ID Subid

1 2

2 1

3 3

4 2

4 3

D t M d l (2) Hi t

8/14/2019 Lecture01_257.ppt

41/51

2004.01.20 - SLIDE 41

IS 257Spring 2004


Object Oriented Data Model (1990s) Encapsulates data and operations as

Objects

Books

(id, title)

Publisher SubjectsAuthors

(first, last)

D t M d l (2) Hi t

8/14/2019 Lecture01_257.ppt

42/51

2004.01.20 - SLIDE 42

IS 257Spring 2004


Object-Relational Model (1990s) Combines the well-known properties of the

Relational Model with such OO features as:

User-defined datatypes

User-defined functions

Inheritance and sub-classing

D t b S t Lif C l

8/14/2019 Lecture01_257.ppt

43/51

2004.01.20 - SLIDE 43

IS 257Spring 2004

Database System Life Cycle

Growth,

Change, &

Maintenance6

Operations

5

Integration

4

Design

1

Conversion

3

Physical

Creation2

D i

8/14/2019 Lecture01_257.ppt

44/51

2004.01.20 - SLIDE 44

IS 257Spring 2004

Design

Determination of the needs of theorganization

Development of the Conceptual Model

of the database Typically using Entity-Relationship

diagramming techniques

Construction of a Data Dictionary

Development of the Logical Model

Ph i l C ti

8/14/2019 Lecture01_257.ppt

45/51

2004.01.20 - SLIDE 45

IS 257Spring 2004

Physical Creation

Development of the Physical Model of theDatabase data formats and types

determination of indexes, etc.

Load a prototype database and test

Determine and implement security, privacyand access controls

Determine and implement integrityconstraints

C i

8/14/2019 Lecture01_257.ppt

46/51

2004.01.20 - SLIDE 46

IS 257Spring 2004

Conversion

Convert existing data sets andapplications to use the new database

May need programs, conversion utilities to

convert old data to new formats.

Integration

8/14/2019 Lecture01_257.ppt

47/51

2004.01.20 - SLIDE 47

IS 257Spring 2004

Integration

Overlaps with Phase 3 Integration of converted applications and

new applications into the new database

Operations

8/14/2019 Lecture01_257.ppt

48/51

2004.01.20 - SLIDE 48

IS 257Spring 2004

Operations

All applications run full-scale Privacy, security, access control must be

in place.

Recovery and Backup procedures must beestablished and used

Growth Change & Maintenance

8/14/2019 Lecture01_257.ppt

49/51

2004.01.20 - SLIDE 49

IS 257Spring 2004

Growth, Change & Maintenance

Change is a way of lifeApplications, data requirements, reports, etc.

will all change as new needs and

requirements are found

The Database and applications and will needto be modified to meet the needs of changes

Another View of the Life Cycle

8/14/2019 Lecture01_257.ppt

50/51

2004.01.20 - SLIDE 50

IS 257Spring 2004

Another View of the Life Cycle

Operations

5

Conversion

3

Physical

Creation

2

Growth,

Change

6

Integration

4

Design1

Next Time

8/14/2019 Lecture01_257.ppt

51/51

Next Time

lecture01_257.ppt

Documents