1 / 40

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. Lecture Outline. Review Database Life Cycle

arthurl
Download Presentation

Database Life Cycle and Introduction to Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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 Life Cycle • Access and the Diveshop Database

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

  4. Database Environment CASE Tools User Interface Application Programs Repository DBMS Database

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  27. 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)

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

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

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

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

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

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

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

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

  36. Diveshop Entities: BIOSITE Species No Site No BioSite

  37. Diveshop Entities: SHIPVIA Ship Via Ship Cost ShipVia

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

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

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

More Related