9/4/2000database management -- fall 2000 -- r. larson database models and introduction to access...

9/4/2000 Database Management -- Fall 2000 -- R. Larson

Database Models and Introduction to Access

University of California, Berkeley

School of Information Management and Systems

SIMS 257: Database Management


Last Time

• Database concepts and terminology

• Database Life cycle


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


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


Models (1)

ConceptualModel

LogicalModel

External Model

Conceptual requirements




Application 1

Application 1

Application 2 Application 3 Application 4

Application 2

Application 3

Application 4

External Model

External Model

External Model

Internal Model


Terms and Concepts

• Data Dictionary

• Data Administration

• Database Administration

• Data Steward

• DA

• DBA


Database System Life Cycle

Growth,Change, &

Maintenance6

Operations5

Integration4

Design1

Conversion3

PhysicalCreation

2


Today

• Models(2): DBMS types– Hierarchical– Network– Relational– Object-Oriented– Object-Relational


Database Data Models

• Hierarchical Model– Similar to data structures in programming

languages.Books

(id, title)

Publisher SubjectsAuthors

(first, last)


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.


Hierarchical Model for Cookie

BIBINFO

SUBINFO

PUBINFO INDXINFO

LIBINFO

CALLINFO


FOCUS BIBFILE DefinitionFILENAME = 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, $


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,$


SUBFILE Segment

FILENAME = SUBFILE, SUFFIX = FOC, $ SEGNAME = SUBINFO, SEGTYPE = S, $ FIELD = SUBID, ALIAS = SID, USAGE = A2, FIELDTYPE = I, $ FIELD = SUBJECT, ALIAS = SUB, USAGE = A32, $


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, $


Hierarchic Querying

• All searches must proceed from the “root” of the hierarchy, and traverse each segment containing required information


Hierarchical Query Processing

BIBINFO

SUBINFO

PUBINFO INDXINFO

LIBINFO

CALLINFO



• Network Model– Provides for single entries of data and

navigational “links” through chains of data.

Subjects Books

Authors

Publishers


Network Database Systems

• Network DBMS are an evolutionary step from Hierarchical systems.

• Hierarchical systems can be considered a subset of Network systems.


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)


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.


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


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”


DDL Definitions

• Example DDL for a DB (partial)

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

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

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.


Set Definition and Pointers

CUSTOMER INVOICE

INVOICE

INVOICE

INVOICE


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



• Relational Model– Provides a conceptually simple model for data

as relations (typically considered “tables”) with all data visible.

Book ID Title pubid Author id1 Introductio 2 12 The history 4 23 New stuff ab 3 34 Another title 2 45 And yet more 1 5

pubid pubname1 Harper2 Addison3 Oxford4 Que

Authorid Author name1 Smith2 Wynar3 Jones4 Duncan5 Applegate

Subid Subject1 cataloging2 history3 stuff

Book ID Subid1 22 13 34 24 3



• Object Oriented Data Model– Encapsulates data and operations as “Objects”

Books(id, title)

Publisher SubjectsAuthors

(first, last)


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)



• 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



• The attribute values of an object represent the object’s status. – Status is accessed or modified by sending

messages to the object to invoke the corresponding methods



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



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



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


Generalization Hierarchy

Employee NoName

AddressDate hired

Date of Birth

employee

Contract No.Date Hired

consultant

Annual SalaryStock Option

Salaried

Hourly Rate

Hourly

calculateAge

AllocateToContractcalculateStockBenefitcalculateWage



• 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


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)


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)


Diveshop ER Entities: SITES

Destinationno

Site no

Site Name

SiteNotes

SiteHighlight

DistanceFrom Town (M)

DistanceFrom Town (Km)

Skill Level

Visibility(ft)

Depth (m)

Depth (ft)Sites

Visibility (m)

Current


Diveshop ER Entities: DIVECUST

Name

Customer no

Street

State/ProvCityZIP/Postal

Code

Country

First Contact

PhoneDiveCust


Diveshop ER Entities: DEST

Destinationname

Destination no

Avg Temp (F)

SpringTemp (F) Avg

Temp (C)Summer

Temp (C)

SummerTemp (F)

TravelCost

WinterTemp (C)

FallTemp (F)

FallTemp (C)Dest

WinterTemp (F)

Accommodations

Body ofWater

NightLife

SpringTemp (C)


Diveshop ER Entities: BIOLIFE

Category

Species no

CommonName

Length(cm)

SpeciesName

Length(in)

Notesexternal

GraphicexternalBioLife


Diveshop ER Entities: SHIPWRCK

Site no

Ship Name

Category

InterestType Tonnage

Length (ft)

Beam(m)

Beam(ft)

Length(m)Shipwrck

Cause

Commentsexternal

DateSunk

Passengers/Crew

Graphicexternal

Survivors

Condition


Diveshop ER Entities: DIVESTOK

Description

Item No

EquipmentClass

ReorderPoint

On Hand Cost

SalePrice

DiveStok RentalPrice


Diveshop ER Entities: DIVEORDS

CustomerNo

Order no

SaleDate

ShipVia

DestinationCCExpDate

CCNumber

PaymentMethodDiveOrds

No ofPeople

VacationCost

ReturnDate

DepartDate


Diveshop ER diagram: DIVEITEM

Item no

Order no

Rental/Sale

Qty

Line Note

DiveItem


Diveshop ER diagram: BIOSITESpecies

NoSiteNo

BioSite


Diveshop ER diagram: SHIPVIAShip Via

ShipCost

ShipVia


DiveShop ER Diagram: AllCustomer

No

ShipVia

Dest

Sites

BioSite

ShipVia

ShipWrck

BioLife DiveStok

DiveItem

DiveOrds

DiveCust

CustomerNo

ShipVia

OrderNo

OrderNo

ItemNo

ItemNo

DestinationName

Destination

SpeciesNo

Site No

Destinationno

Site No

Destinationno

SpeciesNo

Site No

1

1

1

1

1

1

1/n

1

1n

n

n

n

n

n

n

n

1


Assignment 1:Diveshop Questions

1. How many tons was the sunken ship Delaware?2. What is customer Karen Ng’s 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

9/4/2000database management -- fall 2000 -- r. larson database models and introduction to access...

Documents

database management

database conceptual

concepts database

larson database models

time database concepts

relational model

larson models

larson terms