Title: Database Models and Introduction to Access
1Database Models and Introduction to Access
- University of California, Berkeley
- School of Information Management and Systems
- SIMS 257 Database Management
2Last Time
- Database concepts and terminology
- Database Life cycle
3Terms and Concepts
- Database
- Enterprise
- Entity
- Attributes
- Data values
- Records
- File
- Key
- Primary Key
4Terms 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
5Models (1)
6Terms and Concepts
- Data Dictionary
- Data Administration
- Database Administration
- Data Steward
- DA
- DBA
7Database System Life Cycle
Physical Creation 2
Conversion 3
Design 1
Growth, Change, Maintenance 6
Integration 4
Operations 5
8Today
- Models(2) DBMS types
- Hierarchical
- Network
- Relational
- Object-Oriented
- Object-Relational
9Database Data Models
- Hierarchical Model
- Similar to data structures in programming
languages.
10Hierarchical 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.
11Hierarchical Model for Cookie
12FOCUS 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,
13PUBFILE 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,
14SUBFILE Segment
FILENAME SUBFILE, SUFFIX FOC, SEGNAME
SUBINFO, SEGTYPE S, FIELD SUBID,
ALIAS SID, USAGE A2, FIELDTYPE I,
FIELD SUBJECT, ALIAS SUB, USAGE A32,
15LIBFILE 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,
16Hierarchic Querying
- All searches must proceed from the root of the
hierarchy, and traverse each segment containing
required information
17Hierarchical Query Processing
18Database Data Models
- Network Model
- Provides for single entries of data and
navigational links through chains of data.
19Network Database Systems
- Network DBMS are an evolutionary step from
Hierarchical systems. - Hierarchical systems can be considered a subset
of Network systems.
20History
- 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)
21History
- 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.
22Components 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
23DDL 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
24DDL 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.
25Set Definition and Pointers
CUSTOMER
INVOICE
INVOICE
INVOICE
INVOICE
26Set 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
27Database Data Models
- Relational Model
- Provides a conceptually simple model for data as
relations (typically considered tables) with
all data visible.
28Database Data Models
- Object Oriented Data Model
- Encapsulates data and operations as Objects
29Object-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)
30Object-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
31Object-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
32Object-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.
33Object-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).
34Object-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.
35Generalization Hierarchy
36Database 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
37Test 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)
38ER 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)
39Diveshop ER Entities SITES
40Diveshop ER Entities DIVECUST
41Diveshop ER Entities DEST
42Diveshop ER Entities BIOLIFE
43Diveshop ER Entities SHIPWRCK
44Diveshop ER Entities DIVESTOK
Reorder Point
On Hand
Cost
Equipment Class
Sale Price
Description
DiveStok
Rental Price
Item No
45Diveshop 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
46Diveshop ER diagram DIVEITEM
Qty
Rental/ Sale
Item no
DiveItem
Line Note
Order no
47Diveshop ER diagram BIOSITE
48Diveshop ER diagram SHIPVIA
49DiveShop ER Diagram All
1
n
1
n
n
1
1
1
n
n
1
1
1/n
n
n
n
1
1
50Assignment 1Diveshop Questions
- How many tons was the sunken ship Delaware?
- What is customer Karen Ngs address?
- At what site might you find a Spotted Eagle Ray?
- Where 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