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