Database Life Cycle and Introduction to Access

Database Life Cycle and Introduction to Access University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management

Lecture Outline • Review • Database Life Cycle • Access and the Diveshop Database

Database Environment CASE Tools User Interface Application Programs Repository DBMS Database

Database Components DBMS =============== Design tools Table Creation Form Creation Query Creation Report Creation Procedural language compiler (4GL) ============= Run time Form processor Query processor Report Writer Language Run time Database Application Programs Database contains: User’s Data Metadata Indexes Application Metadata User Interface Applications

Terms and Concepts • Database • DBMS • Enterprise • Entity • Attributes • Data values • Records • File • Key • Primary Key

Terms and Concepts • Data Dictionary • Data Administration • Database Administration • Data Steward • DA • DBA

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

Models (1) Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

Data Models(2): History Books (id, title) Authors (first, last) Publisher Subjects • Hierarchical Model (1960’s and 1970’s) • Similar to data structures in programming languages.

Data Models(2): History Authors Subjects Books Publishers • Network Model (1970’s) • Provides for single entries of data and navigational “links” through chains of data.

Data Models(2): History • Relational Model (1980’s) • Provides a conceptually simple model for data as relations (typically considered “tables”) with all data visible.

Data Models(2): History Books (id, title) Authors (first, last) Publisher Subjects • Object Oriented Data Model (1990’s) • Encapsulates data and operations as “Objects”

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

Database System Life Cycle Physical Creation 2 Conversion 3 Design 1 Growth, Change, & Maintenance 6 Integration 4 Operations 5

The “Cascade” View Project Identifcation and Selection Project Initiation and Planning Analysis Logical Design Physical Design Implementation Maintenance See Hoffer, p. 41

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

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

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

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

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

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

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

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.

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)

Diveshop Entities: SITES Site Highlight Site Notes Distance From Town (M) Site Name Distance From Town (Km) Destination no Depth (ft) Sites Site no Depth (m) Visibility(ft) Skill Level Visibility (m) Current

Diveshop Entities: DIVECUST City State/Prov ZIP/Postal Code Street Country Name Phone DiveCust Customer no First Contact

Diveshop Entities: DEST Spring Temp (C) Spring Temp (F) Avg Temp (C) Summer Temp (C) Avg Temp (F) Summer Temp (F) Destination name Fall Temp (C) Dest Destination no Fall Temp (F) Accommodations Winter Temp (C) Travel Cost Winter Temp (F) Body of Water Night Life

Diveshop Entities: BIOLIFE Species Name Length (cm) Length (in) Common Name Notes external Category Graphic external BioLife Species no

Diveshop Entities: SHIPWRCK Type Interest Tonnage Category Length (ft) Site no Length (m) Shipwrck Ship Name Beam (ft) Graphic external Condition Beam (m) Passengers/ Crew Cause Survivors Comments external Date Sunk

Diveshop Entities: DIVESTOK Reorder Point On Hand Cost Equipment Class Sale Price Description DiveStok Rental Price Item No

Diveshop Entities: DIVEORDS Ship Via Sale Date Customer No Payment Method DiveOrds Order no CCNumber Vacation Cost CCExpDate Destination No of People Return Date Depart Date

Diveshop Entities: DIVEITEM Qty Rental/ Sale Item no DiveItem Line Note Order no

Diveshop Entities: BIOSITE Species No Site No BioSite

Diveshop Entities: SHIPVIA Ship Via Ship Cost ShipVia

DiveShop ER Diagram Customer No DiveCust Destination Name ShipVia Customer No Destination no ShipVia ShipVia DiveOrds Dest Destination no Destination Order No Site No Order No Sites DiveItem Site No BioSite ShipWrck Item No Species No Site No DiveStok BioLife Item No Species No 1 n 1 n n 1 1 1 n n 1 1 n 1/n n n 1 1

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

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 Tuesday Feb. 3

Database Life Cycle and Introduction to Access