Database Life Cycle and
Introduction to Access
University of California, Berkeley
School of Information
IS 257: Database Management
IS 257 – Fall 2006
2006-08-31 - SLIDE 1
Lecture Outline
• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database
IS 257 – Fall 2006
2006-08-31 - SLIDE 2
Database Environment
CASE
Tools
Repository
IS 257 – Fall 2006
User
Interface
DBMS
Application
Programs
Database
2006-08-31 - SLIDE 3
Database Components
DBMS
===============
Design tools
Database
Database contains:
User’s Data
Metadata
Indexes
Application Metadata
IS 257 – Fall 2006
Table Creation
Form Creation
Query Creation
Report Creation
Procedural
language
compiler (4GL)
=============
Run time
Form processor
Query processor
Report Writer
Language Run time
Application
Programs
User
Interface
Applications
2006-08-31 - SLIDE 4
Terms and Concepts
•
•
•
•
Database
DBMS
Data Independence
Metadata
– Data Dictionary
IS 257 – Fall 2006
2006-08-31 - SLIDE 5
Terms and Concepts
• Enterprise
– Organization
• Entity
– Person, Place, Thing, Event, Concept...
• Attributes
– 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
IS 257 – Fall 2006
2006-08-31 - SLIDE 6
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
IS 257 – Fall 2006
2006-08-31 - SLIDE 7
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
IS 257 – Fall 2006
2006-08-31 - SLIDE 8
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 responsible
for the Database Administration Function
IS 257 – Fall 2006
2006-08-31 - SLIDE 9
Terms and Concepts
• Data Administration
– Responsibility for the overall management of data
resources within an organization
• Database Administration
– Responsibility for physical database design and
technical issues in database management
• Data Steward
– Responsibility for some subset of the
organization’s data, and all of the interactions
(applications, user access, etc.) for that data
IS 257 – Fall 2006
2006-08-31 - SLIDE 10
Lecture Outline
• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database
IS 257 – Fall 2006
2006-08-31 - SLIDE 11
Terms and Concepts
• Models
– (1) Levels or views of the Database
• Conceptual, logical, physical
– (2) DBMS types
• Relational, Hierarchic, Network, ObjectOriented, Object-Relational
IS 257 – Fall 2006
2006-08-31 - SLIDE 12
Models (1)
Application 1
External
Model
Application 2
Application 3
Application 4
External
Model
External
Model
External
Model
Application 1
Conceptual
requirements
Application 2
Conceptual
requirements
Application 3
Conceptual
requirements
Conceptual
Model
Logical
Model
Internal
Model
Application 4
Conceptual
requirements
IS 257 – Fall 2006
2006-08-31 - SLIDE 13
Data Models(2): History
• Hierarchical Model (1960’s and 1970’s)
– Similar to data structures in programming
languages.
Books
(id, title)
Authors
(first, last)
IS 257 – Fall 2006
Publisher
Subjects
2006-08-31 - SLIDE 14
Data Models(2): History
• Network Model (1970’s)
– Provides for single entries of data and
navigational “links” through chains of data.
Authors
Subjects
Books
Publishers
IS 257 – Fall 2006
2006-08-31 - SLIDE 15
Data Models(2): History
• Relational Model (1980’s)
– Provides a conceptually simple model for data
as relations (typically considered “tables”) with
all data visible.
pubid
Book ID
1
2
3
4
5
Title
pubid
Introductio
The history
New stuff ab
Another title
And yet more
IS 257 – Fall 2006
2
4
3
2
1
Author id
1
2
3
4
5
1
2
3
4
Book ID
pubname
Harper
Addison
Oxford
Que
Authorid
1
2
3
4
5
Author name
Smith
Wynar
Jones
Duncan
Applegate
Subid
1
2
3
4
4
2
1
3
2
3
Subid
Subject
1 cataloging
2 history
3 stuff
2006-08-31 - SLIDE 16
Data Models(2): History
• Object Oriented Data Model (1990’s)
– Encapsulates data and operations as
“Objects”
Books
(id, title)
Authors
(first, last)
IS 257 – Fall 2006
Publisher
Subjects
2006-08-31 - SLIDE 17
Data Models(2): History
• Object-Relational Model (1990’s)
– Combines the well-known properties of the
Relational Model with such OO features as:
• User-defined datatypes
• User-defined functions
• Inheritance and sub-classing
IS 257 – Fall 2006
2006-08-31 - SLIDE 18
Lecture Outline
• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database
IS 257 – Fall 2006
2006-08-31 - SLIDE 19
Database System Life Cycle
Physical
Creation
2
Conversion
3
Design
1
Growth,
Change, &
Maintenance
6
Integration
4
Operations
5
IS 257 – Fall 2006
2006-08-31 - SLIDE 20
The “Cascade” View
Project
Identifcation
and Selection
Project
Initiation
and Planning
Analysis
Logical
Design
Physical
Design
Implementation
See Hoffer, p. 41
IS 257 – Fall 2006
Maintenance
2006-08-31 - SLIDE 21
Design
• Determination of the needs of the
organization
• Development of the Conceptual Model
of the database
– Typically using Entity-Relationship
diagramming techniques
• Construction of a Data Dictionary
• Development of the Logical Model
IS 257 – Fall 2006
2006-08-31 - SLIDE 22
Physical Creation
• Development of the Physical Model of
the Database
– data formats and types
– determination of indexes, etc.
• Load a prototype database and test
• Determine and implement security,
privacy and access controls
• Determine and implement integrity
constraints
IS 257 – Fall 2006
2006-08-31 - SLIDE 23
Conversion
• Convert existing data sets and
applications to use the new database
– May need programs, conversion utilities to
convert old data to new formats.
IS 257 – Fall 2006
2006-08-31 - SLIDE 24
Integration
• Overlaps with Phase 3
• Integration of converted applications and
new applications into the new database
IS 257 – Fall 2006
2006-08-31 - SLIDE 25
Operations
• All applications run full-scale
• Privacy, security, access control must be in
place.
• Recovery and Backup procedures must be
established and used
IS 257 – Fall 2006
2006-08-31 - SLIDE 26
Growth, Change & Maintenance
• Change is a way of life
– Applications, data requirements, reports, etc.
will all change as new needs and
requirements are found
– The Database and applications and will need
to be modified to meet the needs of changes
IS 257 – Fall 2006
2006-08-31 - SLIDE 27
Another View of the Life Cycle
Integration
4
Operations
5
Design
Physical
1
Creation Conversion Growth,
2
Change
3
6
IS 257 – Fall 2006
2006-08-31 - SLIDE 28
Lecture Outline
• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database
IS 257 – Fall 2006
2006-08-31 - SLIDE 29
Test Database
• The DiveShop database contains
information for the business operations of
a skin & scuba diving shop that:
– Organizes trips to particular locations
(destinations) with various dive sites
– Dive sites have various features including
• types of marine life found there
• other features (like shipwrecks)
– Rents/Sells equipment to dive customers for
particular trips.
IS 257 – Fall 2006
2006-08-31 - SLIDE 30
ER Diagrams
• Entity-Relationship Diagrams are one of
the main tools for database design
• We will examine ER diagrams in greater
detail later
• ER Diagrams show Entities (rectangles)
and their attributes (ovals) and the
relationships between entities (diamonds)
IS 257 – Fall 2006
2006-08-31 - SLIDE 31
Diveshop Entities: SITES
Site
Highlight
Site
Notes
Site Name
Destination
no
Distance
From Town (M)
Distance
From Town (Km)
Depth (ft)
Sites
Site no
Depth (m)
Visibility(ft)
Skill Level
Visibility (m)
Current
IS 257 – Fall 2006
2006-08-31 - SLIDE 32
Diveshop Entities: DIVECUST
City
State/Prov
Street
Name
Customer no
IS 257 – Fall 2006
ZIP/Postal
Code
Country
DiveCust
Phone
First
Contact
2006-08-31 - SLIDE 33
Diveshop Entities: DEST
Spring Spring
Avg Temp (C)Temp (F) Summer
Temp (C)
Temp (C)
Avg
Summer
Temp (F)
Temp (F)
Destination
name
Dest
Destination no
Accommodations
Travel
Cost
Body of
Water
IS 257 – Fall 2006
Fall
Temp (C)
Fall
Temp (F)
Winter
Winter Temp (C)
Temp (F)
Night
Life
2006-08-31 - SLIDE 34
Diveshop Entities: BIOLIFE
Species
Name
Common
Name
Category
Length
(cm)
Length
(in)
Notes
external
BioLife
Graphic
external
Species no
IS 257 – Fall 2006
2006-08-31 - SLIDE 35
Diveshop Entities: SHIPWRCK
Type
Interest
Tonnage
Length
(ft)
Category
Site no
Shipwrck
Ship Name
Graphic
external
IS 257 – Fall 2006
Condition
Passengers/
Cause
Crew
Survivors Comments Date
external Sunk
Length
(m)
Beam
(ft)
Beam
(m)
2006-08-31 - SLIDE 36
Diveshop Entities: DIVESTOK
Reorder
Point
On Hand
Cost
Equipment
Class
Description
Sale
Price
DiveStok
Rental
Price
Item No
IS 257 – Fall 2006
2006-08-31 - SLIDE 37
Diveshop Entities: DIVEORDS
Ship
Via
Sale
Date
Customer
No
DiveOrds
Order no
CCNumber
Vacation
Cost
Destination
Return
Date
IS 257 – Fall 2006
Payment
Method
CCExpDate
No of
People
Depart
Date
2006-08-31 - SLIDE 38
Diveshop Entities: DIVEITEM
Rental/
Sale
Item no
DiveItem
Qty
Line
Note
Order no
IS 257 – Fall 2006
2006-08-31 - SLIDE 39
Diveshop Entities: BIOSITE
Species
No
Site
No
BioSite
IS 257 – Fall 2006
2006-08-31 - SLIDE 40
Diveshop Entities: SHIPVIA
Ship
Via
Ship
Cost
ShipVia
IS 257 – Fall 2006
2006-08-31 - SLIDE 41
DiveShop ER Diagram
Customer
No
DiveCust
1
Destination
Name
Destination
no
Dest
Customer
No
ShipVia
n
1
n
DiveOrds
1
n
ShipVia
ShipVia
1
Destination
no
Site No
1
n
Site No
BioSite
Species
No
1
Destination
n
Sites
1
Order
No
n
1/n
Order
No
DiveItem
ShipWrck
n
Site No
1
Item
No
n
1
Species
No
BioLife
IS 257 – Fall 2006
DiveStok
Item
No
2006-08-31 - SLIDE 42
Diveshop Additions
• Over the course of the semester we
(mostly me) will be expanding and
modifying the Diveshop to include
additional data (and entities)
• Most likely inclusions are charter boat
bookings for particular destinations, boat
operators (captains) and dive masters
IS 257 – Fall 2006
2006-08-31 - SLIDE 43
Assignment 1 (also online)
• How many tons was the sunken ship Delaware?
• What is customer Karen Ng’s address?
• At what destinations and sites might you find a
Spotted Eagle Ray?
• Where (what destination) is the site Palancar
Reef?
• What sites might Lorraine Vega dive on her trip?
• Keith Lucas wants to see a shipwreck on his trip.
Is he going to the right place?
• What equipment is Richard Denning getting?
• What is the cost of the equipment rental for
Louis Jazdzewski
IS 257 – Fall 2006
2006-08-31 - SLIDE 44
Assignment 1: cont.
• The Database is available on the course web
site
• Download your own copy (NEW VERSION!)
• For each of the questions create a query in
Access
• Create a document (Word, etc.) containing
– The query being answered
– The results of your query cut and pasted from Access
• Due date Sept. 7
IS 257 – Fall 2006
2006-08-31 - SLIDE 45
Descargar

Document