Database Models and Introduction to Access - PowerPoint PPT Presentation

About This Presentation
Title:

Database Models and Introduction to Access

Description:

Database Models and Introduction to Access University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management – PowerPoint PPT presentation

Number of Views:273
Avg rating:3.0/5.0
Slides: 51
Provided by: RayR157
Category:

less

Transcript and Presenter's Notes

Title: Database Models and Introduction to Access


1
Database Models and Introduction to Access
  • University of California, Berkeley
  • School of Information Management and Systems
  • SIMS 257 Database Management

2
Last Time
  • Database concepts and terminology
  • Database Life cycle

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

4
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

5
Models (1)
6
Terms and Concepts
  • Data Dictionary
  • Data Administration
  • Database Administration
  • Data Steward
  • DA
  • DBA

7
Database System Life Cycle
Physical Creation 2
Conversion 3
Design 1
Growth, Change, Maintenance 6
Integration 4
Operations 5
8
Today
  • Models(2) DBMS types
  • Hierarchical
  • Network
  • Relational
  • Object-Oriented
  • Object-Relational

9
Database Data Models
  • Hierarchical Model
  • Similar to data structures in programming
    languages.

10
Hierarchical Model
  • Data items are structured in a Parent-Child
    hierarchical relationship
  • Data items are grouped into logical record
    types. Each of these approximately corresponds
    to a table in the relational model.
  • FOCUS permits virtual segments that are stored
    as files.

11
Hierarchical Model for Cookie
12
FOCUS BIBFILE Definition
FILENAME BIBFILE, SUFFIX FOC, SEGNAME
BIBINFO, SEGTYPE S, FIELD ACCESSION
NO, ALIAS ACCNO, USAGE A4, FIELD
AUTHOR, ALIAS AU, USAGE A30, FIELD
TITLE, ALIAS TI, USAGE A40, FIELD
LOCATION, ALIAS LOC, USAGE A20,
FIELD PUBLISHERID, ALIAS PUBID, USAGE A2,
FIELD DATE, ALIAS D, USAGE A4,
FIELD PRICE, ALIAS PR, USAGE D6.2,
FIELD PAGINATION, ALIAS PAGIN, USAGE
A13, FIELD ILLUSTRATION, ALIAS ILL,
USAGE A9, FIELD HEIGHT, ALIAS HT,
USAGE I2, SEGNAME PUBINFO, PARENT
BIBINFO, SEGTYPE KU, CRFILE PUBFILE,
CRKEY PUBLISHERID, SEGNAME INDXINFO,
PARENT BIBINFO, SEGTYPE S, FIELD
SUBID, ALIAS SID, USAGE A2, SEGNAME
CALLINFO, PARENT BIBINFO, SEGTYPE S,
FIELD LIBRARYID, ALIAS LIBID, USAGE A2,
FIELD CALL NUMBER, ALIAS CALLNO, USAGE
A15, FIELD COPIES, ALIAS C, USAGE
I4, SEGNAME LIBINFO, PARENT CALLINFO,
SEGTYPE KU, CRFILE LIBFILE, CRKEY
LIBRARYID, SEGNAME SUBINFO, PARENT
INDXINFO, SEGTYPE KU, CRFILE SUBFILE,
CRKEY SUBID,
13
PUBFILE Segment
FILENAME PUBFILE, SUFFIX FOC, SEGNAME
PUBINFO, SEGTYPE S, FIELD
PUBLISHERID, ALIAS PUBID, USAGE A2, FIELDTYPE
I, FIELD PUBLISHER, ALIAS PNAME,
USAGE A30, FIELD PUB ADDRESS, ALIAS
PADDRESS, USAGE A20, FIELD PUB
CITY, ALIAS PCITY, USAGE A15, FIELD
PUB STATE, ALIAS PSTATE, USAGE A2,
FIELD PUB ZIP, ALIAS PZIP, USAGE A5,
FIELD PUB PHONE, ALIAS PPHONE, USAGE A10,
FIELD PUB SHIP, ALIAS SHIP, USAGE
I3,
14
SUBFILE Segment
FILENAME SUBFILE, SUFFIX FOC, SEGNAME
SUBINFO, SEGTYPE S, FIELD SUBID,
ALIAS SID, USAGE A2, FIELDTYPE I,
FIELD SUBJECT, ALIAS SUB, USAGE A32,
15
LIBFILE Segment
FILENAME LIBFILE, SUFFIX FOC, SEGNAME
LIBINFO, SEGTYPE S, FIELD
LIBRARYID, ALIAS LIBID, USAGE A2, FIELDTYPE
I, FIELD LIBRARY, ALIAS LIB, USAGE
A42, FIELD LIB ADDRESS, ALIAS
LADDRESS, USAGE A25, FIELD LIB CITY,
ALIAS LCITY, USAGE A15, FIELD LIB
STATE, ALIAS LSTATE, USAGE A2, FIELD
LIB ZIP, ALIAS LZIP, USAGE A5,
FIELD LIB PHONE, ALIAS LPHONE, USAGE A10,
FIELD MONOPEN, ALIAS MOP, USAGE I4,
FIELD MONCLOSE, ALIAS MCL, USAGE
I4, FIELD TUEOPEN, ALIAS TUOP, USAGE
I4, FIELD TUECLOSE, ALIAS TUCL,
USAGE I4, FIELD WEDOPEN, ALIAS
WOP, USAGE I4, FIELD WEDCLOSE, ALIAS
WCL, USAGE I4, FIELD THURSOPEN,
ALIAS THOP, USAGE I4, FIELD
THURSCLOSE, ALIAS THCL, USAGE I4,
FIELD FRIOPEN, ALIAS FOP, USAGE I4,
FIELD FRICLOSE, ALIAS FCL, USAGE I4,
FIELD SATOPEN, ALIAS SATOP, USAGE I4,
FIELD SATCLOSE, ALIAS SATCL, USAGE
I4, FIELD SUNOPEN, ALIAS SUNOP,
USAGE I4, FIELD SUNCLOSE, ALIAS
SUNCL, USAGE I4,
16
Hierarchic Querying
  • All searches must proceed from the root of the
    hierarchy, and traverse each segment containing
    required information

17
Hierarchical Query Processing
18
Database Data Models
  • Network Model
  • Provides for single entries of data and
    navigational links through chains of data.

19
Network Database Systems
  • Network DBMS are an evolutionary step from
    Hierarchical systems.
  • Hierarchical systems can be considered a subset
    of Network systems.

20
History
  • Specifications for network systems came from
    CODASYL (Conference on Data and Systems
    Languages) -- The same fine folks who brought you
    COBOL.
  • The DTBG (Data Base Task Group) was founded in
    1965 to specify a standard language for
    manipulating records
  • The result was a report (published in 1971)

21
History
  • The CODASYL DBTG report contained specifications
    for
  • A DDL - Data Definition Language
  • A DML - Data Manipulation Language
  • Inherent in the report was the underlying Network
    database structure.

22
Components of DDL and DML
  • DDL is used to describe or define
  • database records
  • individual data items
  • the associations that exist between record types
  • security
  • record positioning
  • The database definition created by the DDL is
    called the database schema
  • User views can also be defined in the DDL and are
    called subschemas

23
DDL Continued
  • DDL is also used by the database designer to
    define all associations between record types
  • These associations are called Sets and are
    sometimes referred to as DTBG Sets.
  • Sets describe a one to many relationship between
    two distinct record types.
  • The record on the one side of the set is called
    the owner
  • The record on the many side of the set is
    called the member

24
DDL Definitions
  • Example DDL for a DB (partial)

RECORD NAME IS INVOICE LOCATION MODE IS
VIA CUSTOMER-INVOICE SET WITHIN ORDERENTRY 02
INVOICE-ID PICTURE IS
X(5). 02 INVOICE-DATE PICTURE
IS 9(6). 02 INVOICE-AMOUNT TYPE IS
BINARY. Etc...
SCHEMA NAME IS SAMPLEDB. AREA NAME IS
ORDERS. RECORD NAME IS CUSTOMER LOCATION
MODE IS CALC USING CUSTOMERID DUPLICATES ARE NOT
ALLOWED WITHIN ORDERENTRY 02 CUSTOMERID
PICTURE IS X(5). 02
CUSTOMER-NAME PICTURE IS
X(30). 02 CUSTOMER-ADDRESS. 05 STREET
PICTURE IS X(25).
05 CITY
PICTURE IS X(15). 05 STATE
PICTURE IS XX. 05 ZIPCODE
PICTURE IS X(10). 02
CUSTOMER-TELEPHONE PICTURE IS X(13). Etc.
SET NAME IS CUSTOMER-INVOICE OWNER IS
CUSTOMER INSERTION IS FIRST MEMBER
IS INVOICE MANDATORY AUTOMATIC LINKED TO
OWNER SET SELECTION IS THRU CUSTOMER-INVOICE
CURRENT OF SET.
25
Set Definition and Pointers
CUSTOMER
INVOICE
INVOICE
INVOICE
INVOICE
26
Set Definitions and Pointers
INVOICE
Lst
Fst
LINE-ITEM
O
N
P
LINE-ITEM
O
N
P
LINE-ITEM
O
N
P
LINE-ITEM
O
N
P
LINE-ITEM
O
N
P
27
Database Data Models
  • Relational Model
  • Provides a conceptually simple model for data as
    relations (typically considered tables) with
    all data visible.

28
Database Data Models
  • Object Oriented Data Model
  • Encapsulates data and operations as Objects

29
Object-Oriented DBMSBasic Concepts
  • Each real-world entity is modeled by an object.
    Each object is associated with a unique
    identifier (sometimes call the object ID or OID)

30
Object-Oriented DBMSBasic Concepts
  • Each object has a set of instance attributes (or
    instance variables) and methods.
  • The value of an attribute can be an object or set
    of objects. Thus complex object can be
    constructed from aggregations of other objects.
  • The set of attributes of the object and the set
    of methods represent the object structure and
    behavior, respectively

31
Object-Oriented DBMSBasic Concepts
  • The attribute values of an object represent the
    objects status.
  • Status is accessed or modified by sending
    messages to the object to invoke the
    corresponding methods

32
Object-Oriented DBMSBasic Concepts
  • Objects sharing the same structure and behavior
    are grouped into classes.
  • A class represents a template for a set of
    similar objects.
  • Each object is an instance of some class.

33
Object-Oriented DBMSBasic Concepts
  • A class can be defined as a specialization of of
    one or more classes.
  • A class defined as a specialization is called a
    subclass and inherits attributes and methods from
    its superclass(es).

34
Object-Oriented DBMSBasic Concepts
  • An OODBMS is a DBMS that directly supports a
    model based on the object-oriented paradigm.
  • Like any DBMS it must provide persistent storage
    for objects and their descriptions (schema).
  • The system must also provide a language for
    schema definition and and for manipulation of
    objects and their schema
  • It will usually include a query language,
    indexing capabilities, etc.

35
Generalization Hierarchy
36
Database Data Models
  • 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

37
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 (or to other dive shops)

38
ER Diagrams
  • We will examine ER diagrams in greater detail
    later
  • ER Diagrams show Entities (rectangles) and their
    attributes (ovals) and the relationships between
    entities (diamonds)

39
Diveshop ER Entities SITES
40
Diveshop ER Entities DIVECUST
41
Diveshop ER Entities DEST
42
Diveshop ER Entities BIOLIFE
43
Diveshop ER Entities SHIPWRCK
44
Diveshop ER Entities DIVESTOK
Reorder Point
On Hand
Cost
Equipment Class
Sale Price
Description
DiveStok
Rental Price
Item No
45
Diveshop ER Entities DIVEORDS
Ship Via
Sale Date
Customer No
Payment Method
DiveOrds
Order no
CCNumber
CCExpDate
Vacation Cost
Destination
No of People
Return Date
Depart Date
46
Diveshop ER diagram DIVEITEM
Qty
Rental/ Sale
Item no
DiveItem
Line Note
Order no
47
Diveshop ER diagram BIOSITE
48
Diveshop ER diagram SHIPVIA
49
DiveShop ER Diagram All
1
n
1
n
n
1
1
1
n
n
1
1
1/n
n
n
n
1
1
50
Assignment 1Diveshop Questions
  1. How many tons was the sunken ship Delaware?
  2. What is customer Karen Ngs address?
  3. At what site might you find a Spotted Eagle Ray?
  4. Where is the site Palancar Reef?
  5. What sites might Lorraine Vega dive on her trip?
  6. Keith Lucas wants to see a shipwreck on his trip.
    Is he going to the right place?
  7. What equipment is Richard Denning getting?
  8. What is the cost of the equipment rental for
    Louis Jazdzewski
Write a Comment
User Comments (0)
About PowerShow.com