Database Life Cycle and Introduction to Access

1
Database Life Cycle and Introduction to Access

• University of California, Berkeley
• School of Information Management and Systems
• SIMS 257 Database Management

2
Lecture Outline

• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database

3
Database Environment
4
Database Components
5
Terms and Concepts

• Database
• DBMS
• Data Independence
• Metadata
• Data Dictionary

6
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

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

9
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

10
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
  organizations data, and all of the interactions
  (applications, user access, etc.) for that data

11
Lecture Outline

• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database

12
Terms and Concepts

• Models
• (1) Levels or views of the Database
• Conceptual, logical, physical
• (2) DBMS types
• Relational, Hierarchic, Network, Object-Oriented,
  Object-Relational

13
Models (1)
14
Data Models(2) History

• Hierarchical Model (1960s and 1970s)
• Similar to data structures in programming
  languages.

15
Data Models(2) History

• Network Model (1970s)
• Provides for single entries of data and
  navigational links through chains of data.

16
Data Models(2) History

• Relational Model (1980s)
• Provides a conceptually simple model for data as
  relations (typically considered tables) with
  all data visible.

17
Data Models(2) History

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

18
Data Models(2) History

• 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

19
Lecture Outline

• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database

20
Database System Life Cycle
21
The Cascade View
See Hoffer, p. 41
22
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

23
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

24
Conversion

• Convert existing data sets and applications to
  use the new database
• May need programs, conversion utilities to
  convert old data to new formats.

25
Integration

• Overlaps with Phase 3
• Integration of converted applications and new
  applications into the new database

26
Operations

• All applications run full-scale
• Privacy, security, access control must be in
  place.
• Recovery and Backup procedures must be
  established and used

27
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

28
Another View of the Life Cycle
Integration 4
Operations 5
Design 1
Physical Creation 2
Conversion 3
Growth, Change 6
29
Lecture Outline

• Review
• Database Models
• Database Life Cycle
• Access and the Diveshop Database

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

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

32
Diveshop Entities SITES
33
Diveshop Entities DIVECUST
34
Diveshop Entities DEST
35
Diveshop Entities BIOLIFE
36
Diveshop Entities SHIPWRCK
37
Diveshop Entities DIVESTOK
Reorder Point
On Hand
Cost
Equipment Class
Sale Price
Description
DiveStok
Rental Price
Item No
38
Diveshop Entities DIVEORDS
39
Diveshop Entities DIVEITEM
40
Diveshop Entities BIOSITE
41
Diveshop Entities SHIPVIA
42
DiveShop ER Diagram
1
n
1
n
n
1
1
1
n
n
1
1
1/n
n
n
n
1
1
43
Assignment 1 (also online)

• How many tons was the sunken ship Delaware?
• What is customer Karen Ngs 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

44
Assignment 1 cont.

• The Database is available on the course web site
• Download your own copy
• 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 day Feb. 3