Database Life Cycle and Introduction to Access University

Database Life Cycle and Introduction to Access University of California, Berkeley School of Information IS 257: Database Management IS 257 – Fall 2006 -08 -31 - SLIDE 1

Lecture Outline • Review • Database Models • Database Life Cycle • Access and the Diveshop Database IS 257 – Fall 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 contains: User’s Data Metadata Indexes Application Metadata IS 257 – Fall 2006 Table Creation Form Creation Query Creation Report Creation Procedural language compiler (4 GL) ======= 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 -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 -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 -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 -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 -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 -08 -31 - SLIDE 10

Lecture Outline • Review • Database Models • Database Life Cycle • Access and the Diveshop Database IS 257 – Fall 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, Object. Oriented, Object-Relational IS 257 – Fall 2006 -08 -31 - SLIDE 12

Models (1) Application 1 External Model Application 2 Application 3 Application 4 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 -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 -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. IS 257 – Fall 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 -08 -31 - SLIDE 18

Lecture Outline • Review • Database Models • Database Life Cycle • Access and the Diveshop Database IS 257 – Fall 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 -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 -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 -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 -08 -31 - SLIDE 24

Integration • Overlaps with Phase 3 • Integration of converted applications and new applications into the new database IS 257 – Fall 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 -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 -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 -08 -31 - SLIDE 28

Lecture Outline • Review • Database Models • Database Life Cycle • Access and the Diveshop Database IS 257 – Fall 2006 -08 -31 - SLIDE 29

Test Database • The Dive. Shop 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 -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 -08 -31 - SLIDE 31

Diveshop Entities: SITES Site Highlight Site Notes Distance From Town (Km) Site Name Destination no Distance From Town (M) Depth (ft) Sites Site no Depth (m) Visibility(ft) Skill Level Visibility (m) Current IS 257 – Fall 2006 -08 -31 - SLIDE 32

Diveshop Entities: DIVECUST City State/Prov Street Name Customer no IS 257 – Fall 2006 ZIP/Postal Code Country Dive. Cust Phone First Contact 2006 -08 -31 - SLIDE 33

Diveshop Entities: DEST Spring Avg Temp (C)Temp (F) Summer Temp (C) Avg Temp (F) Destination name Summer Temp (F) Destination no Accommodations Travel Cost Body of Water IS 257 – Fall 2006 Fall Temp (C) Fall Temp (F) Winter Temp (C) Temp (F) Night Life 2006 -08 -31 - SLIDE 34

Diveshop Entities: BIOLIFE Species Name Length (cm) Length (in) Common Name Notes external Category Graphic external Bio. Life Species no IS 257 – Fall 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 Dive. Stok Rental Price Item No IS 257 – Fall 2006 -08 -31 - SLIDE 37

Diveshop Entities: DIVEORDS Ship Via Sale Date Customer No Dive. Ords Order no CCNumber Vacation Cost Destination Return Date IS 257 – Fall 2006 Payment Method CCExp. Date No of People Depart Date 2006 -08 -31 - SLIDE 38

Diveshop Entities: DIVEITEM Rental/ Sale Item no Qty Dive. Item Line Note Order no IS 257 – Fall 2006 -08 -31 - SLIDE 39

Diveshop Entities: BIOSITE Species No Site No Bio. Site IS 257 – Fall 2006 -08 -31 - SLIDE 40

Diveshop Entities: SHIPVIA Ship Via Ship Cost Ship. Via IS 257 – Fall 2006 -08 -31 - SLIDE 41

Dive. Shop ER Diagram Customer No Dive. Cust 1 Destination Name Destination no Dest Customer No 1 n Ship. Via n Dive. Ords 1 n Ship. Via 1 Destination no Site No 1 n Site No Species No Bio. Site 1 Destination n Sites 1 Order No n 1/n Dive. Item Ship. Wrck n Site No 1 Order No Item No n 1 Species No Bio. Life IS 257 – Fall 2006 Dive. Stok 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 -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 -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 -08 -31 - SLIDE 45