Database Life Cycle and Introduction to Access by g4509244

VIEWS: 23 PAGES: 45

									            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           User      Application
                     Tools        Interface    Programs




                     Repository
                                  DBMS            Database




IS 257 – Fall 2006                                           2006-08-31 - SLIDE 3
Database Components

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



IS 257 – Fall 2006                                          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, Object-
                       Oriented, Object-Relational




IS 257 – Fall 2006                                                2006-08-31 - SLIDE 12
 Models (1)

                         Application 1   Application 2   Application 3      Application 4
                          External        External        External           External
                           Model           Model           Model              Model
  Application 1
 Conceptual
requirements
 Application 2
 Conceptual
requirements                                                             Internal
                      Conceptual             Logical                     Model
 Application 3
 Conceptual
                        Model                Model
requirements
 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
                                     Publisher     Subjects
                     (first, last)



IS 257 – Fall 2006                                            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           pubname    Authorid       Author name
                                                                 1   Harper                1   Smith
                                                                 2   Addison               2   Wynar
                                                                 3   Oxford                3   Jones
Book ID       Title       pubid       Author id                  4   Que                   4   Duncan
          1   Introductio         2            1                                           5   Applegate
          2   The history         4            2
          3   New stuff ab        3            3
          4   Another title       2            4
          5   And yet more        1            5   Book ID       Subid
                                                             1           2
                                                                               Subid     Subject
                                                             2           1
                                                                                       1 cataloging
                                                             3           3
                                                                                       2 history
                                                             4           2
                                                                                       3 stuff
                                                             4           3



   IS 257 – Fall 2006                                                                                    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
                                     Publisher     Subjects
                     (first, last)


IS 257 – Fall 2006                                            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


                      Design                    Conversion
                        1                           3




                       Growth,
                      Change, &                 Integration
                     Maintenance
                                                     4
                          6

                                   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



                                                                                     Maintenance
See Hoffer, p. 41

IS 257 – Fall 2006                                                                   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           3    Change
                                             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        Site        Distance
                          Highlight    Notes     From Town (M)
                                                           Distance
            Site Name
                                                        From Town (Km)

           Destination
                                                               Depth (ft)
               no                       Sites

                Site no                                        Depth (m)


                                                        Visibility(ft)

                             Skill Level      Visibility (m)
                                        Current

IS 257 – Fall 2006                                                       2006-08-31 - SLIDE 32
Diveshop Entities: DIVECUST



                                                  ZIP/Postal
                              City   State/Prov
                                                    Code

                     Street                               Country


                     Name                                  Phone
                                      DiveCust

                                                            First
             Customer no
                                                           Contact




IS 257 – Fall 2006                                             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                                Fall
              name                  Dest             Temp (C)

                                                       Fall
         Destination no
                                                     Temp (F)

                                                    Winter
            Accommodations
                        Travel              Winter Temp (C)
                         Cost              Temp (F)
                           Body of      Night
                            Water        Life

IS 257 – Fall 2006                                            2006-08-31 - SLIDE 34
Diveshop Entities: BIOLIFE



                       Species   Length    Length
                        Name      (cm)      (in)
                 Common                              Notes
                  Name                              external

                                                    Graphic
                 Category
                                 BioLife            external

               Species no




IS 257 – Fall 2006                                       2006-08-31 - SLIDE 35
Diveshop Entities: SHIPWRCK

                               Type   Interest   Tonnage

                                                       Length
                Category
                                                         (ft)

                                                           Length
                     Site no
                                      Shipwrck              (m)

                                                           Beam
              Ship Name
                                                            (ft)
Graphic
                      Condition                       Beam
external                      Passengers/
                                              Cause    (m)
                                 Crew
                      Survivors Comments Date
                                   external Sunk

IS 257 – Fall 2006                                             2006-08-31 - SLIDE 36
Diveshop Entities: DIVESTOK
                                     Reorder
                                      Point
                           On Hand              Cost

              Equipment                                Sale
                Class                                  Price

             Description                               Rental
                                     DiveStok
                                                       Price
                 Item No




IS 257 – Fall 2006                                             2006-08-31 - SLIDE 37
Diveshop Entities: DIVEORDS

                            Ship
                             Via
                     Sale
                     Date

              Customer                                   Payment
                 No                   DiveOrds           Method

               Order no                                 CCNumber

                     Vacation
                                                       CCExpDate
                       Cost                      No of
                             Destination
                                                People
                                   Return   Depart
                                    Date     Date

IS 257 – Fall 2006                                             2006-08-31 - SLIDE 38
Diveshop Entities: DIVEITEM

                           Rental/          Qty
                            Sale


                 Item no                          Line
                                 DiveItem
                                                  Note

                Order no




IS 257 – Fall 2006                                   2006-08-31 - SLIDE 39
Diveshop Entities: BIOSITE

                     Species             Site
                       No                No




                               BioSite




IS 257 – Fall 2006                              2006-08-31 - SLIDE 40
Diveshop Entities: SHIPVIA

                     Ship             Ship
                      Via             Cost




                            ShipVia




IS 257 – Fall 2006                           2006-08-31 - SLIDE 41
  DiveShop ER Diagram
                                        Customer
                                           No              DiveCust
                                                               1
                        Destination         Customer                  ShipVia
                          Name                 No
              Destination                                      n

                  no                                                  n
                                                                                 1
                                                                                     ShipVia          ShipVia
                                        1              n
                                Dest                       DiveOrds
                                                                      1
                                   1
                 Destination
                     no                                     Order
                                            Destination
            Site No                n
                                                             No
                                                                                       Order
                            1
                                Sites   1                                    n
                                                                                        No
                                                 1/n
Site No
                       n
                                                                          DiveItem
                                                                                       Item
                  BioSite                   ShipWrck                         n          No
Species                n
  No
                                             Site No                         1
                       1


Species                                                                   DiveStok     Item
                  BioLife                                                               No
  No

  IS 257 – Fall 2006                                                                    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

								
To top